Another Question for SamC - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Another Question for SamC

Author

Topic

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2003-12-22 : 16:53:39

Sam,

With the help you gave me on my last question, I've got another one.

The results I get from the last post is great, but now I'm wondering if I can take it up another level.

As before I basicly would like to run a query in SQL to get the same result I would if I dumped the data in Excel and ran a pivot table.

What I'd like to see is the breakdown by the different branches and whether we get a Yes or No which we get now, but I'd also like to see where they fall over the last 4 months and then what's left over into 2003 and 2002.

Here is an example out of Excel.

Count of EMP_NR Month
BRA_NR C23 Due 2002 2003 Sep Oct Nov Dec
4410 NO 36 12 26 22 3
YES 1 8 29 51
4410 Total 36 13 34 51 54
4510 NO 12 591 211 248 358 132
YES 5 3 7 20 265
4510 Total 12 596 214 255 378 397
4810 NO 5 46 13 21 23 3
YES 2 1 5 10
4810 Total 5 46 15 22 28 13
4820 NO 1 56 9 8 9 3
YES 2
4820 Total 1 56 9 8 9 5

I have a query that I use to give me the month or year breakdown, here is the code.

CASE
WHEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate) = DATENAME (MONTH,GETDATE()) and DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)= DATENAME (YEAR,GETDATE())THEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate)
WHEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate) = DATENAME (MONTH,(DATEADD(MONTH, -1, GETDATE()))) and DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)= DATENAME (YEAR,GETDATE())THEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate)
WHEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate) = DATENAME (MONTH,(DATEADD(MONTH, -2, GETDATE()))) and DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)= DATENAME (YEAR,GETDATE())THEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate)
WHEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate) = DATENAME (MONTH,(DATEADD(MONTH, -3, GETDATE()))) and DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)= DATENAME (YEAR,GETDATE())THEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate)
ELSE DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)
END 'AbsenceMonth / Year',

Could this be used in the query you helped me with to give me the desired results?

Thanks.

GC

SamC
White Water Yakist

3467 Posts

Posted - 2003-12-23 : 12:38:11

Danger lurks in any forum when a question is addressed to an individual.

One: it may ward off more competent contributors.

Two: individuals can take long breaks.

Three: it may be a tough question and he/she doesn't have a ready answer and doesn't want to admit it!

I've read your question twice, and I'm not clear on exactly what is the resultset you're looking for.

Can you post the resultset you have, the query that generates it and an example of how you would like the resultset to look?

The original thread is: [url]http://sqlteam.com/Forums/topic.asp?TOPIC_ID=31454[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources