November 19, 2007 at 8:35 am
Hello everyone. I am very new to SQL Server. My experience has been in MS Access for over 10 years. I have been asked to write a web based app using SQL Server 2000. I need to get a summary of totals based on the type field of a record.
Here is the query I've written. This query works fine but I would like to be able to break the totals down by department. Can this be done in a single query? If so, can anyone help me with this? I would greatly appreciate it.
Select distinct
(SELECT SUM(TotalHours) FROM TADetail
WHERE (PayPeriod = '11/24/2007') AND (StaffID = '1234') AND (Dept = '56')) As PayTotal,
(SELECT SUM(TotalHours) FROM TADetail
WHERE (PayPeriod = '11/24/2007') AND (StaffID = '1234') AND (Dept = '56') AND (Type = '10')) As RegTotal,
(SELECT SUM(TotalHours) FROM TADetail
WHERE (PayPeriod = '11/24/2007') AND (StaffID = '1234') AND (Dept = '56') AND (Type = '4')) As PTOTotal,
(SELECT SUM(TotalHours) FROM TADetail
WHERE (PayPeriod = '11/24/2007') AND (StaffID = '1234') AND (Dept = '56') AND (Type = '8')) As SickBankTotal,
(SELECT SUM(TotalHours) FROM TADetail
WHERE (PayPeriod = '11/24/2007') AND (StaffID = '1234') AND (Dept = '56') AND (Type <> '4') AND (Type <> '8') AND (Type <> '10')) As OtherTotal
From TADetail WHERE StaffID = '1234'
Jim
November 19, 2007 at 8:53 am
Embed a CASE statement into the SELECT... syntax. You're restricting yourself to one group only, so there's no sense in using a GROUP BY clause.
Something like:
select
SUM(TotalHours) as PayTotal,
sum(case when (Type = '10') then TotalHours else 0 end) as RegTotal,
sum(case when (Type = '4') then TotalHours else 0 end) as PTOTotal,
sum(case when (Type = '8') then TotalHours else 0 end) as SickBankTotal,
sum(case when (Type not in ('10','4','8')) then TotalHours else 0 end) as OtherTotal
from
tadetail
where
StaffID = '1234' and
(PayPeriod = '11/24/2007') and
(Dept = '56')
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 19, 2007 at 9:04 am
I would be taking out Where "dept = 56" code.
November 19, 2007 at 9:07 am
..then group by Dept would give you a line per department?
so....
select
Dept ,
SUM(TotalHours) as PayTotal,
sum(case when (Type = '10') then TotalHours else 0 end) as RegTotal,
sum(case when (Type = '4') then TotalHours else 0 end) as PTOTotal,
sum(case when (Type = '8') then TotalHours else 0 end) as SickBankTotal,
sum(case when (Type not in ('10','4','8')) then TotalHours else 0 end) as OtherTotal
from tadetail
where
StaffID = '1234' and
(PayPeriod = '11/24/2007')
Group by
Dept
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 19, 2007 at 9:08 am
Thanks so much for taking the time to answer my question. That did it! Have a Happy Turkey Day! Jim
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply