Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL 2008 UNION ALL

Posted on 2014-08-06
7
Medium Priority
?
285 Views
Last Modified: 2014-08-06
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
0
Comment
Question by:W.E.B
  • 3
  • 3
7 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40244978
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
 

Author Comment

by:W.E.B
ID: 40244989
Hi Scott,
I don't get any results.

thanks,
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40245015
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:W.E.B
ID: 40245094
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1200 total points
ID: 40245111
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
 

Author Closing Comment

by:W.E.B
ID: 40245116
Thank you very much.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40245121
No problem, thanks for the question. Cheers, Paul
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question