Summing 'created' fields in a SELECT statement

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

Transact-SQL (2008)

Summing 'created' fields in a SELECT statement

Author

Topic

swims01
Yak Posting Veteran

59 Posts

Posted - 2012-03-09 : 15:54:24

Hey all.

I have a form that my company uses to get info on new clients.

We are paying staff for a certain amount of that form being completed. So, if 4 out of the 5 fields are filled out then they get paid on it. (Except there's much more than 5 fields)

I'm trying to figure out how to determine that 4 out of 5 fields have data in them. Any ideas how to do this?

My thought is that I do a CASE on each field and then assign a '1' if there is data found. I could then add the total together to get the final result showing the total fields filled out.


SELECT
CASE WHEN field1 IS NOT NULL THEN '1' ELSE '0' END AS field1,
CASE WHEN field2 IS NOT NULL THEN '1' ELSE '0' END AS field2,
CASE WHEN field3 IS NOT NULL THEN '1' ELSE '0' END AS field3,
CASE WHEN field4 IS NOT NULL THEN '1' ELSE '0' END AS field4,
CASE WHEN field5 IS NOT NULL THEN '1' ELSE '0' END AS field5,
sum( field1 + field2 + field3 + field4 + field5 ) as Score
FROM myTable

(I was thinking of dumping those results into a temp table and then I could 'select results where Score >= 4)

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2012-03-09 : 18:28:31

Would something like this work?

DECLARE @myTable TABLE (field1 int, field2 int, field3 int, field4 int, field5 int)
INSERT @myTable (field1, field2, field3, field4, field5)
SELECT 1, 1, 1, 1, 1 UNION
SELECT NULL, NULL, NULL, NULL, NULL UNION
SELECT 2, NULL, 2, 2, 2 UNION
SELECT 3, NULL, NULL, 3, 3 UNION
SELECT 4, 4, 4, NULL, 4 UNION
SELECT 0, NULL, NULL, 0, NULL UNION
SELECT 3, 3, 3, 3, NULL

SELECT field1+field2+field3+field4+field5 as Score
FROM (SELECT
CASE WHEN field1 IS NOT NULL THEN 1 ELSE 0 END AS field1,
CASE WHEN field2 IS NOT NULL THEN 1 ELSE 0 END AS field2,
CASE WHEN field3 IS NOT NULL THEN 1 ELSE 0 END AS field3,
CASE WHEN field4 IS NOT NULL THEN 1 ELSE 0 END AS field4,
CASE WHEN field5 IS NOT NULL THEN 1 ELSE 0 END AS field5
FROM @myTable) x

Some days you're the dog, and some days you're the fire hydrant.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-03-09 : 18:34:24

[CODE]SELECT *
FROM myTable
where CASE WHEN field1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN field2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN field3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN field4 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN field5 IS NOT NULL THEN 1 ELSE 0 END >= 4[/CODE]

=================================================
Men shout to avoid listening to one another. -Miguel de Unamuno

swims01
Yak Posting Veteran

59 Posts

Posted - 2012-03-11 : 11:47:32

quote:
Originally posted by Bustaz Kool

[CODE]SELECT *
FROM myTable
where CASE WHEN field1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN field2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN field3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN field4 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN field5 IS NOT NULL THEN 1 ELSE 0 END >= 4[/CODE]

=================================================
Men shout to avoid listening to one another. -Miguel de Unamuno

Love it! Thanks all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources