I have a requirement for a user to access time sheet information by accessing a SharePoint portal listing. They will be required enter a date range. The login will be based on their domain access.
Based on the query below, I can return a result set which gives the user all rows they'll need, grouped accordingly and based on a date range. Except for a required billable percentage figure. A billable percentage is defined as billable hours / total hours * 100 or based on the query below;
day1 through 7_hr1 [Worked_hrs] where the project <> admin (@.Worked_hrs_B), divided by the total [Worked_hrs](@.Total_Worked_hrs).
I'm pretty sure I need to use a cursor which will tally all the @.Worked_hrs_NB rows, and another cursor which will tally @.Total_Worked_hrs rows and then divide the two variables * 100, to return it in the @.Billable variable.
This is where I get lost. I'm ashamed to say that my TSQL is rusty & weak at best. Rather than confuse anyone with my idea of cursor syntax, I left it out of this query, & just included the variables I assumed would fit.
A down'n'dirty cursor lesson would be most appreciated (If that's what this needs). Thanks in advance for your help.
DECLARE
@.pe_date1 AS SMALLDATETIME, --Prompt
@.pe_date2 AS SMALLDATETIME, --Prompt
@.emp_id AS CHAR (30), --Login
@.Worked_hrs_B AS INT,
@.Total_Worked_hrs AS INT,
@.Precent AS INT
SET @.pe_date1 = '6/01/2004'
SET @.pe_date2 = '7/01/2005'
SET @.emp_id = 'degajx'
************************************************************************
SET @.Worked_hrs_B = '' Here's where I get lost
SET @.Total_Worked_hrs = '' with the variables & the
SET @.Precent = '' --Make Header Info cursor to populate them.
************************************************************************
SELECT
pjlabhdr.docnbr
, pjlabhdr.pe_date
, pjlabdet.project
, pjlabdet.pjt_entity
, pjlabdet.ld_desc
, (
pjlabdet.day1_hr1 +
pjlabdet.day2_hr1 +
pjlabdet.day3_hr1 +
pjlabdet.day4_hr1 +
pjlabdet.day5_hr1 +
pjlabdet.day6_hr1 +
pjlabdet.day7_hr1
) AS [Worked_hrs]
, pjemploy.manager1
, ltle.employeename AS [Manager] --Make Header Info
, SubAcct.Descr --Make Header Info ************************************************************************
, @.Percent AS [Billable %] --An accurate return, though repeating would be fine.
************************************************************************
FROM IEM_Cut.dbo.PJLABHDR pjlabhdr
INNER JOIN
IEM_Cut.dbo.PJEMPLOY
ON pjlabhdr.employee = pjemploy.employee
Inner JOIN
labortool..laboremployee ltle (NOLOCK)
ON ltle.empid = pjemploy.manager1
LEFT OUTER JOIN
IEM_Cut.dbo.PJLABDET pjlabdet
ON pjlabhdr.docnbr = pjlabdet.docnbr
LEFT OUTER JOIN
IEM_Cut.dbo.SubAcct SubAcct
ON pjemploy.gl_subacct = SubAcct.Sub
WHERE
( pjlabdet.day1_hr1 <> 0
OR pjlabdet.day2_hr1 <> 0
OR pjlabdet.day3_hr1 <> 0
OR pjlabdet.day4_hr1 <> 0
OR pjlabdet.day5_hr1 <> 0
OR pjlabdet.day6_hr1 <> 0
OR pjlabdet.day7_hr1 <> 0
)
AND pjlabhdr.CpnyID_home = 'IEM'
AND pjlabhdr.pe_date BETWEEN CONVERT (varchar, @.pe_date1 , 107) AND CONVERT (varchar, @.pe_date2 , 107)
AND pjlabhdr.employee = @.emp_id
ORDER BY
pjlabhdr.pe_date ASC --Group
, pjlabhdr.docnbr ASC --Group
, pjlabdet.project ASC --Group
, pjlabdet.pjt_entity ASC --Group
You don't really need a cursor. You can write two queries that performs the required SUM operations and divide the results. For example:
select (select sum(...) from ....)/((select sum(...) from ...)*100.0) as billable_per
See Books Online for more details on how to write scalar queries, group by, expressions etc.
sql