Date Parameter - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Date Parameter

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-10-18 : 11:40:50

Hi Guys

I’m having a bit of trouble with the followowing stroed procedure that I have created.

The procedure has two date parameters in which the @StartDate converts the date to date of the beginning of the week, and @EndDate is converted to the end date week for that date.

The code is as follows and at the end I have included script to run the stored procedure:

CREATE PROCEDURE dbo.uspExpectedReturnsSummary
(
@StartDate DATETIME
,@EndDate DATETIME)
AS
SET NOCOUNT ON

DECLARE @WeekNumber INT
SELECT @WeekNumber = DATEPART(WK,@StartDate) -
CASE WHEN DATEPART(WK,@StartDate) = 0 THEN 0 ELSE 1 END

/* Reassign values to beginng of week date for @StartDate and end of week date for @EndDate */
SET @StartDate = DATEADD(WK,DATEDIFF(WK,0,@StartDate),0)
SET @EndDate = DATEADD(WK,DATEDIFF(WK,0,@EndDate),6)

/*Main Query*/
SELECT
DATEPART(WK,vi.DateEntered) - @WeekNumber AS 'WeekNumber'
,DATEADD(WK,DATEDIFF(WK,0,vi.DateEntered),0) AS 'WeekStartDate'
,DATEADD(WK,DATEDIFF(WK,0,vi.DateEntered),6) AS 'WeekEndDate'
,CONVERT(VARCHAR(10),DATEADD(WK,DATEDIFF(WK,0,vi.DateEntered),0),103) + ' to ' + CONVERT(VARCHAR(10),DATEADD(WK,DATEDIFF(WK,0,vi.DateEntered),6),103) AS 'WeekRange'
,'Week ' + CONVERT(VARCHAR(50),DATEPART(wk,vi.DateEntered) - @WeekNumber) + ' (' + CONVERT(VARCHAR(10),DATEADD(WK,DATEDIFF(WK,0,vi.DateEntered),0),103) + ' to ' + CONVERT(VARCHAR(10),DATEADD(WK,DATEDIFF(WK,0,vi.DateEntered),6),103)+')' AS 'DateFormat'
,COUNT(DISTINCT ri.receiptid) AS 'NumberOfOrders'
,COUNT(DISTINCT vi.VoidItemid * vi.Quantity) AS 'Units'
,CAST(CAST(COUNT(DISTINCT ri.ReceiptID) AS DECIMAL(10,2))/
CAST((SELECT COUNT(DISTINCT ri.receiptid)
FROM [dbo].[voiditem] AS vi WITH (NOLOCK)
JOIN [dbo].[receiptitem] AS ri WITH (NOLOCK)
ON vi.receiptitemid = ri.receiptitemid
AND vi.dateentered BETWEEN @StartDate AND DATEADD(s,-1,(DATEADD(D,1,@EndDate)))) AS DECIMAL(10,2)) * 100 AS DECIMAL(10,2)) AS 'Percent'
,SUM(i.CurrentPrice) AS 'PriceOfUnits'
,SUM(i.CostPrice) AS 'CostOfUnits'

FROM [dbo].[voiditem] AS vi WITH (NOLOCK)
INNER JOIN [dbo].[receiptitem] AS ri WITH (NOLOCK)
ON vi.receiptitemid = ri.receiptitemid
AND vi.dateentered BETWEEN @StartDate AND DATEADD(s,-1,(DATEADD(D,1,@EndDate)))
AND vi.enteredbyreasonid IN (47,49,52,56)
INNER JOIN [dbo].[POSItem] AS p WITH (NOLOCK)
ON vi.VoidItemid = p.VoidItemid
AND p.VoidItemId IS NOT NULL
INNER JOIN [dbo].[voidreason] AS vr WITH (NOLOCK)
ON vi.enteredbyreasonid = vr.voidreasonid
INNER JOIN dbo.Inventory AS i WITH (NOLOCK)
ON ri.InventoryId = i.InventoryId

GROUP BY
DATEPART(WK,vi.DateEntered) - @WeekNumber
,DATEADD(WK,DATEDIFF(WK,0,vi.DateEntered),0)
,DATEADD(WK,DATEDIFF(WK,0,vi.DateEntered),6)

ORDER BY
DATEPART(WK,vi.DateEntered) - @WeekNumber

SET NOCOUNT OFF

GO

EXEC dbo.uspExpectedReturnsSummary
@StartDate = '2009-08-01'
,@EndDate = '2009-08-05'

So in theory the procedure would look for date values between the 27th July 09 to the 9th August 090 in the VoidItem table. But instead its bringing back data upto the 16th August 09. I know its something to do with the @EndDate varaible but cant figure out what. I have tried adding it to a WHERE clause instead of the JOIN but still having no luck.

Anyone have an ideas on whats going wrong?

Thanks

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-10-18 : 13:02:46

I've done a bit more further investigation and found that as soon as the vi.DateEntered date becomes greater than 2009-08-09 00:00:00 it is counted as the next week.

Is there any way to change this?

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources