using datediff against datapart

Question

using datediff against datapart

born2achieve

Hall of Fame

Points: 3182
More actions
January 2, 2014 at 2:51 pm

#284089

Hi,
Am using sqlserver 2008 R2
Below is my current Query
select sum(Amount) from Coupon_Amount where IdCoupon in(select IdCoupon from All_Offers where getdate() between OfferStartDate and OfferEndDate
and IsActive = 1 and DATEPART(MONTH,GETDATE()) = DATEPART(MONTH,OfferEndDate) or
(DATEPART(MONTH,GETDATE()) < DATEPART(MONTH,OfferEndDate) and
DATEPART(MONTH,GETDATE()) >= DATEPART(MONTH,OfferStartDate))
and (DATEPART(YEAR,GETDATE()) = DATEPART(YEAR,OfferEndDate) or
(DATEPART(YEAR,GETDATE()) = DATEPART(YEAR,OfferEndDate) and
DATEPART(YEAR,GETDATE()) >= DATEPART(YEAR,OfferStartDate)))
) group by IsActive having IsActive = 1
This query works perfectly. Can this query be written using Datediff instead of DatePart? If yes please rewrite on my query

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

I'm not sure that this will work fine because you didn't provide sample data.

The logic of your query is not clear as the first between seems enough. If you need to compare full months you could certainly edit it.

SELECT sum(Amount)

FROM Coupon_Amount

WHERE IdCoupon IN (

SELECT IdCoupon

FROM All_Offers

WHERE getdate() BETWEEN DATEADD( MONTH, DATEDIFF( MONTH, 0, OfferStartDate),0)

AND DATEADD( MONTH, DATEDIFF( MONTH, 0, OfferEndDate), 0)

AND IsActive = 1

)

AND IsActive = 1

For a better answer, read the article linked in my signature.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

born2achieve Hall of Fame Points: 3182 More actions · Answer 2

Hi luis,

Thanks for your response and here is the sample data.

with All_Offers as (

select 101 as IdCoupon,'2014-01-14 13:30:00.000' as OfferStartDate,'2014-02-14 13:30:00.000' as OfferEndDate union all

select 102 as IdCoupon,'2013-12-19 13:30:00.000' as OfferStartDate,'2014-01-12 13:30:00.000' as OfferEndDate union all

select 103 as IdCoupon,'2013-12-14 13:30:00.000' as OfferStartDate,'2014-01-20 13:30:00.000' as OfferEndDate union all

select 104 as IdCoupon,'2014-01-01 13:30:00.000' as OfferStartDate,'2014-01-01 13:30:00.000' as OfferEndDate union all

select 105 as IdCoupon,'2013-06-14 13:30:00.000' as OfferStartDate,'2013-06-14 13:30:00.000' as OfferEndDate )

with Coupon_Amount as (

select 101 as IdCoupon, 2.00 as Amount union all

select 102 as IdCoupon, 20.00 as Amount union all

select 103 as IdCoupon, 100.00 as Amount union all

select 104 as IdCoupon, 29.00 as Amount union all

select 105 as IdCoupon, 40.00 as Amount)

if you run my query the expected output will be 120 (second and third row data)

Logic will be as follows,

I want to get the coupon based on the corresponding month dates and i should only consider the valid offer( not expired offer and not futured)

the very first record in offer table talks about future offer which doesn't falls in future date. so need not to consider

the second and third row data offers valid till 12th and 20th corresponding so

the fourth row data offer ends yesterday which means expired. so need not to consider

the fifth row data offer was old and expired. so so need not to consider

Hope the requirement is clear now. Also i have executed your query and it didn't return the expeted results as my query does. i know without giving proper test data it is hard to provide solution. Hope now you can provide alternate for my query

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Based on your sample data and explanation, it gets even simpler as you don't need any calculations.