Use Duplicate Query Results to Limit 3rd query

[cabbey77]

Hello,

I have created a duplicates query that shows me exactly what it needs to, now I want to use the results of this query to limit another query, and I cannot get it to work: Here is what I have:

SELECT
	kA.KitInventory_ID, kA.call_ID,kA.Kittype_ID, CD.Call_ID, CD.CASE_ID, KT.Kit_No, KI.LotNo, CT.CallType, CD.SurgeryDate,
	HSTA.ShipToAddress_Desc
FROM
	HubInv.KitAllocation KA JOIN
	HubInv.Call_Details CD on CD.Call_ID = KA.Call_ID INNER JOIN
	HubInv.CallType CT on CT.CallType_ID = CD.CallType_ID INNER JOIN
	HubInv.Hospital_ShipTo_Address HSTA ON HSTA.Hosp_Shipto_Address_ID = CD.Hosp_Shipto_Address_ID INNER JOIN
	HubInv.Kit_Types KT ON KT.KitType_ID = KA.KitType_ID LEFT JOIN
	HubInv.KitInventory KI ON KI.KitInventory_ID = KA.KitInventory_ID
WHERE KA.KitInventory_ID IN
		(WITH CTE_ALLOCATED
				(KitAllocation_ID, KitInventory_ID, RowNumber)
			AS
			(
			SELECT
				KitAllocation_ID, KitInventory_ID, ROW_NUMBER() OVER (PARTITION BY KitInventory_ID ORDER BY KitAllocation_ID) AS 'ROW_NUMBER'  FROM [HubInv].[KitAllocation] 
			   )
			SELECT B.KitAllocation_ID, B.KitInventory_ID, A.KitAllocation_ID AS 'Duplicate Of'
			FROM CTE_Allocated B
			Inner join CTE_Allocated A
			ON B.KitInventory_ID = a.KitInventory_ID
			WHERE B.RowNumber > 1
			AND A.RowNumber = 1  )

 

[philhege]

Can you provide the group with DML and sample data? Did you receive any errors? Did you receive any query results?

Just as a first-glance suggestion, your subquery returns more than one field. Have it select only the kitInventory_ID.

-----------
With business clients like mine, you'd be better off herding cats.

 

[cabbey77]

Yes, it does, as the KitAllocation ID is the unique Identifier, I need this in order to a: calculate a date difference, b: to tie in the other required fields:

The result of this:

WITH CTE_ALLOCATED
				(KitAllocation_ID, KitInventory_ID, RowNumber)
			AS
			(
			SELECT
				KitAllocation_ID, KitInventory_ID, ROW_NUMBER() OVER (PARTITION BY KitInventory_ID ORDER BY KitAllocation_ID) AS 'ROW_NUMBER'  FROM [HubInv].[KitAllocation] 
			   )
			SELECT B.KitAllocation_ID, B.KitInventory_ID, A.KitAllocation_ID AS 'Duplicate Of'
			FROM CTE_Allocated B
			Inner join CTE_Allocated A
			ON B.KitInventory_ID = a.KitInventory_ID
			WHERE B.RowNumber > 1
			AND A.RowNumber = 1 

IS

KitAllocation_ID	KitInventory_ID	Duplicate Of
996	33	615
2542	33	615
3665	33	615
9075	33	615
9767	34	1229
10182	34	1229
2952	1216	332
3612	1216	332
7452	1216	332
3373	1232	1524
7364	1232	1524
7673	1241	3271
1643	1269	593
4053	1269	593
7343	1269	593
7767	1269	593
7467	1273	1717
9392	1277	2946
2959	1282	2561
5830	1284	1686
4259	1286	188
8989	1289	3801
3383	1291	872
5357	1291	872
7120	1291	872
8221	1298	3071
3905	1302	899
1960	1308	762
7438	1308	762
9922	1310	304
7698	1313	1912
8693	1315	4201
1691	1317	1214
1915	1317	1214
3613	1317	1214
5535	1317	1214
5703	1317	1214
5874	1317	1214
7454	1317	1214
9237	1317	1214
9239	1323	338
7512	1326	340
4260	1329	805
6201	1332	3819
3013	1334	400
5338	1334	400
3810	1335	3065
6197	1335	3065
1166	1336	166
1282	1336	166
3423	1336	166
3394	1350	758
5398	1350	758
9141	1350	758
7410	1373	500
2951	1375	1674
9469	9009	9466
9328	9033	9327
9329	9033	9327

 

[gmmastros]

Not tested, but please try this:

WITH CTE_ALLOCATED(KitAllocation_ID, KitInventory_ID, RowNumber) AS
(
	SELECT
		KitAllocation_ID, 
		KitInventory_ID, 
		ROW_NUMBER() OVER (PARTITION BY KitInventory_ID ORDER BY KitAllocation_ID) AS 'ROW_NUMBER'  
	FROM [HubInv].[KitAllocation] 
), Duplicates As
(
  SELECT B.KitAllocation_ID, B.KitInventory_ID, A.KitAllocation_ID AS 'Duplicate Of'
  FROM   CTE_Allocated B
         Inner join CTE_Allocated A
           ON B.KitInventory_ID = a.KitInventory_ID
  WHERE  B.RowNumber > 1
         AND A.RowNumber = 1  
)
SELECT
	kA.KitInventory_ID, kA.call_ID,kA.Kittype_ID, CD.Call_ID, CD.CASE_ID, KT.Kit_No, KI.LotNo, CT.CallType, CD.SurgeryDate,
	HSTA.ShipToAddress_Desc
FROM
	HubInv.KitAllocation KA JOIN
	HubInv.Call_Details CD on CD.Call_ID = KA.Call_ID INNER JOIN
	HubInv.CallType CT on CT.CallType_ID = CD.CallType_ID INNER JOIN
	HubInv.Hospital_ShipTo_Address HSTA ON HSTA.Hosp_Shipto_Address_ID = CD.Hosp_Shipto_Address_ID INNER JOIN
	HubInv.Kit_Types KT ON KT.KitType_ID = KA.KitType_ID LEFT JOIN
	HubInv.KitInventory KI ON KI.KitInventory_ID = KA.KitInventory_ID
      INNER JOIN Duplicates
	    On KA.KitInventory_ID = Duplicates.KitInventory_ID

If this works, I'll explain what I did an why.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cabbey77]

Hi,

Closer, but there are duplicates, here are the top 20 results. I added KitAllocation_ID as that is the unique field in the KA Table.

KitAllocation_ID KitInventory_ID call_ID Kittype_ID Call_ID CASE_ID Kit_No LotNo CallType SurgeryDate ShipToAddress_Desc
996 33 7708 77 7708 20140508054 5886KITA/1 106 New Surgery 5/20/2014 OWATONNA HOSPITAL
2542 33 6633 77 6633 20140430044 5886KITA/1 106 New Surgery 5/27/2014 OWATONNA HOSPITAL
2542 33 6633 77 6633 20140430044 5886KITA/1 106 New Surgery 5/27/2014 OWATONNA HOSPITAL
615 33 8418 77 8418 20140513141 5886KITA/1 106 New Surgery 5/15/2014 OLMSTED MEDICAL CTR
615 33 8418 77 8418 20140513141 5886KITA/1 106 New Surgery 5/15/2014 OLMSTED MEDICAL CTR
996 33 7708 77 7708 20140508054 5886KITA/1 106 New Surgery 5/20/2014 OWATONNA HOSPITAL
360 56 8058 76 8058 20140512070 5885KITA/1 21 New Surgery 5/15/2014 KENNEDY MEM HSP/UNIV MED
2238 56 10183 76 10183 20140520031 5885KITA/1 21 New Surgery 5/21/2014 POTTSTOWN MEM MEDICAL CENTER
2167 74 10024 37 10024 20140519043 4150KIT1 21 New Surgery 5/21/2014 MEMORIAL HOSPITAL
583 74 8249 37 8249 20140512261 4150KIT1 21 New Surgery 5/15/2014 MEDICAL CENTER OF THE ROCKIES
2091 100 10119 4 10119 20140519138 1526KIT3 A102348 New Surgery 5/21/2014 GOOD SAMARITAN HOSPITAL
15 100 4747 4 4747 20140411101 1526KIT3 A102348 New Surgery 5/16/2014 AMQ-ST MARY'S HOSP PASSAIC
59 109 8039 73 8039 20140512051 5361KITA/1 31 New Surgery 5/13/2014 ALBERT EINSTEIN MEDICAL CENTER
1326 109 8753 73 8753 20140515111 5361KITA/1 31 New Surgery 5/20/2014 ALBERT EINSTEIN MEDICAL CENTER
2027 111 8747 75 8747 20140515105 5866KITA/1 185 New Surgery 5/22/2014 CENTER FOR ADVANCED SURGERY
28 111 7815 75 7815 20140508161 5866KITA/1 185 New Surgery 5/16/2014 ROCKLAND AND BERGEN SURG CTR
2260 112 8025 76 8025 20140512037 5885KITA/1 13 New Surgery 5/22/2014 GOOD SAMARITAN HOSPITAL
218 112 8113 76 8113 20140512125 5885KITA/1 13 New Surgery 5/14/2014 MANHATTAN EYE EAR AND THROAT
1458 154 8299 74 8299 20140513022 5601KITA/1 18 New Surgery 5/21/2014 MAYO FOUNDATION
623 154 8417 74 8417 20140513140 5601KITA/1 18 New Surgery 5/14/2014 MAYO FOUNDATION