Rolling Totals. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Rolling Totals.

Author

Topic

Llycas
Starting Member

4 Posts

Posted - 2010-05-17 : 04:58:13

Hi, not sure if this is the correct forum for my post, if not I appologise!

Anyway, i'll cut to the chase. What I am trying to do is create a rolling total for installs and warrentys purchased relating to those installs.

I'm trying to get a count of the previous 2 years worth of sales + installs for each month and then I can work out an % of a team's performance.

My code;

-- obtain count of installations for each month
SELECT DISTINCT DATEPART(yyyy, SaleOrderHeader.[DATE OF DELIVERY]) AS InstalledYear
, DATEPART(mm, SaleOrderHeader.[DATE OF DELIVERY]) AS InstalledMonth
, COUNT(SaleOrderHeader.[DATE OF DELIVERY]) AS Installed
INTO #tmp_InstallationsByMonth
FROM Customers
INNER JOIN SaleOrderHeader
ON Customers.[Sale Number] = SaleOrderHeader.[Customer Number]
INNER JOIN SaleOrderDetails
ON SaleOrderHeader.[Sale Number] = SaleOrderDetails.[Sale Number]
WHERE SaleOrderDetails.[MAKE REQUIRED] = 'Acorn'
AND SaleOrderHeader.[DATE OF DELIVERY] >= '1996-04-01'
AND Customers.[Sale Number] > 10 -- eliminate test records
GROUP BY DATEPART(yyyy, SaleOrderHeader.[DATE OF DELIVERY])
, DATEPART(mm, SaleOrderHeader.[DATE OF DELIVERY])
ORDER BY DATEPART(yyyy, SaleOrderHeader.[DATE OF DELIVERY]) DESC
, DATEPART(mm, SaleOrderHeader.[DATE OF DELIVERY]) DESC

--SELECT * FROM #tmp_InstallationsByMonth

-- obtain monthly Acorn installations
SELECT DISTINCT Customers.[Sale Number]
, DATEPART(yyyy, SaleOrderHeader.[DATE OF DELIVERY]) AS InstalledYear
, DATEPART(mm, SaleOrderHeader.[DATE OF DELIVERY]) AS InstalledMonth
, SaleOrderHeader.[DATE OF DELIVERY] AS Installed
INTO #tmp_CustomerInstallations
FROM Customers
INNER JOIN SaleOrderHeader
ON Customers.[Sale Number] = SaleOrderHeader.[Customer Number]
INNER JOIN SaleOrderDetails
ON SaleOrderHeader.[Sale Number] = SaleOrderDetails.[Sale Number]
WHERE SaleOrderDetails.[MAKE REQUIRED] = 'Acorn'
AND SaleOrderHeader.[DATE OF DELIVERY] >= '1996-04-01'
AND Customers.[Sale Number] > 10 -- eliminate test records
GROUP BY Customers.[Sale Number]
, DATEPART(yyyy, SaleOrderHeader.[DATE OF DELIVERY])
, DATEPART(mm, SaleOrderHeader.[DATE OF DELIVERY])
, SaleOrderHeader.[DATE OF DELIVERY]
ORDER BY DATEPART(yyyy, SaleOrderHeader.[DATE OF DELIVERY]) DESC
, DATEPART(mm, SaleOrderHeader.[DATE OF DELIVERY]) DESC, Customers.[Sale Number]

--SELECT * FROM #tmp_CustomerInstallations

-- Calculate days after installation since warranty purchased
SELECT #tmp_CustomerInstallations.InstalledYear
, #tmp_CustomerInstallations.InstalledMonth
, #tmp_CustomerInstallations.[Sale Number] AS CustNo
, CASE
WHEN CustomerServiceContracts.[Purchase Date] > #tmp_CustomerInstallations.Installed
AND CustomerServiceContracts.[Purchase Date] < (#tmp_CustomerInstallations.Installed + 90) THEN 1
ELSE 0
END AS ContractWithin90Days
, CASE
WHEN CustomerServiceContracts.[Purchase Date] > (#tmp_CustomerInstallations.Installed + 90)
AND CustomerServiceContracts.[Purchase Date] < (#tmp_CustomerInstallations.Installed + 180) THEN 1
ELSE 0
END AS ContractWithin180Days
, CASE
WHEN CustomerServiceContracts.[Purchase Date] > (#tmp_CustomerInstallations.Installed + 180)
AND CustomerServiceContracts.[Purchase Date] < (#tmp_CustomerInstallations.Installed + 365) THEN 1
ELSE 0
END AS ContractWithin365Days
INTO #tmp_ContractsPurchasedWithinXMonths
FROM #tmp_CustomerInstallations
LEFT OUTER JOIN CustomerServiceContracts
ON CustomerServiceContracts.[Sale Number] = #tmp_CustomerInstallations.[Sale Number]

-- Group and count results
SELECT #tmp_ContractsPurchasedWithinXMonths.InstalledYear
, #tmp_ContractsPurchasedWithinXMonths.InstalledMonth
, #tmp_InstallationsByMonth.Installed
, SUM(ContractWithin90Days) AS PurchasedIn3Months
, SUM(ContractWithin180Days) AS PurchasedIn6Months
, SUM(ContractWithin365Days) AS PurchasedIn365Year
, SUM(ContractWithin90Days + ContractWithin180Days + ContractWithin365Days) AS Total
FROM #tmp_ContractsPurchasedWithinXMonths
INNER JOIN #tmp_InstallationsByMonth
ON #tmp_InstallationsByMonth.InstalledYear = #tmp_ContractsPurchasedWithinXMonths.InstalledYear
AND #tmp_InstallationsByMonth.InstalledMonth = #tmp_ContractsPurchasedWithinXMonths.InstalledMonth
GROUP BY #tmp_ContractsPurchasedWithinXMonths.InstalledYear, #tmp_ContractsPurchasedWithinXMonths.InstalledMonth, #tmp_InstallationsByMonth.Installed
ORDER BY #tmp_ContractsPurchasedWithinXMonths.InstalledYear DESC, #tmp_ContractsPurchasedWithinXMonths.InstalledMonth DESC

DROP TABLE #tmp_InstallationsByMonth
DROP TABLE #tmp_CustomerInstallations
DROP TABLE #tmp_ContractsPurchasedWithinXMonths

---------------------------------------------------------------------

I've had many idea's but so far all have been unsuccessful.

Thanks in advance!

Llycas

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-17 : 07:26:05

This is what I use for ROlling Averages, from Peso. Perhaps you can adapt it to what you need.

Jim

DECLARE @period int
SET @period = -5

DECLARE @Sample TABLE
(
theMonth DATETIME,
theValue INT
)

INSERT @Sample
SELECT '2008-01-01', 45 UNION ALL
SELECT '2008-02-01', 32 UNION ALL
SELECT '2008-03-01', 12 UNION ALL
SELECT '2008-04-01', 16 UNION ALL
SELECT '2008-05-01', 25 UNION ALL
SELECT '2008-06-01', 98 UNION ALL
SELECT '2008-07-01', 87 UNION ALL
SELECT '2008-09-01', 56 UNION ALL
SELECT '2008-10-01', 47 UNION ALL
SELECT '2008-11-01', 35 UNION ALL
SELECT '2008-12-01', 61

SELECT s.theMonth,
s.theValue,
f.a
FROM @Sample AS s
CROSS APPLY (
SELECT AVG(1.0E * x.theValue)
FROM @Sample AS x
WHERE x.theMonth BETWEEN DATEADD(MONTH, @period, s.theMonth) AND s.theMonth
) AS f(a)
ORDER BY s.theMonth

Everyday I learn something that somebody else already knew

Llycas
Starting Member

4 Posts

Posted - 2010-05-17 : 07:48:48

Thanks Jim, not an immediate help however you have given me a few ideas!

Anyway, this may help you see exactly what my problem is.

SELECT
DATEPART(mm, SaleOrderHeader.[DATE OF DELIVERY]) AS InstalledMonth
,DATEPART(yyyy, SaleOrderHeader.[DATE OF DELIVERY]) AS InstalledYear
,COUNT(SaleOrderHeader.[DATE OF DELIVERY]) AS Installed

,(SELECT SUM(COUNT(SaleOrderHeader.[DATE OF DELIVERY])) FROM SaleOrderHeader AS i
WHERE DATEDIFF(Month, InstalledMonth , i.InstalledMonth) BETWEEN 1 and 12)
AS RollingPurchasedTotal

FROM Customers
JOIN SaleOrderHeader
ON Customers.[Sale Number] = SaleOrderHeader.[Customer Number]
INNER JOIN SaleOrderDetails
ON SaleOrderHeader.[Sale Number] = SaleOrderDetails.[Sale Number]
WHERE SaleOrderDetails.[MAKE REQUIRED] = 'Acorn'
AND Customers.[Sale Number] > 10
AND SaleOrderHeader.[DATE OF DELIVERY] >= '1996-04-01'
GROUP BY DATEPART(mm, SaleOrderHeader.[DATE OF DELIVERY])
,DATEPART(yyyy, SaleOrderHeader.[DATE OF DELIVERY])
ORDER BY InstalledYear DESC
,InstalledMonth DESC

As you can see, it is the select sub-query that I am having difficulties with. As I know the alias' will not work as in "InstallMonth" and "i.InstallMonth", however, I also "Cannot perform an aggregate function on an expression containing an aggregate or a subquery." As in using "DATEPART(mm, SaleOrderHeader.[DATE OF DELIVERY]) AS InstalledMonth" instead of.

I'm not SQL guru so please excuse my attempt at making things clearer if I have not done :-P.

Thanks,

Llycas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-17 : 12:07:39

please explain with some sample data what you need as output. thats better than posting the query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources