Tuesday, February 12, 2013 6:46 AM
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
GT_BAPI_INP_EXP_SCH GT WITH(NOLOCK)
IT_BAPI_INP_EXP_SCH IT WITH(NOLOCK)
GT.P_CODE = IT.P_CODE
GT.MOULD_CODE = IT.MOULD_CODE
GT.PRESSCAVITY_CODE = IT.PRESSCAVITY_CODE
@date = IT.END_DATE
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...
P T_SIZE Presscavity_DESC Total_SCH PROD_MTD T_Balance Incoming_Size SCH
1000 700/40-22.5 16 PR BKT FLOT648 TL T CURING 91" - 13 (F13) 110.0 0.0 110.0 600/50-22.5 18 PR BKT FLOT648 TL 86.0
1000 4.00-24 32 PR BKT EM937 TL T CURING 75" - 1 (E8) 34.0 0.0 34.0 14.00-25 24 PR BKT EM937 TL 41.0
1000 30.5L-32 12 PR BKT TR137 TL T CURING 104" - 1 (F5) 63.0 0.0 63.0 NULL NULL
2000 11.00-20 18 PR BKT PACMSTR TT T CURING P1 55" - A10 L 158.0 89.0 69.0 NULL NULL
Tuesday, February 12, 2013 6:53 AMPlease post sample data and required result.
Friday, February 22, 2013 12:57 AMModerator
>Now my concern is how to clup this select along with @date-1Day filteronly for this column only to get IT_INP_EXP_SCH.PROGRESS_QTY...
Apply WHERE .... = DATEADD(DD, -1, @date) predicate.
You may have to structure it with CTE or derived table:
Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012
Monday, February 25, 2013 12:25 AM
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
This is minimal polite behavior on SQL forums.
I think that you can use the newer window functions.
OVER (PARTITION BY GT.mould_code,
ORDER BY something_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
But without DDL, we can only guess. Want to try again?
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL