Case with Date column

Question

Hi, I need to generate the report daily with date wise, I have written it with a case statement, How to avoid case statement to minimize the query because I need to add complete dates of a month. Please help me to write it in dynamic way. CREATE TABLE Survey ( City VARCHAR(20), Date Date, SaleAmount INT ) SELECT City, Sum(case when CONVERT(VARCHAR(10),Date,126)='2018-11-05' THEN SaleAmount else 0 end) as '2018-11-05', Sum(case when CONVERT(VARCHAR(10),Date,126)='2018-11-04' THEN SaleAmount else 0 end) as '2018-11-04', Sum(case when CONVERT(VARCHAR(10),Date,126)='2018-11-03' THEN SaleAmount else 0 end) as '2018-11-03' FROM Survey group by City

Suraj Kumar · Answer

You can try the below query- CREATE TABLE #CUSTOMER ( CUSTOMERID INT , CUSTOMERNAME NVARCHAR(100), SaleAmount INT , JoinedDate Date ) INSERT INTO #CUSTOMER VALUES (100, 'CUSTOMER1' ,100, '2018-11-01' ), (400, 'CUSTOMER1' ,150, '2018-11-01' ), (200, 'CUSTOMER2' ,200, '2018-11-02' ), (300, 'CUSTOMER3' ,300, '2018-11-03' ) declare @ month int , @ year int set @ month = 11 set @ year = 2018 SELECT * INTO #DatesTable FROM ( SELECT Convert ( Varchar (10), CAST ( CAST (@ year AS VARCHAR ) + '-' + CAST (@ Month AS VARCHAR ) + '-01' AS DATETIME) + Number,121) AS MonthDate FROM master..spt_values WHERE type = 'P' AND ( CAST ( CAST (@ year AS VARCHAR ) + '-' + CAST (@ Month AS VARCHAR ) + '-01' AS DATETIME) + Number ) DATEADD(mm,1, CAST ( CAST (@ year AS VARCHAR ) + '-' + CAST (@ Month AS VARCHAR ) + '-01' AS DATETIME) ) )tempDt select * into #finalData From ( select #DatesTable.MonthDate, SUM ( ISNULL (SaleAmount,0)) AS SaleAmount from #DatesTable LEFT JOIN #CUSTOMER ON #DatesTable.MonthDate = #CUSTOMER.JoinedDate group by #DatesTable.MonthDate )tempFinal DECLARE @cols AS NVARCHAR( MAX ), @query AS NVARCHAR( MAX ); SET @cols = STUFF(( SELECT distinct ',' + QUOTENAME(c.MonthDate) FROM #finalData c FOR XML PATH( '' ), TYPE ).value( '.' , 'NVARCHAR(MAX)' ) ,1,1, '' ) set @query = 'SELECT ' + @cols + ' from ( select MonthDate , SaleAmount from #finalData ) x pivot ( max (SaleAmount) for MonthDate in ( ' + @cols + ' ) ) p ' execute (@query) DROP TABLE #finalData DROP TABLE #CUSTOMER DROP TABLE #DatesTable The output will be as shown in attachment. Hope this will help you.

Dorababu Meka · Answer

Check this query DECLARE @CUSTOMER TABLE ( CUSTOMERID INT , CUSTOMERNAME NVARCHAR(100), SaleAmount INT , JoinedDate Date ) INSERT INTO @CUSTOMER VALUES (100, 'CUSTOMER1' ,100, '2018-11-01' ), (400, 'CUSTOMER1' ,150, '2018-11-01' ), (200, 'CUSTOMER2' ,200, '2018-11-02' ), (300, 'CUSTOMER3' ,300, '2018-11-03' ) --SELECT * FROM @CUSTOMER select SUM (SaleAmount),JoinedDate from @CUSTOMER group by JoinedDate If you want date to be formatted use this DECLARE @CUSTOMER TABLE ( CUSTOMERID INT , CUSTOMERNAME NVARCHAR(100), SaleAmount INT , JoinedDate Date ) INSERT INTO @CUSTOMER VALUES (100, 'CUSTOMER1' ,100, '2018-11-01' ), (400, 'CUSTOMER1' ,150, '2018-11-01' ), (200, 'CUSTOMER2' ,200, '2018-11-02' ), (500, 'CUSTOMER2' ,200, '2018-11-02' ), (300, 'CUSTOMER3' ,300, '2018-11-03' ) --SELECT * FROM @CUSTOMER select SUM (SaleAmount) DaiySale, CONVERT ( VARCHAR (10),JoinedDate,126) SaleDate from @CUSTOMER group by JoinedDate https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=61fca2050dac19774cf894c36d61cb02

Sai Reddy · Answer

Hi Suraj, Is it possible in MySQL also? Pivot does not work in mysql.

Sai Reddy · Answer

Hi Dorababu, In your query dates are as row wise, but i need them as column wise.

Dorababu Meka · Answer

Have you tried the code I posted?

Sai Reddy · Answer

Hi Suraj, I need every date as a column name as follws. Customerid,Name,SaleAmount, NOV1,NOV2,NOV3.....TILL NOV30

Suraj Kumar · Answer

If your requirement do not fulfill then I request you to provide the sample data and required output in attachment so it will be easy and better to understand and provide the easies way to provide the solution to anyone. Hope this will help you.