July 2, 2007 at 3:11 am
all done and fixed. thanks 🙂
July 2, 2007 at 3:57 am
If I've understood your SQL then the below may help. Be warned I haven't checked the syntax and obviously haven't any data. Basically the principle is make your original UNION query a sub-query and do a SUM on the three calculated fields at the end and GROUP BY the rest. Should work but may be quite slow if you've lots of data. Have you considered generating temporary tables for each quarter or month? (I'm guessing you are trying to create some form of summary report??)
Good luck
Declare @Date1 as datetime
Declare @Date2 as datetime
SET @Date1 = '01 May 2007'
SET @Date2 = '29 June 2007'
SELECT a.tbl_provider.provider_ID , a.tbl_Provider.Provider_Name, a.tbl_Form.Form_Year, a.tbl_Form.Form_Name,
a.tbl_Provider.Consortia_ID, a.tbl_Provider.LEA_ID, SUM(a.Total_Quarter), SUM(a.Total_Quarter_Dist), SUM(a.Total_Quarter_Dist_Out) FROM
(SELECT tbl_provider.provider_ID , tbl_Provider.Provider_Name, tbl_Form.Form_Year, tbl_Form.Form_Name,
tbl_Provider.Consortia_ID, tbl_Provider.LEA_ID,
COUNT(*) as Total_Quarter,
COUNT(distinct tbl_user.user_id) AS Total_Quarter_Dist,
'' as Total_Quarter_Dist_Out
FROM Admin_UserSessionLog INNER JOIN
tbl_User ON Admin_UserSessionLog.User_ID = tbl_User.User_ID INNER JOIN
tbl_provider ON tbl_User.mgmtUse_ProviderID = tbl_provider.provider_ID INNER JOIN
tbl_Form on tbl_User.Form_ID = tbl_Form.Form_ID
WHERE (Admin_UserSessionLog.LoggedIn > CONVERT(DATETIME, @Date1, 102))
AND (Admin_UserSessionLog.LoggedIn < CONVERT(DATETIME, @Date2, 102))
GROUP BY tbl_provider.provider_ID, tbl_Provider.Provider_Name,tbl_Form.Form_Year, tbl_form.form_name,
tbl_Provider.Consortia_ID, tbl_Provider.LEA_ID
UNION
SELECT tbl_provider.provider_ID , tbl_Provider.Provider_Name, tbl_Form.Form_Year, tbl_Form.Form_Name,
tbl_Provider.Consortia_ID, tbl_Provider.LEA_ID,
'' as Total_Quarter,
'' AS Total_Quarter_Dist,
COUNT(distinct tbl_user.user_id) AS Total_Quarter_Dist_Out
FROM Admin_UserSessionLog INNER JOIN
tbl_User ON Admin_UserSessionLog.User_ID = tbl_User.User_ID INNER JOIN
tbl_provider ON tbl_User.mgmtUse_ProviderID = tbl_provider.provider_ID INNER JOIN
tbl_Form on tbl_User.Form_ID = tbl_Form.Form_ID
WHERE (Admin_UserSessionLog.LoggedIn > CONVERT(DATETIME, @Date1, 102))
AND (Admin_UserSessionLog.LoggedIn < CONVERT(DATETIME, @Date2, 102))
AND ((DATEPART(hh, Admin_UserSessionLog.LoggedIn) < 8 AND DATEPART(hh, Admin_UserSessionLog.LoggedIn) > 15) OR DATEPART(dw, Admin_UserSessionLog.LoggedIn) in (1,7))
GROUP BY tbl_provider.provider_ID, tbl_Provider.Provider_Name,tbl_Form.Form_Year, tbl_form.form_name,
tbl_Provider.Consortia_ID, tbl_Provider.LEA_ID
ORDER BY provider_name, form_year, Form_Name) a GROUP BY a.tbl_provider.provider_ID , a.tbl_Provider.Provider_Name, a.tbl_Form.Form_Year, a.tbl_Form.Form_Name,
a.tbl_Provider.Consortia_ID, a.tbl_Provider.LEA_ID
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply