Select Query Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Query Question

Author

Topic

alanhuro
Starting Member

34 Posts

Posted - 2009-05-29 : 10:41:49

Hello

I have a simple problem but it take a long time to process. I wonder if you could help me finding an alternative solution. The problem is I want to write a sql statement to check for any missing status which is not entered in the db. Here is a table looks like.

  
Item   Time                Code                 Status
 A    05/15/2009 00:00:00  100                 Received
 A    05/15/2009 00:01:00  110                 Processed 
 A    05/15/2009 00:02:00  120                 Approved
 B    05/15/2009 00:03:00  130                 Relocated  
 B    05/15/2009 00:04:00  140                 Send complete 
 C    05/15/2009 00:05:00  110                 Received  
 C    05/15/2009 00:06:00  110                 Processed

Everytime an item received it needs to have 3 status Received, Processed and Approved. Received and Processed are automatically done by computer. Approved is manually done by human. So sometime this status slipped. I would like to write a script that to check that if an item is Received and processed it needs to have approved. The script need to filter out Relocated and send complete. Basically the script I came up with is having a subquery to check for any item having the status of approved, relocated and send complete then a main script will filter out all the item in that subquery list. Here is a code


select item,time, code, status
from ProdHis
where Item not in (select distinct(item) where code in (120,130,140) and time between '2009-05-15 00:00:00' and '2009-05-16 00:00:00')
and time between '2009-05-15 00:00:00' and '2009-05-16 00:00:00'

The real table has more than 10 thousand of records a day so it takes a very long time to process for whole month. Could you show me how to write a better sript to improve the server performance.

Thanks

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-05-29 : 12:47:32

10,000 records should take a VERY small amount of time to plow through. Even a million records should take a small amount of time. Do you have any indexes on this table?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-29 : 12:48:26

What is your expected output and or is the query you provided correct? I might be reading your requirement incorrectly, but I thought you wanted to know all the Items that have been Received and Processed but not approved. Is that correct or am I off?

Assuming that I'm right, here is one way to do it:

DECLARE @ProdHis TABLE (Item CHAR(1), Time DATETIME, Code INT, Status VARCHAR(50))
INSERT @ProdHis
SELECT 'A',    '05/15/2009 00:00:00',  100,                 'Received'
UNION ALL SELECT 'A',    '05/15/2009 00:01:00',  110,                 'Processed' 
UNION ALL SELECT 'A',    '05/15/2009 00:02:00',  120,                 'Approved'
UNION ALL SELECT 'B',    '05/15/2009 00:03:00',  130,                 'Relocated'  
UNION ALL SELECT 'B',    '05/15/2009 00:04:00',  140,                 'Send complete' 
UNION ALL SELECT 'C',    '05/15/2009 00:05:00',  110,                 'Received'  
UNION ALL SELECT 'C',    '05/15/2009 00:06:00',  110,                 'Processed' 


SELECT 
	Item
FROM 
	@ProdHis
WHERE 
	Code IN (100, 110, 120)
	AND time BETWEEN '2009-05-15 00:00:00' and '2009-05-16 00:00:00'
GROUP BY
	Item
HAVING 
	COUNT(*) = 2

EDIT: Forgot Time restriction.

alanhuro
Starting Member

34 Posts

Posted - 2009-06-03 : 15:48:47

Thanks for the replied Lamprey and DonAtWork

DonAtWork - I don't know why my sql db took a long time to run a query. What I mean long time is around 30s instead of instant return.

Lamprey the result I try to get after running a query is an item which has missing status. In the table above the result should be C since because it missing Approved status. Your solution may work. I will post a reult

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources