September 24, 2010 at 10:57 am
I have a query that is pulling data from various tables to use in a variable print program. So, I need to export this data out in a specific way. Normally this would not be a problem, except that I need to combine certain data and split out other data.
The query that I came up with gets the data out, but then it has to be re-arranged in Excel before being used in the final application, in other words, I can split it out, but I'm stumped at how to combine it. To combine it, I have to manually manipulate it in Excel. This works, but I want to bypass Excel. I'll give a shot at explaining what I have and what I need below.
Below is some code and samples. This is the raw data.
create table dbo.DataTest
(Venue varchar(50),
Address1 varchar(50),
City varchar(30),
State varchar(2),
Zip varchar(10),
EventDateTime datetime,
MenuChoices varchar(100),
OrderID int)
insert into dbo.datatest(venue, address1, city, state, zip, eventdatetime, menuchoices, orderid)
select 'Cobblestone River', '456 Ryan St', 'Brillion', 'WI', '54110', '2010-10-05 18:30:00.000', 'Sizzling Steak; Chicken Oscar', 235844 union all
select 'Cobblestone River', '456 Ryan St', 'Brillion', 'WI', '54110', '2010-10-06 18:30:00.000', 'Sizzling Steak; Chicken Oscar', 235844 union all
select 'Simba''s Restaurant', '2005 South St', 'Bonifay', 'FL', '32425', '2010-10-04 18:30:00.000', 'Sizzling Steak: Chicken Breast', 235807 union all
select 'Simba''s Restaurant', '2005 South St', 'Bonifay', 'FL', '32425', '2010-10-05 18:30:00.000', 'Sizzling Steak: Chicken Breast', 235807 union all
select 'Dad''s Restaurant', '234 Park St', 'Merigold', 'MS', '38759', '2010-10-05 18:30:00.000', 'Juicy Ribeye Steak; Jumbo Shrimp', 235825 union all
select 'Dad''s Restaurant', '234 Park St', 'Merigold', 'MS', '38759', '2010-10-06 18:30:00.000', 'Juicy Ribeye Steak; Jumbo Shrimp', 235825 union all
select 'Antoinette''s Grill', '130 French St', 'Blairsville', 'GA', '30512', '2010-10-04 18:30:00.000', 'Sizzling Steak; Chicken Parmesan', 235811 union all
select 'Antoinette''s Grill', '130 French St', 'Blairsville', 'GA', '30512', '2010-10-05 18:30:00.000', 'Sizzling Steak; Chicken Parmesan', 235811 union all
select 'Three Brother''s Restaurant', '2 Twins Ave', 'Oelwein', 'IA', '50662', '2010-10-04 18:30:00.000', 'Sizzling Steak; Broasted Chicken', 235812 union all
select 'Three Brother''s Restaurant', '2 Twins Ave', 'Oelwein', 'IA', '50662', '2010-10-05 18:30:00.000', 'Sizzling Steak; Broasted Chicken', 235812 union all
select 'Captains Galley', '11032 Swabby Blvd', 'Matthews', 'NC', '28105', '2010-10-04 18:30:00.000', 'Juicy Ribeye Steak; Broiled Salmon', 235826 union all
select 'Captains Galley', '11032 Swabby Blvd', 'Matthews', 'NC', '28105', '2010-10-06 18:30:00.000', 'Juicy Ribeye Steak; Broiled Salmon', 235826 union all
select 'My Family Grille', '12 Backyard Rd', 'Lititz', 'PA', '17543', '2010-10-04 18:30:00.000', 'Ham Loaf; Rotisserie Chicken', 235857 union all
select 'My Family Grille', '12 Backyard Rd', 'Lititz', 'PA', '17543', '2010-10-05 18:30:00.000', 'Ham Loaf; Rotisserie Chicken', 235857 union all
select 'Los Mules', '4971 Burrito Ave', 'Willow Park', 'TX', '76087', '2010-10-05 16:00:00.000', 'Juicy Ribeye Steak: Mesquite Chicken', 235839 union all
select 'Los Mules', '4971 Burrito Ave', 'Willow Park', 'TX', '76087', '2010-10-05 18:30:00.000', 'Juicy Ribeye Steak: Mesquite Chicken', 235839
And here is the query I've come up with so far. I need to have the Event Date and the Event Time as separate columns and I also need separate columns for the menu choices. There are only ever two dates/times for each location and only two menu choices so the code doesn't have to be smart enough to deal with three or more dates/time or menu items. There simply isn't enough room on the postcard to put another date or menu item.
select Venue as VName,
address1 as VAddr1,
city + ', ' + state as VCSZ,
zip as VZip,
convert(varchar(100), EventDateTime, 101) as [EDate],
case
when datepart(hour, EventDateTime) > 12 then cast(datepart(hour, EventDateTime) - 12 as varchar(2))
else cast(datepart(hour, EventDateTime) as varchar(2))
end + ':' +
right('00' + cast(datepart(minute, EventDateTime) as varchar(2)), 2) + ' ' +
case
when datepart(hour, EventDateTime) >= 12 then 'PM'
else 'AM'
end as [ETime],
isnull(rtrim(ltrim(replace(substring(replace(menuchoices, ':', ';'), 1, charindex(';', replace(menuchoices, ':', ';'))), ';', ''))), '') as Menu1,
isnull(rtrim(ltrim(replace(substring(replace(menuchoices, ':', ';'), charindex(';', replace(menuchoices, ':', ';')), 50), ';', ''))), '') as Menu2,
isnull(menuchoices, '') as Menu,
Orderid as Var2
from dbo.datatest
So, I can split the data out, but I haven't the foggiest idea of how to combine it to the format needed. The attached picture shows the format I need the result to be in.
Each location with its two events goes onto one postcard, there will be only two events per postcard. In the variable print program, the OrderID drives which which events get printed on the postcard.
I need one row per location that lists the two event dates and times in sesparate columns and the two menu choices in separate columns. Again, see the attached picture for the final format needed. I will need the following columns: Venue | Address1 | City | State | Zip | EDate1 | Edate2 | ETime1 | Etime2 | Menu1 | Menu2 | OrderID
I can do all the manipulation in Excel, but it is time consuming and taxing and in a typical file there are between 40 and 50 locations. I want to do it in SQL as a) it will be faster and b) it will avoid human error.
I'm not totally sure of where to start on this and how to get everything to combine properly. I've started digging into doing a cross tab or something (and am reading Jeff Moden's article on it), but I'm not sure how to apply it to what I need.
Any direction is greatly appreciated.
-- Kit
September 24, 2010 at 1:05 pm
Kit, is the sample data representative of the data in your database in so much as there is no way to differentiate the 2 events with the exception of the event date and event times? Is EDate1 always the earliest? If so, how about using group by with Min, Max functions? something along the lines of this...
create table dbo.DataTest
(Venue varchar(50),
Address1 varchar(50),
City varchar(30),
State varchar(2),
Zip varchar(10),
EventDateTime datetime,
MenuChoices varchar(100),
OrderID int) ;
insert into dbo.datatest(venue, address1, city, state, zip, eventdatetime, menuchoices, orderid)
select 'Cobblestone River', '456 Ryan St', 'Brillion', 'WI', '54110', '2010-10-05 18:30:00.000', 'Sizzling Steak; Chicken Oscar', 235844 union all
select 'Cobblestone River', '456 Ryan St', 'Brillion', 'WI', '54110', '2010-10-06 18:30:00.000', 'Sizzling Steak; Chicken Oscar', 235844 union all
select 'Simba''s Restaurant', '2005 South St', 'Bonifay', 'FL', '32425', '2010-10-04 18:30:00.000', 'Sizzling Steak: Chicken Breast', 235807 union all
select 'Simba''s Restaurant', '2005 South St', 'Bonifay', 'FL', '32425', '2010-10-05 18:30:00.000', 'Sizzling Steak: Chicken Breast', 235807 union all
select 'Dad''s Restaurant', '234 Park St', 'Merigold', 'MS', '38759', '2010-10-05 18:30:00.000', 'Juicy Ribeye Steak; Jumbo Shrimp', 235825 union all
select 'Dad''s Restaurant', '234 Park St', 'Merigold', 'MS', '38759', '2010-10-06 18:30:00.000', 'Juicy Ribeye Steak; Jumbo Shrimp', 235825 union all
select 'Antoinette''s Grill', '130 French St', 'Blairsville', 'GA', '30512', '2010-10-04 18:30:00.000', 'Sizzling Steak; Chicken Parmesan', 235811 union all
select 'Antoinette''s Grill', '130 French St', 'Blairsville', 'GA', '30512', '2010-10-05 18:30:00.000', 'Sizzling Steak; Chicken Parmesan', 235811 union all
select 'Three Brother''s Restaurant', '2 Twins Ave', 'Oelwein', 'IA', '50662', '2010-10-04 18:30:00.000', 'Sizzling Steak; Broasted Chicken', 235812 union all
select 'Three Brother''s Restaurant', '2 Twins Ave', 'Oelwein', 'IA', '50662', '2010-10-05 18:30:00.000', 'Sizzling Steak; Broasted Chicken', 235812 union all
select 'Captains Galley', '11032 Swabby Blvd', 'Matthews', 'NC', '28105', '2010-10-04 18:30:00.000', 'Juicy Ribeye Steak; Broiled Salmon', 235826 union all
select 'Captains Galley', '11032 Swabby Blvd', 'Matthews', 'NC', '28105', '2010-10-06 18:30:00.000', 'Juicy Ribeye Steak; Broiled Salmon', 235826 union all
select 'My Family Grille', '12 Backyard Rd', 'Lititz', 'PA', '17543', '2010-10-04 18:30:00.000', 'Ham Loaf; Rotisserie Chicken', 235857 union all
select 'My Family Grille', '12 Backyard Rd', 'Lititz', 'PA', '17543', '2010-10-05 18:30:00.000', 'Ham Loaf; Rotisserie Chicken', 235857 union all
select 'Los Mules', '4971 Burrito Ave', 'Willow Park', 'TX', '76087', '2010-10-05 16:00:00.000', 'Juicy Ribeye Steak: Mesquite Chicken', 235839 union all
select 'Los Mules', '4971 Buriito Ave', 'Willow Park', 'TX', '76087', '2010-10-05 18:30:00.000', 'Juicy Ribeye Steak: Mesquite Chicken', 235839 ;
select Venue as VName,
address1 as VAddr1,
city + ', ' + state as VCSZ,
zip as VZip,
CONVERT(VARCHAR(10),MIN(EventDateTime), 101) AS EDate1,
case
when datepart(hour, MIN(EventDateTime)) > 12 then cast(datepart(hour, MIN(EventDateTime)) - 12 as varchar(2))
else cast(datepart(hour, MIN(EventDateTime)) as varchar(2))
end + ':' +
right('00' + cast(datepart(minute, MIN(EventDateTime)) as varchar(2)), 2) + ' ' +
case
when datepart(hour, MIN(EventDateTime)) >= 12 then 'PM'
else 'AM'
end as [ETime1],
CONVERT(VARCHAR(10),MAX(EventDateTime), 101) AS EDate2,
case
when datepart(hour, MAX(EventDateTime)) > 12 then cast(datepart(hour, MAX(EventDateTime)) - 12 as varchar(2))
else cast(datepart(hour, MAX(EventDateTime)) as varchar(2))
end + ':' +
right('00' + cast(datepart(minute, MAX(EventDateTime)) as varchar(2)), 2) + ' ' +
case
when datepart(hour, MAX(EventDateTime)) >= 12 then 'PM'
else 'AM'
end as [ETime2],
isnull(rtrim(ltrim(replace(substring(replace(menuchoices, ':', ';'), 1, charindex(';', replace(menuchoices, ':', ';'))), ';', ''))), '') as Menu1,
isnull(rtrim(ltrim(replace(substring(replace(menuchoices, ':', ';'), charindex(';', replace(menuchoices, ':', ';')), 50), ';', ''))), '') as Menu2,
isnull(menuchoices, '') as Menu,
Orderid as Var2
from dbo.datatest
GROUP BY Venue, Address1,City, state, zip, menuchoices, orderid;
DROP TABLE dbo.[DataTest];
Perhaps not the most elegant, but it should get the job done right up until the change the requirement to be more than 2 events...
-Luke.
September 24, 2010 at 1:26 pm
I think you can solve this one with a PIVOT and some splitting
;WITH cte AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY Venue, Address1, City, State, Zip, MenuChoices, OrderID ORDER BY EventDateTime) AS ROW, *
FROM DataTest
)
,
cte2 AS
(
SELECT Venue, Address1, City, State, Zip,
CONVERT(VARCHAR, [1], 101) AS EDate1,
CONVERT(VARCHAR, [2], 101) AS Edate2,
CONVERT(VARCHAR, [1], 100) AS ETime1,
CONVERT(VARCHAR, [2], 100) AS Etime2,
REPLACE(REPLACE(MenuChoices, ':', '^'), ';', '^') AS MenuChoices,
OrderID
FROM cte
PIVOT (MAX(EventDateTime) FOR ROW IN ([1],[2])) AS Z
)
SELECT Venue, Address1, City, State, Zip,
EDate1,
Edate2,
RIGHT(ETime1, CHARINDEX(SPACE(1), REVERSE(ETime1)) - 1) AS ETime1,
RIGHT(Etime2, CHARINDEX(SPACE(1), REVERSE(Etime2)) - 1) AS Etime2,
LEFT(MenuChoices, CHARINDEX('^', MenuChoices) - 1) AS Menu1,
LTRIM(RIGHT(MenuChoices, CHARINDEX('^', REVERSE(MenuChoices)) - 1)) AS Menu2,
OrderID
FROM cte2
September 24, 2010 at 2:34 pm
Thanks for the ideas, guys. I, unfortunately, didn't have the time to try them out today, but I will (I hope) on Monday. I'll let you know how each suggestion goes.
-- Kit
September 27, 2010 at 6:11 am
Kit,
Try using DatePart, DateAdd, and CharIndex in your code. It'll make your select statement a lot less messy.
select Venue as VName,
address1 as VAddr1,
city + ', ' + state as VCSZ,
zip as VZip,
Convert(char(10),EventDateTime,101) as EDate,
Convert(char(2),DATEPART(hh,DATEADD(hh,-12,EventDateTime))) + ':'
+ CASE Convert(char(2),DATEPART(mi,EventDateTime)) WHEN 0 THEN '00'
ELSE Convert(char(2),DATEPART(mi,EventDateTime)) END + ' '
+ CASE WHEN DATEPART(hh,EventDateTime) > 12 THEN 'PM' ELSE 'AM' END AS ETime,
Substring(menuchoices,1,Charindex(';',REPLACE(menuchoices,':',';'))-1) as Menu1,
Substring(menuchoices,Charindex(';',REPLACE(menuchoices,':',';'))+1,LEN(menuchoices)) as Menu2,
isnull(menuchoices, '') as Menu,
Orderid as Var2
from dbo.datatest
September 27, 2010 at 6:42 am
And another one:
;WITH OrderedPartitionedData AS
(SELECT RowID = ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY EventDateTime), *
FROM #DataTest)
SELECT dy1.venue, dy1.address1, dy1.city, dy1.[state], dy1.zip,
EDate1 = CONVERT(VARCHAR, dy1.eventdatetime, 101),
Edate2 = CONVERT(VARCHAR, dy2.eventdatetime, 101),
ETime1 = LTRIM(RIGHT(CONVERT(VARCHAR, dy1.eventdatetime, 100), 8)),
Etime2 = LTRIM(RIGHT(CONVERT(VARCHAR, dy2.eventdatetime, 100), 8)),
Menu1 = dy1.menuchoices,
Menu2 = dy2.menuchoices,
dy1.OrderID
FROM OrderedPartitionedData dy1
INNER JOIN OrderedPartitionedData dy2 ON dy2.OrderID = dy1.OrderID AND dy2.RowID = 2
WHERE dy1.RowID = 1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 27, 2010 at 7:51 am
Chris, your code doesn't do what he wants Menu1 and Menu2 to do.
Also, you forgot to remove the # sign from your table name. Since he's a newbie and didn't use temp tables, that might confuse him. @=)
EDIT: Although I missed the whole "1 record per venue" in my code. So I'm going back to redo it.
September 27, 2010 at 8:08 am
Brandie Tarvin (9/27/2010)
Chris, your code doesn't do what he wants Menu1 and Menu2 to do.Also, you forgot to remove the # sign from your table name. Since he's a newbie and didn't use temp tables, that might confuse him. @=)
EDIT: Although I missed the whole "1 record per venue" in my code. So I'm going back to redo it.
Thanks Brandie :blush:
Kit's no newbie, she knows about temp tables.
;WITH OrderedPartitionedData AS
(SELECT RowID = ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY EventDateTime), *
FROM #DataTest)
SELECT dy1.venue, dy1.address1, dy1.city, dy1.[state], dy1.zip,
EDate1 = CONVERT(VARCHAR, dy1.eventdatetime, 101),
Edate2 = CONVERT(VARCHAR, dy2.eventdatetime, 101),
ETime1 = LTRIM(RIGHT(CONVERT(VARCHAR, dy1.eventdatetime, 100), 8)),
Etime2 = LTRIM(RIGHT(CONVERT(VARCHAR, dy2.eventdatetime, 100), 8)),
Menu1 = LEFT(dy1.menuchoices, CHARINDEX(';', REPLACE(dy1.menuchoices, ':', ';'))-1),
Menu2 = LTRIM(REVERSE(LEFT(REVERSE(dy1.menuchoices), CHARINDEX(';', REVERSE(REPLACE(dy1.menuchoices, ':', ';')))-1))),
dy1.OrderID
FROM OrderedPartitionedData dy1
INNER JOIN OrderedPartitionedData dy2 ON dy2.OrderID = dy1.OrderID AND dy2.RowID = 2
WHERE dy1.RowID = 1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 27, 2010 at 8:08 am
Okay, here's redone code taking into account the event date & time columns being on the same row. However, you're making an assumption that Event 1's menu will always be the same as Event 2. Do you know for a fact this is true?
If so, then this code will work for you. Note the subquery making sure the earlier event is always considered as Event1:
select DISTINCT dt1.Venue as VName,
dt1.address1 as VAddr1,
dt1.city + ', ' + dt1.state as VCSZ,
dt1.zip as VZip,
Convert(char(10),dt1.EventDateTime,101) as EDate1,
Convert(char(10),dt2.EventDateTime,101) as EDate2,
Convert(char(2),DATEPART(hh,DATEADD(hh,-12,dt1.EventDateTime))) + ':'
+ CASE Convert(char(2),DATEPART(mi,dt1.EventDateTime)) WHEN 0 THEN '00'
ELSE Convert(char(2),DATEPART(mi,dt1.EventDateTime)) END + ' '
+ CASE WHEN DATEPART(hh,dt1.EventDateTime) > 12 THEN 'PM' ELSE 'AM' END AS ETime1,
Convert(char(2),DATEPART(hh,DATEADD(hh,-12,dt2.EventDateTime))) + ':'
+ CASE Convert(char(2),DATEPART(mi,dt2.EventDateTime)) WHEN 0 THEN '00'
ELSE Convert(char(2),DATEPART(mi,dt2.EventDateTime)) END + ' '
+ CASE WHEN DATEPART(hh,dt2.EventDateTime) > 12 THEN 'PM' ELSE 'AM' END AS ETime2,
Substring(dt1.menuchoices,1,Charindex(';',REPLACE(dt1.menuchoices,':',';'))-1) as Menu1,
Substring(dt1.menuchoices,Charindex(';',REPLACE(dt1.menuchoices,':',';'))+1,LEN(dt1.menuchoices)) as Menu2,
isnull(dt1.menuchoices, '') as Menu,
dt1.Orderid as Var2
from dbo.#datatest dt1
INNER JOIN (SELECT OrderID, MAX(EventDateTime) as EventDateTime
FROM dbo.#datatest
GROUP BY OrderID) dt2
ON dt1.OrderID = dt2.OrderID
AND dt1.EventDateTime <> dt2.EventDateTime
September 27, 2010 at 9:51 am
Luke L (9/24/2010)
Kit, is the sample data representative of the data in your database in so much as there is no way to differentiate the 2 events with the exception of the event date and event times? Is EDate1 always the earliest? If so, how about using group by with Min, Max functions? something along the lines of this...Perhaps not the most elegant, but it should get the job done right up until the change the requirement to be more than 2 events...
-Luke.
Thanks for the code, Luke. I hadn't thought of using the MAX and MIN and it works for everything except the event at Los Mules where we have the same date, but two different times.
Yes, this is representative of the data I have in the database. The requirements for the mailing are:
1 Location
Two Event Times, either two times on the same date, or two dates at the same time, or two dates at two times.
Two Menu Choices
Is EDate1 always the earliest?
Yes, Edate1 should be the earliest date.
Working through the other code samples now (finally got a few moment in the day to take a look at it.)
-- Kit
September 27, 2010 at 10:03 am
steve-893342 (9/24/2010)
I think you can solve this one with a PIVOT and some splitting
Thanks Steve for the code. That date at Los Mules where the Event date is the same but the time is different is throwing a big monkey wrench into the works. It does the job, except for those two dates where it gives me NULL for the time.
-- Kit
September 27, 2010 at 10:19 am
Brandie Tarvin (9/27/2010)
Kit,Try using DatePart, DateAdd, and CharIndex in your code. It'll make your select statement a lot less messy.
select Venue as VName,
address1 as VAddr1,
city + ', ' + state as VCSZ,
zip as VZip,
Convert(char(10),EventDateTime,101) as EDate,
Convert(char(2),DATEPART(hh,DATEADD(hh,-12,EventDateTime))) + ':'
+ CASE Convert(char(2),DATEPART(mi,EventDateTime)) WHEN 0 THEN '00'
ELSE Convert(char(2),DATEPART(mi,EventDateTime)) END + ' '
+ CASE WHEN DATEPART(hh,EventDateTime) > 12 THEN 'PM' ELSE 'AM' END AS ETime,
Substring(menuchoices,1,Charindex(';',REPLACE(menuchoices,':',';'))-1) as Menu1,
Substring(menuchoices,Charindex(';',REPLACE(menuchoices,':',';'))+1,LEN(menuchoices)) as Menu2,
isnull(menuchoices, '') as Menu,
Orderid as Var2
from dbo.datatest
Thanks for the cleaner code on the menu stuff. I built that on the fly so it got pretty messy.
On the conversion of the Date, the one thing your code doesn't handle is event times before noon. 11:00:00 am gets changed to 23:00:00 (11 PM for those not up on 24hr time). And yes, we can have an event that's at 11:00 in the morning, just to make my life more difficult. 🙂
-- Kit
September 27, 2010 at 10:31 am
Chris Morris-439714 (9/27/2010)
Brandie Tarvin (9/27/2010)
Chris, your code doesn't do what he wants Menu1 and Menu2 to do.Also, you forgot to remove the # sign from your table name. Since he's a newbie and didn't use temp tables, that might confuse him. @=)
EDIT: Although I missed the whole "1 record per venue" in my code. So I'm going back to redo it.
Thanks Brandie :blush:
Kit's no newbie, she knows about temp tables.
Yes, indeed I do know about temp tables, and CTEs! :w00t: I would consider myself an experienced novice now. 🙂
Now to just decipher what is going on below cause the below code works very nicely. Thanks a bunch Chris. 😀
[/quote]
;WITH OrderedPartitionedData AS
(SELECT RowID = ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY EventDateTime), *
FROM #DataTest)
SELECT dy1.venue, dy1.address1, dy1.city, dy1.[state], dy1.zip,
EDate1 = CONVERT(VARCHAR, dy1.eventdatetime, 101),
Edate2 = CONVERT(VARCHAR, dy2.eventdatetime, 101),
ETime1 = LTRIM(RIGHT(CONVERT(VARCHAR, dy1.eventdatetime, 100), 8)),
Etime2 = LTRIM(RIGHT(CONVERT(VARCHAR, dy2.eventdatetime, 100), 8)),
Menu1 = LEFT(dy1.menuchoices, CHARINDEX(';', REPLACE(dy1.menuchoices, ':', ';'))-1),
Menu2 = LTRIM(REVERSE(LEFT(REVERSE(dy1.menuchoices), CHARINDEX(';', REVERSE(REPLACE(dy1.menuchoices, ':', ';')))-1))),
dy1.OrderID
FROM OrderedPartitionedData dy1
INNER JOIN OrderedPartitionedData dy2 ON dy2.OrderID = dy1.OrderID AND dy2.RowID = 2
WHERE dy1.RowID = 1[/quote]
If I'm understanding this right, you're taking everything into a CTE and giving it a row number, splitting out what needs to be split out and then joining it to itself and grouping the results?
The part makes my head hurt is the joining the table to itself. Know of any articles about that which might help make my head hurt less?
-- Kit
September 27, 2010 at 10:34 am
Brandie Tarvin (9/27/2010)
Okay, here's redone code taking into account the event date & time columns being on the same row. However, you're making an assumption that Event 1's menu will always be the same as Event 2. Do you know for a fact this is true?If so, then this code will work for you. Note the subquery making sure the earlier event is always considered as Event1:
Thanks Brandie, this works as well. Yes, the menu will be the same for the two events. Since the events are at the same location and the agents putting on these events are getting group rates, it's pretty safe to assume that the menu will stay the same for the two events. 🙂
Thanks again for the code, and to every one who helped out. Got stuff to study and learn about, which is always a good thing!
-- Kit
September 27, 2010 at 11:26 am
Kit G (9/27/2010)
The part makes my head hurt is the joining the table to itself. Know of any articles about that which might help make my head hurt less?
It's called a Recursive Query. That's what CTEs (Common Table Expressions) are built for. Google both terms and you'll find a plethora of articles about both.
Think of it like a pool pump. There's got to be water in the pump before the pump can work (or it burns out), the pump adds water to the pool, then takes water from the pool to pump it back in. The pump's filter? That's the SELECT statement.
On second thought, that might not be the best metaphor I've ever used... Let me know if I've confused you.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply