Counting non-empty fields in one record 1

[bascy]

Hello all,

I'v got a table like the following:

CREATE TABLE PDPPLS01 (
	PLSPLSIDN INT IDENTITY,
	PLSKNDGD1 datetime NOT NULL,
	PLSKNDGD2 datetime NOT NULL,
	PLSKNDGD3 datetime NOT NULL,
	PLSKNDGD4 datetime NOT NULL,
	PLSKNDGD5 datetime NOT NULL,
	PLSKNDGD6 datetime NOT NULL)

The datetime fields are filled with dates on or after jan 1, 1800. There are other reasons why i dont put the 6 similar fields into a separate table.

I want to create a view with the IDENTITY and the number of datefields in each record that are greater then jan 1, 1800
so the following records:

1001, 1965-02-12, 1970-06-05, 1800-01-01, 1800-01-01, 1800-01-01, 1800-01-01
1002, 1982-05-12, 1800-01-01, 1999-05-21, 2001-02-03, 2001-05-29, 2000-11-20

would be shown in the view as:

1001, 2
1002, 5

Anyone got a clue how I can do this?

 

[tlbroadbent]

Here is one suggestion.

SELECT
PLSPLSIDN,
Case When PLSKNDGD1>'1800-01-01' Then 1 Else 0 End +
Case When PLSKNDGD2>'1800-01-01' Then 1 Else 0 End +
Case When PLSKNDGD3>'1800-01-01' Then 1 Else 0 End +
Case When PLSKNDGD4>'1800-01-01' Then 1 Else 0 End +
Case When PLSKNDGD5>'1800-01-01' Then 1 Else 0 End +
Case When PLSKNDGD6>'1800-01-01' Then 1 Else 0 End As Cnt

FROM PDPPLS01 Terry

People who don't take risks generally make about two big mistakes a year. People who do take risks generally make about two big mistakes a year. -Peter Drucker

 

[bascy]

Thanks for the quick respons!

it works perfectly