# Data Source and Table Mapping

**URL:** <https://forum.inductiveautomation.com/t/data-source-and-table-mapping/90092>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [May 8, 2024, 11:35am UTC](https://forum.inductiveautomation.com/t/data-source-and-table-mapping/90092 "2024-05-08T11:35:16Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![ahawes](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahawes/32/36337_2.png) [@ahawes](https://forum.inductiveautomation.com/u/ahawes)\
**Post date:** [May 8, 2024, 11:35am UTC](https://forum.inductiveautomation.com/t/data-source-and-table-mapping/90092/1 "2024-05-08T11:35:16Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/2/2210619cb6140e5f2b57fc944be9220a9d52e7f5.png)

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/f/8f29bd79f9dcecb4608d5da3afd88b6271d78da6.png)

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [May 8, 2024, 11:47am UTC](https://forum.inductiveautomation.com/t/data-source-and-table-mapping/90092/2 "2024-05-08T11:47:23Z")

</div>

> [@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](https://forum.inductiveautomation.com/t/wiki-how-to-post-code-on-this-forum/77831). That way we won't have to type it all out again in our answers.

---

<div class="post-metadata">

**Author:** ![ahawes](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahawes/32/36337_2.png) [@ahawes](https://forum.inductiveautomation.com/u/ahawes)\
**Post date:** [May 8, 2024, 11:47am UTC](https://forum.inductiveautomation.com/t/data-source-and-table-mapping/90092/3 "2024-05-08T11:47:41Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ahawes](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahawes/32/36337_2.png) [@ahawes](https://forum.inductiveautomation.com/u/ahawes)\
**Post date:** [May 8, 2024, 11:50am UTC](https://forum.inductiveautomation.com/t/data-source-and-table-mapping/90092/4 "2024-05-08T11:50:49Z")

</div>

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.

```python
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

```

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

```

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [May 8, 2024, 11:57am UTC](https://forum.inductiveautomation.com/t/data-source-and-table-mapping/90092/5 "2024-05-08T11:57:07Z")

</div>

```sql
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).

---

<div class="post-metadata">

**Author:** ![ahawes](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahawes/32/36337_2.png) [@ahawes](https://forum.inductiveautomation.com/u/ahawes)\
**Post date:** [May 8, 2024, 12:00pm UTC](https://forum.inductiveautomation.com/t/data-source-and-table-mapping/90092/6 "2024-05-08T12:00:43Z")

</div>

> [@Transistor](#):
>
> ```python
> 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.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [May 8, 2024, 12:03pm UTC](https://forum.inductiveautomation.com/t/data-source-and-table-mapping/90092/7 "2024-05-08T12:03:07Z")

</div>

Show how you are capturing the data from the cameras and inserting it into the Cut\_ProductCountScanner table.

---

<div class="post-metadata">

**Author:** ![ahawes](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahawes/32/36337_2.png) [@ahawes](https://forum.inductiveautomation.com/u/ahawes)\
**Post date:** [May 8, 2024, 12:33pm UTC](https://forum.inductiveautomation.com/t/data-source-and-table-mapping/90092/8 "2024-05-08T12:33:46Z")

</div>

I am not sure if this is what you need.

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

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

```

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/e/7e65a4f527a0997c275349189cd3b22b03db75a0.png)

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/8/88ba5146234dd0bbcbe434812c58ce49be0c192f.jpeg)

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [May 8, 2024, 12:36pm UTC](https://forum.inductiveautomation.com/t/data-source-and-table-mapping/90092/9 "2024-05-08T12:36:49Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ahawes](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ahawes/32/36337_2.png) [@ahawes](https://forum.inductiveautomation.com/u/ahawes)\
**Post date:** [May 8, 2024, 1:09pm UTC](https://forum.inductiveautomation.com/t/data-source-and-table-mapping/90092/10 "2024-05-08T13:09:41Z")

</div>

> [@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!!

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [May 9, 2024, 12:31pm UTC](https://forum.inductiveautomation.com/t/data-source-and-table-mapping/90092/11 "2024-05-09T12:31:37Z")

</div>

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.