How to do a PIVOT by day cycle in 31 days in month in SQL Server

Question

I have this table:

CREATE TABLE [dbo].[tblProducts]
(
    [CATEGORY_ID] [nvarchar](50) NULL,
    [PRODUCT] [nvarchar](50) NULL,
    [PRODUCTION_DATE] [datetime] NULL
) ON [PRIMARY]

I need to display total products of every day in month

Days should be pivoted

CATE   1 2 3 4 5 6 7.....30 (or 31)  -->days of month
CATE1  1 5                           --> count product by cate and day
CATE2  1 9
CATE3  5 10

Please note that PRODUCTION date is from 6.00 am of the current date

to 5.59 a.m of the next date

That's my difficulty.

Please help me.

Answer 1

Here is the basic pivot query:

Declare @month int = 1
; With data as (
    Select [CATEGORY_ID], [PRODUCT], [PRODUCTION_DATE] = DATEPART(DAY, DATEADD(HOUR, -6, [PRODUCTION_DATE]))
    , [PRODUCT_YEAR] = DATEPART(YEAR, [PRODUCTION_DATE]), [PRODUCTION_MONTH] = DATEPART(MONTH, [PRODUCTION_DATE]) From [dbo].[tblProducts]
    --Where DATEPART(MONTH, DATEADD(HOUR, -6,[PRODUCTION_DATE])) = @month update if monthly/yearly query is needed
)
Select [CATEGORY_ID], [PRODUCTION_MONTH], [PRODUCTION_YEAR], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31]
From data
Pivot (
    Count([PRODUCT])
    For [PRODUCTION_DATE] In
    ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
    ) as piv
    Order By [CATEGORY_ID], [PRODUCTION_YEAR], [PRODUCTION_MONTH]

I basically offset everything by 6 hours (-6). 20150101 06:00 becomes midnight 20150101 00:00 and 5:59 become 23;59 on the previous day.

PIVOT: https://technet.microsoft.com/fr-fr/library/ms177410%28v=SQL.105%29.aspx