We help IT Professionals succeed at work.

Generating records based upon projectiion.

Hello Experts,
I have attached an xl of my query. I have got 2 tables, the first table contains 2 fileds and the detail table contains some data.

Based upon the value in the 2 fields the data in the detail table will be replicated.

Please provide a view for the same
Book1.xlsx
Comment
Watch Question

Top Expert 2011

Commented:
like this

just expand the tally table to allow for your max number of months
;with n1 as (select 0 as n union all select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all sekect 6 union select 7 union select 8
union select 9)
,n2 as (select (a.n *10)+b.n as n from n1 as ,n2 as b)
,n3 as (select (a.n*10)+b.n as n from n2 as a,n1 as b)
create view vw_projectedMonths as 
select a.employeeid,a.yearid,dateadd(m,n3.n-1,a.projectstartsfrom) as Projectedmonth
      ,b.component,b.amount
 from tableA as A
 left outer join tableB as B
   on a.employeeid=b.employeeid
  and a.yearid=b.yearid
 cross Join n3
 where n3.n between 1 and a.projectedmonths
;
select * from vw_projectedmonths
order by employeeid,projectedmonth,component

Open in new window

Author

Commented:
i am getting the following error when i execute

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'sekect'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ','.
Msg 111, Level 15, State 1, Line 7
'CREATE VIEW' must be the first statement in a query batch.
Top Expert 2011

Commented:
try it now

any sekect should be select
;create view vw_projectedMonths as 

with n1 as (select 0 as n union all select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6 union select 7 union select 8
union select 9)
,n2 as (select (a.n *10)+b.n as n from n1 as ,n2 as b)
,n3 as (select (a.n*10)+b.n as n from n2 as a,n1 as b)

select a.employeeid,a.yearid,dateadd(m,n3.n-1,a.projectstartsfrom) as Projectedmonth
      ,b.component,b.amount
 from tableA as A
 left outer join tableB as B
   on a.employeeid=b.employeeid
  and a.yearid=b.yearid
 cross Join n3
 where n3.n<=a.projectedmonths
;
select * from vw_projectedmonths
order by employeeid,projectedmonth,component

Open in new window

Author

Commented:
Again some arror

Msg 102, Level 15, State 1, Procedure vw_projectedMonths, Line 6
Incorrect syntax near ','.
Top Expert 2011

Commented:
fixed i hope
;create view vw_projectedMonths as 

with n1 as (select 0 as n union all select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6 union select 7 union select 8
union select 9)
,n2 as (select (a.n *10)+b.n as n from n1 as a,n1 as b)
,n3 as (select (a.n*10)+b.n as n from n2 as a,n1 as b)
--select n from n3
select a.employeeid,a.yearid,dateadd(m,n3.n-1,a.projectstartsfrom) as Projectedmonth
      ,b.component,b.amount
 from tableA as A
 left outer join tableB as B
   on a.employeeid=b.employeeid
  and a.yearid=b.yearid
 cross Join n3
 where n3.n<=a.projectedmonths
go
select * from vw_projectedmonths
order by employeeid,projectedmonth,component

Open in new window

Top Expert 2011

Commented:
sorry just noticed the where clause ...

this should be it...
;create view vw_projectedMonths as 

with n1 as (select 0 as n union all select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6 union select 7 union select 8
union select 9)
,n2 as (select (a.n *10)+b.n as n from n1 as a,n1 as b)
,n3 as (select (a.n*10)+b.n as n from n2 as a,n1 as b)
--select n from n3
select a.employeeid,a.yearid,dateadd(m,n3.n-1,a.projectstartsfrom) as Projectedmonth
      ,b.component,b.amount
 from tableA as A
 left outer join tableB as B
   on a.employeeid=b.employeeid
  and a.yearid=b.yearid
 cross Join n3
 where n3.n between 1 and a.projectedmonths
go
select * from vw_projectedmonths
order by employeeid,projectedmonth,component

Open in new window

Author

Commented:
thanks a lot. if any queries i will revert back to you.
Top Expert 2011

Commented:
like this
?
drop table #result

create table #result 
(
  BusinessNameId int,
  BusinessName varchar(250),
  HCproviders varchar(250),
  hCids varchar(250)

)

insert into #result 
 select 3, 'Test Business33', 'Beh, Bob1 Jones, pp333 ppp, yy fff','18, 1, 11, 16'

insert into #result 
 select 39, 'Username test', 'Linda Hensely','12'


;with n1 as (select 0 as n union all select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6 union select 7 union select 8
union select 9)
,n2 as (select (a.n *10)+b.n as n from n1 as a,n1 as b)
,n3 as (select (a.n*10)+b.n as n from n2 as a,n1 as b)
,n4 as (select (a.n*10)+b.n as n from n3 as a,n1 as b)
,expand as (
select BusinessNameId,BusinessName,','+HCproviders+',' as hcproviders ,n
     ,','+hCids+',' as hcids
  from #result
  cross join n4
  )
,expids as (
select BusinessNameId, BusinessName,n ,hcids
       ,ROW_NUMBER() over (partition by businessnameid order by n) as rn
 from expand
 where SUBSTRING(hcids,n,1)=','
 and n between 1 and len(hcids)
 )
 ,expHC as (
 select BusinessNameId, hcproviders,n ,businessname
       ,ROW_NUMBER() over (partition by businessnameid order by n) as rn
 from expand
 where SUBSTRING(hcproviders,n,1)=','
 and n between 1 and len(hcproviders)
 )
 ,exphc2 as (
 select a.businessnameid,SUBSTRING(a.hcproviders,a.n+1,b.n-a.n-1) as hcproviders
            ,a.rn,a.businessname
  from exphc as a
  inner join expHC as b
   on a.BusinessNameId=b.BusinessNameId
   and a.rn=b.rn-1
   )
   ,expids2 as (
 select a.businessnameid,SUBSTRING(a.hcids,a.n+1,b.n-a.n-1) as hcids
            ,a.rn
  from expids as a
  inner join expids b
   on a.BusinessNameId=b.BusinessNameId
   and a.rn=b.rn-1
   )
  
     select a.BusinessNameId,b.businessname
         ,case when LEN(a.hcids)>0 then a.hcids end as hcids
         ,case when LEN(b.hcproviders)>0 then b.hcproviders end as hcproviders 
     from expids2 as a
     inner join exphc2 as b
     on a.BusinessNameId=b.BusinessNameId
     and a.rn=b.rn

Open in new window

Top Expert 2011

Commented:
sorry wrong question

Author

Commented:
the view is working fine for me can explain me about he view i m a bit confused and how many projection months with it support
Top Expert 2011
Commented:
its a standard left join between your two tables linked on employee id and work year id...

that result set is joined in a cross product multiplication with a 1000 row table (n3)

so currently the view supports a projection of up to 999 months  (n3 has numbers in range 0 to 999, but your projection starts at 1 month...)

n3 is formed fromed by an inline specification of a 10 row table (n1) containing the digits 0 to 9 which is "self joined" to produce the eventual 1000 rows via the intermediate stage n2 (100 rows  values 0 - 99)  you can keep building the size of the table by
adding further terms...

e.g. n4 as (select (a.n*10) + b.n as N from N3 as a, n1 as b)   would produce 10,000 rows range 0 - 9,999


hth

Author

Commented:
Thanks a million