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
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?
Thanks for giving us some good sample data and the expected results, it made it so much easier for us.
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]
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.
I will try to tweak my solution according to the OP requirements, but it will be a bit hard to focus after your solution. :)
Good catch, I was seeing double last night. Should be fixed now :)
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.
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.
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 */
@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 :)
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.
@Phil Factor I've done that in a real world app and was surprised when the debit exactly matched the credit--doh!
