Solved

Two Select Queries results into a single query

Posted on 2013-06-02
7
275 Views
Last Modified: 2013-06-03
I have two select queries with slightly different Where Clauses.
The difference is that the date is being filtered by dates value in Transactions table and the other query is using the date filter from Stmnledger table.
They are joined by a PK ID so there is a link between the two tables.
Here are the results of the two queries run separately.

 Results 1
Here are the two select statements

SELECT         Professionals.Professionals, Professionals.FirstName, SUM(Transactions.Units) AS Units, SUM(Transactions.Value) AS Value,
                         MONTH(Transactions.TransDate) AS months
FROM            Transactions INNER JOIN
                         Components ON Transactions.Components = Components.Components LEFT OUTER JOIN
                         StmnLedger ON Transactions.StmnLedger = StmnLedger.StmnLedger LEFT OUTER JOIN
                         Professionals ON Transactions.Professionals = Professionals.Professionals
						 
WHERE        (YEAR(Transactions.TransDate) = 2013) AND (Professionals.Initials = 'beb')
GROUP BY Professionals.firstName, Professionals.Initials, Components.CompType, MONTH(Transactions.TransDate), Professionals.Professionals




/*--------------------------------------------------------------------------------------*/
SELECT        Professionals.Professionals, Professionals.FirstName,SUM(Transactions.StmnUnits) AS Stmnunit, 
                         SUM(Transactions.ExtAmt - Transactions.StmnDiscount) AS Stmnvalue, MONTH(StmnLedger.StmnDate) AS Months
						 
                         
FROM            Transactions INNER JOIN
                         Components ON Transactions.Components = Components.Components LEFT OUTER JOIN
                         StmnLedger ON Transactions.StmnLedger = StmnLedger.StmnLedger LEFT OUTER JOIN
                         Professionals ON Transactions.Professionals = Professionals.Professionals
WHERE        (YEAR(StmnLedger.StmnDate) = 2013) AND (Professionals.Initials = 'BEB')
GROUP BY Professionals.firstName, StmnLedger.LedgerType, Transactions.Stage, MONTH(StmnLedger.StmnDate), Professionals.Professionals
order by Months

Open in new window


dataset.txt
0
Comment
Question by:yo_bee
7 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39215289
Just join those two queries together

SELECT A.Month, A.Professionals, A.FirstName, A.Units, A.Value,
   B.StmnUnit, B.StmnValue
FROM (<first query>) AS A
INNER JOIN (<second query>) AS B
   ON A.Professionals = B.Professionals
   AND A.Month = B.Month

Adjust the joining fields if I'm misinterpreting the column but definitely include month
0
 
LVL 9

Expert Comment

by:sarabhai
ID: 39215339
You need to union these two result set by using following query.

SELECT         Professionals.Professionals, Professionals.FirstName, SUM(Transactions.Units) AS Units, SUM(Transactions.Value) AS Value,
                         MONTH(Transactions.TransDate) AS months
FROM            Transactions INNER JOIN
                         Components ON Transactions.Components = Components.Components LEFT OUTER JOIN
                         StmnLedger ON Transactions.StmnLedger = StmnLedger.StmnLedger LEFT OUTER JOIN
                         Professionals ON Transactions.Professionals = Professionals.Professionals
                                     
WHERE        (YEAR(Transactions.TransDate) = 2013) AND (Professionals.Initials = 'beb')
GROUP BY Professionals.firstName, Professionals.Initials, Components.CompType, MONTH(Transactions.TransDate), Professionals.Professionals


UNION ALL


SELECT        Professionals.Professionals, Professionals.FirstName,SUM(Transactions.StmnUnits) ,
                         SUM(Transactions.ExtAmt - Transactions.StmnDiscount) , MONTH(StmnLedger.StmnDate)
                                     
                         
FROM            Transactions INNER JOIN
                         Components ON Transactions.Components = Components.Components LEFT OUTER JOIN
                         StmnLedger ON Transactions.StmnLedger = StmnLedger.StmnLedger LEFT OUTER JOIN
                         Professionals ON Transactions.Professionals = Professionals.Professionals
WHERE        (YEAR(StmnLedger.StmnDate) = 2013) AND (Professionals.Initials = 'BEB')
GROUP BY Professionals.firstName, StmnLedger.LedgerType, Transactions.Stage, MONTH(StmnLedger.StmnDate), Professionals.Professionals
ORDER BY Months
0
 
LVL 9

Expert Comment

by:sarabhai
ID: 39215340
Union all ensures that all the records from both the queries should come in result set.
0
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!

 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39215926
What is exactly your question ?
0
 
LVL 22

Author Comment

by:yo_bee
ID: 39216038
@sarabhai  I tried the Union and it does somewhat the desired results, but rather than putting the data in the correct column name it groups all Units as one column and all Values as another.
Where is should show

5 Records with values in the Units column and 5 Records with values in the StmnUnits column.

UnionAll Img
@ BriCrowe
When joining you stated <Query 1>   How to I know the name of the query?


@Racimo

The Queries are from the same Join Tables, but the Units are summed up by a date field.
The first query uses the Date field from Transactions table while the second query uses date field from StmnLedger Table.
These two tables have a linked PK between so if I pull a record from stmnledger table I can also return values from the Transactions table

 Daigram Img
So my desired results would be to have a final output of.
Profname, Units, Value, StmnUnits, StmnValue, Months.
0
 
LVL 9

Accepted Solution

by:
sarabhai earned 500 total points
ID: 39216098
I think you need result with their column name also to proper understanding.
so here you can check

SELECT         Professionals.Professionals, Professionals.FirstName, SUM(Transactions.Units) AS Units, SUM(Transactions.Value) AS Value,
                        NULL as  Stmnunit,NULL as Stmnvalue, MONTH(Transactions.TransDate) AS months
FROM            Transactions INNER JOIN
                         Components ON Transactions.Components = Components.Components LEFT OUTER JOIN
                         StmnLedger ON Transactions.StmnLedger = StmnLedger.StmnLedger LEFT OUTER JOIN
                         Professionals ON Transactions.Professionals = Professionals.Professionals
                                     
WHERE        (YEAR(Transactions.TransDate) = 2013) AND (Professionals.Initials = 'beb')
GROUP BY Professionals.firstName, Professionals.Initials, Components.CompType, MONTH(Transactions.TransDate), Professionals.Professionals

UNION ALL

SELECT        Professionals.Professionals, Professionals.FirstName,SUM(Transactions.StmnUnits) AS Stmnunit,
                         NULL AS Units, NULL as Value  ,SUM(Transactions.ExtAmt - Transactions.StmnDiscount) AS Stmnvalue, MONTH(StmnLedger.StmnDate) AS Months
                                     
                         
FROM            Transactions INNER JOIN
                         Components ON Transactions.Components = Components.Components LEFT OUTER JOIN
                         StmnLedger ON Transactions.StmnLedger = StmnLedger.StmnLedger LEFT OUTER JOIN
                         Professionals ON Transactions.Professionals = Professionals.Professionals
WHERE        (YEAR(StmnLedger.StmnDate) = 2013) AND (Professionals.Initials = 'BEB')
GROUP BY Professionals.firstName, StmnLedger.LedgerType, Transactions.Stage, MONTH(StmnLedger.StmnDate), Professionals.Professionals
order by Months
0
 
LVL 22

Author Closing Comment

by:yo_bee
ID: 39216151
Thanks for you help.
Here is my final query.
I added Nulls for the first query for  StmnUnits and StmnValue
Did the same for units and Value in query 2.


SELECT         Professionals.Professionals, Professionals.FirstName, SUM(Transactions.Units) AS Units, SUM(Transactions.Value) AS Value,
                         null as StmnUnits,null as StmnValue,MONTH(Transactions.TransDate) AS months
FROM            Transactions INNER JOIN
                         Components ON Transactions.Components = Components.Components LEFT OUTER JOIN
                         StmnLedger ON Transactions.StmnLedger = StmnLedger.StmnLedger LEFT OUTER JOIN
                         Professionals ON Transactions.Professionals = Professionals.Professionals
                                     
WHERE        (YEAR(Transactions.TransDate) = 2013) AND (Professionals.Initials = 'beb')
GROUP BY Professionals.firstName, Professionals.Initials, Components.CompType, MONTH(Transactions.TransDate), Professionals.Professionals


union all

/*--------------------------------------------------------------------------------------*/
SELECT        Professionals.Professionals, Professionals.FirstName,null as Units, Null as Value,SUM(Transactions.StmnUnits) AS Stmnunit,
                         SUM(Transactions.ExtAmt - Transactions.StmnDiscount) AS Stmnvalue, MONTH(StmnLedger.StmnDate) AS Months
                                     
                         
FROM            Transactions INNER JOIN
                         Components ON Transactions.Components = Components.Components LEFT OUTER JOIN
                         StmnLedger ON Transactions.StmnLedger = StmnLedger.StmnLedger LEFT OUTER JOIN
                         Professionals ON Transactions.Professionals = Professionals.Professionals
WHERE        (YEAR(StmnLedger.StmnDate) = 2013) AND (Professionals.Initials = 'BEB')
GROUP BY Professionals.firstName, StmnLedger.LedgerType, Transactions.Stage, MONTH(StmnLedger.StmnDate), Professionals.Professionals
order by Months
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
export sql results to csv 6 36
SQL SERVER 2008 R2 Could not obtain information about Windows NT group/user 5 36
VB.NET 2008 - SQL Timeout 9 24
SSRS Enable Remote Errors 4 26
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

810 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