none
T-SQL Date Range MAX Amout Calc

    Question

  • Please run the below Code I have shown what is the final result i need - I can do this by alot of steps - Need to if we can achieve this with one CROSS statement etc.

    Thanks

    DECLARE @Table TABLE  (
    	Cust_ID varchar(10) ,
    	Amount money ,
    	ReportDate date ,
    	ThrityDayDate date,
    	SixtyDayDate DATE ,
    	NinetyDayDate DATE
    	)
    	
    INSERT INTO @table
    (
    	Cust_ID ,
    	Amount  ,
    	ReportDate  ,
    	ThrityDayDate ,
    	SixtyDayDate  ,
    	NinetyDayDate 
    )	
    SELECT '123ERTD', 100 , '04-01-2012','03-01-2012','02-01-2012','01-01-2012'	
    	
    DECLARE @ReportsTable TABLE (
    	Cust_ID varchar(10) ,
    	ReportDate date ,
    	Amount Money 
    	)	
    
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','03-04-2012',100 )
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','03-10-2012',200 ) -- This is the MAX so need to select This value for March
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','03-15-2012',10 )
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','02-03-2012',600 ) -- This is the MAX so need to select this value for Feb
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','02-13-2012',400 )
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','02-25-2012',100 )
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','01-02-2012',10 )
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','01-03-2012',60 )
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','01-23-2012',900) -- Thie is the MAX so need to select this value for Jan
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','01-24-2012',100)
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','01-28-2012',40 )
    
    SELECT * FROM @Table
    SELECT * FROM @ReportsTable
    
    --Final Result:
    SELECT  '123ERTD' AS Cust_ID,
    		'04-01-2012' AS ReportDate ,
    		100/200.00 ThrityDayCoeff , --Amount ReportDate $100/ MAX Amount for that Window between 03-01-2012 and 04-01-2012
    		100/600.00 SixtyDayCoeff ,  --Amount ReportDate $100/ MAX Amount for that Window between 02-01-2012 and 03-01-2012
    		100/900.00 NinetyDayCoeff   --Amount ReportDate $100/ MAX Amount for that Window between 01-01-2012 and 02-01-2012
    		
    /*For Each Customer the Report Date can be different - 
    we have to go back 30,60 and 90 Day from their Report 
    Date and search the MAX amount between them and divide by Amount on that Report Date*/
    		
    		


    Thursday, December 13, 2012 9:39 PM

Answers

  • Here you go. Please note that this includes your Insert statements as well and hence the length, I have posted just the solution in my next post below. As you can see in the solution below, all you need is a Simple Join and a Windowed function:

    DECLARE @Table TABLE  (
    	Cust_ID varchar(10) ,
    	Amount money ,
    	ReportDate date ,
    	ThrityDayDate date,
    	SixtyDayDate DATE ,
    	NinetyDayDate DATE
    	)
    	
    INSERT INTO @table
    (
    	Cust_ID ,
    	Amount  ,
    	ReportDate  ,
    	ThrityDayDate ,
    	SixtyDayDate  ,
    	NinetyDayDate 
    )	
    SELECT '123ERTD', 100 , '04-01-2012','03-01-2012','02-01-2012','01-01-2012'	
    	
    DECLARE @ReportsTable TABLE (
    	Cust_ID varchar(10) ,
    	ReportDate date ,
    	Amount Money 
    	)	
    
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','03-04-2012',100 )
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','03-10-2012',200 ) -- This is the MAX so need to select This value for March
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','03-15-2012',10 )
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','02-03-2012',600 ) -- This is the MAX so need to select this value for Feb
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','02-13-2012',400 )
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','02-25-2012',100 )
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','01-02-2012',10 )
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','01-03-2012',60 )
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','01-23-2012',900) -- Thie is the MAX so need to select this value for Jan
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','01-24-2012',100)
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','01-28-2012',40 )
    
    --SELECT * FROM @Table
    --SELECT * FROM @ReportsTable
    
    --Final Result:
    --SELECT  '123ERTD' AS Cust_ID,
    		--'04-01-2012' AS ReportDate ,
    		--100/200.00 ThrityDayCoeff , --Amount ReportDate $100/ MAX Amount for that Window between 03-01-2012 and 04-01-2012
    		--100/600.00 SixtyDayCoeff ,  --Amount ReportDate $100/ MAX Amount for that Window between 02-01-2012 and 03-01-2012
    		--100/900.00 NinetyDayCoeff   --Amount ReportDate $100/ MAX Amount for that Window between 01-01-2012 and 02-01-2012
    		
    /*For Each Customer the Report Date can be different - 
    we have to go back 30,60 and 90 Day from their Report 
    Date and search the MAX amount between them and divide by Amount on that Report Date*/
    
    ;WITH CTE
    AS
    (
      SELECT R.Cust_ID  ,
    	R.ReportDate As ReportedDate,
    	R.Amount  ,
       T.Amount As RepAmnt, 
      T.ReportDate ,
    	T.ThrityDayDate ,
    	T.SixtyDayDate  ,
    	T.NinetyDayDate  FROM @ReportsTable R
      INNER JOIN @Table T ON T.Cust_ID = R.Cust_ID 
    )
    
    
    SELECT DISTINCT RepAmnt/MAX(CASE WHEN ReportedDate > = ThrityDayDate AND ReportedDate < = ReportDate THEN Amount ELSE 0 END) OVER (PARTITION BY Cust_ID) As ThirtyDayMax ,
    RepAmnt/MAX(CASE WHEN ReportedDate > SixtyDayDate AND ReportedDate < ThrityDayDate THEN Amount ELSE 0 END) OVER (PARTITION BY Cust_ID) As SixtyDayMax,
    RepAmnt/MAX(CASE WHEN ReportedDate > NinetyDayDate AND ReportedDate < SixtyDayDate THEN Amount ELSE 0 END) OVER (PARTITION BY Cust_ID)As NinetyDayMax 
    FROM CTE



    Please mark as answer if this answers your question. Please mark as helpful if you found this post helpful.




    • Edited by SQL Novice 01 Thursday, December 13, 2012 10:48 PM
    • Marked as answer by Sonic.Jessy Thursday, December 13, 2012 11:28 PM
    Thursday, December 13, 2012 10:23 PM

All replies

  • Here you go. Please note that this includes your Insert statements as well and hence the length, I have posted just the solution in my next post below. As you can see in the solution below, all you need is a Simple Join and a Windowed function:

    DECLARE @Table TABLE  (
    	Cust_ID varchar(10) ,
    	Amount money ,
    	ReportDate date ,
    	ThrityDayDate date,
    	SixtyDayDate DATE ,
    	NinetyDayDate DATE
    	)
    	
    INSERT INTO @table
    (
    	Cust_ID ,
    	Amount  ,
    	ReportDate  ,
    	ThrityDayDate ,
    	SixtyDayDate  ,
    	NinetyDayDate 
    )	
    SELECT '123ERTD', 100 , '04-01-2012','03-01-2012','02-01-2012','01-01-2012'	
    	
    DECLARE @ReportsTable TABLE (
    	Cust_ID varchar(10) ,
    	ReportDate date ,
    	Amount Money 
    	)	
    
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','03-04-2012',100 )
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','03-10-2012',200 ) -- This is the MAX so need to select This value for March
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','03-15-2012',10 )
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','02-03-2012',600 ) -- This is the MAX so need to select this value for Feb
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','02-13-2012',400 )
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','02-25-2012',100 )
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','01-02-2012',10 )
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','01-03-2012',60 )
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','01-23-2012',900) -- Thie is the MAX so need to select this value for Jan
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','01-24-2012',100)
    INSERT INTO @ReportsTable (Cust_ID, ReportDate, Amount) VALUES  ('123ERTD','01-28-2012',40 )
    
    --SELECT * FROM @Table
    --SELECT * FROM @ReportsTable
    
    --Final Result:
    --SELECT  '123ERTD' AS Cust_ID,
    		--'04-01-2012' AS ReportDate ,
    		--100/200.00 ThrityDayCoeff , --Amount ReportDate $100/ MAX Amount for that Window between 03-01-2012 and 04-01-2012
    		--100/600.00 SixtyDayCoeff ,  --Amount ReportDate $100/ MAX Amount for that Window between 02-01-2012 and 03-01-2012
    		--100/900.00 NinetyDayCoeff   --Amount ReportDate $100/ MAX Amount for that Window between 01-01-2012 and 02-01-2012
    		
    /*For Each Customer the Report Date can be different - 
    we have to go back 30,60 and 90 Day from their Report 
    Date and search the MAX amount between them and divide by Amount on that Report Date*/
    
    ;WITH CTE
    AS
    (
      SELECT R.Cust_ID  ,
    	R.ReportDate As ReportedDate,
    	R.Amount  ,
       T.Amount As RepAmnt, 
      T.ReportDate ,
    	T.ThrityDayDate ,
    	T.SixtyDayDate  ,
    	T.NinetyDayDate  FROM @ReportsTable R
      INNER JOIN @Table T ON T.Cust_ID = R.Cust_ID 
    )
    
    
    SELECT DISTINCT RepAmnt/MAX(CASE WHEN ReportedDate > = ThrityDayDate AND ReportedDate < = ReportDate THEN Amount ELSE 0 END) OVER (PARTITION BY Cust_ID) As ThirtyDayMax ,
    RepAmnt/MAX(CASE WHEN ReportedDate > SixtyDayDate AND ReportedDate < ThrityDayDate THEN Amount ELSE 0 END) OVER (PARTITION BY Cust_ID) As SixtyDayMax,
    RepAmnt/MAX(CASE WHEN ReportedDate > NinetyDayDate AND ReportedDate < SixtyDayDate THEN Amount ELSE 0 END) OVER (PARTITION BY Cust_ID)As NinetyDayMax 
    FROM CTE



    Please mark as answer if this answers your question. Please mark as helpful if you found this post helpful.




    • Edited by SQL Novice 01 Thursday, December 13, 2012 10:48 PM
    • Marked as answer by Sonic.Jessy Thursday, December 13, 2012 11:28 PM
    Thursday, December 13, 2012 10:23 PM
  • To avoid confusion, I'm posting just the script here i.e., the same solution as mine above minus your insert statements:

    ;WITH CTE
    AS
    (
      SELECT R.Cust_ID  ,
    	R.ReportDate As ReportedDate,
    	R.Amount  ,
       T.Amount As RepAmnt, 
      T.ReportDate ,
    	T.ThrityDayDate ,
    	T.SixtyDayDate  ,
    	T.NinetyDayDate  FROM @ReportsTable R
      INNER JOIN @Table T ON T.Cust_ID = R.Cust_ID 
    )
    
    
    SELECT DISTINCT RepAmnt/MAX(CASE WHEN ReportedDate > = ThrityDayDate AND ReportedDate < = ReportDate THEN Amount ELSE 0 END) OVER (PARTITION BY Cust_ID) As ThirtyDayMax ,
    RepAmnt/MAX(CASE WHEN ReportedDate > SixtyDayDate AND ReportedDate < ThrityDayDate THEN Amount ELSE 0 END) OVER (PARTITION BY Cust_ID) As SixtyDayMax,
    RepAmnt/MAX(CASE WHEN ReportedDate > NinetyDayDate AND ReportedDate < SixtyDayDate THEN Amount ELSE 0 END) OVER (PARTITION BY Cust_ID)As NinetyDayMax 
    FROM CTE

    Please mark as answer if this answers your question. Please mark as helpful if you found this post helpful.



    Thursday, December 13, 2012 10:28 PM
  • ;WITH CTE AS
    (
    SELECT  a.Cust_ID
    		    ,a.Amount
    		   ,a.ReportDate
    FROM @ReportsTable AS a
    INNER JOIN 
    (
    SELECT DISTINCT Cust_Id,MAX(Amount) AS Amt
    FROM @ReportsTable
    GROUP BY Cust_ID,DATEPART(mm,ReportDate)
    ) AS b
    ON a.Cust_ID=b.Cust_ID AND a.Amount=b.Amt
    )
    ,CTE2 AS
    (
    SELECT cte.Cust_ID
    ,cte.ReportDate 
    ,main.ReportDate AS CustReportDate
    ,CASE WHEN cte.ReportDate BETWEEN main.ThrityDayDate AND main.ReportDate THEN (main.Amount/cte.Amount)
    	WHEN cte.ReportDate BETWEEN main.SixtyDayDate AND main.ReportDate THEN (main.Amount/cte.Amount)
    	WHEN cte.ReportDate BETWEEN main.NinetyDayDate AND main.ReportDate THEN (main.Amount/cte.Amount) 
    END AS Result
    FROM CTE AS cte
    INNER JOIN @Table AS main
    ON cte.Cust_ID = main.Cust_ID
    )
    
    SELECT * FROM CTE2

    By Pivoting the above output -

    SELECT  Cust_ID
    			,CustReportDate
    			,[2012-03-10] AS ThrityDayCoeff
    			,[2012-02-03] AS SixtyDayCoeff
    			,[2012-01-23] AS NinetyDayCoeff
    FROM 
    	(SELECT Cust_ID,CustReportDate,ReportDate,Result  FROM 
    		CTE2) AS pv 
    		PIVOT (MAX (pv.Result) FOR pv.ReportDate IN 
    						([2012-03-10] ,[2012-02-03] ,[2012-01-23])  
    				  ) AS abc
    				  

    Output -

    Cust_ID	CustReportDate	ThrityDayCoeff	SixtyDayCoeff	NinetyDayCoeff
    123ERTD	2012-04-01	0.50		0.1666		0.1111

    If you consider the above, you may have to Pivot dynamically the Report Dates.


    Narsimha

    Thursday, December 13, 2012 11:29 PM