1

I have two tables:

Ticket table:

sale_date, center_id, ticket_id

Center table:

center name, center_id

I need help on how to make the results look like the one attached image:

enter image description here

I have tried to run the script, re arranging does not give me luck.

declare @salefrom      datetime
declare @saleto        datetime

select @salefrom = '2015-10-01 00:00:00'
select @saleto   = '2015-10-31 23:59:59'

SELECT
    DATEPART(MM, t.Sale_Date) [MONTH], 
    DATEPART(DD, t.Sale_Date) [DAY], 
    c.Center_name [centers], 
    COUNT(ticket_id) AS [Tickets]
FROM 
    Ticket t 
INNER JOIN 
    Center c ON c.center_id = t.center_id
WHERE
    t.Sale_Date BETWEEN @salefrom AND @saleto 
    AND t.status <> 1 
GROUP BY 
    DATEPART(MM, t.Sale_Date), DATEPART(DD, t.Sale_Date), c.Center_name
ORDER BY
    DATEPART(MM, t.Sale_Date), DATEPART(DD, t.Sale_Date), c.Center_name

Thanks, lemi

Results i am getting from running the above script

Lemi
  • 33
  • 7
  • You need grouped counting and pivot, but please provide sample data. Best would be a working [Fiddle](http://www.sqlfiddle.com). – Shnugo Dec 13 '15 at 23:24
  • You can not span or merge column as month row you want. But get in all columns. Only option I though is union the results with three section – Ajay2707 Dec 14 '15 at 05:41
  • can you post dome data. – Ajay2707 Dec 14 '15 at 05:48
  • Hi, I was able to produce the result that i need using pivot, by first creating temp table, then from there run the pivot script. But the issue is, how to convert the month row to column, like from horizontal to vertical without affecting day to day results. any help will be appreciated. – Lemi Dec 17 '15 at 21:54

0 Answers0