Solved

Two Select Queries results into a single query

Posted on 2013-06-02
7
280 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Table where row act as column 11 67
The AZure backup problem 11 51
SQL Server 2008 Std. License Key owner or vendor 4 53
SQL Syntax 6 36
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

679 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