using DATEDIFF instead DATEPART

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 2008 Forums

Transact-SQL (2008)

using DATEDIFF instead DATEPART

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-01-02 : 16:53:30

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

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-01-02 : 18:35:39

Here is the test data and requirement


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 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

i know without giving proper test data it is hard to provide solution.

Anyone can please provide me the alternate query for this.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-01-02 : 23:18:06

[code]SELECT SUM(Amount)
FROM All_Offers o
INNER JOIN Coupon_Amount c ON o.IdCoupon = c.IdCoupon
WHERE o.IsActive = 1
AND c.IsActive = 1
AND OfferStartDate <= GETDATE()
AND OfferEndDate >= GETDATE()
[/code]

KH
[spoiler]Time is always against us[/spoiler]

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-01-03 : 08:17:04

Hi Khtan,

Perfect. your query works great. thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-01-03 : 15:42:34

AND GETDATE() BETWEEN OfferStartDate AND OfferEndDate;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-01-03 : 18:17:29

i did add the condition. Thanks everyone for this help

Kristen
Test

22859 Posts

Posted - 2014-01-05 : 06:35:55

[code]
AND GETDATE() BETWEEN OfferStartDate AND OfferEndDate
[/code]
I'm always nervous about the edge-condition of the value (GetDate()) matching the terminal value (OfferEndDate).

More often it seems better to have

GetDate() < OfferEndDate + "Some small offset"

e.g.

GetDate() < Tomorrow

Rather than

GetDate() <= Just-before-midnight

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-01-06 : 15:52:54

I agree with you, but in this case the original business logic says <= and >=.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources