Databases
StackOverflow2013
Postsdbo
POIn-memory table multiple INNER JOIN
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIn-memory table multiple INNER JOIN
    primarykey
    data
    text
    <p>I am trying to update a report in SSRS. The query is build by another colleague using a stored procedure, whom has left the project and can't contact anymore. He used an in-memory table for all batches in order to add some aggregations.</p> <p>The columns as outputs are:</p> <ul> <li>BatchID </li> <li>TransformDate </li> <li>SourceSystem </li> <li>CompanyType </li> <li>MAX(B.FooterLines) AS FooterTotalLines </li> <li>MAX(SRC.TotalLines) AS SourceTotalLines</li> <li>MAX(BalancingLinesTable.BalancingLines) AS BalancingLines</li> <li>SUM(STG.TotalLines) AS StagingTotalLines </li> <li>(CAST(STG.Company AS int) * 1000000) + B.BatchID AS CompanyCode</li> </ul> <p>However I need to add an extra column called Balancing lines. I developed the query for it and it works:</p> <pre><code>SELECT COUNT(0) as "Balancing lines" FROM [Source].[Staging].[TransactionLine] T INNER JOIN [Source].[Staging].HeaderLine H ON T.HeaderID = H.HeaderID where H.BatchID = 1234* and H.SourceSystemInstance = 'ABC' and AccountNumber = '98765' *1234 should be variable -&gt; B.BatchID and also the SourseSystemInstance 'ABC'-&gt; B.SourceSystem </code></pre> <p>But, the stored procedure written by my collegue is still foreign to me. Do you know how to 'add' my working query to his working stored procedure?</p> <p>For clarity the table used:</p> <ul> <li>@batches B </li> <li>HeaderLine H </li> <li>FooterLine FL </li> <li>'INNER JOIN table' BalancingLinesTable </li> <li>'INNER JOIN table' SRC </li> <li>'LEFT JOIN table' STG</li> </ul> <p>The stored procedure:</p> <pre><code>USE [Source] ALTER PROCEDURE [dbo].[usp_GetSomeCounts] ( @StartAt datetime ) AS BEGIN SET NOCOUNT ON; -- Set a default for start date if none is specified SELECT @StartAt = ISNULL(@StartAt, DATEADD(dd, -7, GETDATE())) -- Use an in-memory table for all batches for the specified period DECLARE @batches TABLE(BatchID int, TransformDate datetime, HeaderID nvarchar(36), CompanyType nvarchar(30), SourceSystem nvarchar(4), FooterLines int) -- Fill in-memory table batches INSERT INTO @batches ( BatchID ,TransformDate ,HeaderID ,CompanyType ,SourceSystem ,FooterLines ) SELECT H.BatchID ,H.TransformDate ,H.HeaderID ,CompanyTypeID ,H.SourceSystemInstance ,ISNULL(FL.TotalTransactionLines, 0) FROM Staging.HeaderLine H WITH (NOLOCK) INNER JOIN Staging.FooterLine FL WITH (NOLOCK) ON H.HeaderID = FL.HeaderID WHERE H.BatchDate &gt;= @StartAt ORDER BY H.BatchID /* Using in-memory table 'batches', count all valid transactions in both Source and Staging databases. Include the footer totals */ SELECT B.BatchID , B.TransformDate , B.SourceSystem , B.CompanyType , MAX(B.FooterLines) AS FooterTotalLines , MAX(SRC.TotalLines) AS SourceTotalLines , MAX(BalancingLinesTable.BalancingLines) AS BalancingLines , SUM(STG.TotalLines) AS StagingTotalLines , (CAST(STG.Company AS int) * 1000000) + B.BatchID AS CompanyCode FROM @batches B INNER JOIN ( SELECT B.BatchID ,B.HeaderID ,COUNT(0) as BalancingLines FROM @batches B -- this 'inner join table' BalancingLinesTable is what I added INNER JOIN Staging.TransactionLine T WITH (NOLOCK) ON B.HeaderID = T.HeaderID INNER JOIN Staging.HeaderLine H WITH (NOLOCK) ON T.HeaderID = H.HeaderID WHERE H.BatchID = B.BatchID AND H.SourceSystemInstance = B.SourceSystem AND AccountNumber = 399990 GROUP BY B.BatchID ,B.HeaderID ) BalancingLinesTable ON B.BatchID = BalancingLinesTable.BatchID INNER JOIN ( SELECT B.BatchID ,B.HeaderID ,COUNT(0) AS TotalLines FROM @batches B INNER JOIN Staging.TransactionLine T WITH (NOLOCK) ON B.HeaderID = T.HeaderID WHERE T.LineStatus = 'N' GROUP BY B.BatchID ,B.HeaderID ) SRC ON B.BatchID = SRC.BatchID LEFT JOIN ( SELECT B.BatchID ,B.HeaderID ,MT.Company ,COUNT(0) AS TotalLines FROM @batches B INNER JOIN [Production].[dbo].[Mutated_Transactions] MT WITH (NOLOCK) ON B.HeaderID = MT.HeaderID WHERE MT.RowHasError = 'N' GROUP BY B.BatchID ,B.HeaderID ,MT.Company) STG ON SRC.BatchID = STG.BatchID AND SRC.HeaderID = STG.HeaderID GROUP BY B.BatchID ,B.TransformDate ,B.SourceSystem ,B.CompanyType ,STG.Company ORDER BY B.TransformDate desc ,B.BatchID </code></pre> <p>END</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDanny
    1. USDanny
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POIn-memory table multiple INNER JOIN
      1. This table or related slice is empty.
      1. VTClose
    2. VO
      singulars
      1. POIn-memory table multiple INNER JOIN
      1. This table or related slice is empty.
      1. VTClose
    1. COMaybe ask your colleague?
      singulars
      1. POIn-memory table multiple INNER JOIN
      1. USpeterm
    2. COI can come up with a BS story. But this is a technical question that can be answered without knowing the 'business side'. Fact is I have trouble reaching him...
      singulars
      1. POIn-memory table multiple INNER JOIN
      1. USDanny
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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