June 29, 2011 at 2:32 pm
I have read a few Misc post on creating a Cross Tab Query and I understand the concept but I am having a hard time putting the entire query together. I was attempting to use something similar to this stored procedure because I will have dynamic date ranges. Again teh problem I am not getting a solution to (at least in my mind) is how to pull this together with the rest of the report.
I have a SP that collect the data needed but it is presenting it in rows rather then in columns. so for instance in the Query Results image attached you can see that The employee Dave has 6 records (3 per day) what we would want to show for this is actually 2 rows One for each JobName Column and we want to see (in this case 7 Columns one for each day) that would house the PunchTotal summed for each JobName. So Monday would have 3.25 hours in the row for PW-Job1 and .25 hour in the Monday column for PW-Job2.
The days or columns if you will come from a parameter passed and are obtained in the SQL Query I am using now. I am having a hard time understanding how to place this all together in one procedure. Comming from a VB programming background I tend to want to grab some data and drop the results into a while loop and build additional data into the final results. I would not mind doing it that way but I am also not sure of the correct formatting for that either.
Any help directly or even pointing me to an article that may help would be appreciated. Here is my current SP
USE [TTSTimeClock]
GO
/****** Object: StoredProcedure [dbo].[TTS_RptPrevailingWages] Script Date: 06/29/2011 15:59:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[TTS_RptPrevailingWages]
@BureauID nvarchar(36),
@CompanyID nvarchar(36),
@DivisionID nvarchar(10) ,
@Department as nvarchar(90),
@punchDate smalldatetime,
@PayFrequency as nvarchar(10),
@PeriodDays integer,
@EmployeeID nvarchar(20) = ''
As
--with DayTotals as(
select
Companies.CompanyID,
Companies.CompanyName,
EmpTime.DivisionID,
Divisions.DivsionNumber,
EmpTime.DivisionName,
@PayFrequency as PayFrequency,
EmpTime.EmployeeID,
EmpData.EmployeeFirstName AS First,
EmpData.EmployeeLastName AS Last,
EmpData.employeetypeid AS EmpId,
-- Format the Date as MM/DD DOW or 2Digit Month & 2Digit Day and the 3Char Day of the week Uppercase
convert(varchar(5),EmpTime.PunchDate,101) + ' ' + upper(left(datename(dw,EmpTime.Punchdate),3))as PunchDate,
-- Format the in and out time as non military time with AM or PM No Dates
-- Also Need to handle no date functions when the value is a string such as
-- Missing, Hours or Dollars
-- In Time
case when isdate(EmpTime.TimeIn) = 1 then
substring(convert(varchar(25),cast(EmpTime.TimeIn as datetime)),13,7) --as TimeIn
else
EmpTime.TimeIn
end as TimeIn,
--Out Time
case when isdate(EmpTime.TimeOut) = 1 then
substring(convert(varchar(25),cast(EmpTime.TimeOut as datetime)),13,7) --as TimeIn
else
EmpTime.TimeOut
end as TimeOut,
EmpTime.RegHours,
EmpTime.OtherHours,
EmpTime.OTHours,
EmpTime.PunchTotal,
isnull(EmpTime.DepartmentName,'--')as DepartmentName,
isnull(EmpTime.JobName,'--') as JobName,
EmpTime.PunchType,
--EmpTime.ActualTimeIn handle date formatting and nulls
case when isdate(EmpTime.ActualTimeIn) = 1 then
substring(convert(varchar(25),cast(EmpTime.ActualTimeIn as datetime)),13,7) --as TimeIn
else
isnull(EmpTime.ActualTimeIn,'--')
end as ActualTimeIn,
--EmpTime.ActualTimeOut handle date formatting and nulls
case when isdate(EmpTime.ActualTimeOut) = 1 then
substring(convert(varchar(25),cast(EmpTime.ActualTimeOut as datetime)),13,7) --as TimeIn
else
isnull(EmpTime.ActualTimeOut ,'--')
end as ActualTimeOut,
-- Format this field as Money 000.00 ALSO if any null values set
-- them to 0 This can happen as ther are parents for everyday but
-- May be no child records if no action for that day.
cast(isnull(EmpTime.TotalDollars,0)as money) AS TotalDollars,
cast(isnull(EmpTime.Payrate,0)as money) AS Payrate,
-- As Periodstart for report Range
convert(varchar(10),(SELECT DISTINCT PeriodStart
FROM TTS_EmpTimeDayTotal
WHERE(BureauID = @BureauID) AND (CompanyID = @CompanyID) AND ( (DivisionID = @DivisionID))
AND (PunchDate = @punchDate)and periodend = dateadd(d,(@PeriodDays - 1),(periodstart))),101) as PeriodStart,
-- As PeriodEnd for report Range
convert(varchar(10),(SELECT DISTINCT PeriodEnd
FROM TTS_EmpTimeDayTotal
WHERE(BureauID = @BureauID) AND (CompanyID = @CompanyID) AND ( (DivisionID = @DivisionID))
AND (PunchDate = @punchDate)and periodend = dateadd(d,(@PeriodDays-1),(periodstart))),101) as PeriodEnd,
-- Grouping weeks together for Bi=weekly Reports. Can't use DatePart for week
-- Because the week may or may not start on Sunday So we create our own week number
'WeekNo' =
case
when Emptime.Punchdate <= dateadd(day,7,(SELECT DISTINCT PeriodStart
FROM TTS_EmpTimeDayTotal
WHERE(BureauID = @BureauID) AND (CompanyID = @CompanyID) AND ( (DivisionID = @DivisionID))
AND (PunchDate = @punchDate)and periodend = dateadd(d,(@PeriodDays - 1),(periodstart)))) then '1'
else '2'
end
From TTS_EmpTime as EmpTime
INNER JOIN TTS_PayrollEmployees AS EmpData
ON EmpTime.EmployeeID = EmpData.EmployeeID
inner JOIN TTS_Companies AS Companies
ON EmpTime.CompanyID = Companies.CompanyID and EmpTime.BureauID = Companies.BureauID
inner JOIN TTS_Divisions AS Divisions
ON EmpTime.CompanyID = Divisions.CompanyID and EmpTime.BureauID = Divisions.BureauID and EmpTime.DivisionID = Divisions.DivisionID
where
EmpTime.BureauID = @BureauID
AND EmpTime.CompanyID = @CompanyID
AND (EmpTime.DivisionID = @DivisionID)
AND EmpTime.DepartmentName = @Department
-- If the employee is "In Active" or "Deleted" we only want to show them if they
-- have time recorded. Never show employees that are hidden. Here we dont need
-- to worry about not showing time records for inactive or deleted Employees
-- since we are collecting records from the EmpTime table first If there are
-- records we show them .
--AND Empdata.ActiveYN='True'
--AND Empdata.Deleted= 'False'
AND (Empdata.HiddenYN = 'false' or Empdata.HiddenYN is null)
AND Emptime.punchdate between
-- Period start date
(SELECT DISTINCT PeriodStart
FROM TTS_EmpTimeDayTotal
WHERE(BureauID = @BureauID) AND (CompanyID = @CompanyID) AND ( (DivisionID = @DivisionID))
AND (PunchDate = @punchDate)and periodend = dateadd(d,(@PeriodDays - 1),(periodstart)))
AND -- Period End Date NOTE I add a day to the enddate because the dates time is 12am
-- and we need the punches for the last day too
(SELECT DISTINCT PeriodEnd
FROM TTS_EmpTimeDayTotal
WHERE(BureauID = @BureauID) AND (CompanyID = @CompanyID) AND ( (DivisionID = @DivisionID))
AND (PunchDate = @punchDate)and periodend = dateadd(d,(@PeriodDays-1),(periodstart))) +1
-- Optional all employees or just one
AND (( @EmployeeID = '') or (Emptime.EmployeeID = @EmployeeID))
order by Empdata.employeetypeid,Emptime.punchdate
June 29, 2011 at 2:42 pm
please provide table def and sample data in a ready to use format as described in the first link in my signature.
You might also want to have a look at the CrossTab or DynamicCrossTab references in my signature if you prefer to give it another try by yourself.
June 29, 2011 at 3:24 pm
I can create the tables to file no problem but I am having a hard time trying to create test data as the data is in 4 tables and normalized
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply