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

Author

Topic

sqlserverlearner
Starting Member

21 Posts

Posted - 2009-01-22 : 18:07:04

I need a query to fetch (I have a table like this with values).Anyone please help.

Need Sum(HeadCount) and SUM(Hours)
WHERE Year 2008 for Month JUL,AUG,SEP,OCT,NOV,DEC,JAN,FEB
and Year 2009 for Month MAR,APR,MAY,JUN
Locationwise from this table

Year Month Location HeadCount Hours
2007 MAR AN001 35 5600
2007 MAR BB001 68 10880
2007 MAR TT001 78 12480
2007 MAR TT002 0 0
2007 MAR TT003 4 640
2008 MAR AN001 43 6880
2008 MAR BB001 69 11040
2008 MAR TT001 71 11360
2008 MAR TT002 9 1440
2008 MAR TT003 2 320
2007 APR AN001 39 7800
2007 APR BB001 66 13200
2007 APR TT001 76 15200
2007 APR TT002 0 0
2007 APR TT003 4 800
2008 APR AN001 41 8200
2008 APR BB001 69 13800
2008 APR TT001 67 13400
2008 APR TT002 9 1800
2008 APR TT003 2 400

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-22 : 18:28:47

Something like this may do it

SELECT [Year],[Month],Location,SUM(HeadCount),SUM(Hours)
FROM YourTable
WHERE ([Year] = 2008 AND [Month] IN ('JUL','AUG','SEP','OCT','NOV','DEC','JAN','FEB'))
OR ([Year] = 2009 AND [Month] IN ('MAR','APR','MAY','JUN'))
GROUP BY [Year],[Month],Location

sqlserverlearner
Starting Member

21 Posts

Posted - 2009-01-22 : 18:31:58

It is AND as
SELECT [Year],[Month],Location,SUM(HeadCount),SUM(Hours)
FROM YourTable
WHERE ([Year] = 2008 AND [Month] IN ('JUL','AUG','SEP','OCT','NOV','DEC','JAN','FEB'))
AND ([Year] = 2009 AND [Month] IN ('MAR','APR','MAY','JUN'))
GROUP BY [Year],[Month],Location

I tried this but it is not returning any result eventhough record is there. By the way im using table variable.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-22 : 18:34:06

If you use AND to combine the years then it can't return anything since you can only have one year. Have you tried it with OR like I suggested?

sqlserverlearner
Starting Member

21 Posts

Posted - 2009-01-22 : 18:38:02

THank you for looking into my request.OR is working but I want to find SUM(headcount) for these months across 2 years by location in which case I need to use AND. Please help me.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-22 : 18:42:49

I guess I'm not fully understanding your requirement. If you could, please post some sample data and expected output.

Also what you could try is grouping only by Location and removing the [Year] and [Month] columns from both the SELECT and GROUP BY clauses.

sqlserverlearner
Starting Member

21 Posts

Posted - 2009-01-22 : 18:46:31

HeadCount data is spread across years say 2008 and 2009 starting JUL 2008 and goes till FEB 2008 and then from MAR to JUN is considered as 2009. that is requirement and i have data as required. Now I have to sum the headcount for all these months across 2008 and 2009 locationwise.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-22 : 18:51:04

Please try running this and see if this is close to what you're looking for

SELECT Location,SUM(HeadCount) AS SumHeadCount,SUM(Hours) AS SumHours
FROM YourTable
WHERE ([Year] = 2008 AND [Month] IN ('JUL','AUG','SEP','OCT','NOV','DEC','JAN','FEB'))
OR ([Year] = 2009 AND [Month] IN ('MAR','APR','MAY','JUN'))
GROUP BY Location

sqlserverlearner
Starting Member

21 Posts

Posted - 2009-01-22 : 18:55:15

this is working but it is not what i need.

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2009-01-22 : 19:00:35

Try this one..

regards,
Anil Kumar.

select
Years = datename(YEAR,"dbo"."Table"."ordertime"),
Month = datename(Month,"dbo"."Table"."ordertime"),
Location, sum(headcount), sum(hours)
from
Table --Whatever table
group by
Location,
datename(YEAR,"dbo"."Table"."ordertime"),
datename(Month,"dbo"."Table"."ordertime")
having (datename(Month,"dbo"."Table"."ordertime") )
in ('November', 'JULY', 'AUGUST', 'September', 'October', 'December', 'January', 'February')
and datename(YEAR,"dbo"."Table"."ordertime") = '2008'

union all

select
Years = datename(YEAR,"dbo"."Table"."ordertime"),
Month = datename(Month,"dbo"."Table"."ordertime"),
Location, sum(headcount), sum(hours)
from
Table --Whatever table
group by
Location,
datename(YEAR,"dbo"."Table"."ordertime"),
datename(Month,"dbo"."Table"."ordertime")
having (datename(Month,"dbo"."Table"."ordertime") )
in ('March', 'April', 'May', 'June')
and datename(YEAR,"dbo"."Table"."ordertime") = '2009'

sqlserverlearner
Starting Member

21 Posts

Posted - 2009-01-22 : 19:16:46

this gives added rows but not one SUM(HeadCount) value.

sqlserverlearner
Starting Member

21 Posts

Posted - 2009-01-22 : 19:44:50

anyway please help me. Im running out of time.

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2009-01-22 : 20:09:15

Im not sure if I understood you correctly but they are going to be different as we are talking about grouping data for different months/years.
Did you validate what you are saying by individually running a query for a Location lets say 'AN001' ?

regards,
Anil Kumar.

sqlserverlearner
Starting Member

21 Posts

Posted - 2009-01-23 : 09:09:03

Yes I tried a different query for one location and it worked but because of grouping by location im not able to. I tried this for one location.
Select
(Select Sum(HeadCount) FROM table where year = '2008' and month in (JUL,AUG,....FEB) where location = 'AN001')
+SUM(HeadCount) FROM table where year = '2009' and month in (MAR,APR,MAY,JUN) where location = 'AN001'

it worked perfect.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 09:21:38

isnt that same as?


SELECT [Year],Location,SUM(Count)
FROM YourTable
WHERE ([Year] = 2008 AND [Month] IN ('JUL','AUG','SEP','OCT','NOV','DEC','JAN','FEB'))
OR ([Year] = 2009 AND [Month] IN ('MAR','APR','MAY','JUN'))
GROUP BY [Year],Location

where you get locations sum of count yearwise

sqlserverlearner
Starting Member

21 Posts

Posted - 2009-01-23 : 09:23:10

I need this. I need sum(count) for months across years 2008 and 2009. Im using table variable and year and month are stored as varchar

SELECT [Year],Location,SUM(Count)
FROM YourTable
WHERE ([Year] = 2008 AND [Month] IN ('JUL','AUG','SEP','OCT','NOV','DEC','JAN','FEB'))
AND ([Year] = 2009 AND [Month] IN ('MAR','APR','MAY','JUN'))
GROUP BY [Year],Location

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-01-23 : 11:04:54

quote:
Originally posted by sqlserverlearner

I need this. I need sum(count) for months across years 2008 and 2009. Im using table variable and year and month are stored as varchar

SELECT [Year],Location,SUM(Count)
FROM YourTable
WHERE ([Year] = 2008 AND [Month] IN ('JUL','AUG','SEP','OCT','NOV','DEC','JAN','FEB'))
AND ([Year] = 2009 AND [Month] IN ('MAR','APR','MAY','JUN'))
GROUP BY [Year],Location

You have to realize that the use of AND there will prevent you from returning any rows. A single row in your data contains only ONE year. So what you're effectively asking is to return all rows where the year both equals to 2008 and 2009, which will be false for every case. The use of OR instead of AND will allow you to query based on either 2008 or 2009, which is what you're trying to achieve based on how you explained it. For testing purposes, you can try commenting out the line for 2009 and running the query for 2008 only.

sqlserverlearner
Starting Member

21 Posts

Posted - 2009-01-23 : 16:27:41

I changed the table adding an id column and filtering based on the id column which was available in original table. My issue resolved. I want to thank everyone who looked into it. That was good help to know why it will not return any row for the condition i was trying.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources