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

General SQL Server Forums

New to SQL Server Programming

DATE DIFFERENCE

Author

Topic

craigsql
Starting Member

6 Posts

Posted - 2009-07-07 : 19:54:06

I am trying to write an SQL to find the number of DAYS between a CheckOutDate & ReturnDate. I would also like to multiply the answers that greater than 7 days by $0.75.

This is what I have:

DECLARE @ReturnDate DATETIME
DECLARE @CheckOutDate DATETIME

SET @ReturnDate = @ReturnDate
SET @CheckoutDate = @CheckOutDate

SELECT datediff(Day, @ReturnDate, @CheckOutDate)

AS

[DATEDIFFERENCE]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-08 : 01:41:05

So what is your problem exactly?

This:
SET @ReturnDate = @ReturnDate
SET @CheckoutDate = @CheckOutDate
makes no sense in the given example.

Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-08 : 01:46:09

which date is later ? ReturnDate or CheckOutDate ?


select [DATEDIFFERENCE] * case when [DATEDIFFERENCE] > 7 then 0.75 else 1.00 end
from
(
    SELECT datediff(Day, @CheckOutDate, @ReturnDate) as [DATEDIFFERENCE]
) d

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-08 : 01:54:33

looking at your other thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=128914

you probably need a query something like


select CardID, sum(Fines)
from
(
select c.CardID, o.MovieID, o.CheckOutDate, o.ReturnDate,
       Amount = p.Amount,
       Fines  = case when datediff(day, o.CheckOutDate, o.ReturnDate) > 7 
                     then (datediff(day, o.CheckOutDate, o.ReturnDate) - 7) * 0.75
                     else 0
                     end
from   Customer c
       inner join CheckOut o on c.CardID = o.CardID
       inner join Payment p  on o.MovieID = p.MovieID
where  <some other conditions>
) f
group by CardID

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-08 : 01:59:12

quote:

Originally posted by khtan

looking at your other thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=128914

you probably need a query something like


select c.CardID, o.MovieID, o.CheckOutDate, o.ReturnDate,
       Amount = p.Amount 
              + case when datediff(day, o.CheckOutDate, o.ReturnDate) >= 7 
                     then datediff(day, o.CheckOutDate, o.ReturnDate) * 0.75
                     else 0
                     end
from   Customer c
       inner join CheckOut o on c.CardID = o.CardID
       inner join Payment p  on o.MovieID = p.MovieID
where  <some other conditions>

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

No, you're never too old to Yak'n'Roll if you're too young to die.

craigsql
Starting Member

6 Posts

Posted - 2009-07-08 : 02:00:55

The CheckOutDate would be the first, and the ReturnDate will be the last.

For example, CheckOutDate = 03/09/2009, and ReturnDate = 04/11/2009

The difference in days is 33.
7 of those days are the allowed 'normal' checkout.
Thus, 33-7 = 26 days overdue.
This means that the customer will be charged for 26 days overdue.
So, $0.75 per day is equivalent to (26 * 0.75) = 19.50

I would want the sum of ALL fines that the customer accumulates.
So, 19.50+other fines = Total FINES

I hope this is clear, and appreciate the help!!

craigsql
Starting Member

6 Posts

Posted - 2009-07-08 : 02:13:22

Thank you both Fred & KHTAN. Both scholars indeed!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources