July 2, 2007 at 3:10 am
All fixed and done. Thanks a lot 🙂
July 2, 2007 at 3:56 am
You need to put the extra bit in the where clause into a CASE. Something like:
SELECT P.provider_ID
,P.Provider_Name
,F.Form_Year
,F.Form_Name
,P.Consortia_ID
,P.LEA_ID
,COUNT(*) AS Total_Quarter
,COUNT(DISTINCT U.[User_id]) AS Total_Quarter_Dist
,COUNT(DISTINCT CASE
WHEN (DATEPART(hh, L.LoggedIn) < 8 AND DATEPART(hh, L.LoggedIn) > 15)
OR DATEPART(dw, L.LoggedIn) in (1,7)
THEN U.[User_Id]
END) AS Total_Quarter_Dist_Out
FROM Admin_UserSessionLog L
JOIN tbl_User U
ON L.[User_ID] = U.[User_ID]
JOIN tbl_provider P
ON U.mgmtUse_ProviderID = P.provider_ID
JOIN tbl_Form F
ON U.Form_ID = F.Form_ID
WHERE L.LoggedIn > DATEADD(day, 0, DATEDIFF(day, 0, @Date1))
AND L.LoggedIn < DATEADD(day, 0, DATEDIFF(day, 0, @Date2))
GROUP BY P.provider_ID, P.Provider_Name, F.Form_Year, F.Form_Name, P.Consortia_ID, P.LEA_ID
July 2, 2007 at 4:16 am
Thanks a lot.
That worked really well
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply