• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

Condense SQL Results to Single Rows Per Vendor

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
trbbhm
Asked:
trbbhm
  • 7
  • 7
1 Solution
 
ButlerTechnologyCommented:
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
 
trbbhmAuthor Commented:
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
 
ButlerTechnologyCommented:
You would need to remove the transaction date as that won't group well.

Tom
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
trbbhmAuthor Commented:
But I NEED the transaction date (yearmonth).
0
 
ButlerTechnologyCommented:
If a vendor has an entry that has two different year month combo, then you will always get multiple entries.

Tom
0
 
trbbhmAuthor Commented:
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
 
ButlerTechnologyCommented:
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
 
trbbhmAuthor Commented:
(left(ASC_BR.dbo.ITMAUDIT.ItemNum,2)) provides the div.

I tried the modified GROUP BY with same results.
0
 
ButlerTechnologyCommented:
What do the DIN number look like?

Tom
0
 
trbbhmAuthor Commented:
Sorry, I don't understand.  DIN number?
0
 
ButlerTechnologyCommented:
Sorry -- DIV
0
 
trbbhmAuthor Commented:
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
 
ButlerTechnologyCommented:
I am at a lost on this one.  I would think that grouping by the three fields would produce the desired results.

Tom
0
 
trbbhmAuthor Commented:
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now