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