[Resolved] Query not producing desired result

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

SQL Server 2005 Forums

Transact-SQL (2005)

[Resolved] Query not producing desired result

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-07-02 : 11:06:20

I have a query that selects job production data (see code below). The query works except for that I am not able to extract existing "notes" for a specific job.

SELECT          dbo.event.eventdate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId,
                dbo.JobNoteEvent.Note, dbo.productionevent.quantity   
		
FROM            dbo.Batch
		inner join dbo.event on dbo.event.batchguid = dbo.batch.batchguid
		inner join dbo.job on dbo.job.jobguid = dbo.event.jobguid
		inner join dbo.item on dbo.item.itemguid = dbo.event.itemguid
		inner join dbo.productionevent on dbo.productionevent.eventguid = dbo.event.eventguid
                left join dbo.JobNoteEvent on dbo.JobNoteEvent.EventGuid = dbo.event.eventguid
                
WHERE          dbo.Job.CompanyJobId = 3505048 and dbo.Item.CompanyItemId = '05001' 
	       
ORDER BY       dbo.event.eventdat

Result:

EventDate = 2008-04-07
CompanyJobId = 3505048
CompanyItemId = 05001
Notes = Null
Quantity = 276.78

Now, I if comment out lines that extarcts the quantity like this:

SELECT          dbo.event.eventdate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId,
                dbo.JobNoteEvent.Note --, dbo.productionevent.quantity   		
FROM            dbo.Batch
		inner join dbo.event on dbo.event.batchguid = dbo.batch.batchguid
		inner join dbo.job on dbo.job.jobguid = dbo.event.jobguid
		inner join dbo.item on dbo.item.itemguid = dbo.event.itemguid
		--inner join dbo.productionevent on dbo.productionevent.eventguid = dbo.event.eventguid                
                left join dbo.JobNoteEvent on dbo.JobNoteEvent.EventGuid = dbo.event.eventguid
                
WHERE          dbo.Job.CompanyJobId = 3505048 and dbo.Item.CompanyItemId = '05001' 
	       
ORDER BY       dbo.event.eventdate

Then I get the notes like below

EventDate = 2008-04-07
CompanyJobId = 3505048
CompanyItemId = 05001
Notes = LTL 1/1 7484-7915

Any ideas how I can make this work, get quantity and notes?

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2008-07-02 : 11:31:22

As a pure guess, you could puyt parentheses around some of the join clauses to force an order, but it looks liek it should work as is. i would start by looking at the EventGUIDs to see if there is something funky with them in the ProductionEvent table. Perhaps starting by joining ProductionEvent to JobNoteEvent to begin troubleshooting. Not much help, I admit.


FROM            (dbo.Batch
		inner join dbo.event on dbo.event.batchguid = dbo.batch.batchguid
		inner join dbo.job on dbo.job.jobguid = dbo.event.jobguid
		inner join dbo.item on dbo.item.itemguid = dbo.event.itemguid
		inner join dbo.productionevent on dbo.productionevent.eventguid = dbo.event.eventguid)
                left join dbo.JobNoteEvent on dbo.JobNoteEvent.EventGuid = dbo.event.eventguid

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-07-02 : 11:52:53

I tried using parentheses, but that did not help. Any other good ideas? When looping through the "quantity" select, I just feel that the "dbo.event.eventguid" gets changed and that it does not find a match on the "notes". I do not know how to get around this. I could be wrong however.

I even tried this:

SELECT	        i.eventdate, i.companyjobid, i.companyitemid, n.note, i.quantity 
FROM
(SELECT         dbo.event.eventdate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId,
                dbo.productionevent.quantity   
FROM            dbo.Batch
		inner join dbo.event on dbo.event.batchguid = dbo.batch.batchguid
		inner join dbo.job on dbo.job.jobguid = dbo.event.jobguid
		inner join dbo.item on dbo.item.itemguid = dbo.event.itemguid
		inner join dbo.productionevent on dbo.productionevent.eventguid = dbo.event.eventguid
                                 
WHERE           dbo.Job.CompanyJobId = 3505048 and dbo.Item.CompanyItemId = '05001' and dbo.productionevent.quantity > 0 
	       
GROUP BY        dbo.event.eventdate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId, dbo.productionevent.quantity)i
Inner JOIN
(SELECT         dbo.Job.CompanyJobId, dbo.Item.CompanyItemId, dbo.JobNoteEvent.Note
FROM            dbo.Batch
                inner join dbo.event on dbo.event.batchguid = dbo.batch.batchguid
		inner join dbo.job on dbo.job.jobguid = dbo.event.jobguid
		inner join dbo.item on dbo.item.itemguid = dbo.event.itemguid
		left join dbo.JobNoteEvent on dbo.JobNoteEvent.EventGuid = dbo.event.eventguid
WHERE           dbo.Job.CompanyJobId = 3505048 and dbo.Item.CompanyItemId = '05001'
GROUP BY        dbo.event.eventdate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId, dbo.JobNoteEvent.Note)n
ON              n.CompanyJobId = i.CompanyJobId

Result: I get 2 records (I just want the record that have both qty and notes)

EventDate = 2008-04-07
CompanyJobId = 3505048
CompanyItemId = 05001
Notes = Null
Quantity = 276.78

EventDate = 2008-04-07
CompanyJobId = 3505048
CompanyItemId = 05001
Notes = LTL 1/1 7484-7915
Quantity = 276.78

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-02 : 13:53:13

Try changing inner to left join

SELECT          dbo.event.eventdate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId,
                dbo.JobNoteEvent.Note, dbo.productionevent.quantity   
		
FROM            dbo.Batch
		inner join dbo.event on dbo.event.batchguid = dbo.batch.batchguid
		inner join dbo.job on dbo.job.jobguid = dbo.event.jobguid
		inner join dbo.item on dbo.item.itemguid = dbo.event.itemguid
		left join dbo.productionevent on dbo.productionevent.eventguid = dbo.event.eventguid
                left join dbo.JobNoteEvent on dbo.JobNoteEvent.EventGuid = dbo.event.eventguid
                
WHERE          dbo.Job.CompanyJobId = 3505048 and dbo.Item.CompanyItemId = '05001' 
	       
ORDER BY       dbo.event.eventdat

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-07-02 : 15:21:50

Tried above and got 2 records:

EveventDate: 2008-04-07
CompanyJobId: 3505048
CompanyItemId: 05001
Note: LTL 1/1 7484-7915
Quamtity: NULL

EvenetDate: 2008-04-07
CompanyJobId: 3505048
CompanyItemId: 05001
Notes: NULL
Quantity: 276.78

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2008-07-02 : 16:11:35

Are the datatypes of all the GUIDs the same? We aren't chasing some sort of implicit conversion here, are we? Line up the rows for both columns from sp_help tablename, and see what is different. 2005 can be awfully picky in some cases.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-07-02 : 16:30:13

File layout

JOB - one job per record

JobGuid uniqueidentifier
CompanyJobId nvarchar

ITEM - multiple items within a job

ItemGuid uniqueidentifier
CompanyItemId nvarchar
JobGuid uniqueidentifier

PRODUCTIONEVENT - multiple records within an item

ProductionEventGuid uniqueidentifier
EventGuid uniqueidentifier
Quantity numeric

EVENT - identifier for batch, job, item and jobnotevent

EventGuid uniqueidentifier
BatchGuid uniqueidentifier
EventTypeGuid uniqueidentifier
JobGuid uniqueidentifier
ItemGuid uniqueidentifier
EventDate datetime

BATCH

BatchGuid uniqueidentifier

JOBNOTEEVENT - one record per event

JobNoteEventGuid uniqueidentifier
EventGuid uniqueidentifier
Note nvarchar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-03 : 02:42:39

[code]SELECT dbo.event.eventdate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId,
MAX(dbo.JobNoteEvent.Note) AS Note, MAX(dbo.productionevent.quantity) AS quantity

FROM dbo.Batch
inner join dbo.event on dbo.event.batchguid = dbo.batch.batchguid
inner join dbo.job on dbo.job.jobguid = dbo.event.jobguid
inner join dbo.item on dbo.item.itemguid = dbo.event.itemguid
left join dbo.productionevent on dbo.productionevent.eventguid = dbo.event.eventguid
left join dbo.JobNoteEvent on dbo.JobNoteEvent.EventGuid = dbo.event.eventguid

WHERE dbo.Job.CompanyJobId = 3505048 and dbo.Item.CompanyItemId = '05001'
GROUP BY dbo.event.eventdate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId
ORDER BY dbo.event.eventdate[/code]

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-07-03 : 14:46:07

Initially it looked great, then I found out that using max will not work as there could be multiple records for the same date.

Example:
On date: 2008-04-24 there are two records: One record with quantity = 21.46 and one record with quantity = 45.37. What I get with the query is the record with quantity = 45.37 (= max).

So if I run like this:

SELECT          dbo.event.eventdate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId,
                dbo.JobNoteEvent.Note, dbo.productionevent.quantity 
		
FROM            dbo.Batch
		inner join dbo.event on dbo.event.batchguid = dbo.batch.batchguid
		inner join dbo.job on dbo.job.jobguid = dbo.event.jobguid
		inner join dbo.item on dbo.item.itemguid = dbo.event.itemguid
		left join dbo.productionevent on dbo.productionevent.eventguid = dbo.event.eventguid
                left join dbo.JobNoteEvent on dbo.JobNoteEvent.EventGuid = dbo.event.eventguid
                
WHERE          dbo.Job.CompanyJobId = 3505048 and dbo.Item.CompanyItemId = '05001' 
ORDER BY       dbo.event.eventdate

I get 2 separate records one for note and one for quantity (note data is correct and qty data is correct):

2008-04-09 3505048 05001 1.5" sidestreet 1/2 NULL
2008-04-09 3505048 05001 NULL 77.86

Any Ideas?

Any ideas? Do I need to create a #temp table with the results of the quantity and then join it to the other tables for the notes query? I have never done this before so I'm not sure if I have it correct:

Drop Table #JobListTable
CREATE TABLE	#JobListTable

		(
			job_guid uniqueidentifier,
			job_date datetime,
			job_number char(15),
			cost_code char(15),
			qty decimal(8,2)
			                  		)

INSERT INTO     #JobListTable (job_date, job_number, cost_code, qty)
SELECT          dbo.event.eventdate as job_date, dbo.Job.CompanyJobId as job_number, dbo.Item.CompanyItemId as cost_code,
                dbo.productionevent.quantity as qty
FROM            dbo.Batch
		inner join dbo.event on dbo.event.batchguid = dbo.batch.batchguid
		inner join dbo.job on dbo.job.jobguid = dbo.event.jobguid
		inner join dbo.item on dbo.item.itemguid = dbo.event.itemguid
		left join dbo.productionevent on dbo.productionevent.eventguid = dbo.event.eventguid
WHERE          dbo.Job.CompanyJobId = 3505048 and dbo.Item.CompanyItemId = '05001' 
GROUP BY       dbo.event.eventdate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId, dbo.productionevent.quantity       
ORDER BY       dbo.event.eventdate


SELECT          job_date, job_number, cost_code, qty, dbo.JobNoteEvent.Note
FROM            #JobListTable
		inner join dbo.event on dbo.event.JobGuid = #JobListTable.job_guid
		inner join dbo.item on dbo.item.itemguid = dbo.event.itemguid
		left join dbo.JobNoteEvent on dbo.JobNoteEvent.EventGuid = dbo.event.eventguid
WHERE          job_date= 3505048 and cost_code = '05001' 
ORDER BY       job_date

My last select produces an error:

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-03 : 16:51:40

quote:

Originally posted by snufse

Initially it looked great, then I found out that using max will not work as there could be multiple records for the same date.

Example:
On date: 2008-04-24 there are two records: One record with quantity = 21.46 and one record with quantity = 45.37. What I get with the query is the record with quantity = 45.37 (= max).

So if I run like this:

SELECT          dbo.event.eventdate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId,
                dbo.JobNoteEvent.Note, dbo.productionevent.quantity 
		
FROM            dbo.Batch
		inner join dbo.event on dbo.event.batchguid = dbo.batch.batchguid
		inner join dbo.job on dbo.job.jobguid = dbo.event.jobguid
		inner join dbo.item on dbo.item.itemguid = dbo.event.itemguid
		left join dbo.productionevent on dbo.productionevent.eventguid = dbo.event.eventguid
                left join dbo.JobNoteEvent on dbo.JobNoteEvent.EventGuid = dbo.event.eventguid
                
WHERE          dbo.Job.CompanyJobId = 3505048 and dbo.Item.CompanyItemId = '05001' 
ORDER BY       dbo.event.eventdate

Drop Table #JobListTable
CREATE TABLE	#JobListTable

		(
			job_guid uniqueidentifier,
			job_date datetime,
			job_number char(15),
			cost_code char(15),
			qty decimal(8,2)
			                  		)

INSERT INTO     #JobListTable (job_date, job_number, cost_code, qty)
SELECT          dbo.event.eventdate as job_date, dbo.Job.CompanyJobId as job_number, dbo.Item.CompanyItemId as cost_code,
                dbo.productionevent.quantity as qty
FROM            dbo.Batch
		inner join dbo.event on dbo.event.batchguid = dbo.batch.batchguid
		inner join dbo.job on dbo.job.jobguid = dbo.event.jobguid
		inner join dbo.item on dbo.item.itemguid = dbo.event.itemguid
		left join dbo.productionevent on dbo.productionevent.eventguid = dbo.event.eventguid
WHERE          dbo.Job.CompanyJobId = 3505048 and dbo.Item.CompanyItemId = '05001' 
GROUP BY       dbo.event.eventdate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId, dbo.productionevent.quantity       
ORDER BY       dbo.event.eventdate


SELECT          job_date, job_number, cost_code, qty, dbo.JobNoteEvent.Note
FROM            #JobListTable
		inner join dbo.event on dbo.event.JobGuid = #JobListTable.job_guid
		inner join dbo.item on dbo.item.itemguid = dbo.event.itemguid
		left join dbo.JobNoteEvent on dbo.JobNoteEvent.EventGuid = dbo.event.eventguid
WHERE          job_date= 3505048 and cost_code = '05001' 
ORDER BY       job_date

My last select produces an error:

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

--> better maybe: CompanyJobId

Greetings Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-07-04 : 12:48:05

I finnaly seem to get it working:

Drop Table #JobListTable
CREATE TABLE	#JobListTable

		(
			batch_guid uniqueidentifier,
			event_guid uniqueidentifier,
                        production_event uniqueidentifier,
			job_guid uniqueidentifier,
			item_guid uniqueidentifier,
			job_date datetime,
			job_number char(15),
			cost_code char(15),
			qty decimal(8,2)
			                  		)

INSERT INTO     #JobListTable (batch_guid, event_guid, job_guid, item_guid, production_event, 
                job_date, job_number, cost_code, qty)
SELECT          dbo.event.batchguid as batch_guid, 
                dbo.event.eventguid as event_guid, 
		dbo.job.jobguid as job_guid,
		dbo.item.itemguid as item_guid,
                dbo.productionevent.productioneventguid as production_event, 
                dbo.event.eventdate as job_date, 
                dbo.Job.CompanyJobId as job_number, 
                dbo.Item.CompanyItemId as cost_code,
                dbo.productionevent.quantity as qty
FROM            dbo.Batch
		inner join dbo.event on dbo.event.batchguid = dbo.batch.batchguid
		inner join dbo.job on dbo.job.jobguid = dbo.event.jobguid
		inner join dbo.item on dbo.item.itemguid = dbo.event.itemguid
		left join dbo.productionevent on dbo.productionevent.eventguid = dbo.event.eventguid
WHERE          dbo.Job.CompanyJobId = 3505048 and dbo.Item.CompanyItemId = '05001' and dbo.productionevent.quantity > 0


SELECT          batch_guid, event_guid, job_date, job_number, cost_code, qty, dbo.JobNoteEvent.Note, dbo.jobnoteevent.eventguid

FROM            #JobListTable
		inner join dbo.event on #Joblisttable.event_guid = event.relatedeventguid
		left join dbo.JobNoteevent on dbo.JobNoteEvent.EventGuid = event.eventguid
WHERE          job_number = 3505048 and cost_code = '05001' 
ORDER BY       job_date

Thank you all that helped me out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources