T-SQL Date Range MAX Amout Calc

Thursday, December 13, 2012 9:39 PM

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*/

All Replies

Thursday, December 13, 2012 10:23 PM

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:33 PM
Edited by SQL Novice 01 Thursday, December 13, 2012 10:34 PM
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:28 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.

Edited by SQL Novice 01 Thursday, December 13, 2012 10:35 PM
Edited by SQL Novice 01 Thursday, December 13, 2012 10:47 PM

Thursday, December 13, 2012 11:29 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