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
W.E.BAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
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

0
W.E.BAuthor Commented:
Hi Scott,
I don't get any results.

thanks,
0
PortletPaulfreelancerCommented:
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"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

W.E.BAuthor Commented:
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,
0
PortletPaulfreelancerCommented:
Ah, yes, ok, flip the final part? Is this sufficient for your needs?
SELECT
      'InvoiceNumber'       AS [InvoiceNumber]
    , 'OrderNo'             AS [OrderNo]
    , 'OrderDate'           AS [OrderDate]
    , 'AccountNumber'       AS [AccountNumber]
    , 'Caller'              AS [Caller]

... more

UNION ALL

SELECT
      *
FROM @FinalizedOrdersPieces

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
W.E.BAuthor Commented:
Thank you very much.
0
PortletPaulfreelancerCommented:
No problem, thanks for the question. Cheers, Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.