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

• Tuesday, February 12, 2013 6:46 AM

   

   
  

  0

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

  Sample Data

  P                T_SIZE                                          Presscavity_DESC   Total_SCH PROD_MTD T_Balance     Incoming_Size                  SCH

  1000    700/40-22.5 16 PR BKT FLOT648 TL    T CURING 91" - 13 (F13)    110.0    0.0     110.0    600/50-22.5 18 PR BKT FLOT648 TL  86.0
  1000    4.00-24 32 PR BKT EM937 TL              T CURING 75" - 1 (E8)         34.0     0.0      34.0     14.00-25 24 PR BKT EM937 TL           41.0
  1000    30.5L-32 12 PR BKT TR137 TL             T CURING 104" - 1 (F5)       63.0     0.0      63.0      NULL                                                 NULL
  2000    11.00-20 18 PR BKT PACMSTR TT        T CURING P1 55" - A10 L     158.0   89.0    69.0      NULL                                                NULL

  Thanks,

  Bhushan

  
  

  • Edited by Bhushan Juare Tuesday, February 12, 2013 6:49 AM
  • Edited by Bhushan Juare Tuesday, February 12, 2013 7:11 AM
  •  
  • Reply
  • Quote