Solved

Display data from multiple select statements

Posted on 2016-09-14
3
70 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
[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
3 Comments
 
LVL 70

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:
Scott Pletcher 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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 15 55
Display field if column exists 7 36
Need SSIS project 2 31
Trouble installing msi file with msiexe.exe 2 21
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

696 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