Pivot query

Question

Post reply

Pivot query

pandeharsh

Ten Centuries

Points: 1106
More actions
September 2, 2010 at 5:51 am

#239809

Iam unable to convert this in required out put.
My Table Consist of following data
TimeId -- EmpId -- weekStartingDate -- Totalhours
1 -- 100 -- 8/1/2010 -- 42:00
2 -- 100 -- 8/8/2010 -- 24:00
3 -- 100 -- 8/15/2010 -- 24:00
4 -- 100 -- 8/22/2010 -- 42:00
5 -- 101 -- 8/1/2010 -- 24:00
6 -- 101 -- 8/8/2010 -- 42:00
I need to generate output,
Reports like this
Scheduled Hours | 8/1/2010 | 8/8/2010 | 8/15/2010 | 8/22/2010
less than 24 hrs -- 50% | 50% | 50% | 0%
b/w 24 and 48 -- 50% | 50% | 0% | 50%
more than 48 hrs -- 0% | 0% | 0% | 0%
Additional Information
No of employees is per week(i.e calculate percentage of employees scheduled less than 24hrs....)
Date Column - SUnday date.(i.e sunday to saturday)
Month -user will select excel file data of every week uploads it to database.
I will select particular date from date control(select only date which falls on sunday).
If i select this sunday date i want reports for last 4 weeks,3 weeks,2 weeks and this week.
Everytime when user selects date we need to generate reports for last 4,3,2 from the selected sunday date.
Thanks in advance
[font="Verdana"]Regards
Kumar Harsh[/font]
:unsure:

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Your question is not really clear...

What do you mean by "If this much is sufficient then its ok else..."??? What determines "sufficient"?

Also, please clarify what SQL Server version you're using. It'll make a huge difference regarding the possible solutions.

Finally, please provide teable def and sample data in a ready to use format including your expected result based on the sample data as described in the first link in my signature.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

pandeharsh Ten Centuries Points: 1106 More actions · Answer 2

Sorry,its sql server 2000.

I hv corrected it.

CREATE TABLE [tblTest] (

[TimeId] [int] NOT NULL ,

[EmpId] [int] NOT NULL ,

[weekStartingDate] [datetime] NOT NULL ,

[Totalhours] [decimal](18, 2) NOT NULL

) ON [PRIMARY]

GO

Just someone convert given sample data to output.

[font="Verdana"]Regards
Kumar Harsh[/font]

:unsure:

LutzM SSC Guru Points: 107049 More actions · Answer 3

How about taking a look at the CrossTab article referenced in my signature?

It might help you to get the data pivoted.

I'm not sure who you refer to with "Just someone convert given sample data to output."...

It seems like you didn't find the time to read and follow the advice given in the link I pointed you at.

So, all I can say is "Just someone to read that CrossTab article and modify it to your needs."

Please note that those of us trying to help are volunteers spending our spare time helping the community. Don't you think it would be appropriate if you'd invest some of your time to help us help you?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

pandeharsh Ten Centuries Points: 1106 More actions · Answer 4

Hi,

Kindly ignore my language.I never meant that,I am not trying and someone just help me.

I am trying to follow the link u gave.

What I meant was even if someone convert the input table data in desired output then its more than enough.

I just want one example in pivot/cross tab thru this example.Then may be I understand this for the rest of my life.

[font="Verdana"]Regards
Kumar Harsh[/font]

:unsure:

WayneS SSC Guru Points: 95460 More actions · Answer 5

This should get you started.

Things still left to to:

1. convert to %, and summarize.

2. probably want this to be dynamic.

Please read the Cross-Tabs and Pivot Tables, Part 1 and Part 2 links in my signature. The part 2 will help in making this dynamic.

-- See how you start off by actually creating a table and then

-- inserting the data into it? Your doing this makes it a lot easier

-- for all of us volunteers to help you. So, help us help you.

-- See http://www.sqlservercentral.com/articles/Best+Practices/61537/

-- for more details on how to do all of this.

DECLARE @test-2 TABLE (TimeID int, EmpID int, weekStartingDate datetime, TotalHours varchar(5));

INSERT INTO @test-2

SELECT 1, 100, '8/1/2010', '42:00' UNION ALL

SELECT 2, 100, '8/8/2010', '24:00' UNION ALL

SELECT 3, 100, '8/15/2010', '23:00' UNION ALL

SELECT 4, 100, '8/22/2010', '49:00' UNION ALL

SELECT 5, 101, '8/1/2010', '24:00' UNION ALL

SELECT 6, 101, '8/8/2010', '42:00'

SELECT [Scheduled Hours] = CASE WHEN [Hours] < 24 THEN 'less than 24 hrs'

WHEN [Hours] > 48 THEN 'more than 48 hrs'

ELSE 'b/w 24 and 48' END,

[8/1/2010] = SUM(CASE WHEN weekStartingDate = '20100801' THEN [Hours] ELSE 0 END),

[8/8/2010] = SUM(CASE WHEN weekStartingDate = '20100808' THEN [Hours] ELSE 0 END),

[8/15/2010] = SUM(CASE WHEN weekStartingDate = '20100815' THEN [Hours] ELSE 0 END),

[8/22/2010] = SUM(CASE WHEN weekStartingDate = '20100822' THEN [Hours] ELSE 0 END)

FROM (-- need to strip out the hours

SELECT *,

[Hours] = convert(int, LEFT(TotalHours, CharIndex(':', TotalHours)-1)),

[Minutes] = convert(int, RIGHT(TotalHours, CharIndex(':', TotalHours)-1))

FROM @test-2) t1

GROUP BY [Hours]

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

pandeharsh Ten Centuries Points: 1106 More actions · Answer 6

Thanks.

Data is repeating.If I run the query as it is then,

"b/w 24 and 48" is repeating,rather it should show its sum.

Please check my query,only thing is that I am unable to pivot it.

DECLARE @test-2 TABLE (TimeID int, EmpID int, weekStartingDate datetime, TotalHours decimal(5,2));

INSERT INTO @test-2

SELECT 1, 100, '8/1/2010', 42.13 UNION ALL

SELECT 2, 100, '8/8/2010', 24.00 UNION ALL

SELECT 3, 100, '8/15/2010',23.00 UNION ALL

SELECT 4, 100, '8/22/2010',49.00 UNION ALL

SELECT 5, 101, '8/1/2010', 24.00 UNION ALL

SELECT 6, 101, '8/8/2010', 42.00 union all

SELECT 7, 101, '8/8/2010', 13.00

SELECT weekstartingdate ,

Count(Case When Totalhours<=24 Then empid end) as LessThan24hrs,

Count(Case when Totalhours>24 and Totalhours<=48 Then empid end) as bw24and48,

Count(Case when Totalhours>48 Then empid end) as Morethan48hrs

FROM @test-2

GROUP BY weekstartingdate

[font="Verdana"]Regards
Kumar Harsh[/font]

:unsure:

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

pandeharsh (9/4/2010)
Please check my query,only thing is that I am unable to pivot it.

I know what your next question will be but make sure that this is what you want at this stage first...

DECLARE @test-2 TABLE (TimeID int, EmpID int, weekStartingDate datetime, TotalHours decimal(5,2));

INSERT INTO @test-2

(TimeID, EmpID, weekStartingDate, TotalHours)

SELECT 1, 100, '8/1/2010', 42.13 UNION ALL

SELECT 2, 100, '8/8/2010', 24.00 UNION ALL

SELECT 3, 100, '8/15/2010',23.00 UNION ALL

SELECT 4, 100, '8/22/2010',49.00 UNION ALL

SELECT 5, 101, '8/1/2010', 24.00 UNION ALL

SELECT 6, 101, '8/8/2010', 42.00 UNION ALL

SELECT 7, 101, '8/8/2010', 13.00

;

SELECT CASE TimeSlot

WHEN 1 THEN 'LessThan24hrs'

WHEN 2 THEN 'bw24and48'

WHEN 3 THEN 'Morethan48hrs'

END AS [Scheduled Hours],

SUM(CASE WHEN WeekStartingDate = '20100801' THEN TheCount ELSE 0 END) AS '20100801',

SUM(CASE WHEN WeekStartingDate = '20100808' THEN TheCount ELSE 0 END) AS '20100808',

SUM(CASE WHEN WeekStartingDate = '20100815' THEN TheCount ELSE 0 END) AS '20100815',

SUM(CASE WHEN WeekStartingDate = '20100822' THEN TheCount ELSE 0 END) AS '20100822'

FROM ( --=== Preaggregate the data

SELECT WeekStartingDate, TimeSlot, COUNT(*) AS TheCount

FROM

( --=== Classify the hours for a given time slot to create groupable rows from

SELECT WeekStartingDate,

CASE

WHEN TotalHours <=24 THEN 1

WHEN TotalHours >24 AND TotalHours <=48 THEN 2

ELSE 3

END AS TimeSlot

FROM @test-2

) classify

GROUP BY WeekStartingDate, TimeSlot

) preagg

GROUP BY TimeSlot

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pandeharsh Ten Centuries Points: 1106 More actions · Answer 8

Thanks,I got it.

[font="Verdana"]Regards
Kumar Harsh[/font]

:unsure: