nkewney
asked on
Adding time conditions to query - help!
Dear Experts,
This query is proving to be a real problem - this is my third question so far! Please bear with me - I am learning from your answers.
I am trying to filter the following query down to all responses received within the last month (i.e. from 1st of the month to the current date).
I tried adding this onto the end of my query but it's skewing up my results set... It's meant to be grouped by username but is messing up when I add this in...
AND cbr.Date BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) AND GETDATE()
Entire query so far...
=======================
SELECT TOP (10) Username, Name, Person_ID, Company_ID, NoCompleted, NoAllocated, NoCompleted * 100 / NoAllocated AS OutputFigure
FROM (SELECT TOP (10) cb.Username, SUM(CASE WHEN cbr.call_id IS NULL THEN 0 ELSE 1 END) AS NoCompleted, COUNT(*) AS NoAllocated, People.Person_nameFirst + ' ' + People.Person_nameLast AS Name, People.Person_ID, ClientAccounts.Company_ID
FROM CallBank AS cb
INNER JOIN UserList ON cb.Username = UserList.User_Username
INNER JOIN People ON UserList.Person_ID = People.Person_ID
INNER JOIN ActivityOutlets ON cb.ActivityOutlet_ID = ActivityOutlets.ActivityOu tlet_ID
INNER JOIN OutletList ON ActivityOutlets.Outlet_ID = OutletList.Outlet_ID
INNER JOIN AccountOutlets ON OutletList.Outlet_ID = AccountOutlets.Outlet_ID
INNER JOIN ClientAccounts ON AccountOutlets.Account_ID = ClientAccounts.Account_ID
LEFT OUTER JOIN CallBankResponse AS cbr ON cbr.Call_ID = cb.Call_ID
GROUP BY cb.Username, People.Person_nameFirst, People.Person_nameLast, People.Person_ID, ClientAccounts.Company_ID
HAVING(ClientAccounts.Comp any_ID = 31)
ORDER BY NoAllocated DESC) AS derivedtbl_1
ORDER BY OutputFigure DESC
This query is proving to be a real problem - this is my third question so far! Please bear with me - I am learning from your answers.
I am trying to filter the following query down to all responses received within the last month (i.e. from 1st of the month to the current date).
I tried adding this onto the end of my query but it's skewing up my results set... It's meant to be grouped by username but is messing up when I add this in...
AND cbr.Date BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) AND GETDATE()
Entire query so far...
=======================
SELECT TOP (10) Username, Name, Person_ID, Company_ID, NoCompleted, NoAllocated, NoCompleted * 100 / NoAllocated AS OutputFigure
FROM (SELECT TOP (10) cb.Username, SUM(CASE WHEN cbr.call_id IS NULL THEN 0 ELSE 1 END) AS NoCompleted, COUNT(*) AS NoAllocated, People.Person_nameFirst + ' ' + People.Person_nameLast AS Name, People.Person_ID, ClientAccounts.Company_ID
FROM CallBank AS cb
INNER JOIN UserList ON cb.Username = UserList.User_Username
INNER JOIN People ON UserList.Person_ID = People.Person_ID
INNER JOIN ActivityOutlets ON cb.ActivityOutlet_ID = ActivityOutlets.ActivityOu
INNER JOIN OutletList ON ActivityOutlets.Outlet_ID = OutletList.Outlet_ID
INNER JOIN AccountOutlets ON OutletList.Outlet_ID = AccountOutlets.Outlet_ID
INNER JOIN ClientAccounts ON AccountOutlets.Account_ID = ClientAccounts.Account_ID
LEFT OUTER JOIN CallBankResponse AS cbr ON cbr.Call_ID = cb.Call_ID
GROUP BY cb.Username, People.Person_nameFirst, People.Person_nameLast, People.Person_ID, ClientAccounts.Company_ID
HAVING(ClientAccounts.Comp
ORDER BY NoAllocated DESC) AS derivedtbl_1
ORDER BY OutputFigure DESC
Apologies for short-changing you - my suggestion will be a poor performer on large populations. A more efficient condition to use is:
AND cbr.Date >= convert (datetime, substring (convert (varchar, getdate(), 121), 1, 8) + '01', 121)
AND cbr.Date >= convert (datetime, substring (convert (varchar, getdate(), 121), 1, 8) + '01', 121)
ASKER
hello lahousden,
Can you give me an example of how i would integrate it as I'm getting a Group By error?
Nick
Can you give me an example of how i would integrate it as I'm getting a Group By error?
Nick
add the condition to the left outer join on clause
SELECT Username
, People.Person_nameFirst + ' ' + People.Person_nameLast AS Name, Person_ID
, 31 as Company_ID
, NoCompleted
, NoAllocated
, (NoCompleted * 100.000) / NoAllocated AS OutputFigure
FROM (SELECT TOP 10 cb.Username, SUM(CASE WHEN cbr.call_id IS NULL THEN 0 ELSE 1 END) AS NoCompleted
, COUNT(*) AS NoAllocated, , UserList.Person_ID
FROM CallBank AS cb
INNER JOIN UserList
ON cb.Username = UserList.User_Username
INNER JOIN ActivityOutlets
ON cb.ActivityOutlet_ID = ActivityOutlets.ActivityOu tlet_ID
INNER JOIN OutletList
ON ActivityOutlets.Outlet_ID = OutletList.Outlet_ID
INNER JOIN AccountOutlets
ON OutletList.Outlet_ID = AccountOutlets.Outlet_ID
INNER JOIN ClientAccounts
ON AccountOutlets.Account_ID = ClientAccounts.Account_ID
LEFT OUTER JOIN CallBankResponse AS cbr
ON cbr.Call_ID = cb.Call_ID
and cbr.Date BETWEEN convert(datetime,Convert(c har(8),DAT EADD(d,((- 1)*Day(get date()) + 1),Getdate()),112)) AND GETDATE()
Where ClientAccounts.Company_ID = 31
GROUP BY cb.Username, Userlist.Person_ID
ORDER BY NoAllocated DESC
) AS x
INNER JOIN People
ON x.Person_ID = People.Person_ID
ORDER BY OutputFigure DESC
i've also moved the person table join to the outer query... for efficiency (you shouldn't need it on the inside)
also you having condition is actually a where condition... (just use having for aggregate comparisions..)
and the company id is known so you shouldn't need it in the actual query results...
(so if you must just add it as a literal into the outer select)
the top 10 isn't required on the outer query you'll only get 10 anyway...
hth
SELECT Username
, People.Person_nameFirst + ' ' + People.Person_nameLast AS Name, Person_ID
, 31 as Company_ID
, NoCompleted
, NoAllocated
, (NoCompleted * 100.000) / NoAllocated AS OutputFigure
FROM (SELECT TOP 10 cb.Username, SUM(CASE WHEN cbr.call_id IS NULL THEN 0 ELSE 1 END) AS NoCompleted
, COUNT(*) AS NoAllocated, , UserList.Person_ID
FROM CallBank AS cb
INNER JOIN UserList
ON cb.Username = UserList.User_Username
INNER JOIN ActivityOutlets
ON cb.ActivityOutlet_ID = ActivityOutlets.ActivityOu
INNER JOIN OutletList
ON ActivityOutlets.Outlet_ID = OutletList.Outlet_ID
INNER JOIN AccountOutlets
ON OutletList.Outlet_ID = AccountOutlets.Outlet_ID
INNER JOIN ClientAccounts
ON AccountOutlets.Account_ID = ClientAccounts.Account_ID
LEFT OUTER JOIN CallBankResponse AS cbr
ON cbr.Call_ID = cb.Call_ID
and cbr.Date BETWEEN convert(datetime,Convert(c
Where ClientAccounts.Company_ID = 31
GROUP BY cb.Username, Userlist.Person_ID
ORDER BY NoAllocated DESC
) AS x
INNER JOIN People
ON x.Person_ID = People.Person_ID
ORDER BY OutputFigure DESC
i've also moved the person table join to the outer query... for efficiency (you shouldn't need it on the inside)
also you having condition is actually a where condition... (just use having for aggregate comparisions..)
and the company id is known so you shouldn't need it in the actual query results...
(so if you must just add it as a literal into the outer select)
the top 10 isn't required on the outer query you'll only get 10 anyway...
hth
yes sorry like lahousden's method of getting day 1 better...
and cbr.Date BETWEEN convert(datetime,Convert(c har(6),get date(),112 )+'01') AND GETDATE()
but do use char(6) and avoid the substring...
and cbr.Date BETWEEN convert(datetime,Convert(c
but do use char(6) and avoid the substring...
(Don't know what is up with my "internal QA" today) - and, of course, my last post assumes that there are no rows where the value of cbr.date is greater than right now...
ASKER
Perfect, lowfatspread - really appreciate your help here.
Can you tell me how I could apply the same to 'week' and 'year' ?
Don't quite follow the logic behind the date part!!
Thanks
Nick
Can you tell me how I could apply the same to 'week' and 'year' ?
Don't quite follow the logic behind the date part!!
Thanks
Nick
ASKER
Sorry just to clarify, i mean:
Week, Find the first day of the current week (assuming it starts Monday at 12:01am)
Year, Find the first day of the current year in the same foramt.
Thanks
Nick
Week, Find the first day of the current week (assuming it starts Monday at 12:01am)
Year, Find the first day of the current year in the same foramt.
Thanks
Nick
year is easy
and cbr.Date BETWEEN convert(datetime,Convert(c har(4),get date(),112 )+'0101') AND GETDATE()
Week is more trouble...
assuming you haven't altered datefirst...
so that Sunday=1, Monday=2,..
then
and cbr.Date BETWEEN convert(datetime,Convert(c har(6),get date(),112 )+Right('0 ' + convert(varchar(2),day(dat eadd(d,Cas e Datepart(dw,Getdate()) when 1 then 0 when 0 then -6 else 1 - datepart(dw,getdate()) end,getdate()))) ) AND GETDATE()
basically
Convert(char(6),getdate(), 112)
style 112 is YYYYMMDD so the convert extracts year + month asa desired...
the rest of it
datepart(dw,getdate()) returns the day of the week ... 0 is sunday, 1 is monday
so the case works out an adjustment for the dateadd to calulate the previous monday..
ie 6 days ago if sunday
1 - dw if tues ... etc
when you don't sepecify a time you get the start of the day...
hth
and cbr.Date BETWEEN convert(datetime,Convert(c
Week is more trouble...
assuming you haven't altered datefirst...
so that Sunday=1, Monday=2,..
then
and cbr.Date BETWEEN convert(datetime,Convert(c
basically
Convert(char(6),getdate(),
style 112 is YYYYMMDD so the convert extracts year + month asa desired...
the rest of it
datepart(dw,getdate()) returns the day of the week ... 0 is sunday, 1 is monday
so the case works out an adjustment for the dateadd to calulate the previous monday..
ie 6 days ago if sunday
1 - dw if tues ... etc
when you don't sepecify a time you get the start of the day...
hth
ASKER
Lowfatspread,
I think I am beginning to get this. Seems quite daunting at first though..
The week one returns the following...
System.Data.SqlClient.SqlE xception: The right function requires 2 argument(s).
Also, last thing I'll ask of you... how would I approach 'today' only (not within last 24 hours..) just today!
Thanks again for this.
Nick
I think I am beginning to get this. Seems quite daunting at first though..
The week one returns the following...
System.Data.SqlClient.SqlE
Also, last thing I'll ask of you... how would I approach 'today' only (not within last 24 hours..) just today!
Thanks again for this.
Nick
With all points to lowfatspread, I was listening to the Maple Leafs game and playing with this and I came up with these mods... there are two queries here, try the top to see how the if statements work, then paste the query on the bottom into QA to create a procedure...
Tom
-- ========================== =====
-- this starts the test. copy / paste these lines to query analyzer to see how it works...
declare @today datetime, @startdate datetime, @test varchar(20)
-- change the next two lines to test program flow
set @today = '20070320 12:00' -- adjust to see outcome for different calendar days
set @test = 'day' --
if @test = 'day'
begin
select @startdate = cast(datediff(day, 0, @today) as datetime)
end
if @test = 'week'
begin
select @startdate =
cast(floor(cast(@today as float)) - datepart(weekday,@today) + case datepart(weekday,@today) when 1 then - 5 else 2 end as datetime)
end
if @test = 'month'
begin
select @startdate = convert(datetime,Convert(c har(6),get date(),112 )+'01')
end
if @test = 'year'
begin
select @startdate = convert(datetime,Convert(c har(4),get date(),112 )+'0101')
end
select @startdate
-- end of test
--======================== ========== =====
-- This starts the stored procedure. copy / paste these lines into a new query window.
--drop procedure COMPANY_31_RPT
create procedure COMPANY_31_RPT @rpt_type varchar(20) = 'month' as
declare @startdate datetime
if @rpt_type = 'day'
begin
select @startdate = cast(datediff(day, 0, getdate()) as datetime)
end
if @rpt_type = 'week'
begin
select @startdate =
cast(floor(cast(getdate() as float)) - datepart(weekday,getdate() ) + case datepart(weekday,getdate() ) when 1 then - 5 else 2 end as datetime)
end
if @rpt_type = 'month'
begin
select @startdate = convert(datetime,Convert(c har(6),get date(),112 )+'01')
end
if @rpt_type = 'year'
begin
select @startdate = convert(datetime,Convert(c har(4),get date(),112 )+'0101')
end
if @rpt_type in ('day', 'week', 'month', 'year')
begin
SELECT Username
, People.Person_nameFirst + ' ' + People.Person_nameLast AS Name, Person_ID
, 31 as Company_ID
, NoCompleted
, NoAllocated
, (NoCompleted * 100.000) / NoAllocated AS OutputFigure
FROM (SELECT TOP 10 cb.Username, SUM(CASE WHEN cbr.call_id IS NULL THEN 0 ELSE 1 END) AS NoCompleted
, COUNT(*) AS NoAllocated, , UserList.Person_ID
FROM CallBank AS cb
INNER JOIN UserList
ON cb.Username = UserList.User_Username
INNER JOIN ActivityOutlets
ON cb.ActivityOutlet_ID = ActivityOutlets.ActivityOu tlet_ID
INNER JOIN OutletList
ON ActivityOutlets.Outlet_ID = OutletList.Outlet_ID
INNER JOIN AccountOutlets
ON OutletList.Outlet_ID = AccountOutlets.Outlet_ID
INNER JOIN ClientAccounts
ON AccountOutlets.Account_ID = ClientAccounts.Account_ID
LEFT OUTER JOIN CallBankResponse AS cbr
ON cbr.Call_ID = cb.Call_ID
and cbr.Date BETWEEN @startdate and GETDATE()
Where ClientAccounts.Company_ID = 31
GROUP BY cb.Username, Userlist.Person_ID
ORDER BY NoAllocated DESC
) AS x
INNER JOIN People
ON x.Person_ID = People.Person_ID
ORDER BY OutputFigure DESC
end
-- end procedure
-- sample procedure call statement. time period is optional, if omitted, month report version runs.
COMPANY_31_RPT [year]
Tom
-- ==========================
-- this starts the test. copy / paste these lines to query analyzer to see how it works...
declare @today datetime, @startdate datetime, @test varchar(20)
-- change the next two lines to test program flow
set @today = '20070320 12:00' -- adjust to see outcome for different calendar days
set @test = 'day' --
if @test = 'day'
begin
select @startdate = cast(datediff(day, 0, @today) as datetime)
end
if @test = 'week'
begin
select @startdate =
cast(floor(cast(@today as float)) - datepart(weekday,@today) + case datepart(weekday,@today) when 1 then - 5 else 2 end as datetime)
end
if @test = 'month'
begin
select @startdate = convert(datetime,Convert(c
end
if @test = 'year'
begin
select @startdate = convert(datetime,Convert(c
end
select @startdate
-- end of test
--========================
-- This starts the stored procedure. copy / paste these lines into a new query window.
--drop procedure COMPANY_31_RPT
create procedure COMPANY_31_RPT @rpt_type varchar(20) = 'month' as
declare @startdate datetime
if @rpt_type = 'day'
begin
select @startdate = cast(datediff(day, 0, getdate()) as datetime)
end
if @rpt_type = 'week'
begin
select @startdate =
cast(floor(cast(getdate() as float)) - datepart(weekday,getdate()
end
if @rpt_type = 'month'
begin
select @startdate = convert(datetime,Convert(c
end
if @rpt_type = 'year'
begin
select @startdate = convert(datetime,Convert(c
end
if @rpt_type in ('day', 'week', 'month', 'year')
begin
SELECT Username
, People.Person_nameFirst + ' ' + People.Person_nameLast AS Name, Person_ID
, 31 as Company_ID
, NoCompleted
, NoAllocated
, (NoCompleted * 100.000) / NoAllocated AS OutputFigure
FROM (SELECT TOP 10 cb.Username, SUM(CASE WHEN cbr.call_id IS NULL THEN 0 ELSE 1 END) AS NoCompleted
, COUNT(*) AS NoAllocated, , UserList.Person_ID
FROM CallBank AS cb
INNER JOIN UserList
ON cb.Username = UserList.User_Username
INNER JOIN ActivityOutlets
ON cb.ActivityOutlet_ID = ActivityOutlets.ActivityOu
INNER JOIN OutletList
ON ActivityOutlets.Outlet_ID = OutletList.Outlet_ID
INNER JOIN AccountOutlets
ON OutletList.Outlet_ID = AccountOutlets.Outlet_ID
INNER JOIN ClientAccounts
ON AccountOutlets.Account_ID = ClientAccounts.Account_ID
LEFT OUTER JOIN CallBankResponse AS cbr
ON cbr.Call_ID = cb.Call_ID
and cbr.Date BETWEEN @startdate and GETDATE()
Where ClientAccounts.Company_ID = 31
GROUP BY cb.Username, Userlist.Person_ID
ORDER BY NoAllocated DESC
) AS x
INNER JOIN People
ON x.Person_ID = People.Person_ID
ORDER BY OutputFigure DESC
end
-- end procedure
-- sample procedure call statement. time period is optional, if omitted, month report version runs.
COMPANY_31_RPT [year]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Lowfatspread - you have been a fantastic help!
The week line still returns the following...
The right function requires 2 argument(s).
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlE xception: The right function requires 2 argument(s).
Source Error:
Line 60:
Line 61:
Line 62: dbAdapter.Fill(ds)
Line 63:
The week line still returns the following...
The right function requires 2 argument(s).
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlE
Source Error:
Line 60:
Line 61:
Line 62: dbAdapter.Fill(ds)
Line 63:
ASKER
Absolutely fantastic.
Thanks lowfatspread - it worked perfectly.
Nick
Thanks lowfatspread - it worked perfectly.
Nick
AND YEAR(cbr.Date) = YEAR(GetDate()) and MONTH(cbr.Date) = MONTH(GetDate())