Post reply

Need help to build query

  • July 25, 2013 at 8:58 am

    #276358

    create table sample ( a date,b date, c date ,d date )

    insert into sample values (null,GETDATE(),GETDATE(),GETDATE())

    insert into sample values (null,GETDATE(),GETDATE(),null)

    insert into sample values (GETDATE(),GETDATE(),null,GETDATE())

    insert into sample values (GETDATE(),GETDATE()+1,GETDATE(),null)

    select * from sample

    output :

    abcd

    NULL2013-07-252013-07-252013-07-25

    NULL2013-07-252013-07-25NULL

    2013-07-252013-07-25NULL2013-07-25

    2013-07-252013-07-262013-07-25NULL

    Logic : all not null column should be equal. If any row not falls in that condition

    needs to be hi-lighted.

    requested output is

    abcd

    2013-07-252013-07-262013-07-25NULL

  • July 25, 2013 at 9:10 am

    #1635773

    Something like this?

    select *

    from sample

    WHERE a <> b

    OR a <> c

    OR a <> d

    OR b <> c

    OR b <> d

    OR c <> d

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 25, 2013 at 9:47 am

    #1635800

    yes.

    but null values need not to be cheked

  • July 25, 2013 at 9:50 am

    #1635802

    vignesh.ms (7/25/2013)

    yes.

    but null values need not to be cheked

    The code that Luis posted will do exactly that. NULL <> a date.

    Try the code he posted.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • July 25, 2013 at 9:56 am

    #1635805

    Another way:

    SELECT S.*

    FROM SAMPLE S

    CROSS APPLY (SELECT MAX(dt) mxd, MIN(dt) mnd FROM (VALUES (a),(b),(c),(d)) as v(dt)) A

    WHERE A.mxd != A.mnd

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • July 25, 2013 at 9:58 am

    #1635808

    yeah its working ...

    any other simple method ???

  • July 25, 2013 at 10:24 am

    #1635823

    thanks many

  • July 25, 2013 at 10:26 am

    #1635825

    vignesh.ms (7/25/2013)

    yeah its working ...

    any other simple method ???

    I thought it was quite simple... 😉

    Cursor anyone?

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • July 25, 2013 at 10:35 am

    #1635833

    yes your query is very simple ..

    but i cant understand ..

    Please explain what is cross apply ..

    how dose it work in this query?

  • July 25, 2013 at 10:43 am

    #1635834

    vignesh.ms (7/25/2013)

    yes your query is very simple ..

    but i cant understand ..

    Please explain what is cross apply ..

    how dose it work in this query?

    Check out Paul White's 2 part series on APPLY here. http://www.sqlservercentral.com/articles/APPLY/69953/[/url]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • July 25, 2013 at 10:47 am

    #1635835

    vignesh.ms (7/25/2013)

    yes your query is very simple ..

    but i cant understand ..

    Please explain what is cross apply ..

    how dose it work in this query?

    Search for "APPLY" in this BoL article:

    http://msdn.microsoft.com/en-us/library/ms177634(v=sql.105).aspx

    it explains what APPLY does.

    (VALUES (a),(b),(c),(d) ) v(dt)

    transposes columns from your "sample" table into the rows of in-line table/set v (dt - is the alias for its column)

    The rest is very simple:

    Select MAX and MIN out of a,b,c & d (via v(dt)) will ignore nulls and return two values.

    If MAX=MIN then it means that all values are the same. If MAX!=MIN, then at least one value is different to others.

    I bet, that whould be faster than a<>b or b<>c etc...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

Viewing 11 posts - 1 through 10 (of 10 total)

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