Results 1 to 10 of 10
  1. #1
    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. #2
    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
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Maybe something like this, though I haven't tested the syntax:

    select cast(Month([IncomeDate])/4 as int) as Quarter
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    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. #5
    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)
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    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 06:25.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  8. #8
    Join Date
    Mar 2004
    Location
    London, UK
    Posts
    71

    Talking

    you guy's are geniuses!!!

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote 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. #10
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Quote 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...
    That which does not kill me postpones the inevitable.

Posting Permissions

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