How do I combine records

Question

How do I combine records

ramadesai108

SSChampion

Points: 10609
More actions
December 26, 2008 at 7:27 am

#94623

Hi All,
Here is a query I am using that Shows the following records:
CAROLYN
CAROLYN -OPSX
CAROLYN - PICERNE
CAROLYN - VEN
CAROLYN -HMS
CAROLYN -NON
CAROLYN -WSTDL
I want to combine the result into one record that shows the name as 'CAROLYN'.
SELECT
CASE SalesMan1,
SUM(grand_inv_total) as TotalSales,
DATEPART(mm,CONVERT(DATETIME,DateEntered,106)) as Month
FROM Sales WITH (NOLOCK)
WHERE SUBSTRING(DateEntered,1,4) = 2008
AND SalesMan1 <> ''
GROUP BY SalesMan1, DATEPART(mm,CONVERT(DATETIME,DateEntered,106))
ORDER BY SalesMan1, DATEPART(mm,CONVERT(DATETIME,DateEntered,106))
Thank you.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736059 More actions · Answer 1

I'm not sure what you mean here. You haven't provided info that would help us write a query. Is this the salesman field? What should the result look like?

Also, why use datepart in the column list and substring in the WHERE clause? Doesn't make sense. You'd want YEAR or DATEPART in the WHERE clause as well.

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 2

If I understand what you are trying to do, you will need to use the charindex function to find the location of the "-" and the substring function to "trim" off the "-" and all that is following. Check those out in BOL and post back with any problems you have with the query.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

ramadesai108 SSChampion Points: 10609 More actions · Answer 3

Here is the correct query:

SELECT

SalesMan1,

SUM(grand_inv_total) as TotalSales,

DATEPART(mm,CONVERT(DATETIME,DateEntered,106)) as Month

FROM Sales WITH (NOLOCK)

WHERE SUBSTRING(DateEntered,1,4) = 2008

AND SalesMan1 <> ''

GROUP BY SalesMan1, DATEPART(mm,CONVERT(DATETIME,DateEntered,106))

ORDER BY SalesMan1, DATEPART(mm,CONVERT(DATETIME,DateEntered,106))

Here the DateEntered is a varchar field and not a date field so I use convert.

I am trying to get SalesMan, sum of total and month. But the name of the same sales man is as follows:

CAROLYN

CAROLYN -OPSX

CAROLYN - PICERNE

CAROLYN - VEN

CAROLYN -HMS

CAROLYN -NON

CAROLYN -WSTDL

I want to combine the result into one record.

I tried using CHARINDEX as follows but it give me multiple records

SELECT

CASE WHEN CHARINDEX('-',SalesMan1) <> 0 THEN SUBSTRING(SalesMan1,1,CHARINDEX('-',SalesMan1)-2)

ELSE SalesMan1 END,

SUM(grand_inv_total) as TotalSales, DATEPART(mm,CONVERT(DATETIME,DateEntered,106)) as Month

FROM Sales WITH (NOLOCK)

WHERE SUBSTRING(DateEntered,1,4) = 2008

AND SalesMan1 <> ''

GROUP BY SalesMan1, DATEPART(mm,CONVERT(DATETIME,DateEntered,106))

ORDER BY SalesMan1, DATEPART(mm,CONVERT(DATETIME,DateEntered,106))

Thanks.

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 4

If you add the case statement for Salesman1 in your group by statement you should be all set.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

ramadesai108 SSChampion Points: 10609 More actions · Answer 5

Hi David,

I previsouly added the case statement in the group by, but forgot to add it in the order by and that's where the problem was. I have now resolved the problem.

Thanks and have a great day to all.

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 6

I don't think that not having it in the order by would cause you to have duplicate records. It is the grouping which is going to eliminate the dupes.

Glad you were able to get it working.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Steve Jones - SSC Editor SSC Guru Points: 736059 More actions · Answer 7

Putting it in the ORDER BY will move the duplicates, not eliminate them. It needs to be in the GROUP BY, and you want to SUBSTRING back to the string that you need in the GROUP BY to eliminate duplicates.

Also, your WHERE clause might cause you issues if someone enters 12252008 or 25122008 at some point instead of 20081225. Be aware of that.

Jeff Moden SSC Guru Points: 1004279 More actions · Answer 8

ramadesai108 (12/26/2008)
Hi David,
I previsouly added the case statement in the group by, but forgot to add it in the order by and that's where the problem was. I have now resolved the problem.
Thanks and have a great day to all.

Please post your solution so that other's may learn from it. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)