74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.
With new material every week, we'll make sure that you stay ahead of the game.
Course Of The Month
4 days, 23 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Display data from multiple select statements
Posted on 2016-09-14
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,TotalPurch2
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
order by b.custid
Your assistance in formatting this query to get the return data set would be greatly appreciated.
Ideally I would like to get in return the following columns:
slsperid,CustId, Name, TotalPurch2013,TotalPurch2
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
order by b.custid
Your assistance in formatting this query to get the return data set would be greatly appreciated.
[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
Active today
can't you use your client to report in columns?
from the SQL side, you could group by years simply like this:
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
Active 2 days ago
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
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
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes. We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses
Course of the Month4 days, 23 hours left to enroll
688 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.