Union Query and Case Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Union Query and Case Question

Author

Topic

Potnik
Starting Member

2 Posts

Posted - 2007-01-04 : 19:52:34

This is my first post and I am doing my best to follow all of the rules that have been stated in the sticky thread within this forum. I have included my code and I am sure that it is not the best as I am just really starting to get into SQL as I am more profecient with Access. I believe what I am attempting to do can be done, it is just I am not sure how to currently achieve it.

When I run my code this is the output that I receive:
Region Quantity
Americas 43325
Americas 303138
Americas 2 8440
Americas 2 11996
Asia 29294
Asia 45763
Asia 2 811
Asia 2 13363
EMEA 290782
EMEA 2 17134

What I would like the code to be able to do is to consolidate any of the duplicate regions that are created. I believe the reason why it is creating duplicates is that I tell it to order by the Region though I am sure that it orders it by the original value for the Region and not the value that I tell it to convert to. So a perfect scenario would be that the information would display out like this:

Region Quantity
Americas 346363
Americas 2 20436
Asia 75057
Asia 2 14174
EMEA 290782
EMEA 2 17134

Now if that can be done that would be wonderful, but then my next question is there anyway that I would be able to have the information display like this:

Region Quantity 1 Quantity 2
Americas 346363 20436
Asia 75057 14174
EMEA 290782 17134

Again I am sorry if I haven't follow any of the rules that have been put in place if I have missed anything I will add the information when requested.

I believe that I might need to follow something similar to this example and that the union might not even be what I needed. Sorry for the super long post especially seeing how it is my first time.
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72743[/url]

Although that seems to help me get the Quantity 1 and 2 seperated I am still running into the issue of having the multiple Americas.

Declare @StartingDate DateTime ,
@EndingDate DateTime

Set @StartingDate = '12/03/2006'
Set @EndingDate = '01/04/2007'

SELECT Case
when ARAFC.Region = 'North America' then 'Americas'
when ARAFC.Region = 'Latin America' then 'Americas'
when ARAFC.Region = 'APAC' then 'Asia'
when ARAFC.Region = 'Far East' then 'Asia'
Else 'EMEA'
end as 'Region', sum(SV.Quantity) as Quantity

FROM ShipmentView SV (nolock) INNER JOIN
ARandAFCSite ARAFC (nolock) ON ARAFC.ARName = SV.FromSiteName
Left Join ProductMaster PM (nolock) on SV.PartNumber = PM.PartNumber

where SV.EventDate between @StartingDate and @EndingDate
and SV.EventtypeID = 2700
and SV.ordertype = 1
and Left(SV.ProductFamilyName, 3) in
('Win'
)

group by ARAFC.Region

Union

SELECT Case
when ARAFC.Region = 'North America' then 'Americas 2'
when ARAFC.Region = 'Latin America' then 'Americas 2'
when ARAFC.Region = 'APAC' then 'Asia 2'
when ARAFC.Region = 'Far East' then 'Asia 2'
Else 'EMEA 2'
end as 'Region', sum(SV.Quantity) as Quantity

FROM ShipmentView SV (nolock) INNER JOIN
ARandAFCSite ARAFC (nolock) ON ARAFC.ARName = SV.FromSiteName
Left Join ProductMaster PM (nolock) on SV.PartNumber = PM.PartNumber

where SV.EventDate between @StartingDate and @EndingDate
and SV.EventtypeID = 2700
and SV.ordertype = 1
and Left(SV.ProductFamilyName, 6) in
('Office'
)

group by ARAFC.Region

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-05 : 03:59:13

[code]-- prepare test data
declare @t table (region varchar(20), quantity int)

insert @t
select 'Americas', 346363 union all
select 'Americas 2', 20436 union all
select 'Asia', 75057 union all
select 'Asia 2', 14174 union all
select 'EMEA', 290782 union all
select 'EMEA 2', 17134

-- show the result
select reg as Region,
sum(q1) as 'Quantity 1',
sum(q2) as 'Quantity 2'
from (
select left(region, len(region) - 2) as reg,
0 as q1,
quantity as q2
from @t
where right(region, 2) = ' 2'
union all
select region,
quantity,
0
from @t
where right(region, 2) <> ' 2'
) d
group by reg
order by reg[/code]

Peter Larsson
Helsingborg, Sweden

Potnik
Starting Member

2 Posts

Posted - 2007-01-11 : 18:55:41

Thanks a bunch.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources