Need help with a TSQL Query please

mjohnson3091 · #1 (**permalink**) 10-25-05, 10:06

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.

Quote:

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:

Quote:

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.

Quote:

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.

madafaka · #2 (**permalink**) 10-26-05, 10:26

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
							  )
				)

mjohnson3091 · #3 (**permalink**) 10-26-05, 11:13

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

madafaka · #4 (**permalink**) 10-26-05, 12:10

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

mjohnson3091 · #5 (**permalink**) 10-27-05, 03:23

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".

madafaka · #6 (**permalink**) 10-27-05, 09:29

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

mjohnson3091 · #7 (**permalink**) 10-29-05, 04:58

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:

Quote:

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:

Quote:

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.

madafaka · #8 (**permalink**) 10-29-05, 17:43

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
                       )

mjohnson3091 · #9 (**permalink**) 10-30-05, 05:37

Thanks mate - that works much better.

Appreciate your effort on this one.

Internet Services

Web Development

Digital Marketing

Consumer Tech