asked on

display only completed quarters in SQL script

i have a sql script where i am displaying last month hours, Q1 hours, Q2 hours, Q3 hours, Q hours and YTD hours.
Now, I need to display only completed quarters, so for eg. right now we are still in Q2, so I want to display 0 for Q2 hours.
till now, i was displaying all the hours in Q2 till date.

this is my script:

select
employee,
      SUM(case when end_date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)
       and
       end_date < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) then hours else 0 end) as LastMonthhours,
      SUM(case when month(end_date) between 1 and 3 then hours else 0 end) as Q1Hrs,
      SUM(case when month(end_date) between 4 and 6 then hours else 0 end) as Q2Hrs,
      SUM(case when month(end_date) between 7 and 9 then hours else 0 end) as Q3Hrs,
      SUM(case when month(end_date) between 10 and 12 then hours else 0 end) as Q4Hrs,
      SUM(case when month(end_date) < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) then hours else 0 end) as YTDHrs
from
      table
where
      end_date >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
      and
      end_date< DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1)
group by
      employee

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

need_solution

ASKER

Sorry Guy Hengel but i had to modify your code a bit as i thought it was not giving exactly what i wanted,

here is my updated code:

select
      employee,
      SUM(case when end_date >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)
       and
       end_date < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) then hours else 0 end) as LastMonthhours,
      SUM(case when month(end_date) between 1 and 3 and month(getdate()) not in (1, 2, 3) then hours else 0 end) as Q1Hrs,
      SUM(case when month(end_date) between 4 and 6 and month(getdate()) not in (4, 5, 6) then hours else 0 end) as Q2Hrs,
      SUM(case when month(end_date) between 7 and 9 and month(getdate()) not in (7, 8, 9) then hours else 0 end) as Q3Hrs,
      SUM(case when month(end_date) between 10 and 12 and month(getdate()) not in (10, 11, 12) then hours else 0 end) as Q4Hrs,
      SUM(case when month(end_date) < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) then hours else 0 end) as YTDHrs,
from
      table
where

      end_date >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)
      and
      end_date< DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1)

group by
      employee

Guy Hengel [angelIII / a3]

oups ... indeed.
glad I could however help you (I assume you got your answer?)

need_solution

ASKER

Yes and Thank you so much!