Count Several fields in same row 1

[dvannoy]

I'm trying to count records that are past due.

table has 5 dates and a field called type

e.g.

date_1, date_2, date_3, date_4, date_5, type

I'm trying to count the above dates based on <getdate() and group them by type. so the results would show something like this

type  date_1   date_2   date_3   date_4   date_5
---------------------------------------------------
A     10       5        1        0        5
B     2        1        3        5        10

I can't seem to figure this out.

any help would be appreciated

thanks

 

[Olaf Doschke]

Let's start with date_1

SELECT type, SUM(CASE WHEN date_1<GETDATE() THEN 1 ELSE 0 END) as date_1_past_count FROM yourtable GROUP BY type

The rest now is just repetition, Copy&Paste and substitution date_2,3, etc.

Bye, Olaf.

PS: You better had the data not in its pivoted state. SQL works best on normalized data and your data is pivoted, not normal. A normalized schema would only have a date column and then 5 records instead of 1.