Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2004
    Posts
    7

    Unanswered: Need help with a TSQL Query please

    Hi All,

    I'm a real novice with SQL and I'm struggling getting the required data out of a Table.

    The Table is non-normalised (at customers request) and has the following layout.

    ID int
    Zone varchar(10)
    Location varchar(10)
    Item varchar(10)
    Count float
    Date datetime
    This is used for a stock count system where two users have to count the same area. so you would get a single entry per user per item/location.

    The query I need to perform is to get a list of the difference in Count. So based on the following simplified data:
    ID ZONE LOCN. ITEM USER DATE COUNT
    1 FG ABC1 123456 MAJ 25/10/2005 10
    2 FG ABC1 123456 DAV 25/10/2005 10
    3 FG ABC2 654321 MAJ 25/10/2005 15
    4 FG ABC2 654321 DAV 25/10/2005 14
    5 FG ABC3 999999 DAV 25/10/2005 20
    I would want something like this as an output so I can present that back.

    ID ZONE LOCN. ITEM USER DATE COUNT
    3 FG ABC2 654321 MAJ 25/10/2005 15
    4 FG ABC2 654321 DAV 25/10/2005 14
    5 FG ABC3 999999 DAV 25/10/2005 20
    Any help or advice on this one would be appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    try this one: (replace temp_temp with your table name)

    Code:
    select * from temp_temp
    where id not in (select id 
    	  	 	 	 from temp_temp t2 
    	  	 	     where exists (select 1 from temp_temp t3 
    				 	   		   where t2.count = t3.count and t2.id <> t3.id
    							  )
    				)

  3. #3
    Join Date
    Oct 2004
    Posts
    7
    Thanks Madafaka,

    That does exactly what I needed. I can now do some manipulation in my code to identify which lines are miscounts (i.e.: Both users counted, but are different) and singular counts (Only one user actually counted that item).

    Thanks again.

    Regards,

    Mark

  4. #4
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    actually this one should work as well and is bit easier:

    Code:
    select * from temp_temp
    where COUNT not in (select COUNT
    	  	  from temp_temp t2 
    	  	  group by COUNT
                                having count(1) > 1
                               )
    now it's quite confusing, COUNT is column name, and count(1) is agregation function

  5. #5
    Join Date
    Oct 2004
    Posts
    7
    Quote Originally Posted by madafaka
    actually this one should work as well and is bit easier:

    Code:
    select * from temp_temp
    where COUNT not in (select COUNT
    	  	  from temp_temp t2 
    	  	  group by COUNT
                                having count(1) > 1
                               )
    now it's quite confusing, COUNT is column name, and count(1) is agregation function
    This works well also - the "Count" column is actually called "Counted Quantity", wasn't thinking when I abbreviated it for the post! (DOH!).

    I have to ask another question, where you have the aggregate Count (1), what is the "1" representing? I spotted something similar in the first post "select 1 from temp_temp".

  6. #6
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    1 is just constant. e.g. if you have table:
    Code:
    ID ZONE LOCN. ITEM USER DATE COUNT
    1 FG ABC1 123456 MAJ 25/10/2005 10
    2 FG ABC1 123456 DAV 25/10/2005 10
    3 FG ABC2 654321 MAJ 25/10/2005 15
    4 FG ABC2 654321 DAV 25/10/2005 14
    5 FG ABC3 999999 DAV 25/10/2005 20
    you select from the table:
    Code:
    select ID, ZONE, LOCN, 1, 'ANOTHER_CONSTANT'
    from table
    your result will be:
    Code:
     
    1 FG ABC1 1 ANOTHER_CONSTANT
    2 FG ABC1 1 ANOTHER_CONSTANT
    3 FG ABC2 1 ANOTHER_CONSTANT
    4 FG ABC2 1 ANOTHER_CONSTANT
    5 FG ABC3 1 ANOTHER_CONSTANT
    for function count() you can use whatever
    count(ID), count(*) or count('constant') I used count(1)
    Code:
    select 1 from...
    is usually used in WHERE EXISTS (...) as you're not interested in exact value you're just interested if there's a record in the table which match condition. The trick is, database engine doesn't have to read the value (physical read on the HDD) it just check if there's a record matching condition in this case usually index is used for accessing data. It might save you a time if you're selecting huge volume of data
    Last edited by madafaka; 10-27-05 at 10:34.

  7. #7
    Join Date
    Oct 2004
    Posts
    7
    Hi Mate,

    Been having a further play with this query and it seems to have a slight problem.

    If I add extra records to the table that have the same quantity but are a different Item, Location they don't get returned in the query.

    Maybe I confused things from the beginning or didn't explain properly.....

    The table is populated by two users counting items in a store room.

    So there should be one record from each person for each Item/Location pairing. The quantities may or may not be the same.

    It is also possible for one user to miss counting an item altogether, therefore there would only be one record for the Item/Location.

    I'm looking for the query to return all records that don't match. So it should return and mismatch quantities for a Item/Location pairing and also any single record Item/Location entries.

    That probably makes no sense at all!

    So back to the table:

    ID ZONE LOCN. ITEM USER DATE COUNT
    1 FG ABC1 123456 MAJ 25/10/2005 10
    2 FG ABC1 123456 DAV 25/10/2005 10
    3 FG ABC2 654321 MAJ 25/10/2005 15
    4 FG ABC2 654321 DAV 25/10/2005 14
    5 FG ABC3 999999 DAV 25/10/2005 20
    this should return the following:

    ID ZONE LOCN. ITEM USER DATE COUNT
    3 FG ABC2 654321 MAJ 25/10/2005 15
    4 FG ABC2 654321 DAV 25/10/2005 14
    5 FG ABC3 999999 DAV 25/10/2005 20
    Because ID 2 and 3 have a matching Item/Location/Count they are not returned because they are correct.

    ID 3 and 4 are both returned, because although they have a matching Item/Location, the Counts are different.

    ID 5 is returned because it is a single entry record, so one user didn't count it.

    Hope that makes some sense.

    Thanks again.

  8. #8
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    OK, try this one, but I didn't test it as I'm not currently connected to database, so in case it doesn't work just let me know, and I'll fix it as soon as I'll be connected.
    Code:
    select * 
    from table A
    where not exists (select 1 
                            from table B
                            where A.ITEM = B.ITEM
                            and    A.LOCATION = B.LOCATION
                            and    A.COUNT = B.COUNT
                            and    A.ID <> B.ID
                           )

  9. #9
    Join Date
    Oct 2004
    Posts
    7
    Thanks mate - that works much better.

    Appreciate your effort on this one.

Posting Permissions

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