| Author |
 Topic  |
|
|
bhushan_juare
Starting Member
44 Posts |
 Posted - 02/12/2013 : 01:46:24
|
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
48012 Posts |
Posted - 02/12/2013 : 03:43:45
|
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/
|
 |
|
|
bhushan_juare
Starting Member
44 Posts |
Posted - 02/12/2013 : 07:13:39
|
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
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 02/12/2013 : 10:17:23
|
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/
|
|
|
| |
Topic |
|
|
|
How to filter DATE data by DATE-1 day & Store..?
