SQL Query Optimisation

pize
pize used Ask the Experts™
on
I am querying a database view that has some 11 million records.

I want the query to return the number of records that fall within a week date range where a week is defined as Sunday - Saturday.  If the StartDate and EndDate passed in are not Saturday or Sunday dates then the first week and last week should start and end at those dates.

For example....I pass in @StartDate =  '2010-09-08' which is a Wednesday and @DateEnd = '2010-09-21' which is a Tuesday, I should end up with the following

Week Start    WeekEnd      Qty
-----------------------------------------
2010-09-08      2010-09-11      38193      (Wednesday to Saturday)
2010-09-12      2010-09-18      75934      (Saturday to Sunday)
2010-09-19      2010-09-21      16614      (Saturday to Tuesday)

I have written the following query

select
  convert(nvarchar(10), (case when DATEADD(DAY, 1-DATEPART(dw, DateTimeReceived), DateTimeReceived) < @DateStart then @DateStart
  else DATEADD(DAY, 1-DATEPART(dw, DateTimeReceived), DateTimeReceived)
                         end),121) As WeekStartDate,  
  convert(nvarchar(10), (case when DATEADD(DAY, 7-DATEPART(dw, DateTimeReceived), DateTimeReceived) > @DateEnd then @DateEnd
  else DATEADD(DAY, 7-DATEPART(dw, DateTimeReceived), DateTimeReceived)
             end),121) As WeekEndDate,  
  count(*) As Quantity
from TableA_VW with (nolock)
where DateTimeReceived between @DateStart and @DateEnd
group by
  convert(nvarchar(10),(case when DATEADD(DAY, 7-DATEPART(dw, DateTimeReceived), DateTimeReceived) > @DateEnd then @DateEnd
                                        else DATEADD(DAY, 7-DATEPART(dw, DateTimeReceived), DateTimeReceived)
                        end),121),
 convert(nvarchar(10),(case when DATEADD(DAY, 1-DATEPART(dw, DateTimeReceived),  DateTimeReceived) < @DateStart then @DateStart
                         else DATEADD(DAY, 1-DATEPART(dw, DateTimeReceived), DateTimeReceived)
                       end),121)
order by 1

The DateTimeReceived column is the one Im most interested in.
The speed isnt too bad.  Im just wondering if there is a more efficient way to wrtite a query of this type?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Is this marginally faster?
---- use functions to prevent the Date expression from being evaluated twice in the case statement

create function dbo.minDateOf(@d1 datetime, @d2 datetime)
returns datetime as
begin
-- assumes neither will be null
return case when @d1 < @d2 then @d1 else @d2 end
end
GO
create function dbo.maxDateOf(@d1 datetime, @d2 datetime)
returns datetime as
begin
-- assumes neither will be null
return case when @d1 > @d2 then @d1 else @d2 end
end
GO
select
 convert(nvarchar(10), dbo.minDateOf(
	DATEADD(DAY, 1-DATEPART(dw, DateTimeReceived), DateTimeReceived),
	@DateStart),121) As WeekStartDate, 
 convert(nvarchar(10), dbo.maxDateOf(
	DATEADD(DAY, 7-DATEPART(dw, DateTimeReceived), DateTimeReceived),
	@DateEnd),121) As WeekEndDate, 
 count(*) As Quantity
from TableA_VW with (nolock)
where DateTimeReceived between @DateStart and @DateEnd
group by -- convert is not required in the group by part
 dbo.minDateOf(
	DATEADD(DAY, 1-DATEPART(dw, DateTimeReceived), DateTimeReceived),
	@DateStart) As WeekStartDate, 
 dbo.maxDateOf(
	DATEADD(DAY, 7-DATEPART(dw, DateTimeReceived), DateTimeReceived),
	@DateEnd) As WeekEndDate, 
order by 1

Open in new window

Author

Commented:
I have made the change as per your suggestion however the WeekStart and EndDates are no longer correct.  For example, executing with your changes produces the following.

(Ignore the quantities as I had to run on a different database due to having to create functions)

Week Start    WeekEnd      Qty
--------------------------------------
2010-09-05      2010-09-21      4
2010-09-05      2010-09-21      4
2010-09-08      2010-09-21      581

Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Hi pize,

Sorry, but I have the min/max the other way around.
I also cleaned up the syntax errors (sorry for the poor form!).

Regards
select
 convert(nvarchar(10), dbo.maxDateOf(
	DATEADD(DAY, 1-DATEPART(dw, DateTimeReceived), DateTimeReceived),
	@DateStart),121) As WeekStartDate, 
 convert(nvarchar(10), dbo.minDateOf(
	DATEADD(DAY, 7-DATEPART(dw, DateTimeReceived), DateTimeReceived),
	@DateEnd),121) As WeekEndDate, 
 count(*) As Quantity
from TableA_VW with (nolock)
where DateTimeReceived between @DateStart and @DateEnd
group by -- convert is not required in the group by part
 dbo.maxDateOf(
	DATEADD(DAY, 1-DATEPART(dw, DateTimeReceived), DateTimeReceived),
	@DateStart), 
 dbo.minDateOf(
	DATEADD(DAY, 7-DATEPART(dw, DateTimeReceived), DateTimeReceived),
	@DateEnd)
order by 1

Open in new window

Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Expert of the Quarter 2010
Expert of the Year 2010

Commented:
It turns out using a function like that made it slower, but a factor of 3 in fact.
This one however reduced run time to about 25 to 30%
I create a table of 5mil+ rows to test.

The key is that the comparison and convert-varchar are not repeated x times.  Instead, the start/end dates are allowed to propagate through to grouping, and ONLY after grouping to the eventual final rows do the two get applied.
/*
drop table TableA_VW
--
create table TableA_VW(id int identity primary key clustered, DateTimeReceived datetime)
--
insert TableA_VW
select getdate()+ascii(newid())
from master..spt_values a cross join master..spt_values b
--
-- you should really have an index
create index ix_TableA_VW_DateTimeReceived on TableA_VW(DateTimeReceived)
--
select max(DateTimeReceived), min(DateTimeReceived) from tablea_vw
*/
-- final query bake off
declare @DateStart datetime set @datestart = '20101207'
declare @Dateend   datetime set @dateend   = '20101226'
declare @timer decimal(20,10)

set @timer = convert(decimal(20,10),getdate()); select 'starttime', @timer
;with t as (
select
  DATEADD(DAY, 1-DATEPART(dw, DateTimeReceived), DateTimeReceived) As WeekStartDate,  
  DATEADD(DAY, 7-DATEPART(dw, DateTimeReceived), DateTimeReceived) As WeekEndDate
from TableA_VW with (nolock)
where DateTimeReceived between @DateStart and @DateEnd
)
select
	convert(nvarchar(10), CASE WHEN WeekStartDate < @DateStart then @DateStart else WeekStartDate end) as WeekStartDate,
	convert(nvarchar(10), CASE WHEN WeekEndDate > @DateEnd then @DateEnd else WeekEndDate end) AS WeekEndDate,
	count(*) as Quantity
from t
group by WeekStartDate, WeekEndDate
order by 1

-- vs
select 'first query took', (convert(decimal(20,10),getdate()) - @timer) * 24 *60*60 as seconds
set @timer = convert(decimal(20,10),getdate());
select
  convert(nvarchar(10), (case when DATEADD(DAY, 1-DATEPART(dw, DateTimeReceived), DateTimeReceived) < @DateStart then @DateStart
  else DATEADD(DAY, 1-DATEPART(dw, DateTimeReceived), DateTimeReceived)
                         end),121) As WeekStartDate,  
  convert(nvarchar(10), (case when DATEADD(DAY, 7-DATEPART(dw, DateTimeReceived), DateTimeReceived) > @DateEnd then @DateEnd
  else DATEADD(DAY, 7-DATEPART(dw, DateTimeReceived), DateTimeReceived)
             end),121) As WeekEndDate,  
  count(*) As Quantity
from TableA_VW with (nolock)
where DateTimeReceived between @DateStart and @DateEnd
group by
  convert(nvarchar(10),(case when DATEADD(DAY, 7-DATEPART(dw, DateTimeReceived), DateTimeReceived) > @DateEnd then @DateEnd
                                        else DATEADD(DAY, 7-DATEPART(dw, DateTimeReceived), DateTimeReceived)
                        end),121),
 convert(nvarchar(10),(case when DATEADD(DAY, 1-DATEPART(dw, DateTimeReceived),  DateTimeReceived) < @DateStart then @DateStart
                         else DATEADD(DAY, 1-DATEPART(dw, DateTimeReceived), DateTimeReceived)
                       end),121)
order by 1

select 'second query took', (convert(decimal(20,10),getdate()) - @timer) * 24 *60*60 as seconds

Open in new window

Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Well, there is not much that can be done if needing to compare start dates with week commencings and same for end date - thats just something that has to happen...

But if we are talking about weeks, then why not group by week ?

That way we can use date arithmetic all throughout which will perform better. The only thin is the final "display" format but that can be done in what ever the reporting / display tool is going to be used.

Also, your column says DateTimeReceived, so the end periods really should consider time, and have the "close of day" being 23:59:59.997 as the time and not "00:00:00" as might be indicated with the original routine...

we can use the week datepart (and adjust if day 1 is not a sunday), or even create our own week calendar (also avoids times). Check out you query now using weeks (and I had to ajust the number of days by adding / subtracting to get the sunday/saturday week)

The only other thing is to make sure that DateTimeReceived is an index...

select case when @datestart > min(dateadd(wk,datediff(wk,0,datetimereceived),0)-1) then @datestart else min(dateadd(wk,datediff(wk,0,datetimereceived),0)-1) end as startweek
       ,case when @dateend < min(dateadd(wk,datediff(wk,0,datetimereceived),0)+5) then @dateend else min(dateadd(wk,datediff(wk,0,datetimereceived),0)+5) end as endweek
       ,count(*) As Quantity
--     ,datepart(wk,DateTimeReceived) weekno
from TableA_VW with (nolock)
where DateTimeReceived between @DateStart and @DateEnd 
group by datepart(wk,DateTimeReceived) 
order by 1

Open in new window

Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
If end time is important, you can get there easily enough by simply adding the appropriate time as a numeric function

e.g.

declare @dateend datetime
set @DateEnd = '2010-09-21' --which is a Tuesday
set @dateend = @dateend + 0.999999995   -- (and in the week ending above use 5.999999995 to get to end datetime period)

select @dateend

of course if you are in SQL 2008 then can use the DATE datatype and avoid having to handle time.
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Mark,

How do you propose to merge 2 rows when the result crosses a year boundary?
There's probably an easy way.

FWIW, I expanded my test to include your date maths.
It runs just a tad slower.
/*
drop table TableA_VW
--
create table TableA_VW(id int identity primary key clustered, DateTimeReceived datetime)
--
insert TableA_VW
select getdate()+ascii(newid())
from master..spt_values a cross join master..spt_values b
--
-- you should really have an index
create index ix_TableA_VW_DateTimeReceived on TableA_VW(DateTimeReceived)
--
select max(DateTimeReceived), min(DateTimeReceived) from tablea_vw
*/
-- final query bake off
declare @DateStart datetime set @datestart = '20101207'
declare @Dateend   datetime set @dateend   = '20101226'
declare @timer decimal(20,10)

set @timer = convert(decimal(20,10),getdate()); select 'starttime', @timer
set @timer = convert(decimal(20,10),getdate());

;with t as (
select
  DATEADD(DAY, 1-DATEPART(dw, DateTimeReceived), DateTimeReceived) As WeekStartDate,  
  DATEADD(DAY, 7-DATEPART(dw, DateTimeReceived), DateTimeReceived) As WeekEndDate
from TableA_VW with (nolock)
where DateTimeReceived between @DateStart and @DateEnd
)
select
	convert(nvarchar(10), CASE WHEN WeekStartDate < @DateStart then @DateStart else WeekStartDate end) as WeekStartDate,
	convert(nvarchar(10), CASE WHEN WeekEndDate > @DateEnd then @DateEnd else WeekEndDate end) AS WeekEndDate,
	count(*) as Quantity
from t
group by WeekStartDate, WeekEndDate
order by 1

-- vs
select '0th query took', (convert(decimal(20,10),getdate()) - @timer) * 24 *60*60 as seconds
select case when @datestart > min(dateadd(wk,datediff(wk,0,datetimereceived),0)-1) then @datestart else min(dateadd(wk,datediff(wk,0,datetimereceived),0)-1) end as startweek
       ,case when @dateend < min(dateadd(wk,datediff(wk,0,datetimereceived),0)+5) then @dateend else min(dateadd(wk,datediff(wk,0,datetimereceived),0)+5) end as endweek
       ,count(*) As Quantity
--     ,datepart(wk,DateTimeReceived) weekno
from TableA_VW with (nolock)
where DateTimeReceived between @DateStart and @DateEnd 
group by datepart(wk,DateTimeReceived) 
order by 1

-- vs
select 'first query took', (convert(decimal(20,10),getdate()) - @timer) * 24 *60*60 as seconds
set @timer = convert(decimal(20,10),getdate());
select
  convert(nvarchar(10), (case when DATEADD(DAY, 1-DATEPART(dw, DateTimeReceived), DateTimeReceived) < @DateStart then @DateStart
  else DATEADD(DAY, 1-DATEPART(dw, DateTimeReceived), DateTimeReceived)
                         end),121) As WeekStartDate,  
  convert(nvarchar(10), (case when DATEADD(DAY, 7-DATEPART(dw, DateTimeReceived), DateTimeReceived) > @DateEnd then @DateEnd
  else DATEADD(DAY, 7-DATEPART(dw, DateTimeReceived), DateTimeReceived)
             end),121) As WeekEndDate,  
  count(*) As Quantity
from TableA_VW with (nolock)
where DateTimeReceived between @DateStart and @DateEnd
group by
  convert(nvarchar(10),(case when DATEADD(DAY, 7-DATEPART(dw, DateTimeReceived), DateTimeReceived) > @DateEnd then @DateEnd
                                        else DATEADD(DAY, 7-DATEPART(dw, DateTimeReceived), DateTimeReceived)
                        end),121),
 convert(nvarchar(10),(case when DATEADD(DAY, 1-DATEPART(dw, DateTimeReceived),  DateTimeReceived) < @DateStart then @DateStart
                         else DATEADD(DAY, 1-DATEPART(dw, DateTimeReceived), DateTimeReceived)
                       end),121)
order by 1

select 'second query took', (convert(decimal(20,10),getdate()) - @timer) * 24 *60*60 as seconds

Open in new window

Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
well, the week is to very simply break into groups of 7 the start and end is based on those groupings. Yeah there is that "magical" week 53 which happens to be the same as week 1...

we havent actually been told about what happens at start of year and end of year, always an "interesting" discussion when reporting in week commencings.

in which case I will go back to my original code before I thought about week...


select case when @datestart > startweek then @datestart else startweek end as startweek
       ,case when @dateend > endweek then @dateend else endweek end as endweek
       ,count(*) as qty
from (
       select dateadd(day, 1-datepart(dw, DateTimeReceived), DateTimeReceived) as startweek
             ,dateadd(day, 7-datepart(dw, DateTimeReceived), DateTimeReceived) as endweek
       from TableA_VW with (nolock)
       where DateTimeReceived between @DateStart and @DateEnd  ) w
group by startweek,endweek
order by 1
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Lines 38-40 in http:#a33925974 was missing a line, as shown in code box.
(it was not resetting the timer, but even doing that, it is still slower).

Without even testing http:#a33926073, I know it will perform equally to http:#a33925905 since it is a dead ringer. :)

-- vs
select '0th query took', (convert(decimal(20,10),getdate()) - @timer) * 24 *60*60 as seconds
set @timer = convert(decimal(20,10),getdate());  ---/// WAS MISSING
select case when @datestart > min(dateadd(wk,datediff(wk,0,datetimereceived),0)-1) then @datestart else min(dateadd(wk,datediff(wk,0,datetimereceived),0)-1) end as startweek

Open in new window

Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Oh, and the cte query should work the fastest of all because it first expresses the two columns using simple date functions and then produces the groupings...

So, the fastest by all accounts will most likely be :

;with t as (
select
  DATEADD(DAY, 1-DATEPART(dw, DateTimeReceived), DateTimeReceived) As WeekStartDate,  
  DATEADD(DAY, 7-DATEPART(dw, DateTimeReceived), DateTimeReceived) As WeekEndDate
from TableA_VW with (nolock)
where DateTimeReceived between @DateStart and @DateEnd
)
select
      convert(nvarchar(10), CASE WHEN WeekStartDate < @DateStart then @DateStart else WeekStartDate end) as WeekStartDate,
      convert(nvarchar(10), CASE WHEN WeekEndDate > @DateEnd then @DateEnd else WeekEndDate end) AS WeekEndDate,
      count(*) as Quantity
from t
group by WeekStartDate, WeekEndDate
order by 1

Whilst not so different from the subquery approach, I have found that the CTE tends to be much of a muchness than the equivelent subqueries....

In you "bake off" should really clear buffers the second and subsequent will probably fall prey to sniffing - the first will perform best (unless all that is cached already).
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
OK, plugged in the subquery (at the start), did a reset of the timer, did a freeproccache and the first three performed within a second of each other. The last one was a lot more ordinary in comparison.

Apart from the week 53 nonsense... the CTE or the Subquery works best (cte just a fraction - literally a fraction - in front), and considerably better than the original.

skip the comment about sniffing, I had it in a stored procedure for testing / playing (because of the use of parameters).
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
pize,

My final submission is as follows in code box.  I realised that I forgot the convert specifier (121) for your specific output format.

@Mark,

> - the first will perform best (unless all that is cached already).

Just clarifying, do you mean "worst"?  I deliberately put it first to disadvantage my own preferred query, letting the others benefit from buffer cache.

But to be clear, I always swap them around and test again to get averages.  In this case, the queries are very simple and no amount of swapping has revealed any caching benefits/disadvantages.

dbcc freeproccache
dbcc dropcleanbuffers

;with t as (
select
  DATEADD(DAY, 1-DATEPART(dw, DateTimeReceived), DateTimeReceived) As WeekStartDate,  
  DATEADD(DAY, 7-DATEPART(dw, DateTimeReceived), DateTimeReceived) As WeekEndDate
from TableA_VW with (nolock)
where DateTimeReceived between @DateStart and @DateEnd
)
select
	convert(nvarchar(10), CASE WHEN WeekStartDate < @DateStart then @DateStart else WeekStartDate end,121) as WeekStartDate,
	convert(nvarchar(10), CASE WHEN WeekEndDate > @DateEnd then @DateEnd else WeekEndDate end,121) AS WeekEndDate,
	count(*) as Quantity
from t
group by WeekStartDate, WeekEndDate
order by 1

Open in new window

Author

Commented:
Thanks for all the postings on this which i need to go back and read in some detail.

cyberkiwi, I plugged in your last post but I do not get the required groupings of weeks.  I get WeekStart and WeekEnd dates that are repeated like this:-


Week Start    WeekEnd      Qty
--------------------------------------
2010-09-08      2010-09-11      3
2010-09-08      2010-09-11      7
2010-09-08      2010-09-11      4
2010-09-08      2010-09-11      2
2010-09-08      2010-09-11      5
2010-09-08      2010-09-11      7
2010-09-08      2010-09-11      8
2010-09-08      2010-09-11      13
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
The one in here? http:#a33926157
Did you cut and paste?

Can you try this query (which works in tempdb) and check if it produces 3 rows?
use tempdb
--
create table TableA_VW(id int identity primary key clustered, DateTimeReceived datetime)
--
insert TableA_VW
select getdate()+ascii(newid())
from master..spt_values a --cross join master..spt_values b

-- you should really have an index
create index ix_TableA_VW_DateTimeReceived on TableA_VW(DateTimeReceived)

-- test query
declare @DateStart datetime set @datestart = '20101207'
declare @Dateend   datetime set @dateend   = '20101226'

;with t as (
select
  DATEADD(DAY, 1-DATEPART(dw, DateTimeReceived), DateTimeReceived) As WeekStartDate,  
  DATEADD(DAY, 7-DATEPART(dw, DateTimeReceived), DateTimeReceived) As WeekEndDate
from TableA_VW with (nolock)
where DateTimeReceived between @DateStart and @DateEnd
)
select
	convert(nvarchar(10), CASE WHEN WeekStartDate < @DateStart then @DateStart else WeekStartDate end,121) as WeekStartDate,
	convert(nvarchar(10), CASE WHEN WeekEndDate > @DateEnd then @DateEnd else WeekEndDate end,121) AS WeekEndDate,
	count(*) as Quantity
from t
group by WeekStartDate, WeekEndDate
order by 1

Open in new window

Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
OK, well here is an alternate query which works as quick...


select case when @datestart > startweek then @datestart else startweek end as startweek
       ,case when @dateend > endweek then @dateend else endweek end as endweek
       ,count(*) as qty
from (
       select dateadd(day, 1-datepart(dw, DateTimeReceived), DateTimeReceived) as startweek
             ,dateadd(day, 7-datepart(dw, DateTimeReceived), DateTimeReceived) as endweek
       from TableA_VW with (nolock)
       where DateTimeReceived between @DateStart and @DateEnd  ) w
group by startweek,endweek
order by 1


Now, if end of year is important and needs to be differentiated then the Week Commencing one could also be used - would need to put in the start/end dates for start of year, but it will group properly to enable that to happen. Please let me know how you want to handle change of years.

Expert of the Quarter 2010
Expert of the Year 2010

Commented:
pize,

Let us know once you have tried http:#a33926653 using it either verbatim or with Mark's query in http:#33927552.

I started out with the CTE because I thought the results may be needed more than once, but if not, I do prefer Mark's variant.  A subquery exposes the result to the main query the same way as a CTE.  Performance wise, there should be no significant difference and the plans should come out the same.

[ ergo I completely agree with the sentiment "CTE tends to be much of a muchness than the equivelent subqueries...." ]

When you do accept a solution, I would recommend a split to be fair.

Author

Commented:
I have tried this verbatim:-

;with t as (
select
  DATEADD(DAY, 1-DATEPART(dw, DateTimeReceived), DateTimeReceived) As WeekStartDate,  
  DATEADD(DAY, 7-DATEPART(dw, DateTimeReceived), DateTimeReceived) As WeekEndDate
from TableA_VW with (nolock)
where DateTimeReceived between @DateStart and @DateEnd
)
select
      convert(nvarchar(10), CASE WHEN WeekStartDate < @DateStart then @DateStart else WeekStartDate end,121) as WeekStartDate,
      convert(nvarchar(10), CASE WHEN WeekEndDate > @DateEnd then @DateEnd else WeekEndDate end,121) AS WeekEndDate,
      count(*) as Quantity
from t
group by WeekStartDate, WeekEndDate
order by 1

I do not have permissions to create tables in tempdb so just tried the actual retrieval statement against my datasource

I have also tried Marks Select statement in 33927552 verbatim.

Neither of those give me just a few weeks rows of results , they actually give me some 27,000 odd rows of results with the WeekStart and WeekEnd dates repeated numerous times.
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Hi pize,

I know what the problem is.
Your data contains time info, which on reflection your original query handled properly.
The below will work (notice I have switched to subquery form, like Mark's), but I will run some tests for performance.
use tempdb
--
create table TableA_VW(id int identity primary key clustered, DateTimeReceived datetime)
--
insert TableA_VW
select getdate()+ascii(newid()) + ascii(newid())*0.3+ascii(newid())*0.2
from master..spt_values a --cross join master..spt_values b

-- you should really have an index
create index ix_TableA_VW_DateTimeReceived on TableA_VW(DateTimeReceived)

select * from TableA_VW

-- test query
declare @DateStart datetime set @datestart = '20101231'
declare @Dateend   datetime set @dateend   = '20110111'

select
	convert(nvarchar(10), CASE WHEN WeekStartDate < @DateStart then @DateStart else WeekStartDate end,121) as WeekStartDate,
	convert(nvarchar(10), CASE WHEN WeekEndDate > @DateEnd then @DateEnd else WeekEndDate end,121) AS WeekEndDate,
	count(*) as Quantity
from (
	select
	  DATEADD(DAY, 1-DATEPART(dw, DateTimeReceived), convert(char(10),DateTimeReceived,120)) As WeekStartDate,  
	  DATEADD(DAY, 7-DATEPART(dw, DateTimeReceived), convert(char(10),DateTimeReceived,120)) As WeekEndDate
	from TableA_VW with (nolock)
	where DateTimeReceived between @DateStart and @DateEnd
) t
group by WeekStartDate, WeekEndDate
order by 1

Open in new window

Expert of the Quarter 2010
Expert of the Year 2010
Commented:
That didn't work well, or rather, was as bad (or as good) as the original.
Mark is right when he says that date maths is faster, because it is all working in the floating point register. Strings (varchar) are terribly expensive to work with.

Please find a version that works, and quickly!
select
	convert(char(10), CASE WHEN WeekStartDate < @DateStart then @DateStart else WeekStartDate end,121) as WeekStartDate,
	convert(char(10), CASE WHEN WeekEndDate > @DateEnd then @DateEnd else WeekEndDate end,121) AS WeekEndDate,
	count(*) as Quantity
from (
	select
	  DATEADD(DAY, DateDiff(d,0,DateTimeReceived), 1-DATEPART(dw, DateTimeReceived)) As WeekStartDate,  
	  DATEADD(DAY, DateDiff(d,0,DateTimeReceived), 7-DATEPART(dw, DateTimeReceived)) As WeekEndDate
	from TableA_VW with (nolock)
	where DateTimeReceived between @DateStart and @DateEnd
) t
group by WeekStartDate, WeekEndDate
order by 1

Open in new window

Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Yep, that should bo it just nicely...

Thought the time component was too good to be true (and explains why my group by week almost got there). You will need to add the 23:59:59 to the @dateend otherwise you will be missing any rows that have activity on that day. Per previous post, might try :

set @dateend = @dateend + 0.999999995

Or, if the query is running over the top of a view (as the _VW suggests), and you are using SQL2008 then you could also consider:

cast (datetimereceived as date) as datereceived

It is one of the very few functions that retains SARGability (meaning it will still use the index of dateandtime if it exists). Then you can use the more recent code without having to worry about converting to remove the time component.

Maybe much of a muchness in a single case, but if using the view for other purposes, might be worthwhile considering.

e.g. Just by way of example only...

select case when @datestart > startweek then @datestart else startweek end as startweek
       ,case when @dateend < endweek then @dateend else endweek end as endweek   -- note the < now fixed.
       ,count(*) as qty
from (
       select dateadd(day, 1-datepart(dw, DateReceived), DateReceived) as startweek
             ,dateadd(day, 7-datepart(dw, DateReceived), DateReceived) as endweek
       from TableA_VW with (nolock)
       where DateReceived between @DateStart and @DateEnd  ) w
group by startweek,endweek
order by 1


Just to show it (datetime) is nothing but a numbers game, have a look at (and not suggesting it as an answer - just the floating point arithmetic as a matter of interest) :


select
	CASE WHEN WeekStartDate < @DateStart then @DateStart else WeekStartDate end as WeekStartDate,
	CASE WHEN WeekEndDate > @DateEnd then @DateEnd else WeekEndDate end AS WeekEndDate,
	count(*) as Quantity
from (
	select
      cast(floor(cast (datetimereceived as float)) - (cast(floor(cast (datetimereceived as float)) as int)%7 + 2) as datetime) + 1 as weekstartdate,   -- the + 2 = datepart(dw,0)
      cast(floor(cast (datetimereceived as float)) - (cast(floor(cast (datetimereceived as float)) as int)%7 + 2) as datetime) + 7 as weekenddate 
	from TableA_VW with (nolock)
	where DateTimeReceived between @DateStart and @DateEnd
) t
group by WeekStartDate, WeekEndDate
order by 1

-- and cast(datetimereceived as date) is even quicker than raw maths over a few million rows...

Open in new window

Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018
Commented:
@cyberkiwi, Enjoyed working on this question with you. Luv ya work :)

@pize, think that cyberkiwi's post in http:#a33933709 should be what you are looking for. And if you feel so inclined, any "assisted" points would be most welcomed :)

Author

Commented:
Thanks for your help guys. Will split the points as per above suggestion

Author

Commented:
Prompt replies and knew their stuff

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial