select based on item in another table

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

Other SQL Server Topics (2005)

select based on item in another table

Author

Topic

Zoomer36
Starting Member

13 Posts

Posted - 2014-03-10 : 12:54:49

I need to count a distinct tile where it meets certain criteria. However, if one of the items in that tile is a different category, then I should not count the tile. I tried using distinct but I still get the count as each tile has 2-5 lines in the database. I join two tables and end up with a table Main that has each Tile, Batch, Order Number, Item Number and quantity. There are other columns but they don't matter.

This is data from my table:

Work_Date_Time Badge_ID Employee_ID Employee_Name Shift Cost Op_Code Op_Code_Description Clock_Code PV_Number Process_Type Batch_Hdr Quantity Media Tile_Length Print_Count Tile_Header Order_Number Detail_Line_Seq PZ_Seq Item_Number Personalization_Type PZ_String Pers_Batch_ID Batch_Seq Order_Qty
3/6/14 8:37 AM 161785 161785 Smith, John 1 0 FP LFP Print 46 0 LB LB405868S 1 LB-Banner 75 1 0438257TL 662293688 4 1 BANNER/YS LBN *** LB405868S 1 1
3/6/14 8:37 AM 161785 161785 Smith, John 1 3.16 FP LFP Print 46 0 LB LB405868S 1 LB-Banner 75 1 0438257TL 662293688 2 1 13634843 LBN Saturday, May 3rd, 2014 LB405868S 2 1
3/6/14 8:37 AM 161785 161785 Smith, John 1 0 FP LFP Print 46 0 LB LB405868S 1 LB-Banner 75 1 0438257TL 662293688 3 1 42/1176 LBN *** LB405868S 3 1
3/6/14 8:37 AM 161785 161785 Smith, John 1 0 FP LFP Print 46 0 LB LB406100L 1 LB-Banner 21 1 0437165TL 662325699 5 1 BANNER/BD LBN *** LB406100L 1 1
3/6/14 8:37 AM 161785 161785 Smith, John 1 9.6 FP LFP Print 46 0 LB LB406100L 1 LB-Banner 21 1 0437165TL 662325699 3 1 13628586 LBN RIHA LB406100L 2 1
3/6/14 8:37 AM 161785 161785 Smith, John 1 9.6 FP LFP Print 46 16 LB LB406100L 1 LB-Banner 21 1 0437165TL 662325699 3 1 13628586 LBN 19-Apr-14 LB406100L 2 1
3/6/14 8:37 AM 161785 161785 Smith, John 1 0 FP LFP Print 46 0 LB LB406100L 1 LB-Banner 21 1 0437165TL 662325699 4 1 42/1475 LBN *** LB406100L 3 1
3/6/14 8:37 AM 161785 161785 Smith, John 1 0 FP LFP Print 46 0 LB LB406100R 1 LB-Banner 5 1 0337481TL 662319655 4 1 BANNER/TR LBN *** LB406100R 1 1
3/6/14 8:37 AM 161785 161785 Smith, John 1 9.55 FP LFP Print 46 0 LB LB406100R 1 LB-Banner 5 1 0337481TL 662319655 3 1 13632829 LBN JUST DANCE INC LB406100R 2 1
3/6/14 8:37 AM 161785 161785 Smith, John 1 9.55 FP LFP Print 46 15 LB LB406100R 1 LB-Banner 5 1 0337481TL 662319655 3 1 13632829 LBN PBG, 561-691-9641 LB406100R 2 1

The problem I have is that Items 13628586 in Tile 0437165TL and 13632829 in Tile 0337481TL disqualify the entire tile. So the only Tile I want to count is 0438257TL.

This is the code I have now and it checks the other table for items that are not counted.

SELECT 
	WORK_DATE, 
	SHIFT, 
	EMPLOYEE_ID, 
	Op_Code, 
	Clock_Code, 
	PV_Number = '11',
	REPLICATE('0',6-LEN(CAST((Count(DISTINCT Tile_Header)*Print_Count) AS VARCHAR(6)))) 
					  + CAST((Count(DISTINCT Tile_Header)*Print_Count) AS VARCHAR(6)) 
	AS PV_Count,
	'000' AS Not_Used,
	'usp_XP_PZ_Data_Capture_Final_Export',
	@@SPID,
	GETDATE(),
	suser_sname(),
	'usp_XP_PZ_Data_Capture_Final_Export'
FROM SupplyChain.dbo.XP_PZ_Data_Capture_Main
WHERE EMPLOYEE_ID <> '' 
AND SHIFT IN ('01','02','03','99')
AND Op_Code = 'FP'
AND Print_Count > 0
AND LEFT(Batch_Hdr,2) IN ('IB','LB')
AND Disallowed = 'False'
--See if Item is in SKU table to disqualify
AND Item_Number NOT IN (
						SELECT SKU#
						FROM SupplyChain.DBO.XP_PZ_Data_Capture_SKU 
						WHERE Op_Code = 'FP')
GROUP BY  WORK_DATE, EMPLOYEE_ID, SHIFT, OP_CODE, CLOCK_CODE, Print_Count

Any help is greatly appreciated. I have tried differing grouping methods and other queries but can't come up with anything.

Thank you!

Have a great day!

Zoomer36
Starting Member

13 Posts

Posted - 2014-03-10 : 16:33:40

Ok....I solved it myself. In case anyone needs it I did a couple of sub selects and it worked. Probably not the most efficient method but it got the job done.

ELECT 	WORK_DATE,	
		SHIFT,	
		EMPLOYEE_ID,	
		Op_Code,	
		Clock_Code,	
		PV_Number = '11',
		REPLICATE('0',6-LEN(CAST((COUNT(DISTINCT Tile_Header)*Print_Count) AS VARCHAR(6)))) 
					  + CAST((COUNT(DISTINCT Tile_Header)*Print_Count) AS VARCHAR(6)) 
		AS PV_Count,
		'000' AS Not_Used,
		'usp_XP_PZ_Data_Capture_Final_Export',
		@@SPID,
		GETDATE(),
		suser_sname(),
		'usp_XP_PZ_Data_Capture_Final_Export'
FROM SupplyChain.dbo.XP_PZ_Data_Capture_Main 
--INNER JOIN SupplyChain.dbo.XP_PZ_Data_Capture_Main B
WHERE EMPLOYEE_ID <> '' 
AND SHIFT IN ('01','02','03','99')
AND Op_Code = 'FP'
AND Print_Count > 0
AND LEFT(Batch_Hdr,2) IN ('IB','LB')
AND Disallowed = 'False'
--See if Item is in SKU table to disqualify
AND Tile_Header NOT IN(Select Tile_Header 
						FROM SupplyChain.DBO.XP_PZ_Data_Capture_Main
						Where Item_Number IN (
											SELECT SKU#
											FROM SupplyChain.DBO.XP_PZ_Data_Capture_SKU 
											WHERE Op_Code = 'FP'
											)
						)
GROUP BY  WORK_DATE, EMPLOYEE_ID, SHIFT, OP_CODE, CLOCK_CODE, Print_Count

Have a great day!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources