troubleshooting Question

Need help combining four columns into one for each record

Avatar of maximus1974
maximus1974 asked on
Microsoft SQL ServerOracle DatabaseSQL* syntax
5 Comments1 Solution99 ViewsLast Modified:
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'))
ASKER CERTIFIED SOLUTION
Vishal Jaiswal
Database Architect

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros