A particular view

Transact-SQL
1

Hi,
Can anyone help me to solve my problem :slight_smile: ?

I have some data table in the following format....
ID Contract Ver. Value
1 BlahBlahBlah 0 A1 B1 Open 100
2 BlahBlahBlah 0 A2 B1 Open 150
3 BlahBlahBlah 0 A2 B2 Open 50
4 BlahBlahBlah 1 A1 B1 Open 100
5 BlahBlahBlah 1 A2 B2 Open 75
6 BlahBlahBlah 2 A1 B1 Open 100
7 OupsOups 0 C1 B1 Open 1500
8 OupsOups 0 A2 B1 Open 150
9 OupsOups 0 A2 B2 Open 50
10 OupsOups 1 C1 B1 Open 125
11 OupsOups 1 A2 B2 Open 75

and i want somehow to obtain the following format:

ID Contract Ver. Value
BlahBlahBlah 0 A1 B1 Open 100
BlahBlahBlah 0 A2 B1 Open 150
BlahBlahBlah 0 A2 B2 Open 50
BlahBlahBlah 1 A1 B1 Open 125
BlahBlahBlah 1 A2 B2 Open 75
BlahBlahBlah 1 A2 B1 Open 0
BlahBlahBlah 2 A1 B1 Open 100
BlahBlahBlah 2 A2 B1 Open 0
BlahBlahBlah 2 A2 B2 Open 0
OupsOups 0 C1 B1 Open 1500
OupsOups 0 A2 B1 Open 150
OupsOups 0 A2 B2 Open 50
OupsOups 1 C1 B1 Open 125
OupsOups 1 A2 B2 Open 75
OupsOups 1 A2 B1 Open 0

I want to insert to every version of a contract data rows that are missing from the 0's version of the same contract with 0 as value.

3

Please consider that BlahBlahBlah is a project with a 0 version and one or multiple updated versions. A project is characterized by phases with 3 parameters A1, B1, Open and a Value.
I want to be sure that the rows from ver.0 of a project are present in all versions with 0 value if the phase doesn't exists anymore or with an updated value.

6

hi Gabriel

I tried to do this
Hope it helps :slight_smile:

This solution is NOT DYNAMIC

please click arrow on the left for "drop create data " 
drop table #data 
go 

create table #data 
(
ID int, 
[Contract] varchar(100),
ver int,
param1 varchar(10),
param2 varchar(10),
Value int,
)
go 

insert into #data select 1,'BlahBlahBlah', 0 ,'A1B1','Open',100
insert into #data select 2, 'BlahBlahBlah', 0 ,'A2B1','Open', 150
insert into #data select 3, 'BlahBlahBlah', 0 ,'A2B2','Open', 50
insert into #data select 4, 'BlahBlahBlah', 1 ,'A1B1','Open', 100
insert into #data select 5, 'BlahBlahBlah', 1 ,'A2B2','Open', 75
insert into #data select 6, 'BlahBlahBlah', 2 ,'A1B1','Open', 100
insert into #data select 7, 'OupsOups', 0 ,'C1B1','Open', 1500
insert into #data select 8, 'OupsOups', 0 ,'A2B1','Open', 150
insert into #data select 9, 'OupsOups', 0 ,'A2B2','Open', 50
insert into #data select 10, 'OupsOups', 1 ,'C1B1','Open', 125
insert into #data select 11, 'OupsOups', 1 ,'A2B2','Open', 75
go
please click arrow to the left for seeing "SQL " 
SELECT * 
FROM   (SELECT contract, 
               ver, 
               param1, 
               param2, 
               value 
        FROM   #data 
        WHERE  [contract] = 'BlahBlahBlah' 
        UNION ALL 
        SELECT b.contract, 
               1, 
               b.param1, 
               b.param2, 
               0 
        FROM   (SELECT param1, 
                       [contract] 
                FROM   #data 
                WHERE  ver = 0 
                       AND contract = 'BlahBlahBlah' 
                EXCEPT 
                SELECT param1, 
                       [contract] 
                FROM   #data 
                WHERE  ver = 1 
                       AND contract = 'BlahBlahBlah') a 
               JOIN (SELECT * 
                     FROM   #data b 
                     WHERE  ver = 0) b 
                 ON a.param1 = b.param1 
                    AND a.contract = b.contract 
        UNION ALL 
        SELECT b.contract, 
               2, 
               b.param1, 
               b.param2, 
               0 
        FROM   (SELECT param1, 
                       [contract] 
                FROM   #data 
                WHERE  ver = 0 
                       AND contract = 'BlahBlahBlah' 
                EXCEPT 
                SELECT param1, 
                       [contract] 
                FROM   #data 
                WHERE  ver = 2 
                       AND contract = 'BlahBlahBlah') a 
               JOIN (SELECT * 
                     FROM   #data b 
                     WHERE  ver = 0) b 
                 ON a.param1 = b.param1 
                    AND a.contract = b.contract 
        UNION ALL 
        SELECT contract, 
               ver, 
               param1, 
               param2, 
               value 
        FROM   #data 
        WHERE  [contract] = 'OupsOups' 
        UNION ALL 
        SELECT b.contract, 
               1, 
               b.param1, 
               b.param2, 
               0 
        FROM   (SELECT param1, 
                       [contract] 
                FROM   #data 
                WHERE  ver = 0 
                       AND contract = 'OupsOups' 
                EXCEPT 
                SELECT param1, 
                       [contract] 
                FROM   #data 
                WHERE  ver = 1 
                       AND contract = 'OupsOups') a 
               JOIN (SELECT * 
                     FROM   #data b 
                     WHERE  ver = 0) b 
                 ON a.param1 = b.param1 
                    AND a.contract = b.contract) a 
ORDER  BY 1, 
          2, 
          3

image
image.png931×1246 423 KB

7

Thanks a lot ! This is what i wanted ....Now, it's my turn to find a solution to make it DYNAMIC :slight_smile:

10

I think this works..

;with cte
as
(
select a.Contract , a.ver , a.param1 , a.param2 , a.Value  
from #data a
)

select * from cte 

union all 

select distinct a.Contract , b.ver , a.param1 , a.param2 , 0 
from #data a
inner join #data b
on a.Contract = b.Contract 
and b.ver <> 0
and not exists 
	(
	select 1
	from #data c
	where c.Contract = b.Contract 
	and c.param1 = a.param1 
	and c.ver = b.ver 
	)
where a.ver = 0

order by 1, 2, 3
11

hi femiolan

It works !!!!!
i have being going NUTS for 2 days trying to figure it out ..

i am not the guy who posted the question !!!

Thanks :slight_smile: :slight_smile:

12

Thanks for the feedback. I hope the op sees the solution.

13

This it was great ! Thanks guys for your help !!!!