asked on

SQL 2008 UNION ALL

Hello,
Can you please help.
I need to union the headers with the rest of the statement.
Please see attached

thank you,
UNION-ALL.txt

Scott Pletcher

DECLARE @Pieces Varchar(2000), @OrderNumber int
DECLARE  @FinalizedOrdersPieces TABLE(InvoiceNumber int,OrderNo int,OrderDate DateTime,AccountNumber int,Caller Varchar(255),Reference Varchar(50), 
PickupCompanyName Varchar(255),PickupStreet Varchar(100),PickupUnit Varchar(50),PickupPostalCode Varchar(11),PickupZoneID Varchar(50),PickupCity Varchar(50),PickupProvince Varchar(2),pickupContact Varchar(50),pickupPhone Varchar(50),
DeliveryCompanyName Varchar(255),DeliveryStreet Varchar(100),DeliveryUnit Varchar(50),DeliveryPostalcode Varchar(11),DeliveryZoneID Varchar(50),
DeliveryCity Varchar(50),DeliveryProvince Varchar(2),DeliveryContact Varchar(50),DeliveryPhone Varchar(50), Vehicle Varchar(50),PieceCount int,Weight real,ServiceType Varchar(50), 
DeliveredTime Varchar(50),POD Varchar(50),BaseAmount money,FuelAmount money,Subtotalamount money,Totalamount money,Pieces Varchar(2000),Empty int)
INSERT INTO @FinalizedOrdersPieces

Select (Select InvoiceNumber From FinalizedOrdersInvoices where FinalizedOrdersInvoices.OrderNo = Finalizedorders.OrderNo) AS [InvoiceNumber],OrderNo,OrderDate,AccountNumber,Caller,Reference, 
PickupCompanyName,PickupStreet,PickupUnit,PickupPostalCode,(Select  Convert (Varchar,Description) from Zones where ZoneID = Finalizedorders.PickupZoneID) AS [From Zone], PickupCity,PickupProvince,pickupContact,pickupPhone,
DeliveryCompanyName,DeliveryStreet,DeliveryUnit,DeliveryPostalcode,(Select  Convert (Varchar,Description) from Zones where ZoneID = Finalizedorders.DeliveryZoneID) AS [To Zone], DeliveryCity,DeliveryProvince,DeliveryContact,DeliveryPhone,
(Select Description From VehicleTypes where VehicleTypes.VehicletypeID = finalizedorders.VehicletypeID) AS [Vehicle],(Select Convert (Varchar,Count (PieceNo)) from FinalizedOrderPackages where FinalizedOrderPackages.OrderNO = Finalizedorders.Orderno) AS [PieceCount],
Weight,(Select Description from Servicetypes where Servicetypes.ServiceTypeId = finalizedorders.ServiceTypeId) AS [Service Type],DeliveredTime,POD,BaseAmount,FuelAmount,Subtotalamount,Totalamount,
(Select Convert (Varchar,Count (PieceNo)) from FinalizedOrderPackages where FinalizedOrderPackages.OrderNO = Finalizedorders.Orderno) AS [Empty],Null
FROM finalizedorders  
WHERE orderdate between (select DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,getdate())), 0)) and 
	(select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()  ), 0)))
	And Accountnumber in (9,10,74,554,845,1713,2310,2313,4556,7640,10046,10065,10066,10067,10068,10069,10076,10135,10145,11149)
ORDER BY FinalizedOrders.OrderNo
SET @OrderNumber = 0
set @Pieces = ''
WHILE (@OrderNumber IS NOT NULL)
BEGIN
	SELECT @OrderNumber = MIN(OrderNo) FROM @FinalizedOrdersPieces WHERE OrderNo > @OrderNumber 
	IF @OrderNumber IS NOT NULL
	BEGIN
		SELECT @Pieces = (Packagetypes.description ) 
		FROM finalizedorderpackages
		INNER Join PackageTypes ON Packagetypes.packagetypeid = finalizedorderpackages.packagetypeid
		WHERE orderno in (SELECT orderno FROM finalizedorders WHERE orderdate between (select DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,getdate())), 0)) and 
		(select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()  ), 0))))
		AND OrderNo = @OrderNumber 
		GROUP BY Packagetypes.description 
		UPDATE @FinalizedOrdersPieces SET Pieces = @Pieces WHERE OrderNo = @OrderNumber 
		SET @Pieces = ''
	END
END


Select   Distinct 'InvoiceNumber' AS [InvoiceNumber],
				'OrderNo' AS [OrderNo],
				'OrderDate' AS [OrderDate],
                'AccountNumber' AS [AccountNumber],
                'Caller' AS [Caller],
                'Reference' AS [Reference],
                'PickupCompanyName' AS [PickupCompanyName],
                'PickupStreet' AS [PickupStreet],
                'PickupUnit' AS [PickupUnit],                
                'PickupPostalCode' AS [PickupPostalCode],                                 
                'PickupZoneID' AS [PickupZoneID],
                'PickupCity' AS [PickupCity],                
                'PickupProvince' AS [PickupProvince],
                'pickupContact' AS [pickupContact],
                'pickupPhone' AS [pickupPhone],
                'DeliveryCompanyName' AS [DeliveryCompanyName],
                'DeliveryStreet' AS [DeliveryStreet],
                'DeliveryUnit' AS [DeliveryUnit],
                'DeliveryPostalcode' AS [DeliveryPostalcode],
                'DeliveryZoneID' AS [DeliveryZoneID],
                'DeliveryCity' AS [DeliveryCity],
                'DeliveryProvince' AS [DeliveryProvince],
                'DeliveryContact' AS [DeliveryContact],
                'DeliveryPhone' AS [DeliveryPhone],
                'Vehicle' AS [Vehicle],
                'PieceCount' AS [PieceCount],
                'Weight' AS [Weight],
                'ServiceType' AS [ServiceType],
                'DeliveredTime' AS [DeliveredTime],
                'POD' AS [POD],
                'BaseAmount' AS [BaseAmount], 
                'FuelAmount' AS [FuelAmount],
                'Subtotalamount' AS [Subtotalamount],
                'Totalamount' AS [Totalamount],
                'Pieces' AS [Pieces] , 
                'NULL' AS [NULL] 
     WHERE 1 = 1
     
Union All      
     
Select * from @FinalizedOrdersPieces

Open in new window

W.E.B

ASKER

Hi Scott,
I don't get any results.

thanks,

PortletPaul

all the columns would have to be varchar to include those headings

DECLARE @Pieces varchar(2000)
      , @OrderNumber int
DECLARE @FinalizedOrdersPieces TABLE
      (
            InvoiceNumber int -- change all columns to varchar

... more

      )
INSERT
      INTO @FinalizedOrdersPieces

      SELECT (
                   SELECT TOP 1
                         convert(varchar,InvoiceNumber)
                   FROM FinalizedOrdersInvoices
                   WHERE FinalizedOrdersInvoices.OrderNo = Finalizedorders.OrderNo
             )
             AS [InvoiceNumber]

... more

FROM finalizedorders
      WHERE orderdate BETWEEN (
                  SELECT
                        DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, -1, GETDATE())), 0)
            )
            AND (
                  SELECT
                        DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
            )
            AND Accountnumber IN (9, 10, 74, 554, 845, 1713, 2310, 2313, 4556, 7640, 10046, 10065, 10066, 10067, 10068, 10069, 10076, 10135, 10145, 11149)
      ORDER BY
            FinalizedOrders.OrderNo

SELECT
      *
FROM @FinalizedOrdersPieces

UNION ALL

SELECT
      'InvoiceNumber'       AS [InvoiceNumber]
    , 'OrderNo'             AS [OrderNo]
    , 'OrderDate'           AS [OrderDate]
    , 'AccountNumber'       AS [AccountNumber]
    , 'Caller'              AS [Caller]
    , 'Reference'           AS [Reference]
    , 'PickupCompanyName'   AS [PickupCompanyName]
    , 'PickupStreet'        AS [PickupStreet]
    , 'PickupUnit'          AS [PickupUnit]
    , 'PickupPostalCode'    AS [PickupPostalCode]
    , 'PickupZoneID'        AS [PickupZoneID]
    , 'PickupCity'          AS [PickupCity]
    , 'PickupProvince'      AS [PickupProvince]
    , 'pickupContact'       AS [pickupContact]
    , 'pickupPhone'         AS [pickupPhone]
    , 'DeliveryCompanyName' AS [DeliveryCompanyName]
    , 'DeliveryStreet'      AS [DeliveryStreet]
    , 'DeliveryUnit'        AS [DeliveryUnit]
    , 'DeliveryPostalcode'  AS [DeliveryPostalcode]
    , 'DeliveryZoneID'      AS [DeliveryZoneID]
    , 'DeliveryCity'        AS [DeliveryCity]
    , 'DeliveryProvince'    AS [DeliveryProvince]
    , 'DeliveryContact'     AS [DeliveryContact]
    , 'DeliveryPhone'       AS [DeliveryPhone]
    , 'Vehicle'             AS [Vehicle]
    , 'PieceCount'          AS [PieceCount]
    , 'Weight'              AS [Weight]
    , 'ServiceType'         AS [ServiceType]
    , 'DeliveredTime'       AS [DeliveredTime]
    , 'POD'                 AS [POD]
    , 'BaseAmount'          AS [BaseAmount]
    , 'FuelAmount'          AS [FuelAmount]
    , 'Subtotalamount'      AS [Subtotalamount]
    , 'Totalamount'         AS [Totalamount]
    , 'Pieces'              AS [Pieces]
    , 'NULL'                AS [NULL]

Open in new window

------------
deducting 3 milliseconds from a datetime value is NOT a reliable method for your date range.

      WHERE orderdate BETWEEN (
                  SELECT
                        DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, -1, GETDATE())), 0)
            )
            AND (
                  SELECT
                        DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))
            )

Open in new window

Instead just ignore 'between', use >= with < instead:

      WHERE (
             orderdate >= DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, -1, GETDATE())), 0)
            AND
             orderdate <  DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
            )

Open in new window

for more on this topic please see: "Beware of Between"

W.E.B

ASKER

thank you Paul,
how do I get it to sort with headers on top?
the headers are coming as the last row now.

thank you,

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

W.E.B

ASKER

Thank you very much.

PortletPaul

No problem, thanks for the question. Cheers, Paul