SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Other SQL Server Topics (2005)
 select based on item in another table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Zoomer36
Starting Member

USA
13 Posts

Posted - 03/10/2014 :  12:54:49  Show Profile  Reply with Quote
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!

Edited by - Zoomer36 on 03/10/2014 12:56:52

Zoomer36
Starting Member

USA
13 Posts

Posted - 03/10/2014 :  16:33:40  Show Profile  Reply with Quote
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!

Edited by - Zoomer36 on 03/10/2014 16:34:35
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000