Solved

Make my stored procedure more efficient?

Posted on 2004-09-23
26
315 Views
Last Modified: 2008-02-01
This stored procedure is designed to return sales history data, broken down for Mon/Tue/Wed and Thu/Fri/Sat of five different week periods (the week specified by the date parameter, the three previous weeks, and the same week one year previous).   It takes a crop and a date as parameters.  Based on the given date, I
a) determine the day of the week so I can get the corresponding Monday and Thursday dates
b) determine the dates for the corresponding Monday and Thursday for the four other time periods
c) run a UNION query with all these different dates.

I run this query from an Access ADP and it returns rather slowly, much slower than other stored procedures I've written which, on the surface, seem to be more complicated.  It is returning all the correct data, I just want it to be faster.  Thank you!

Note all the casting I do in the SELECT statement is so that I can have a text representation of the date range in the Access ADP.

============================
CREATE PROCEDURE dbo.spOrderHistory
      @CropID nvarchar(25),
      @Date smalldatetime

AS

DECLARE @OrigDate smalldatetime
SET @OrigDate = @Date
--1)  get dates for current week

DECLARE @StartDay int
DECLARE @Mon smalldatetime
DECLARE @Thu smalldatetime
SET @StartDay = DATEPART(dw, @Date)
IF @StartDay = 1 SET @Mon = DATEADD(d, 1, @Date)  -- if Sunday, go to following Monday
IF @StartDay = 2 SET @Mon = @Date
IF @StartDay = 3 SET @Mon = DATEADD(d, -1, @Date)
IF @StartDay = 4 SET @Mon = DATEADD(d, -2, @Date)
IF @StartDay = 5 SET @Mon = DATEADD(d, -3, @Date)
IF @StartDay = 6 SET @Mon = DATEADD(d, -4, @Date)
IF @StartDay = 7 SET @Mon = DATEADD(d, -5, @Date)
SET @Thu = DATEADD(d,3,@Mon)

-- 2) get dates for previous week

DECLARE @Mon1 smalldatetime
DECLARE @Thu1 smalldatetime
SET @Date = DATEADD(ww, -1, @OrigDate)
SET @StartDay = DATEPART(dw, @Date)
IF @StartDay = 1 SET @Mon1 = DATEADD(d, 1, @Date)  -- if Sunday, go to following Monday
IF @StartDay = 2 SET @Mon1 = @Date
IF @StartDay = 3 SET @Mon1 = DATEADD(d, -1, @Date)
IF @StartDay = 4 SET @Mon1 = DATEADD(d, -2, @Date)
IF @StartDay = 5 SET @Mon1 = DATEADD(d, -3, @Date)
IF @StartDay = 6 SET @Mon1 = DATEADD(d, -4, @Date)
IF @StartDay = 7 SET @Mon1 = DATEADD(d, -5, @Date)
SET @Thu1 = DATEADD(d,3,@Mon1)

-- 3) get dates for 2 weeks ago

DECLARE @Mon2 smalldatetime
DECLARE @Thu2 smalldatetime
SET @Date = DATEADD(ww, -2, @OrigDate)
SET @StartDay = DATEPART(dw, @Date)
IF @StartDay = 1 SET @Mon2 = DATEADD(d, 1, @Date)  -- if Sunday, go to following Monday
IF @StartDay = 2 SET @Mon2 = @Date
IF @StartDay = 3 SET @Mon2 = DATEADD(d, -1, @Date)
IF @StartDay = 4 SET @Mon2 = DATEADD(d, -2, @Date)
IF @StartDay = 5 SET @Mon2 = DATEADD(d, -3, @Date)
IF @StartDay = 6 SET @Mon2 = DATEADD(d, -4, @Date)
IF @StartDay = 7 SET @Mon2 = DATEADD(d, -5, @Date)
SET @Thu2 = DATEADD(d,3,@Mon2)

-- 4) get dates for three weeks ago

DECLARE @Mon3 smalldatetime
DECLARE @Thu3 smalldatetime
SET @Date = DATEADD(ww, -3, @OrigDate)
SET @StartDay = DATEPART(dw, @Date)
IF @StartDay = 1 SET @Mon3 = DATEADD(d, 1, @Date)  -- if Sunday, go to following Monday
IF @StartDay = 2 SET @Mon3 = @Date
IF @StartDay = 3 SET @Mon3 = DATEADD(d, -1, @Date)
IF @StartDay = 4 SET @Mon3 = DATEADD(d, -2, @Date)
IF @StartDay = 5 SET @Mon3 = DATEADD(d, -3, @Date)
IF @StartDay = 6 SET @Mon3 = DATEADD(d, -4, @Date)
IF @StartDay = 7 SET @Mon3 = DATEADD(d, -5, @Date)
SET @Thu3 = DATEADD(d,3,@Mon3)

-- 5) get dates for previous year

DECLARE @Mon4 smalldatetime
DECLARE @Thu4 smalldatetime
SET @Date = DATEADD(yy, -1, @OrigDate)
SET @StartDay = DATEPART(dw, @Date)
IF @StartDay = 1 SET @Mon4 = DATEADD(d, 1, @Date)  -- if Sunday, go to following Monday
IF @StartDay = 2 SET @Mon4 = @Date
IF @StartDay = 3 SET @Mon4 = DATEADD(d, -1, @Date)
IF @StartDay = 4 SET @Mon4 = DATEADD(d, -2, @Date)
IF @StartDay = 5 SET @Mon4 = DATEADD(d, -3, @Date)
IF @StartDay = 6 SET @Mon4 = DATEADD(d, -4, @Date)
IF @StartDay = 7 SET @Mon4 = DATEADD(d, -5, @Date)
SET @Thu4 = DATEADD(d,3,@Mon4)


-- get dates and totals for current week
SELECT       
            DISTINCT CAST(MONTH(@Mon) as varchar) + '/' + CAST(DAY(@Mon)  as varchar) + '/' + CAST(YEAR(@Mon)  as varchar) + ' - ' + CAST(MONTH(DATEADD(d,2,@Thu))  as varchar) + '/' + CAST(DAY(DATEADD(d,2,@Thu)) as varchar) + '/' + CAST(YEAR(DATEADD(d,2,@Thu)) as varchar) AS DateRange,
                          (SELECT     ISNULL(SUM(dbo.tblInvoiceLineItems.fld_iOrdered), 0)
                            FROM          dbo.tblInvoiceLineItems
                            WHERE      fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.fld_dtDate BETWEEN @Mon AND DATEADD(d,2,@Mon))) AS Total1,
                          (SELECT     ISNULL(SUM(dbo.tblInvoiceLineItems.fld_iOrdered), 0)
                            FROM          dbo.tblInvoiceLineItems
                            WHERE      fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.fld_dtDate BETWEEN @Thu AND DATEADD(d,2,@Thu))) AS Total2
FROM         dbo.tblInvoiceLineItems


-- get dates and totals for previous week
UNION
SELECT       
            DISTINCT CAST(MONTH(@Mon1) as varchar) + '/' + CAST(DAY(@Mon1)  as varchar) + '/' + CAST(YEAR(@Mon1)  as varchar) + ' - ' + CAST(MONTH(DATEADD(d,2,@Thu1))  as varchar) + '/' + CAST(DAY(DATEADD(d,2,@Thu1)) as varchar) + '/' + CAST(YEAR(DATEADD(d,2,@Thu1)) as varchar) AS DateRange,
                          (SELECT     ISNULL(SUM(dbo.tblInvoiceLineItems.fld_iOrdered), 0)
                            FROM          dbo.tblInvoiceLineItems
                            WHERE      fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.fld_dtDate BETWEEN @Mon1 AND DATEADD(d,2,@Mon1))) AS Total1,
                          (SELECT     ISNULL(SUM(dbo.tblInvoiceLineItems.fld_iOrdered), 0)
                            FROM          dbo.tblInvoiceLineItems
                            WHERE      fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.fld_dtDate BETWEEN @Thu1 AND DATEADD(d,2,@Thu1))) AS Total2
FROM         dbo.tblInvoiceLineItems


-- get dates and totals for 2 weeks prior
UNION
SELECT       
            DISTINCT CAST(MONTH(@Mon2) as varchar) + '/' + CAST(DAY(@Mon2)  as varchar) + '/' + CAST(YEAR(@Mon2)  as varchar) + ' - ' + CAST(MONTH(DATEADD(d,2,@Thu2))  as varchar) + '/' + CAST(DAY(DATEADD(d,2,@Thu2)) as varchar) + '/' + CAST(YEAR(DATEADD(d,2,@Thu2)) as varchar) AS DateRange,
                          (SELECT     ISNULL(SUM(dbo.tblInvoiceLineItems.fld_iOrdered), 0)
                            FROM          dbo.tblInvoiceLineItems
                            WHERE      fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.fld_dtDate BETWEEN @Mon2 AND DATEADD(d,2,@Mon2))) AS Total1,
                          (SELECT     ISNULL(SUM(dbo.tblInvoiceLineItems.fld_iOrdered), 0)
                            FROM          dbo.tblInvoiceLineItems
                            WHERE      fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.fld_dtDate BETWEEN @Thu2 AND DATEADD(d,2,@Thu2))) AS Total2
FROM         dbo.tblInvoiceLineItems


-- get dates and totals for 3 weeks prior
UNION
SELECT       
            DISTINCT CAST(MONTH(@Mon3) as varchar) + '/' + CAST(DAY(@Mon3)  as varchar) + '/' + CAST(YEAR(@Mon3)  as varchar) + ' - ' + CAST(MONTH(DATEADD(d,2,@Thu3))  as varchar) + '/' + CAST(DAY(DATEADD(d,2,@Thu3)) as varchar) + '/' + CAST(YEAR(DATEADD(d,2,@Thu3)) as varchar) AS DateRange,
                          (SELECT     ISNULL(SUM(dbo.tblInvoiceLineItems.fld_iOrdered), 0)
                            FROM          dbo.tblInvoiceLineItems
                            WHERE      fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.fld_dtDate BETWEEN @Mon3 AND DATEADD(d,2,@Mon3))) AS Total1,
                          (SELECT     ISNULL(SUM(dbo.tblInvoiceLineItems.fld_iOrdered), 0)
                            FROM          dbo.tblInvoiceLineItems
                            WHERE      fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.fld_dtDate BETWEEN @Thu3 AND DATEADD(d,2,@Thu3))) AS Total2
FROM         dbo.tblInvoiceLineItems


-- get dates and totals for 1 yr ago
UNION
SELECT       
            DISTINCT CAST(MONTH(@Mon4) as varchar) + '/' + CAST(DAY(@Mon4)  as varchar) + '/' + CAST(YEAR(@Mon4)  as varchar) + ' - ' + CAST(MONTH(DATEADD(d,2,@Thu4))  as varchar) + '/' + CAST(DAY(DATEADD(d,2,@Thu4)) as varchar) + '/' + CAST(YEAR(DATEADD(d,2,@Thu4)) as varchar) AS DateRange,
                          (SELECT     ISNULL(SUM(dbo.tblInvoiceLineItems.fld_iOrdered), 0)
                            FROM          dbo.tblInvoiceLineItems
                            WHERE      fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.fld_dtDate BETWEEN @Mon4 AND DATEADD(d,2,@Mon4))) AS Total1,
                          (SELECT     ISNULL(SUM(dbo.tblInvoiceLineItems.fld_iOrdered), 0)
                            FROM          dbo.tblInvoiceLineItems
                            WHERE      fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.fld_dtDate BETWEEN @Thu4 AND DATEADD(d,2,@Thu4))) AS Total2
FROM         dbo.tblInvoiceLineItems
GO
============================
Results look like this (sorry for the funky non-tabs):  This was for a date entered of 8/18/2004.
I don't bother to sort in the stored procedure, but let the ADP do the sorting by the date range.  However I'm not against sorting here if you can do that.  I want them in reverse date order (most recent at top).

DateRange                                 Total1      Total2
8/16/2004 - 8/21/2004      6      3
8/9/2004 - 8/14/2004                     5      12
8/2/2004 - 8/7/2004                       2      5
7/26/2004 - 7/31/2004      1      5
8/18/2003 - 8/23/2003      7      6
0
Comment
Question by:rsoble
  • 11
  • 8
  • 6
  • +1
26 Comments
 
LVL 1

Author Comment

by:rsoble
ID: 12136477
Oh another note, perhaps the DISTINCT is what is slowing this down...  each SELECT would actually return many records if I didn't put in the DISTINCT....  hmmm.
0
 
LVL 6

Expert Comment

by:mcp111
ID: 12136820
Can you post another stored procedure which you feel is more complex and runs faster?
Since you are running from an Access ADP, it will run slower than if you ran directly in SQL. Also the volume of data should be considered since Access is not scalable at higher data volume. Do you have to run it from an Access ADP?
0
 
LVL 1

Author Comment

by:rsoble
ID: 12136989
I'm reluctant to be posting all my code up here, but I will say this.... I have another SP that is very similar in terms of the date processing part (the first half of the sp) but does a non-UNION, non-DISTINCT query (but more complex in that it has several JOINs, WHEREs, and GROUP BYs).  Run in the Query Analyzer, it runs with a finish time of :01 whereas this query, in the Query Analyzer runs with :02 or :03.  It's obviously slower.  The addition of the ADP on top just exacerbates that, but it's not the cause of the speed problem.

I was hoping someone would see some obvious problems (like with the DISTINCT being used to eliminate duplicate records ... I will have to research that more) or perhaps a more efficient query structure that would be possible.

Yes, I do have to run from ADP.  That's the front end.  Thank you for your help.
0
 
LVL 22

Assisted Solution

by:Snarf0001
Snarf0001 earned 250 total points
ID: 12137254
you're losing a LOT of time using the distinct and the subquery SUMs
try this:


CREATE PROCEDURE dbo.spOrderHistory
     @CropID nvarchar(25),
     @Date smalldatetime

AS

declare @mon datetime, @sat datetime
declare @mon1 datetime, @sat1 datetime
declare @mon2 datetime, @sat2 datetime
declare @mon3 datetime, @sat3 datetime
declare @mon4 datetime, @sat4 datetime
declare @nStart int

set @nStart = datepart(dw, @date)

if(@nStart = 1) set @mon = dateadd(d, 1, @date)
else set @mon = dateadd(d, (-1 * @nStart), @date)

set @sat = dateadd(d, 5, @mon)

--trim time off the dates so they appear as midnight, not whatever time it is now
set @mon = convert(datetime, convert(varchar(10), @mon, 101))
set @sat = convert(datetime, convert(varchar(10), @sat, 101))

--last week
set @mon1 = dateadd(ww, -1, @mon)
set @sat1 = dateadd(ww, -1, @sat)

--2 weeks ago
set @mon2 = dateadd(ww, -2 @mon)
set @sat2 = dateadd(ww, -2 @sat)

--3 weeks ago
set @mon3 = dateadd(ww, -3 @mon)
set @sat3 = dateadd(ww, -3 @sat)

--1 year ago
set @mon4 = dateadd(yy,-1, @mon)
set @sat4 = dateadd(yy, -1, @sat)

select
(convert(varchar(10), @mon, 101) + ' ' +  convert(varchar(10), @sat, 101)) as DateRange,
sum(case when (datepart(dw, dbo.tblInvoiceLineItems.fld_dtDate) < 5) then dbo.tblInvoiceLineItems.fld_iOrdered else 0 end) as Total1,
sum(case when (datepart(dw, dbo.tblInvoiceLineItems.fld_dtDate) < 5) then 0 else dbo.tblInvoiceLineItems.fld_iOrdered end) as Total2
from dbo.tblInvoiceLineItems
where fld_txtCropID=@CropID AND (dbo.tblInvoiceLineItems.fld_dtDate BETWEEN @mon AND DATEADD(d,1,@sat))
union
select
(convert(varchar(10), @mon1, 101) + ' ' +  convert(varchar(10), @sat1, 101)) as DateRange,
sum(case when (datepart(dw, dbo.tblInvoiceLineItems.fld_dtDate) < 5) then dbo.tblInvoiceLineItems.fld_iOrdered else 0 end) as Total1,
sum(case when (datepart(dw, dbo.tblInvoiceLineItems.fld_dtDate) < 5) then 0 else dbo.tblInvoiceLineItems.fld_iOrdered end) as Total2
from dbo.tblInvoiceLineItems
where fld_txtCropID=@CropID AND (dbo.tblInvoiceLineItems.fld_dtDate BETWEEN @mon1 AND DATEADD(d,1,@sat1))
union
select
(convert(varchar(10), @mon2, 101) + ' ' +  convert(varchar(10), @sat2, 101)) as DateRange,
sum(case when (datepart(dw, dbo.tblInvoiceLineItems.fld_dtDate) < 5) then dbo.tblInvoiceLineItems.fld_iOrdered else 0 end) as Total1,
sum(case when (datepart(dw, dbo.tblInvoiceLineItems.fld_dtDate) < 5) then 0 else dbo.tblInvoiceLineItems.fld_iOrdered end) as Total2
from dbo.tblInvoiceLineItems
where fld_txtCropID=@CropID AND (dbo.tblInvoiceLineItems.fld_dtDate BETWEEN @mon2 AND DATEADD(d,1,@sat2))
union
select
(convert(varchar(10), @mon3, 101) + ' ' +  convert(varchar(10), @sat3, 101)) as DateRange,
sum(case when (datepart(dw, dbo.tblInvoiceLineItems.fld_dtDate) < 5) then dbo.tblInvoiceLineItems.fld_iOrdered else 0 end) as Total1,
sum(case when (datepart(dw, dbo.tblInvoiceLineItems.fld_dtDate) < 5) then 0 else dbo.tblInvoiceLineItems.fld_iOrdered end) as Total2
from dbo.tblInvoiceLineItems
where fld_txtCropID=@CropID AND (dbo.tblInvoiceLineItems.fld_dtDate BETWEEN @mon3 AND DATEADD(d,1,@sat3))
union
select
(convert(varchar(10), @mon4, 101) + ' ' +  convert(varchar(10), @sat4, 101)) as DateRange,
sum(case when (datepart(dw, dbo.tblInvoiceLineItems.fld_dtDate) < 5) then dbo.tblInvoiceLineItems.fld_iOrdered else 0 end) as Total1,
sum(case when (datepart(dw, dbo.tblInvoiceLineItems.fld_dtDate) < 5) then 0 else dbo.tblInvoiceLineItems.fld_iOrdered end) as Total2
from dbo.tblInvoiceLineItems
where fld_txtCropID=@CropID AND (dbo.tblInvoiceLineItems.fld_dtDate BETWEEN @mon4 AND DATEADD(d,1,@sat4))




0
 
LVL 12

Accepted Solution

by:
kselvia earned 250 total points
ID: 12140702
Nice rewrite Snarf. I don't want the points but I thought there was a way to write this without the unions so I played with it a bit. I can't really test it with data but I believe this should return the same thing as your original procedure.  (It will show a leading '0' for single digit months, but that's what convert code 101 does. You can expand it again the way you had it if you need to.)

SELECT
convert(varchar,Days.Mon,101) + ' - ' + convert(varchar, Dateadd(dd,5,Days.Mon),101) AS DateRange,
sum(case when dbo.tblInvoiceLineItems.fld_dtDate between Days.Mon and Dateadd(dd,2,Days.Mon) Then dbo.tblInvoiceLineItems.fld_iOrdered Else 0 End) Total1,
sum(case when dbo.tblInvoiceLineItems.fld_dtDate between Dateadd(dd,2,Days.Mon) and Dateadd(dd,5,Days.Mon) Then dbo.tblInvoiceLineItems.fld_iOrdered Else 0 End) Total2
FROM dbo.tblInvoiceLineItems,
(
      select dateadd(dd,(datepart(dw,@Date) - 2) * -1 + x.id * -1,@Date) Mon From
      (select 0 id union select 7 union select 14 union select 21 union
            select datediff(dd,Dateadd(yy,-1,@Date),dateadd(dd,(datepart(dw,@Date) - 2) * -1,@Date))) x
) Days
WHERE fld_txtCropID=@CropID AND (dbo.tblInvoiceLineItems.fld_dtDate BETWEEN Days.Mon AND Dateadd(dd,5,Days.Mon))
GROUP BY Days.Mon

If you wonder how it can work, consider the derived table Days. See below how it will return a table with your desired mondays.
You can join to it as I did above and group by the mondays to allow you to eliminate the individual queries.

declare @Date smalldatetime
set @Date = '8-18-04'

SELECT Mon FROM
(
      select dateadd(dd,(datepart(dw,@Date) - 2) * -1 + x.id * -1,@Date) Mon From
      (select 0 id union select 7 union select 14 union select 21 union
            select datediff(dd,Dateadd(yy,-1,@Date),dateadd(dd,(datepart(dw,@Date) - 2) * -1,@Date))) x
) Days

Mon                                                    
------------------------------------------------------
2004-08-16 00:00:00
2004-08-09 00:00:00
2004-08-02 00:00:00
2004-07-26 00:00:00
2003-08-18 00:00:00
0
 
LVL 1

Author Comment

by:rsoble
ID: 12143080
Thanks for the code - I'll give both a tryout in a couple hours and report back on speed etc.
0
 
LVL 1

Author Comment

by:rsoble
ID: 12144143
Ok, both solutions worked for the most part.  And both ran lickety-split.

Kselvia:  yours worked without one tiny bit of a change.  Ran it and it produced the same results as my original.

Snarf:  yours required a couple commas added to pass the syntax check, easy to find.  It also didn't get quite the right dates, but I think that too would be simple to fix.  

I'm going to use Kselvia's solution, but split the points evenly between the two of you, since Snarf did provide a workable solution quite quickly, and got the ball rolling....  I hope that's satisfactory.  I really appreciate your help!  Thanks again.


0
 
LVL 12

Expert Comment

by:kselvia
ID: 12144202
Glad my idea worked. What did you have to change (in case someone else needs the same technique and I point them to this post)

BTW: You can easily add an ORDER BY Days.Mon to the query to make it sort by date.
0
 
LVL 1

Author Comment

by:rsoble
ID: 12144282
The syntax errors were in Snarf's post:
======
--2 weeks ago
set @mon2 = dateadd(ww, -2 @mon)  --<--- needs a comma after the -2, same for the three lines below
set @sat2 = dateadd(ww, -2 @sat)

--3 weeks ago
set @mon3 = dateadd(ww, -3 @mon)
set @sat3 = dateadd(ww, -3 @sat)
======

As for the duplicate date problems, I didn't dig in to see what was causing that, since I was going to use the other solution.  Probably just not resetting the variable name after copying the union select.

Thanks again.
0
 
LVL 12

Expert Comment

by:kselvia
ID: 12144309
Oh OK. You had said 'Kselvia:  yours worked without one tiny bit of a change.'  I was wondering what change that was since I can't actually run it myself.
0
 
LVL 1

Author Comment

by:rsoble
ID: 12144324
Ah,  I do see one small problem with kselvia's solution:  If there is a week with 0 sales for M/T/W and 0 sales for T/F/S, the row is left out rather than showing the date and the two zero Totals.
0
 
LVL 1

Author Comment

by:rsoble
ID: 12144341
Kselvia:  yours worked WITHOUT one tiny bit of a change.  NO changes :)
Except see the little comment above.  
0
 
LVL 12

Expert Comment

by:kselvia
ID: 12144364
Doh! My eyes are going out on me again :)
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 22

Expert Comment

by:Snarf0001
ID: 12144367
A word of caution, the dates you're returning might not actually reflect what you want.

You can see in my posting that I have

  dbo.tblInvoiceLineItems.fld_dtDate BETWEEN @mon AND DATEADD(d,1,@sat))

This will grab dates between midnight monday, and midnight SUNDAY morning, as you said you wanted mon/tues/wed, and thurs/fri/sat.  In your original code, and in kselvia's (no offence, quite eloquently written table), you have

  dbo.tblInvoiceLineItems.fld_dtDate BETWEEN @Thu AND DATEADD(d,2,@Thu))
and
  dbo.tblInvoiceLineItems.fld_dtDate BETWEEN Days.Mon AND Dateadd(dd,5,Days.Mon))

which will grab up until midnight saturday morning, thus excluding all orders put in on saturday
0
 
LVL 22

Expert Comment

by:Snarf0001
ID: 12144387
unless they were put in right at midnight
0
 
LVL 1

Author Comment

by:rsoble
ID: 12144433
I believe BETWEEN is INCLUSIVE, no?   I will check against my data...
0
 
LVL 22

Expert Comment

by:Snarf0001
ID: 12144471
You are right, it is inclusive, but all date variables in sql server still include a time portion.
Thus "09-04-2004" passed as a "between" is technically up to and including "09-04-2004 0:00:00".

And order put in at "09-04-2004 0:00:01" would be past that, and not included.
0
 
LVL 12

Expert Comment

by:kselvia
ID: 12144481
You can make my version show dates with no orders by changing it to:

SELECT
convert(varchar,Days.Mon,101) + ' - ' + convert(varchar, Dateadd(dd,5,Days.Mon),101) AS DateRange,
sum(case when dbo.tblInvoiceLineItems.fld_dtDate between Days.Mon and Dateadd(dd,2,Days.Mon) Then dbo.tblInvoiceLineItems.fld_iOrdered Else 0 End) Total1,
sum(case when dbo.tblInvoiceLineItems.fld_dtDate between Dateadd(dd,2,Days.Mon) and Dateadd(dd,5,Days.Mon) Then dbo.tblInvoiceLineItems.fld_iOrdered Else 0 End) Total2
FROM dbo.tblInvoiceLineItems
LEFT OUTER JOIN
(
     select dateadd(dd,(datepart(dw,@Date) - 2) * -1 + x.id * -1,@Date) Mon From
     (select 0 id union select 7 union select 14 union select 21 union
          select datediff(dd,Dateadd(yy,-1,@Date),dateadd(dd,(datepart(dw,@Date) - 2) * -1,@Date))) x
) Days ON dbo.tblInvoiceLineItems.fld_dtDate BETWEEN Days.Mon AND Dateadd(dd,5,Days.Mon)
WHERE fld_txtCropID=@CropID
GROUP BY Days.Mon

but Snarf may be right about it not getting the right days you expect - but it should work like your original.
0
 
LVL 1

Author Comment

by:rsoble
ID: 12144510
Thanks to you both.  I will check my dates diligently and make sure the resulting data matches what is in the tables.  I'll post my results here when I'm done.  Thanks Snarf for the heads up.
0
 
LVL 22

Expert Comment

by:Snarf0001
ID: 12144558
np

0
 
LVL 12

Expert Comment

by:kselvia
ID: 12145054
That LEFT OUTER JOIN was a little off.  I made up some test data just to make sure this time.  This should do it:

create table tblInvoiceLineItems (fld_txtCropID int, fld_dtDate datetime, fld_iOrdered int)
insert tblInvoiceLineItems select 1, '8-17-05', 6
insert tblInvoiceLineItems select 1, '8-17-04', 6
insert tblInvoiceLineItems select 1, '8-10-04', 5
--insert tblInvoiceLineItems select 1, '8-3-04',  2
insert tblInvoiceLineItems select 1, '7-27-04', 1
insert tblInvoiceLineItems select 1, '8-19-03', 7

insert tblInvoiceLineItems select 1, '8-20-05', 3
insert tblInvoiceLineItems select 1, '8-20-04', 3
insert tblInvoiceLineItems select 1, '8-13-04', 12
--insert tblInvoiceLineItems select 1, '8-6-04',  5
insert tblInvoiceLineItems select 1, '7-30-04', 5
insert tblInvoiceLineItems select 1, '8-22-03', 6


insert tblInvoiceLineItems select 2, '8-17-05', 6
insert tblInvoiceLineItems select 2, '8-17-04', 6
insert tblInvoiceLineItems select 2, '8-10-04', 5
--insert tblInvoiceLineItems select 2, '8-3-04',  2
insert tblInvoiceLineItems select 2, '7-27-04', 1
insert tblInvoiceLineItems select 2, '8-19-03', 7

insert tblInvoiceLineItems select 2, '8-20-05', 3
insert tblInvoiceLineItems select 2, '8-20-04', 3
insert tblInvoiceLineItems select 2, '8-13-04', 12
--insert tblInvoiceLineItems select 2, '8-6-04',  5
insert tblInvoiceLineItems select 2, '7-30-04', 5
insert tblInvoiceLineItems select 2, '8-22-03', 6


declare @Date smalldatetime
set @Date = '8-18-04'

declare @CropID int
set @CropID = 1

SELECT
convert(varchar,Days.Mon,101) + ' - ' + convert(varchar, Dateadd(dd,5,Days.Mon),101) AS DateRange,
sum(case when dbo.tblInvoiceLineItems.fld_dtDate between Days.Mon and Dateadd(dd,2,Days.Mon) Then dbo.tblInvoiceLineItems.fld_iOrdered Else 0 End) Total1,
sum(case when dbo.tblInvoiceLineItems.fld_dtDate between Dateadd(dd,2,Days.Mon) and Dateadd(dd,5,Days.Mon) Then dbo.tblInvoiceLineItems.fld_iOrdered Else 0 End) Total2
FROM
(
     select dateadd(dd,(datepart(dw,@Date) - 2) * -1 + x.id * -1,@Date) Mon From
     (select 0 id union select 7 union select 14 union select 21 union
          select datediff(dd,Dateadd(yy,-1,@Date),dateadd(dd,(datepart(dw,@Date) - 2) * -1,@Date))) x
) Days
LEFT OUTER JOIN dbo.tblInvoiceLineItems
ON dbo.tblInvoiceLineItems.fld_dtDate BETWEEN Days.Mon AND Dateadd(dd,5,Days.Mon) AND fld_txtCropID=@CropID
GROUP BY Days.Mon
ORDER BY Days.Mon

DateRange                Total1      Total2      
------------------------------------ -----------
08/18/2003 - 08/23/2003  7           6
07/26/2004 - 07/31/2004  1           5
08/02/2004 - 08/07/2004  0           0
08/09/2004 - 08/14/2004  5           12
08/16/2004 - 08/21/2004  6           3

0
 
LVL 1

Author Comment

by:rsoble
ID: 12145115
Yeah I noticed that didn't work quite right.  I played with it and found this solution:

SELECT     CONVERT(varchar, Days.Mon, 101) + ' - ' + CONVERT(varchar, Dateadd(dd, 5, Days.Mon), 101) AS DateRange,
                      SUM(CASE WHEN dbo.tblInvoiceLineItems.fld_dtDate BETWEEN Days.Mon AND Dateadd(dd, 2, Days.Mon)
                      THEN dbo.tblInvoiceLineItems.fld_iOrdered ELSE 0 END) Total1, SUM(CASE WHEN dbo.tblInvoiceLineItems.fld_dtDate BETWEEN Dateadd(dd, 2,
                      Days.Mon) AND Dateadd(dd, 5, Days.Mon) THEN dbo.tblInvoiceLineItems.fld_iOrdered ELSE 0 END) Total2
FROM         dbo.tblInvoiceLineItems,
                          (SELECT     DATEADD(dd, (DATEPART(dw, @Date) - 2) * - 1 + id * - 1, @Date) AS Mon
                            FROM          (SELECT     0 id
                                                    UNION
                                                    SELECT     7
                                                    UNION
                                                    SELECT     14
                                                    UNION
                                                    SELECT     21
                                                    UNION
                                                    SELECT     datediff(dd, Dateadd(yy, - 1, @Date), dateadd(dd, (datepart(dw, @Date) - 2) * - 1, @Date))) x) Days
WHERE     fld_txtCropID =  @CropID
GROUP BY Days.Mon
ORDER BY Days.Mon DESC



But I didn't get the date/BETWEEN stuff sorted out yet, as I first have to get a question answered by my customer about the dates...
0
 
LVL 12

Expert Comment

by:kselvia
ID: 12145955
Removing the join criteria like you did generates what is called a cartesion product - joing every row of Days to every row of tblInvoiceItems. The end result is still correct because your CASE statement converts undesired rows to zero values.  That means for even a small 10 row table, more than 150 additional rows are processed (as you can see by running 'set statisitics profile on' and running the query) and execution time is 4 times longer than if you use the LEFT OUTER JOIN clause.

I also realized that my solution did not always calculate the prior years Monday correctly. (It did for your example, but not all dates.)  This should fix it:

SELECT
convert(varchar,Days.Mon,101) + ' - ' + convert(varchar, Dateadd(dd,5,Days.Mon),101) AS DateRange,
sum(case when dbo.tblInvoiceLineItems.fld_dtDate between Days.Mon and Dateadd(dd,2,Days.Mon) Then dbo.tblInvoiceLineItems.fld_iOrdered Else 0 End) Total1,
sum(case when dbo.tblInvoiceLineItems.fld_dtDate between Dateadd(dd,2,Days.Mon) and Dateadd(dd,5,Days.Mon) Then dbo.tblInvoiceLineItems.fld_iOrdered Else 0 End) Total2
FROM
(
     select dateadd(dd,(datepart(dw,@Date) - 2) * -1 + x.id * -1,@Date) Mon From
     (select 0 id union select 7 union select 14 union select 21 union
          select Datediff(dd,dateadd(dd,(datepart(dw,DateAdd(yy,-1,@Date)) - 2) * -1,
             DateAdd(yy,-1,@Date)),dateadd(dd,(datepart(dw,@Date) - 2) * -1,@Date))
) x
) Days
LEFT OUTER JOIN dbo.tblInvoiceLineItems
ON dbo.tblInvoiceLineItems.fld_dtDate BETWEEN Days.Mon AND Dateadd(dd,5,Days.Mon) AND fld_txtCropID=@CropID
GROUP BY Days.Mon
ORDER BY Days.Mon

0
 
LVL 1

Author Comment

by:rsoble
ID: 12148721
Hi again.  Finally got back to this (Friday night, 9pm yahoo! :)

Totally overlooked that the fld_dtDate field in the table is actually also a shortdatetime, so the between seems to work just fine with the Mon-Wed, Thu-Sat time frames.  When I thought I had "fixed" it, the numbers didn't match what was in the tables at all.  Feel free to argue with me if you think I'm still missing something, though I did check it against the table data.

Finally, my customers asked for a slight change to the date ranges to look at (three weeks from current year, same three weeks from previous year), so this final version includes that as well, along with a column just for the Monday so I can do some conditional formatting in Access based on the date.  So here's what should be pretty close to final.  Thanks again.  

CREATE PROCEDURE dbo.spOrderHistory
      @CropID nvarchar(25),
      @Date smalldatetime

AS
SELECT     Mon, CONVERT(varchar, Days.Mon, 101) + ' - ' + CONVERT(varchar, Dateadd(dd, 5, Days.Mon), 101) AS DateRange,
                      SUM(CASE WHEN dbo.tblInvoiceLineItems.fld_dtDate BETWEEN Days.Mon AND Dateadd(dd, 2, Days.Mon)
                      THEN dbo.tblInvoiceLineItems.fld_iOrdered ELSE 0 END) Total1, SUM(CASE WHEN dbo.tblInvoiceLineItems.fld_dtDate BETWEEN Dateadd(dd, 2,
                      Days.Mon) AND Dateadd(dd, 5, Days.Mon) THEN dbo.tblInvoiceLineItems.fld_iOrdered ELSE 0 END) Total2
FROM         (SELECT     dateadd(dd, (datepart(dw,@Date) - 2) * - 1 + x.id * - 1, @Date) Mon
                       FROM          (SELECT     0 id
                                               UNION
                                               SELECT     7
                                               UNION
                                               SELECT     14
                                               UNION
                                               SELECT     Datediff(dd, dateadd(dd, (datepart(dw, DateAdd(yy, - 1, @Date)) - 2) * - 1, DateAdd(yy, - 1, @Date)), dateadd(dd,
                                                                     (datepart(dw, @Date) - 2) * - 1, @Date))
                                               UNION
                                               SELECT     (Datediff(dd, dateadd(dd, (datepart(dw, DateAdd(yy, - 1, @Date)) - 2) * - 1, DateAdd(yy, - 1, @Date)), dateadd(dd,
                                                                     (datepart(dw, @Date) - 2) * - 1, @Date)) + 7)
                                               UNION
                                               SELECT     (Datediff(dd, dateadd(dd, (datepart(dw, DateAdd(yy, - 1, @Date)) - 2) * - 1, DateAdd(yy, - 1, @Date)), dateadd(dd,
                                                                     (datepart(dw, @Date) - 2) * - 1,@Date))) + 14) x) Days LEFT OUTER JOIN
                      dbo.tblInvoiceLineItems ON dbo.tblInvoiceLineItems.fld_dtDate BETWEEN Days.Mon AND Dateadd(dd, 5, Days.Mon) AND
                      fld_txtCropID = @CropID
GROUP BY Days.Mon
ORDER BY Days.Mon DESC
GO
0
 
LVL 22

Expert Comment

by:Snarf0001
ID: 12148733
Smalldatetime does still record a time on it, it just cuts off milliseconds.

I'm glad it seems not to have caused you any problems, perhaps you're system cuts of the time when inserting, I can't really say since I don't know how it's functioning.  Be aware though, that "between" will still exclude saturdays if they have any time with them.
0
 
LVL 12

Expert Comment

by:kselvia
ID: 12148754
There are lots of ways to strip time from a date but the most efficient I know of is:

SET @Date = DATEADD(day, DATEDIFF(day, 0, @Date), 0)

No character conversion and only integer addition. If you put that at the beginning of your procedure it will strip any time from your date if there is a time.

Good luck!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now