CODEWITHSUNDEEP
sqlsql-server
vfiola
vfiola

Reputation: 79

Quarterly totals using loop in SQL server

----I am trying to get yearly and quarterly totals for 2013 and 2014 for San Francisco and San mateo counties seperately. I know it is probably very easy but having difficulty with the loop. I can do it without looping but it is lengthy and would like to do it in a cleaner neater fashion. Any help would be greatly appreciated. New to programming------

DECLARE @Qtotal int
DECLARE @Q1total int
DECLARE @Q2total int
DECLARE @Q3total int
DECLARE @Q4total int
DECLARE @year int
DECLARE @County int
DECLARE @CountyName varchar(40)
DECLARE @startmonth nvarchar
DECLARE @endmonth nvarchar

SET @startmonth = '1'
SET @endmonth = '3'
SET @year = '2013'
SET @county = '038'
SET @countyName = 'San Francisco'

Begin
 SELECT @Qtotal = (select COUNT(*) FROM #tCounty 
 where year(cast(dDate as date)) = @year
   and countycode = @County
   and month(cast(deathDate as date)) between @startmonth and @endmonth)--get quarter total
if @startmonth=1 SET @Q1total = @Qtotal
if @startmonth=4 SET @Q2total = @Qtotal
if @startmonth=7 SET @Q3total = @Qtotal
if @startmonth=10 SET @Q4total = @Qtotal

Set @startmonth = @startmonth + 3
Set @startmonth = @endmonth + 3
if @startmonth > 10 
end

--------insert into table created before and not shown in code above

INSERT INTO #Totals([County],[referenceYear],[Total],[Q1],[Q2],[Q3],[Q4]) Values         (@countyName,@year,@yrtotal,@Q1total,@Q2total,@Q3total,@Q4total)

Upvotes: 0

Views: 384

Answers (1)

attila
attila

Reputation: 2229

this should do it

select County, 
    Year(dDate) as Year, 
    count(*) as Total, 
    sum(case when DATEPART(q, dDate)=1 then 1 else 0 end) as Q1,
    sum(case when DATEPART(q, dDate)=2 then 1 else 0 end) as Q2,
    sum(case when DATEPART(q, dDate)=3 then 1 else 0 end) as Q3,
    sum(case when DATEPART(q, dDate)=4 then 1 else 0 end) as Q4
from #tCounty
group by County, 
    Year(dDate)

Upvotes: 2

Related Questions