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.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Help on COUNTS

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
Go to Top of Page
   

- Advertisement -