Sai Reddy

Sai Reddy

  • 1.6k
  • 180
  • 8.1k

Case with Date column

Nov 5 2018 1:13 AM
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
 

Answers (7)