Solved

Summing columns on duplicate rows in the same table.

Posted on 2015-01-16
14
57 Views
Last Modified: 2015-03-12
My code looks like this:
ase to use
USE DmStagingVD;

    -- table the results write to, this script as it stands runs one company at a time to change the company, 
    -- change the DMFBOMENTITY table name and the where where psco = ?

    DELETE [DMFBOMENTITY3];
    INSERT INTO [DMFBOMENTITY3]


    SELECT   DISTINCT  
    
  -- APPROVAL INFORMATION
                      'YES' AS [BOM_APPROVED],
                      ' ' AS [BOM_APPROVERPARTYNAME],
                      '00005' AS [BOM_APPROVERPARTYNUMBER],
    
	                  '00005' AS [BOM_APPROVERPERSONNELNUMBER]-- BOM INFORMATION & DESCRIPTION
                      ,
                      [PSPMRN] AS [BOM_BOMID],
                      ' ' AS [BOM_CHECKBOM],
                      'Prts' AS [BOM_ITEMGROUPID],

				      ISNULL([EcoResProductTranslation_Name],' ') AS [BOM_NAME],
						
                      ' ' AS [BOM_ROUTE_CONFIGGROUPID],
                      0 AS [BOM_ROUTE_LINENUM],
                      ' ' AS [BOM_ROUTE_NAME],
                      'Variable' AS [BOMCONSUMP],----- ITEM QUANTITY
					   
                       CAST (LTRIM(RTRIM([PSQTY]))AS INT) AS [BOMQTY],

                      1 AS [BOMQTYSERIE]----- BOM TYPE


                      ,
                      CASE 
						WHEN [Phantom] IN ('N', 'P') THEN 'Phantom' 
						WHEN [Phantom] = ' ' THEN 'ITEM' ELSE 'ITEM' 
						END AS [BOMTYPE], 


                      'YES' AS [CALCULATION]---CONFIGURATION INFO
                      ,
                      ' ' AS [CONFIGGROUPID],

                       CASE 
				   WHEN substring([INVENTDIM_CONFIGID],1,15) = PSCMRN THEN [ITEMID] ELSE ' ' END as  [CONFIGID] 


                      ,0 AS [CONSTANT],
                      ' ' AS [DEFINITIONGROUP],
                      0 AS [DENSITY],
                      0 AS [DEPTH],
                      ' ' AS [ENDSCHEDCONSUMP],
                      ' ' AS [EXECUTIONID],
                      ' ' AS [FORMULA]---  FROM DATE
                      ,
                      0 AS [FROMDATE]---- PRODUCT DIMENSIONS: NOT USED AT THIS TIME
                      ,
                      0 AS [HEIGHT],
                      ' ' AS [INVENTBATCHID],
                      ' ' AS [INVENTCOLORID],
                      ' ' AS [INVENTDIMID],
                      ' ' AS [INVENTLOCATIONID],
                      ' ' AS [INVENTSERIALID],
                      'IND' AS [INVENTSITEID],
                      ' ' AS [INVENTSIZEID],
                      ' ' AS [INVENTSTYLEID],
                      0 AS [ISSELECTED],

                      [PSPMRN] AS [ITEMBOMID],--- ITEMID / CHILD PART #,
                      [PSCMRN] AS [ITEMID],
 
                      ' ' AS [ITEMPBAID],
                      ' ' AS [ITEMROUTEID]--- LINE NUMBER AUTOMATED AT THE SERVER LEVEL/ WE DO NOT USE PSOPR
                      ,
                      0 AS [LINENUM],
                      ' ' AS [OPRNUM],
                      ' ' AS [PDSBASEVALUE],
                      0 AS [PDSCWQTY],
                      ' ' AS [PDSINGREDIENTTYPE],--- INHERITIED INFORMATION

                      ' ' AS [PDSINHERITCOPRODUCTBATCHATTRIB],
                      ' ' AS [PDSINHERITCOPRODUCTSHELFLIFE],
                      ' ' AS [PDSINHERITENDITEMBATCHATTRIB],
                      ' ' AS [PDSINHERITENDITEMSHELFLIFE],
                      0 AS [PMFFORMULAPCT],
                      ' ' AS [PMFPCTENABLE],
                      ' ' AS [PMFPLANGROUPID],
                      0 AS [PMFPLANGROUPPRIORITY],
                      ' ' AS [PMFSCALABLE]--- ITEM SEQ #
                      ,
                      CAST (LTRIM(RTRIM(PSSEQ)) AS VARCHAR (20)) AS [POSITION]--- ITEM FINISH
                      ,
                      'FINISH' AS [PRODFLUSHINGPRINCIP],
                      ' ' AS [PROJSETSUBPRODTOCONSUMED],
                      ' ' AS [ROUNDUP],
                      0 AS [ROUNDUPQTY],
                      0 AS [ROWID],
                      0 AS [SCRAPCONST],
                      0 AS [SCRAPVAR]--- TO DATE
                      ,

                      CASE 
						WHEN PSEXMO BETWEEN 1 AND 12 
						THEN substring(CAST (20000000 + [PSEXYR] * 10000 + [PSEXMO] * 100 + [PSEXDA] AS CHAR (10)), 1, 4)
						 + '-' + substring(CAST (20000000 + [PSEXYR] * 10000 + [PSEXMO] * 100 + [PSEXDA] AS CHAR (10)), 5, 2)
						 + '-' + substring(CAST (20000000 + [PSEXYR] * 10000 + [PSEXMO] * 100 + [PSEXDA] AS CHAR (10)), 7, 2) 
						WHEN PSEXYR = 99 THEN '2049-12-31' 
						END AS [TODATE],


                      0 AS [TRANSFERSTATUS]-- UNIT & VENDOR ID
                      ,
					
					 CASE 
						WHEN [BOMUnitId] <> ' '
                                AND [BOMUnitId] <> 'PC' THEN [BOMUnitId] ELSE 'EA' 
                      END AS [UNITID],


                      '  ' AS [VENDID],
                      0 AS [WIDTH],
                      ' ' AS [WMSLOCATIONID],
                      ' ' AS [WMSPALLETID],
                      ' ' AS [WRKCTRCONSUMPTION],
                      0 AS [RECVERSION],
                      0 AS [PARTITION],
                      0 AS [RECID]


    ---  ALL CHAINS BY CHILD ITEM# and Validate that co# is correct, pull on 2014 
    ---- and later years for expiration date (includes 99) 
    ---- and validate that the century PSEXCT is not 20th century (19xx)


    FROM     OPENQUERY ([INDY.ALLEGION.COM], 
	   ' SELECT DISTINCT

				     [PSPMRN]
					,[EcoResProductTranslation_Name]
					,[PSQTY]
					,[Phantom]
					,[PSCMRN]
					,[PSEFCT]
					,[PSEFYR]
					,[PSEFMO]
					,[PSEFDA]
					,[PSSEQ]
					,[PSOPR]
					,[PSEXYR]
					,[PSEXMO]
					,[PSEXDA]
					,[BOMUnitId] 
					,[DisplayProductNumber]
					,[ITEMID]
					,[INVENTDIM_CONFIGID]
		                      	 
			   FROM    [VD_GPL].[DMFPRODUCTENTITY] b
				   inner join [KBM400MFG].[FKPSTRUC] c on b.[DISPLAYPRODUCTNUMBER] = c.[PSCMRN]
 				   where psco = 3 and [PSEXYR] > 14   
 
   ') AS A

Open in new window


the table I am populating looks like this:

Table Name	Column Name	Default Value
DMFBOMENTITY3	BOM_APPROVED	NULL
DMFBOMENTITY3	BOM_APPROVERPARTYNAME	NULL
DMFBOMENTITY3	BOM_APPROVERPARTYNUMBER	NULL
DMFBOMENTITY3	BOM_APPROVERPERSONNELNUMBER	NULL
DMFBOMENTITY3	BOM_BOMID	NULL
DMFBOMENTITY3	BOM_CHECKBOM	NULL
DMFBOMENTITY3	BOM_ITEMGROUPID	NULL
DMFBOMENTITY3	BOM_NAME	NULL
DMFBOMENTITY3	BOM_ROUTE_CONFIGGROUPID	NULL
DMFBOMENTITY3	BOM_ROUTE_LINENUM	NULL
DMFBOMENTITY3	BOM_ROUTE_NAME	NULL
DMFBOMENTITY3	BOMCONSUMP	NULL
DMFBOMENTITY3	BOMQTY	NULL
DMFBOMENTITY3	BOMQTYSERIE	NULL
DMFBOMENTITY3	BOMTYPE	NULL
DMFBOMENTITY3	CALCULATION	NULL
DMFBOMENTITY3	CONFIGGROUPID	NULL
DMFBOMENTITY3	CONFIGID	NULL
DMFBOMENTITY3	CONSTANT	NULL
DMFBOMENTITY3	DEFINITIONGROUP	NULL
DMFBOMENTITY3	DENSITY	NULL
DMFBOMENTITY3	DEPTH	NULL
DMFBOMENTITY3	ENDSCHEDCONSUMP	NULL
DMFBOMENTITY3	EXECUTIONID	NULL
DMFBOMENTITY3	FORMULA	NULL
DMFBOMENTITY3	FROMDATE	NULL
DMFBOMENTITY3	HEIGHT	NULL
DMFBOMENTITY3	INVENTBATCHID	NULL
DMFBOMENTITY3	INVENTCOLORID	NULL
DMFBOMENTITY3	INVENTDIMID	NULL
DMFBOMENTITY3	INVENTLOCATIONID	NULL
DMFBOMENTITY3	INVENTSERIALID	NULL
DMFBOMENTITY3	INVENTSITEID	NULL
DMFBOMENTITY3	INVENTSIZEID	NULL
DMFBOMENTITY3	INVENTSTYLEID	NULL
DMFBOMENTITY3	ISSELECTED	NULL
DMFBOMENTITY3	ITEMBOMID	NULL
DMFBOMENTITY3	ITEMID	NULL
DMFBOMENTITY3	ITEMPBAID	NULL
DMFBOMENTITY3	ITEMROUTEID	NULL
DMFBOMENTITY3	LINENUM	NULL
DMFBOMENTITY3	OPRNUM	NULL
DMFBOMENTITY3	PDSBASEVALUE	NULL
DMFBOMENTITY3	PDSCWQTY	NULL
DMFBOMENTITY3	PDSINGREDIENTTYPE	NULL
DMFBOMENTITY3	PDSINHERITCOPRODUCTBATCHATTRIB	NULL
DMFBOMENTITY3	PDSINHERITCOPRODUCTSHELFLIFE	NULL
DMFBOMENTITY3	PDSINHERITENDITEMBATCHATTRIB	NULL
DMFBOMENTITY3	PDSINHERITENDITEMSHELFLIFE	NULL
DMFBOMENTITY3	PMFFORMULAPCT	NULL
DMFBOMENTITY3	PMFPCTENABLE	NULL
DMFBOMENTITY3	PMFPLANGROUPID	NULL
DMFBOMENTITY3	PMFPLANGROUPPRIORITY	NULL
DMFBOMENTITY3	PMFSCALABLE	NULL
DMFBOMENTITY3	POSITION	NULL
DMFBOMENTITY3	PRODFLUSHINGPRINCIP	NULL
DMFBOMENTITY3	PROJSETSUBPRODTOCONSUMED	NULL
DMFBOMENTITY3	ROUNDUP	NULL
DMFBOMENTITY3	ROUNDUPQTY	NULL
DMFBOMENTITY3	ROWID	NULL
DMFBOMENTITY3	SCRAPCONST	NULL
DMFBOMENTITY3	SCRAPVAR	NULL
DMFBOMENTITY3	TODATE	NULL
DMFBOMENTITY3	TRANSFERSTATUS	NULL
DMFBOMENTITY3	UNITID	NULL
DMFBOMENTITY3	VENDID	NULL
DMFBOMENTITY3	WIDTH	NULL
DMFBOMENTITY3	WMSLOCATIONID	NULL
DMFBOMENTITY3	WMSPALLETID	NULL
DMFBOMENTITY3	WRKCTRCONSUMPTION	NULL
DMFBOMENTITY3	RECVERSION	NULL
DMFBOMENTITY3	PARTITION	NULL
DMFBOMENTITY3	RECID	NULL

Open in new window


Where there are duplicates I need to sum the duplicate row's coulumn BOMQTY, where BOM_BOMID is the same per duplicate row and the ItemID are the same.

I was trying to use this code to remove the duplicates directly after the above query, but it is not totally effective as it leaves some duplicates and of course it does not sum the BOMQTY column

DELETE X  from (
  select *, rn=row_number() over (partition by  [BOM_BOMID] ORDER by [ItemId])
  from   [dbo].[DMFBOMENTITY3]
) x
where rn > 1;

Open in new window


I am trying to incorporate the SUM of the field BOMQTY when the rows have identical BOM_BOMID and ITEMID's within the body of the main script at the top of this question. Unfortunately I am failing miserably. Can someone help me please?  I am using SQL SERVER 2012. Thank you!
0
Comment
Question by:Leogal
  • 5
  • 4
  • 2
  • +2
14 Comments
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 40554096
you may try this:

SELECT DISTINCT BOM_BOMID, ITEMID, SUM(BOMQTY) OVER (PARTITION BY  BOM_BOMID, ITEMID ORDER BY BOM_BOMID, ITEMID) 
FROM DMFBOMENTITY3

Open in new window


HTH.
0
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 40554513
SELECT	BOM_BOMID
,	ITEMID
,	BOMQTY = SUM(BOMQTY)
FROM	DMFBOMENTITY3
GROUP
BY	BOM_BOMID
,	ITEMID
HAVING	COUNT(*) > 1

Open in new window

0
 

Author Comment

by:Leogal
ID: 40555181
@viliappan AN,  When i try to use your code as a subselect with the script like this, i get an error.
 (SELECT DISTINCT BOM_BOMID, ITEMID,
                                SUM(BOMQTY) OVER (PARTITION BY  BOM_BOMID, ITEMID ORDER BY BOM_BOMID, ITEMID)
                        FROM DMFBOMENTITY3) as BOMQTY,
the error: Msg 116, Level 16, State 1, Line 33
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.


@john vidmar,  When i try to use your code as a subselect with the script like this, i get an error.
 
                               (SELECT BOM_BOMID, ITEMID, BOMQTY = SUM(BOMQTY)
                        FROM      DMFBOMENTITY3 GROUP BY      BOM_BOMID,      ITEMID
                         HAVING      COUNT(*) > 1) as BOMQTY,

the error: Msg 116, Level 16, State 1, Line 33
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
0
 

Author Comment

by:Leogal
ID: 40555217
Trying this..
(select sum(bomqty)
                                          from DMFBOMENTITY3 a
                                          where exists (select 1
                                                              from DMFBOMENTITY3 b
                                                              where b.BOMQTY = a.BOMQTY
                                                              group by b.BOM_BOMID, ITEMID
                                                              having count(distinct b.ITEMID) > 2)) AS BOMQTY,
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 40555316
Hi you just may need to add it as a field and not subquery like this:

SELECT <YOURFIELDSLIST>,
SUM(BOMQTY) OVER (PARTITION BY  BOM_BOMID, ITEMID ORDER BY BOM_BOMID, ITEMID)  FROM <TABLENAME>

HTH
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 40555318
You may use Distinct to avoid duplicate BOM_BOMID, ITEMID in results.
0
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 40555485
The above solutions are providing a summation for each duplicated BOM_BOMID and ITEMID combination, which may give you multiple records, each record has 3 fields (that is why you cannot collapse into a subquery with a single label).

However, the above solutions can be used as an inline-table (example below), or placed into a with-clause.

Deleting duplicates using an inline-table (note: this example does not need the BOMQTY summation):
DELETE	a
FROM	DMFBOMENTITY3	a
JOIN	(	SELECT	BOM_BOMID
		,	ITEMID
		,	BOMQTY = SUM(BOMQTY)
		FROM	DMFBOMENTITY3
		GROUP
		BY	BOM_BOMID
		,	ITEMID
		HAVING	COUNT(*) > 1
	)	b	ON	a.BOM_BOMID = b.BOM_BOMID
			AND	a.ITEMID = b.ITEMID

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 9

Expert Comment

by:Valliappan AN
ID: 40556254
John,

>> The above solutions are providing a summation for each duplicated BOM_BOMID and ITEMID combination, which may give you multiple records, each record has 3 fields (that is why you cannot collapse into a subquery with a single label).

Did you read my post http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28598252.html#a40555316.

Its not to be used as sub query, and its to be included as a field to the fields list in the DISTINCT query to avoid duplicates.

HTH.
0
 

Author Comment

by:Leogal
ID: 40556774
My reasoning in using a sub-select is that my results file must have one entry of ITEM and BOM_BOMID in the table for the duplicates and a total of all the quantities for each duplicate records.    @Valliappan AN, you code does remove the duplicates but it appears it deletes all of the duplicates as opposed to keeping a single record with a cumulative total.

When i try my code sub-select i do end up with multiples.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 40556940
try grouping instead of using "select distinct" in the openquery string:
SELECT
      [PSPMRN]
    , [EcoResProductTranslation_Name]
    , SUM([PSQTY]) AS PSQTY
    , [Phantom]
    , [PSCMRN]
    , [PSEFCT]
    , [PSEFYR]
    , [PSEFMO]
    , [PSEFDA]
    , [PSSEQ]
    , [PSOPR]
    , [PSEXYR]
    , [PSEXMO]
    , [PSEXDA]
    , [BOMUnitId]
    , [DisplayProductNumber]
    , [ITEMID]
    , [INVENTDIM_CONFIGID]
FROM [VD_GPL].[DMFPRODUCTENTITY] B
      INNER JOIN [KBM400MFG].[FKPSTRUC] C ON B.[DISPLAYPRODUCTNUMBER] = C.[PSCMRN]
WHERE psco = 3
AND [PSEXYR] > 14
GROUP BY
      [PSPMRN]
    , [EcoResProductTranslation_Name]
    , [Phantom]
    , [PSCMRN]
    , [PSEFCT]
    , [PSEFYR]
    , [PSEFMO]
    , [PSEFDA]
    , [PSSEQ]
    , [PSOPR]
    , [PSEXYR]
    , [PSEXMO]
    , [PSEXDA]
    , [BOMUnitId]
    , [DisplayProductNumber]
    , [ITEMID]
    , [INVENTDIM_CONFIGID]

Open in new window

However it seems that [PSQTY] may be a string, if that's true then cast it to to integer before summing.

NB: It would be useful (and best practice) to use the table alias on ALL references as it might be better to sum the quantity BEFORE joining. However as I cannot tell which table owns what fields I can't propose that alternative.

--------------------------------
Providing a sample of data, and the "expected result" from that sample, is the most efficient way to reach a solution.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40556942
I should also like to point out that "select distinct" is the natural enemy of accuracy when summing....

Consider this scenario.

base_table
fldA Qty
abc 1
abc 1
abc 1

select flda, sum(qty) from (
    select distinct flda, qty from base_table
 ) as a

would produce a result of:

fldA Qty
abc 1
instead:

select fldA, sum(Qty) as Qty from base_table group by fldA

would produce:

fldA Qty
abc 3
0
 
LVL 9

Accepted Solution

by:
Valliappan AN earned 250 total points
ID: 40557343
Hi,

Portlet:
I was pointing out in this direction, for ex take your example: the query should be:

select distinct flda, sum(qty)  over (partition by fldA order by fldA) from base_table

which is misunderstood by many here.

Leogal:

If you cant use the above syntax obviously can use GROUP BY clause, as follows:
 
USE DmStagingVD;

    -- table the results write to, this script as it stands runs one company at a time to change the company, 
    -- change the DMFBOMENTITY table name and the where where psco = ?

    DELETE [DMFBOMENTITY3];
    INSERT INTO [DMFBOMENTITY3]


    SELECT   DISTINCT  
    
  -- APPROVAL INFORMATION
                      'YES' AS [BOM_APPROVED],
                      ' ' AS [BOM_APPROVERPARTYNAME],
                      '00005' AS [BOM_APPROVERPARTYNUMBER],
    
                  '00005' AS [BOM_APPROVERPERSONNELNUMBER]-- BOM INFORMATION & DESCRIPTION
                      ,
                      [PSPMRN] AS [BOM_BOMID],
                      ' ' AS [BOM_CHECKBOM],
                      'Prts' AS [BOM_ITEMGROUPID],

				      ISNULL([EcoResProductTranslation_Name],' ') AS [BOM_NAME],
						
                      ' ' AS [BOM_ROUTE_CONFIGGROUPID],
                      0 AS [BOM_ROUTE_LINENUM],
                      ' ' AS [BOM_ROUTE_NAME],
                      'Variable' AS [BOMCONSUMP],----- ITEM QUANTITY
					   
                       CAST (LTRIM(RTRIM([PSQTY]))AS INT) AS [BOMQTY],

                      1 AS [BOMQTYSERIE]----- BOM TYPE


                      ,
                      CASE 
						WHEN [Phantom] IN ('N', 'P') THEN 'Phantom' 
						WHEN [Phantom] = ' ' THEN 'ITEM' ELSE 'ITEM' 
						END AS [BOMTYPE], 


                      'YES' AS [CALCULATION]---CONFIGURATION INFO
                      ,
                      ' ' AS [CONFIGGROUPID],

                       CASE 
				   WHEN substring([INVENTDIM_CONFIGID],1,15) = PSCMRN THEN [ITEMID] ELSE ' ' END as  [CONFIGID] 


                      ,0 AS [CONSTANT],
                      ' ' AS [DEFINITIONGROUP],
                      0 AS [DENSITY],
                      0 AS [DEPTH],
                      ' ' AS [ENDSCHEDCONSUMP],
                      ' ' AS [EXECUTIONID],
                      ' ' AS [FORMULA]---  FROM DATE
                      ,
                      0 AS [FROMDATE]---- PRODUCT DIMENSIONS: NOT USED AT THIS TIME
                      ,
                      0 AS [HEIGHT],
                      ' ' AS [INVENTBATCHID],
                      ' ' AS [INVENTCOLORID],
                      ' ' AS [INVENTDIMID],
                      ' ' AS [INVENTLOCATIONID],
                      ' ' AS [INVENTSERIALID],
                      'IND' AS [INVENTSITEID],
                      ' ' AS [INVENTSIZEID],
                      ' ' AS [INVENTSTYLEID],
                      0 AS [ISSELECTED],

                      [PSPMRN] AS [ITEMBOMID],--- ITEMID / CHILD PART #,
                      [PSCMRN] AS [ITEMID],
 
                      ' ' AS [ITEMPBAID],
                      ' ' AS [ITEMROUTEID]--- LINE NUMBER AUTOMATED AT THE SERVER LEVEL/ WE DO NOT USE PSOPR
                      ,
                      0 AS [LINENUM],
                      ' ' AS [OPRNUM],
                      ' ' AS [PDSBASEVALUE],
                      0 AS [PDSCWQTY],
                      ' ' AS [PDSINGREDIENTTYPE],--- INHERITIED INFORMATION

                      ' ' AS [PDSINHERITCOPRODUCTBATCHATTRIB],
                      ' ' AS [PDSINHERITCOPRODUCTSHELFLIFE],
                      ' ' AS [PDSINHERITENDITEMBATCHATTRIB],
                      ' ' AS [PDSINHERITENDITEMSHELFLIFE],
                      0 AS [PMFFORMULAPCT],
                      ' ' AS [PMFPCTENABLE],
                      ' ' AS [PMFPLANGROUPID],
                      0 AS [PMFPLANGROUPPRIORITY],
                      ' ' AS [PMFSCALABLE]--- ITEM SEQ #
                      ,
                      CAST (LTRIM(RTRIM(PSSEQ)) AS VARCHAR (20)) AS [POSITION]--- ITEM FINISH
                      ,
                      'FINISH' AS [PRODFLUSHINGPRINCIP],
                      ' ' AS [PROJSETSUBPRODTOCONSUMED],
                      ' ' AS [ROUNDUP],
                      0 AS [ROUNDUPQTY],
                      0 AS [ROWID],
                      0 AS [SCRAPCONST],
                      0 AS [SCRAPVAR]--- TO DATE
                      ,

                      CASE 
						WHEN PSEXMO BETWEEN 1 AND 12 
						THEN substring(CAST (20000000 + [PSEXYR] * 10000 + [PSEXMO] * 100 + [PSEXDA] AS CHAR (10)), 1, 4)
						 + '-' + substring(CAST (20000000 + [PSEXYR] * 10000 + [PSEXMO] * 100 + [PSEXDA] AS CHAR (10)), 5, 2)
						 + '-' + substring(CAST (20000000 + [PSEXYR] * 10000 + [PSEXMO] * 100 + [PSEXDA] AS CHAR (10)), 7, 2) 
						WHEN PSEXYR = 99 THEN '2049-12-31' 
						END AS [TODATE],


                      0 AS [TRANSFERSTATUS]-- UNIT & VENDOR ID
                      ,
					
					 CASE 
						WHEN [BOMUnitId] <> ' '
                                AND [BOMUnitId] <> 'PC' THEN [BOMUnitId] ELSE 'EA' 
                      END AS [UNITID],


                      '  ' AS [VENDID],
                      0 AS [WIDTH],
                      ' ' AS [WMSLOCATIONID],
                      ' ' AS [WMSPALLETID],
                      ' ' AS [WRKCTRCONSUMPTION],
                      0 AS [RECVERSION],
                      0 AS [PARTITION],
                      0 AS [RECID]


    ---  ALL CHAINS BY CHILD ITEM# and Validate that co# is correct, pull on 2014 
    ---- and later years for expiration date (includes 99) 
    ---- and validate that the century PSEXCT is not 20th century (19xx)


    FROM     OPENQUERY ([INDY.ALLEGION.COM], 
	   ' SELECT 

				     [PSPMRN]
					,[EcoResProductTranslation_Name]
					,SUM(CAST (LTRIM(RTRIM([PSQTY]))AS INT)) AS PSQTY
					,[Phantom]
					,[PSCMRN]
					,[PSEFCT]
					,[PSEFYR]
					,[PSEFMO]
					,[PSEFDA]
					,[PSSEQ]
					,[PSOPR]
					,[PSEXYR]
					,[PSEXMO]
					,[PSEXDA]
					,[BOMUnitId] 
					,[DisplayProductNumber]
					,[ITEMID]
					,[INVENTDIM_CONFIGID]
		                      	 
			   FROM    [VD_GPL].[DMFPRODUCTENTITY] b
				   inner join [KBM400MFG].[FKPSTRUC] c on b.[DISPLAYPRODUCTNUMBER] = c.[PSCMRN]
                                          GROUP BY  [PSPMRN]
					,[EcoResProductTranslation_Name]
					,[Phantom]
					,[PSCMRN]
					,[PSEFCT]
					,[PSEFYR]
					,[PSEFMO]
					,[PSEFDA]
					,[PSSEQ]
					,[PSOPR]
					,[PSEXYR]
					,[PSEXMO]
					,[PSEXDA]
					,[BOMUnitId] 
					,[DisplayProductNumber]
					,[ITEMID]
					,[INVENTDIM_CONFIGID]
 				   where psco = 3 and [PSEXYR] > 14   
 

   ') AS A

Open in new window


It appears Portlet have suggested a similar answer earlier.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 40560522
do you still need assistance with this?

if so please confirm what you mean by a duplicate row...

if your duplication test is just related to a couple of the columns , which data values do you wish to maintain in the other columns for that row....

whilst you are considering this ... re-evaluate your open query code and consider what  you are trying to achieve with the distinct clause...

where/why is the duplication occuring in the first place?  
where is it most logical to contain/select the best candidate date from...?

as others have requested  can you supply some test data and expected result so that we can actually see/understand your problem and desired solution.
0
 

Author Comment

by:Leogal
ID: 40561827
Sorry for the delay in responding. I was pulled in another direction for two days.
I will be returning to this today.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now