We help IT Professionals succeed at work.

Need help combining four columns into one for each record

maximus1974
maximus1974 asked
on
Need to combine WO_DAYS_LEFT, SO_DAYS_LEFT, RO_DAYS_LEFT, RW_DAYS_LEFT columns into one column named DAYS_LEFT for corresponding record.
How would I do this?

SELECT        dbo.BA_VIEW_SHIPPING_ORDERS.SM_NUMBER, dbo.BA_VIEW_SHIPPING_ORDERS.STATUS AS SM_STATUS, dbo.BA_VIEW_SHIPPING_ORDERS.ORDER_TYPE, 
                         dbo.BA_VIEW_SHIPPING_ORDERS.ORDER_NUMBER, dbo.DEPARTMENT.DEPT_NAME, dbo.BA_VIEW_SHIPPING_ORDERS.PN, 
                         dbo.BA_VIEW_SHIPPING_ORDERS.DESCRIPTION, dbo.BA_VIEW_SHIPPING_ORDERS.SHIP_NAME AS CUSTOMER_NAME, 
                         dbo.BA_VIEW_SHIPPING_ORDERS.SHIP_VIA_CODE, CASE WHEN BA_VIEW_WO_SUMMARY.CONTRACT_TAT IS NULL 
                         THEN 21 ELSE BA_VIEW_WO_SUMMARY.CONTRACT_TAT END - dbo.BA_VIEW_WO_SUMMARY.NET_TAT AS WO_DAYS_LEFT, DATEDIFF(DAY, 
                         dbo.BA_VIEW_SHIPPING_ORDERS.DUE_DATE, GETDATE()) AS SO_DAYS_LEFT, DATEDIFF(DAY, GETDATE(), dbo.BA_VIEW_REPAIR_ORDERS.SM_ENTRY) 
                         AS RO_DAYS_LEFT, DATEDIFF(DAY, GETDATE(), dbo.BA_VIEW_REWORK_RO.SM_ENTRY) 
                         AS RW_DAYS_LEFT
FROM            dbo.BA_VIEW_SHIPPING_ORDERS LEFT OUTER JOIN
                         dbo.BA_VIEW_REWORK_RO ON dbo.BA_VIEW_SHIPPING_ORDERS.ORDER_NUMBER = dbo.BA_VIEW_REWORK_RO.RO LEFT OUTER JOIN
                         dbo.BA_VIEW_REPAIR_ORDERS ON dbo.BA_VIEW_SHIPPING_ORDERS.ORDER_NUMBER = dbo.BA_VIEW_REPAIR_ORDERS.RO LEFT OUTER JOIN
                         dbo.BA_VIEW_WO_SUMMARY ON dbo.BA_VIEW_SHIPPING_ORDERS.ORDER_NUMBER = dbo.BA_VIEW_WO_SUMMARY.WO_NUMBER LEFT OUTER JOIN
                         dbo.DEPARTMENT ON dbo.BA_VIEW_SHIPPING_ORDERS.DPT_AUTO_KEY = dbo.DEPARTMENT.DPT_AUTO_KEY
WHERE        (dbo.BA_VIEW_SHIPPING_ORDERS.SM_NUMBER LIKE 'SHA%') AND (dbo.BA_VIEW_SHIPPING_ORDERS.STATUS NOT IN ('SHIPPED', 'CANCELLED'))

Open in new window

Comment
Watch Question

View Solution Only

Database Architect
Commented:
With N1 As (
SELECT        dbo.BA_VIEW_SHIPPING_ORDERS.SM_NUMBER, dbo.BA_VIEW_SHIPPING_ORDERS.STATUS AS SM_STATUS, dbo.BA_VIEW_SHIPPING_ORDERS.ORDER_TYPE,
                         dbo.BA_VIEW_SHIPPING_ORDERS.ORDER_NUMBER, dbo.DEPARTMENT.DEPT_NAME, dbo.BA_VIEW_SHIPPING_ORDERS.PN,
                         dbo.BA_VIEW_SHIPPING_ORDERS.DESCRIPTION, dbo.BA_VIEW_SHIPPING_ORDERS.SHIP_NAME AS CUSTOMER_NAME,
                         dbo.BA_VIEW_SHIPPING_ORDERS.SHIP_VIA_CODE, CASE WHEN BA_VIEW_WO_SUMMARY.CONTRACT_TAT IS NULL
                         THEN 21 ELSE BA_VIEW_WO_SUMMARY.CONTRACT_TAT END - dbo.BA_VIEW_WO_SUMMARY.NET_TAT AS WO_DAYS_LEFT, DATEDIFF(DAY,
                         dbo.BA_VIEW_SHIPPING_ORDERS.DUE_DATE, GETDATE()) AS SO_DAYS_LEFT, DATEDIFF(DAY, GETDATE(), dbo.BA_VIEW_REPAIR_ORDERS.SM_ENTRY)
                         AS RO_DAYS_LEFT, DATEDIFF(DAY, GETDATE(), dbo.BA_VIEW_REWORK_RO.SM_ENTRY)
                         AS RW_DAYS_LEFT
FROM            dbo.BA_VIEW_SHIPPING_ORDERS LEFT OUTER JOIN
                         dbo.BA_VIEW_REWORK_RO ON dbo.BA_VIEW_SHIPPING_ORDERS.ORDER_NUMBER = dbo.BA_VIEW_REWORK_RO.RO LEFT OUTER JOIN
                         dbo.BA_VIEW_REPAIR_ORDERS ON dbo.BA_VIEW_SHIPPING_ORDERS.ORDER_NUMBER = dbo.BA_VIEW_REPAIR_ORDERS.RO LEFT OUTER JOIN
                         dbo.BA_VIEW_WO_SUMMARY ON dbo.BA_VIEW_SHIPPING_ORDERS.ORDER_NUMBER = dbo.BA_VIEW_WO_SUMMARY.WO_NUMBER LEFT OUTER JOIN
                         dbo.DEPARTMENT ON dbo.BA_VIEW_SHIPPING_ORDERS.DPT_AUTO_KEY = dbo.DEPARTMENT.DPT_AUTO_KEY
WHERE        (dbo.BA_VIEW_SHIPPING_ORDERS.SM_NUMBER LIKE 'SHA%') AND (dbo.BA_VIEW_SHIPPING_ORDERS.STATUS NOT IN ('SHIPPED', 'CANCELLED'))
)
Select N1.*,(WO_DAYS_LEFT||SO_DAYS_LEFT||RO_DAYS_LEFT||RW_DAYS_LEFT) Days_Left
From N1
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
you have both Oracle and Microsoft in your tags. One is surely not good!
maximus1974

Author

Commented:
Yes, Eric, you are correct. Should have been Oracle.
Kent OlsenData Warehouse / Database Architect

Commented:
Oracle?  The dbo reference suggests that it's SQL Server.

You're computing all 4 values for Days_Left.  Do you want to see all 4 in one column or just add them?  Either is pretty trivial with the query that you have.
jtriftsMI and Automation

Commented:
if you want to add them, use the + operator...if you want to concatenate them, concatenate them (CONCAT)... You may need to ensure that you do not have a null (depending on which platform you're using, in which case you can REPLACENULL / NVL / COALESCE accordingly).

so the example provided:
...
Select N1.*,(WO_DAYS_LEFT||SO_DAYS_LEFT||RO_DAYS_LEFT||RW_DAYS_LEFT) Days_Left
...
concatenates using Oracle SQL shorthand (double pipe)...

If you wanted to add them it would be:

Select N1.*,(WO_DAYS_LEFT+SO_DAYS_LEFT+RO_DAYS_LEFT+RW_DAYS_LEFT) Days_Left
(if the fields were NOT NULL fields)...
Otherwise it might be:

Select N1.*,(NVL(WO_DAYS_LEFT,0)+NVL(SO_DAYS_LEFT,0)+NVL(RO_DAYS_LEFT,0)+NVL(RW_DAYS_LEFT,0)) Days_Left

...
etc.