Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
bhushan_juare
Starting Member
45 Posts |
Posted - 2013-02-12 : 01:46:24
|
Hi All,I have below query...Declare @date DATESet @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 SCHFrom GT_BAPI_INP_EXP_SCH GT WITH(NOLOCK)Inner Join IT_BAPI_INP_EXP_SCH IT WITH(NOLOCK)OnGT.P_CODE = IT.P_CODE ANDGT.MOULD_CODE = IT.MOULD_CODEANDGT.PRESSCAVITY_CODE = IT.PRESSCAVITY_CODE Where @date = IT.END_DATEGroup By GT.MOULD_CODE,GT.PRESSCAVITY_CODE,GT.P_CODEOrder By GT.P_CODEEverthning 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 GTwhere 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_QTYNow 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
52326 Posts |
Posted - 2013-02-12 : 03:43:45
|
you will need a subquery for thatlike belowDeclare @date DATESet @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 SCHFrom GT_BAPI_INP_EXP_SCH GT WITH(NOLOCK)Inner Join IT_BAPI_INP_EXP_SCH IT WITH(NOLOCK)OnGT.P_CODE = IT.P_CODE ANDGT.MOULD_CODE = IT.MOULD_CODEANDGT.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 )tWhere @date = IT.END_DATEGroup By GT.MOULD_CODE,GT.PRESSCAVITY_CODE,GT.P_CODEOrder By GT.P_CODE ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bhushan_juare
Starting Member
45 Posts |
Posted - 2013-02-12 : 07:13:39
|
Hi Visakh,Thanks for reply butwhen i execute whole query i am getting an erro"Operand type clash: date is incompatible with int" Now, what went wrong with codeThanks,Bhushan |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-12 : 10:17:23
|
that was a typo. it should beDeclare @date DATESet @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 SCHFrom GT_BAPI_INP_EXP_SCH GT WITH(NOLOCK)Inner Join IT_BAPI_INP_EXP_SCH IT WITH(NOLOCK)OnGT.P_CODE = IT.P_CODE ANDGT.MOULD_CODE = IT.MOULD_CODEANDGT.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 )tWhere @date = IT.END_DATEGroup By GT.MOULD_CODE,GT.PRESSCAVITY_CODE,GT.P_CODEOrder By GT.P_CODE ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|