SQL Server Dynamic Pivot Query

I am creating a dynamic pivot query that shows the total NetAmount per Week of every Customers within a given date range. The problem is it doesn't ADD ALL the NetAmount within the Week. Here are the data of tblSampleSalesInvoices:

enter image description here

Here is my script.

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'

In this script the SUM of NetAmount will be 10000 only because From the Day of Jan 1, 2016 until Feb 1,2016 only 1 transaction (TR0002) has been made. But when I place Dec 27,2015 and Feb 1,2016 in the parameters. It shows only the NetAmount of TR0001 instead of 25000 which the SUM of TR0001 and TR0002.

enter image description here


ANSWERS:


If you are looking for week numbers relative to a start date and can create # tables then this might suit

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

Please note dates are UK localised



 MORE:


 ? store the result of the a dynamic pivot to a temp table
 ? sql server dynamic pivoting
 ? sql server dynamic pivoting
 ? sql server dynamic pivoting
 ? Pivots with dynamic columns in SQL Server
 ? SQL Server : dynamic pivot over 5 columns
 ? sql server 2008 pivot table
 ? SQL - Combining Dynamic SQL with Pivot and Full Join
 ? SQL - Combining Dynamic SQL with Pivot and Full Join
 ? SQL - Combining Dynamic SQL with Pivot and Full Join