Another Date Question - 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)

Another Date Question

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-11-04 : 09:19:38

Hi Guys

I’m using the follwoing query to gain some figures. The thing is I wanna do is add two parameters (@month and @year) that will let me determine what month and year to extract the figures from. The thing is, I am not sure how to structure them on the WHERE clause!

Any Ideas???

CREATE PROCEDURE uspHRDiscountCodeUsage
AS

SELECT
cus.CustomerId
,cus.FirstName
,cus.LastName
,cus.Email
,ISNULL(SUM(PriceIncTax),0) AS 'Total Spent'
,ISNULL(SUM(DiscountTotal),0) AS 'Discount Total'
,dc.DiscountCode

FROM dbo.Customer AS cus WITH (NOLOCK)
LEFT JOIN dbo.Receipt AS rec WITH (NOLOCK)
ON cus.CustomerId = rec.CustomerId
LEFT JOIN dbo.ReceiptItem AS reci WITH (NOLOCK)
ON rec.ReceiptId = reci.ReceiptId
LEFT JOIN dbo.ReceiptDiscount AS recd WITH (NOLOCK)
ON rec.ReceiptId = recd.ReceiptId
LEFT JOIN dbo.DiscountCode AS dc WITH (NOLOCK)
ON recd.DiscountCodeId = dc.DiscountCodeId

WHERE DiscountCode like ('%ataff%')
AND DATEDIFF(MONTH, reci.DateEntered, GETDATE()) = 0
AND DATEDIFF(YEAR, reci.DateEntered, GETDATE()) = 0

GROUP BY
cus.CustomerId
,cus.FirstName
,cus.LastName
,cus.Email
,dc.DiscountCode

HAVING SUM(PriceIncTax) > 500.00

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-11-04 : 09:25:01

WHERE CAST(@YEAR + '-' + @MONTH + '-01' AS DATETIME) = DATEADD(month, DATEDIFF(month, 0, reci.DateEntered), 0)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-11-04 : 09:26:47

DECLARE @date datetime

SET @date = getdate()

WHERE reci.DateEntered >= DATEADD(month,DATEDIFF(month,0,@date),0)
and
reci.DateEntered < DATEADD(month,DATEDIFF(month,-1,@date),0)

Jim

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-11-04 : 10:17:50

Hi Guys

The query I am using displays data for the current month through using
AND DATEDIFF(MONTH, reci.DateEntered, GETDATE()) = 0
AND DATEDIFF(YEAR, reci.DateEntered, GETDATE()) = 0

This shows 6 records which is correct, however if I try using the two parameters, no records are displayed.

DOes anyone know what the problem could be?

Thanks

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-11-04 : 10:32:44

and how do you use the two parameters?

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-11-04 : 10:38:37

I'm using integer parameters.

I found using the folloing seems to give me the correct result:
AND DATEPART(m,reci.DateEntered) = @month
AND DATEPART(yy,reci.DateEntered) = @year

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-04 : 11:26:11

AND reci.DateEntered>= DATEADD(mm,@month-1,DATEADD(yy,DATEDIFF(yy,0,CAST(@year AS varchar(4))),0))
AND reci.DateEntered<DATEADD(mm,@month,DATEADD(yy,DATEDIFF(yy,0,CAST(@year AS varchar(4))),0))

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-05 : 01:58:16

quote:
Originally posted by visakh16

AND reci.DateEntered>= DATEADD(mm,@month-1,DATEADD(yy,DATEDIFF(yy,0,CAST(@year AS varchar(4))),0))
AND reci.DateEntered<DATEADD(mm,@month,DATEADD(yy,DATEDIFF(yy,0,CAST(@year AS varchar(4))),0))

It can be simplified to

AND reci.DateEntered>= dateadd(month,@month-1,dateadd(year,@year-1900,0))
AND reci.DateEntered<dateadd(month,@month,dateadd(year,@year-1900,0))

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 02:33:52

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

AND reci.DateEntered>= DATEADD(mm,@month-1,DATEADD(yy,DATEDIFF(yy,0,CAST(@year AS varchar(4))),0))
AND reci.DateEntered<DATEADD(mm,@month,DATEADD(yy,DATEDIFF(yy,0,CAST(@year AS varchar(4))),0))

It can be simplified to

AND reci.DateEntered>= dateadd(month,@month-1,dateadd(year,@year-1900,0))
AND reci.DateEntered<dateadd(month,@month,dateadd(year,@year-1900,0))

Madhivanan

Failing to plan is Planning to fail

Ok. Thanks :)

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-11-05 : 03:44:41

Hi

Thanks guys for that!

Can someone explain the syntax because I'm not totally sure what is going on?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 03:48:23

its just finding the start date of month and year passed using the below
dateadd(month,@month-1,dateadd(year,@year-1900,0))

so we are looking at records which were created from first day of month till next months start which is obtained by this

dateadd(month,@month,dateadd(year,@year-1900,0))

which gives you all records for that month and year value

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-11-05 : 04:02:04

Thanks Buddy!!!

Helped me out alot there!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-05 : 04:09:58

quote:
Originally posted by rcr69er

Thanks Buddy!!!

Helped me out alot there!

You are welcome

Madhivanan

Failing to plan is Planning to fail

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-11-05 : 04:19:40

Just a quickie, if I use the following query, how come the results are NULL:

DECLARE @month int
DECLARE @year int

SET @month = 11
SET @month = 2008

SELECT
dateadd(month,@month-1,dateadd(year,@year-1900,0))
,dateadd(month,@month,dateadd(year,@year-1900,0))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 04:24:06

quote:
Originally posted by rcr69er

Just a quickie, if I use the following query, how come the results are NULL:

DECLARE @month int
DECLARE @year int

SET @month = 11
SET @year~~@month~~ = 2008

SELECT
dateadd(month,@month-1,dateadd(year,@year-1900,0))
,dateadd(month,@month,dateadd(year,@year-1900,0))

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-11-05 : 04:53:55

Oops my bad.

Thanks mate!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources