If Satatement

  • 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

  • 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...

  • 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

  • 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.

  • 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

  • Sorry - worked it out.

    Thanks guys

  • 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