question

siera_gld avatar image
siera_gld asked

First Date in Sequence

I am looking to come up with a query of the first date when an item is on a special program. The special program is controlled by having a non zero amount in the columns called rec_prc and spcl_prc. Items are coming off and on this all of the time so the trick for me is to figure out the min(prc_eff_dt) the Last time the item was on the program. CREATE TABLE #temp( [ITEM_NUM] [char](7) NULL, [PRC_EFF_DT] [date] NULL, [PRC_END_DT] [date] NULL, [FOB_COST] [decimal](13, 2) NULL, [REC_PRC] [decimal](13, 2) NULL, [SPCL_PRC] [decimal](13, 2) NULL) `Insert into #Temp values('1165802','9/20/2001','10/31/2001',58,27,40) Insert into #Temp values('1165802','11/1/2001','3/3/2002',58,27,40) Insert into #Temp values('1165802','3/4/2002','3/18/2002',58,27,40) Insert into #Temp values('1165802','3/19/2002','3/28/2002',58,25,40) Insert into #Temp values('1165802','3/29/2002','9/26/2002',58,25,32) Insert into #Temp values('1165802','9/27/2002','1/16/2003',58,25,31) Insert into #Temp values('1165802','1/17/2003','4/7/2003',58,25,29) Insert into #Temp values('1165802','4/2/2003','12/31/2099',58,25,30) Insert into #Temp values('1165802','4/8/2003','6/9/2003',58,37,42) Insert into #Temp values('1165802','6/10/2003','7/8/2003',58,37,44) Insert into #Temp values('1165802','7/9/2003','9/25/2003',58,37,44) Insert into #Temp values('1165802','9/26/2003','9/26/2003',0,0,0) Insert into #Temp values('1165802','8/27/2004','10/22/2004',58,37,44) Insert into #Temp values('1165802','10/23/2004','3/31/2005',58,33,44) Insert into #Temp values('1165802','4/1/2005','9/15/2005',58,33,44) Insert into #Temp values('1165802','9/16/2005','6/14/2006',58,33,42) Insert into #Temp values('1165802','6/15/2006','7/13/2006',58,33,39) Insert into #Temp values('1165802','7/14/2006','3/22/2010',58,33,36) Insert into #Temp values('1165802','3/23/2010','2/18/2011',58,33,36) Insert into #Temp values('1165802','2/19/2011','12/31/2099',58,0,0) Insert into #Temp values('1166263','7/10/2009','1/4/2010',43,0,0) Insert into #Temp values('1166263','1/5/2010','1/11/2010',43,14,35) Insert into #Temp values('1166263','1/12/2010','1/27/2010',43,14,16) Insert into #Temp values('1166263','1/28/2010','1/28/2010',0,0,0) Insert into #Temp values('1166263','1/30/2010','4/19/2011',43,14,16) Insert into #Temp values('1166263','4/20/2011','4/21/2011',43,14,16) Insert into #Temp values('1166263','4/22/2011','4/22/2011',0,0,0) Insert into #Temp values('2130680','6/12/2009','6/17/2010',24,12,15) Insert into #Temp values('2130680','6/18/2010','11/10/2010',24,11,15) Insert into #Temp values('2130680','11/11/2010','12/31/2099',24,12,17)` So For instance - 1165802 - would be 2004-08-27 - comes on and off program many times 1166263 - 2010-01-30 - comes on and off program many times 2130680 - 2009-06-12 - only on program one time
t-sqlrownumfirst
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Please add the desired output against your sample data to get better assistance. Otherwise, it is quite confusing. e.g. For 1165802 and group "37-44", 6/10/2003 is the minimum date rather than 2004-08-27. Do you need the maximum date?
1 Like 1 ·
Thanks for giving us some good sample data and the expected results, it made it so much easier for us.
1 Like 1 ·
Scot Hauder avatar image
Scot Hauder answered
SELECT d.[ITEM_NUM], COALESCE(e.[PRC_EFF_DT], d.[PRC_EFF_DT]) FROM (SELECT [ITEM_NUM], MIN([PRC_EFF_DT])[PRC_EFF_DT] FROM #temp GROUP BY [ITEM_NUM])d LEFT JOIN (SELECT [ITEM_NUM], MAX(x.[PRC_EFF_DT])[PRC_EFF_DT] FROM #temp t1 CROSS APPLY (SELECT MIN([PRC_EFF_DT])[PRC_EFF_DT] FROM #temp t2 WHERE t2.[ITEM_NUM] = t1.[ITEM_NUM] AND t2.PRC_EFF_DT > t1.PRC_EFF_DT)x WHERE [SPCL_PRC] = 0 AND [REC_PRC] = 0 GROUP BY [ITEM_NUM])e ON (d.[ITEM_NUM] = e.[ITEM_NUM]) ORDER BY d.[ITEM_NUM]
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Nice one. Really liked the approach. One thing missed may be is, the group is reset when [SPCL_PRC] = 0 as well as [REC_PRC] = 0.
0 Likes 0 ·
I will try to tweak my solution according to the OP requirements, but it will be a bit hard to focus after your solution. :)
0 Likes 0 ·
Good catch, I was seeing double last night. Should be fixed now :)
0 Likes 0 ·
I really like you guys sharing real world GOTHCHA's to watch out for.....THANKS
0 Likes 0 ·
Usman Butt avatar image
Usman Butt answered
Since, you did not provide the desired output, I assume you needed the maximum date. Please see the comments in the script, and you can change it easily, if you need minimum dates instead EDIT: OK, I get it. I think this is what required by you. ; WITH CTE AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY T.[ITEM_NUM], T.[REC_PRC], T.[SPCL_PRC] ORDER BY T.[PRC_EFF_DT] DESC ) ROWID , -- CHANGE "DESC" TO "ASC" IF YOU NEED MIN DATE ROW_NUMBER() OVER ( PARTITION BY T.[ITEM_NUM], T.[REC_PRC] ORDER BY T.[SPCL_PRC] DESC, T.[PRC_EFF_DT] DESC ) ROWID1 -- CHANGE "DESC" TO "ASC" IF YOU NEED MIN DATE , T.* FROM [#temp] AS T INNER JOIN ( SELECT [ITEM_NUM] , MAX([REC_PRC]) [REC_PRC] FROM [#temp] AS T WHERE [REC_PRC] > 0 AND [SPCL_PRC] > 0 --COMMENT THIS LINE IF THIS IS NOT THE REQUIREMENT GROUP BY [ITEM_NUM] ) Filtered ON [T].[ITEM_NUM] = [Filtered].[ITEM_NUM] AND [T].[REC_PRC] = [Filtered].[REC_PRC] WHERE T.[REC_PRC] > 0 AND T.[SPCL_PRC] > 0 --COMMENT THIS LINE IF THIS IS NOT THE REQUIREMENT ) SELECT [ITEM_NUM] , [PRC_EFF_DT] , [PRC_END_DT] , [FOB_COST] , [REC_PRC] , [SPCL_PRC] FROM [CTE] WHERE [ROWID] = 1 AND [ROWID1] = 1 If this is what is required, please let me know as I beleive there would be room for improvement.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Chopped one unnecessary iteration
0 Likes 0 ·
Thanks Usman. The example items I showed have the expected results. You are choosing the Min date - it's not what I need. I need the min prc eff date when there is not a previous 0 price for rec_prc and 0 spcl_prc so you can use these items for good examples (1165802,1166263 ,2130680) - they all have 0 prices for dates which i need to ignore - I only need the last time it went on spcl program.
0 Likes 0 ·
Phil Factor avatar image
Phil Factor answered
SELECT #temp.item_num, COALESCE(max(StartDate),MIN(PRC_EFF_DT)) AS [On Program] FROM #temp LEFT OUTER JOIN (SELECT t.Item_num, MIN(PRC_EFF_DT) AS startDate FROM #temp t INNER join (SELECT Item_num, PRC_EFF_DT AS start FROM #temp WHERE rec_prc + spcl_prc=0)f ON f.Item_num =t.Item_num AND PRC_EFF_DT> start GROUP BY t.Item_num, start) CameOffAndOn ON CameOffAndOn.Item_num=#temp.Item_num GROUP BY #temp.item_num /* item_num On Program -------- ---------- 1165802 2004-08-27 1166263 2010-01-30 2130680 2009-06-12 */
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Phil Factor I think you wrote it in a single breath. Otherwise, I am sure you would have not used the aggregation "rec_prc + spcl_prc=0" in the WHERE clause :)
0 Likes 0 ·
Well spotted. It was sheer mischief. I was relying on positive-only values! Yes, it should have been rec_prc =0 and spcl_prc=0 to be clearer.
0 Likes 0 ·
Besides, it seems the SARGability would have been lost as well.
0 Likes 0 ·
@Phil Factor I've done that in a real world app and was surprised when the debit exactly matched the credit--doh!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.