How to compare rows from two datatables

Question

0.00/5 (No votes)

See more:

Below are two datatables i get in VB.NET - each has 100+ rows. I would like to compare date in columns Qty1 and Qty2 for each rows.

If Qty2 (19.5.2015) > Qty1 (5.5.2015) then send user email, else (Qty2 < Qty1) do nothing. How can i do that?

TABLE 1
  MA		       Qty1
MA31623		19.5.2015 6:17:38
MA11702		11.6.2015 6:01:53
MA05840		22.6.2015 6:16:52
MA05840		22.6.2015 6:16:57
MA05840		22.6.2015 6:17:05
.....           .....

TABLE 2
  MA		       Qty2
MA31623		5.5.2015 0:00:00
MA11702		16.3.2015 0:00:00
MA05840		4.6.2015 0:00:00
MA05840		4.6.2015 0:00:00
MA05840		4.6.2015 0:00:00
.....           .....

I hope it's written clear enough, otherwise i'll provide more info.
Thanks!

Posted 23-Jul-15 1:42am

mrUR

Add a Solution

Comments

[no name] 23-Jul-15 7:54am

This Returns the MA's for which you like to send a mail:

SELECT
TABLE1.ME
FROM TABLE1
LEFT JOIN TABLE2 ON TABLE2.ME = TABLE1.ME
WHERE NOT TABLE2.ME IS NULL AND (TABLE2.QTY2 > TABLE1.QTY1)

... and that is some nice stuff explaining join:
Visual Representation of SQL Joins[^]

mrUR 23-Jul-15 13:06pm

But TABLE1 and TABLE2 are already made of multiple INNER JOIN, what about that case?

This is SQL query for my TABLE1:

SELECT TOP (100) PERCENT [cabtrptp].Microplan3T.dbo.MP_OperationDestination.MaterialShortage AS Manjkajoči_MA, dbo.MP_OperationDestination.LastChanged AS Datum_konca FROM dbo.MP_WorkOrderItem INNER JOIN dbo.MP_OperationSource ON dbo.MP_WorkOrderItem.ID_WorkOrderItem = dbo.MP_OperationSource.ID_WorkOrderItem INNER JOIN dbo.MP_WorkOrder ON dbo.MP_WorkOrderItem.ID_WorkOrder = dbo.MP_WorkOrder.ID_WorkOrder INNER JOIN dbo.MP_OperationDestination ON dbo.MP_OperationSource.ID_OperationSource = dbo.MP_OperationDestination.ID_OperationSource INNER JOIN dbo.MP_Capacity ON dbo.MP_OperationDestination.ID_Capacity = dbo.MP_Capacity.ID_Capacity INNER JOIN dbo.MP_Item ON dbo.MP_WorkOrderItem.ID_Item = dbo.MP_Item.ID_Item WHERE (dbo.MP_OperationDestination.Status = 3) AND (dbo.MP_OperationSource.Active = 1) AND (dbo.MP_OperationDestination.Active = 1) AND (dbo.MP_WorkOrder.Active = 1) AND (NOT (dbo.MP_OperationDestination.MaterialShortage IS NULL)) ORDER BY dbo.MP_OperationDestination.LastChanged

And this is SQL query for TABLE2

SELECT DISTINCT TOP(1) WMSJOURNALTRANS.ITEMID Prevzeti_MA, WMSJOURNALTRANS.TRANSDATE As Datum_prevzema FROM WMSJOURNALTRANS INNER JOIN WMSJOURNALTABLE ON WMSJOURNALTRANS.JOURNALID = WMSJOURNALTABLE.JOURNALID INNER JOIN WMSJOURNALNAME ON WMSJOURNALTRANS.DATAAREAID = WMSJOURNALNAME.DATAAREAID WHERE (WMSJOURNALTRANS.ITEMID = '" + MA + "'" + ") GROUP BY WMSJOURNALTRANS.ITEMID, WMSJOURNALTRANS.TRANSDATE ORDER BY WMSJOURNALTRANS.TRANSDATE DESC

1 solution

Add a Solution

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

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Answer 1 · 2015-07-23T02:01:00

Solution 1

Yes, join both and retrieve the rows where Qty2 is greater than Qty1.

You can do it by SQL Query else with DataTable comparisons.

Posted 23-Jul-15 2:01am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Comments

mrUR 25-Jul-15 13:45pm

But TABLE1 and TABLE2 are already made of multiple INNER JOIN, what about that case? Can i do this in Visual Studio with "for each" or somthing like that??

This is SQL query for my TABLE1:

SELECT TOP (100) PERCENT [cabtrptp].Microplan3T.dbo.MP_OperationDestination.MaterialShortage AS Manjkajoči_MA, dbo.MP_OperationDestination.LastChanged AS Datum_konca FROM dbo.MP_WorkOrderItem INNER JOIN dbo.MP_OperationSource ON dbo.MP_WorkOrderItem.ID_WorkOrderItem = dbo.MP_OperationSource.ID_WorkOrderItem INNER JOIN dbo.MP_WorkOrder ON dbo.MP_WorkOrderItem.ID_WorkOrder = dbo.MP_WorkOrder.ID_WorkOrder INNER JOIN dbo.MP_OperationDestination ON dbo.MP_OperationSource.ID_OperationSource = dbo.MP_OperationDestination.ID_OperationSource INNER JOIN dbo.MP_Capacity ON dbo.MP_OperationDestination.ID_Capacity = dbo.MP_Capacity.ID_Capacity INNER JOIN dbo.MP_Item ON dbo.MP_WorkOrderItem.ID_Item = dbo.MP_Item.ID_Item WHERE (dbo.MP_OperationDestination.Status = 3) AND (dbo.MP_OperationSource.Active = 1) AND (dbo.MP_OperationDestination.Active = 1) AND (dbo.MP_WorkOrder.Active = 1) AND (NOT (dbo.MP_OperationDestination.MaterialShortage IS NULL)) ORDER BY dbo.MP_OperationDestination.LastChanged

And this is SQL query for TABLE2

SELECT DISTINCT TOP(1) WMSJOURNALTRANS.ITEMID Prevzeti_MA, WMSJOURNALTRANS.TRANSDATE As Datum_prevzema FROM WMSJOURNALTRANS INNER JOIN WMSJOURNALTABLE ON WMSJOURNALTRANS.JOURNALID = WMSJOURNALTABLE.JOURNALID INNER JOIN WMSJOURNALNAME ON WMSJOURNALTRANS.DATAAREAID = WMSJOURNALNAME.DATAAREAID WHERE (WMSJOURNALTRANS.ITEMID = '" + MA + "'" + ") GROUP BY WMSJOURNALTRANS.ITEMID, WMSJOURNALTRANS.TRANSDATE ORDER BY WMSJOURNALTRANS.TRANSDATE DESC

How to compare rows from two datatables

1 solution

Solution 1

Add your solution here

Preview 0

How to compare rows from two datatables

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us