DateTime calculation

23 Jul 2005 3:56 AM

quest

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.

23 Jul 2005 6:04 AM

Roji. P. Thomas

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

Show quote

"quest" <anonym***@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.
>
>

24 Jul 2005 4:36 AM

quest

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.

Show quote

"Roji. P. Thomas" <thomasr***@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" <anonym***@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.
> >
> >
>
>

24 Jul 2005 5:50 AM

Roji. P. Thomas

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

Show quote

"quest" <anonym***@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" <thomasr***@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" <anonym***@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.
>> >
>> >
>>
>>
>
>

24 Jul 2005 10:04 AM

Chandra

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.developersdex.com ***