Solved

Condense SQL Results to Single Rows Per Vendor

Posted on 2013-11-25
14
364 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
  • 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
 

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 500 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Help with SQL Query 23 39
How to calculate iops? 12 27
How to query date ranges with SQL 6 25
SQL query to summarize items per month 5 28
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this article I will describe the Detach & Attach 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.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 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

20 Experts available now in Live!

Get 1:1 Help Now