Solved

Two Select Queries results into a single query

Posted on 2013-06-02
7
269 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
Comment Utility
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
Comment Utility
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
Comment Utility
Union all ensures that all the records from both the queries should come in result set.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
What is exactly your question ?
0
 
LVL 21

Author Comment

by:yo_bee
Comment Utility
@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
Comment Utility
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 21

Author Closing Comment

by:yo_bee
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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 …
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…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

772 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

10 Experts available now in Live!

Get 1:1 Help Now