Count with conditions in another table and joining records to a third table

Question

0.00/5 (No votes)

See more:

I am counting the records for a specific project Value (cprj) from the table [MatchingRowsOutput-CI-BOM-ProjectCumulative] and cross-referencing the number to a [DWRowCount] value in another table, [OrderEntry], for the same project 'cprj'.

If these values match, I would like to take all the records for that project (cprj) from the [MatchingRowsOutput-CI-BOM-ProjectCumulative] table and output them into another table called [CI-BM-CPRJ-Transfer].

I have tried many different methods, trying to use CASE clause, WHERE clause, HAVING clause but none of these have worked.

The code pasted below is the most successful that I can achieve, returning 0 rows affected. I know for a fact there should be a return of 26 rows for the TEK#7000 cprj.

Also, after I have pulled the records from the [MatchingRowsOutput-CI-BOM-ProjectCumulative] table and placed into the [CI-BM-CPRJ-Transfer] table I would like to delete the records that was used for the reference

out of the [OrderEntry] table. I have not tried to code that part yet in the code below.

Any help is much appreciated.

Here are samples of the 2 tables with the information.

[OrderEntry] Table
ID	cprj		TotalRowCount		DWRowCount
1	TEK#7000	80			26
2	TEK#4444	50			30
3	TEK#88888	120			24
4	TEK#4700	37			13
5	TEK#99999	53			42

[MatchingRowsOutput-CI-BOM-ProjectCumulative] table
ID	cprj		item		PartNumber		SWITEM	QTY	rutm	
196	TEK#7000	A23-3171-X	A23-3171\2583		1.2	2	NULL	
197	TEK#5555	N02-0846-X	N02-0846A5\59\2583	2	1	NULL	
198	TEK#7000	N02-0846-X	N02-0846A5\59\2583	2	1	NULL	
199	TEK#5555	A23-1354-X	A23-1354\2583		2.3	2	NULL	
200	TEK#7000	A23-1354-X	A23-1354\2583		2.3	2	NULL	
201	TEK#7000	B11-0304-X	B11-0304\25		2.4	2	NULL	
202	TEK#7000	B11-0304-X	B11-0304\25		1.4	2	NULL	
203	TEK#5555	B11-0304-X	B11-0304\25		2.4	2	NULL	
204	TEK#5555	B11-0304-X	B11-0304\25		1.4	2	NULL	
205	TEK#5555	B11-0305-X	B11-0305\59		1.5	2	NULL	
206	TEK#5555	B11-0305-X	B11-0305\59		2.5	2	NULL	
207	TEK#7000	B11-0305-X	B11-0305\59		1.5	2	NULL

I could not show all the columns for this 2nd table.

[CI-BM-CPRJ-Transfer] is a new table that the results would be written into.

What I have tried:

SQL

Insertinto SSIS.dbo.[CI-BM-CPRJ-Transfer](cprj, item, PartNumber, SWITEM, QTY, rutm, trid, mitm, pono, sitm, opol, qana, scpf, cwar, opno, cpha, exin, itlu, ssta, dsca, dscb, dscd, suno, ctyo, eitm)

SELECT 
    PartList.[cprj],
    XMLLines.[item],
    XMLLines.[PartNumber],
    XMLLines.[SWITEM],
    XMLLines.[QTY],
    XMLLines.[rutm],
    XMLLines.[trid],
    XMLLines.[mitm],
    XMLLines.[pono],
    XMLLines.[sitm],
    XMLLines.[opol],
    XMLLines.[qana],
    XMLLines.[scpf],
    XMLLines.[cwar],
    XMLLines.[opno],
    XMLLines.[cpha],
    XMLLines.[exin],
    XMLLines.[itlu],
    XMLLines.[ssta],
    XMLLines.[dsca],
    XMLLines.[dscb],
    XMLLines.[dscd],
    XMLLines.[suno],
    XMLLines.[ctyo],
    XMLLines.[eitm]
FROM 
    OrderEntry.dbo.OrderEntry as PartList

INNERJOIN
SSIS.dbo.[MatchingRowsOutput-CI-BOM-ProjectCumulative]AS XMLLines ON 
XMLLines.cprj = PartList.cprj

    (SELECT cprj FROM SSIS.dbo.[MatchingRowsOutput-CI-BOM-ProjectCumulative])GroupBy XMLLines.cprj, PartList.DWRowCount, XMLLines.[item], XMLLines.[PartNumber], XMLLines.[SWITEM], XMLLines.[QTY], XMLLines.[rutm], XMLLines.[trid], XMLLines.[mitm], XMLLines.[pono], XMLLines.[sitm],
XMLLines.[opol], XMLLines.[qana], XMLLines.[scpf], XMLLines.[cwar], XMLLines.[opno], XMLLines.[cpha], XMLLines.[exin], XMLLines.[itlu], XMLLines.[ssta], XMLLines.[dsca], XMLLines.[dscb], XMLLines.[dscd],
XMLLines.[suno], XMLLines.[ctyo], XMLLines.[eitm], PartList.[cprj]


Having count(XMLLines.cprj)= PartList.DWRowCount

Posted 20-Mar-20 7:22am

Member 14778402

Updated 23-Mar-20 4:38am

Maciej Los

v2

Add a Solution

2 solutions

Solution 1

It's quite easy to get count of items by using COUNT (Transact-SQL)[^]

SQL

DECLARE @tmp TABLE (ID	INT, cprj VARCHAR(30), item VARCHAR(30), PartNumber VARCHAR(30), SWITEM VARCHAR(10), QTY INT)

INSERT INTO @tmp (ID, cprj, item, PartNumber, SWITEM, QTY)
VALUES(196, 'TEK#7000', 'A23-3171-X', 'A23-3171\2583', '1.2', 2),
(197, 'TEK#5555', 'N02-0846-X', 'N02-0846A5\59\2583', '2', 1),
(198, 'TEK#7000', 'N02-0846-X', 'N02-0846A5\59\2583', '2', 1),
(199, 'TEK#5555', 'A23-1354-X', 'A23-1354\2583', '2.3', 2),
(200, 'TEK#7000', 'A23-1354-X', 'A23-1354\2583', '2.3', 2),
(201, 'TEK#7000', 'B11-0304-X', 'B11-0304\25', '2.4', 2),
(202, 'TEK#7000', 'B11-0304-X', 'B11-0304\25', '1.4', 2),
(203, 'TEK#5555', 'B11-0304-X', 'B11-0304\25', '2.4', 2),
(204, 'TEK#5555', 'B11-0304-X', 'B11-0304\25', '1.4', 2),
(205, 'TEK#5555', 'B11-0305-X', 'B11-0305\59', '1.5', 2),
(206, 'TEK#5555', 'B11-0305-X', 'B11-0305\59', '2.5', 2),
(207, 'TEK#7000', 'B11-0305-X', 'B11-0305\59', '1.5', 2)

SELECT cprj, COUNT(cprj) CountOfCprj
FROM @tmp
GROUP BY cprj

Based on data have been posted, the result is:

cprj	CountOfCprj
TEK#5555	6
TEK#7000	6

Posted 20-Mar-20 23:55pm

Maciej Los

Comments

Member 14778402 23-Mar-20 9:16am

Thanks Maciej. My big problem I have is using this counter as a condition to extract records from the [MatchingRowsOutput-CI-BOM-ProjectCumulative] table with the condition that the counter matches a value in the column DWRowCount of the [OrderEntry] table and that the cprj value in both tables match. The records are then extracted to a new table [CI-BM-CPRJ-Transfer].
Lastly once the extraction occurs, I have to delete the records from the [OrderEntry] table based on the same conditions above, where the count is equal and the cprj are equal.
I keep getting a '0 rows returned' from my code.
My latest code is as below:

declare @COUNTER INT
select @COUNTER = count(cprj)
from SSIS.dbo.[MatchingRowsOutput-CI-BOM-ProjectCumulative]

Insert into SSIS.dbo.[CI-BM-CPRJ-Transfer]

(cprj, item, PartNumber, SWITEM, QTY, rutm, trid, mitm, pono, sitm, opol, qana, scpf, cwar, opno, cpha, exin, itlu, ssta, dsca, dscb, dscd, suno, ctyo, eitm)

SELECT
XMLLines.[cprj],
XMLLines.[item],
XMLLines.[PartNumber],
XMLLines.[SWITEM],
XMLLines.[QTY],
XMLLines.[rutm],
XMLLines.[trid],
XMLLines.[mitm],
XMLLines.[pono],
XMLLines.[sitm],
XMLLines.[opol],
XMLLines.[qana],
XMLLines.[scpf],
XMLLines.[cwar],
XMLLines.[opno],
XMLLines.[cpha],
XMLLines.[exin],
XMLLines.[itlu],
XMLLines.[ssta],
XMLLines.[dsca],
XMLLines.[dscb],
XMLLines.[dscd],
XMLLines.[suno],
XMLLines.[ctyo],
XMLLines.[eitm]

FROM
SSIS.dbo.[MatchingRowsOutput-CI-BOM-ProjectCumulative] AS XMLLines

inner JOIN
OrderEntry.dbo.OrderEntry as PartList ON
(XMLLines.cprj) = PartList.cprj

WHERE @COUNTER = PartList.DWRowCount

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

MarcusCole6833 · Accepted Answer · 2020-03-23T04:39:00

Solution 3

Rough Guide of my solution

I would create a table variable as shown in solution 1 that is representative of the columns in the transfer table that is populated by join to order entry and the cumulative.

Enter the data into temp

insert into @temp (columns needed)
select (columns needed)
from order_enter
inner join cumulative

Populate Transfer

insert into transfer (columns needed )
select (columns needed )
from @temp

Deletion

delete from oe

orderentry oe
inner join @temp t on t.cprj = oe.cprj

Deletion with a join Example

How to Delete using INNER JOIN with SQL Server? - Stack Overflow[^]

Posted 23-Mar-20 4:39am

MarcusCole6833

Comments

Member 14778402 23-Mar-20 10:52am

Hi Marcus, Thanks for your help. The one condition that is really pertinent is that the count of a particular cprj from the cumulative table must equal the value in the column DWRowCount in the order entry table for the same cprj. This determines the cprj records to pull from the cumulative table to the transfer table. Not sure how to code that condition. The count is returning a zero value as mentioned above.
You see, if the count from cumulative table for cprj and the value of DWRowCount are not equal, i do not want the records at all.

MarcusCole6833 23-Mar-20 11:07am

use the table variable to create a record of where you have a condition. Then you use this temp table as a reference for the remainder of steps. The reason I did not add any extra code or conditional logic was I could not work out what was meant to match in each tables.

Member 14778402 23-Mar-20 12:55pm

Hi Marcus for the life of me I cannot figure out how to create the condition.
Here is what I have so far, but I can't seem to get the condition to work:

declare @COUNTER INT
select @COUNTER = count(cprj)
from SSIS.dbo.[MatchingRowsOutput-CI-BOM-ProjectCumulative]
--where
--XMLLines.cprj=OrderEntry.DBO.OrderEntry.cprj

DECLARE @tmp TABLE (
ID INT,
[DWRowCount] [int] NULL,
[CumuCount] [int] NULL,
[cprj] [varchar](500) NULL,
[item] [varchar](500) NULL,
[PartNumber] [varchar](500) NULL,
[SWITEM] [varchar](500) NULL,
[QTY] [varchar](500) NULL,
[rutm] [varchar](500) NULL,
[trid] [varchar](500) NULL,
[mitm] [varchar](500) NULL,
[pono] [varchar](500) NULL,
[sitm] [varchar](500) NULL,
[opol] [varchar](500) NULL,
[qana] [varchar](500) NULL,
[scpf] [varchar](500) NULL,
[cwar] [varchar](500) NULL,
[opno] [varchar](500) NULL,
[cpha] [varchar](500) NULL,
[exin] [varchar](500) NULL,
[itlu] [varchar](500) NULL,
[ssta] [varchar](500) NULL,
[dsca] [varchar](500) NULL,
[dscb] [varchar](500) NULL,
[dscd] [varchar](500) NULL,
[suno] [varchar](500) NULL,
[ctyo] [varchar](500) NULL,
[eitm] [varchar](500) NULL)

Insert into @tmp

(DWRowCount, CumuCount, cprj, item, PartNumber, SWITEM, QTY, rutm, trid, mitm, pono, sitm, opol, qana, scpf, cwar, opno, cpha, exin, itlu, ssta, dsca, dscb, dscd, suno, ctyo, eitm)

SELECT
PartList.DWRowCount,
CumuCount = @COUNTER,
XMLLines.[cprj],
XMLLines.[item],
XMLLines.[PartNumber],
XMLLines.[SWITEM],
XMLLines.[QTY],
XMLLines.[rutm],
XMLLines.[trid],
XMLLines.[mitm],
XMLLines.[pono],
XMLLines.[sitm],
XMLLines.[opol],
XMLLines.[qana],
XMLLines.[scpf],
XMLLines.[cwar],
XMLLines.[opno],
XMLLines.[cpha],
XMLLines.[exin],
XMLLines.[itlu],
XMLLines.[ssta],
XMLLines.[dsca],
XMLLines.[dscb],
XMLLines.[dscd],
XMLLines.[suno],
XMLLines.[ctyo],
XMLLines.[eitm]

FROM
SSIS.dbo.[MatchingRowsOutput-CI-BOM-ProjectCumulative] AS XMLLines

inner JOIN
OrderEntry.dbo.OrderEntry as PartList ON
(XMLLines.cprj) = PartList.cprj

with the @Counter variable declared it only returns 156 count so its counting all the records under the cprj column. I need it to count only those total records in the cumulative table column for a specific project i.e TEK#7000 has only 26 entries, TEK#4700 has 13 entries and so forth but the return value is 156 which represents all the records for the entire table.

In the new @tmp table there are 2 columns created DWRowCount (from the Order Entry Table) and CumuCount that was just calculated with the condition.

I can then finally create the final table, deleting everything in the exception where the DWRowCount and CumuCount are equal.

Once this table is created I would like to got to the OrderEntry table and delete out the records that were retained in the final transfer table.

Thanks for your help!

MarcusCole6833 23-Mar-20 14:57pm

is the qnty from cumulative you wish to count as a group, then compare to the order list?

Member 14778402 23-Mar-20 15:08pm

No its all based on the number of rows and the cprj #. If you look at the tables I provided at the top QTY is not in the orderentry table. This order entry table is just a reference we use on how many rows for a particualr project is being sent thru our backend process and I am just comparing the number of lines on a per project basis from our cumulative table of the things sitting in a qeue. If they are equal I am getting the qeue to flow on a per project basis sending the transfer table thru

Member 14778402 23-Mar-20 15:16pm

I may have misinterpreted your statement in my other reply. I wish to group based on the same number of records from the cumulative table to the value in the DWRowCount column of the OrderEntry table having the same cprj as well. Not sure if this is what you were referring to.

MarcusCole6833 23-Mar-20 16:11pm

declare @t table (id int identity(1,1), cprojID varchar(20), countC int , CountE int)
select distinct cproj , 0, 0
from cumulative
inner join order entry on cproj = cproj

from here you can loop in the totals

update t
set countE =

TotalRowCount	or DWRowCount

inner join order entry on t.cproj = oe.cproj

use a loop to then go over each croj id and do a count per id in the cumulative table

from this temp table where countc == count E run the remainder of the insert and deletion logic

Member 14778402 23-Mar-20 16:40pm

This is getting way out of my league. I'm just a beginner at this SQL programming trying to muddle my way thru. I'm not familiar with looping at all. I wouldnt even know where to begin to use the loop in that code I had written. I will try to figure things out by what you have explained. Thanks for your help.

MarcusCole6833 23-Mar-20 16:55pm

it is not the simplest thing to do, is get a set value in one table that is cumulatively held in another table then run logic against this. Do you not have a more Senior Developer helping you?

Member 14778402 23-Mar-20 17:26pm

No one helping at this time. We are developing a company roll-out for automation of our products from a design point using driveworks for the models and drawings at the front end, and then using sql to handle the backend to manipulate all the data and information flow that would feed manufacturing and in house process flows. At this time, we have developed most of the backend but have put a little more thought on how to control with checks. This is one of the controls, because We want the transfer table to be developed in this particualr project, to accumulate all the projects (xml lines) it has been fed, and check it against what has been fed to us and then push this transfer table to an http post which will feed our configurator router. I want to accumulate the data before sending because I dont want to be constantly pinging the router. Also, if there are problems with certain projects that have been fed to the backend they will be errored out and handled manually. So the count in the cumulative project would not be the same as the orderentry thus we know not to push this data thru. Thats out intention with this query I have been trying to figure out. Its one piece of our puzzle.

MarcusCole6833 23-Mar-20 17:50pm

loop values ok here where we start

we have distinct values in the temp table with an incremental id

so we choose a min and max value as are counters for the loop

in the loop we need a part id and result to get hte summed values

below is a blue proint for that

I laid off the code solution as I thought this was homework, and that is why I might have been a bit opaque with my answers but reading your last reply you are doing something similar to me on a daily basis

select @min - min(id), @max = max(id) from @t
declare @cproj varchar(20)
declare @sum int = 0
while @min <= @max
begin

select @cproj = cprof from @t where id = @min

select @sum = sum(qnty) from comparative where cproj = @cproj

update @t set cCount = @sum where id = @min
set @min = @min + 1

end

Member 14778402 24-Mar-20 11:40am

Hi Marcus, Thank you for all your help and suggestions. I did manage to get it running yielding the results that I needed. I created a @tmp table and inserted into this table using an inner join,columns from the Cumulative table and the order entry table (DWRowCount from oe and count (cprj) as Cumucount). The count wasnt working before as I was unaware of a group by clause.
Then I inner joined this @tmp table and the cumulative table to the final transfer table calling in all the other columns I required from Cumulative table.
Then deleted the records in the transfer table where DWRowCount<>CumuCount. This yield the results I will be sending to the HTTP post.
Finally, I did a delete with the inner join that you recommended to delete the records from the orderentry table against the final transfer table where DWRowCount = CumuCount from their respective tables.

The programming may be inefficient the way I could understand how to code it, but the logic is there and the results are what we are after. After our proof of concept for the entire project we will have a professional SQL programmer evaluate our queries and possibly improve.

Thanks once again for all your help. It was much appreciated.

MarcusCole6833 24-Mar-20 12:16pm

would you mind adding my suggestion as the answer?