Avatar of maximus1974
maximus1974
 asked on

Need help combining four columns into one for each record

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

Microsoft SQL ServerOracle DatabaseSQL* syntax

Avatar of undefined
Last Comment
jtrifts

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Vishal Jaiswal

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Éric Moreau

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

ASKER
Yes, Eric, you are correct. Should have been Oracle.
Kent Olsen

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
jtrifts

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.