CODEWITHSUNDEEP
sqlsql-servert-sqlcommon-table-expression
Jo Jo
Jo Jo

Reputation: 73

UNION ALL various temporary tables (issue with FROM (create table .... ))

I am trying to union all a series of invoiced and non-invoiced data. Each batch of data (invoiced/non-invoiced) retrieves data from various temporary tables and I want to bring all the data by assigning 1 to invoiced and 0 to non-invoiced so that when I export the data in Excel I can filter out based on that criteria.

Now, I am a bit unsure how to use UNION ALL to bring all that data together. I have posted a snippet of the query. My understanding is that use UNION all at the end of my current query and bring the second select (for the non-invoiced data in). Another issue I am having is that writing ... from ( created table ...) doesn't seem to be correct. Any ideas? 

WITH CTE_Invoiced (DateCreatedInvoiced, [Destination Country], [Destination Depot]) AS
(SELECT DateCreatedInvoiced,
                DestinationCountry,
                DestinationDepot
        FROM    (CREATE TABLE #tempResults
             (
                          [DateCreatedInvoiced] DATETIME NOT NULL,
                          [Destination Country] VARCHAR (15) NOT NULL,
                          [Destination Depot]   VARCHAR (15) NOT NULL
INSERT INTO #tempResults
SELECT
-- DISTINCT
MAX(CAST(ME.InvoicedDate AS DATE)) AS [Invoice Date1],
MIN(CR.DestinationCountry)         AS [Destination Country],
MIN(t1.DestinationDepot)           AS [Destination Depot],
FROM   dbo.movMovement AS MM
       INNER JOIN dbo.MALExport AS ME
               ON MM.MovementRef = ME.MovementReference
       INNER JOIN dbo.movConLink AS MCL
               ON ME.ConsignmentReference = MCL.ConsignmentReference
                  AND MCL.MovementID = MM.MovementID
       INNER JOIN dbo.cgtRoute AS CR
               ON CR.RouteID = MCL.CMRRouteID
       LEFT JOIN #tempCompare1 t1
              ON t1.MovementID = MM.MovementID --this is a temporary table used in calculations
       LEFT JOIN dbo.movUnit AS MU
              ON MU.UnitID = MM.TrailerID
WHERE  cr.DestinationCountry <> 'SF'
       AND CR.DestinationDepot <> 'BSB'
       AND ( MM.SailingDateTime BETWEEN @startDate AND @endDate )
GROUP  BY MM.MovementRef,
          MM.MovementID
ORDER  BY mm.MovementRef;

CREATE INDEX IDX_tempresults
  ON #tempResults ([Movement Reference])

Upvotes: 0

Views: 71

Answers (1)

Rigerta
Rigerta

Reputation: 4039

You initially create the temporary table and fill it with data. Then you can SELECT from it.

So, what you need to do is this:

 CREATE TABLE #tempResults (
                                [DateCreatedInvoiced] DATETIME NOT NULL,
                                [Destination Country] VARCHAR (15) NOT NULL,
                                [Destination Depot] VARCHAR (15) NOT NULL
                            )

Then you insert into this table:

INSERT INTO #tempResults
SELECT Max(Cast(ME.InvoicedDate as Date)) AS [Invoice Date1] ,
       MIN(CR.DestinationCountry) AS [Destination Country] ,
       MIN(t1.DestinationDepot) AS [Destination Depot]
FROM    dbo.movMovement AS MM
            INNER JOIN dbo.MALExport AS ME ON MM.MovementRef = ME.MovementReference
            INNER JOIN dbo.movConLink AS MCL ON ME.ConsignmentReference = MCL.ConsignmentReference AND MCL.MovementID = MM.MovementID
            INNER JOIN dbo.cgtRoute AS CR ON CR.RouteID = MCL.CMRRouteID
            LEFT  JOIN #tempCompare1 t1 ON t1.MovementID = MM.MovementID --this is a temporary table used in calculations
            LEFT  JOIN dbo.movUnit AS MU ON MU.UnitID = MM.TrailerID
 WHERE   cr.DestinationCountry <> 'SF'
    AND CR.DestinationDepot <> 'BSB'
    AND ( MM.SailingDateTime BETWEEN @startDate AND @endDate )
 GROUP BY MM.MovementRef, MM.MovementID
 ORDER BY mm.MovementRef

You can additionally check what you need, by selecting from this table:

SELECT [DateCreatedInvoiced],
       [Destination Country],
       [Destination Depot]
FROM #tempResults

In this case you could do it without using a CTE.

Upvotes: 2

Related Questions