Solved

Condense SQL Results to Single Rows Per Vendor

Posted on 2013-11-25
14
371 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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
 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to count the days a record spends in a step 21 55
Disable TLS1.0 on Win 2012 server 7 57
SQL- GROUP BY 4 22
Split string into 3 separate fields 5 19
In this article I will describe the Backup & Restore 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.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

763 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