| Author |
Topic |
|
masterdineen
Aged Yak Warrior
550 Posts |
 Posted - 2010-10-15 : 07:21:13
|
| Hello everyone.could i use this function to acheive the following.I am working with a database that has paying members. and after 60 days i am going to create a trigger to change there member status to In-Active on the day after 60 days. If that day falls on a sat or sun, Using the F_TABLE_DATE function can we make the change to happen on the friday before the sat?Kind RegardsRobMCTS / MCITP certified |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-15 : 07:35:40
|
Why not the monday after that sat?And how shall a trigger be fired because of a date?A trigger is only fired if the data in the table is changing...
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2010-10-15 : 07:46:41
|
| It will fire if getdate is more than 60 day from date within record.Yes it can be monday after, as long as that monday is not the start of the next month due to other processes.Is this acheivable with the F_Table_Date function do you think. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-15 : 07:52:56
|
Sorry but I don't understand.A trigger fires on an event (insert/update/delete).The event that the time is progressing is nothing that the trigger can detect.So if there is a row with more than 60 days from getdate() and nobody is going to change something on that row then the trigger isn't working/will not be fired.Hope that I am not bothering but it is not clear to me...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-10-15 : 09:04:34
|
I think the OP is confusing the "trigger" word...? What I think the OP should do is to create a sql agent job that has an update sort of like this (I hope the logic is correct, it's Friday afternoon so no guarantees!  ):UPDATE members SET Status = 'Inactive' WHERE JoinedDate <= CASE WHEN DATEPART(dw, getdate()) IN (7, 1) THEN GETDATE()-58 ELSE GETDATE()-60 END - LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-15 : 09:08:00
|
quote:
Originally posted by Lumbago
I think the OP is confusing the "trigger" word...? What I think the OP should do is to create a sql agent job that has an update sort of like this (I hope the logic is correct, it's Friday afternoon so no guarantees! ):UPDATE members SET Status = 'Inactive' WHERE JoinedDate <= CASE WHEN DATEPART(dw, getdate()) IN (7, 1) THEN GETDATE()-58 ELSE GETDATE()-60 END - LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com
Yes - that's what I thought too but I wanted to make it clear...I was in the mood...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2010-10-18 : 03:54:06
|
| Hello Guys thank you for your replys.Sorry if i wasnt clear enough.It would be on two conditions.1) to make a member inactive if payment date goes over 60days.2) if that day lands on a sat or sun to make it the friday before so it will land on a working day.Lumbagowould the code below take care of that using the F_Table_Date functionUPDATE members SET Status = 'Inactive' WHERE JoinedDate <= CASE WHEN DATEPART(dw, getdate()) IN (7, 1) THEN GETDATE()-58 ELSE GETDATE()-60 END |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-10-18 : 04:12:51
|
| No but I can't really see what you need it for.- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2010-10-18 : 05:45:17
|
| what is it you dont understand |
|
|
|
shaggy
Posting Yak Master
248 Posts |
Posted - 2010-10-18 : 11:49:38
|
| run this above query in a job |
|
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2010-10-19 : 08:16:32
|
| THe script above, does it reference the date function, is this something i could use on its own. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-19 : 08:25:53
|
There are many date functions like datepart(), datediff(), dateadd() and so on.What do want to do?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2010-10-19 : 08:57:41
|
| I am working with a database that has paying members. and after 60 days a member joined. I want to automate that member's membership to change to inactive. whatever column that may be.BUT if the day after 60 days lands on sat or sun, i want the membership to change to inactive on the friday before.does that make sense?Kind RegardsRob |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-19 : 09:07:15
|
Lumbago's code should do what you want.It is using GETDATE() so on each start it will calculate what you need from the current date.Just schedule it in a sql server agent job to run once every day and all should be fine.And be sure to test it in a dev environment 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2010-10-19 : 09:10:46
|
| Thank you very much |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-10-19 : 09:13:43
|
welcome
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
masterdineen
Aged Yak Warrior
550 Posts |
Posted - 2010-10-19 : 11:12:44
|
| To put a spanner in the works.what about if i want the day it lands on after the 60+ without being a weekend day OR the last working day of the month. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-10-19 : 12:07:59
|
| What you really need is a table of dates that shows if each date is a working, non-working date, last working date of month, etc. Then that you can query that to find what you want.Also, you need to put together your actual requirements before you start asking how to do something.CODO ERGO SUM |
|
|
|
|
F_TABLE_DATE function table question