DateTime calculation

I have two tables "publicholidays" and "RegDate". "publicholidays" simply
stores all the date of a public holidays in a particular year. RegDate
stores the date a user is being registered. I need to perform a calculation
that will calculate the number of days the user has been registered (exclude
publicholidays) and store the result in another field in RegDate table.
Put it simply:
TotalDays = TodayDate-DateRegistered-Number of PublicHolidays in between
Date registered and Todays Date
I need to implement this as store procedure. How do I perform such task ?
Thanks.SELECT DATEDIFF(d,DateRegistered, CURRENT_TIMESTAMP) - (SELECT COUNT *
FROM PublicHolidays
WHERE HolidayDate BETWEEN O.DataRegistered AND
CURRENT_TIMESTAMP) AS TotalDays
FROM YourTable O
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"quest" <anonymous@.microsoft.com> wrote in message
news:%23F2BLpzjFHA.1504@.TK2MSFTNGP10.phx.gbl...
>I have two tables "publicholidays" and "RegDate". "publicholidays" simply
> stores all the date of a public holidays in a particular year. RegDate
> stores the date a user is being registered. I need to perform a
> calculation
> that will calculate the number of days the user has been registered
> (exclude
> publicholidays) and store the result in another field in RegDate table.
> Put it simply:
> TotalDays = TodayDate-DateRegistered-Number of PublicHolidays in between
> Date registered and Todays Date
> I need to implement this as store procedure. How do I perform such task ?
> Thanks.
>|||Thanks. Is it possible to update a field in the same table ("O" in this
case) with the new calculated value ? I tried to do update but don't seem to
get the syntax right.
Thanks again.
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:uMHekw0jFHA.1416@.TK2MSFTNGP09.phx.gbl...
> SELECT DATEDIFF(d,DateRegistered, CURRENT_TIMESTAMP) - (SELECT COUNT *
> FROM PublicHolidays
> WHERE HolidayDate BETWEEN O.DataRegistered AND
> CURRENT_TIMESTAMP) AS TotalDays
> FROM YourTable O
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "quest" <anonymous@.microsoft.com> wrote in message
> news:%23F2BLpzjFHA.1504@.TK2MSFTNGP10.phx.gbl...
?
>|||Try this
UPDATE YourTable
SET TotalDays = SELECT DATEDIFF(d,DateRegistered, CURRENT_TIMESTAMP) -
(SELECT COUNT *
FROM PublicHolidays
WHERE HolidayDate BETWEEN YourTable .DataRegistered AND
CURRENT_TIMESTAMP)
WHERE TotalDays IS NULL
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"quest" <anonymous@.microsoft.com> wrote in message
news:OFdHHkAkFHA.1416@.TK2MSFTNGP09.phx.gbl...
> Thanks. Is it possible to update a field in the same table ("O" in this
> case) with the new calculated value ? I tried to do update but don't seem
> to
> get the syntax right.
> Thanks again.
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:uMHekw0jFHA.1416@.TK2MSFTNGP09.phx.gbl...
> ?
>|||hi
just try this
UPDATE RegDate
SEt Regdate.Column = DATEDIFF(d,DateRegistered, CURRENT_TIMESTAMP) -
count(HolidayDate)
FROM RegDate
INNER JOIN PublicHolidays PH ON
HolidayDate BETWEEN O.DataRegistered AND CURRENT_TIMESTAMP
best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***