Grouping Issue

[jcale4]

Hello!

I have a table which holds scheduling information about people. The way this table has to be built results in data that looks like this:

Person Monday Tuesday
-------------- ------------- -------------
Mary Fletchall SMR D9 -
Mary Fletchall SMR E10 -
Mary Fletchall SMR E8 -
Mary Fletchall - SMR D9
Mary Fletchall - SMR E15
Mary Fletchall - SMR Y7


(where "-" = null)

I want to select that data into a recordset that looks like this:


Person Monday Tuesday
-------------- ------------- -------------
Mary Fletchall SMR D9 SMR D9
Mary Fletchall SMR E10 SMR E15
Mary Fletchall SMR E8 SMR Y7


I have done this in the past very easily by using SUM(), but that was with integer data. Now that i'm working with Varchar data, i cant figure out how to group these rows to produce this result.

Help!

Thanks!

 

[PHV]

Perhaps something like this ?
SELECT DISTINCT P.Person, M.Monday, T.Tuesday
FROM yourTable P
LEFT JOIN yourTable M ON P.Person = M.Person AND M.Tuesday IS NULL
LEFT JOIN yourTable T ON P.Person = T.Person AND T.Monday IS NULL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zathras]

That results in a cartesian product (returns 9 rows).

Here is a script that works with the data given. It won't scale up very easily and doesn't handle the case where there are not the same number of values for each day. (It may not be ANSI SQL either -- it uses SQL Server IDENTITY feature.)

It may however give you a place to start.
[tt]
create table #t (person char(15),Monday char(7), Tuesday char(7))
insert into #t (person,Monday) values('Mary Fletchall','SMR D9')
insert into #t (person,Monday) values('Mary Fletchall','SMR E10')
insert into #t (person,Monday) values('Mary Fletchall','SMR E8')
insert into #t (person,Tuesday) values('Mary Fletchall','SMR D9')
insert into #t (person,Tuesday) values('Mary Fletchall','SMR E15')
insert into #t (person,Tuesday) values('Mary Fletchall','SMR Y7')

create table #Monday (ID integer IDENTITY, person char(15), Code char(7))
create table #Tuesday (ID integer IDENTITY, person char(15), Code char(7))
insert into #Monday select person,Monday from #t where Monday is not null
insert into #Tuesday select person,Tuesday from #t where Tuesday is not null

select m.person,m.code as Monday,t.code as Tuesday
from #Monday M
inner join #Tuesday T on m.person=t.person and m.id=t.id

drop table #Monday
drop table #Tuesday
drop table #t
[/tt]

 

[JarlH]

To fully understand the problem at least I need some more information.

Do you want that specific combination of Monday and Tuesday values? (Does it really matter...?)
I.e. is "Mary Fletchall SMR D9 SMR E15" fine as well?

What's the expected result if the number of (non-null) Monday rows isn't the same as the number of (non-null) Tuesday rows?

May the same value occur twice in a column?

 

[PHV]

That results in a cartesian product (returns 9 rows).
You're right, typed and submitted too fast :~/
Perhaps this is better ?
SELECT M.Person, M.Monday, T.Tuesday
FROM (
SELECT A.Person, A.Monday, COUNT(B.Monday) Rank
FROM yourTable A INNER JOIN yourTable B
ON A.Person = B.Person AND A.Monday >= B.Monday
WHERE A.Monday IS NOT NULL AND B.Monday IS NOT NULL
GROUP BY A.Person, A.Monday
) M LEFT JOIN (
SELECT A.Person, A.Tuesday, COUNT(B.Tuesday) Rank
FROM yourTable A INNER JOIN yourTable B
ON A.Person = B.Person AND A.Tuesday >= B.Tuesday
WHERE A.Tuesday IS NOT NULL AND B.Tuesday IS NOT NULL
GROUP BY A.Person, A.Tuesday
) T ON M.Person = T.Person AND M.Rank = T.Rank
UNION
SELECT T.Person, M.Monday, T.Tuesday
FROM (
SELECT A.Person, A.Monday, COUNT(B.Monday) Rank
FROM yourTable A INNER JOIN yourTable B
ON A.Person = B.Person AND A.Monday >= B.Monday
WHERE A.Monday IS NOT NULL AND B.Monday IS NOT NULL
GROUP BY A.Person, A.Monday
) M RIGHT JOIN (
SELECT A.Person, A.Tuesday, COUNT(B.Tuesday) Rank
FROM yourTable A INNER JOIN yourTable B
ON A.Person = B.Person AND A.Tuesday >= B.Tuesday
WHERE A.Tuesday IS NOT NULL AND B.Tuesday IS NOT NULL
GROUP BY A.Person, A.Tuesday
) T ON M.Person = T.Person AND M.Rank = T.Rank

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

May the same value occur twice in a column ?
Hopefully no (at least for a given person) because this would defeat my ranking subqueries ...

 

[juliane26]

JarlH's questions are right:

just as it looks right now it doesn't make too much sense.

Juliane

 

[jcale4]

A person can only have 1 value per day column, so the other rows for that day are null. I could not figure out how to get that query to work so i converted them to numbers and i'm summing. I then just convert them back to the character equivs in a "decode" table. But, I really appreciate everyone's help on this!