SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to filter DATE data by DATE-1 day & Store..?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bhushan_juare
Starting Member

45 Posts

Posted - 02/12/2013 :  01:46:24  Show Profile  Reply with Quote
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

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/12/2013 :  03:43:45  Show Profile  Reply with Quote
you will need a subquery for that
like below

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,
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 
CROSS APPLY(Select SUM(IT_INP_EXP_SCH.PROGRESS_QTY)  AS PRODN_QTY_TILL_END_DATE
            FROM IT_INP_EXP_SCH
           Where END_DATE =DATEDIFF(day,  IT.END_DATE, DATEADD(day, -1,  IT.END_DATE))
           AND P_CODE = IT.P_CODE AND MOULD_CODE = IT.MOULD_CODE AND PRESSCAVITY_CODE = IT.PRESSCAVITY_CODE 
         )t
Where 
@date = IT.END_DATE
Group By 
GT.MOULD_CODE,
GT.PRESSCAVITY_CODE,
GT.P_CODE
Order By 
GT.P_CODE


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bhushan_juare
Starting Member

45 Posts

Posted - 02/12/2013 :  07:13:39  Show Profile  Reply with Quote
Hi Visakh,
Thanks for reply but
when i execute whole query i am getting an erro
"Operand type clash: date is incompatible with int" Now, what went wrong with code

Thanks,
Bhushan
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/12/2013 :  10:17:23  Show Profile  Reply with Quote
that was a typo. it should be



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,
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 
CROSS APPLY(Select SUM(IT_INP_EXP_SCH.PROGRESS_QTY)  AS PRODN_QTY_TILL_END_DATE
            FROM IT_INP_EXP_SCH
           Where END_DATE =DATEADD(day,DATEDIFF(day, -1,  IT.END_DATE),0)
           AND P_CODE = IT.P_CODE AND MOULD_CODE = IT.MOULD_CODE AND PRESSCAVITY_CODE = IT.PRESSCAVITY_CODE 
         )t
Where 
@date = IT.END_DATE
Group By 
GT.MOULD_CODE,
GT.PRESSCAVITY_CODE,
GT.P_CODE
Order By 
GT.P_CODE


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000