previous & next month quantity, cost recovery

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)

previous & next month quantity, cost recovery

Author

Topic

palak
Yak Posting Veteran

55 Posts

Posted - 2009-03-18 : 09:24:21

i have one table named - QuantCRtable in which itemnmbr, itemtitle,date,quantity and unitprce columns are there.

for Cost recovery - it will be quantity*unitprce.

Now, i have to calculate quantity and CR(Cost Recovery) for previous month(Before 30 days of that date) and next month(Next 30 days of that date) for each itemnmbrs.

example: -
itemnmbr, itemtitle,date, Previous Month Q, Pre CR, Next Q, Next CR
--------------------------------------------------------------------
07-2725 Buy HHHHH 3/17/2009 252.35 21.00 123.52 5.00

so here - Previous Month Q, Pre CR will be before 30days of date - before 30 days of 3/17 - means from 2/17 - 3/16 one month Quantity and Cost Recovery and
Next Q, Next CR will be next 30days after date - means from 3/17-4/16 one month Quantity and Cost Recovery.

can anyone tell me what will be the query for this in case statement for those 4 columns?

CREATE function [dbo].[QuantCRtemp]
(@startdate datetime,
@enddate datetime)
returns table
As
return
(
Select t.Itemnmbr, t.ItemTitle, t.Date, [Previous Month Quantity], [Previous Month CR], [Next Month Quantity], [Next Month CR] from (
select

vs.itemnmbr,
vs.ItemTitle,
vs.date,
case when vs.date then calculate previous month sum(vs.quantity) as ][Previous Month Quantity]
From QuantCRtable vs

group by vs.itemnmbr, vs.date, vs.ItemTitle, vs.quantity, vs.unitprce

) as t

group by t.Itemnmbr, t.ItemTitle, t.Date

)

If anyone can help me? Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-18 : 09:48:33

[code]SELECT qcr.ItemNmbr,
qcr.ItemTitle,
qcr.[Date],
pm.Q AS [Previous Month Q],
pm.CR AS [Previous Month CR],
nm.Q AS [Next Month Q],
nm.CR AS [Next Month CR]
FROM (
SELECT ItemNmbr,
ItemTitle,
[Date]
FROM QuantCRtable
GROUP BY ItemNmbr,
ItemTitle,
[Date]
) AS qcr
CROSS APPLY (
SELECT SUM(x.Quantity * q.UnitPrce) AS CR,
SUM(x.Quantity) AS Q
FROM QuantCRtable AS x
WHERE x.ItemNmbr = qcr.ItemNmbr
AND x.ItemTitle = qcr.ItemTitle
AND x.[Date] BETWEEN DATEADD(DAY, -30, qcr.[Date]) AND DATEADD(DAY, -1, qcr.[Date])
) AS pm
CROSS APPLY (
SELECT SUM(x.Quantity * q.UnitPrce) AS CR,
SUM(x.Quantity) AS Q
FROM QuantCRtable AS x
WHERE x.ItemNmbr = qcr.ItemNmbr
AND x.ItemTitle = qcr.ItemTitle
AND x.[Date] BETWEEN qcr.[Date] AND DATEADD(DAY, 30, qcr.[Date])
) AS nm[/code]

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