Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Posts
    128

    Unanswered: Number of entry query.

    MSSQL2K
    SP4

    Howdy all. Im trying to write a query that will track a data modification grouped by employer ID and transaction date. I don't know if Im asking it right so here is what I have, plus my current and desired outputs.

    --drop table #foo
    create table #foo
    (empID int,
    transDate datetime,
    transType varchar(10))

    insert into #foo values(1, '01/01/06 01:01:01','Insert')
    insert into #foo values(1, '01/01/06 01:01:02','Update')
    insert into #foo values(1, '01/01/06 01:01:03','Delete')
    insert into #foo values(2, '01/01/06 01:01:01','Insert')
    insert into #foo values(2, '01/01/06 01:01:02','Update')

    select f.empID, Change =
    (select count(transDate) from #foo f2
    where f2.empID = f.empID
    group by empID),
    f.transDate, f.transType
    from #foo f

    Current results:

    1 3 2006-01-01 01:01:01.000 Insert
    1 3 2006-01-01 01:01:02.000 Update
    1 3 2006-01-01 01:01:03.000 Delete
    2 2 2006-01-01 01:01:01.000 Insert
    2 2 2006-01-01 01:01:02.000 Update

    Desired results:

    1 1 2006-01-01 01:01:01.000 Insert
    1 2 2006-01-01 01:01:02.000 Update
    1 3 2006-01-01 01:01:03.000 Delete
    2 1 2006-01-01 01:01:01.000 Insert
    2 2 2006-01-01 01:01:02.000 Update

    TIA, CFR

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    where f2.empID = f.empID AND f2.transdate <= f.transdate

    Which presumes no 2 can have the same date/time value.
    Paul

  3. #3
    Join Date
    Nov 2004
    Posts
    128
    So close, yet so far. Thanks.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Sorry, does that mean the answer is close but not correct? It produces the desired result on the sample data.
    Paul

  5. #5
    Join Date
    Jul 2006
    Posts
    87
    Quote Originally Posted by pbaldy
    Sorry, does that mean the answer is close but not correct? It produces the desired result on the sample data.
    Nah, I am pretty sure he means that until your assist, CFR was so closer and yet so far from their solution.

    After all, they would have asked for more help other wise, or said what they were getting wrong.

    I guess we should be glad that they at least wrote back to thank you. Some folks get the answer, and then disappear.

  6. #6
    Join Date
    Nov 2004
    Posts
    128
    As Code Carpenter mentioned, I was so close yet so far. The code did exactly what I needed. As far as disappearing, Im afraid you folks are stuck with me for a while.

    Thanks again!

Posting Permissions

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