Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to filter DATE data by DATE-1 day and store filtered result in another column..? Expand / Collapse
Author
Message
Posted Monday, February 11, 2013 11:44 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 28, 2013 2:48 AM
Points: 64, Visits: 367
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
Post #1418762
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse