Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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..?
 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
52326 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
52326 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  
 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