t-sql help - 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)

t-sql help

Author

Topic

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2009-10-15 : 21:59:27

I want TO calculate FOR a given product the SUM OF ALL the payments FOR the LAST 12 months OF March, 2009(i.e SUM OF payments FROM march,2008 TO Feb, 2009, which IS trailing 12 months).
I will be passing parameters: MONTH, YEAR, productID

Please let me know. Thanks!

Below IS the data:

--DROP TABLE #Temp1
CREATE TABLE #Temp1
(ProdID VARCHAR(20),
[Month] VARCHAR(20),
[Year] VARCHAR(5),
Payment MONEY)

INSERT INTO #Temp1
SELECT 'prod1', 1,2008,100.00
UNION
SELECT 'prod2', 1,2008,100.00
UNION
SELECT 'prod3', 1,2008,100.00
UNION
SELECT 'prod1', 2,2008,100.00
UNION
SELECT 'prod2', 2,2008,100.00
UNION
SELECT 'prod3', 2,2008,100.00
UNION
SELECT 'prod1',3,2008,100.00
UNION
SELECT 'prod1',4,2008,100.00
UNION
SELECT 'prod1',5,2008,100.00
UNION
SELECT 'prod1',9,2008,100.00
UNION
SELECT 'prod1',11,2008,100.00
UNION
SELECT 'prod1',12,2008,100.00
UNION
SELECT 'prod1',1,2009,100.00
UNION
SELECT 'prod1',2,2009,100.00
UNION
SELECT 'prod1',3,2009,100.00

SELECT * FROM #Temp1

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-10-16 : 01:36:25

Declare
@MONTH int,
@YEAR int,
@productID varchar(100)

Set @MONTH=3
Set @YEAR=2009
Set @productID='prod1'

SELECT SUM(Payment) AS Payment
FROM #TEMP1
WHERE
CAST(str([MONTH])+'/'+'01'+'/'+str([YEAR])AS datetime) BETWEEN DATEADD(M,-12,CAST(str(@MONTH)+'/'+'01'+'/'+str(@YEAR)AS datetime)) AND CAST(str(@MONTH)+'/'+'01'+'/'+str(@YEAR)AS datetime) AND
prodId=@productID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-16 : 02:39:18

[code]DECLARE @Sample TABLE(ProdID VARCHAR(20), [Month] VARCHAR(20), [Year] VARCHAR(5), Payment MONEY)

INSERT @Sample
SELECT 'prod1', 1, 2008, 100.00 UNION ALL
SELECT 'prod2', 1, 2008, 100.00 UNION ALL
SELECT 'prod3', 1, 2008, 100.00 UNION ALL
SELECT 'prod1', 2, 2008, 100.00 UNION ALL
SELECT 'prod2', 2, 2008, 100.00 UNION ALL
SELECT 'prod3', 2, 2008, 100.00 UNION ALL
SELECT 'prod1', 3, 2008, 100.00 UNION ALL
SELECT 'prod1', 4, 2008, 100.00 UNION ALL
SELECT 'prod1', 5, 2008, 100.00 UNION ALL
SELECT 'prod1', 9, 2008, 100.00 UNION ALL
SELECT 'prod1', 11, 2008, 100.00 UNION ALL
SELECT 'prod1', 12, 2008, 100.00 UNION ALL
SELECT 'prod1', 1, 2009, 100.00 UNION ALL
SELECT 'prod1', 2, 2009, 100.00 UNION ALL
SELECT 'prod1', 3, 2009, 100.00

SELECT SUM(Payment)
FROM @Sample
WHERE 12 * [Year] + [Month] > 12 * DATEPART(YEAR, GETDATE()) + DATEPART(MONTH, GETDATE()) - 12[/code]

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-16 : 03:59:47

Peso, shouldn't it be this?

SELECT SUM(Payment)
FROM @Sample
WHERE 12 * [Year] + [Month] between 12 * 2008 + 3 and 12 * 2009 + 2

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources