StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to Join two different queries in SQL Server
text
Body
copied!<p>I have a stored procedure and a get date range query.I want to join both the queries that it show one resultset only. Procedure and query are given below..</p> <pre><code>CREATE PROCEDURE ak_ReportData_CMTDB @BankAccountID uniqueidentifier, @StartDate datetime, @EndDate datetime AS ---------------------------------------------------------------------------- -- Name: ak_ReportData_CMTDB -- Purpose: Returns the daily activity on the passed bank account for the -- passed date range -- Input: BankAccountID - The bank account to report on -- StartDate, EndDate - The date range to report on -- Output: Recordset containing activity -- Return: 0 = Sucess -- >0 = Error ---------------------------------------------------------------------------- SET NOCOUNT ON DECLARE @OpeningBalance money -- Calculate opening balance for deposits SELECT NULL AS fDepositDate, SUM(CASE fType WHEN 0 THEN fAmount -- Deposit (Add amount) WHEN 1 THEN (CASE fSide WHEN 1 THEN fAmount * -1 -- Transfer Out (Subtract amount) WHEN 2 THEN fAmount -- Transfer In (Add amount) ELSE 0 END) WHEN 2 THEN fAmount -- Credit Card (Add amount) WHEN 3 THEN fAmount * -1 -- Withdrawal (Subtract amount) ELSE 0 END) As fNetChange FROM tBADepositMaster WHERE fBankAccountID = @BankAccountID AND fDepositDate < @StartDate AND fVoid = 0 UNION ALL -- Calculate NetChange for deposits for each date SELECT fDepositDate, SUM(CASE fType WHEN 0 THEN fAmount -- Deposit (Add amount) WHEN 1 THEN (CASE fSide WHEN 1 THEN fAmount * -1 -- Transfer Out (Subtract amount) WHEN 2 THEN fAmount -- Transfer In (Add amount) ELSE 0 END) WHEN 2 THEN fAmount -- Credit Card (Add amount) WHEN 3 THEN fAmount * -1 -- Withdrawal (Subtract amount) ELSE 0 END) AS fNetChange FROM tBADepositMaster WHERE fBankAccountID = @BankAccountID AND fDepositDate BETWEEN @StartDate AND @EndDate AND fVoid = 0 GROUP BY fDepositDate UNION ALL -- Calculate opening balance for checks SELECT NULL As fDepositDate, SUM(fAmount) * -1 AS fNetChange FROM tBACheck WHERE fBankAccountID = @BankAccountID AND fDate < @StartDate AND fVoid = 0 UNION ALL -- Calculate NetChange for checks for each date SELECT fDate As fDepositDate, SUM(fAmount) * -1 AS fNetChange FROM tBACheck WHERE fBankAccountID = @BankAccountID AND fDate BETWEEN @StartDate AND @EndDate AND fVoid = 0 GROUP BY fDate ORDER BY fDepositDate </code></pre> <p>And get date range query</p> <pre><code>;WITH tmpinfo(tmpdt) AS (SELECT @StartDate UNION ALL SELECT tmpdt + 1 FROM tmpinfo WHERE tmpdt < @EndDate ) SELECT * FROM tmpinfo OPTION (MAXRECURSION 0); </code></pre> <p>Is it possible to join these two queries in single query?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload