#114 New fnRptHourlyCount
Create a new statistical report via the web to help
the TKManager determine peak usage for their labs. The
report should have the following columns:
SiteNo, WkNo, Date, DOW, Hour (0 - 23), ClockIn Count,
Clock Out Count, Raw Usage Minutes
Start with the following prototype code:
Select
SiteNo,
rtrim(convert(char(10), TKLog.ClockIn, 1)) As
Date,
substring(DATENAME(dw, TKLog.ClockIn),1,3) As
DOW,
substring(convert(char(20), TKLog.ClockIn,
120),12,2) As Hour,
count(substring(convert(char(2),
TKLog.ClockIn, 120),12,2)) as ClkIn
From TKLog
where SiteNo = 7
group by SiteNo,
rtrim(convert(char(10), TKLog.ClockIn, 1)),
substring(DATENAME(dw, TKLog.ClockIn),1,3),
substring(convert(char(20), TKLog.ClockIn,
120),12,2)
order by SiteNo, Date, DOW, Hour
Logged In: YES
user_id=19972
Done first production draft SQL, Brad need to write Web
report for it now. Syntax:
EXEC spRptHourlyCount @pSiteNo=1 -- A siteNo for TKMgr
or
EXEC spRptHourlyCount @pSiteNo=0 -- siteNo 0 for TKAdmin
Detail Function code =
CREATE FUNCTION fnRptHourlyCount ( @pSiteNo tinyint)
RETURNS @tblHourlyStat TABLE (
SiteNo tinyint,
WkNo tinyint,
DOW char(3),
[Date] char(8), -- smalldatetime,
[Hour] tinyint,
ClkInCnt int,
ClkOutCnt int,
ElapsedMin bigint
) AS BEGIN
IF (@pSiteNo > 0) BEGIN -- Report for individual site
-- Creating the skeleton table
INSERT @tblHourlyStat
Select
SiteNo,
DatePart(week,TKLog.ClockIn)-wkTbl.WkNo+1 as WkNo,
substring(DATENAME(dw, TKLog.ClockIn),1,3) As DOW,
rtrim(convert(char(10), TKLog.ClockIn, 1)) As
[Date],
datepart(hour, TKLog.ClockIn) as [Hour],
count(datepart(hour, TKLog.ClockIn)) as ClkInCnt,
isnull(aTbl.aClkOut, 0) as ClkOutCnt,
isnull(bTbl.bElapse, 0) as ElapsedMin
From TKLog
left join (
Select DatePart(week,Max(StartDate)) as
WkNo from CurrentSemester
) WkTbl on (DatePart(week,TKLog.ClockIn)
>=WkTbl.WkNo )
left join ( -- ClockOut count with no Runner table
Select
TKLog.SiteNo as aSiteNo,
rtrim(convert(char(10),
TKLog.ClockIn, 1)) As aDate,
datepart(hour, TKLog.ClockOut) As
aHour,
count(datepart(hour,
TKLog.ClockOut)) as aClkOut
from TKLog
where datepart(hour,
TKLog.ClockOut) > 0
AND TKLog.SiteNo = @pSiteNo
group by
TKLog.SiteNo,
rtrim(convert(char(10),
TKLog.ClockIn, 1)),
datepart(hour,
TKLog.ClockOut)
) aTbl on ( TKLog.SiteNo=aTbl.aSiteNo AND rtrim
(convert(char(10), TKLog.ClockIn, 1))=aTbl.aDate
AND datepart(hour, TKLog.ClockIn)
=aTbl.aHour)
left join ( -- ElapseTime with no Runner table
Select
TKLog.SiteNo as bSiteNo,
rtrim(convert(char(10),
TKLog.ClockIn, 1)) As bDate,
datepart(hour, TKLog.ClockIn) As
bHour,
sum(DATEDIFF(minute, TKLog.ClockIn,
TKLog.ClockOut) ) as bElapse
from TKLog
where datepart(hour,
TKLog.ClockOut) > 0
AND datediff(minute, TKLog.ClockIn,
TKLog.ClockOut) >= 0
AND TKLog.SiteNo = @pSiteNo
group by
TKLog.SiteNo,
rtrim(convert(char(10),
TKLog.ClockIn, 1)),
datepart(hour,
TKLog.ClockIn)
) bTbl on (TKLog.SiteNo=bTbl.bSiteNo AND rtrim
(convert(char(10), TKLog.ClockIn, 1))=bTbl.bDate
AND datepart(hour, TKLog.ClockIn)
=bTbl.bHour)
where SiteNo = @pSiteNo
group by
SiteNo,
DatePart(week,TKLog.ClockIn)-wkTbl.WkNo+1,
rtrim(convert(char(10), TKLog.ClockIn, 1)),
substring(DATENAME(dw, TKLog.ClockIn),1,3),
datepart(hour, TKLog.ClockIn),
aTbl.aClkOut,
bTbl.bElapse
END ELSE BEGIN -- REPORT FOR ALL SITES
INSERT @tblHourlyStat
Select
SiteNo,
0 as WkNo,
substring(DATENAME(dw, TKLog.ClockIn),1,3) As DOW,
rtrim(convert(char(10), TKLog.ClockIn, 1)) As
[Date],
datepart(hour, TKLog.ClockIn) as [Hour],
count(datepart(hour, TKLog.ClockIn)) as ClkInCnt,
isnull(aTbl.aClkOut, 0) as ClkOutCnt,
isnull(bTbl.bElapse, 0) as ElapsedMin
From TKLog
left join (
Select DatePart(week,Max(StartDate)) as
WkNo from CurrentSemester
) WkTbl on (DatePart(week,TKLog.ClockIn)
>=WkTbl.WkNo )
left join ( -- ClockOut count with no Runner table
Select
TKLog.SiteNo as aSiteNo,
rtrim(convert(char(10),
TKLog.ClockIn, 1)) As aDate,
datepart(hour, TKLog.ClockOut) As
aHour,
count(datepart(hour,
TKLog.ClockOut)) as aClkOut
from TKLog
where datepart(hour,
TKLog.ClockOut) > 0
-- AND TKLog.SiteNo = @pSiteNo
group by
TKLog.SiteNo,
rtrim(convert(char(10),
TKLog.ClockIn, 1)),
datepart(hour,
TKLog.ClockOut)
) aTbl on ( TKLog.SiteNo=aTbl.aSiteNo AND rtrim
(convert(char(10), TKLog.ClockIn, 1))=aTbl.aDate
AND datepart(hour, TKLog.ClockIn)
=aTbl.aHour)
left join ( -- ElapseTime with no Runner table
Select
TKLog.SiteNo as bSiteNo,
rtrim(convert(char(10),
TKLog.ClockIn, 1)) As bDate,
datepart(hour, TKLog.ClockIn) As
bHour,
sum(DATEDIFF(minute, TKLog.ClockIn,
TKLog.ClockOut) ) as bElapse
from TKLog
where datepart(hour,
TKLog.ClockOut) > 0
AND datediff(minute, TKLog.ClockIn,
TKLog.ClockOut) >= 0
-- AND TKLog.SiteNo = @pSiteNo
group by
TKLog.SiteNo,
rtrim(convert(char(10),
TKLog.ClockIn, 1)),
datepart(hour,
TKLog.ClockIn)
) bTbl on (TKLog.SiteNo=bTbl.bSiteNo AND rtrim
(convert(char(10), TKLog.ClockIn, 1))=bTbl.bDate
AND datepart(hour, TKLog.ClockIn)
=bTbl.bHour)
-- where SiteNo = @pSiteNo
group by
SiteNo,
DatePart(week,TKLog.ClockIn)-wkTbl.WkNo+1,
rtrim(convert(char(10), TKLog.ClockIn, 1)),
substring(DATENAME(dw, TKLog.ClockIn),1,3),
datepart(hour, TKLog.ClockIn),
aTbl.aClkOut,
bTbl.bElapse
END
RETURN
END
Logged In: YES
user_id=52284
Completed... Need to create PDF for Report...