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,@Th u)) as varchar) + '/' + CAST(DAY(DATEADD(d,2,@Thu) ) as varchar) + '/' + CAST(YEAR(DATEADD(d,2,@Thu )) as varchar) AS DateRange,
(SELECT ISNULL(SUM(dbo.tblInvoiceL ineItems.f ld_iOrdere d), 0)
FROM dbo.tblInvoiceLineItems
WHERE fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.f ld_dtDate BETWEEN @Mon AND DATEADD(d,2,@Mon))) AS Total1,
(SELECT ISNULL(SUM(dbo.tblInvoiceL ineItems.f ld_iOrdere d), 0)
FROM dbo.tblInvoiceLineItems
WHERE fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.f ld_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,@Th u1)) as varchar) + '/' + CAST(DAY(DATEADD(d,2,@Thu1 )) as varchar) + '/' + CAST(YEAR(DATEADD(d,2,@Thu 1)) as varchar) AS DateRange,
(SELECT ISNULL(SUM(dbo.tblInvoiceL ineItems.f ld_iOrdere d), 0)
FROM dbo.tblInvoiceLineItems
WHERE fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.f ld_dtDate BETWEEN @Mon1 AND DATEADD(d,2,@Mon1))) AS Total1,
(SELECT ISNULL(SUM(dbo.tblInvoiceL ineItems.f ld_iOrdere d), 0)
FROM dbo.tblInvoiceLineItems
WHERE fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.f ld_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,@Th u2)) as varchar) + '/' + CAST(DAY(DATEADD(d,2,@Thu2 )) as varchar) + '/' + CAST(YEAR(DATEADD(d,2,@Thu 2)) as varchar) AS DateRange,
(SELECT ISNULL(SUM(dbo.tblInvoiceL ineItems.f ld_iOrdere d), 0)
FROM dbo.tblInvoiceLineItems
WHERE fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.f ld_dtDate BETWEEN @Mon2 AND DATEADD(d,2,@Mon2))) AS Total1,
(SELECT ISNULL(SUM(dbo.tblInvoiceL ineItems.f ld_iOrdere d), 0)
FROM dbo.tblInvoiceLineItems
WHERE fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.f ld_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,@Th u3)) as varchar) + '/' + CAST(DAY(DATEADD(d,2,@Thu3 )) as varchar) + '/' + CAST(YEAR(DATEADD(d,2,@Thu 3)) as varchar) AS DateRange,
(SELECT ISNULL(SUM(dbo.tblInvoiceL ineItems.f ld_iOrdere d), 0)
FROM dbo.tblInvoiceLineItems
WHERE fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.f ld_dtDate BETWEEN @Mon3 AND DATEADD(d,2,@Mon3))) AS Total1,
(SELECT ISNULL(SUM(dbo.tblInvoiceL ineItems.f ld_iOrdere d), 0)
FROM dbo.tblInvoiceLineItems
WHERE fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.f ld_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,@Th u4)) as varchar) + '/' + CAST(DAY(DATEADD(d,2,@Thu4 )) as varchar) + '/' + CAST(YEAR(DATEADD(d,2,@Thu 4)) as varchar) AS DateRange,
(SELECT ISNULL(SUM(dbo.tblInvoiceL ineItems.f ld_iOrdere d), 0)
FROM dbo.tblInvoiceLineItems
WHERE fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.f ld_dtDate BETWEEN @Mon4 AND DATEADD(d,2,@Mon4))) AS Total1,
(SELECT ISNULL(SUM(dbo.tblInvoiceL ineItems.f ld_iOrdere d), 0)
FROM dbo.tblInvoiceLineItems
WHERE fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.f ld_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
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,@Th
(SELECT ISNULL(SUM(dbo.tblInvoiceL
FROM dbo.tblInvoiceLineItems
WHERE fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.f
(SELECT ISNULL(SUM(dbo.tblInvoiceL
FROM dbo.tblInvoiceLineItems
WHERE fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.f
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,@Th
(SELECT ISNULL(SUM(dbo.tblInvoiceL
FROM dbo.tblInvoiceLineItems
WHERE fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.f
(SELECT ISNULL(SUM(dbo.tblInvoiceL
FROM dbo.tblInvoiceLineItems
WHERE fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.f
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,@Th
(SELECT ISNULL(SUM(dbo.tblInvoiceL
FROM dbo.tblInvoiceLineItems
WHERE fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.f
(SELECT ISNULL(SUM(dbo.tblInvoiceL
FROM dbo.tblInvoiceLineItems
WHERE fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.f
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,@Th
(SELECT ISNULL(SUM(dbo.tblInvoiceL
FROM dbo.tblInvoiceLineItems
WHERE fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.f
(SELECT ISNULL(SUM(dbo.tblInvoiceL
FROM dbo.tblInvoiceLineItems
WHERE fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.f
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,@Th
(SELECT ISNULL(SUM(dbo.tblInvoiceL
FROM dbo.tblInvoiceLineItems
WHERE fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.f
(SELECT ISNULL(SUM(dbo.tblInvoiceL
FROM dbo.tblInvoiceLineItems
WHERE fld_txtCropID = @CropID AND (dbo.tblInvoiceLineItems.f
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
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?
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?
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the code - I'll give both a tryout in a couple hours and report back on speed etc.
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.
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.
BTW: You can easily add an ORDER BY Days.Mon to the query to make it sort by date.
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.
======
--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.
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.
ASKER
Kselvia: yours worked WITHOUT one tiny bit of a change. NO changes :)
Except see the little comment above.
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.fl d_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.fl d_dtDate BETWEEN @Thu AND DATEADD(d,2,@Thu))
and
dbo.tblInvoiceLineItems.fl d_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
You can see in my posting that I have
dbo.tblInvoiceLineItems.fl
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.fl
and
dbo.tblInvoiceLineItems.fl
which will grab up until midnight saturday morning, thus excluding all orders put in on saturday
unless they were put in right at midnight
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.
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,1 01) + ' - ' + convert(varchar, Dateadd(dd,5,Days.Mon),101 ) AS DateRange,
sum(case when dbo.tblInvoiceLineItems.fl d_dtDate between Days.Mon and Dateadd(dd,2,Days.Mon) Then dbo.tblInvoiceLineItems.fl d_iOrdered Else 0 End) Total1,
sum(case when dbo.tblInvoiceLineItems.fl d_dtDate between Dateadd(dd,2,Days.Mon) and Dateadd(dd,5,Days.Mon) Then dbo.tblInvoiceLineItems.fl d_iOrdered Else 0 End) Total2
FROM dbo.tblInvoiceLineItems
LEFT OUTER JOIN
(
select dateadd(dd,(datepart(dw,@D ate) - 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),dat eadd(dd,(d atepart(dw ,@Date) - 2) * -1,@Date))) x
) Days ON dbo.tblInvoiceLineItems.fl d_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.
SELECT
convert(varchar,Days.Mon,1
sum(case when dbo.tblInvoiceLineItems.fl
sum(case when dbo.tblInvoiceLineItems.fl
FROM dbo.tblInvoiceLineItems
LEFT OUTER JOIN
(
select dateadd(dd,(datepart(dw,@D
(select 0 id union select 7 union select 14 union select 21 union
select datediff(dd,Dateadd(yy,-1,
) Days ON dbo.tblInvoiceLineItems.fl
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.
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.
np
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,1 01) + ' - ' + convert(varchar, Dateadd(dd,5,Days.Mon),101 ) AS DateRange,
sum(case when dbo.tblInvoiceLineItems.fl d_dtDate between Days.Mon and Dateadd(dd,2,Days.Mon) Then dbo.tblInvoiceLineItems.fl d_iOrdered Else 0 End) Total1,
sum(case when dbo.tblInvoiceLineItems.fl d_dtDate between Dateadd(dd,2,Days.Mon) and Dateadd(dd,5,Days.Mon) Then dbo.tblInvoiceLineItems.fl d_iOrdered Else 0 End) Total2
FROM
(
select dateadd(dd,(datepart(dw,@D ate) - 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),dat eadd(dd,(d atepart(dw ,@Date) - 2) * -1,@Date))) x
) Days
LEFT OUTER JOIN dbo.tblInvoiceLineItems
ON dbo.tblInvoiceLineItems.fl d_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
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,1
sum(case when dbo.tblInvoiceLineItems.fl
sum(case when dbo.tblInvoiceLineItems.fl
FROM
(
select dateadd(dd,(datepart(dw,@D
(select 0 id union select 7 union select 14 union select 21 union
select datediff(dd,Dateadd(yy,-1,
) Days
LEFT OUTER JOIN dbo.tblInvoiceLineItems
ON dbo.tblInvoiceLineItems.fl
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
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.fl d_dtDate BETWEEN Days.Mon AND Dateadd(dd, 2, Days.Mon)
THEN dbo.tblInvoiceLineItems.fl d_iOrdered ELSE 0 END) Total1, SUM(CASE WHEN dbo.tblInvoiceLineItems.fl d_dtDate BETWEEN Dateadd(dd, 2,
Days.Mon) AND Dateadd(dd, 5, Days.Mon) THEN dbo.tblInvoiceLineItems.fl d_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...
SELECT CONVERT(varchar, Days.Mon, 101) + ' - ' + CONVERT(varchar, Dateadd(dd, 5, Days.Mon), 101) AS DateRange,
SUM(CASE WHEN dbo.tblInvoiceLineItems.fl
THEN dbo.tblInvoiceLineItems.fl
Days.Mon) AND Dateadd(dd, 5, Days.Mon) THEN dbo.tblInvoiceLineItems.fl
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,1 01) + ' - ' + convert(varchar, Dateadd(dd,5,Days.Mon),101 ) AS DateRange,
sum(case when dbo.tblInvoiceLineItems.fl d_dtDate between Days.Mon and Dateadd(dd,2,Days.Mon) Then dbo.tblInvoiceLineItems.fl d_iOrdered Else 0 End) Total1,
sum(case when dbo.tblInvoiceLineItems.fl d_dtDate between Dateadd(dd,2,Days.Mon) and Dateadd(dd,5,Days.Mon) Then dbo.tblInvoiceLineItems.fl d_iOrdered Else 0 End) Total2
FROM
(
select dateadd(dd,(datepart(dw,@D ate) - 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,(da tepart(dw, DateAdd(yy ,-1,@Date) ) - 2) * -1,
DateAdd(yy,-1,@Date)),date add(dd,(da tepart(dw, @Date) - 2) * -1,@Date))
) x
) Days
LEFT OUTER JOIN dbo.tblInvoiceLineItems
ON dbo.tblInvoiceLineItems.fl d_dtDate BETWEEN Days.Mon AND Dateadd(dd,5,Days.Mon) AND fld_txtCropID=@CropID
GROUP BY Days.Mon
ORDER BY Days.Mon
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,1
sum(case when dbo.tblInvoiceLineItems.fl
sum(case when dbo.tblInvoiceLineItems.fl
FROM
(
select dateadd(dd,(datepart(dw,@D
(select 0 id union select 7 union select 14 union select 21 union
select Datediff(dd,dateadd(dd,(da
DateAdd(yy,-1,@Date)),date
) x
) Days
LEFT OUTER JOIN dbo.tblInvoiceLineItems
ON dbo.tblInvoiceLineItems.fl
GROUP BY Days.Mon
ORDER BY Days.Mon
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.fl d_dtDate BETWEEN Days.Mon AND Dateadd(dd, 2, Days.Mon)
THEN dbo.tblInvoiceLineItems.fl d_iOrdered ELSE 0 END) Total1, SUM(CASE WHEN dbo.tblInvoiceLineItems.fl d_dtDate BETWEEN Dateadd(dd, 2,
Days.Mon) AND Dateadd(dd, 5, Days.Mon) THEN dbo.tblInvoiceLineItems.fl d_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.fl d_dtDate BETWEEN Days.Mon AND Dateadd(dd, 5, Days.Mon) AND
fld_txtCropID = @CropID
GROUP BY Days.Mon
ORDER BY Days.Mon DESC
GO
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.fl
THEN dbo.tblInvoiceLineItems.fl
Days.Mon) AND Dateadd(dd, 5, Days.Mon) THEN dbo.tblInvoiceLineItems.fl
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.fl
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.
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!
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!
ASKER