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

Display data from multiple select statements

Need to be able to query an SQL Database for sales figures from 2013,2014,2015, and 1/1/2016 to 8/30/2016
Ideally I would like to get in return the following columns:
slsperid,CustId, Name, TotalPurch2013,TotalPurch2014,TotalPurch2015,TotalPurch2016

Here is the query I normally would use to return:
slsperid,CustId, Name, TotalPurch

Query:

select b.custid,
b.SlsperId,
B.name,
sum(a.TotInvc) as TotalPurch

from SOShipHeader a,Customer b
where a.custid=B.custid
and a.InvcDate>='1/1/2015'
and a.InvcDate<='12/31/2015'
and a.Status ='C'
and a.cancelled<>'1'
and b.SlsperID like '%'
and a.CustId>='a'
group by b.custid,b.SlsperId,B.name,b.phone,b.fax,b.ClassId
order by b.custid

Your assistance in formatting this query to get the return data set would be greatly appreciated.
0
armgon
Asked:
armgon
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
can't you use your client to report in columns?

from the SQL side, you could group by years simply like this:

select b.custid,
b.SlsperId,
B.name,
sum(a.TotInvc) as TotalPurch, year(a.InvcDate) as InvoiceYear
from SOShipHeader a,Customer b
where a.custid=B.custid 
and a.Status ='C'
and a.cancelled<>'1'
and b.SlsperID like '%'
and a.CustId>='a'
group by b.custid,b.SlsperId,B.name,b.phone,b.fax,b.ClassId, year(a.InvcDate)
order by b.custid

Open in new window

0
 
Scott PletcherSenior DBACommented:
select b.custid,
b.SlsperId,
B.name,
SUM(case when a.InvcDate >= '20130101' AND a.InvcDate < '20140101' then a.TotInvc else 0 end) as TotalPurch2013,
SUM(case when a.InvcDate >= '20140101' AND a.InvcDate < '20150101' then a.TotInvc else 0 end) as TotalPurch2014,
SUM(case when a.InvcDate >= '20150101' AND a.InvcDate < '20160101' then a.TotInvc else 0 end) as TotalPurch2015,
SUM(case when a.InvcDate >= '20160101' AND a.InvcDate < '20160901' then a.TotInvc else 0 end) as TotalPurch2016

from SOShipHeader a,Customer b
where a.custid=B.custid
and a.InvcDate>='20130101'
and a.InvcDate<'20160901'
and a.Status ='C'
and a.cancelled<>'1'
and b.SlsperID like '%'
and a.CustId>='a'
group by b.custid,b.SlsperId,B.name
order by b.custid,b.SlsperId
0
 
armgonAuthor Commented:
I cannot thank you enough. Worked simply by copy and pasting. True out of the box solution.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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