What is the correct way to do date math in this TSQL Stored Procedure?

Question

Here is my business case: I need to, based on the user's input of date values for "BegDate" and "EndDate" also determine two other dates. Specifically, I need to know:

Week1Begin (which is the value they enter for "BegDate")
Week2End (which is the value they enter for "EndDate")
Week1End (which is the value they enter for "BegDate" + 6 days)
Week2Begin (which is the value they enter for "BegDate" + 7 days)

IOW, if the user enters these values:

BegDate = 12/27/2015
EndDate = 1/6/2016

...the values for the others need to be calculated as:

Week1End = 1/2/2016
Week2Begin = 1/3/2016

I have a Stored Procedure which executes but returns all Nulls and 0s in the Summed columns, probably because the date math hasn't been included in the SP. Here is the SP as it is now:

CREATE PROCEDURE [dbo].[duckbilledPlatypi]
    @Unit varchar(25),
    @BegDate datetime,
    @EndDate datetime
AS

DECLARE 
@Week1End datetime,
@Week2Begin datetime

Select Ind.Description,
    @BegDate  BegDate,
    @Week1End Week1End,
    @Week1End Week2Begin,
    @EndDate EndDate,
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.QtyShipped ELSE 0 END) 
Week1Usage,
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.QtyShipped ELSE 0 END) 
Week2Usage,
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) 
Week1Price,
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Price ELSE 0 END) -
   SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.QtyShipped ELSE 0 END) 
UsageVariance,
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.QtyShipped ELSE 0 END) 
 -
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) 
PriceVariance,
    (SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.QtyShipped ELSE 0 
END)  -
    SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) )
    / 
NULLIF(SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END),0) 
PercentageOfPriceVariance
    From    InvoiceDetail Ind
    Where   Ind.Unit = @Unit
    AND Ind.InvoiceDate BETWEEN @BegDate AND @EndDate
    Group By Ind.Description

What do I need to add to perform the needed date math? Something like this:

. . .
Select Ind.Description,
    @BegDate  BegDate,
    DATE_ADD(BegDate,INTERVAL 6 DAY) Week1End,
    DATE_ADD(BegDate,INTERVAL 7 DAY)  Week2Begin,
    @EndDate EndDate,
. . .

or this:

. . .
DECLARE 
@Week1End datetime = DATE_ADD(BegDate,INTERVAL 6 DAY),
@Week2Begin datetime = DATE_ADD(BegDate,INTERVAL 7 DAY)
. . .

...or???

UPDATE

Okay, this:

DECLARE @Week1End datetime = DATEADD(Day, 6, @BegDate),
@Week2Begin datetime = DATEADD(Day, 7, @BegDate);

...or this:

DECLARE @Week1End datetime = DATEADD(Day, 6, @BegDate),
@Week2Begin datetime = DATEADD(Day, 7, @BegDate)

...or this:

DECLARE @Week1End datetime = DATEADD(Day, 6, @BegDate);
DECLARE @Week2Begin datetime = DATEADD(Day, 7, @BegDate);

...is now the (sub)question.

UPDATE 2

It works this way:

DECLARE 
@Week1End datetime = DATEADD(Day, 6, @BegDate),
@Week2Begin datetime = DATEADD(Day, 7, @BegDate);

[TSQL Date and Time functions](https://msdn.microsoft.com/en-us/library/ms186724.aspx). Specifically [DATEADD](https://msdn.microsoft.com/en-us/library/ms186819.aspx). The question is too trivial to answer to someone with nearly 10k rep. — TT., Jan 13 '16 at 19:59
I have DateAdd in my question; my question is where to use it. The two cats below helped me with that. Most of my points are from C#, certainly not TSQL. — B. Clay Shannon-B. Crow Raven, Jan 13 '16 at 20:00
And your second proposal (or this) is the way to go. Declare your weekbegin, weekend variables and initialize them. Eg `DECLARE @Week1End DATETIME = DATEADD(DAY,6,@BegDate);` etc. — TT., Jan 13 '16 at 20:05
Cool - at the keyboards of N cats, the matter is firmly established - just to be clear, though - a ";" at the end rather than a "," when there are two such declarations? — B. Clay Shannon-B. Crow Raven, Jan 13 '16 at 21:06
Question of style, I prefer one declaration per line, and each SQL statement seperated by `;`. — TT., Jan 14 '16 at 05:50

score 2 · Accepted Answer · answered Jan 13 '16 at 19:44

2

Do you want to just add dates?

DECLARE @BeginDatePlus6 datetime = DATEADD(dd, 6, begDate)

answered Jan 13 '16 at 19:44

Shantanu Gupta

20,688
54
182
286

It looks like I need to hear from a third TSQL expert; one cat says one thing, the other another... – B. Clay Shannon-B. Crow Raven Jan 13 '16 at 19:47
@B.ClayShannon I am expecting something different. However your requirements seems similar. – Shantanu Gupta Jan 13 '16 at 19:49

score 2 · Answer 2 · answered Jan 13 '16 at 19:45

2

I think you are looking for something like this:

.
.
Select Ind.Description,
    @BegDate  BegDate,
    DATEADD(day, 6,@BegDate)  Week1End,
    DATEADD(day, 7,@BegDate)  Week2Begin, 
    @EndDate EndDate,

.
.

answered Jan 13 '16 at 19:45

Fuzzy

3,810
2
15
33

It looks like I need to hear from a third TSQL expert; one cat says one thing, the other another... – B. Clay Shannon-B. Crow Raven Jan 13 '16 at 19:47
1

The answer which Shantanu Gupta gave is also the same as what I'm suggesting just a different approach. – Fuzzy Jan 13 '16 at 19:50
I don't doubt your syntax, I'm just wondering where the date math/manipulation needs to go - where you indicate (in the Select statement) or where the other cat indicates (in the var declarations). Both were ideas I had, and I don't know which one to prefer. – B. Clay Shannon-B. Crow Raven Jan 13 '16 at 19:50
1

Its a matter of choice. But I would have it the way the other cat has it as this reduces performance overheads within the select statement. – Fuzzy Jan 13 '16 at 19:51

What is the correct way to do date math in this TSQL Stored Procedure?

UPDATE

UPDATE 2

2 Answers2