Create Data model in QV cript

gizzel123 — Sat, 03 Jan 2015 10:14:46 GMT

Hi All,

I have a stored procedure( SP), I had create dQVDs of the table. and the joins

can anyone help me in implimenting the rest of the logic in QV data model.

[sample SP]

Alter Procedure[dbo.Sample]

(proj_no varchar (40),
project_name varchar (120),
....#..#..#
//-----declarinng fields---//
)

insert into #TEMP_table
select

dd.proj_no,
kk.xyz_name as [project_name],
bb.....#
.
#
#
case
when bb.act_start_date is null then datediff(dd, bb.start_date, bb.end_date)
else datediff(dd, bb.act_start_date, bb.end_date) end as [total_duration],
datediff(dd, bb.start_date, bb.end_date) as [remain_duration],
0 as [left_hrs_day],
0 as [left_days_next_90],
0 as [left_hrs_next_90 numeric],
isnull(bb.total_float_hr_cnt / 8, 9999) as [total_float],
isnull(bb.free_float_hr_cnt / 8, 9999) as [free_float]

from dbo.RSRC aa
left outer join dbo.TASK bb on aa.task_id = bb.task_id
left outer join dbo.ROLE cc on aa.role_id = cc.role_id
left outer join dbo.PROJECT dd on aa.proj_id = dd.proj_id
left outer join dbo.ACTIVITY ee on bb.task_id = ee.task_id
left outer join dbo.XYZ ff on dd.proj_short_name = ff.projnum
left outer join dbo.PROJECT_1 gg on aa.proj_id = gg.proj_id
left outer join dbo.METADATA hh on dd.proj_short_name = hh.segment1
left outer join dbo.JWBS kk on dd.proj_n0 = kk.wbso_short_name
where aa.proj_id in
(
select a.proj_id from dbo.PROJECT a
left outer join dbo.PROJECT_1 b on a.proj_id = b.proj_id
inner join dbo.xyz c on a.proj_id = c.proj_id
left outer join dbo.MAS d on a.proj_short_name = d.segment1 -- inner join excludes EPS nodes
left outer join dbo.META e on a.proj_short_name = e.segment1
where a.orig_proj_id is null
and c.proj_node_flag = 'Y'
and c.status_code = 'WS_Open'
and (d.segment1 is not null or a.proj_short_name like ('_xyz%'))
)
and (aa.left_qty <> 0 or aa.target_qty <> 0)
and ee.rsrc not like 'X%'

and substring(dd.proj_no, 6, 4) not in ('xy1', 'xy2', 'xy3')
and aa.rsrc_id is null

update #TEMP_table set project_name = b.xyz_name

from #TEMP_table a, dbo.JWBS b

where a.proj_no= b.wbs_no

drop table #TEMP_table

PS: please ignohe table names and syntax error they are just for sample

Thanks in advance

topic Create Data model in QV cript in QlikView

Create Data model in QV cript