Динамический сводный запрос SQL Server

Question

Я создаю динамический сводный запрос, который показывает общее количество NetAmount за неделю для каждого клиента в указанном диапазоне дат. Проблема в том, что он НЕ ДОБАВЛЯЕТ ВСЕ NetAmount в течение недели. Вот данные tblSampleSalesInvoices:

Вот мой сценарий.

CREATE PROCEDURE uspSalesWeeklySummary 
(
    @CustomerId INT,
    @FromDate DATETIME,
    @ToDate DATETIME
)
AS 

SET NOCOUNT ON

DECLARE @Query AS VARCHAR(MAX)
DECLARE @DateStart DATETIME = @FromDate 
DECLARE @tmp TABLE ([Date] VARCHAR(MAX))
DECLARE @Month VARCHAR(MAX)
DECLARE @Day VARCHAR(MAX)
DECLARE @ColumnHeader VARCHAR(MAX)
DECLARE @Headers VARCHAR(MAX)

WHILE DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @DateStart), '19050101') <= DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @ToDate), '19050101')
BEGIN

    SET @month = DATENAME(Month, DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @DateStart), '19050101'))
    SET @day = CAST( DATEPART(DD, DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @DateStart), '19050101')) AS VARCHAR(MAX) ) 

    SET @ColumnHeader = 'Week ' + CAST(DatePart(WEEK,@DateStart) AS VARCHAR(MAX)) + ' - ' + CAST(Year(DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @DateStart), '19050101')) AS VARCHAR(MAX)) + ' - ' + @month + ' - ' + @day

    INSERT INTO @tmp ([Date])
    VALUES (@ColumnHeader)

    SET @DateStart = DATEADD(DD, 7, @DateStart)

END


SELECT @Headers = ISNULL(@Headers + ',','') + QUOTENAME(t.[Date])
FROM @tmp t

SET @Headers = @Headers + ',[Grand Total]'

    SET @Query =
    '
    DECLARE @CustomerId INT = ' + CAST(@CustomerId AS VARCHAR) + '
    DECLARE @FromDate DATETIME = CAST(''' + CAST(@FromDate AS VARCHAR) + ''' AS DATETIME)
    DECLARE @ToDate DATETIME = CAST(''' + CAST(@ToDate AS VARCHAR) + ''' AS DATETIME)
    DECLARE @Headers VARCHAR(MAX) = ''' + CAST(@Headers AS VARCHAR(MAX)) + '''

    SELECT *
    FROM
    (   
    SELECT c.CustomerName AS CustomerName, 
            ''Week '' + CAST(DatePart(WEEK,si.TransactionDate) AS VARCHAR(MAX)) + '' - '' 
            + CAST(Year(DATEADD(WEEK, DATEDIFF(WEEK, ''19050101'', si.TransactionDate), ''19050101'')) AS VARCHAR(MAX)) + '' - '' 
            + CAST(DATENAME(Month, DATEADD(WEEK, DATEDIFF(WEEK, ''19050101'', si.TransactionDate), ''19050101'')) AS VARCHAR(MAX)) + '' - '' 
            + CAST(DATEPART(DD, DATEADD(WEEK, DATEDIFF(WEEK, ''19050101'', si.TransactionDate), ''19050101'')) AS VARCHAR(MAX)) AS Header,
            SUM(si.NetAmount) AS NetAmount
    FROM tblSampleSalesInvoices si
        LEFT OUTER JOIN tblSampleCustomers c ON c.Id = si.CustomerId
    WHERE (si.TransactionDate BETWEEN @FromDate AND DATEADD(WEEK, DATEDIFF(WEEK, ''19050101'', DATEADD(DD, 7 , @ToDate)), ''19050101''))
        AND (si.CustomerId = @CustomerId OR @CustomerId = 0)
    GROUP BY c.CustomerName, si.TransactionDate

    UNION ALL

    SELECT c.CustomerName AS CustomerName, 
            ''Grand Total'' AS Header,
            SUM(si.NetAmount) AS NetAmount
    FROM tblSampleSalesInvoices si
        LEFT OUTER JOIN tblSampleCustomers c ON c.Id = si.CustomerId
    WHERE (si.TransactionDate BETWEEN @FromDate AND DATEADD(WEEK, DATEDIFF(WEEK, ''19050101'', DATEADD(DD, 7 , @ToDate)), ''19050101''))
        AND (si.CustomerId = @CustomerId OR @CustomerId = 0)
    GROUP BY c.CustomerName

    ) AS BaseData
    PIVOT
    (   
        SUM(NetAmount)
        FOR Header IN (' + @Headers + ') 
    ) AS Pivoting'


EXEC (@Query)


GO

EXEC uspSalesWeeklySummary 0,'01/01/2016','02/01/2016'

В этом сценарии сумма NetAmount будет равна 10000 только потому, что со дня 1 января 2016 года по 1 февраля 2016 года была сделана только 1 транзакция (TR0002). Но когда я помещаю в параметры параметры 27 декабря 2015 года и 1 февраля 2016 года. Он показывает только NetAmount TR0001 вместо 25000, который является суммой TR0001 и TR0002.

Answer 1

Если вы ищете номера недель относительно даты начала и можете создать # таблицы, тогда это может подойти

CREATE PROCEDURE uspSalesWeeklySummary 
(
    @CustomerId INT,
    @FromDate DATETIME,
    @ToDate DATETIME
)
AS 
SET NOCOUNT ON
set datefirst 1
/*
create table tblSampleSalesInvoices (id int,transactionNo int,customerid int,TransactionDate date,netamount int)
insert into  tblSampleSalesInvoices values
(1,1,1,'2015-12-29',15000),
(2,2,1,'2016-01-01',15000),
(3,3,2,'2016-03-01',15000),
(4,4,3,'2016-04-01',15000),
(5,5,4,'2016-06-01',15000),
(6,6,1,'2016-09-01',15000),
(7,7,2,'2016-10-01',15000),
(8,8,3,'2016-12-01',15000),
(9,9,4,'2017-01-01',15000),
(10,10,1,'2017-04-01',15000),
(11,11,2,'2017-07-01',15000),
(12,12,3,'2017-10-01',15000),
(13,13,4,'2017-12-01',15000)
*/
--declare    @CustomerId INT    = 1
--declare    @FromDate DATETIME = '2015-12-27'
--declare    @ToDate DATETIME   = '2016-02-01'

DECLARE @Query AS VARCHAR(MAX)
DECLARE @DateStart DATETIME = @FromDate 

IF OBJECT_ID(N'tempdb..#Tempdates') IS NOT NULL
BEGIN
    DROP TABLE #Tempdates
END
declare @id         int = 0
create  table #tempdates (id int,[lodate] date, [hidate] date ,yyyy varchar(4), mm varchar(max), dd int,txt varchar(max))

DECLARE @Month VARCHAR(MAX)
DECLARE @Day VARCHAR(MAX)
DECLARE @ColumnHeader VARCHAR(MAX)
DECLARE @Headers VARCHAR(MAX)


WHILE DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @DateStart), '19050101') <= DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @ToDate), '19050101')
BEGIN
    set datefirst 1
    SET @month = DATENAME(Month, DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @DateStart), '19050101'))
    SET @day = CAST( DATEPART(DD, DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @DateStart), '19050101')) AS VARCHAR(MAX) ) 

    SET @ColumnHeader = 'Week ' + CAST(DatePart(WEEK,@DateStart) AS VARCHAR(MAX)) + ' - ' + 
            CAST(Year(DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @DateStart), '19050101')) AS VARCHAR(MAX)) + ' - ' + @month + ' - ' + @day

    set @id = @id + 1

    insert into #tempdates (id,[lodate],[hidate],yyyy,mm,dd,txt)
    values (@id,@datestart,dateadd(dd,6,@datestart),
    year(@datestart),
    datename(month,@datestart),day(@datestart), 
    'Week ' + cast(@id as varchar(2)) + ' - ' + cast(datename(month,@datestart) as varchar(max)) + ' - ' + cast(day(@datestart) as varchar(max))
    )

    SET @DateStart = DATEADD(DD, 7, @DateStart)

END
update #tempdates 
    set hidate = @todate where id = @id

SELECT @Headers = ISNULL(@Headers + ',','') + QUOTENAME(t.[txt])
FROM #tempdates t

SET @Headers = @Headers + ',[Grand Total]'

--select @headers headers
--select * from #tempdates

set @query = 
'

 DECLARE @CustomerId INT = ' + CAST(@CustomerId AS VARCHAR) + '
    DECLARE @FromDate DATETIME = CAST(''' + CAST(@FromDate AS VARCHAR) + ''' AS DATETIME)
    DECLARE @ToDate DATETIME = CAST(''' + CAST(@ToDate AS VARCHAR) + ''' AS DATETIME)
    DECLARE @Headers VARCHAR(MAX) = ''' + CAST(@Headers AS VARCHAR(MAX)) + '''  
 SELECT *
    FROM
    (   
    SELECT  si.customerid, 
            t.txt as header,
            SUM(si.NetAmount) AS NetAmount
    FROM tblsamplesalesinvoices si
    left join   #tempdates  t on si.Transactiondate between lodate and hidate
    --LEFT OUTER JOIN tblSampleCustomers c ON c.Id = si.CustomerId
    WHERE si.TransactionDate BETWEEN @FromDate AND DATEADD(WEEK, DATEDIFF(WEEK, ''19050101'', DATEADD(DD, 7 , @ToDate)), ''19050101'')
        AND (si.CustomerId = @CustomerId OR @CustomerId = 0)
    GROUP BY -- c.CustomerName, 
            si.customerid
            ,
            t.txt

    UNION ALL

    SELECT si.CustomerId,
            ''Grand Total'' AS Header,
            SUM(si.NetAmount) AS NetAmount
    FROM tblsamplesalesinvoices si
    -- LEFT OUTER JOIN tblSampleCustomers c ON c.Id = si.CustomerId
    WHERE (si.TransactionDate BETWEEN @FromDate AND DATEADD(WEEK, DATEDIFF(WEEK, ''19050101'', DATEADD(DD, 7 , @ToDate)), ''19050101''))
       AND (si.CustomerId = @CustomerId OR @CustomerId = 0)
    GROUP BY si.customerid

) s

 PIVOT
    (   
        SUM(NetAmount)
        FOR Header IN (' + @Headers + ') 
    ) AS Pivoting'

--select @query
exec (@query)
IF OBJECT_ID(N'tempdb..#Tempdates') IS NOT NULL
BEGIN
    DROP TABLE #Tempdates
END

go

Обратите внимание, даты в Великобритании локализованы