Grouping

[Mdavis123]

I'm working on a Attendance Management Solution for our clients. I record values for
Sunday - Saturday in varchar(7) format. If a class was on Mon and Wed at 10 am, it would requite 2 records
CLASS1,01/01/1900 10:00:AM, 'FTFFFFF'
CLASS1,01/01/1900 10:00:AM, 'FFFTFFF'

I do attendance for each record(class) but some times I want to show when the class meets, I need to roll
the individual days into 'FTFTFFF'
I have tried sorting and grouping to no avail.

Anybody have a solution for my grouping problem??
TIA
Mike Davis

 

[tlbroadbent]

The table or application is poorly designed. Why store 7 days of data in one record and then create a record for every day? What is the purpose of the 7 character column?

If you can't change or don't want to change the table design, you'll have to parse the 7 character column, roll up the parsed values and concatenate. The following is one possible solution.

Select
Max(substring(sun_sat,1,1)) +
Max(substring(sun_sat,2,1)) +
Max(substring(sun_sat,3,1)) +
Max(substring(sun_sat,4,1)) +
Max(substring(sun_sat,5,1)) +
Max(substring(sun_sat,6,1)) +
Max(substring(sun_sat,7,1))
From table_name Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Mdavis123]

Terry,
We use a tool for the ClientSide that dosn't allow for much manipulation. I have a radio buttons for Sun thru Sat and it stores char(1) values in the table. Actually 7 individual Char(1) fields.

So I have a class that meets on Mon and Wed at 10 am. from Jan 1, 2002 through June 3, 2002. This is the master record and stores 'F','T','F','W','F','F','F','F'.
Our scheduler requires 0,1,0,1,0,0,0,0 to show a recurring appointment that I manipulate thru a view.

So the Tool Saves 'T' or 'F'
The Schedulere requires 0 or 1
The users when viewing see 'Y' or Blank.

When the user saves the record, a SP kicks in and creates an appointment record for each day the class meets. Additionally, an appointment is made for each Student and Staff member assigned to the class. When I roll these staff and students up for the week, I run into the problem with the group sort.

I have control over how these appointment records are stored and could go to a different data format if required.
Using a Desc sort on the Varchar(7), I get two records one with a 'Y' on Mon, the other with a 'Y' on Wed, the other days are blank. Not sure moving to 1 field would help cause I would still have to manipulate it in the view.

Any thoughts are welcome.
Thanks

 

[tlbroadbent]

Now, I am almost totally confused. This happens quite often on Monday!

What is the actual table structure? You listed a varchar(7) column, another example with 7 one character columns. What is the structure of the table(s) you are selecting from and want to group on? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Mdavis123]

Sorry for the confusion. The actual structure is
Classkey,ClientKey,TimeStart,TimeEnd,Sun,Mon,Tue,Wed,Thu,Fri,Sat
The records looks like
12345,4567,01/01/1900 10:00:AM,01/01/1900 11:00:AM, 'F','T','F,'F','F','F','F'

12345,4567,01/01/1900 10:00:AM,01/01/1900 11:00:AM, 'F','F','F,'W','F','F','F'

12345,4567,01/01/1900 10:00:AM,01/01/1900 11:00:AM, 'F','F','F,'F','F','T','F'


I want to group on distinct (ClientKey,ClassKey)
so that i get a summary record with Mon, Wed, Fri combined

12345,4567,10:00:AM,11:00:AM, 'F','T','F,'T','F','T','F'

Thanks Terry

 

[tlbroadbent]

Try this.

Select
Classkey,
ClientKey,
TimeStart=Max(TimeStart),
TimeEnd=Max(TimeEnd),
Sun=max(Sun),
Mon=max(Mon),
Tue=max(Tue),
Wed=max(Wed),
Thu=max(Thu),
Fri=max(Fri),
Sat=max(Sat)
From Table
Group By
Classkey,
ClientKey Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Mdavis123]

Terry,
By turnning all the other fields into aggreate fields, the Group by then works.

Thanks Mike

 

[tlbroadbent]

That's what I did, right? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.