SQLTeam.com | Weblogs | Forums

Remove duplicates and adding rows of a particular id

sql2008r2

#1

SELECT *
INTO #SEMFEST_TABLE
FROM ((SELECT CUS_ACTIVITY_ID, MASTER_CUSTOMER_ID, SUB_CUSTOMER_ID,
SUBSYSTEM, ACTIVITY_DATE, ACTIVITY_CODE, ACTIVITY_SUBCODE,
ACTIVITY_TEXT, CALL_TYPE_CODE, CALL_TOPIC_CODE,
CALL_TOPIC_SUBCODE, STAFF_USER_ID, COMMENTS, MARKET_CODE, ADDOPER, ADDDATE
FROM CUS_ACTIVITY
WHERE (ACTIVITY_CODE = 'CONTACTTRACKING') AND
(CALL_TOPIC_CODE = 'fest_callS') AND
(ACTIVITY_DATE > CONVERT(DATETIME, '2016-08-01 00:00:00', 102)))T1
LEFT OUTER JOIN
(SELECT PARENT_PRODUCT, SHIP_MASTER_CUSTOMER_ID, SHIP_SUB_CUSTOMER_ID,
ACTUAL_TOTAL_AMOUNT,
INVOICE_DATE AS REGISTRANT_DATE
FROM ORDER_DETAIL
WHERE (PARENT_PRODUCT = '17LV'))T2 ON T1.MASTER_CUSTOMER_ID = T2.SHIP_MASTER_CUSTOMER_ID)

---- I am trying to get total amount of a particular id.( There are multiple id's ) and get greatest invoice date??


#2

Basically, you need to SUM(ACTUAL_TOTAL_AMOUNT), MAX(INVOICE_DATE) and GROUP BY (MASTER_CUSTOMER_ID)

if that's the ID you want to report on.

It might be simplest to add a second query after this one that works on #SEMFEST_TABLE

e.g.

select MASTER_CUSTOMER_ID
       , SUM(ACTUAL_TOTAL_AMOUNT) as SumAmount
       , MAX(REGISTRANT_DATE) as MaxDate
FROM #SEMFEST_TABLE
GROUP by MASTER_CUSTOMER_ID

#3

Ok..What if i want to get all columns with this result?

i tried this one: But getting error. Msg 306, Level 16, State 2, Line 11
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

select CUS_ACTIVITY_ID, MASTER_CUSTOMER_ID, SUB_CUSTOMER_ID,
SUBSYSTEM, ACTIVITY_DATE, ACTIVITY_CODE, ACTIVITY_SUBCODE,
ACTIVITY_TEXT, CALL_TYPE_CODE, CALL_TOPIC_CODE,
CALL_TOPIC_SUBCODE, STAFF_USER_ID, COMMENTS, MARKET_CODE, ADDOPER, ADDDATE,
SUM(ACTUAL_TOTAL_AMOUNT) as SumAmount,
MAX(INVOICE_DATE) as MaxDate
FROM #SEMFEST_TABLE
GROUP by CUS_ACTIVITY_ID, MASTER_CUSTOMER_ID, SUB_CUSTOMER_ID,
SUBSYSTEM, ACTIVITY_DATE, ACTIVITY_CODE, ACTIVITY_SUBCODE,
ACTIVITY_TEXT, CALL_TYPE_CODE, CALL_TOPIC_CODE,
CALL_TOPIC_SUBCODE, STAFF_USER_ID, COMMENTS, MARKET_CODE, ADDOPER, ADDDATE


#4

the usual approach is to get the aggregates (the sum and max) then join back to the main query to show all the columns. Alternately, use SUM() OVER() and MAX() OVER() to do the work


#5

Thanks a lot


#6

SELECT *
FROM (((SELECT CUS_ACTIVITY_ID, MASTER_CUSTOMER_ID, SUB_CUSTOMER_ID,
SUBSYSTEM, ACTIVITY_DATE, ACTIVITY_CODE, ACTIVITY_SUBCODE,
ACTIVITY_TEXT, CALL_TYPE_CODE, CALL_TOPIC_CODE,
CALL_TOPIC_SUBCODE, STAFF_USER_ID, COMMENTS, MARKET_CODE, ADDOPER, ADDDATE
FROM CUS_ACTIVITY
WHERE ACTIVITY_CODE = 'CONTACTTRACKING' AND
CALL_TOPIC_CODE = 'fest_callS' AND
ACTIVITY_DATE > CONVERT(DATETIME, '2016-08-01 00:00:00', 102))T1
LEFT OUTER JOIN
(SELECT PARENT_PRODUCT, SHIP_MASTER_CUSTOMER_ID, SHIP_SUB_CUSTOMER_ID, sum(ACTUAL_TOTAL_AMOUNT)as REGISTRANT_TOTAL,
max(INVOICE_DATE) AS GREATEST_INVOICE_DATE
FROM ORDER_DETAIL
WHERE PARENT_PRODUCT = '17LV' group by SHIP_MASTER_CUSTOMER_ID, SHIP_SUB_CUSTOMER_ID, PARENT_PRODUCT)T2 ON T1.MASTER_CUSTOMER_ID = T2.SHIP_MASTER_CUSTOMER_ID))

I did this one. I am getting result ..sum and max date with duplicates max date and ID.


#7


#8

Sure, that makes sense, since your group by includes three columns. You can have multiple results for a given Master_Customer_ID.

What should your output look like?


#9

i need to display only one ID rather than duplicates


#10

OK -- then only group by the Master_Customer_ID.