Defining a weekly total - 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)

Defining a weekly total

Author

Topic

eljapo4
Posting Yak Master

100 Posts

Posted - 2011-04-06 : 07:05:56

I have the following piece of code

SELECT
inFromInventory.InvUnitResource		AS Resource,
inFromInventory.InvUnitSite			AS Production_Site,
ROUND(SUM(inFromInventory.PrimQty),5)                                AS UsageRND ,
inFromInventory.UpdateDate

FROM
inFromInventory (NOLOCK)
INNER JOIN fdBasResc (NOLOCK)     ON fdBasResc.ObjectID = inFromInventory.InvUnitRescOID
WHERE
/* Sunday of 16 Weeks ago*/
inFromInventory.UpdateDate               >= 
(
cast(
left(
cast(
getdate()-
datepart(dw,getdate()) as varchar(35)
)
,11) + ' '+cast('00:00:00' as varchar(8)
) as datetime)
) -111
AND inFromInventory.ActyDrvrActyTyp             IN (3,16,17) /*Schedule Consumption, Inventory Adjustments, Cycle Counts*/
AND inFromInventory.InvUnitSite                 IN ('S01','S02','S05','S06','S10','S11','S14','S15') /*Production Sites*/
AND inFromInventory.InvUnitArea                 IN ('RW1','CK1','ER1','PY1','PN1','RTC','SA1','CSM') /*Production Areas*/
AND fdBasResc.CatCodesCode1                     = 'MEAT'
GROUP BY
inFromInventory.InvUnitResource,
inFromInventory.InvUnitSite,
inFromInventory.UpdateDate

the inFromInventory.UpdateDate field contains a date value range of the last 16weeks (will always be the last 16 weeks). What would be the best way for me to further define this field by week ranging from Sun-Sat? My aim is to get a total weekly spend.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-06 : 07:46:48

[code]SELECT inFromInventory.InvUnitResource AS [Resource],
inFromInventory.InvUnitSite AS Production_Site,
ROUND(SUM(inFromInventory.PrimQty), 5) AS UsageRND ,
inFromInventory.UpdateDate,
DATEADD(DAY, (DATEDIFF(DAY, -1, inFromInventory.UpdateDate) / 7) * 7, -1) AS WeekStart
FROM dbo.inFromInventory WITH (NOLOCK)
INNER JOIN dbo.fdBasResc WITH (NOLOCK) ON fdBasResc.ObjectID = inFromInventory.InvUnitRescOID
INNER JOIN (
SELECT 3 AS ActyDrvrActyTyp UNION ALL
SELECT 16 UNION ALL
SELECT 17
) AS x1 ON x1.ActyDrvrActyTyp = inFromInventory.ActyDrvrActyTyp
INNER JOIN (
SELECT 'S01' AS InvUnitSite UNION ALL
SELECT 'S02' UNION ALL
SELECT 'S05' UNION ALL
SELECT 'S06' UNION ALL
SELECT 'S10' UNION ALL
SELECT 'S11' UNION ALL
SELECT 'S14' UNION ALL
SELECT 'S15'
) AS x1 ON x1.InvUnitSite = inFromInventory.InvUnitSite
INNER JOIN (
SELECT 'RW1' AS InvUnitArea UNION ALL
SELECT 'CK1' UNION ALL
SELECT 'ER1' UNION ALL
SELECT 'PY1' UNION ALL
SELECT 'PN1' UNION ALL
SELECT 'RTC' UNION ALL
SELECT 'SA1' UNION ALL
SELECT 'CSM'
) AS x1 ON x1.InvUnitArea = inFromInventory.InvUnitArea
WHERE fdBasResc.CatCodesCode1 = 'MEAT'
AND inFromInventory.UpdateDate >= DATEADD(DAY, (DATEDIFF(DAY, -1, GETDATE()) / 112) * 112, -1)
GROUP BY inFromInventory.InvUnitResource,
inFromInventory.InvUnitSite,
inFromInventory.UpdateDate,
DATEADD(DAY, (DATEDIFF(DAY, -1, inFromInventory.UpdateDate) / 7) * 7, -1)[/code]

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

eljapo4
Posting Yak Master

100 Posts

Posted - 2011-04-06 : 09:48:44

Great Stuff Peso - thanks for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources