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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » How to filter DATE data by DATE-1 day and...

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

Rate Topic

Display Mode

Topic Options

Author

Message

bhushan_juare

Posted Monday, February 11, 2013 11:44 PM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:14 AM
Points: 62, Visits: 358

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 »

Permissions