November 19, 2013 at 8:11 am
Good Afternoon,
I have been tasked with creating a travel Itinerary, simple stuff....
however upon further investigation each part of a journey is stored in a different table each one with thieir own date(sometimes datetime) for example
TS_Flights
TS_Accomodation
TS_HireCar
TS_Taxi's
etc
The issue i am having is that the end user has requested that the itinerary be in chronological order.
My initial thought was that i would create 4 tables and then use the CountRows()=0 function to hide the table if the query yielded no results
Is there a way that i can sort the tables based on date.
November 19, 2013 at 8:22 am
waxb18 (11/19/2013)
Good Afternoon,I have been tasked with creating a travel Itinerary, simple stuff....
however upon further investigation each part of a journey is stored in a different table each one with thieir own date(sometimes datetime) for example
TS_Flights
TS_Accomodation
TS_HireCar
TS_Taxi's
etc
The issue i am having is that the end user has requested that the itinerary be in chronological order.
My initial thought was that i would create 4 tables and then use the CountRows()=0 function to hide the table if the query yielded no results
Is there a way that i can sort the tables based on date.
With an ORDER BY. 😀
If you need more detailed assistance we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 19, 2013 at 8:35 am
Unfortunately i dont know if an order by will work. They are completely seperate tables not rows within one table.
I will do my best to get together some scripts for creating a test but it most likely wont be pretty, my SQL isnt so good but i will try and work something out
November 19, 2013 at 8:42 am
waxb18 (11/19/2013)
Unfortunately i dont know if an order by will work. They are completely seperate tables not rows within one table.I will do my best to get together some scripts for creating a test but it most likely wont be pretty, my SQL isnt so good but i will try and work something out
Do you have this as a single result set or do you have 4 different result sets?
select [Columns] from TS_Flights union all
select [Columns] from TS_Accomodation union all
select [Columns] from TS_HireCar union all
select [Columns] from TS_Taxi's
order by YourDateColumn
I have a feeling that won't work because the columns are likely not consistent across these tables. I can certainly help come up with a way to do this but without more information it is pure speculation.
If you take 5-10 minutes and read the article I pointed you to it explains how to script the tables and the data so that others can consume it easily.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 19, 2013 at 8:48 am
That seems exactly whats required to get them into one data set
I have created some scripts that will create and input some data into them see attached...I will read your post now
Sorry if its in the wrong format
November 19, 2013 at 8:58 am
Just read through your post, if you need me to rewrite the scripts please let me know
November 19, 2013 at 9:08 am
No it's cool. I would suggest though that you test this stuff before you post it. There were loads of errors. This is the script after I combined them into a single script and cleaned it up.
set dateformat dmy
CREATE TABLE [dbo].[TS_CUSTOM_TRAVELTEST_TAB_ACCOMODATION](
[CFT_TREQ_ID] [float] NOT NULL,
[CFT_TREQ_PARENT_ID] [float] NULL,
[CFT_TREQ__GUID] [uniqueidentifier] NULL,
[CFT_TREQ_DATES_FROM] [datetime] NULL,
[CFT_TREQ_DATES_TO] [datetime] NULL,
[CFT_TREQ_NO_OF_NIGHTS] [float] NULL,
[CFT_TREQ_HOTEL_NAME] [nvarchar](100) NULL,
[CFT_TREQ_HOTEL_ADDRESS] [nvarchar](1000) NULL,
[CFT_TREQ_HOTEL_TELEPHONE_NO] [nvarchar](30) NULL,
[CFT_TREQ_COST_PER_NIGHT] [float] NULL,
[CFT_TREQ_BOOKING_REFERENCE] [nvarchar](50) NULL,
CONSTRAINT [TS_CUSTOM_TRAVELTEST_TAB_ACCOMODATION_PK] PRIMARY KEY NONCLUSTERED
(
[CFT_TREQ_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[TS_CUSTOM_TRAVELTEST_TAB_ACCOMODATION]
([CFT_TREQ_ID]
,[CFT_TREQ_PARENT_ID]
,[CFT_TREQ__GUID]
,[CFT_TREQ_DATES_FROM]
,[CFT_TREQ_DATES_TO]
,[CFT_TREQ_NO_OF_NIGHTS]
,[CFT_TREQ_HOTEL_NAME]
,[CFT_TREQ_HOTEL_ADDRESS]
,[CFT_TREQ_HOTEL_TELEPHONE_NO]
,[CFT_TREQ_COST_PER_NIGHT]
,[CFT_TREQ_BOOKING_REFERENCE])
VALUES
(871
,12
,NULL
,'21/11/2013'
,'30/11/2031'
,8
,'Hilton'
,'Hilton Barbados'
,'111555888'
,100
,'12234566')
GO
CREATE TABLE [dbo].[TS_CUSTOM_TRAVELTEST_TAB_FLIGHT](
[CFT_TREQ_ID] [float] NOT NULL,
[CFT_TREQ_PARENT_ID] [float] NULL,
[CFT_TREQ__GUID] [uniqueidentifier] NULL,
[CFT_TREQ_INBOUNDOUTBOUND] [nvarchar](10) NULL,
[CFT_TREQ_DEPART_FROM] [nvarchar](200) NULL,
[CFT_TREQ_TRAVEL_TO] [nvarchar](200) NULL,
[CFT_TREQ_DEPARTURE_DATE] [datetime] NULL,
[CFT_TREQ_DEPARTURE_TIME] [datetime] NULL,
[CFT_TREQ_AIRPORT_TERMINAL] [nvarchar](30) NULL,
[CFT_TREQ_AIRLINE_REFERENCE] [nvarchar](100) NULL,
[CFT_TREQ_AIRLINE] [nvarchar](50) NULL,
[CFT_TREQ_FLIGHT_NO] [nvarchar](50) NULL,
[CFT_TREQ_COST] [float] NULL,
[CFT_TREQ_ARRIVAL_DATE] [datetime] NULL,
[CFT_TREQ_ARRIVAL_TIME] [datetime] NULL,
CONSTRAINT [TS_CUSTOM_TRAVELTEST_TAB_TEST_PK] PRIMARY KEY NONCLUSTERED
(
[CFT_TREQ_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[TS_CUSTOM_TRAVELTEST_TAB_FLIGHT]
([CFT_TREQ_ID]
,[CFT_TREQ_PARENT_ID]
,[CFT_TREQ__GUID]
,[CFT_TREQ_INBOUNDOUTBOUND]
,[CFT_TREQ_DEPART_FROM]
,[CFT_TREQ_TRAVEL_TO]
,[CFT_TREQ_DEPARTURE_DATE]
,[CFT_TREQ_DEPARTURE_TIME]
,[CFT_TREQ_AIRPORT_TERMINAL]
,[CFT_TREQ_AIRLINE_REFERENCE]
,[CFT_TREQ_AIRLINE]
,[CFT_TREQ_FLIGHT_NO]
,[CFT_TREQ_COST]
,[CFT_TREQ_ARRIVAL_DATE]
,[CFT_TREQ_ARRIVAL_TIME])
VALUES
(897
,12
,NULL
,'Outbound'
,'London Heathrow'
,'Barbados'
,'20/11/2013'
,'12:00'
,'Terminal 5'
,'BA-123456'
,'BA'
,'BA-001'
,300
,'21/11/2013'
,'05:00')
GO
CREATE TABLE [dbo].[TS_CUSTOM_TRAVELTEST_TAB_HIRECAR](
[CFT_TREQ_ID] [float] NOT NULL,
[CFT_TREQ_PARENT_ID] [float] NULL,
[CFT_TREQ__GUID] [uniqueidentifier] NULL,
[CFT_TREQ_COLLECTION_DATE__TIME] [datetime] NULL,
[CFT_TREQ_DROPOFF_DATE__TIME] [datetime] NULL,
[CFT_TREQ_HIRE_COMPANY] [nvarchar](100) NULL,
[CFT_TREQ_PICKUP__DROP_OFF_ADDRESS] [nvarchar](500) NULL,
[CFT_TREQ_VEHICLE_GROUP] [nvarchar](15) NULL,
[CFT_TREQ_BOOKING_REFERENCE] [nvarchar](30) NULL,
[CFT_TREQ_COST] [float] NULL,
CONSTRAINT [TS_CUSTOM_TRAVELTEST_TAB_HIRECAR_PK] PRIMARY KEY NONCLUSTERED
(
[CFT_TREQ_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
------------------------------
INSERT INTO [dbo].[TS_CUSTOM_TRAVELTEST_TAB_HIRECAR]
([CFT_TREQ_ID]
,[CFT_TREQ_PARENT_ID]
,[CFT_TREQ__GUID]
,[CFT_TREQ_COLLECTION_DATE__TIME]
,[CFT_TREQ_DROPOFF_DATE__TIME]
,[CFT_TREQ_HIRE_COMPANY]
,[CFT_TREQ_PICKUP__DROP_OFF_ADDRESS]
,[CFT_TREQ_VEHICLE_GROUP]
,[CFT_TREQ_BOOKING_REFERENCE]
,[CFT_TREQ_COST])
VALUES
(824
,12
, null
,'21/11/2013 05:00'
,'30/11/2013 05:00'
,'Avis'
,'Barbados Airport'
,'4'
,'123ABC'
,500)
GO
CREATE TABLE [dbo].[TS_CUSTOM_TRAVELTEST_TAB_TRAIN](
[CFT_TREQ_ID] [float] NOT NULL,
[CFT_TREQ_PARENT_ID] [float] NULL,
[CFT_TREQ__GUID] [uniqueidentifier] NULL,
[CFT_TREQ_DATE_OF_DEPARTURE] [datetime] NULL,
[CFT_TREQ_TIME_OF_DEPARTURE] [datetime] NULL,
[CFT_TREQ_DEPART_FROM] [nvarchar](100) NULL,
[CFT_TREQ_TRAVEL_TO] [nvarchar](50) NULL,
[CFT_TREQ_BOOKING_REFERENCE] [nvarchar](50) NULL,
[CFT_TREQ_COST] [float] NULL,
CONSTRAINT [TS_CUSTOM_TRAVELTEST_TAB_TRAIN_PK] PRIMARY KEY NONCLUSTERED
(
[CFT_TREQ_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-----------------------
INSERT INTO [dbo].[TS_CUSTOM_TRAVELTEST_TAB_TRAIN]
([CFT_TREQ_ID]
,[CFT_TREQ_PARENT_ID]
,[CFT_TREQ__GUID]
,[CFT_TREQ_DATE_OF_DEPARTURE]
,[CFT_TREQ_TIME_OF_DEPARTURE]
,[CFT_TREQ_DEPART_FROM]
,[CFT_TREQ_TRAVEL_TO]
,[CFT_TREQ_BOOKING_REFERENCE]
,[CFT_TREQ_COST])
VALUES
(458
,12
,NULL
,'20/11/2013'
,'09:00'
,'Oxford'
,'Heathrow Airport'
,'895486'
,50)
GO
Why do you have date and time separated into 2 columns? They are both defined as datetime columns yet you only use either the date or the time portion of each one.
The UNION ALL guess won't work exactly because when you use UNION the columns must match in each table and these tables don't quite match. Perhaps if you can explain what you want as output based on your sample data we can figure this out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 19, 2013 at 9:22 am
I understand the issue with dates and times, this is how the tables were when i inherited them. I will amend them appropriately.
Essentially all i want to be able to do is use these tables to tell the story so to speak based on their dates and Journey ID.
So for journey ID 12 (example given in the code) the sequence is:
Train Journey from Oxford to Heathrow
Flight to Barbados
Hire Car @ Barbados Airport
Accomodation in Barbados Hilton.
So what I neet to be able to sort the 4 tables based on their date.
November 19, 2013 at 9:32 am
waxb18 (11/19/2013)
Essentially all i want to be able to do is use these tables to tell the story so to speak based on their dates and Journey ID.
So for journey ID 12 (example given in the code) the sequence is:
Train Journey from Oxford to Heathrow
Flight to Barbados
Hire Car @ Barbados Airport
Accomodation in Barbados Hilton.
So what I neet to be able to sort the 4 tables based on their date.
Got it. But what data do you want displayed?
If you can trim down the columns so that all the tables have a uniform set of columns this is simple using UNION ALL.
Here is an example as a possibility for the Accommodation table.
select 'Accomodation', CFT_TREQ_DATES_FROM as BeginDate, CFT_TREQ_DATES_TO as EndDate, CFT_TREQ_HOTEL_NAME from [TS_CUSTOM_TRAVELTEST_TAB_ACCOMODATION]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 20, 2013 at 1:33 am
Unfortunately i cant trim down the columns the data from each table needs to bre represented in the column.
I think the best way to solve this would be to create a SPROC looking at all 5 tables, then execute a plan which will create a temporary table which can be used to populate the SSRS report.
But how to do that i havent a clue, time to get learning
November 20, 2013 at 3:31 am
Just taking a quick look I don't see anything showing what the output should look like based on the sample data in the tables. Can you please put together what it is you would like to see as the final product (results) from the query/stored procedure based on the sample data. This should include all the headers for each column of data so that we can see where data should go.
November 20, 2013 at 7:14 am
Update:
I have taken the Type, ID and Date of each of the tables, union'd them and sorted them to give me a chronological view.
The next stage im thinking would be to have a stored procedure to loop through the view and return the table rows based on their Type and ID.
I dont even know if this is possible or not but im willing to give it a try
Any pointers would be greatly beneficial
November 20, 2013 at 7:26 am
waxb18 (11/20/2013)
Update:I have taken the Type, ID and Date of each of the tables, union'd them and sorted them to give me a chronological view.
The next stage im thinking would be to have a stored procedure to loop through the view and return the table rows based on their Type and ID.
I dont even know if this is possible or not but im willing to give it a try
Any pointers would be greatly beneficial
Update, look at your sample data, how would you like that data presented by the query or stored procedure. Looking back it looks like this is the third time (second by me) to ask you for this information. Personally, I am a visual type person, show me the beginning (your sample data) and the end result (what we are asking you to provide) and then I should be able to figure out how to get from A to B.
November 20, 2013 at 7:27 am
I agree with Lynn about showing us what you want.
There is absolutely no need to resort to looping for this type of thing. We can help you find a solution if you can tell us what the result should be.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 20, 2013 at 8:34 am
Im ever so sorry guys, i didnt see the 2nd page
The best way to show the result would be to see the spreadsheet attached
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply