Ok, sorry
the solution is :

SELECT
CASE
WHEN city IS NULL OR CONVERT(VARCHAR(50), city) = '' THEN
1
ELSE
0
END
+
CASE
WHEN postcode IS NULL OR CONVERT(VARCHAR(50), postcode) = '' THEN
1
ELSE
0
END
+
CASE
WHEN lastname IS NULL OR CONVERT(VARCHAR(50), lastname) = ''THEN
1
ELSE
0
END TotalNullFields, employee_id
FROM Table1
WHERE employee_id = 1000

if empty fields are numeric you have to convert varchar (exmp:
CONVERT(VARCHAR(50), lastname)) else unnecessary.
best regards
ali

----- Original Message -----
From: "Valsan, Jessy" <Jessy.[Email Address Removed] "O15_11_2002" <[Email Address Removed] "LazyDBA.com Discussion"
<[Email Address Removed] Thursday, April 29, 2004 12:20 PM
Subject: RE: Need help in query


> Hi Ali,
>
> Thanks for the reply. This is not exactly i required.
>
> For eg: in my employee table for employee id 100 i have 5 null fields
> (city,postcode,lastname...etc) So for emp id 100 query should return 5.
For
> another emp id 200 if six fields are null for that particular record it
> should display 6.
> Not the sum for a particular field,But total number of empty fileds for a
> particular record. We can pass employee_id to filter the record
>
> Thanks
> Jessy
>
>
>
> -----Original Message-----
> From: O15_11_2002 [mailto:[Email Address Removed] Sent: Thursday, April 29, 2004 2:23 PM
> To: LazyDBA.com Discussion; Valsan, Jessy
> Subject: Re: Need help in query
>
>
> SELECT SUM(TotalField1) AS SumOfTotalField1
> FROM TABLE1
> WHERE (NullField1 IS NULL or NullField1 = '')
> AND /*-or OR-*/ (NullField2 IS NULL OR NullField2 = '')
> AND /*-or OR-*/ (NullField3 IS NULL OR NullField3 = '')
>
> If you use And operator query show only all null fields which are your
> written where condition to result
> if use Or operator query show whichever null fields to result
>
> best regards
> ali
>
> ----- Original Message -----
> From: "Valsan, Jessy" <Jessy.[Email Address Removed] To: "LazyDBA.com Discussion" <[Email Address Removed] Sent: Thursday, April 29, 2004 11:21 AM
> Subject: RE: Need help in query
>
>
> > Hi,
> >
> > I have a table with 100 fields,Now few of these fields are emplty. I
need
> a
> > query that will give me the total number of empty fields for a given
> record.
> >
> > Pls help.
> >
> > Thanks
> > Jessy
> >
> >
> > This e-mail and any attachment is for authorised use by the intended
> recipient(s) only. It may contain proprietary material, confidential
> information and/or be subject to legal privilege. It should not be copied,
> disclosed to, retained or used by, any other party. If you are not an
> intended recipient then please promptly delete this e-mail and any
> attachment and all copies and inform the sender. Thank you.
> >
> > ---------------------------------------------------------------------
> > TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> > Get today's cartoon: http://www.LazyDBA.com
> > To unsubscribe, e-mail: mssqldba-[Email Address Removed] ,
> > then respond to the confirmation email you get
> > For additional commands, e-mail: mssqldba-[Email Address Removed] >
> >
>
> This e-mail and any attachment is for authorised use by the intended
recipient(s) only. It may contain proprietary material, confidential
information and/or be subject to legal privilege. It should not be copied,
disclosed to, retained or used by, any other party. If you are not an
intended recipient then please promptly delete this e-mail and any
attachment and all copies and inform the sender. Thank you.
>
MS Sql Server LazyDBA home page