Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

SQL QUERY

Hi All,

I need to implement the below SQL Query inside dataflow in BODS and Load the data into a Temporary Table. Can anyone suggest me step by step process.

select 'Eval_Revenue' as APPLICATION_NAME,

'On Track' AS KPI_TYPE,NULL AS KPI_VALUE, NULL as PTYPE,

(convert(decimal(15,0), (sum(NetRevenue)/1000)*0.08) -isnull(A.KPI_NUMBERS,0)) as KPI_NUMBERS,

b.customername from AlexDM.dbo.vwSVEvalMasterDashBoard B,(select

convert(decimal(15,0), sum(NetRevenue)/1000) as KPI_NUMBERS,customername from AlexDM.dbo.vwSVEvalMasterDashBoard

where EvalStatusName IN ( 'Complete') and

EvalQuarterSK between DATEADD(q, DATEDIFF(q, 0, getdate()), 0) and

DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, getdate()) + 1, 0)) group by customername)A

where

EvalQuarterSK between DATEADD(q, DATEDIFF(q, 0, getdate()), 0) and DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, getdate()) + 1, 0))

and A.customername = B.customername

group by B.customername,A.KPI_NUMBERS

regards

karthik

Former Member
replied

Hi Former Member

Source:

ETL DF Design:

Query logic follows below:

Create a NEW OUTPUT Column as APPLICATION_NAME and Map it as 'Eval_Revenue'. Same follows for KPI_TYPE

KPI_TYPE:

For KPI_VALUE and PTYPE map it with null


KPI_NUMBERS Logic: Create a NEW OUTPUT COLUMN as KPI_NUMBERS with Data Type Decimal(15,0)


ifthenelse( SQL_QUERY.NETREVENUE is null, 0 ,((sum(SQL_QUERY.NETREVENUE)/1000))*0.08 )

Drag and Drop the CUSTOMERNAME Directly from the Source.

WHERE Class Logic

((SQL_QUERY.EVALQUATERSK >= to_date(to_char(((((year(sysdate()) || '.') || month(sysdate())) || '.') || '01'), 'yyyy.mm.dd'), 'yyyy.mm.dd'))

AND

(SQL_QUERY.EVALQUATERSK <= to_date(add_months(last_date(sysdate()), 2), 'yyyy.mm.dd')))

Example : Output as EVALQUATERSK>= 2016-04-01 and 2016-06-30

Group By Tab:

Query_1_1 logic follows below:

Create a NEW OUTPUT Column as KPI_NUMBERS with Data Type :decimal (15,0)

Where Condition :

SQL_QUERY_1_1.EVALSTATUSNAME IN ( 'Complete')

AND

((SQL_QUERY_1_1.EVALQUATERSK >= to_date(to_char(((((year(sysdate()) || '.') || month(sysdate())) || '.') || '01'), 'yyyy.mm.dd'), 'yyyy.mm.dd'))

AND

(SQL_QUERY_1_1.EVALQUATERSK <= to_date(add_months(last_date(sysdate()), 2), 'yyyy.mm.dd')))

Group By Tab:

QUERY_1(Joining the two Query Transforms): Map all the fields to the Output.

Join Pairs: you need to specify the type of join and join condition.

Final Output:

Regards,

Akhilesh Kiran.

1 View this answer in context
Not what you were looking for? View more on this topic or Ask a question