How do I sub total and total these rows? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How do I sub total and total these rows?

Author

Topic

diane.lonergan@gmail.com
Starting Member

6 Posts

Posted - 2009-01-15 : 12:01:31

I have written the following code, which produces a list of employees who have been off sick in the last week, with the start date and end date. It will also show any long term sickness, or an employee who went sick in the last week, but is yet to return.

I want to subtotal by department and have a grand total. I thought I would use rollup, but the syntax I am using doesn't produce the desired result. Can anyone help?

The report so far has a row for each employee, in dept, surname order.

select final.forenames, final.surname,final.department, final.start_date, sum(duration) as total
,final.start_date, final.type
,final.Paid_Unpaid,final.Sickness_in_Last_12_Mths, final.end_date, final.reason

from
(Select emp_Names.forenames
,emp_names.Surname
,emp_names.Department
,ab_dates.start_date
,ab_dates.duration
,ab_dates.Type
,ab_dates.Paid_Unpaid
,emp_names.Sickness_in_Last_12_Mths
,ab_dates.end_date
,ab_dates.reason

from (select ce.staff_no
,ce.forenames
,ce.surname
,ce.department
,ce.id
,ce.Sickness_in_Last_12_Mths
from dbo.Current_Employees CE

) as Emp_names

Left Join

(select Ab.Start_Date
,Ab.End_Date
,Ab.id_1
,ab.type
,ab.duration
,ab.Paid_Unpaid
,ab.reason
from absence ab

where (
(--1ST LEVEL
(--2ND LEVEL
( --3rd level
ab.start_date > dateadd(dd,-8,getdate())
and
(--4th level
ab.end_date <= dateadd(dd,1,getdate())
or
(ab.end_date is null and ab.start_date <= getdate())
)--4th level
)-- 3rd level
)-- 2ND LEVEL
or
(-- 2nd level
( --3rd level
ab.end_date > dateadd(dd,-7,getdate())
and
(--4th level
ab.end_date <= dateadd(dd,1,getdate())
or
(ab.end_date is null and ab.start_date <= getdate())
)--4th level
)-- 3rd level
) -- 2nd level
)--1ST LEVEL
and
ab.type not in ('hols','worked')

) -- Parenthesis ends the where
) -- parenthesis ends the Select From

as ab_dates
on emp_names.id = ab_dates.id_1
where ab_dates.start_date is not null
) as final
group by final.department, final.forenames, final.surname, final.start_date, final.type
, final.Paid_Unpaid,final.Sickness_in_Last_12_Mths, final.end_date, final.reason [/red]

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2009-01-15 : 15:25:39

Please illustrate in sample tables and expected output.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 23:23:18

much better to provide sample data and output reqd in below format rather than giving query
Also if this is for sql reporting services, then you could do all thses aggregation very easily in reports.

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources