WeekOfYear -1 SQL HELPP?

Question

select distinct
pos.DivNo 
,WeekOfYear
,[ProductCode]
,sum(Quantity)[Quantity]
into #sales1
from cbis799p.dbo.pos pos
LEFT JOIN DataWarehouse.dbo.Calendar c on pos.PosDate = c.Datetime
Where pos.DivNo = 772 and ProductCode = '1020'
and WeekOfYear = 1
and YearWeek = 2015
group by pos.DivNo 
,WeekOfYear
,[ProductCode]

select * from #sales1
drop table #sales1


select distinct
pos.DivNo 
,Min(WeekOfYear) WeekOfYear
,[ProductCode]
,SUM(quantity) PreQuantity
into #sales2
from cbis799p.dbo.pos pos
LEFT JOIN DataWarehouse.dbo.Calendar c on pos.PosDate = c.Datetime
where pos.DivNo = 772 and ProductCode = '1020'
and WeekOfYear = 1
and YearWeek = 2015
group by pos.DivNo 
,WeekOfYear
,[ProductCode]

select * from #sales2
drop table #sales2

here is my output i need prequantity to come from the week of year - 1 week which would make it the 52nd week of 2014

i need this value to come dynamically so that it can be understood by parameters

@lad2025 thats just going to show me the difference between 2 days — James Chaggar, Aug 21 '15 at 13:00
also the user wants to search on week of year as a parameter — James Chaggar, Aug 21 '15 at 13:00
I don't understand the question. Is there some reason DATEADD() isn't the answer? — Tab Alleman, Aug 21 '15 at 13:47

score 0 · Answer 1 · answered Dec 18 '15 at 03:12

Declare @d Date = '2015-01-01'

    select 
        --Date
        @d
        --WeekYear of Date
        , DatePart(WEEK, @D)
        --Last day of 2014
        , DATEADD(DAY, -1, @D) 
        --Last WeekYear of 2014
        , DatePart(WEEK, DATEADD(DAY, -1, @D))

WeekOfYear -1 SQL HELPP?

1 Answers1