SQL - pick current date from machine and compare

Question

For the below query Scenario 1: Current Year (Previous Week) - For eg- Week 31

sum(case when s.Date between '2016-07-17' and '2016-07-23' then s.SELLINC else 0 end) ActualSales 

Scenario 2: Last Year (Previous Week) - For eg- Week 31

sum(case when s.Date between '2015-07-19' and '2015-07-25' then s.SELLINC else 0 end) LastYrVarianc 

Scenario 3: Picking dates between beginning of current year till today's date

sum(case when s.Date between '2016-01-01' and '2016-09-05' then s.SELLINC else 0 end) YrToDateActual 

Scenario 4: Picking dates between beginning of last year till last year today's date

sum(case when s.Date between '2015-01-01' AND '2015-09-05' then s.SELLINC else 0 end) LastYrToDateActual

Instead of hard coding the date. I would like to pick current date from machine and compare.

Week start from Sunday and ends Saturday. Any help please?

Answer 1

First off, GETDATE() is the SQL Server function for today's date

DATEADD(..) is the function to add stuff to dates

1) case when s.date between DATEADD(dd,-6,getdate()) and getdate()) then...

2) case when s.date between DATEADD(yy,-1,DATEADD(dd,-6,getdate())) and DATEADD(yy,-1,getdate()) then ...

3) case when s.date between DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) and getdate() then ...

4) case when s.date between dateadd(yy,-1,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) and DATEADD(yy,-1,GETDATE()) then ..

A little help from here

Answer 2

I assume that your week starts from sunday to saturday.

You can use this query,

Scenario-1 (PreviousWeekStartDate and PreviousWeekEndDate)

s.Date between convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) - 1) and convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) + 5)

Scenario-2 (LastYearPreviousWeekStartDate and LastYearPreviousWeekEndDate)

s.Date between convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) - 1) and convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5)

Scenario-3 (StartOfYear and CurrentDate)

s.Date between convert(date,DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)) and convert(date,getdate())

Scenario-4 (StartOfLastYear and CurrentDateLastYear)

s.Date between convert(date,DATEADD(yy, DATEDIFF(yy, 0, dateadd(YEAR, - 1, getdate())), 0)) and convert(date,dateadd(YEAR, - 1, getdate()))

Answer 3

Use date arithmetic

declare @weekNo int = 31; 
--start of the year
declare @ys datetime = dateadd(year,datediff(year,0,getdate()),0) 
-- start of the first week of the year (may start in December of prev year)
declare @y1ws datetime = dateadd(week,datediff(week,0,@ys),0) 

select @ys, @y1ws, dateadd(week, @weekNo-1, @y1ws) [week31 start], dateadd(week, @weekNo, @y1ws) [week32 start]
--    use it this way for week 31
-- .. where somedate >= [week31 start] and somedate < [week32 start]

Answer 4

SELECT
    GETDATE(),                                          -- Today
    DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0),     -- Start of this year
    DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0), -- Start of last year
    DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0),     -- Start of this week
    DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()) - 1, 0)  -- Start of last week

Answer 5

I think using DATEPART (Transact-SQL) is a more portable solution. My answers for scenario 1 and scenario 2 refer a natural week starting from Sunday, which are different from JohnHC's answers (referring last 7 days).

--
-- Scenario 1: Current Year (Previous Week) - For eg- Week 31
--
CASE WHEN
    DATEPART(ww, s.Date) = DATEPART(ww, GETDATE()) - 1 AND
    DATEPART(yy, s.Date) = DATEPART(yy, GETDATE())
THEN ...

--
-- Scenario 2: Last Year (Previous Week) - For eg- Week 31
--
CASE WHEN
    DATEPART(ww, s.Date) = DATEPART(ww, GETDATE()) - 1 AND
    DATEPART(yy, s.Date) = DATEPART(yy, GETDATE()) - 1
THEN ...

--
-- Scenario 3: Picking dates between beginning of current year till today's date
--
CASE WHEN
    DATEPART(yy, s.Date) = DATEPART(yy, GETDATE())
THEN ...

--
-- Scenario 4: Picking dates between beginning of last year till last year today's date
--
CASE WHEN
    DATEPART(yy, s.Date) >= DATEPART(yy, GETDATE()) - 1
THEN ...