Solved

Display data from multiple select statements

Posted on 2016-09-14
3
55 Views
Last Modified: 2016-09-15
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
Comment
Question by:armgon
3 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41798469
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
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 41798518
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
 

Author Closing Comment

by:armgon
ID: 41799854
I cannot thank you enough. Worked simply by copy and pasting. True out of the box solution.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

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.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

910 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

17 Experts available now in Live!

Get 1:1 Help Now