Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Condense SQL Results to Single Rows Per Vendor

Posted on 2013-11-25
14
Medium Priority
?
390 Views
Last Modified: 2013-11-25
The following code....

select ca1.div
	,vendornum,LEFT(asc_br.dbo.ITMAUDIT.TransDateYYYYMMDD,6)as yearmth,SUM(PriceExtension) as rtl
      ,sum(ExtendedCost) as cost
      ,sum(ItemPieces) as qty
from [ASC_BR].[dbo].ITMAUDIT
cross apply (
select
              case 
                    when convert(numeric,left(ASC_BR.dbo.ITMAUDIT.ItemNum,2)) between 10 and 19 then '10'
                    when convert(numeric,left(ASC_BR.dbo.ITMAUDIT.ItemNum,2)) between 20 and 29 then '20'
                    when convert(numeric,left(ASC_BR.dbo.ITMAUDIT.ItemNum,2)) between 30 and 39 then '30'
                    when convert(numeric,left(ASC_BR.dbo.ITMAUDIT.ItemNum,2)) between 40 and 49 then '40'
                    when convert(numeric,left(ASC_BR.dbo.ITMAUDIT.ItemNum,2)) between 50 and 59 then '50'
                    else
                          convert(numeric,left(ASC_BR.dbo.ITMAUDIT.ItemNum,2)) 
              end as div
) as ca1
where ISNUMERIC(left(ASC_BR.dbo.ITMAUDIT.ItemNum,2))=1
and DocType in ('SLS','SLC','CRD','LAF')

Open in new window



....results in the following data.....

DIV     VENDORNUM     YEARMTH     RTL     COST     QTY
10	496368	201012	3165.00	-2462.70	-1
10	13	201212	1320.00	-1200.00	-1
10	12441	200908	0.00	0.00	0
10	12441	200910	1017.00	-644.40	-1
10	12441	201208	2475.00	-1287.90	-1
10	69847	201212	8160.00	-3643.00	-3
10	82581	201109	1350.00	-675.00	-1
10	101710	201112	2567.50	-1111.38	-2
10	122250	200905	7500.00	-3368.04	-2
10	122250	200907	105117.00	-55123.71	-9
10	122250	201012	12791.00	-8754.50	-3
10	140910	201105	4950.00	-2475.00	-1
10	167367	201011	10952.00	-6774.24	-2
10	182041	201207	0.00	0.00	0
10	200347	200910	804.00	-556.00	-1
10	200347	201006	1350.00	-604.00	-1
10	200347	201212	6120.00	-1863.00	-2
10	200347	201309	4983.00	-1441.00	-2
10	206969	200904	470.00	-188.00	-1
10	206969	201011	1085.00	-620.00	-1
10	206969	201303	1650.00	-1528.25	-1
10	218591	201201	2860.00	-2600.00	-1
10	218591	201203	1990.00	-802.96	-1
10	238089	201205	540.00	-275.00	-1
10	250931	201210	162.50	-135.00	-1
10	263067	201101	24322.65	15648.00	2
10	375400	201106	2070.00	-1007.50	-1
10	406167	201106	2000.00	-902.00	-1
10	449946	201107	2850.00	-1300.00	-1
10	459305	200910	570.00	-311.00	-1
10	459305	201102	869.40	-536.00	-1
10	459305	201201	1325.00	-529.00	-1
10	1368	201104	630.00	-280.00	-1
10	12441	200804	3307.50	-1583.10	-2
10	12441	200807	6000.00	-389.70	-1
10	12441	201209	0.00	0.00	0
10	21924	201102	1300.00	-1375.00	-1
10	54687	201005	748.01	-321.08	-1
10	54687	201106	1100.00	-670.40	-1
10	69847	201209	3750.00	-2940.00	-1
10	82581	200906	3465.00	-1748.83	-1
10	82581	201301	875.00	-789.00	-1
10	94223	200807	10500.00	4211.00	1
10	98753	200904	1298.46	-575.00	-1
10	140910	201103	4761.00	-2400.00	-1
10	140910	201202	5180.00	0.00	0
10	148289	201207	2750.00	-2471.00	-1
10	148289	201210	21150.00	-8426.00	-1

Open in new window


You can see where the same [VendorNum] is sometimes repeated for the same [Div].  How can I get this SQL query to condense the results so that there is one row per [Div] per [VendorNum]?
0
Comment
Question by:trbbhm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
14 Comments
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39674994
You can use the group feature:
Group By Div, VendorNum

Open in new window


You will need to decide what to do with the YearMonth as it should be removed otherwise the group by won't work.

Tom
0
 

Author Comment

by:trbbhm
ID: 39675049
I'm sorry!  I accidentally left off a line of the query as follows:

group by ca1.div,(left(ASC_BR.dbo.ITMAUDIT.ItemNum,2)) ,vendornum,LEFT(asc_br.dbo.ITMAUDIT.TransDateYYYYMMDD,6)

Open in new window


So the GROUP BY is in there, but it's still giving me multiple lines per [vendornum] per [div].
0
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39675079
You would need to remove the transaction date as that won't group well.

Tom
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:trbbhm
ID: 39675096
But I NEED the transaction date (yearmonth).
0
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39675135
If a vendor has an entry that has two different year month combo, then you will always get multiple entries.

Tom
0
 

Author Comment

by:trbbhm
ID: 39675188
I guess what I'm talking about is there are instances where you have the same [vendornum], with the same [yearmth], and the same [div].  I want to see if I can get these instances condensed so that there is only one row per this combination.

In other words, my current query may produce:

10     10111     201211     434.22     453.24
10     10111     201212     434.24     422.43
10     12345     201211     123.45     323.45
10     12345     201211     432.34     543.45
10     12345     201211     543.24     453.45
10     12345     201212     533.42     623.44

I would want to condense the three rows above that appear for vendor 12345, year-month 201211 so that there is only one row for this particular year-month.
0
 
LVL 6

Accepted Solution

by:
ButlerTechnology earned 2000 total points
ID: 39675216
Weird -- that should work.
What is this for: (left(ASC_BR.dbo.ITMAUDIT.ItemNum,2))
I think this might be the issue.

Try this:
group by ca1.div ,vendornum,LEFT(asc_br.dbo.ITMAUDIT.TransDateYYYYMMDD,6)

Open in new window

0
 

Author Comment

by:trbbhm
ID: 39675322
(left(ASC_BR.dbo.ITMAUDIT.ItemNum,2)) provides the div.

I tried the modified GROUP BY with same results.
0
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39675435
What do the DIN number look like?

Tom
0
 

Author Comment

by:trbbhm
ID: 39675478
Sorry, I don't understand.  DIN number?
0
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39675507
Sorry -- DIV
0
 

Author Comment

by:trbbhm
ID: 39675584
The DIV is a truncation of a string value, the first two digits of which make up the DIV:

select
              case 
                    when convert(numeric,left(ASC_BR.dbo.ITMAUDIT.ItemNum,2)) between 10 and 19 then '10'
                    when convert(numeric,left(ASC_BR.dbo.ITMAUDIT.ItemNum,2)) between 20 and 29 then '20'
                    when convert(numeric,left(ASC_BR.dbo.ITMAUDIT.ItemNum,2)) between 30 and 39 then '30'
                    when convert(numeric,left(ASC_BR.dbo.ITMAUDIT.ItemNum,2)) between 40 and 49 then '40'
                    when convert(numeric,left(ASC_BR.dbo.ITMAUDIT.ItemNum,2)) between 50 and 59 then '50'
                    else
                          convert(numeric,left(ASC_BR.dbo.ITMAUDIT.ItemNum,2)) 
              end as div
) as ca1

Open in new window

0
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39675614
I am at a lost on this one.  I would think that grouping by the three fields would produce the desired results.

Tom
0
 

Author Closing Comment

by:trbbhm
ID: 39675647
I was not reading your response correctly!  You correctly inquired about that additional GROUP BY value:

What is this for: (left(ASC_BR.dbo.ITMAUDIT.ItemNum,2))

....which, when removed, did the trick!  I was basically doubling the work, grouping by both DIV and value above, which are the same things.

Thanks!!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

618 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