Post reply

datediff with where condition

  • July 30, 2009 at 9:07 am

    #140341

    Hi there!

    Could somebody please help me with my datediff function?

    I want to add a where condition to my datediff but I'm not sure on the syntax.

    Here is my statement:

    ;with

    CTEMain as

    (SELECT DISTINCT

    datediff(day,CRM_StatusCodes.START_DATE, CRM_StatusCodes.END_DATE) AS [Service Order Completion Duration (Days)]

    Now, I want to perform this calculation based on this:

    where CRM_StatusCodes.USER_STATUS = 'Complete'

    Does anyone have any ideas?

    Thank you! 🙂

    Michelle

  • July 30, 2009 at 9:20 am

    #1032455

    You are missing the table name in the CTE clause.

    You can place the where clause right after the table name like normal or you can do it in the following select statement.

    Where in the select statement

    ;with

    CTEMain as

    (SELECT DISTINCT

    datediff(day,CRM_StatusCodes.START_DATE, CRM_StatusCodes.END_DATE) AS [Service Order Completion Duration (Days)] from ...)

    select * from CTEMain where CRM_StatusCodes.USER_STATUS = 'Complete'

    Where in the CTE

    ;with

    CTEMain as

    (SELECT DISTINCT

    datediff(day,CRM_StatusCodes.START_DATE, CRM_StatusCodes.END_DATE) AS [Service Order Completion Duration (Days)] from ... where CRM_StatusCodes.USER_STATUS = 'Complete')

    select * from CTEMain

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply