Using CASE stement to insert values into different columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using CASE stement to insert values into different columns

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-07-13 : 07:48:17

Don Donoghue writes "Im using sql on a application and an accounts table
Im trying to work out statstics of how long it takes apps to turn into accounts. My code works out the time by using the months between function and then I update a monthe field accordingly i.e month1 is an account that was set up in less than a month and the month2 column is an acount is set up in 1 to 2 months. You can see below what I do with a scipt but is there a way I can do this with a case statement

update mortgage_pipeline APP
set MONTH1 = 1
WHERE months_between (account_opened_date,application_date)<1;

commit;

update mortgage_pipeline APP
set MONTH2 = 1
WHERE months_between (account_opened_date,application_date)<2
AND months_between (account_opened_date,application_date)>=1;

commit;

update mortgage_pipeline APP
set MONTH3 = 1
WHERE months_between (account_opened_date,application_date)<3
AND months_between (account_opened_date,application_date)>=2;

commit;"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-07-13 : 08:35:44

yuk? how about redesign the database. I am sure there is a "App started" date somewhere. Probably a "Became Account" date field too. Now just calculate how long it was between the two dates, voila! There is your statistic, without needing 2 more columns.

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources