Urgent Help with query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Urgent Help with query

Author

Topic

Giorgio
Starting Member

7 Posts

Posted - 2007-07-02 : 06:15:31

Declare @Date1 as datetime

Declare @Date2 as datetime

SET @Date1 = '01 May 2007'

SET @Date2 = '29 June 2007'

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

Kristen
Test

22859 Posts

Posted - 2007-07-02 : 06:20:56

What's wrong with it then?

Don't do this:

SET @Date1 = '01 May 2007'

it will only work on an English locale. Use

SET @Date1 = '20070501'

instead.

Also don't do this:

Admin_UserSessionLog.LoggedIn > CONVERT(DATETIME, @Date1, 102))

@Date1 is already a DateTime datatype. If Admin_UserSessionLog.LoggedIn is NOT a DateTime datatype it ought to be!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources