Data Source and Table Mapping

[ahawes]

Good morning Team,

I am using a Cognex camera to 'count' cases coming from our production floor. The production floor receives items from 5 different packing locations.

I have been asked to segregate the SKUs by pack station.

Since the data is coming from a scanner, I am unsure how to solve this.

I thought perhaps mapping, but that will not work.

SKU Counts Pack Station
1376 177 1
1952 162 2
1378 152 2

[Transistor]

@ahawes

I have been asked to segregate the SKUs by pack station.

What does that mean?

1. Please give an example of the desired output. Either use the </> button to apply fixed with font so your columns line up or use the editor's built-in table generator.
2. Post the SQL query as code. See Wiki - how to post code on this forum. That way we won't have to type it all out again in our answers.

[ahawes]

One other note. The cognex camera is only capturing the SKU and counts incrementally.

[ahawes]

My appologies. This is an excel export of the data that is captured. I would like to have a 3rd column, Pack Station, that identifieds the pack station from which the SKU comes from.

SELECT ProductCode AS SKU, count (ProductCode) AS Counts
FROM Cut_ProductCountScanner
WHERE t_stamp >= :StartDate
      and t_stamp <= :EndDate
       and ShiftProduced = 1
GROUP BY ProductCode
ORDER BY count (ProductCode) desc

SKU	Counts	Pack Station
1952	233	2
1376	227	1
1955	196	1
1715	195	2
1926	159	3

[Transistor]

SELECT 
    ProductCode AS SKU, 
    COUNT(ProductCode) AS Counts,
    Station
FROM Cut_ProductCountScanner
WHERE t_stamp >= :StartDate
  AND t_stamp <= :EndDate
  AND ShiftProduced = 1
GROUP BY ProductCode
ORDER BY 
    Station,
    COUNT(ProductCode) DESC

Watch your ≥ and ≤ in the WHERE clause. Usually you want ≥ and < so that you don't show the start of the next shift on today's report and tomorrow's as well (if you happened to get a reading right on the shift-change).

[ahawes]

@Transistor

Cut_ProductCountScanner
WHERE t_stamp >= :StartDate
  AND t_stamp <= :EndDate
  AND ShiftProduced = 1
GROUP BY ProductCode
ORDER BY 
    Station,

My issue is that the data is coming from a camera that provides the SKU and count.

There is no pack station associated to the data source.

[Transistor]

Show how you are capturing the data from the cameras and inserting it into the Cut_ProductCountScanner table.

[ahawes]

I am not sure if this is what you need.

The camera captures just the SKU number. We capture the data in our db.

SELECT TOP (1000) [ID]
      ,[ProductCode]
      ,[ScannerCount]
      ,[ShiftProduced]
      ,[t_stamp]
  FROM [Ignitioncw].[dbo].[Cut_ProductCountScanner]

[Transistor]

You're showing how you're getting data out of the database. We need to see how you're putting it into the database.

You are going to need to add a column for Station but we need the other information first.

[ahawes]

@Transistor

g how you're getting data out of the database. We need to see how you're putting it into the database.

You are going to need to add a column for Station but we need the other information first.

Ok, that helps. I will get that information and come back.

Thanks!!

[JordanCClark]

Some thoughts:

• If it's always the same SKUs on the same packers, I'd make a separate table for that and do a join.
• If a single SKU can run on multiple packers, you can update the SKU-to-packer packer table, depending on which packer is running it.
• If they run the same SKU on multiple packers at the same time, you're rather hosed with your current setup. You would need to capture it at the packer.