Aggregation in query - SQL Server Forums

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

Aggregation in query

Author

Topic

Sambasivam
Starting Member

36 Posts

Posted - 2008-11-17 : 10:22:48

I have to write a query to fetch some rows along with total of a column. The structure is as follows:
1) Table that I need to use to fetch rows to display (Work Hours which have scehduled hours and linked to other tables)
2) Plan table which have plans scheduled for fiscal year for client.
3) Table Fiscal Month which have details of month for fiscal year
4) Tables CLient, Business Unit & Location which are master tables.

My Inputs (selection in front end ) would be Business Unit, Location and Client.

Now I have to display Hours Scheduled (From hours scheduled table) for each month (from fiscal month table) for selected Business Unit (Business Unit table), Location (Location table) and Client (from client table) that are linked to Hours Scheduled table. I also have to display total hrs scheduled (From hours scheduled table). Currently I have 2 different queries but since the query parameters are same would like to use as single query. I tried using derived tables but since Im new to this I need help how to write this as single query.

Sample Query I wrote
--------------------

SELECT HS.[Hours_Scheduled] AS HoursScheduled
,FM.[FY_Month] AS FiscalMonth
,CL.[Client_Code] AS ClientCode
,CL.[Client_Name] AS ClientName
-- ,SUM(HS.[Hours_Scheduled]) AS FYTotal

FROM [Hours_Scheduled] HS

INNER JOIN [Client] CL ON HS.[Client_Id] = CL.[Client_Id]
INNER JOIN [Plan] P ON P.[Client_Id] = HS.[Client_Id]
AND P.[Business_Unit_Id] = HS.[Busness_Unit_Id]
AND P.[Location_Id] = HS.[Location_Id]
INNER JOIN [Fiscal_Month] FM ON FM.[Fiscal_Month_Id] = HS.[Fiscal_Month_Id]

WHERE (HS.[Client_Id] IN
(SELECT [Client_Id] FROM [RPT].[dbo].[Plan] WHERE
[Business_Unit_Id] = (Select Business_Unit_Id from Business_Unit where Business_Unit_Code = 'US001'
AND Business_Unit_Name = 'Northeast')
AND [Location_Id] = (Select Location_Id from Location Where Location_Code = '0010L'
AND Location_Name = 'New York, NY')
AND [Designee_Person_Id] = (Select [Person_Id] from Person Where [Person_First_Name] = 'Roger'
AND [Person_Last_Name] = 'Brown')
)
AND FM.[FY_Year] = '2009')

-- Total Column

SELECT SUM(HS.[Hours_Scheduled]) AS FYTotal

FROM [Hours_Scheduled] HS
INNER JOIN [Client] CL ON HS.[Client_Id] = CL.[Client_Id]
INNER JOIN [Plan] P ON P.[Client_Id] = HS.[Client_Id]
AND P.[Business_Unit_Id] = HS.[Busness_Unit_Id]
AND P.[Location_Id] = HS.[Location_Id]
INNER JOIN [Fiscal_Month] FM ON FM.[Fiscal_Month_Id] = HS.[Fiscal_Month_Id]

WHERE (HS.[Client_Id] IN
(SELECT [Client_Id] FROM [RPT].[dbo].[Plan] WHERE
[Business_Unit_Id] = (Select Business_Unit_Id from Business_Unit where Business_Unit_Code = 'US001'
AND Business_Unit_Name = 'Northeast')
AND [Location_Id] = (Select Location_Id from Location Where Location_Code = '0010L'
AND Location_Name = 'New York, NY')
AND [Designee_Person_Id] = (Select [Person_Id] from Person Where [Person_First_Name] = 'Roger'
AND [Person_Last_Name] = 'Brown')
)
AND FM.[FY_Year] = '2009')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 11:16:34

are you using sql 2005?

Sambasivam
Starting Member

36 Posts

Posted - 2008-11-17 : 11:37:31

Yes Im using SqlServer 2005. Im not sure whether this is possible but would be good if I can do these 2 queries in one.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 11:48:34

SELECT DISTINCT HS.[Hours_Scheduled] AS HoursScheduled
,FM.[FY_Month] AS FiscalMonth
,CL.[Client_Code] AS ClientCode
,CL.[Client_Name] AS ClientName
,SUM(HS.[Hours_Scheduled])OVER(PARTITION BY FM.[FY_Month] ,CL.[Client_Code],P.[Business_Unit_Id],HS.[Location_Id]) AS FYTotal
SUM(HS.[Hours_Scheduled])OVER() AS GrandFYTotal

FROM [Hours_Scheduled] HS

INNER JOIN [Client] CL ON HS.[Client_Id] = CL.[Client_Id]
INNER JOIN [Plan] P ON P.[Client_Id] = HS.[Client_Id]
AND P.[Business_Unit_Id] = HS.[Busness_Unit_Id]
AND P.[Location_Id] = HS.[Location_Id]
INNER JOIN [Fiscal_Month] FM ON FM.[Fiscal_Month_Id] = HS.[Fiscal_Month_Id]

WHERE (HS.[Client_Id] IN
(SELECT [Client_Id] FROM [RPT].[dbo].[Plan] WHERE
[Business_Unit_Id] = (Select Business_Unit_Id from Business_Unit where Business_Unit_Code = 'US001'
AND Business_Unit_Name = 'Northeast')
AND [Location_Id] = (Select Location_Id from Location Where Location_Code = '0010L'
AND Location_Name = 'New York, NY')
AND [Designee_Person_Id] = (Select [Person_Id] from Person Where [Person_First_Name] = 'Roger'
AND [Person_Last_Name] = 'Brown')
)
AND FM.[FY_Year] = '2009')

Sambasivam
Starting Member

36 Posts

Posted - 2008-11-17 : 12:13:18

Thank You. I tweaked it a bit as per my requirement.I need to understand the Over() that you have provided.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 12:14:57

http://msdn.microsoft.com/en-us/library/ms189461.aspx

Sambasivam
Starting Member

36 Posts

Posted - 2008-11-17 : 14:40:15

thank you very much.Can you suggest a good study material for me as a beginner.