June 30, 2009 at 6:48 am
Hi there,
The below select statement pulls back all the Schedule of Rate Jobs that are held against a certain job on our Contractor System.
[font="Verdana"]Select '1' AS Trigger_Status,
dbo.ServiceOrders.UID AS ServiceOrderID,
LEFT(GETDATE(),19) AS PractCompleteDate,
LEFT (dbo.Products.ProductCode, 6) AS SOR,
dbo.SalesOrders.CustomerOrderRef AS ClientRef,
'0' AS SORSequence,
DataWarehouse.dbo.SHG_SORITEMEXPORT.KeySORNumber,
dbo.SalesOrdersItems.Quantity AS SORQty
FROM dbo.SalesOrders
LEFT JOIN dbo.SalesOrdersItems
ON dbo.SalesOrders.UID = dbo.SalesOrdersItems.SalesOrderID
LEFT JOIN dbo.Products
ON dbo.SalesOrdersItems.ProductID = dbo.Products.UID
LEFT JOIN dbo.ServiceOrders
ON dbo.SalesOrders.UID = dbo.ServiceOrders.DocumentID
LEFT JOIN dbo.SalesInvoices
ON dbo.SalesOrders.UID = dbo.SalesInvoices.DocumentID
LEFT JOIN DataWarehouse.dbo.SHG_SORITEMEXPORT
ON DataWarehouse.dbo.SHG_SORITEMEXPORT.UserCode = LEFT(dbo.Products.ProductCode, 6)
LEFT JOIN DataWarehouse.dbo.SHG_COMPLETE_JOB
ON DataWarehouse.dbo.SHG_COMPLETE_JOB.ClientRef = dbo.SalesOrders.CustomerOrderRef
LEFT JOIN DataWarehouse.dbo.SHG_JOB_FULL_COMPLETION
ON DataWarehouse.dbo.SHG_JOB_FULL_COMPLETION.ClientRef = DataWarehouse.dbo.SHG_COMPLETE_JOB.ClientRef
WHERE dbo.SalesOrders.CreatedDate >= GETDATE()- 15
AND(dbo.SalesOrders.RecordStatusID < 6)
AND(dbo.SalesOrders.SOStatusID = 7)
AND (dbo.ServiceOrders.RecordStatusID < 6)
AND (dbo.ServiceOrders.CustomerID = 41)
AND(dbo.SalesInvoices.DocumentTypeID = 4)
ANDdbo.SalesOrders.CustomerOrderRef <> 'SHG'[/font]
At the moment the SORSequence number is hardcoded as a 0 (this data is not held on our database)
What I want to do is say that if the KeySORNumber is equal to 2/2687, then pull back "1" as the SORSequence, otherwise contune to pull back "0"
Any help would be appreciated.
Many Thanks
June 30, 2009 at 10:00 am
You should be able to use a case statment in your selct. Something similar to...
SELECT...
CASE KeySORNumber
WHEN '2/2687' THEN 1
ELSE 0 END AS [Column Name],
....
FROM...
June 30, 2009 at 10:06 am
I think the previous poster was exactly on the right track, however, I had some additional thoughts, is the value '2/2687' or the product of what appears to be a division operation. If it is the former then the previous posters answer is right on. If not I think you might have a problem, the 2/2687 number is a fairly inexact number so getting a match on the value will likely be hit and miss. The structure of the previous posters resolution is likely correct and if the value can be more exact I think you will have better success.
CEWII
June 30, 2009 at 12:22 pm
Good catch. I agree if its a divisional result then this will pose issues of rounding, percision, etc which will have to be dealt with. I assumed it was a string value... which obviously might not be the case.
July 7, 2009 at 6:18 am
Hi there,
Just to confirm it is a string value and will always be in the same format.
Sorry, but I'm really useless when it comes to coding. Where exactly would you put the Case statement in my Select?
Select '1' AS Trigger_Status,
dbo.ServiceOrders.UID AS ServiceOrderID,
LEFT(GETDATE(),19) AS PractCompleteDate,
dbo.SalesOrders.CustomerOrderRef AS ClientRef,
'0' AS SORSequence,
DataWarehouse.dbo.SHG_SORITEMEXPORT.KeySORNumber,
dbo.SalesOrdersItems.Quantity AS SORQty
July 7, 2009 at 6:23 am
Sorry - worked it out.
Thanks guys
July 7, 2009 at 5:41 pm
No problem, I've had that happen many times..
CEWII
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply