September 20, 2010 at 7:12 am
Hi there experts,
I have the following TSQL -
SELECT DISTINCT '1' AS Trigger_Status,
dbo.ServiceOrders.UID,
CAST(dbo.Customers.InterfaceCode AS INT) AS KeyProperty,
LEFT(dbo.SalesOrders.Note, 50) AS JobDescription1,
LEFT(CASE WHEN LEN(dbo.SalesOrders.Note) < 50
THEN 'End' ELSE RIGHT(dbo.SalesOrders.Note,
LEN(dbo.SalesOrders.Note) - 50) END, 50) AS JobDescription2,
(SELECT min(PriorityID)
FROM dbo.ServiceOrdersJobs
WHERE dbo.ServiceOrders.UID = dbo.ServiceOrdersJobs.ServiceOrderID
AND (RecordStatusID < 6)) AS Priority,
dbo.SalesOrders.CreatedDate+dbo.SalesOrders.CreatedTime+2 AS ReportedDate,
LEFT(dbo.CustomersContacts.ShortName, 15) AS ReportedBy,
'MHS' + dbo.SalesOrders.Reference AS Access,
CAST(CASE WHEN dbo.CustomersContacts.BusinessPhone1 = ''
THEN dbo.CustomersContacts.MobilePhone1 ELSE
dbo.CustomersContacts.BusinessPhone1 END AS NVARCHAR (15)) AS ContactTel,
'0Y' AS PrincipalTrade,
CAST(CASE
WHEN SalesOrderCategoryID = 1 THEN '1'
WHEN SalesOrderCategoryID = 6 THEN '2'
WHEN SalesOrderCategoryID = 10 THEN '10'
WHEN SalesOrderCategoryID = 22 THEN 'V1'
ELSE '1' END AS NVARCHAR(2))AS ExpenseType,
17831 AS KeyContractID,
dbo.SalesOrders.Note AS ExtendedText,
'' AS JobDetailsSOR,
'2/2687' AS KeySORNumber,
'1' AS SorQTY,
'0' AS SorLocation
FROM dbo.SalesOrders Inner JOIN dbo.ServiceOrders
ON dbo.SalesOrders.UID = dbo.ServiceOrders.DocumentID
inner JOIN dbo.Customers
ON dbo.SalesOrders.CustomerID = dbo.Customers.UID
inner JOIN dbo.CustomersContacts
ON dbo.SalesOrders.CustomerContactID = dbo.CustomersContacts.UID
inner JOIN dbo.ServiceOrdersJobs
ON dbo.ServiceOrders.UID = dbo.ServiceOrdersJobs.ServiceOrderID
inner JOIN dbo.Personnel
ON dbo.ServiceOrdersJobs.AssignedToPersonnelID = dbo.Personnel.UID
inner JOIN dbo.InternalDepts
ON dbo.Personnel.InternalDeptID = dbo.InternalDepts.UID
WHERE --(dbo.SalesOrders.CreatedDate >= GETDATE() - 100)
(dbo.SalesOrders.RecordStatusID < 6)
AND(dbo.SalesOrders.SOStatusID = 1)
AND(dbo.ServiceOrders.RecordStatusID < 6)
AND (dbo.ServiceOrders.CustomerID = 41)
AND (dbo.ServiceOrders.DocumentTypeID = 4)
AND (dbo.Customers.ParentCustomerID = 41)
AND (dbo.CustomersContacts.RecordStatusID <> 6)
AND(dbo.Customers.InterfaceCode <> 0)
AND(dbo.SalesOrders.CustomerOrderRef not like '%RECALL%')
AND (dbo.SalesOrders.CustomerOrderRef = 'SHG')
AND (dbo.SalesOrders.SalesOrderCategoryID <> '23')
AND (dbo.InternalDepts.Name <> 'SHG Staff')
--AND NOT EXISTS (SELECT ServiceOrderID
--FROM DataWarehouse.dbo.SHG_JOB_FROM_OUTSMART
--WHERE DataWarehouse.dbo.SHG_JOB_FROM_OUTSMART.ServiceOrderID =
--dbo.ServiceOrders.UID)
GO
This at preset pulls back the following results -
Trigger_Status UID KeyProperty JobDescription1 JobDescription2 Priority ReportedDate ReportedBy Access ContactTel PrincipalTrade ExpenseType KeyContractID ExtendedText JobDetailsSOR KeySORNumber SorQTY SorLocation
-------------- ----------- ----------- -------------------------------------------------- -------------------------------------------------- ----------- ------------------------------------------------------ --------------- ----------------------- --------------- -------------- ----------- ------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------- ------------ ------ -----------
1 271822 13691 Res has a bad leak from top of cylinder - res has turned off water supply and drained off most off t 2 2010-06-21 07:44:28.000 Andrews, (Mrs.) MHS1252610 01233 332990 0Y 1 17831 Res has a bad leak from top of cylinder - res has turned off water supply and drained off most off the water - please rectify 2/2687 1 0
1 276573 36733 please inspect damp to property End 16 2010-07-28 13:32:13.000 D Waterton, (Mr MHS1257088 0Y 1 17831 please inspect damp to property 2/2687 1 0
1 279264 523 reported by martyn ralph - s/f slim line double wa ll unit in the caretakers cloak rom - please laise 3 2010-08-20 08:11:20.000 Steve (Caretake MHS1259657 07990 616109 0Y 1 17831 reported by martyn ralph - s/f slim line double wall unit in the caretakers cloak rom - please laise with steve on what he would like + location 2/2687 1 0
1 280425 520 communal lights are not please rectify End 3 2010-08-31 19:31:38.000 Access, (Gen/Ti MHS1260755 07947347839 0Y 1 17831 communal lights are not please rectify 2/2687 1 0
(4 row(s) affected)
It is the subquery in the select statment that I require help with..
(SELECT min(PriorityID)
FROM dbo.ServiceOrdersJobs
WHERE dbo.ServiceOrders.UID = dbo.ServiceOrdersJobs.ServiceOrderID
AND (RecordStatusID < 6)) AS Priority,
in my results for this part of the subquery one row is coming back as Priority 2, another as 16 and the remaining two as 3. What I want to include in this subquery is a CASE statement which will say along the lines of if the Priority is 16 then pull back a 0 otherwise just pull back the PriorityID.
Are you able to assist with this?
Thanks
Ryan
September 20, 2010 at 7:31 am
Something like this?
(SELECT CASE WHEN min(PriorityID) = 16 THEN 0 ELSE min(PriorityID) END
FROM dbo.ServiceOrdersJobs
WHERE dbo.ServiceOrders.UID = dbo.ServiceOrdersJobs.ServiceOrderID
AND (RecordStatusID < 6)) AS Priority,
Incidentally Ryan, your code would be much more readable if you used table aliases:
SELECT DISTINCT '1' AS Trigger_Status,
vo.UID,
CAST(c.InterfaceCode AS INT) AS KeyProperty,
LEFT(so.Note, 50) AS JobDescription1,
LEFT(CASE WHEN LEN(so.Note) < 50
THEN 'End' ELSE RIGHT(so.Note,
LEN(so.Note) - 50) END, 50) AS JobDescription2,
(SELECT min(PriorityID)
FROM dbo.ServiceOrdersJobs
WHERE vo.UID = dbo.ServiceOrdersJobs.ServiceOrderID
AND (RecordStatusID < 6)) AS Priority,
so.CreatedDate + so.CreatedTime+2 AS ReportedDate,
LEFT(cc.ShortName, 15) AS ReportedBy,
'MHS' + so.Reference AS Access,
CAST(CASE WHEN cc.BusinessPhone1 = ''
THEN cc.MobilePhone1 ELSE
cc.BusinessPhone1 END AS NVARCHAR (15)) AS ContactTel,
'0Y' AS PrincipalTrade,
CAST(CASE
WHEN SalesOrderCategoryID = 1 THEN '1'
WHEN SalesOrderCategoryID = 6 THEN '2'
WHEN SalesOrderCategoryID = 10 THEN '10'
WHEN SalesOrderCategoryID = 22 THEN 'V1'
ELSE '1' END AS NVARCHAR(2))AS ExpenseType,
17831 AS KeyContractID,
so.Note AS ExtendedText,
'' AS JobDetailsSOR,
'2/2687' AS KeySORNumber,
'1' AS SorQTY,
'0' AS SorLocation
FROM dbo.SalesOrders so
Inner JOIN dbo.ServiceOrders vo ON so.UID = vo.DocumentID
inner JOIN dbo.Customers c ON so.CustomerID = c.UID
inner JOIN dbo.CustomersContacts cc ON so.CustomerContactID = cc.UID
inner JOIN dbo.ServiceOrdersJobs vj ON vo.UID = vj.ServiceOrderID
inner JOIN dbo.Personnel p ON vj.AssignedToPersonnelID = p.UID
inner JOIN dbo.InternalDepts id ON p.InternalDeptID = id.UID
WHERE --(dbo.SalesOrders.CreatedDate >= GETDATE() - 100)
(so.RecordStatusID < 6)
AND (so.SOStatusID = 1)
AND (vo.RecordStatusID < 6)
AND (vo.CustomerID = 41)
AND (vo.DocumentTypeID = 4)
AND (c.ParentCustomerID = 41)
AND (cc.RecordStatusID <> 6)
AND (c.InterfaceCode <> 0)
--AND (so.CustomerOrderRef not like '%RECALL%') -- don't need this because of line below
AND (so.CustomerOrderRef = 'SHG')
AND (so.SalesOrderCategoryID <> '23')
AND (id.Name <> 'SHG Staff')
--AND NOT EXISTS (SELECT ServiceOrderID
--FROM DataWarehouse.dbo.SHG_JOB_FROM_OUTSMART
--WHERE DataWarehouse.dbo.SHG_JOB_FROM_OUTSMART.ServiceOrderID =
--dbo.ServiceOrders.UID)
GO
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 20, 2010 at 7:37 am
Thanks so much. I was putting "something" similar to what you suggested but couldn't quite make it 🙂
Thanks for the suggestion on the alias. I usually do but have inherited this script from someone else.
Thanks
Ryan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply