# Thread: Query by quarter

1. Registered User
Join Date
Mar 2003
Posts
223

## Unanswered: Query by quarter

Hello, everyone:

There is a table named INCOME that has INCOME column for each day and DATE column starting from Aug. 29 1980. How to calculate income summary by each quarter? Thanks.

ZYT

2. Registered User
Join Date
Apr 2004
Location
Kansas City, MO
Posts
734
Define quarter. Are you a calendar year quarter, fiscal 4434 or 4344??? When does your year begin and end?

After you find that out,

SELECT
SUM(CASE WHEN date BETWEEN @q1begin AND @q1end THEN income ELSE 0 END) AS q1,
SUM(CASE WHEN date BETWEEN @q2begin AND @q2end THEN income ELSE 0 END) AS q2,
SUM(CASE WHEN date BETWEEN @q3begin AND @q3end THEN income ELSE 0 END) AS q3,
SUM(CASE WHEN date BETWEEN @q4begin AND @q4end THEN income ELSE 0 END) AS q4
FROM
that_table

3. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Maybe something like this, though I haven't tested the syntax:

select cast(Month([IncomeDate])/4 as int) as Quarter

4. Registered User
Join Date
May 2003
Location
Parsippany NJ
Posts
36
The following dates are UK-Defined Quarter Day
25 March (Lady Day),
24 June (Midsummer Day),
29 September (Michaelmas), and 25 December (Christmas Day).

Try this stored procedure

Create Proc usp_QuarterIncome
As
Set Nocount On
Create Table #Quarter_Income(Id int identity(1,1),Quarters datetime,QuarterIncome money)

insert into #Quarter_Income (Quarters)
select distinct datename(yy,date)+'/'+convert(varchar(2),datepart(mm,date))+'/'+
case datepart(mm,date) when 3 then '25' when 6 then '24' when 9 then '29' when 12 then '25' end as Quarter
from income where datepart(mm,date) in (3,6,9,12)

declare @id int,
@StartDate Datetime,
@Start DateTime,
@EndDate Datetime,
@Sum money
Select @Start = Min(Date) From Income

set @id = 0
while @Id < (select max(id) from #quarter_Income)
begin
Select @StartDate= Quarters From #quarter_Income where Id = @Id
Select @EndDate = Quarters From #quarter_Income where Id = @Id+1
Select @Sum = Sum(Income) From Income where [Date] >= isnull(@StartDate,@Start) and [Date] < @EndDate
Update #quarter_Income Set QuarterIncome = @Sum where Id = @Id+1
Set @Id = @id + 1
End
Select Quarters,QuarterIncome from #quarter_Income
Set Nocount Off

--Usage:
--Exec usp_QuarterIncome

5. The SQL Apostle
Join Date
Jul 2003
Location
The Dark Planet
Posts
1,401
What is the problem with
Code:
`select sum(income),datepart(qq,date),year(date) from income group by datepart(qq,date),year(date)`

6. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Humph! Sure, any shmuck can use the fancy-pants "built-in functions". But where is the challenge in that? If you are lucky, tomorrow I will post my innovative cursor-based dynamic SQL function for removing leading spaces from strings.

7. The SQL Apostle
Join Date
Jul 2003
Location
The Dark Planet
Posts
1,401
You will need to write another one for the trailing spaces ... I will need that too ...
Last edited by Enigma; 05-20-04 at 05:25.

8. Registered User
Join Date
Mar 2004
Location
London, UK
Posts
71
you guy's are geniuses!!!

9. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Originally Posted by Enigma
What is the problem with
Code:
`select sum(income),datepart(qq,date),year(date) from income group by datepart(qq,date),year(date)`
Man, I like the way that you think!

-PatP

10. Useless...
Join Date
Jul 2003
Location
SoCal
Posts
721
Originally Posted by blindman
Humph! Sure, any shmuck can use the fancy-pants "built-in functions". But where is the challenge in that? If you are lucky, tomorrow I will post my innovative cursor-based dynamic SQL function for removing leading spaces from strings.
I almost fell outta my chair... I could've hurt myself! Hysterical laughter at work implies insanity... I hope no one noticed...

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•