SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Time and date calculations
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JBRTaylor
Starting Member

United Kingdom
5 Posts

Posted - 06/16/2013 :  03:34:18  Show Profile  Reply with Quote
I have written a query in ms Access that i have been using for some time now and works great but the time has come to rewrite it for sql server which is where i have come unstuck. I am currently stuck with the time calculations but the whole sql code from access is as follows:

SELECT tblCollection.HireID, tblCollectionItemized.HireLine, tblCollection.CollectionDate, tblCollection.DueBack, tblTariff.Tariff, ([DueBack]-[CollectionDate])/7 AS WeeksUsed, ([DueBack]-[CollectionDate]) AS DaysUsed, Int(([DueBack]-[CollectionDate])/7) AS Weeks, ((([DueBack]-[CollectionDate]))-Int(([DueBack]-[CollectionDate])/7)*7) AS Days, tblKitSubCat.CategoryID, tblCollection.DiscountPc, [WeekRate]/[DaysPerWeek] AS DayRate, (IIf([Days]<[DaysPerWeek],[DayRate]*[Days],[WeekRate]*1)+([Weeks]*[WeekRate])) AS DayTariffSub, [WeeksUsed]*[WeekRate] AS StdTariffSub, (IIf([DayRateApply]=True,(([DiscountPc]/-100)*[DayTariffSub])+[DayTariffSub],(([DiscountPc]/-100)*[StdTariffSub])+[StdTariffSub])) AS EstAmountDue, Round([EstAmountDue],2) AS EstAmountDueRnd
FROM ((tblTariff INNER JOIN (tblCollection INNER JOIN tblCollectionItemized ON tblCollection.HireID = tblCollectionItemized.HireID) ON tblTariff.TariffKey = tblCollection.Tarriff) INNER JOIN tblRateCard ON tblTariff.TariffKey = tblRateCard.Tariff) INNER JOIN tblKitSubCat ON (tblCollectionItemized.KitKey = tblKitSubCat.CategoryID) AND (tblRateCard.KitKey = tblKitSubCat.CategoryID)
WHERE (((tblCollection.HireID)=1532));

I have been using the graphical query builder in sql management studio seeing as i am a beginner and want to be able to have two columns which calculate the weeks and days between two dates. so if the difference is 10 days col 1 will read 1 week, and col two will read 3 days, or if the difference is 15 then, col 1 2 weeks and col 2 1day.

Thanks in advance
Jon

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 06/16/2013 :  13:06:44  Show Profile  Reply with Quote
There may be a few things that you need to change, but regarding your specific question about calculating the number of days, instead of "Int(([DueBack]-[CollectionDate])/7)" use
DATEDIFF(day,[CollectionDate],[DueBack])/7
I did not use a cast there because the numerator and denominator are integers, so it will be an integer division (truncation of any fractional part).

There are some examples of DATEDIFF here: http://msdn.microsoft.com/en-us/library/ms189794.aspx

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/17/2013 :  01:12:15  Show Profile  Reply with Quote
similarly DATEDIFF(day,[CollectionDate],[DueBack])%7 will give you the residual days after whole weeks.

see

http://visakhm.blogspot.com/2012/07/generate-datetime-values-from-integers.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000