How to filter DATE data by DATE-1 day and store filtered result in another column..?

  • [font="Courier New"]Hi All,

    I have below query...

    Declare @date DATE

    Set @date = '2013-01-29'

    Select max(GT.P_CODE) As P,

    max(GT.FG_DESCRIPTION) As T_SIZE,

    max(GT.PRESSCAVITY_DESC) As PRESSCAVITY_DESC,

    max(GT.PLANNED_QUANTITY) As TOTAL_SCH,

    Select (SUM(IT_INP_EXP_SCH.PROGRESS_QTY) Where DATEDIFF(day, @date, DATEADD(day, -1, @date)) AS PRODN_QTY_TILL_END_DATE,

    max(GT.PROGRESS_QTY) As PROD_MTD,

    (max(GT.PLANNED_QUANTITY) - max(GT.PROGRESS_QTY)) As T_BALANCE,

    max(CASE WHEN GT.FG_DESCRIPTION = IT.FG_DESCRIPTION THEN NULL ELSE IT.FG_DESCRIPTION END) AS INCOMING_SIZE,

    max(CASE WHEN GT.FG_DESCRIPTION = IT.FG_DESCRIPTION THEN NULL ELSE IT.PLANNED_QUANTITY END) AS SCH

    From

    GT_BAPI_INP_EXP_SCH GT WITH(NOLOCK)

    Inner Join

    IT_BAPI_INP_EXP_SCH IT WITH(NOLOCK)

    On

    GT.P_CODE = IT.P_CODE

    AND

    GT.MOULD_CODE = IT.MOULD_CODE

    AND

    GT.PRESSCAVITY_CODE = IT.PRESSCAVITY_CODE

    Where

    @date = IT.END_DATE

    Group By

    GT.MOULD_CODE,

    GT.PRESSCAVITY_CODE,

    GT.P_CODE

    Order By

    GT.P_CODE

    Everthning is goin well except when i add PRODN_QTY_TILL_END_DATE column, which will give me SUM(IT_INP_EXP_SCH.PROGRESS_QTY) based on @date - 1 Day = '2013-01-28' in the same select statement..

    NOTE:- Select DISTINCT * from IT_BAPI_INP_EXP_SCH IT, GT_BAPI_INP_EXP_SCH GT

    where IT.END_DATE = '2013-01-28' and GT.FG_DESCRIPTION = '700/40-22.5 16 PR BKT FLOT648 TL' AND GT.PRESSCAVITY_DESC = 'T CURING 91" - 13 (F13)' give me IT_INP_EXP_SCH.PROGRESS_QTY

    Now my concern is how to clup this select along with @date-1Day filter only for this column only to get IT_INP_EXP_SCH.PROGRESS_QTY...

    Thanks,

    Bhushan[/font]

Viewing 0 posts

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