May 25, 2011 at 8:14 am
Hi Guys,
I'm stuck with the following piece of SQL code where I'm trying to retrieve simplefied data in the end.
Start information:
DECLARE @BOOKING_PERIOD TABLE
(
BOOKING_PERIOD_ID INT IDENTITY(1,1),
[FROM] DATETIME,
TILL DATETIME
)
DECLARE @AGE TABLE
(
AGE_ID INT IDENTITY(1,1),
BOOKING_PERIOD_ID INT,
MIN_AGE INT ,
MAX_AGE INT,
MIN_FULLPAYERS INT
)
DECLARE @discount TABLE
(
DISCOUNT_ID INT IDENTITY(1,1),
AGE_ID INT,
DISCOUNT DECIMAL(11,4)
)
INSERT INTO @BOOKING_PERIOD ([FROM],TILL ) VALUES ( '2011-07-01','2011-07-31' )
INSERT INTO @BOOKING_PERIOD ([FROM],TILL ) VALUES ( '2011-08-01',NULL )
INSERT INTO @AGE (BOOKING_PERIOD_ID,MIN_AGE,MAX_AGE,MIN_FULLPAYERS) VALUES ( 1,0,1,NULL)
INSERT INTO @AGE (BOOKING_PERIOD_ID,MIN_AGE,MAX_AGE,MIN_FULLPAYERS) VALUES ( 1,2,12,NULL)
INSERT INTO @AGE (BOOKING_PERIOD_ID,MIN_AGE,MAX_AGE,MIN_FULLPAYERS) VALUES ( 1,13,18,NULL)
INSERT INTO @AGE (BOOKING_PERIOD_ID,MIN_AGE,MAX_AGE,MIN_FULLPAYERS) VALUES ( 1,18,999,NULL)
INSERT INTO @AGE (BOOKING_PERIOD_ID,MIN_AGE,MAX_AGE,MIN_FULLPAYERS) VALUES ( 2,0,1,NULL)
INSERT INTO @AGE (BOOKING_PERIOD_ID,MIN_AGE,MAX_AGE,MIN_FULLPAYERS) VALUES ( 2,2,12,NULL)
INSERT INTO @AGE (BOOKING_PERIOD_ID,MIN_AGE,MAX_AGE,MIN_FULLPAYERS) VALUES ( 2,13,17,2)
INSERT INTO @AGE (BOOKING_PERIOD_ID,MIN_AGE,MAX_AGE,MIN_FULLPAYERS) VALUES ( 2,18,54,NULL)
INSERT INTO @AGE (BOOKING_PERIOD_ID,MIN_AGE,MAX_AGE,MIN_FULLPAYERS) VALUES ( 2,55,999,NULL)
INSERT INTO @discount (AGE_ID,DISCOUNT) VALUES (1,-60.00)
INSERT INTO @discount (AGE_ID,DISCOUNT) VALUES (2,-60.00)
INSERT INTO @discount (AGE_ID,DISCOUNT) VALUES (3,-60.00)
INSERT INTO @discount (AGE_ID,DISCOUNT) VALUES (4,-60.00)
INSERT INTO @discount (AGE_ID,DISCOUNT) VALUES (5,-80.00)
INSERT INTO @discount (AGE_ID,DISCOUNT) VALUES (6,-80.00)
INSERT INTO @discount (AGE_ID,DISCOUNT) VALUES (7,-80.00)
INSERT INTO @discount (AGE_ID,DISCOUNT) VALUES (8,-80.00)
INSERT INTO @discount (AGE_ID,DISCOUNT) VALUES (9,-80.00)
Above code will result in the following output table:
SELECT
DC.DISCOUNT_ID,
AGE.MIN_AGE,
AGE.MAX_AGE,
AGE.MIN_FULLPAYERS,
BP.[FROM],
BP.TILL,
DC.DISCOUNT
FROM @discount DC
INNER JOIN @AGE AGE ON DC.AGE_ID = AGE.AGE_ID
INNER JOIN @BOOKING_PERIOD BP ON AGE.BOOKING_PERIOD_ID = BP.BOOKING_PERIOD_ID
DISCOUNT_IDMIN_AGEMAX_AGEMIN_FULLPAYERSFROMTILLDISCOUNT
101NULL2011-07-01 00:00:00.0002011-07-31 00:00:00.000-60.0000
2212NULL2011-07-01 00:00:00.0002011-07-31 00:00:00.000-60.0000
31318NULL2011-07-01 00:00:00.0002011-07-31 00:00:00.000-60.0000
418999NULL2011-07-01 00:00:00.0002011-07-31 00:00:00.000-60.0000
501NULL2011-08-01 00:00:00.000NULL-60.0000
6212NULL2011-08-01 00:00:00.000NULL-60.0000
7131722011-08-01 00:00:00.000NULL-80.0000
81854NULL2011-08-01 00:00:00.000NULL-80.0000
955999NULL2011-08-01 00:00:00.000NULL-80.0000
The expected result is that every row that matches on MIN_FULLPAYERS / DISCOUNT / FROM / TILL values, and which are following each other up with MIN_AGE and MAX_AGE are combined into 1 row.
I would like to retrieve the following result set, but do not know how to get there:
DISCOUNT_IDMIN_AGEMAX_AGEMIN_FULLPAYERSFROMTILLDISCOUNT
100999NULL2011-07-01 00:00:00.0002011-07-31 00:00:00.000-60.0000
11012NULL2011-08-01 00:00:00.000NULL-60.0000
7131722011-08-01 00:00:00.000NULL-80.0000
818999NULL2011-08-01 00:00:00.000NULL-80.0000
I'm looking for a solution without the use of cursors.
May 25, 2011 at 9:07 am
In your results, where do the DISCOUNT_ID values of 10/11 come from?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 25, 2011 at 9:07 am
Hi Koen
This could be the solution?
SELECT
SUM(DC.DISCOUNT_ID),
MIN(AGE.MIN_AGE) MIN_AGE,
MAX(AGE.MAX_AGE) MAX_AGE,
AGE.MIN_FULLPAYERS,
BP.[FROM],
BP.TILL,
DC.DISCOUNT
FROM @discount DC
INNER JOIN @AGE AGE ON DC.AGE_ID = AGE.AGE_ID
INNER JOIN @BOOKING_PERIOD BP ON AGE.BOOKING_PERIOD_ID = BP.BOOKING_PERIOD_ID
GROUP BY AGE.MIN_FULLPAYERS, DC.DISCOUNT, BP.[FROM], BP.TILL
Cheers
Chirag
May 25, 2011 at 9:12 am
WayneS (5/25/2011)
In your results, where do the DISCOUNT_ID values of 10/11 come from?
The values 10/11 would be as new result set. (They are not in the original set)
I've made 1 mistake in the orignal post, as the discounts for AGE_ID 5 and 6 should be the same as the rest.
The original post is updated with this information.
The result set is correct when the discounts are different, but when you have a discount of -80 for AGE_ID 5 and 6 the result set is wrong.
May 25, 2011 at 9:19 am
You want these processed "sequentially"? so that for DiscountIDs 5-9, when #7 has the different MIN_FULLPAYERS, it starts the evaluating over again, and then #8-9 are not considered part of #5-6?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 25, 2011 at 9:21 am
WayneS (5/25/2011)
You want these processed "sequentially"? so that for DiscountIDs 5-9, when #7 has the different MIN_FULLPAYERS, it starts the evaluating over again, and then #8-9 are not considered part of #5-6?
Your correct in this example. The problem is that they dont have to be order'd as this, but can be inserted into the temp tables random.
May 25, 2011 at 9:41 am
Ok. Then you would actually want your results to be like this? (Changes in bold)
DISCOUNT_ID MIN_AGE MAX_AGE MIN_FULLPAYERS FROM TILL DISCOUNT
10 0 999 NULL 2011-07-01 00:00:00.000 2011-07-31 00:00:00.000 -60.0000
11 0 12 NULL 2011-08-01 00:00:00.000 NULL -80.0000
7 13 17 2 2011-08-01 00:00:00.000 NULL -80.0000
12 18 999 NULL 2011-08-01 00:00:00.000 NULL -80.0000
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 25, 2011 at 9:53 am
This code produces the results in my prior post.
Comments in the code.
;
WITH cte AS
(
-- Get the difference between the row number (ordered by discount id)
-- and the row number when partitioned by those 4 columns. This gives
-- a "Group" that sequential rows belong to.
SELECT
DC.DISCOUNT_ID,
AGE.MIN_AGE,
AGE.MAX_AGE,
AGE.MIN_FULLPAYERS,
BP.[FROM],
BP.TILL,
DC.DISCOUNT,
Grp = ROW_NUMBER() OVER (ORDER BY DC.DISCOUNT_ID) -
ROW_NUMBER() OVER (PARTITION BY AGE.MIN_FULLPAYERS, DC.DISCOUNT, BP.[FROM], BP.TILL ORDER BY BP.[FROM], BP.TILL)
FROM @discount DC
INNER JOIN @AGE AGE ON DC.AGE_ID = AGE.AGE_ID
INNER JOIN @BOOKING_PERIOD BP ON AGE.BOOKING_PERIOD_ID = BP.BOOKING_PERIOD_ID
), cte2 AS
(
-- get the groupings, plus the count of records in that grouping
SELECT DISCOUNT_ID = MAX(DISCOUNT_ID),
MIN_AGE = MIN(MIN_AGE),
MAX_AGE = MAX(MAX_AGE),
MIN_FULLPAYERS,
[FROM],
TILL,
DISCOUNT,
Grp,
Qty = COUNT(*)
--FROM cte2
FROM cte
GROUP BY Grp, MIN_FULLPAYERS, [FROM], TILL, DISCOUNT
), cte3 (DISCOUNT_ID) AS
(
-- get the max discount id
SELECT MAX(DISCOUNT_ID) FROM cte
), cte4 AS
(
-- Get the values where only one record in the grouping
-- Add the sort column = current discount_id
SELECT DISCOUNT_ID,
MIN_AGE,
MAX_AGE,
MIN_FULLPAYERS,
[FROM],
TILL,
DISCOUNT,
Sort = DISCOUNT_ID
FROM cte2
WHERE Qty = 1
UNION ALL
-- Union it with all the records where > 1 record in grouping.
-- Calculate new discount_id by adding this row number to the max.
-- Add the sort column = current discount_id.
SELECT cte3.DISCOUNT_ID + ROW_NUMBER() OVER (ORDER BY cte2.DISCOUNT_ID),
MIN_AGE,
MAX_AGE,
MIN_FULLPAYERS,
[FROM],
TILL,
DISCOUNT,
Sort = cte2.DISCOUNT_ID
FROM cte2
CROSS JOIN cte3
WHERE Qty > 1
)
SELECT DISCOUNT_ID,
MIN_AGE,
MAX_AGE,
MIN_FULLPAYERS,
[FROM],
TILL,
DISCOUNT
FROM cte4
ORDER BY Sort;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 25, 2011 at 10:34 am
Hi Wayne,
This seems to do exactly what I wanted to reach.
I will perform some more testing tomorrow with real results from the query but it looks ok.
May 25, 2011 at 10:41 am
Koen Reijns (5/25/2011)
Hi Wayne,This seems to do exactly what I wanted to reach.
I will perform some more testing tomorrow with real results from the query but it looks ok.
Great. Do the comments allow you to understand what's going on?
The problem is that they dont have to be order'd as this, but can be inserted into the temp tables random.
Ensure that you test this random insertion stuff also. Perhaps the insert into the #temp table can have an order by?
My original code had the Grp = ROW_NUMBER() OVER (ORDER BY DC.DISCOUNT_ID) -
ROW_NUMBER() OVER (PARTITION BY AGE.MIN_FULLPAYERS, DC.DISCOUNT, BP.[FROM], BP.TILL ORDER BY BP.[FROM], BP.TILL)
as:
Grp = ROW_NUMBER() OVER (ORDER BY BP.[FROM], BP.TILL) -
ROW_NUMBER() OVER (PARTITION BY AGE.MIN_FULLPAYERS, DC.DISCOUNT, BP.[FROM], BP.TILL ORDER BY BP.[FROM], BP.TILL)
That seemed to work also, but test that out.
And, if you don't mind, please follow up here for how it worked out for you. I'm interested, and I'll bet others following up will be also.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 25, 2011 at 2:44 pm
WayneS (5/25/2011)
Great. Do the comments allow you to understand what's going on?
They make it understandeble. After a few tests and disabling some parts I understood the functionality.
In my final query I've removed the sort column from the CTE as this information does not need to be sorted.
Ensure that you test this random insertion stuff also. Perhaps the insert into the #temp table can have an order
by?
My original code had the
Grp = ROW_NUMBER() OVER (ORDER BY DC.DISCOUNT_ID) -ROW_NUMBER() OVER (PARTITION BY AGE.MIN_FULLPAYERS, DC.DISCOUNT, BP.[FROM], BP.TILL ORDER BY BP.[FROM], BP.TILL)
as:
Grp = ROW_NUMBER() OVER (ORDER BY BP.[FROM], BP.TILL) -ROW_NUMBER() OVER (PARTITION BY AGE.MIN_FULLPAYERS, DC.DISCOUNT, BP.[FROM], BP.TILL ORDER BY BP.[FROM], BP.TILL)
That seemed to work also, but test that out.
This did not fully do the trick yet,
I've modified the first ROW_NUMBER to be as following to work in this case and proces the randomly added discounts.
GRP = ROW_NUMBER() OVER (ORDER BY BP.[FROM], BP.TILL, AGE.MIN_AGE, AGE.MAX_AGE, AGE.MIN_FULLPAYERS) -
ROW_NUMBER() OVER (PARTITION BY AGE.MIN_FULLPAYERS, DC.DISCOUNT, BP.[FROM], BP.TILL ORDER BY BP.BOOKING_PERIOD_ID,BP.[FROM], BP.TILL)
May 25, 2011 at 3:40 pm
Sounds like you're ready to roll. Glad you got it working, and very glad that you're understanding what's going on.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply