[Resolved] SQL Select Problem - 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

[Resolved] SQL Select Problem

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-02-01 : 09:46:51

I have a sp that selects from a table. The table has a MA record (received qty) type and a PR record (used qty) type.

Below is code that produces an extract and gives 2 records, one for MA (received qty) and one for PR (used qty).

SELECT              dbo.Batch.ReportDate AS job_date, dbo.Job.CompanyJobId AS job_number, dbo.Item.CompanyItemId AS cost_code, dbo.Product.CompanyProductId as product_id, SUBSTRING(dbo.Job.CompanyJobId, 1,3)  as plant_id, dbo.SourceType.CompanySourceTypeId, 
					CASE dbo.SourceType.CompanySourceTypeId
					WHEN 'PR' then SUM(dbo.ProductionEvent.Quantity)
					ELSE SUM(dbo.ProductionEvent.AlternateQuantity)
					END
					AS
					tot_qty
		FROM                 dbo.Batch INNER JOIN
                     			dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
                  			dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
                  			dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN
                    			dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid INNER JOIN
                   			dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN
                  			dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid LEFT OUTER JOIN
                   			dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
		WHERE             dbo.SourceType.CompanySourceTypeId = 'PR'
                                                   	  	and substring(dbo.Product.CompanyProductId, 1, 4) = 'ASPH'
                                                    	 	and dbo.Item.UnitOfMeasure = 'TN'
                                                    	      	            	 	
                                                     or  dbo.SourceType.CompanySourceTypeId = 'MA' 
                                                     		and substring(dbo.Product.CompanyProductId, 1, 4) = 'ASPH'
                                                     		and dbo.Item.UnitOfMeasure = 'TN'
                                                		                                                    		
		GROUP BY       dbo.Batch.ReportDate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId, dbo.SourceType.CompanySourceTypeId, dbo.Product.CompanyProductId

Tot Qty
2007-12-17 3006204 05001 ASPH - SP 9.5 C 300 MA 372.25
2007-12-17 3006204 05001 ASPH - SP 9.5 C 300 PR 357.25

Now I want to extract the same data into a temp table (that I have created) and when reading the same records I would like to update either a received qty field or a used qty field depending upon record type MA or PR). The query works but both qty fields are empty. Can anyone see what I am doing wrong?

SELECT              dbo.Batch.ReportDate AS job_date, dbo.Job.CompanyJobId AS job_number, dbo.Item.CompanyItemId AS cost_code, dbo.Product.CompanyProductId as product_id, SUBSTRING(dbo.Job.CompanyJobId, 1,3)  as plant_id, dbo.SourceType.CompanySourceTypeId, 
				 qty_received =
					 CASE dbo.SourceType.CompanySourceTypeId WHEN 'MA' then SUM(dbo.ProductionEvent.Quantity) ELSE 0 END,
				 qty_used = 
					CASE dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN SUM(dbo.ProductionEvent.AlternateQuantity) ELSE 0 END,
 				 qty_wasted = 	
					CASE dbo.SourceType.CompanySourceTypeId 
					WHEN 'MA' then SUM(dbo.ProductionEvent.Quantity) 
					ELSE 0 
					END
					-
					CASE dbo.SourceType.CompanySourceTypeId 
					WHEN 'PR' THEN SUM(dbo.ProductionEvent.AlternateQuantity) 
					ELSE 0 
					END
		FROM                 dbo.Batch INNER JOIN
                     			dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
                  			dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
                  			dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN
                    			dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid INNER JOIN
                   			dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN
                  			dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid LEFT OUTER JOIN
                   			dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
		WHERE             dbo.SourceType.CompanySourceTypeId = 'PR'
                                                   	  	and substring(dbo.Product.CompanyProductId, 1, 4) = 'ASPH'
                                                    	 	and dbo.Item.UnitOfMeasure = 'TN'
                                                    	      	 	
                                                    	 	
                                                     or  dbo.SourceType.CompanySourceTypeId = 'MA' 
                                                     		and substring(dbo.Product.CompanyProductId, 1, 4) = 'ASPH'
                                                     		and dbo.Item.UnitOfMeasure = 'TN'
                                                     		
                                                     		
		GROUP BY       dbo.Batch.ReportDate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId, dbo.SourceType.CompanySourceTypeId, dbo.Product.CompanyProductId

QTY QTY QTY
REC USED WASTE
2007-12-17 3006204 05001 ASPH - SP 9.5 C 300 MA .00 .00 .00
2007-12-17 3006204 05001 ASPH - SP 9.5 C 300 PR .00 .00 .00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-01 : 10:08:07

can you change like this and try:-

SELECT dbo.Batch.ReportDate AS job_date, dbo.Job.CompanyJobId AS job_number, dbo.Item.CompanyItemId AS cost_code, dbo.Product.CompanyProductId as product_id, SUBSTRING(dbo.Job.CompanyJobId, 1,3) as plant_id, dbo.SourceType.CompanySourceTypeId,
qty_received =
SUM(CASE dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.Quantity ELSE 0 END),
qty_used =
SUM(CASE dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.AlternateQuantity ELSE 0 END) ,
qty_wasted =
SUM(CASE dbo.SourceType.CompanySourceTypeId
WHEN 'MA' then dbo.ProductionEvent.Quantity ELSE 0 END)-
SUM(CASE dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.AlternateQuantity ELSE 0 END)
FROM dbo.Batch INNER JOIN
dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN
dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid INNER JOIN
dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN
dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid LEFT OUTER JOIN
dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
WHERE dbo.SourceType.CompanySourceTypeId = 'PR'
and substring(dbo.Product.CompanyProductId, 1, 4) = 'ASPH'
and dbo.Item.UnitOfMeasure = 'TN'

or dbo.SourceType.CompanySourceTypeId = 'MA'
and substring(dbo.Product.CompanyProductId, 1, 4) = 'ASPH'
and dbo.Item.UnitOfMeasure = 'TN'

GROUP BY dbo.Batch.ReportDate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId, dbo.SourceType.CompanySourceTypeId, dbo.Product.CompanyProductId

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-02-01 : 10:22:42

Worked, thank you.....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-01 : 15:40:22

For speedier query, try this WHERE clause

WHERE		dbo.Product.CompanyProductId LIKE 'ASPH%'
		AND dbo.Item.UnitOfMeasure = 'TN'
		AND dbo.SourceType.CompanySourceTypeId IN ('PR', 'MA')

E 12°55'05.25"
N 56°04'39.16"

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-02-01 : 16:40:10

Sorry, still does not work:

Here is my table:

Job Date, Job Number, Cost Code, Quantity, Type Code, Product Id, Tonnage-UOM

Where Type code MA = Received and PR = Used

2007-12-17 3006204 05001 372.25 MA ASPH-SP9.5C TN
2007-12-17 3006204 05001 357.25 PR ASPH-SP9.5C TN

Now I want to populate my temp table:

CREATE TABLE	#EquentialJobListTable
		(
			job_date datetime,
			job_number char(15),
			cost_code char(15),
			qty_received decimal(8,2),
			qty_used decimal(8,2),
			qty_wasted decimal(8,2),
			product_id char(25),
			plant_id char(10)
			                                     		)

This is the code I have:

INSERT INTO     #EquentialJobListTable (job_date, job_number, cost_code, qty_received, qty_used, qty_wasted, product_id, plant_id)
		SELECT              dbo.Batch.ReportDate AS job_date, dbo.Job.CompanyJobId AS job_number, dbo.Item.CompanyItemId AS cost_code, 
				 qty_received = 
					CASE dbo.SourceType.CompanySourceTypeId WHEN 'MA' THEN SUM(dbo.ProductionEvent.AlternateQuantity) ELSE 0 END,
				 qty_used =
					 CASE dbo.SourceType.CompanySourceTypeId WHEN 'PR' then SUM(dbo.ProductionEvent.Quantity) ELSE 0 END,
 				 qty_wasted = 	
					CASE dbo.SourceType.CompanySourceTypeId 
					WHEN 'MA' THEN SUM(dbo.ProductionEvent.AlternateQuantity) 
					ELSE 0 
					END
					-
					CASE dbo.SourceType.CompanySourceTypeId 
					WHEN 'PR' then SUM(dbo.ProductionEvent.Quantity) 
					ELSE 0 
					END,
					dbo.Product.CompanyProductId as product_id, 
					SUBSTRING(dbo.Job.CompanyJobId, 1,3)  as plant_id
		FROM                 dbo.Batch INNER JOIN
                     			dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
                  			dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
                  			dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN
                    			dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid INNER JOIN
                   			dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN
                  			dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid LEFT OUTER JOIN
                   			dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
		WHERE             dbo.SourceType.CompanySourceTypeId = 'PR'
                                                   	  	and substring(dbo.Product.CompanyProductId, 1, 4) = 'ASPH'
                                                    	 	and dbo.Item.UnitOfMeasure = 'TN'
                                                    	 	
                                                    	 	and dbo.ProductionEvent.Quantity  > 0
                                                     or  dbo.SourceType.CompanySourceTypeId = 'MA' 
                                                     		and substring(dbo.Product.CompanyProductId, 1, 4) = 'ASPH'
                                                     		and dbo.Item.UnitOfMeasure = 'TN'
                                                     		
                                                     		and dbo.ProductionEvent.Quantity  > 0
		GROUP BY       dbo.Batch.ReportDate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId, dbo.SourceType.CompanySourceTypeId, dbo.Product.CompanyProductId

The result is that the qty-received is not getting populated

2007-12-17 3006204 5001 .00 357.25 -357.25 ASPH-SP9.5C 300

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-01 : 17:08:15

Ok, I first now spotted the LEFT JOIN.
Try this

SELECT		dbo.Batch.ReportDate AS job_date,
		dbo.Job.CompanyJobId AS job_number,
		dbo.Item.CompanyItemId AS cost_code,
		dbo.Product.CompanyProductId as product_id,
		SUBSTRING(dbo.Job.CompanyJobId, 1, 3) as plant_id,
		dbo.SourceType.CompanySourceTypeId, 
		SUM(CASE dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.Quantity ELSE 0 END) AS qty_received,
		SUM(CASE dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS qty_used,
		SUM(CASE dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.Quantity ELSE 0 END)
		- SUM(CASE dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS qty_wasted,
FROM		dbo.Batch
INNER JOIN	dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid
INNER JOIN	dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid
INNER JOIN	dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid
INNER JOIN	dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid
INNER JOIN	dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid
INNER JOIN	dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid
LEFT JOIN	dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
			and dbo.Product.CompanyProductId LIKE 'ASPH%'
WHERE		dbo.SourceType.CompanySourceTypeId IN ('PR', 'MA')
		and dbo.Item.UnitOfMeasure = 'TN'
GROUP BY	dbo.Batch.ReportDate,
		dbo.Job.CompanyJobId,
		dbo.Item.CompanyItemId,
		dbo.Product.CompanyProductId,
		dbo.SourceType.CompanySourceTypeId

E 12°55'05.25"
N 56°04'39.16"

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-02-04 : 08:18:51

Still does not work:

There are 2 records for 2007/12/17, they are not being selected.

Job Date, Job Number, Cost Code, Quantity, Type Code, Product Id, Tonnage-UOM

Where Type code MA = Received and PR = Used

2007-12-17 3006204 05001 372.25 MA ASPH-SP9.5C TN
2007-12-17 3006204 05001 357.25 PR ASPH-SP9.5C TN

Result from query:

2007-12-17 3006204 05001 ASPH - SP 9.5 C 300 MA .00 .00 .00
2007-12-17 3006204 05001 ASPH - SP 9.5 C 300 PR .00 .00 .00
2007-12-17 3006349 05201 NULL 300 PR .00 .00 .00
2007-12-17 3006349 05201 ASPH - S3 300 MA .00 .00 .00
2007-12-17 3505040 04601 NULL 350 PR .00 .00 .00
2007-12-17 3505040 04901 NULL 350 PR .00 .00 .00

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-02-04 : 09:46:24

Got the query to work:

SELECT     dbo.Batch.ReportDate AS job_date, dbo.Job.CompanyJobId AS job_number, dbo.Item.CompanyItemId AS cost_code,
	   SUM(CASE dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS qty_received,
           SUM(CASE dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS qty_used,
	   	SUM(CASE dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END)
		- SUM(CASE dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS qty_wasted
FROM       dbo.Batch INNER JOIN
                      dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
                      dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
                      dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN
                      dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid INNER JOIN
                      dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN
                      dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid LEFT OUTER JOIN
                      dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
WHERE 		      dbo.SourceType.CompanySourceTypeId IN ('PR', 'MA')and dbo.Product.CompanyProductId LIKE 'ASPH%'and dbo.Item.UnitOfMeasure = 'TN'
GROUP BY 	      dbo.Batch.ReportDate, dbo.Job.CompanyJobId,dbo.Item.CompanyItemId

Result:
2007-12-17 3006204 05001 372.25 357.25 15.00

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-04 : 09:58:18

The LEFT JOIN is not longer used since you have typed dbo.Product.CompanyProductId LIKE 'ASPH%'

Change that LEFT JOIN dbo.Product to INNER JOIN dbo.Product for more speed.

E 12°55'05.25"
N 56°04'39.16"

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-02-04 : 11:40:34

Will do, thank you for pointing that out....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources