| Author |
Topic |
|
lug668
Starting Member
8 Posts |
 Posted - 2004-09-17 : 02:59:56
|
| Hi, I'm currently developing a web form for our employment department. My client asked me to do a conditional update to the database. Since I don't have any experience in Stored Procedures can anyone help please? Retrieve PlcmtStatus, Claimid4to12month, APMOutcomeEffectDate from database 'escpps'if PlcmtStatus = 'O', then If Claimid4to12month is not null OR (today's date - APMOutcomeEffectDate) > 34 weeks Then PlcmtStatus = 'C' Thank you,George |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-09-17 : 04:02:35
|
something like:select PlcmtStatus, Claimid4to12month, APMOutcomeEffectDate from escpps -- now this depends on what it is, a database or a table. -- if it's a table do "from escpps" else "from escpps..TableName"where (Claimid4to12month is not null) OR (datediff(ww, GetDate(), APMOutcomeEffectDate) > 34)now i'm not sure how your is statements are... you didn't quite explain ok.but if ... then ... else...end construct in sql looks like:case...when ... when ... else ... endi think that should put you on the right track :)also get familiar with BOL = Books on-line = SQL help. comes with the instalation of sql serverGo with the flow & have fun! Else fight the flow  |
 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-09-17 : 04:12:24
|
I'm impressed spirit1 - You made sense? out of that spec *lol*  It's not always easy to know what is table or a database...lug668, if you posted the structure of the tables it would be much easier to help You.Since the conditional update is afunction of "today's date" you would need to do this update on a regular basis right ?rockmoose/* Chaos is the nature of things...Order is a lesser state of chaos */ |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-17 : 04:48:31
|
| i was tempted to post a solution but didn't quite got the condition:If Claimid4to12month is not null OR (today's date - APMOutcomeEffectDate) > 34 weeksThen PlcmtStatus = 'C'my assumption is that this will provide 'C' in case the condition is true else provide the plcmtstatus.Spirit's esp must be on... |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-09-17 : 05:05:12
|
LOL yeah people tell me i have good ESP. and that comes handy...  Go with the flow & have fun! Else fight the flow |
|
|
|
lug668
Starting Member
8 Posts |
Posted - 2004-09-19 : 19:50:58
|
| Sorry everyone. My original statement wasn't very clear...I hope this will be much clear...Data source: escppsTable: tblppsFields:SiteCd OutcomeType ManagedBy JskrId JskrSurname JskrFirstName HDFlag UeDurn JobCrsId APMOutcomeEffectDate ClaimId4-12Month FinalClaimId PlcmtStatus (JskrId, JobCrsId and APMOutcomeEffectDate are the primary keys)If the condition is true (Claimid4to12month is not null OR (today's date - APMOutcomeEffectDate) > 34 weeks) then any PlcmtStatus with 'O' value will be replaced with 'C'. If it's not true then do nothing. This will be scheduled monthly.Please let me know this is still not clear. BTW, what I really need to know is how to write this in store procedure format. Something like 'CREATE PROCEDURE spUpdateStatus ...'.Thanks everyone for helping me. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-09-20 : 03:41:47
|
it would be good if u look up Stored procedures in BOL = Books Online = Sql Helpsearch under Create Procedure.but to get you on the right track:CREATE PROCEDURE spUpdateStatusASUpdate escpps..tblppsset PlcmtStatus = 'C'Where (PlcmtStatus = 'O') and ((Claimid4to12month is not null) OR (datediff(ww, GetDate(), APMOutcomeEffectDate) > 34))GO Go with the flow & have fun! Else fight the flow |
|
|
|
lug668
Starting Member
8 Posts |
Posted - 2004-09-20 : 22:32:19
|
| Thank you so much spirit1. I've run it and it works! One more question - how can I save it as store procedure for this database and how can I schedule it? Yeah, I will look at the BOL but this one is quite urgent for me.Thanks again! |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-20 : 23:20:11
|
| hey spirit, the guy's waiting... when you run the script spirit graciously provided for you on the target database this will create the procedure because you've issued the command "create procedure spupdatestatus".to schedule it, you need to create a job that will call this procedure for you as specified. I suggest you look it up in BOL, no matter how urgent...have fun learning... |
|
|
|
lug668
Starting Member
8 Posts |
Posted - 2004-09-21 : 00:50:35
|
| Thank you jen. I've found the procedure I've run under Query Analyzer. I'll try to figure out how to do schedule...Just curious - do I need DTS to run schedule? |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-21 : 02:38:34
|
| no, if we're still talking about your topic. a dts is actually a job, if you design a package and schedule it, a job will be automatically created for you...what you need is to create a job, add a step in it (calling the sproc you created) and schedule or have it invoked by an alert. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-09-21 : 03:58:17
|
quote:
Originally posted by jen
hey spirit, the guy's waiting...
yeah... but while he was waiting i was doing a very importatnt job...my beauty sleep :))let's see 22:30 new york (i assume) time + 7 hours (my time) is 5:30 in the morning .... yup i was asleep... hey jen where are you from?Go with the flow & have fun! Else fight the flow |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-21 : 04:07:05
|
curious if i sleep at all? 4 hours a day or none at all...have this nasty insomnia... so i use the opportunity to learn or answer some posts, this is my first "active" forum,sqlteam.com really rocks... |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-09-21 : 04:21:21
|
| yup i know what you mean :) i got addicted too on this one....insomnia?? uau. so where do you get the energy???Go with the flow & have fun! Else fight the flow |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-21 : 04:25:30
|
| lolz, aren't we going to be kicked out for chatting here?3 things... coffee, coffee and coffeeodd thing is if i don't sleep for 1 or 2 days, and get a nap for an hour or so, i get refreshed. if i do sleep for more than 6 hours, i get a splitting headache... |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-09-21 : 04:34:14
|
| well there is always Y! messenger... we can chat there if u want...personally i prefer sleep over coffee Go with the flow & have fun! Else fight the flow |
|
|
|
lug668
Starting Member
8 Posts |
Posted - 2004-09-22 : 01:25:04
|
quote:
Originally posted by jen
no, if we're still talking about your topic. a dts is actually a job, if you design a package and schedule it, a job will be automatically created for you...what you need is to create a job, add a step in it (calling the sproc you created) and schedule or have it invoked by an alert.
Hey Hey guys! This is still my topic?  BTW, I've created a job in SQL Enterprise Mgr under SQL Server Agent and added a step. But I can't just put 'Create procedure ...' in the Command box since it'll create a procedure each time it run. I guess I should put just the SQL code (Update tblpps ...) into the command box right |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-22 : 01:55:37
|
| nope...i thought what you need is a way to schedule the stored procedures?so in your step:choose tsql, choose the database, then type...exec spnamethen add a schedule for this...and yes, this is still your topic |
|
|
|
lug668
Starting Member
8 Posts |
Posted - 2004-09-22 : 02:41:16
|
| Thanks for your great help jen. You are the superstar for me! |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-22 : 02:49:00
|
| what i know i learned from here, stick around, these guys are great... |
|
|
|
|
How can I do this in store procedure?