November 13, 2013 at 9:35 am
Hi,
I have a query that returns data like this:
Name emp id TypeCode TValue TypeCode TValue TypeCode Value
Don King 1234 S 8
Don King 1234 V 8
Don King 1234 H 8
This is for a employee that for a 2 week pay period has 8 hours vacation, 8 hours sick time and 8 hours holiday. These are in 3 different records in the database.
What I would like is for the below result where they are all on one line.
Name emp id TypeCode TValue TypeCode TValue TypeCode Value
Don King 1234 S 8 V 8 H 8
Thanks for any help in the right direction.
Dave
November 13, 2013 at 9:49 am
Take a look at the links in my signature about cross tabs. It explains how to do this thing quite nicely.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 13, 2013 at 10:40 am
I am already doing a case statement to get to where Im at right now for the columns. The original data is in one column so I have split it into multiple columns, but dont know how to go from there.
Thanks
November 13, 2013 at 11:39 am
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 13, 2013 at 1:52 pm
Here are the create table statements:
CREATE TABLE #tblTimeOffRequest
(
[EmpId] [int] NULL,
[Type] [varchar](40) NULL,
[From_Date] [smalldatetime] NULL,
[To_Date] [smalldatetime] NULL,
[Total_Hours] [decimal](5, 2) NULL,
[Status] [varchar](20) DEFAULT ('Pending'),
)
INSERT INTO #tblTimeOffRequest
(EmpID, Type, From_Date, To_Date, Total_Hours, Status)
SELECT '500','Sick','Oct 24 2013 12:00AM','Oct 24 2013 12:00AM','4.00','Approved' UNION ALL
SELECT '500','Sick','Oct 25 2013 12:00AM','Oct 25 2013 12:00AM','8.00','Approved' UNION ALL
SELECT '500','Vacation','Oct 21 2013 12:00AM','Oct 22 2013 12:00AM','16.00','Pending' UNION ALL
SELECT '125','Vacation','Oct 21 2013 12:00AM','Oct 21 2013 12:00AM','8.00','Approved' UNION ALL
SELECT '125','Sick','Oct 14 2013 12:00AM','Oct 14 2013 12:00AM','8.00','Approved' UNION ALL
SELECT '110','Pay In Leiu','Oct 23 2013 12:00AM','Oct 25 2013 12:00AM','24.00','Approved' UNION ALL
SELECT '110','Sick','Oct 16 2013 12:00AM','Oct 16 2013 12:00AM','8.00','Pending'
-----------------------------------
CREATE TABLE #tblEmployee
(
[EmpId] [int] IDENTITY(1,1) NOT NULL,
[EmpNum] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EmpFirstName] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmpLastName] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Active] [bit] NULL CONSTRAINT [DF_tblEmployee_Active] DEFAULT (1),
[Location] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PayType] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
SET IDENTITY_INSERT #tblEmployee ON
INSERT INTO #tblEmployee
(EmpID, EmpNum, EmpFirstName, EmpLastName, Active, Location, PayType)
SELECT '500','2380','Don','King','1','PEI','S' UNION ALL
SELECT '125','21038','Jam','Jahana','1','PEI','S' UNION ALL
SELECT '110','21010','Bill','Smith','1','PEI','S' UNION ALL
SELECT '27','2019','Edward','Dicks','1','PEI','S' UNION ALL
SELECT '28','2143','Rowdy','Bergross','1','PEI','S' UNION ALL
SELECT '29','16164','Corinna','Tillstoy','1','CML','S' UNION ALL
SELECT '30','2186','Paula','Pie','1','PEI','S'
-----------------------------------------------------------------
This is the query im running:
declare @FromDate as smalldatetime
declare @Todate as smalldatetime
declare @Loc as varchar(25)
set @Fromdate = '10/13/2013'
SET @ToDate = '10/26/2013'
SET @Loc = 'PEI'
SELECT
B.Location,
'EXEMPT' as 'Batch ID',
A.EmpId as 'File #',
B.EmpLastName as 'Last Name',
B.EmpFirstName as 'First Name',
'y' as 'Cancel Pay',
NULL as 'Pay #',
NULL as 'Reg Earnings',
2 AS 'Rate Code',
NULL as 'Tax Frequency',
NULL as 'Reg Hours',
CASE WHEN Type = 'Sick' THEN 'S' end as 'Hours 4 Code',
SUM(CASE WHEN Type = 'Sick' THEN A.Total_Hours end) as 'Hours 4 Amount',
CASE WHEN Type = 'Vacation' THEN 'V' end as 'Hours 4 Code',
SUM(CASE WHEN Type = 'Vacation' THEN A.Total_Hours end) as 'Hours 4 Amount',
CASE WHEN (Type != 'Vacation' AND Type != 'Sick') THEN 'O' end as 'Hours 4 Code',
SUM(CASE WHEN (Type != 'Vacation' AND Type != 'Sick') THEN A.Total_Hours end) as 'Hours 4 Amount'
FROM #tblTimeOffRequest A
INNER Join
#tblEmployee B
ON
A.EmpId = B.EmpId
WHERE From_Date between @FromDate AND @ToDate
AND
Location = @Loc
AND
PayType = 'S'
GROUP BY Location,A.EmpId,EmpLastName,EmpFirstName,A.Type,A.Total_Hours
----------------------------------------------------------------
I am getting output like this:
Co Code BatchId File# LastName First Cancel Pay Pay# Reg Earn RateCode Tax Freq Reg hrs Hrs4Code hrs4Amthrs4Code hrs4Amt hrs4codehrs4Amt
K00EXEMPT500KingDonyNULLNULL2NULLNULLS 4.00NULL NULLNULLNULL
K00EXEMPT500KingDonyNULLNULL2NULLNULLS8.00NULLNULLNULLNULL
K00EXEMPT500KingDonyNULLNULL2NULLNULLNULLNULL V16.00NULLNULL
I want the 3 lines above to be on 1 line with summing if more than 1 of same type. like if 2 sick days, add together.
Let me know if you need anything else.
Thanks
November 13, 2013 at 2:37 pm
Something like this should do it.
SELECT
B.Location,
'EXEMPT' as 'Batch ID',
A.EmpId as 'File #',
B.EmpLastName as 'Last Name',
B.EmpFirstName as 'First Name',
'y' as 'Cancel Pay',
NULL as 'Pay #',
NULL as 'Reg Earnings',
2 AS 'Rate Code',
NULL as 'Tax Frequency',
NULL as 'Reg Hours',
case when SUM(CASE WHEN Type = 'Sick' THEN A.Total_Hours end) IS not null then 'S' end as 'Hours 4 Code',
SUM(CASE WHEN Type = 'Sick' THEN A.Total_Hours end) as 'Sick Hours',
case when SUM(CASE WHEN Type = 'Vacation' THEN A.Total_Hours end) IS not null then 'V' end as 'Hours 4 Code',
SUM(CASE WHEN Type = 'Vacation' THEN A.Total_Hours end) as 'Vacation Hours',
case when SUM(CASE WHEN (Type != 'Vacation' AND Type != 'Sick') THEN A.Total_Hours end) is not null then 'O' end as 'Hours 4 Code',
SUM(CASE WHEN (Type != 'Vacation' AND Type != 'Sick') THEN A.Total_Hours end) as 'Unknown Hours'
FROM #tblTimeOffRequest A
INNER Join #tblEmployee B ON A.EmpId = B.EmpId
WHERE From_Date between @FromDate AND @ToDate
AND Location = @Loc
AND PayType = 'S'
GROUP BY Location, A.EmpId, EmpLastName, EmpFirstName
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 13, 2013 at 2:53 pm
Im still getting the same output with the new code?
November 13, 2013 at 3:18 pm
I am not sure what the "new code" added means. This returns exactly same set as Sean but using PIVOT. Just a different approach. I compared the two in Actual Query Execution plan and Sean's had a lower query cost.
SELECT
pvt.Location,
'EXEMPT' as 'Batch ID',
pvt.EmpId as 'File #',
pvt.EmpLastName as 'Last Name',
pvt.EmpFirstName as 'First Name',
'y' as 'Cancel Pay',
NULL as 'Pay #',
NULL as 'Reg Earnings',
2 AS 'Rate Code',
NULL as 'Tax Frequency',
NULL as 'Reg Hours',
'Hours 4 Code' = CASE WHEN S IS NOT NULL
THEN 'S'
ELSE NULL
END,
'Sick Hours' = S,
'Hours 4 Code' = CASE WHEN V IS NOT NULL
THEN 'V'
ELSE NULL
END,
'Vacation Hours' = V,
'Hours 4 Code' = CASE WHEN O IS NOT NULL
THEN 'O'
ELSE NULL
END,
'Unknown Hours' = O
FROM (
SELECT
e.EmpID
,e.Location
,e.EmpFirstName
,e.EmpLastName
,Type = CASE WHEN Type = 'Sick'
THEN 'S'
WHEN Type = 'Vacation'
THEN 'V'
ELSE 'O'
END
,Total_hours = SUM(total_hours)
FROM #tblEmployee e
JOIN #tblTimeOffRequest r
ON e.EmpId = r.EmpId
GROUP BY
e.EmpID
,e.Location
,e.EmpFirstName
,e.EmpLastName
,CASE WHEN Type = 'Sick'
THEN 'S'
WHEN Type = 'Vacation'
THEN 'V'
ELSE 'O'
END
) PersonalTime
PIVOT
( MAX(total_hours) FOR Type IN (, [V],[O])) as pvt
November 13, 2013 at 3:33 pm
David Tooker (11/13/2013)
Im still getting the same output with the new code?
Here is the output I get with the code I posted.
LocationBatch IDFile #Last NameFirst NameCancel PayPay #Reg EarningsRate CodeTax FrequencyReg HoursHours 4 CodeSick HoursHours 4 CodeVacation HoursHours 4 CodeUnknown Hours
PEIEXEMPT110SmithBillyNULLNULL2NULLNULLS8.00NULLNULLO24.00
PEIEXEMPT125JahanaJamyNULLNULL2NULLNULLS8.00V8.00NULLNULL
PEIEXEMPT500KingDonyNULLNULL2NULLNULLS12.00V16.00NULLNULL
You should be able to paste that into excel and it should be formatted for columns correctly.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 13, 2013 at 3:40 pm
brad.mason5 (11/13/2013)
I am not sure what the "new code" added means. This returns exactly same set as Sean but using PIVOT. Just a different approach. I compared the two in Actual Query Execution plan and Sean's had a lower query cost.
SELECTpvt.Location,
'EXEMPT' as 'Batch ID',
pvt.EmpId as 'File #',
pvt.EmpLastName as 'Last Name',
pvt.EmpFirstName as 'First Name',
'y' as 'Cancel Pay',
NULL as 'Pay #',
NULL as 'Reg Earnings',
2 AS 'Rate Code',
NULL as 'Tax Frequency',
NULL as 'Reg Hours',
'Hours 4 Code' = CASE WHEN S IS NOT NULL
THEN 'S'
ELSE NULL
END,
'Sick Hours' = S,
'Hours 4 Code' = CASE WHEN V IS NOT NULL
THEN 'V'
ELSE NULL
END,
'Vacation Hours' = V,
'Hours 4 Code' = CASE WHEN O IS NOT NULL
THEN 'O'
ELSE NULL
END,
'Unknown Hours' = O
FROM (
SELECT
e.EmpID
,e.Location
,e.EmpFirstName
,e.EmpLastName
,Type = CASE WHEN Type = 'Sick'
THEN 'S'
WHEN Type = 'Vacation'
THEN 'V'
ELSE 'O'
END
,Total_hours = SUM(total_hours)
FROM #tblEmployee e
JOIN #tblTimeOffRequest r
ON e.EmpId = r.EmpId
GROUP BY
e.EmpID
,e.Location
,e.EmpFirstName
,e.EmpLastName
,CASE WHEN Type = 'Sick'
THEN 'S'
WHEN Type = 'Vacation'
THEN 'V'
ELSE 'O'
END
) PersonalTime
PIVOT
( MAX(total_hours) FOR Type IN (
, [V],[O])) as pvt
PIVOT can add some nasty performance issues. I just assumed not to use PIVOT here since the data was already aggregated. Even though your excellent code produces the same results, I tend to shy away from PIVOT as a general rule anymore having read Jeff Moden's articles about cross tabs. They tend to be more performant and are a bit easier to digest. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 14, 2013 at 9:18 am
I figured out what I was doing wrong. I added back to the end of statement
GROUP BY Location,A.EmpId,EmpLastName,EmpFirstName,A.Type,A.Total_Hours.
that caused all the rows to be displayed.
Thanks for all the help!!
November 14, 2013 at 9:27 am
David Tooker (11/14/2013)
I figured out what I was doing wrong. I added back to the end of statementGROUP BY Location,A.EmpId,EmpLastName,EmpFirstName,A.Type,A.Total_Hours.
that caused all the rows to be displayed.
Thanks for all the help!!
You are welcome. Glad you got it sorted out and thanks for letting us know. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply