July 28, 2013 at 12:31 pm
can some give me hint i am trying to get a statement that will perform a daily sum aggregation on the field cost. It must only use costs that are associated with the follow items: Z001, Z002. The cost must also only come from division
this what I have tried :
DECLARE @dailycost date
SELECT @dailycost = GETDATE()
if @dailycost = @dailycost
SELECT SUM(Cost)
FROM dbo.Cost
where Item = 'ZOO1'
July 28, 2013 at 1:42 pm
engstevo (7/28/2013)
can some give me hint i am trying to get a statement that will perform a daily sum aggregation on the field cost. It must only use costs that are associated with the follow items: Z001, Z002. The cost must also only come from divisionthis what I have tried :
DECLARE @dailycost date
SELECT @dailycost = GETDATE()
if @dailycost = @dailycost
SELECT SUM(Cost)
FROM dbo.Cost
where Item = 'ZOO1'
DECLARE @dailycost DATE
SET @dailycost = GETDATE()
IF DATEDIFF(day,c.dailycost,@dailycost) = 0
BEGIN
SELECT SUM(c.Cost)
FROM dbo.Cost AS c
WHERE c.Item = 'ZOO1'
END
But perhaps better is to get rid of the need for an IF statement altogether:
SELECT
SUM(c.Cost) OVER (PARTITION BY c.DailyCost) AS DailySum
FROM
dbo.Cost AS c
WHERE
c.Item = 'ZOO1'
AND c.DailyCost = @dailycost
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply