Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ryankeast
Starting Member
9 Posts |
 Posted - 2014-09-18 : 10:31:11
|
Hi there, I have the following code which lists out all the void properties for the financial year. What I want to achieve is have multiple COUNTS based on the query below which will show me columns - such as Total Number of Voids in April, Total Number Of Voids in May and so on. How do you achieve that with the below code?SELECT DISTINCT TOP 100 PERCENT HIST.[PLACE-REF] AS 'Place Referance' ,PLA.[address1] AS 'Address Line1' ,HIST.[START-DATE] AS 'Void Start Date' ,HIST.[END-DATE] AS 'Void End Date' ,HIST.[END-DATE] + 1 AS 'Start Of Tenancy' ,LOC.[mgt-area] AS 'Managment Area' ,LOC.[scheme] AS 'Scheme' ,LOC.[location-sts] AS 'Location Status' ,LOC.[location-type] AS 'Location Type' ,DATEPART(MM, HIST.[START-DATE]) AS 'Void Start Date MONTH' ,DATEPART(YYYY, HIST.[START-DATE]) AS 'Void Start Date YEAR' FROM [dbo].[IH_IH-LOCATION-HIST] AS HIST INNER JOIN [dbo].[IH_IH-LOCATION] AS LOC ON HIST.[PLACE-REF] = LOC.[place-ref] INNER JOIN DBO.[CORE_CO-PLACE] AS PLA ON HIST.[PLACE-REF] = PLA.[place-ref] WHERE HIST.[LOCATION-STS] = 'V' AND HIST.[START-DATE] BETWEEN CONVERT(DATETIME, '2014-04-01 00:00:00', 102) AND CONVERT(DATETIME, '2015-03-31 00:00:00', 102) AND LOC.[location-type] <> 'D' AND LOC.[location-type] <> 'Garage' ORDER BY [Place Referance] |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2014-09-18 : 12:21:20
|
[code],sum(case when DATEPART(MM, HIST.[START-DATE]) = 1 then 1 else 0 end) as CountJan,sum(case when DATEPART(MM, HIST.[START-DATE]) = 1 then 1 else 0 end) as CountJan,sum(case when DATEPART(MM, HIST.[START-DATE]) = 2 then 1 else 0 end) as CountFeb,sum(case when DATEPART(MM, HIST.[START-DATE]) = 3 then 1 else 0 end) as CountMar,sum(case when DATEPART(MM, HIST.[START-DATE]) = 4 then 1 else 0 end) as CountApr,sum(case when DATEPART(MM, HIST.[START-DATE]) = 5 then 1 else 0 end) as CountMay,sum(case when DATEPART(MM, HIST.[START-DATE]) = 6 then 1 else 0 end) as CountJun,sum(case when DATEPART(MM, HIST.[START-DATE]) = 7 then 1 else 0 end) as CountJul,sum(case when DATEPART(MM, HIST.[START-DATE]) = 8 then 1 else 0 end) as CountAug,sum(case when DATEPART(MM, HIST.[START-DATE]) = 9 then 1 else 0 end) as CountSept,sum(case when DATEPART(MM, HIST.[START-DATE]) = 10 then 1 else 0 end) as CountOct,sum(case when DATEPART(MM, HIST.[START-DATE]) = 11 then 1 else 0 end) as CountNov,sum(case when DATEPART(MM, HIST.[START-DATE]) = 12 then 1 else 0 end) as CountDec[/code]
Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy |
 |
|
|
|
|
|
|
|
Help on COUNTS