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