Case with Date column

Sai Reddy

7y1.1k

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

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Suraj Kumar7y ago
Accepted 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.
0
Sai Reddy7y ago
Hi Suraj,

Is it possible in MySQL also?

Pivot does not work in mysql.
0
Sai Reddy7y ago
Hi Dorababu,

In your query dates are as row wise, but i need them as column wise.
0
Dorababu Meka7y ago
Have you tried the code I posted?
0
Sai Reddy7y ago
Hi Suraj, I need every date as a column name as follws.

Customerid,Name,SaleAmount, NOV1,NOV2,NOV3.....TILL NOV30
0
Suraj Kumar7y ago
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.
0
Dorababu Meka7y ago
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
+1