Link to home
Start Free TrialLog in
Avatar of rsoble
rsoble

asked on

Make my stored procedure more efficient?

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
Avatar of rsoble
rsoble

ASKER

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.
Avatar of Partha Mandayam
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?
Avatar of rsoble

ASKER

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.
SOLUTION
Avatar of Snarf0001
Snarf0001
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rsoble

ASKER

Thanks for the code - I'll give both a tryout in a couple hours and report back on speed etc.
Avatar of rsoble

ASKER

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.


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.
Avatar of rsoble

ASKER

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.
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.
Avatar of rsoble

ASKER

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.
Avatar of rsoble

ASKER

Kselvia:  yours worked WITHOUT one tiny bit of a change.  NO changes :)
Except see the little comment above.  
Doh! My eyes are going out on me again :)
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
unless they were put in right at midnight
Avatar of rsoble

ASKER

I believe BETWEEN is INCLUSIVE, no?   I will check against my data...
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.
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.
Avatar of rsoble

ASKER

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.
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

Avatar of rsoble

ASKER

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...
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

Avatar of rsoble

ASKER

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
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.
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!