count of values for two fields

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 2005 Forums

Analysis Server and Reporting Services (2005)

count of values for two fields

Author

Topic

dmaxj
Posting Yak Master

174 Posts

Posted - 2009-07-12 : 14:00:26

I am struggling with the best way to deal with counting the values of two fields. My dataset follows:

pkPatient ID DayAvailable1 TimeAvailable1 DayAvailable2 TimeAvailable2 DayAvailable3 TimeAvailable3
1 Monday Morning Wednesday Afternoon Saturday Evening
2 Monday Morning Tuesday Afternoon Wednesday Evening
3 Monday Morning Wednesday Morning Tuesday Afternoon
4 Tuesday Morning Tuesday Afternoon Wednesday Afternoon
5 Tuesday Afternoon Tuesday Afternoon Wednesday Afternoon
6 Monday Morning Tuesday Afternoon Tuesday Afternoon
7 Monday Morning Tuesday Afternoon Tuesday Afternoon
8 Tuesday Morning Monday Morning Tuesday Afternoon
9 Monday Morning Tuesday Morning Tuesday Afternoon
10 Monday Morning Tuesday Afternoon Wednesday Afternoon
11 Monday Morning Tuesday Morning Tuesday Evening
12 Monday Morning Tuesday Afternoon Wednesday Evening
13 Monday Morning Tuesday Afternoon Wednesday Evening
14 Monday Afternoon Tuesday Afternoon Thursday Afternoon
15 Monday Morning Tuesday Afternoon Wednesday Afternoon
16 Tuesday Afternoon Monday Morning Tuesday Morning
17 Monday Morning Tuesday Afternoon Wednesday Afternoon
18 Monday Morning Tuesday Afternoon Wednesday Afternoon
19 Tuesday Morning Wednesday Morning Thursday Evening
20 Friday Afternoon Saturday Morning Tuesday Afternoon
21 Tuesday Afternoon Tuesday Afternoon Monday Evening
22 Wednesday Evening Tuesday Morning Saturday Morning
23 Tuesday Morning Wednesday Morning Wednesday Evening
24 Monday Morning Wednesday Afternoon Friday Evening

The field names are on the first line and the values follow.

I am trying to figure the best way to count each DayAvailable1 and each TimeAvailable1 and the same for each following field.

So I have 15 Monday Mornings, 1 Monday Afternoon, 0 Monday Evening for DayAvailable1 and TimeAvailable2, etc. I see that I could potentially have 21 queries per set of Date and Time Availability ( 7 days X 3 times of days).

This would mean a total of 63 queries( 21 queries X 3 column sets) - I am wondering if there is an easier or better way to produce this report.

Regards

bhaskar.vce
Starting Member

5 Posts

Posted - 2009-07-12 : 22:10:46

Frankly Speaking i was unable to find what is ur requirement....could u be more specific...then i think i can help u

Bhaskar Avisha

dmaxj
Posting Yak Master

174 Posts

Posted - 2009-07-13 : 01:09:30

I am trying to get a count of each set: DayAvailable1 TimeAvailable1; DayAvailable2 TimeAvailable2; DayAvailable3 TimeAvailable3

How many Monday Morning, Monday, Afternoon, Monday Evening -
How many Tuesday Morning, Tuesday, Afternoon, Tuesday Evening -
.
.
.
How many Saturday Morning, Saturday Afternoon, Saturday Evening -

At this point I am looking at this query:
SELECT COUNT (*) AS Day1_MondayMornings
FROM tableName
WHERE DayAvailable1 = 'Monday' AND TimeAvailable1 = 'Morning'

I would have to perform this query 63 times in order to cover all combinations. Is there a smarter and more efficient way to handle this report?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-13 : 14:16:54

one method is this:-


SELECT Val1,Val2,COUNT(*)
FROM
(SELECT DayAvailable1 AS Val1,	TimeAvailable1 AS Val2
 FROM Table
UNION ALL
SELECT DayAvailable2 ,	TimeAvailable2 
 FROM Table
UNION ALL
SELECT DayAvailable3,	TimeAvailable3 
 FROM Table
....
)t
GROUP BY Val1,Val2

dmaxj
Posting Yak Master

174 Posts

Posted - 2009-07-14 : 12:04:29

Thanks, visakh16 - works like a charm in SSMS!!!

I do wonder why the same query does not work in the Data Tab in SSRS. I get a 'Parse Error in SQL' message.

Any clues?

Regards

dmaxj
Posting Yak Master

174 Posts

Posted - 2009-08-24 : 17:36:55

I have been banging my head for 4 weeks with the following problems. The following query give this error when run in SSRS: "Parse Error in SQL"

Any know why the query would work in SS Management Studio, but give a 'Parse Error in SQL' in Reporting Services?

I am working on another solution in case SSRS can't process the query. This includes SSIS to pull the data from my ODBC source and populating a SQL table, but I can't seem to get DataReader Source or OLE DB Source to work. Maybe I should start a new thread.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources