I need help on how to make months convert to columns same as the dates, with the below script i made. It is fine if I am putting only for a duration of a single month, the issue starts when i put different month duration, i need to use order by month.
But i would like to make the months in a horizontal like the dates, instead of vertical.
And how to change null values to zero with the below script?
Any help will be greatly appreciated
please see results for the below script:
http://jmp.sh/v/DPnmcLRNZkUCjvXFbphA[
^]
declare @salefrom datetime declare @saleto datetime
select @salefrom = '2015-10-16 00:00:00' select @saleto = '2015-11-15 23:59:59'
SELECT DATEPART(MM,t.Sale_Date)[MONTH], r.Centre_ID [District], DATEPART(DD,t.Sale_Date)[DAY], COUNT(DISTINCT CASE t.Terminal_Sale WHEN 100000 THEN 0 ELSE t.Terminal_Sale END) [ActivePOS] INTO #TEST FROM Ticket t INNER JOIN Migration_Maindatabase.dbo.Retailer r ON t.Retailer_Sale = r.Retailer_ID WHERE t.Sale_Date BETWEEN @salefrom AND @saleto AND t.status <> 1 AND r.Centre_ID IN (1,2,3) GROUP BY DATEPART(MM,t.Sale_Date), DATEPART(DD,t.Sale_Date),r.Centre_ID ORDER BY DATEPART(MM,t.Sale_Date), DATEPART(DD,t.Sale_Date),r.Centre_ID
select * from #TEST
select * from (select [MONTH], [district], [day], [activePOS]
from #TEST) as s pivot ( max([activePOS]) for [day] 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 pvt
order by [MONTH] asc drop table #TEST
Submit an article or tip