Query Problem - 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

Query Problem

Author

Topic

Sambasivam
Starting Member

36 Posts

Posted - 2008-11-18 : 16:29:41

Sorry if I'm repeating the same query but I need help.

Want to know why this query is asking for Group By or Aggregate? How to resolve this wihtout adding Group BY of fm.Fiscal_Month and HA.Hours_Actual. IS there a way to ADD SUM() aggregate as I tried without using Grou BY of fm.Fiscal_Month and HA.Hours_Actual.

-- Parameters BU, Loc, Client, UserRole
-- Bu - Business Unit
--Loc - Location
-- user role - Owner, Designee etc
-- To Display - Fiscal Month actual hours with total

SELECT DISTINCT

CL.[Client_Code] AS ClientCode
,CL.[Client_Name] AS ClientName
,
(CASE fm.FY_Month WHEN 'JAN' THEN HA.[Hours_Actual] ELSE 0 END) AS JANUARY,
(CASE fm.FY_Month WHEN 'FEB' THEN HA.[Hours_Actual] ELSE 0 END) AS FEBRUARY,
(CASE fm.FY_Month WHEN 'MAR' THEN HA.[Hours_Actual] ELSE 0 END) AS MARCH,
(CASE fm.FY_Month WHEN 'APR' THEN HA.[Hours_Actual] ELSE 0 END) AS APRIL,
(CASE fm.FY_Month WHEN 'MAY' THEN HA.[Hours_Actual] ELSE 0 END) AS MAY,
(CASE fm.FY_Month WHEN 'JUN' THEN HA.[Hours_Actual] ELSE 0 END) AS JUNE,
(CASE fm.FY_Month WHEN 'JUL' THEN HA.[Hours_Actual] ELSE 0 END) AS JULY,
(CASE fm.FY_Month WHEN 'AUG' THEN HA.[Hours_Actual] ELSE 0 END) AS AUGUST,
(CASE fm.FY_Month WHEN 'SEP' THEN HA.[Hours_Actual] ELSE 0 END) AS SEPTEMBER,
(CASE fm.FY_Month WHEN 'OCT' THEN HA.[Hours_Actual] ELSE 0 END) AS OCTOBER,
(CASE fm.FY_Month WHEN 'NOV' THEN HA.[Hours_Actual] ELSE 0 END) AS NOVEMBER,
(CASE fm.FY_Month WHEN 'DEC' THEN HA.[Hours_Actual] ELSE 0 END) AS DECEMBER
/*,

SUM(
(CASE fm.FY_Month WHEN 'JAN' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'FEB' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'MAR' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'APR' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'MAY' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'JUN' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'JUL' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'AUG' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'SEP' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'OCT' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'NOV' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'DEC' THEN HA.[Hours_Actual] ELSE 0 END)
) AS FYTotal*/

FROM [Hours_Actual] HA

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

WHERE (HA.[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 HA.[Fiscal_Month_Id] IN
(SELECT Fiscal_Month_Id
FROM Fiscal_Month
WHERE FY_Year = '2007')
)

GROUP BY
CL.[Client_Code]
, CL.[Client_Name]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-18 : 17:04:22

SUM(
(CASE fm.FY_Month WHEN 'JAN' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'FEB' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'MAR' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'APR' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'MAY' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'JUN' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'JUL' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'AUG' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'SEP' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'OCT' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'NOV' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'DEC' THEN HA.[Hours_Actual] ELSE 0 END)
) AS FYTotal*/

SUM(HA.[Hours_Actual])

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-18 : 17:05:14

Why are you having BOTH distinct and group by?

E 12°55'05.63"
N 56°04'39.26"

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-18 : 17:17:30

select 
CL.[Client_Code] ,
CL.[Client_Name],
SUM(CASE fm.FY_Month WHEN 'JAN' THEN HA.[Hours_Actual] ELSE 0 END) as JANUARY ,
SUM(CASE fm.FY_Month WHEN 'FEB' THEN HA.[Hours_Actual] ELSE 0 END) as FEBRUARY ,
....
....
....
SUM(CASE fm.FY_Month WHEN 'JAN' THEN HA.[Hours_Actual] ELSE 0 END) +SUM(CASE fm.FY_Month WHEN 'FEB' THEN HA.[Hours_Actual] ELSE 0 END)+ .... as Hours_Actual
FROM
..
GROUP BY
CL.[Client_Code] ,
CL.[Client_Name]

Sambasivam
Starting Member

36 Posts

Posted - 2008-11-18 : 17:20:40

The problem is I dont want to SUM in the below line
--------------------------------------------------------
CL.[Client_Code] ,
CL.[Client_Name],
SUM(CASE fm.FY_Month WHEN 'JAN' THEN HA.[Hours_Actual] ELSE 0 END) as JANUARY ,
SUM(CASE fm.FY_Month WHEN 'FEB' THEN HA.[Hours_Actual] ELSE 0 END) as FEBRUARY
-----------------------------------------------------
but I want the value as Column and it is just HA.[Hours_Actual] for each month and specific year (which is part of subquery )

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-18 : 17:21:33

quote:
Originally posted by Peso

SUM(
(CASE fm.FY_Month WHEN 'JAN' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'FEB' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'MAR' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'APR' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'MAY' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'JUN' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'JUL' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'AUG' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'SEP' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'OCT' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'NOV' THEN HA.[Hours_Actual] ELSE 0 END)
+ (CASE fm.FY_Month WHEN 'DEC' THEN HA.[Hours_Actual] ELSE 0 END)
) AS FYTotal*/
SUM(HA.[Hours_Actual])

E 12°55'05.63"
N 56°04'39.26"

Mightn't work if if FY_Month has junk values ,which shouldn't add up.

Sambasivam
Starting Member

36 Posts

Posted - 2008-11-18 : 17:24:24

I have problem in these line

(CASE fm.FY_Month WHEN 'JAN' THEN HA.[Hours_Actual] ELSE 0 END) AS JANUARY,
(CASE fm.FY_Month WHEN 'FEB' THEN HA.[Hours_Actual] ELSE 0 END) AS FEBRUARY,
(CASE fm.FY_Month WHEN 'MAR' THEN HA.[Hours_Actual] ELSE 0 END) AS MARCH,
(CASE fm.FY_Month WHEN 'APR' THEN HA.[Hours_Actual] ELSE 0 END) AS APRIL,
(CASE fm.FY_Month WHEN 'MAY' THEN HA.[Hours_Actual] ELSE 0 END) AS MAY,
(CASE fm.FY_Month WHEN 'JUN' THEN HA.[Hours_Actual] ELSE 0 END) AS JUNE,
(CASE fm.FY_Month WHEN 'JUL' THEN HA.[Hours_Actual] ELSE 0 END) AS JULY,
(CASE fm.FY_Month WHEN 'AUG' THEN HA.[Hours_Actual] ELSE 0 END) AS AUGUST,
(CASE fm.FY_Month WHEN 'SEP' THEN HA.[Hours_Actual] ELSE 0 END) AS SEPTEMBER,
(CASE fm.FY_Month WHEN 'OCT' THEN HA.[Hours_Actual] ELSE 0 END) AS OCTOBER,
(CASE fm.FY_Month WHEN 'NOV' THEN HA.[Hours_Actual] ELSE 0 END) AS NOVEMBER,
(CASE fm.FY_Month WHEN 'DEC' THEN HA.[Hours_Actual] ELSE 0 END) AS DECEMBER

SUM() is something I have written but I need to have these lines without having the columns used in these lines in GROUp BY Clause.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-18 : 17:35:02

Are you using SQL Server 2005? Try the PIVOT operator.

E 12°55'05.63"
N 56°04'39.26"

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-18 : 17:36:58

I might be wrong, But I think you're asking to convert rows to columns. Is that right ?
Something like,

Client1---Jan--2
Client1---Feb--3
Client2---Mar--5
Client2---Apr--7
Client2---Jan--11

Client---Jan--Feb--Mar--Apr
Client1---2----3----0-----0
Client2---11---0----5-----7

Refer to this link if this is what you want.
http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

Sambasivam
Starting Member

36 Posts

Posted - 2008-11-19 : 06:12:38

I need to change the condition and it worked the way I wanted. Thank You for everyone who is helping me though my questions are very basic. :) Kudos to all folks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources