CODEWITHSUNDEEP
sqlsql-serverpivot
Ion
Ion

Reputation: 559

pivot solds by month

I have a problem trying to do a sales pivot per month and by 30-minute intervals, THIS IS MY RESULT:

enter image description here

As you can see this is not correct, since it repeats the sales of the whole year in each of the months.

I will put my code below, hormod is a time type field and fecalb is a date type field , 

    select concat(DATEPART(HOUR, hormod),
case when(DATEPART(MINUTE, hormod) / 30)=0 then ':00' else ':30'end) Hour,
count([January])[Jan],count([February])[Feb],
count([March])[Mar],count([April])[Apr] 
from 
(
    select hormod, fecalb,codalb, datename(month,fecalb) mes
    from balbara WHERE year(fecalb)=2017) as z
    pivot(count(codalb) for mes in ([January],[February],[March],[April])
)as pivotable
group by DATEPART(HOUR, hormod),(DATEPART(MINUTE,hormod) / 30)

Here I have not placed every month to make them more readable

EDIT I use SQL Fiddle with data: http://sqlfiddle.com/#!18/66430/1

Upvotes: 0

Views: 53

Answers (1)

Shushil Bohara
Shushil Bohara

Reputation: 5656

You should query as below, I have compared minute in the condition if it's fine then use it otherwise you can change the logic according to your requirement:

SELECT 
    CONCAT(hormodh, ':', CASE WHEN hormodm >= 30 THEN '30' ELSE '00' END) Hour,
    SUM([January])[Jan],
    SUM([February])[Feb],
    SUM([March])[Mar],
    SUM([April])[Apr]
FROM 
(
    SELECT 
        DATEPART(HOUR, hormod) AS hormodh, 
        DATEPART(MINUTE, hormod) AS hormodm, 
        --fecalb, not required
        codalb, 
        DATENAME(MONTH,fecalb) mes
    FROM balbara 
    WHERE YEAR(fecalb)=2017) AS z
    PIVOT(COUNT(codalb) FOR mes IN ([January],[February],[March],[April])
)AS pivotable
GROUP BY CONCAT(hormodh, ':', CASE WHEN hormodm >= 30 THEN '30' ELSE '00' END)

Upvotes: 1

Related Questions