Post reply

How to Get the count of columns based on some value

  • May 21, 2010 at 3:03 am

    #156973

    I have 40 columns in my database table

    col1 col2 col3 col4....col40 countcol

    datevalue datevalue x x x 2

    datevalue x x x x 1

    x datevalue x x x 1

    x x x x x 0

    I want to get the column countcol so that it gives me count of total datevalues present

    for example:

    in row 1 col1 and col2 has datevalue i.e. some datevalue will be there

    in row 2 only 1 date value is there...

    like this....

    please help me in writing the query logic for this

  • May 21, 2010 at 3:22 am

    #1170669

    What are the datatypes of the columns ?

    you could probably us ISDATE() and add the each of the columns to each other.

    eg

    SELECT ISDATE(col1) + ISDATE(col2) + ISDATE(col3)..... from YourTable

    post the table layout DDL and some sample data for more specific help..

  • May 21, 2010 at 3:25 am

    #1170671

    All the columns are of type nvarchar

  • May 21, 2010 at 3:25 am

    #1170672

    steveb. (5/21/2010)

    What are the datatypes of the columns ?

    you could probably us ISDATE() and add the each of the columns to each other.

    eg

    SELECT ISDATE(col1) + ISDATE(col2) + ISDATE(col3)..... from YourTable

    post the table layout DDL and some sample data for more specific help..

    All the columns are of type nvarchar

  • May 21, 2010 at 3:29 am

    #1170673

    ISDATE should work then, though you should do some reading on the limitations of ISDATE and how sql handles and determines a date and how your system settings such as language and dateformat wiil affect your results.

  • May 21, 2010 at 3:33 am

    #1170677

    Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply