Take values from field for first and last day of week

Question

I need to extract the values from a field from the first and last day of week. Basically I need to show a status of units at the beginning and at the end of the week. Week is determined from monday to sunday, the table I need to extract this from is as follows:

Product number  Quantity    Date
1               15          4/3/2017 00:00:00.0000000
1               20          4/4/2017 00:00:00.0000000
1               20          4/5/2017 00:00:00.0000000
1               20          4/6/2017 00:00:00.0000000
1               25          4/7/2017 00:00:00.0000000
1               32          4/8/2017 00:00:00.0000000
1               37          4/9/2017 00:00:00.0000000
2               5           4/3/2017 00:00:00.0000000
2               10          4/4/2017 00:00:00.0000000
2               11          4/5/2017 00:00:00.0000000
2               12          4/6/2017 00:00:00.0000000
2               14          4/7/2017 00:00:00.0000000
2               15          4/8/2017 00:00:00.0000000
2               20          4/9/2017 00:00:00.0000000

In my table, I have an entry per date (the Date field is actually datetime) showing an snapshot of the inventory for that specific date. Taking in mind that 4/3 is monday and 4/9 is sunday, I need to have the quantity for the 4/3 and the 4/9 for all my product numbers, so the results should be:

Week              Product number    Starting inventory    Ending Inventory
From 4/3 to 4/9   1                 15                    37
From 4/3 to 4/9   2                 5                     20

I need to do this for all april and may 2017 for more than 100 product numbers through a query in sql server. Can you help me out on how to do it?

Thanks!

What do you want as the result if there is no data on sunday? The saturday data or null? — sepupic, May 16 '17 at 13:47
Surely the closing stock is the stock as at 00:00 on Monday, not 00:00 on Sunday? e.g. 4/10/17 00:00, as the value on 4/09/17 00:00 is the value at the START of Sunday, not the end of Sunday? — JeffUK, May 16 '17 at 13:57
Hi JeffUK, following this example, stock should be shown from 4/3 00:00:00 to 4/9 11:59:59. thanks — nomury, May 16 '17 at 14:04

Mazhar · Answer 1 · 2017-05-17T10:17:15.200

DECLARE @Product TABLE( Product INT, Quanity INT, Date DATETIME2)
SET DATEFORMAT MDY

INSERT INTO @Product
 ( Product , Quanity , Date )
VALUES
(1,15,'4/3/2017 00:00:00.0000000')
,(1, 20,'4/4/2017 00:00:00.0000000')
,(1, 20,'4/5/2017 00:00:00.0000000')
,(1, 20,'4/6/2017 00:00:00.0000000')
,(1, 25,'4/7/2017 00:00:00.0000000')
,(1, 32,'4/8/2017 00:00:00.0000000')
,(1, 37,'4/9/2017 00:00:00.0000000')
,(2, 5, '4/3/2017 00:00:00.0000000')
,(2, 10,'4/4/2017 00:00:00.0000000')
,(2, 11,'4/5/2017 00:00:00.0000000')
,(2, 12,'4/6/2017 00:00:00.0000000')
,(2, 14,'4/7/2017 00:00:00.0000000')
,(2, 15,'4/8/2017 00:00:00.0000000')
,(2, 20,'4/9/2017 00:00:00.0000000')

SET DATEFIRST 1 --to specify that monday is the first day of the week

;WITH cteX
AS(
    SELECT
         DATEADD(WEEK, DATEDIFF(DAY, 0, [date])/7, 0) AS StartWeek
        ,DATEADD(WEEK, DATEDIFF(DAY, 0, [date])/7, 6) AS EndWeek
        ,*
    FROM @Product
)
SELECT 
    'From ' + LEFT(CONVERT(VARCHAR(20), X.StartWeek,101 ), 5) 
        + ' to ' + LEFT(CONVERT(VARCHAR(20), X.EndWeek,101 ), 5) 'Week'
    ,  X.Product    'Product Number'
    , MAX( CASE WHEN CAST(X.StartWeek AS DATE) = CAST([X].[Date] AS DATE)
        THEN X.Quanity END  )'Starting Inventory'
    ,MAX( CASE WHEN CAST(X.EndWeek AS DATE) = CAST([X].[Date] AS DATE)
        THEN X.Quanity END  )'Ending Inventory'
FROM
    cteX X
GROUP BY
    X.Product,X.StartWeek, X.EndWeek

Produces following output:

Week                Product Number  Starting Inventory  Ending Inventory
From 04/03 to 04/09 1               15                  37 
From 04/03 to 04/09 2               5                   20

Edit to answer follow up question in comments.

SELECT 
    'From ' + LEFT(CONVERT(VARCHAR(20), X.StartWeek,101 ), 5) 
        + ' to ' + LEFT(CONVERT(VARCHAR(20), X.EndWeek,101 ), 5) 'Week'
    ,  X.Product    'Product Number'
    , MAX( CASE WHEN CAST(X.StartWeek AS DATE) = CAST([X].[Date] AS DATE)
        THEN X.Quanity - T.Quantity END  )'Starting Inventory'
    ,MAX( CASE WHEN CAST(X.EndWeek AS DATE) = CAST([X].[Date] AS DATE)
        THEN X.Quanity - T.Quantity END  )'Ending Inventory'
FROM
    cteX X
INNER JOIN
    dbo.OtherTable T ON T.Product = X.Product AND T.[date] BETWEEN X.StartWeek AND X.EndWeek
GROUP BY
    X.Product,X.StartWeek, X.EndWeek

Thanks so much for the help guys!!! It's really helpful! If I need to add a calculation to the query, for example substract the quantity of products from another table to the quantity I'm getting here and keeping in mind that the table is almost identical and that I'll be joining by product number, how can I do it? I'm not sure how I can determine and join the weeks between the two tables. Thanks again — nomury, May 16 '17 at 15:11

score 0 · Answer 2 · answered May 16 '17 at 14:11

Ideally you have a calendar as fixed table in your db, but you can generate it as I do in my example. The query below is not the final result but it does extract all data on all sundays/mondaysthat you have between '20170401', '20170601'(first of june excluded)

declare @t table (prod int,  q int,    dt date);
insert into @t values
(1,              15         ,'4/3/2017 00:00:00.0000000'),
(1,              20         ,'4/4/2017 00:00:00.0000000'),
(1,              20         ,'4/5/2017 00:00:00.0000000'),
(1,              20         ,'4/6/2017 00:00:00.0000000'),
(1,              25         ,'4/7/2017 00:00:00.0000000'),
(1,              32         ,'4/8/2017 00:00:00.0000000'),
(1,              37         ,'4/9/2017 00:00:00.0000000'),
(2,             5          ,'4/3/2017 00:00:00.0000000'),
(2,             10         ,'4/4/2017 00:00:00.0000000'),
(2,             11         ,'4/5/2017 00:00:00.0000000'),
(2,             12         ,'4/6/2017 00:00:00.0000000'),
(2,             14         ,'4/7/2017 00:00:00.0000000'),
(2,             15         ,'4/8/2017 00:00:00.0000000'),
(2,             20         ,'4/9/2017 00:00:00.0000000');


with nums as
(
select number as n
from master..spt_values
where type = 'p'
and number < datediff(day, '20170401', '20170601')
),

calendar as
(
select dateadd(day, n, '20170401') as dt, 
       datename(dw,dateadd(day, n, '20170401'))  as dw
from nums
where datename(dw,dateadd(day, n, '20170401')) in('sunday', 'monday')
)

select t.*,
       c.dw
from @t t
     join calendar c
        on t.dt = c.dt;

Take values from field for first and last day of week

2 Answers2