conditional grouping? - SQL Server Forums

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

Transact-SQL (2000)

conditional grouping?

Author

Topic

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-08-15 : 15:29:39

CREATE TABLE acodes
(acode int, acodedesc varchar(32))

CREATE TABLE bcodes
(bcode int, bcodedesc varchar(32))

CREATE TABLE mytable
(xxxdate datetime, person varchar(32), xxxvalue float, a_b_code int)

INSERT INTO acodes VALUES(10,'red')
INSERT INTO acodes VALUES(20,'blue')
INSERT INTO acodes VALUES(30,'green')
INSERT INTO acodes VALUES(40,'grey')
INSERT INTO acodes VALUES(50,'white')
INSERT INTO acodes VALUES(120,'orange')

INSERT INTO bcodes VALUES(10,'blueberry')
INSERT INTO bcodes VALUES(30,'black')
INSERT INTO bcodes VALUES(100,'rusty')
INSERT INTO bcodes VALUES(110,'pink')
INSERT INTO bcodes VALUES(120,'purple')
INSERT INTO bcodes VALUES(130,'yellow')

INSERT INTO mytable VALUES ('01/01/1975', 'jeff', 5, 10)
INSERT INTO mytable VALUES ('01/01/1975', 'jane', 5, 30)
INSERT INTO mytable VALUES ('08/12/2002', 'jane', 5, 30)
INSERT INTO mytable VALUES ('08/13/2002', 'jim', 5, 120)
INSERT INTO mytable VALUES ('08/14/2002', 'jim', 5, 120)
INSERT INTO mytable VALUES ('08/13/2002', 'janice', 5, 10)
INSERT INTO mytable VALUES ('08/13/2002', 'kelly', 5, 130)
INSERT INTO mytable VALUES ('08/14/2002', 'kelly', 5, 130)
INSERT INTO mytable VALUES ('08/15/2002', 'kelly', 5, 130)
INSERT INTO mytable VALUES ('08/19/2002', 'kelly', 5, 120)
INSERT INTO mytable VALUES ('08/17/2002', 'kelly', 5, 120)
INSERT INTO mytable VALUES ('01/01/1975', 'kelly', 5, 120)
INSERT INTO mytable VALUES ('01/01/1974', 'kelly', 5, 120)

Desired result is

person****sum_of_xxxvalue****a_or_b_code****a_or_b_code_description

jane-----5-------30---------------green
jane-----5-------30---------------black
janice---5-------10---------------blueberry
jeff-----5-------10---------------red
jim------10------120--------------purple
kelly----10------120--------------orange
kelly----10------120--------------purple
kelly----15------130--------------yellow

The gist of it is:
- when a row in mytable has a date value <= 01/01/1975
draw the description of the code from acodes and sum xxxvalue grouped on the person and acode.
- when a row in mytable has a date value > 01/01/1975 draw the description
of the code from bcodes and sum xxxvalue grouped on the person and bcode

This is what I came up with but something dosen't look feel right...

SELECT mytable.person, sum(mytable.xxxvalue), mytable.a_b_code,
CASE
WHEN mytable.xxxdate <= '01/01/1975' THEN acodes.acodedesc
ELSE bcodes.bcodedesc
END, MAX(mytable.xxxdate)
FROM mytable LEFT JOIN acodes ON mytable.a_b_code=acodes.acode LEFT JOIN bcodes ON mytable.a_b_code=bcodes.bcode
GROUP BY mytable.person, mytable.a_b_code,
CASE WHEN mytable.xxxdate <= '01/01/1975' THEN acodes.acodedesc
ELSE bcodes.bcodedesc
END
ORDER BY mytable.person asc, mytable.a_b_code asc

**DROP TABLE acodes,bcodes,mytable

is this the best way?

not convinced the dual left joins are acting proper cause in a way they are conditional as well, or the case in the group (never done it)

Voted best SQL forum nickname...."Tutorial-D"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-15 : 15:42:43

I think you want the date comparison in the ON clauses ...


SELECT
    mytable.person,
    sum(mytable.xxxvalue),
    mytable.a_b_code, 
    coalesce(acodes.acodedesc,bcodes.bcodedesc),
    MAX(mytable.xxxdate) 
FROM
    mytable
    LEFT JOIN acodes
        ON (mytable.a_b_code=acodes.acode and
            mytable.xxxdate <= '01/01/1975')
    LEFT JOIN bcodes
        ON (mytable.a_b_code=bcodes.bcode and
            mytable.xxxdate > '01/01/1975')
GROUP BY
    mytable.person,
    mytable.a_b_code, 
    coalesce(acodes.acodedesc,bcodes.bcodedesc)
ORDER BY
    mytable.person asc,
    mytable.a_b_code asc

Jay White
{0}

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-08-15 : 16:28:35

Thanks for the answer

As for me thinking, smoke out of ears..
that part will stop one day won't it Page47?

Do you talk to yourself when you work these out or does speech not work right?

Example: here's a quick look at how I'd explain your response to myself given the context

mytable takes its values <= '01/01/1975' and matches them on mytable.a_b_code=acodes.acode

any records > '01/01/1975' during this first join get a null
as far as the acodes contributions to the set are concerned

second join takes result set after the first join and when > '01/01/1975' it matches them on mytable.a_b_code=bcodes.bcode

the resultant set after both joins has each original mytable record accounted for by either a contribution from acodes or bcodes such that
if it was an acode contribution then the bcode contribution is null, if there was a bcode contribution then the acode contribution is null

We want our select to utilize only not null values and the final result set to present to us the contributions that are not null and not to make unnesscery groupings based on a null value.

The point being.. is it totally counterproductive to try and understand via language, or is it the only way, or somewhere in between.

Voted best SQL forum nickname...."Tutorial-D"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-15 : 17:29:29

quote:
The point being.. is it totally counterproductive to try and understand via language, or is it the only way, or somewhere in between.

It is the BEST way...

I usually mumble to myself as I'm figuring something out, sometimes hearing the words spoken helps to clarify the problem. A lot of times I see the tables in my mind, and the lines and arrows that you'd see in an Access query designer pop up too, so usually I can figure it out visually and not need to write it down or otherwise "state" what the idea is or how to do it.

Nevertheless, you won't get far WITHOUT understanding the problem, regardless of how that understanding comes to you. I used to figure things out as I went along, and after a number of years and a lot of re-doing stuff, lately I've been figuring everything out in my head before I even start. I can't really recommend that though, because it's a SLOOOOOOOOOOOWWWWWWWWWWW process and it's easy to lose track. On the other hand, it's a quick way to try an idea without putting a lot of effort into it, only to throw it out later. If you really understand your data, you can "see" when something WON'T work very quickly.

As you do more database stuff it'll become second nature and the smoke won't pour out anymore. If you want to get a good idea what SQL can do, pick up Ken Henderson's

The Guru's Guide To Transact-SQL

and read it cover-to-cover. It's a mind-blower in some ways, and in others it's like (sheet) music; you'd never believe that the solution could be so easy and elegant unless someone showed you. You can also pick up Joe Celko's books, like SQL For Smarties, they'll give you tons of ideas too.

Sitka
Aged Yak Warrior

571 Posts

Posted - 2002-08-15 : 21:26:50

right on robvolk, that's the kind of feedback we need.

Actually the original problem was one I was working out for some legacy Access stuff, but I couldn't post that stuff because it didn't cleanly represent the idea of what I was trying to do (too noisy). So I set up this little demo problem and little lights start flashing but just came up short. This forum inspires me a lot. I figured I needed a good clean problem in order to ask for help and maybe getting down to a base idea would help me learn something along the way. Guess it was a momentary step into discipline (oops). But the hooks get in ya know, when you can see part of it but not all, addictive as all get out. Can't wait to chug a celebratory beer with the one and only reason being a clean flow from idea to elegant SQL solution. (Probably done that already a time or two but never gave myself credit or made it official cause there is usually a bit of a head slap involved)

The English language musings are tough to nail down when typed because of multiple/casual meanings of different words but figured it made sense to ask directly about that fact cause it seems a legitimate aspect of the problem solving process. Don't want to get in the habit mumbling out the plan to myself only to find it is a limitation to the relational thought process. But since those mumblings aren't a trap, but a method or tool, I'll use it when needed.

Still haven't committed to an SQL only retreat, just too many hot irons, but soon for sure. Tidying up payroll, pump out a few good summary reports and then we will deserve a little dedicated pressure off time. I am noticing that my skills are improving in little bits all the time just out of neccessity. But the mix of investigational trial and error just for the sake of it is so important, it really affects how you interpret the real stuff. That and data fimiliarity really open up the horizons and simply reduce the agony.

Like some here told me, it takes a while, and the procedural groundwork doen't always like to let go of certain parts of the brain.

And yeah got Henderson's books (stored procedures one too) they are already getting wrinkled.
Funny about computer books, they all seem like a great idea but only one in ten gets worn out.

ps. Alias season premiere soon (Sundays), just watched one episode but that show rocks.

Voted best SQL forum nickname...."Tutorial-D"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-16 : 08:13:02

I think the more experience you have the more it transcends language. Different solutions become grouped and named and when a new problem is in front of you, its a game of "this is like that". You build a toolbox from books, forums your own accidental solutions and then its just a matter of applying the right tool to the problem. The trick is the tool re-evaluation process. When I first started, I came over from the programming world. I very quickly latched onto cursors. It took a long time for me to not see the cursor solution first. I supposed its like a lot of things in the human condition; habituation. The difference between a good dba and a bad, is her ability know the difference between the good tools and the bad and to use the good ones out of habit. I'm not actually a smart guy. It's just that I've done this problem before ...

Bristow is hot; my new Buffy.

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources