Professional Web Applications Themes

Is this Query Possible ? - Microsoft SQL / MS SQL Server

Krist, Table structures and sample data make answers easier. Something like this: create table Krist ( EmpNo char(3), Branch char(4), TrDate datetime ) insert Krist values ( '001', 'AAAA', '20020125' ) insert Krist values ( '001', 'AAAA', '20020225' ) insert Krist values ( '001', 'AAAA', '20020325' ) insert Krist values ( '001', 'BBBB', '20020425' ) insert Krist values ( '001', 'BBBB', '20020525' ) insert Krist values ( '001', 'BBBB', '20020625' ) insert Krist values ( '001', 'AAAA', '20020725' ) insert Krist values ( '001', 'AAAA', '20020825' ) insert Krist values ( '001', 'BBBB', '20020925' ) insert Krist values ( '001', ...

  1. #1

    Default Re: Is this Query Possible ?

    Krist,

    Table structures and sample data make answers easier.
    Something like this:

    create table Krist
    (
    EmpNo char(3),
    Branch char(4),
    TrDate datetime
    )
    insert Krist values ( '001', 'AAAA', '20020125' )
    insert Krist values ( '001', 'AAAA', '20020225' )
    insert Krist values ( '001', 'AAAA', '20020325' )
    insert Krist values ( '001', 'BBBB', '20020425' )
    insert Krist values ( '001', 'BBBB', '20020525' )
    insert Krist values ( '001', 'BBBB', '20020625' )
    insert Krist values ( '001', 'AAAA', '20020725' )
    insert Krist values ( '001', 'AAAA', '20020825' )
    insert Krist values ( '001', 'BBBB', '20020925' )
    insert Krist values ( '001', 'BBBB', '20021025' )

    Select k1.EmpNo, k1.Branch, k1.TrDate
    from Krist k1
    left outer join Krist k2
    on k2.TrDate = dateadd( m, +1, k1.TrDate )
    and k2.Branch = k1.Branch
    where k2.Branch is NULL

    Regards
    AJ


    "Krist Lioe" <com> wrote in message news:phx.gbl... 


    Andrew Guest

  2. #2

    Default Re: Is this Query Possible ?

    Please post DDL in the future. The following provides the requested
    output but this may be an incomplete solution.

    CREATE TABLE MyTable
    (
    EmpNo char(3) NOT NULL,
    Branch char(4) NOT NULL,
    TrDate smalldatetime not null
    CONSTRAINT PK_MyTable
    PRIMARY KEY (TrDate)
    )
    GO

    INSERT INTO MyTable VALUES('001', 'AAAA', '2002-01-25')
    INSERT INTO MyTable VALUES('001', 'AAAA', '2002-02-25')
    INSERT INTO MyTable VALUES('001', 'AAAA', '2002-03-25')
    INSERT INTO MyTable VALUES('001', 'BBBB', '2002-04-25')
    INSERT INTO MyTable VALUES('001', 'BBBB', '2002-05-25')
    INSERT INTO MyTable VALUES('001', 'BBBB', '2002-06-25')
    INSERT INTO MyTable VALUES('001', 'AAAA', '2002-07-25')
    INSERT INTO MyTable VALUES('001', 'AAAA', '2002-08-25')
    INSERT INTO MyTable VALUES('001', 'BBBB', '2002-09-25')
    INSERT INTO MyTable VALUES('001', 'BBBB', '2002-10-25')
    GO

    SELECT
    EmpNo,
    Branch,
    CONVERT(char(10), TrDate, 120) AS TrDate
    FROM MyTable a
    WHERE a.Branch <>
    COALESCE(
    (
    SELECT Branch
    FROM MyTable b
    WHERE b.TrDate =
    (
    SELECT MIN(c.TrDate)
    FROM MyTable c
    WHERE c.TrDate > a.TrDate
    )
    ),
    '')

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):

    http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
    http://www.sqlserverfaq.com
    http://www.mssqlserver.com/faq
    -----------------------

    "Krist Lioe" <com> wrote in message
    news:phx.gbl... 



    Dan Guest

  3. #3

    Default Re: Is this Query Possible ?

    That coalesce is a stunner. Can you provide a simple example of using it
    based upon what you said

    Thanks

    "Steve Kass" <edu> wrote in message
    news:phx.gbl... 
    >[/ref]


    Hassan Guest

  4. #4

    Default Re: Is this Query Possible ?

    Hassan,

    COALESCE(argument1, argument2, argument3, ...) simply
    evaluates to the first non-NULL argument provided (or NULL
    if all arguments are null). Here it makes sure that if there is no
    "next" row for a particular employee, that the <> comparison with
    the current Branch is against '', which will return true (assuming
    '' isn't a valid branch), as opposed to against NULL, which will
    return unknown, resulting in the latest row for the employee not
    being returned by the query.

    You can find the details of COALESCE in the SQL Server
    doentation, Books Online.

    SK

    Hassan wrote:
     [/ref]
    >
    >
    >
    >[/ref]

    Steve Guest

  5. #5

    Default Re: Is this Query Possible ?

    Hi All,
    Thanks to you all,
    That solve my problem...
    Krist

    "Dan Guzman" <net> wrote in message
    news:phx.gbl... 
    > ? 
    >
    >[/ref]


    tristant Guest

Similar Threads

  1. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  2. Replies: 1
    Last Post: July 2nd, 09:09 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139