Help converting queries - .Net to T-SQL

[WoodyGuthrie]

Hi Everyone,

I'm looking for some direction on how to go about using the queries below -- (which are currently in a .net application and are used to build a report that is rendered in Crystal) -- I need to convert the report to SSRS. So I need to figure out how to combine the queries for a set-based result grouped by associate.

The report is to be grouped by Associate (join on guidAssociateID) and is typically run for a single week and will be 1 page per associate. Section 1 (below), The header, will have a single row for the Associate. All other sections (4) will have multiple rows per associate.

For now I have the parameters for date/period, facility and department hard coded and it assumes 'all' associates for testing purposes. I'll eventually have parameters for:
UserID, StartDate, EndDate, Facility, Department, Shift, and Associate.

I hope I've provided enough detail for a start. As always, any help on this is greatly appreciated.

[b]
----------------------------------------------------------------------
-- BELOW FOR TESTING  - TEMPORARILY pass input parameter values     --
-- Set date/period parameter to select from 'tblPayrollWeekNumbers' --
--  Pass a Facility and Department guid                             --
---------------------------------------------------------------------- [/b]
declare @PeriodStart as datetime
declare @PeriodEnd as datetime
declare @ReportDate as datetime
declare @PeriodWeekNumber integer
declare @FacilityID as varchar(100)
declare @DepartmentID as varchar(100)

Set @PeriodWeekNumber=152


SELECT 
  @PeriodStart=dteWeekBegin
, @PeriodEnd=dteWeekEnd 
From tblPayrollWeekNumbers 
where intPayrollWeekNumber=@PeriodWeekNumber

Set @ReportDate=@PeriodEnd
Set @FacilityID='C1E8E517-FE12-40C3-8756-CF5FE659982D'
Set @DepartmentID='4B3E1DEB-6669-46A1-A223-484B119F2C8D'
[b]-- ABOVE IS FOR TESTING  - TEMPORARILY pass input parameter values --
------------------------------------------------------------------ [/b]

[b] -- Section 1 --
-- Set up Associate Header and Get Supplemental Data-- [/b]
SELECT 
  Convert(nvarchar(20),@PeriodStart,101) + ' - ' + Convert(nvarchar(20),@PeriodEnd,101) As Period
, Convert(nvarchar(20),@ReportDate,101)
, val.*
, vas.strStatus
, vas.VacationDaysAvailable as intVacationDays
, vas.PersonalDaysAvailable as intPersonalDays
, vas.curADWRate

FROM vwReportAssociateList val
Join vwReportAssociateSupplementalData vas
On val.guidAssociateID = vas.guidAssociateID

WHERE val.guidAssociateID 
in (	select distinct(vw_AssociateFilter.guidAssociateID) 
		from vw_AssociateFilter
		where theDate >= @PeriodStart AND theDate <= @PeriodEnd
	)
AND guidFacilityID=@FacilityID
AND guidDepartmentID=@DepartmentID


[b] -- Section 2 --
-- Attendance : For Each Date, Show Total Hours Worked, Scans In, Out, OnBreak, OffBreak By guidAssociateID -- [/b]
SELECT 
  asn.guidAssociateID
, ad.strFirstName
, ad.strLastName
, CONVERT(varchar, asn.dteShiftDate, 101) AS TheDate
, CONVERT(CHAR(5),asn.dteScanTime,8) as TheTime
, asn.intScanTypeCode
, sti.strScanTypeItem 
, ahw.dblHoursWorked 

FROM         
tblAssociateScans asn 
INNER JOIN tblAssociateDemographics ad
ON asn.guidAssociateID = ad.guidAssociateID 
INNER JOIN  tblScanTypeItems sti
ON asn.intScanTypeCode = sti.intScanTypeItemCode 
INNER JOIN tblAssociateHoursWorkedSummary ahw
ON asn.dteShiftDate = ahw.dteWorkDate 
AND asn.guidAssociateID = ahw.guidAssociateID 

WHERE     (asn.bitTimeApproved = 1)and (asn.dteShiftDate>= @PeriodStart
		AND asn.dteShiftDate <= @PeriodEnd)
		AND asn.guidAssociateID in 
				(
				select ad.guidAssociateID 
				from tblAssociateDemographics ad 
				where ad.guidDetailsHomeFacility=@FacilityID
				)

		AND asn.guidAssociateID in 
				(
				select guidAssociateID 
				from tblAssociateDemographics 
				where guidDetailsHomeDepartment=@DepartmentID
				)

GROUP BY 
  asn.dteShiftDate
, asn.dteScanTime
, ad.strFirstName
, ad.strLastName
, asn.intScanTypeCode
, sti.strScanTypeItem
, ad.guidAssociateID
, asn.guidAssociateID
, ahw.dblHoursWorked 

ORDER BY 
  asn.guidAssociateID
, TheDate
, TheTime
, asn.intScanTypeCode



[b] -- Section 3 --
-- Get Payroll Details for each Associate for Report Date Only -- [/b]
SELECT 
guidControlLogID
, guidAssociateID
, [Date Completed] as dateCompleted
, Location
, Activity
, Trailer
, Shipper
, Description
, [Employee(s)] as Employee
, curPay
, curtotalPay    

FROM   vwReportAssociatePayrollDetails 

WHERE [Date Completed]=@ReportDate -- report date
AND guidFacilityID=@FacilityID
AND guidDepartmentID=@DepartmentID

ORDER BY [Date Completed], guidControlLogID


[b] -- Section 4 --
-- Get each Earnings Code and Total for each Associate for each date in period -- [/b]
SELECT * FROM vwReportAssociateEarningsProduction where dtePayrollDate >= @PeriodStart AND dtePayrollDate <= @PeriodEnd
SELECT * FROM vwReportAssociateEarningsFixed where dtePayrollDate >= @PeriodStart AND dtePayrollDate <= @PeriodEnd
SELECT * FROM vwReportAssociateEarningsVacation where dtePayrollDate >= @PeriodStart AND dtePayrollDate <= @PeriodEnd
SELECT * FROM vwReportAssociateEarningsOvertime where dtePayrollDate >= @PeriodStart AND dtePayrollDate <= @PeriodEnd
SELECT * FROM vwReportAssociateEarningsBonus where dtePayrollDate >= @PeriodStart AND dtePayrollDate <= @PeriodEnd
SELECT * FROM vwReportAssociateEarningsCab where dtePayrollDate >= @PeriodStart AND dtePayrollDate <= @PeriodEnd
SELECT * FROM vwReportAssociateEarningsOther where dtePayrollDate >= @PeriodStart AND dtePayrollDate <= @PeriodEnd


[b] -- Section 5 -- 
-- Attendance Codes and related points for each associate for each date in period -- [/b]
SELECT 
AssociateID AS guidAssociateID
, [Attendance Date] AS dteDate
, [Attendance Item Letter] AS AttendanceLetter
, [Attendance Item Value] AS AttendanceValue 

FROM  dbo.vwReportAssociateAttendanceCodes 

WHERE [Attendance Date]  >= @PeriodStart AND [Attendance Date] <= @PeriodEnd

 

[AlexCuse]

Hi WoodyGuthrie -

I am not sure I know what you are asking here, but it doesn't look like you can get all your queries into 1 resultset (unless you want to do a ton of joins). However, SSRS will allow you to specify multiple datasets, and have one query feeding into each (FWIW, I suggest you convert these queries to stored procedures also).

If you need to return this all in one resultset (scripted sample data and an idea of your desired results would help us to help you with achieving this goal), I would suggest creating a view that combins all the tables you need, and having your queries/procs draw from there.

Also, there is a forum geared specifically toward Reporting Services: forum1462

I hope this help,s

Alex


[small]----signature below----[/small]
Numbers is hardly real and they never have feelings but you push too hard, even numbers got limits
Why did one straw break the camel's back? Here's the secret: the million other straws underneath it

My Crummy Web Page