SQL - combine queries without using UNION ALL

Hi,

Can you explain why you don't want to use union all?

Regards
  David

would like to use CASE Statement only.   Also how can I  add STR   function for formatting dollar amounts  with two decimal place?  Thank you.

 CASE WHEN OrderItems.qty >=10 THEN 0.05 END) AS 'Total_Final_Price_After_Discount'

Hi,

Does this help?

I reformatted your select so it was readable and added this line to each case statement
else 0.00

SELECT 
	ORDERS.orderid
	, INVENTORY.partid
	, Inventory.description
	, ORDERITEMS.qty
	, Inventory.price
	, (OrderItems.qty * Inventory.price) AS 'Total Original Price'
  
	-----If an order contains ten or more units of a given product, give a 5% discount on that line item.

	, (
		OrderItems.qty 
		* Inventory.price 
		* CASE 
			WHEN OrderItems.qty >=10 THEN 0.05 
			-- added line
			else 0.00
		END
		) AS 'Total Discount Value'

	----Get Total_Final_Price_After_Discount

	, OrderItems.qty 
		* Inventory.price 
		- (
			OrderItems.qty 
			* Inventory.price 
			* CASE 
				WHEN OrdeItems.qty >=10 THEN 0.05 
				-- added line
				else 0.00
			END
		) AS 'Total_Final_Price_After_Discount' 

Select allOpen in new window

HTH
  David

I combined it as follows:  I get  5% and 2% discount as output for each item.   That is fine but  I need to format the Dollar values of  the  output columns.  Example: I am getting 10.000 which is correct . I need to format it to 10.00.

 So How can I format   OrderItems.qty
            * Inventory.price
            * CASE
                  WHEN OrderItems.qty >=10 THEN 0.05
                  -- added line
                  else 0.00
            END
            ) AS 'Total Discount Value'



SELECT
      ORDERS.orderid
      , INVENTORY.partid
      , Inventory.description
      , ORDERITEMS.qty
      , Inventory.price AS 'Unit price'
      , (OrderItems.qty * Inventory.price) AS 'Original Cost'
 
      -----If an order contains ten or more units of a given product, give a 5% discount on that line item.

      , (
            OrderItems.qty
            * Inventory.price
            * CASE
                  WHEN OrderItems.qty >=10 THEN 0.05
                  -- added line
                  else 0.00
            END
            ) AS 'Total Discount Value'

      ----Final Cost

      , OrderItems.qty
            * Inventory.price
            - (
                  OrderItems.qty
                  * Inventory.price
                  * CASE
                        WHEN OrderItems.qty >=10 THEN 0.05
                        -- added line
                        else 0.00
                  END
            ) AS 'Final Cost'  
            
            FROM ORDERS, INVENTORY, ORDERITEMS
 WHERE ORDERS.OrderID = ORDERITEMS.OrderID  AND ORDERITEMS.PartID = INVENTORY.PartID
 AND ORDERITEMS.qty >=10
 ORDER BY ORDERS.OrderId, INVENTORY.PartId
 
 UNION ALL
 
 SELECT
      ORDERS.orderid
      , INVENTORY.partid
      , Inventory.description
      , ORDERITEMS.qty
      , Inventory.price AS 'Unit price'
      , (OrderItems.qty * Inventory.price) AS 'Original Cost'
 
      -----If an order contains ten or more units of a given product, give a 5% discount on that line item.

      , (
            OrderItems.qty
            * Inventory.price
            * CASE
                  WHEN OrderItems.qty < 10 THEN 0.2
                  -- added line
                  else 0.00
            END
            ) AS 'Total Discount Value'

      ----Get Total_Final_Price_After_Discount

      , OrderItems.qty
            * Inventory.price
            - (
                  OrderItems.qty
                  * Inventory.price
                  * CASE
                        WHEN OrderItems.qty < 10 THEN 0.2
                        -- added line
                        else 0.00
                  END
            ) AS 'Final Cost'  
            
            FROM ORDERS, INVENTORY, ORDERITEMS
 WHERE ORDERS.OrderID = ORDERITEMS.OrderID  AND ORDERITEMS.PartID = INVENTORY.PartID
  AND ORDERITEMS.qty BETWEEN 5  AND  9
 ORDER BY ORDERS.OrderId, INVENTORY.PartId

Hi

With the lines I added to the case, loose the where clause, and loose the union all.

Formatting is something best left to presentation layer - excel, SSRS, web, application, etched.

Hth
  David

Hi,

you only did -----If an order contains ten or more units of a given product, give a 5% discount

I also need one  for -----If an order contains 5 or more units of a given product, give a 2% discount.  And need to combine both in the CASE statement.  

As far as formatting is concern, I need to format the output columns to 2 decimal.    Thank you
 for your help.