Click here to Skip to main content
15,893,487 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
I need to join data from 2 table and I need to optimize the performance.
I want to join orders with working steps.
SQL
Table 1 - Orders
Barcode, OrderData, etc
184512, 20160412, ...
184752, 20160412, ...
184764, 20160413, ...

Table 2 - Working Steps (idWorkingStep from 1 to 5)
Barcode, Data, idWorkingStep
184512, 20160413, 1
184512, 20160413, 2
184512, 20160413, 4
184512, 20160413, 5
184764, 20160413, 2
184764, 20160414, 3
184752, 20160414, 4

Needed result
SQL
Barcode, count(idWorkingStep(1)) ws1, count(idWorkingStep(2)) ws2, count(idWorkingStep(3)) ws3, count(idWorkingStep(4)) ws4, count(idWorkingStep(5)) ws5
184512, 1, 1, 1, 1, 1
184764, 1, 1, 1, 0, 0
184752, 1, 1, 1, 1, 0

All WorkingSteps are required, so if a step is not readed by the scanner and don't appears in the 'table 2' for same reason, I need to fill all the missing information.
As you can see in the result table for then Barcode 184752 i fill with '1' all the steps up to the max(idWorkingStep) from 'Table 2' for that Barcode.


Sometime I use this (script ex.2):
SQL
SELECT BarCode
, CASE ws1
	WHEN 0
	THEN
		CASE ws2
			WHEN 0 
			THEN 
				CASE ws3
					WHEN 0 
					THEN
						CASE ws4
							WHEN 0 THEN ''
							ELSE 1
						END
					ELSE 1
				 END
			ELSE 1
		END
	ELSE 
		CASE ws1
			WHEN 0 THEN ''
			ELSE 1
		END
END ws1
, CASE ws2
	WHEN 0 
	THEN 
		CASE ws3
			WHEN 0 
			THEN
				CASE ws4
					WHEN 0 THEN ''
					ELSE 1
				END
			ELSE 1
			END
	ELSE 1
END ws2
, CASE ws3
	WHEN 0 
	THEN
		CASE ws4
			WHEN 0 THEN ''
			ELSE 1
		END
	ELSE 1
END ws3
, CASE ws4
	WHEN 0 THEN ''
	ELSE 1
END ws4



Tnx for replies

What I have tried:

I actualy use views to group the data and I use:
SELECT Max(v) FROM (VALUES (ws1), (ws2), (ws3), (ws4), (ws5)) AS value(v)) WHERE Barcode = '184512'
All this code is to obtain ws1
For ws2 I use:
SELECT Max(v) FROM (VALUES (ws2), (ws3), (ws4), (ws5)) AS value(v)) WHERE Barcode = '184512'

Probably not the best solution
Posted
Updated 29-Apr-16 5:46am
v3
Comments
Nigam,Ashish 29-Apr-16 2:47am    
Are you joining tables?
RickyReds 29-Apr-16 2:55am    
If I join 'table 1' and 'table 2' the number of rows increase due to Barcode duplicates in 'table 2'.
I have used PIVOT to transpose results, but is not so easy to fill missing data.

1 solution

First step (excuse the pun) is to generate a list of all the possible steps for each barcode. This query will generate a Table variable containing steps 1 to 5 for each of the barcodes in your sample:
SQL
DECLARE @AllSteps TABLE (step int, barcode bigint)
;with CTE as
(
	select 1 as num, barcode from Working_Steps
	UNION ALL
	select CTE.num + 1 , Barcode
	from CTE where num < 5
)
INSERT INTO @AllSteps
	select DISTINCT * from CTE

You can then use that table variable to LEFT OUTER JOIN to table Working_Steps like this
SQL
SELECT O.Barcode, A.step, WS.idWorkingStep
FROM Orders O
INNER JOIN @AllSteps A ON O.Barcode = A.BarCode
LEFT OUTER JOIN Working_Steps WS ON WS.Barcode = O.Barcode AND WS.idWorkingStep=A.step
group by O.Barcode, A.step, WS.idWorkingStep
to get the following results (based on your sample in the question)
barcode step    idWorkingStep
184512	1	1
184512	2	2
184512	3	NULL
184512	4	4
184512	5	5
184752	1	NULL
184752	2	NULL
184752	3	NULL
184752	4	4
184752	5	NULL
184764	1	NULL
184764	2	2
184764	3	3
184764	4	NULL
184764	5	NULL

You can then use a simple PIVOT to get the data into the format you want
SQL
SELECT * FROM
(
	SELECT O.Barcode, A.step, WS.idWorkingStep
	FROM Orders O
	INNER JOIN @AllSteps A ON O.Barcode = A.BarCode
	LEFT OUTER JOIN Working_Steps WS ON WS.Barcode = O.Barcode AND WS.idWorkingStep=A.step
	group by O.Barcode, A.step, WS.idWorkingStep
) AS psource
PIVOT
(
	COUNT(idWorkingStep) FOR step in ([1],[2],[3],[4],[5])
)	AS pvt
Which gave me the following results
barcode 1       2       3       4       5
184512	1	1	0	1	1
184752	0	0	0	1	0
184764	0	1	1	0	0


[EDIT]
OP has clarified the requirement - any gaps in the sequence earlier than the maximum step achieved can be assumed to have been done. This adjustment to the query will achieve that (incorporating @RichardDeeming's suggestion too)
SQL
DECLARE @AllSteps TABLE (step int, barcode bigint)
INSERT INTO @AllSteps
SELECT T.num, S.barcode FROM Working_Steps As S
 CROSS APPLY (VALUES (1), (2), (3), (4), (5)) As T (num)

SELECT Barcode, [1],[2],[3],[4],[5] FROM
(
	SELECT O.Barcode, A.step, T.MaxPer,
	CASE WHEN A.step < T.MaxPer THEN A.step 
		 ELSE NULL END AS idWorkingStep
	FROM Orders O
	INNER JOIN @AllSteps A ON O.Barcode = A.BarCode
	INNER JOIN (SELECT MAX(idWorkingStep) as MaxPer, Barcode FROM Working_Steps GROUP BY Barcode) T ON O.Barcode=T.Barcode
	LEFT OUTER JOIN Working_Steps WS ON WS.Barcode = O.Barcode AND WS.idWorkingStep=A.step
	group by O.Barcode, A.step, t.MaxPer, WS.idWorkingStep
) AS psource
PIVOT
(
	COUNT(idWorkingStep) FOR step in ([1],[2],[3],[4],[5])
)	AS pvt
ORDER BY Barcode

Which yields the results
barcode 1       2       3       4       5
184512	1	1	1	1	1
184752	1	1	1	1	0
184764	1	1	1	0	0


[EDIT 2]
Given that all steps up to the maximum recorded can be assumed to have been actioned then all we are really interested in is the maximum step recorded per barcode. So there is no need for the PIVOT at all - the same results can be generated by the following:
SQL
DECLARE @MaxSteps TABLE(MaxPer int, barcode bigint)
INSERT INTO @MaxSteps 
SELECT MAX(idWorkingStep) as MaxPer, Barcode FROM Working_Steps GROUP BY Barcode

SELECT O.Barcode,
	[1] = CASE WHEN M.MaxPer >= 1 THEN 1 ELSE 0 END,
	[2] = CASE WHEN M.MaxPer >= 2 THEN 1 ELSE 0 END,
	[3] = CASE WHEN M.MaxPer >= 3 THEN 1 ELSE 0 END,
	[4] = CASE WHEN M.MaxPer >= 4 THEN 1 ELSE 0 END,
	[5] = CASE WHEN M.MaxPer >=5 THEN 1 ELSE 0 END
	FROM Orders O
	LEFT OUTER JOIN @MaxSteps M ON O.Barcode = M.Barcode
 
Share this answer
 
v3
Comments
Richard Deeming 29-Apr-16 11:59am    
Since you only need five steps, I'd be inclined to replace the recursive CTE with a static list of values:

SELECT T.num, S.barcode
FROM Working_Steps As S
CROSS APPLY (VALUES (1), (2), (3), (4), (5)) As T (num)
CHill60 29-Apr-16 12:08pm    
Good point, and fits the OP's brief of "optimize performance" better than my rCTE ... especially as I went ahead and hard-coded the columns in the Pivot anyway :facepalm:
RickyReds 30-Apr-16 9:53am    
Hi guys,
thanks a lot for the replies, but you have probably misunderstud part of my request, for sure due to my bad english or examples.
I don't need to fill 'table 2' with missing steps, I need to fill 'result table' with missing values.
Reading the result table from left to right, I need to fill with '1' all the '0' values till the last one '1' values readed from 'table 2'.
No '0' values allowed but eventually condensed in the right side values of the same barcode line.
1 0 0 0 0 Good
1 1 1 0 0 Good
1 1 1 1 1 Good
0 1 1 0 1 BAD, need to fill both '0' with 1
CHill60 30-Apr-16 13:11pm    
Er...neither of us attempted to fill table. The query I provided and Richard improved just replaced the nulls with 0s. You're saying "no 0 values allowed" but the expected results in both your question and the comment above contain zeroes
RickyReds 1-May-16 11:54am    
OkLet's me explain the production flow of our items:
1) Item is injected (printed)
2) Item is cutted (cnc)
3) Item is polished
4) Item is packed
5) Item is shipped

Let's look at 'Table 2'

Example 1 (barcode 184512)
Item with barcode 184512 was not fired (the barcode reader doesn't read it), but I now that the items passed
throw the WorkingStep 3, because passed throw ws 4 and ws 5.
When I ask to server the working steps passed by Barcode 184512 I need that response:

ws1, sw2, ws3, ws4, ws5
1 1 1 1 1

Example 2 (barcode 184764)
It seems that this item wasn't printed yet, no row in the 'Table 2' with idWorkingStep = 1.
But we know that it was cutted and polished, so we can speculate that reader fail to read the first step.
When I ask to server the working steps passed by Barcode 184764 I need that response:

ws1, sw2, ws3, ws4, ws5
1 1 1 0 0

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900