Click here to Skip to main content
14,486,801 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have written some sql code that is not yielding the proper results that I'm looking for. I have 2 table one is an order entry and the other is a cumulative table. I am inner joining the 2 tables and storing my results in a temp table.
The problem I have with the code is on the inner join where I am mapping based on the cprj columns. I want the count functions to be done on the cumulative table for the cprj and the sum function to be done on the orderentry table for the NoPartsPerLine Column. Something is wrong in the code after the 'ON' section of the inner join.

The answer I seem to be getting is that it counts the rows in the cumulative table and then multiplys by the # of rows for the same cprj that exists in the orderentry table. I tried the select top 1 statement for the orderentry table but it yields no results at all. When (XMLLines.cprj) = PartList.cprj it yields the multiplied result.

The results I am looking for are for example:

cprj TotalRowCount DWRowCount SumNoPartsPerLine CumuCount
TEK#55555 60 8 25 6


The Tables I am using are as such:

[OrderEntry] Table
ID	cprj		TotalRowCount		DWRowCount	NoPartsPerLine
1	TEK#7000	80			26		10
2	TEK#4444	50			30		2
3	TEK#88888	120			24		15
4	TEK#4700	37			13		6
5	TEK#99999	53			42		8
203	TEK#5555	60			8		10
203	TEK#5555	60			8		5
203	TEK#5555	60			8		5
203	TEK#5555	60			8		2
203	TEK#5555	60			8		3


and

[MatchingRowsRUwithRU-DWCALCS-DATA-ProjectCumulative] table
ID	cprj		item		PartNumber		SWITEM	QTY	
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	


Any Help is appreciated!

What I have tried:

DECLARE @tmp TABLE (
    ID INT,
    [cprj] [varchar](500) NULL,
    [TotalRowCount] [int] NULL, 
    [DWRowCount] [int] NULL,
    [SumNoPartsPerLine] [int] NULL,
    [CumuCount] [int] NULL
)

----------------------------------------inner join between order entry and cumulative table and insert results into @tmp table

INSERT INTO @tmp
    (cprj, TotalRowCount, DWRowCount, SumNoPartsPerLine, CumuCount) 
SELECT
    XMLLines.[cprj],
    PartList.TotalRowCount,
    PartList.DWRowCount,
    sum (PartList.NoPartsPerLine) SumNoPartsPerLine,
    count (XMLLines.cprj) CumuCount
FROM 
    OrderEntry.dbo.OrderEntry as PartList
    INNER JOIN SSIS.dbo.[MatchingRowsRUwithRU-DWCALCS-DATA-ProjectCumulative] AS XMLLines 
    ON
        --(XMLLines.cprj) = PartList.cprj
        (Select top 1 cprj from OrderEntry.dbo.OrderEntry) = (XMLLines.cprj)
GROUP BY
    XMLLines.cprj, 
    PartList.DWRowCount, 
    TotalRowCount
;

SELECT * FROM @tmp
Posted
Updated 4 days ago
v3
Rate this:
Please Sign up or sign in to vote.

Solution 1

You'll need to do the aggregation before you join the tables.
DECLARE @tmp TABLE 
(
    ID INT,
    [cprj] [varchar](500) NULL,
    [TotalRowCount] [int] NULL, 
    [DWRowCount] [int] NULL,
    [SumNoPartsPerLine] [int] NULL,
    [CumuCount] [int] NULL
);

WITH ctePartList As
(
    SELECT
        cprj,
        TotalRowCount,
        DWRowCount,
        Sum(NoPartsPerLine) As SumNoPartsPerLine
    FROM
        OrderEntry.dbo.OrderEntry
    GROUP BY
        cprj,
        TotalRowCount,
        DWRowCount
),
cteXmlLines As
(
    SELECT
        cprj,
        Count(1) As CumuCount
    FROM
        SSIS.dbo.[MatchingRowsRUwithRU-DWCALCS-DATA-ProjectCumulative]
    GROUP BY
        cprj
)
INSERT INTO @tmp
(
    cprj, 
    TotalRowCount, 
    DWRowCount, 
    SumNoPartsPerLine, 
    CumuCount
)
SELECT
    P.cprj,
    P.TotalRowCount,
    P.DWRowCount,
    P.SumNoPartsPerLine,
    X.CumuCount
FROM
    ctePartList As P
    INNER JOIN cteXmlLines As X
    ON X.cprj = P.cprj
;

SELECT * FROM @tmp;
   
Comments
Member 14778402 4 days ago
   
Thank you very much! Your Help was much appreciated. Worked great! This aggregation stuff is aggravating :).
Rate this:
Please Sign up or sign in to vote.

Solution 2

I don't think you need the temp table. The following works fine in PostgreSQL. Something similar should work for SQL-Server
SELECT parts.cprj, TotalRowCount, DWRowCount, SumNoPartsPerLine, cumul.CumuCount
   FROM (SELECT  cprj, TotalRowCount, DWRowCount, sum(NoPartsPerLine) as SumNoPartsPerLine
            FROM OrderEntry
            GROUP BY (cprj, TotalRowCount, DWRowCount) as parts
   INNER JOIN (SELECT cprj, count(cprj) as CumuCount
             FROM MatchingRowsRUwithRU-DWCALCS-DATA-ProjectCumulative
             GROUP BY cprj) as cumul
   ON parts.cprj = cumul.cprj
   
Comments
Member 14778402 4 days ago
   
Thanks very much. I will give it a go as well.

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100