Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Working with a Very Poorly Designed Database Expand / Collapse
Author
Message
Posted Friday, October 25, 2013 3:47 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:32 PM
Points: 977, Visits: 3,357
Hello everyone, and Happy Friday
I am tasked with trying to update a table. I have told them that the table is not at all designed correctly, but no one seems to listen. A Java front-end developer designed this database, if you can believe that. Now they want me to perform some miracle when it comes to getting the data out.

The query at the bottom pretty much tells the logic story. I need to update a Category Column depending on the value in the score column.



CREATE TABLE MemberScore
(
Score float
, Category1 bit
, Category2 bit
, Category3 bit
, Category4 bit
, Category5 bit
)

INSERT INTO MemberScore
SELECT 0.1,0,0,0,0,0 UNION ALL
SELECT 0.2,0,0,0,0,0 UNION ALL
SELECT 0.4,0,0,0,0,0 UNION ALL
SELECT 0.55,0,0,0,0,0 UNION ALL
SELECT 0.65,0,0,0,0,0 UNION ALL
SELECT 0.8,0,0,0,0,0 UNION ALL
SELECT 0.97,0,0,0,0,0 UNION ALL
SELECT 0.99,0,0,0,0,0 UNION ALL
SELECT 0.12,0,0,0,0,0 UNION ALL
SELECT 0.23,0,0,0,0,0 UNION ALL
SELECT 0.34,0,0,0,0,0 UNION ALL
SELECT 0.56,0,0,0,0,0 UNION ALL
SELECT 0.54,0,0,0,0,0 UNION ALL
SELECT 0.98,0,0,0,0,0 UNION ALL
SELECT 0.95,0,0,0,0,0 UNION ALL
SELECT 0.91,0,0,0,0,0 UNION ALL
SELECT 0.29,0,0,0,0,0 UNION ALL
SELECT 0.36,0,0,0,0,0 UNION ALL
SELECT 0.88,0,0,0,0,0 UNION ALL
SELECT 0.68,0,0,0,0,0 UNION ALL
SELECT 0.88,0,0,0,0,0 UNION ALL
SELECT 0.77,0,0,0,0,0 UNION ALL
SELECT 0.75,0,0,0,0,0 UNION ALL
SELECT 0.48,0,0,0,0,0 UNION ALL
SELECT 0.5,0,0,0,0,0 UNION ALL
SELECT 0.69,0,0,0,0,0 UNION ALL
SELECT 0.66,0,0,0,0,0 UNION ALL
SELECT 0.37,0,0,0,0,0 UNION ALL
SELECT 0.42,0,0,0,0,0 UNION ALL
SELECT 0.4,0,0,0,0,0 UNION ALL
SELECT 0.27,0,0,0,0,0 UNION ALL
SELECT 0.33,0,0,0,0,0 UNION ALL
SELECT 0.57,0,0,0,0,0

SELECT * FROM MemberScore

-- Logic for the query
UPDATE MemberScore
SELECT Score FROM MemberScore(
CASE WHEN ( Score > 0.0 AND Score < 0.4 ) THEN (UPDATE MemberScore SET Category1 = 1)
CASE WHEN ( Score > 0.4 AND Score < 0.55 ) THEN (UPDATE MemberScore SET Category2 = 1)
CASE WHEN ( Score > 0.55 AND Score < 0.66 ) THEN (UPDATE MemberScore SET Category3 = 1)
CASE WHEN ( Score > 0.66 AND Score < 0.85 ) THEN (UPDATE MemberScore SET Category4 = 1)
CASE WHEN ( Score > 0.85) THEN (UPDATE MemberScore SET Category5 = 1)
END)

Please try not to laugh too hard at this awful mess of a table design and query. I would greatly appreciate any and all assistance.

Thank you in advance for all your assistance, comments and suggestions.

Andrew SQLDBA
Post #1508617
Posted Friday, October 25, 2013 4:10 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 3,630, Visits: 8,138
It's not that difficult, you can do it this way or with 5 UPDATEs, one for each condition.
UPDATE m SET
Category1 = CASE WHEN ( Score > 0.0 AND Score < 0.4 ) THEN 1 ELSE Category1 END,
Category2 = CASE WHEN ( Score > 0.4 AND Score < 0.55 ) THEN 1 ELSE Category2 END,
Category3 = CASE WHEN ( Score > 0.55 AND Score < 0.66 ) THEN 1 ELSE Category3 END,
Category4 = CASE WHEN ( Score > 0.66 AND Score < 0.85 ) THEN 1 ELSE Category4 END,
Category5 = CASE WHEN ( Score > 0.85) THEN 1 ELSE Category5 END
FROM #MemberScore m

You're missing equal signs (=) somewhere in your conditions but I'm uncertain of which ones, maybe the on the bottom limits.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1508619
Posted Friday, October 25, 2013 5:21 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:24 AM
Points: 811, Visits: 5,157
I had a disaster like this once, but it was in [ick] Access. After I restructured and showed them how simple it was to query, they were sold.
Post #1508627
Posted Friday, October 25, 2013 5:31 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:32 PM
Points: 977, Visits: 3,357
Thanks Luis, that is exactly. I was close, but just could not get it correct.

Have a nice weekend

Andrew SQLDBA
Post #1508628
Posted Friday, October 25, 2013 5:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, August 16, 2014 8:16 AM
Points: 107, Visits: 513
Just a thought - category1 thru category5 could just be computed fields. You already have all the information you need in Score.
Post #1508629
Posted Friday, October 25, 2013 6:07 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 3,630, Visits: 8,138
lnardozi 61862 (10/25/2013)
Just a thought - category1 thru category5 could just be computed fields. You already have all the information you need in Score.

I'm sure that Andrew can't change the table structure. If that would be possible, five category columns are not ideal. The best option would be to have a Categories table and a category column in the MemberScore table to reference the Categories table (a.k.a. a Foreign Key).

I'm glad I could help, have a good weekend you too.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1508631
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse