Value should not repeated again

Question

Hi,

I have table like below

create table #mytable (vouno varchar(20), voudate date, procedure varchar(10))
create table #resulttable (vouno varchar(20), '01/01/2021' date, '02/01/2021' date, '03/01/2021' date, '04/01/2021' date, '05/01/2021' date)

insert into #mytable values ('1001','01/01/2021','A')
insert into #mytable values ('1001','02/01/2021','A')
insert into #mytable values ('1001','03/01/2021','B')
insert into #mytable values ('1001','04/01/2021','A')
insert into #mytable values ('1001','05/01/2021','A')

insert into #mytable values ('1002','01/01/2021','A')
insert into #mytable values ('1002','02/01/2021','A')
insert into #mytable values ('1002','03/01/2021','B')
insert into #mytable values ('1002','04/01/2021','B')

insert into #mytable values ('1003','01/01/2021','A')
insert into #mytable values ('1003','02/01/2021','B')
insert into #mytable values ('1003','03/01/2021','A')
insert into #mytable values ('1003','04/01/2021','B')
insert into #mytable values ('1003','05/01/2021','B')

insert into #mytable values ('1004','01/01/2021','A')
insert into #mytable values ('1004','02/01/2021','A')
insert into #mytable values ('1004','03/01/2021','B')
insert into #mytable values ('1004','04/01/2021','B')
insert into #mytable values ('1004','05/01/2021','B')

select * from #mytable

insert into #resulttable values ('1001', 'A', 'A', 'B', 'A', 'A')
insert into #resulttable values ('1003', 'A', 'B', 'A', 'B', 'B')

select * from #resulttable

drop table #mytable
drop table #resulttable

in the above #mytable vouno=1001 the procedure 'A' starts. Again the procedure of vouno = 1001 changes to 'B' on '03/01/2001'.
if any value changes from one value to another again the previous values should not come.
in the above example vouno 1001 and 1003 is happened. i need that record.

The dates are dynamically changes

kindly guide us to query this

Thanks
Kasim

Answer 1

Check if the output is suitable:

;
with E1 as
(
    select *, LAG([procedure]) over (partition by vouno order by voudate) as lg
    from #mytable
),
E2 as
(
    select *, row_number() over (partition by vouno order by voudate) as rn
    from #mytable
),
E3 as
(
    select vouno
    from E1
    where [procedure] <> lg
    group by vouno
    having count(*) > 1
)
select vouno, [1], [2], [3], [4], [5], [6]
from
(
    select E3.vouno, E2.rn, E2.[procedure] from E3
    inner join E2 on E2.vouno = E3.vouno
) t
pivot
(
    max([procedure]) for rn in ([1], [2], [3], [4], [5], [6])
) p
order by vouno

Answer 2

Hi @kasim mohamed ,

Welcome to Microsoft Q&A!

Please refer below and check whether it is helpful to you.

;with cte as (  
select *,LEAD([procedure]) over (partition by vouno order by voudate) as LEAD from #mytable)  
,cte1 as (  
select vouno,voudate,[procedure] from cte   
where vouno in  
(select vouno from cte where [procedure]<>LEAD  
group by vouno  
having count(*)>1))  
,cte2 as (  
select *,row_number() over (partition by vouno order by voudate) as rn from cte1 )  
,cte3 as (  
SELECT vouno,[1] ,[2],[3] ,[4] ,[5]   
FROM    
(SELECT vouno,voudate,[procedure],rn from cte2) AS SourceTable    
PIVOT    
(    
 max([procedure]) for rn in ([1], [2], [3], [4], [5])    
) AS PivotTable)  
select vouno, max([1]) '01/01/2021', max([2]) '02/01/2021' ,max([3]) '03/01/2021',max([4]) '04/01/2021',max([5]) '05/01/2021'  
 from cte3   
 group by vouno  

If the dates are dynamically changes, you could refer below dynamic way:

drop table if exists #temp  
;with cte as (  
select *,LEAD([procedure]) over (partition by vouno order by voudate) as LEAD from #mytable)  
,cte1 as (  
select vouno,voudate,[procedure] from cte   
where vouno in  
(select vouno from cte where [procedure]<>LEAD  
group by vouno  
having count(*)>1))  
select *,row_number() over (partition by vouno order by voudate) as rn   
into #temp from cte1   
  
declare @finalsql nvarchar(max)  
declare @sql nvarchar(max)  
declare @sql2 nvarchar(max)  
declare @s nvarchar(max)  
declare @s2 nvarchar(max)  
declare @n int  
declare @max int  
  
set @n=1  
set @sql=''  
set @sql2=''  
set @finalsql=''  
select @max= max(rn) from #temp  
  
while @n<=@max  
begin  
  set @s=',['+cast(@n as char(1))+']'  
  select @s2=',max(['+cast(@n as char(1))+']) '''+ CONVERT(NVARCHAR(30), voudate, 101) +'''' from #temp where rn=@n   
  set @n=@n+1  
  set @sql=@sql+@s  
  set @sql2=@sql2+@s2  
end  
  
 set @sql=SUBSTRING(@sql,2,len(@sql)-1)  
set @sql2=SUBSTRING(@sql2,2,len(@sql2)-1)  
  
set @finalsql=N'  
;with cte3 as (  
SELECT vouno,'+@sql+'  
FROM    
(SELECT vouno,voudate,[procedure],rn from #temp) AS SourceTable    
PIVOT    
(    
 max([procedure]) for rn in ('+@sql+')    
) AS PivotTable)  
select vouno,'+@sql2+'  
 from cte3   
 group by vouno'  
  
 EXECUTE sp_executesql  @finalsql  

Output:

vouno	01/01/2021	02/01/2021	03/01/2021	04/01/2021	05/01/2021  
1001	A	A	B	A	A  
1003	A	B	A	B	B  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

> I have table like below <<

Actually you don't have any tables at all! By definition, a table must have a key and none of your declarations can ever have a key because all the columns can be null. You also don't seem to know that the only format permitted for DATE NOT NULLs in ANSI-ISO standard SQL (and a lot of other ISO standards) is 'yyyy-mm-dd' and not your local ambiguous dialect. Considering this is the second most common ISO standard on earth after the metric system, you really should know if you're going to work in IT. You also don't seem to note that you can declare the entire table with instructor using the standard ANSI ISO syntax.

I'm going to make a guess that each of those columns in your report table are actually months, and not dates. The problem is that SQL Server does not have interval data types (the ANSI/ISO standards and other SQL products do).

I have found that stealing the syntax from MySQL is very useful. It has also been proposed as an extension to the ISO 8601 standards, but it is not official yet essential you take the original ISO standard and use double zeros in the day field for a month or double zeros in the month and day field for the year as an interval. Here's a rewrite of your DDL:

CREATE TABLE Vouchers
(voucher_nbr VARCHAR(20) NOT NULL,
voucher_month NOT NULL CHAR(10) NOT NULL
CHECK(voucher_month LIKE '[01][0-9][0-9][0-9]-[01][0-9]-00'), -- study this carefully
PRIMARY KEY (voucher_nbr, voucher_month), -- this is not optional !
procedure_name CHAR(1) NOT NULL CHECK (procedure_name IN ('A', 'B'))
);

Did you know that "procedure" is a reserved word in SQL should never be a table name?

CREATE TABLE Voucher_Report -- notice the use of double quotes
(voucher_nbr VARCHAR(20),
"2021-01-00" CHAR(1) NOT NULL,
"2021-02-00" CHAR(1) NOT NULL,
"2021-03-00" CHAR(1) NOT NULL,
"2021-04-00" CHAR(1) NOT NULL,
"2021-05-00" CHAR(1) NOT NULL);

INSERT INTO Vouchers
VALUES
('1001', '2021-01-00', 'A'),
('1001', '2021-02-00', 'A'),
('1001', '2021-03-00', 'B'),
('1001', '2021-04-00', 'A'),
('1001', '2021-05-00', 'A'),

('1002', '2021-01-00', 'A'),
('1002', '2021-02-00', 'A'),
('1002', '2021-03-00', 'B'),
('1002', '2021-04-00', 'B'),

('1003', '2021-01-00', 'A'),
('1003', '2021-02-00', 'B'),
('1003', '2021-03-00', 'A'),
('1003', '2021-04-00', 'B'),
('1003', '2021-05-00', 'B'),

('1004', '2021-01-00', 'A'),
('1004', '2021-02-00', 'A'),
('1004', '2021-03-00', 'B'),
('1004', '2021-04-00', 'B'),
('1004', '2021-05-00', 'B');

INSERT INTO Voucher_Report
VALUES ('1001', 'A', 'A', 'B', 'A', 'A'), ('1003', 'A', 'B', 'A', 'B', 'B');

in the above Voucher_Report voucher_nbr=1001 the procedure 'A' starts. Again the procedure of voucher_nbr = 1001 changes to 'B' on '03-01-2001'.
if any value changes from one value to another again the previous values should not come.
in the above example voucher_nbr 1001 and 1003 is happened. I need that record [sic - rows are nothing like records!] .

> The date are dynamically changed <<

Not in RDBMS! A column name is an attribute, not the header on a nonrelational report. This is as silly as having the attribute "automobile_color" switch over to "airplane_weight"; each attribute is fixed and does not change. This is one of the basic principles of logic too.

Again, guessing at what you want, this would seem to be a PIVOT operation that turns a relational table into a nonrelational spreadsheet or report. Personally, I would prefer to use a report tool of some kind for this.