January 2, 2014 at 2:51 pm
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
January 2, 2014 at 3:09 pm
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.
January 2, 2014 at 4:31 pm
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
January 2, 2014 at 4:56 pm
Based on your sample data and explanation, it gets even simpler as you don't need any calculations.
SELECT sum(Amount)
FROM Coupon_Amount
WHERE IdCoupon IN (
SELECT IdCoupon
FROM All_Offers
WHERE getdate() between OfferStartDate and OfferEndDate
)
January 2, 2014 at 5:36 pm
It's Crazy. You are correct. No need of Datepart and datediff. Simple query is enough.
Thanks for helping out stopping me to think much. Appreciate your time on this post. Have wonderful year.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply