Solved

Two Select Queries results into a single query

Posted on 2013-06-02
7
285 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
[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
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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
LVL 23

Expert Comment

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

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 23

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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

729 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