Solved

Condense SQL Results to Single Rows Per Vendor

Posted on 2013-11-25
14
365 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

895 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

11 Experts available now in Live!

Get 1:1 Help Now