Получить значения из поля для первого и последнего дня недели

Мне нужно извлечь значения из поля первого и последнего дня недели. В основном мне нужно показывать состояние юнитов в начале и в конце недели. Неделя определяется с понедельника по воскресенье, таблица, из которой я должен извлечь это, выглядит следующим образом:

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

В моей таблице есть запись на дату (в поле "Дата" фактически указывается дата и время), показывающая снимок инвентаря на эту конкретную дату. Учитывая, что 4/3 - понедельник, а 4/9 - воскресенье, мне нужно иметь количество для 4/3 и 4/9 для всех номеров моего продукта, поэтому результаты должны быть:

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

Мне нужно сделать это для всех апреля и мая 2017 года для более чем 100 номеров продуктов через запрос в SQL Server. Можете ли вы помочь мне, как это сделать?

Спасибо!

Источник

2 ответа 

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

Производит следующий вывод:

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

Изменить, чтобы ответить на последующий вопрос в комментариях.

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
Источник

В идеале у вас есть календарь в виде фиксированной таблицы в вашей базе данных, но вы можете сгенерировать его, как я делаю в моем примере. Приведенный ниже запрос не является окончательным результатом, но он извлекает все данные по всем воскресеньям / понедельникам, которые у вас есть между "20170401", "20170601" (исключая первое июня)

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;
Источник
Другие вопросы по тегам