is this possible with transact sql?

Question

Post reply

is this possible with transact sql?

nkat

SSChasing Mays

Points: 658
More actions
May 1, 2022 at 3:32 pm

#4025088
Hello!
There's a table
|Date|QueueName|Param|
----------------------
|0404|Queue0001|Q1P01|
|0405|Queue0001|Q1P01|
|0406|Queue0001|Q1P02|
|0407|Queue0001|Q1P02|
|0408|Queue0001|Q1P03|
|0409|Queue0001|Q1P03|
|0404|Queue0002|Q2P01|
|0405|Queue0002|Q2P01|
|0406|Queue0002|Q2P01|
|0407|Queue0002|Q2P01|
|0408|Queue0002|Q2P01|
|0409|Queue0002|Q2P02|
......................
The aim is to have it transpose and create a script that will output the following table
|QueueName|PrevP|Since|CurrP|Since|
-----------------------------------
|Queue0001|Q1P02| 0406|Q1P03|0408 |
|Queue0002|Q2P01| 0404|Q2P02|0409 |
Would someone hint how this can be achieved?
- This topic was modified 2 years, 8 months ago by nkat. Reason: body editing

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246988 More actions · Answer 1

Would you please explain the required logic a little more?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

nkat SSChasing Mays Points: 658 More actions · Answer 2

USE [LSWDS]
declare @tt table (dt date, QueueName varchar(10), Prm varchar(10))
insert into @tt(Dt, QueueName, Prm)
values
('2001-04-04','Queue0001','Q1P01'),
('2001-04-05','Queue0001','Q1P01'),
('2001-04-06','Queue0001','Q1P02'),
('2001-04-07','Queue0001','Q1P02'),
('2001-04-08','Queue0001','Q1P03'),
('2001-04-09','Queue0001','Q1P03'),
('2001-04-04','Queue0002','Q2P01'),
('2001-04-05','Queue0002','Q2P01'),
('2001-04-06','Queue0002','Q2P01'),
('2001-04-07','Queue0002','Q2P01'),
('2001-04-08','Queue0002','Q2P01'),
('2001-04-09','Queue0002','Q2P02');

with sinceQueueDate (QueueName,Prm,SinceDt) as
(
  select distinct QueueName, Prm, FIRST_VALUE(Dt) over(partition by QueueName,Prm order by Dt) SinceDt
  from @tt
),

currPrm(QueueName,Prm) as 
(
  select distinct QueueName, FIRST_VALUE(Prm) over(partition by QueueName order by Dt desc) Prm
  from @tt
)

select sQD.* from
  (select QueueName, Prm as currP, SinceDt as currSinceDt,
  LAG(Prm) over(partition by QueueName order by SinceDt) as prevP, 
  LAG(SinceDt) over(partition by QueueName order by SinceDt) as prevSinceDt
  from sinceQueueDate) sQD
join currPrm cP
  on cP.QueueName=sQD.QueueName and cP.Prm=sQD.currP

my acquaintance, Anton Molchanov, solved the problem. He is worthy of the Programmer name

I post it here in case anyone else need it

Steve Collins SSCrazy Points: 2174 More actions · Answer 3

Assuming 'Prm' is ordinal maybe something like this

[EDIT] Got rid of comments

;with rn_cte as (
    select *, 
           rank() over (partition by QueueName order by prm desc) rn_q,
           row_number() over (partition by QueueName, Prm order by dt asc) rn_qp
    from @tt)
select r.QueueName, r.Prm as currP, r.dt as currSinceDt, 
       v.max_prm prev_P, v.max_dt prevSinceDt
from rn_cte r
     cross apply (select max(dt), max(rr.prm) 
                  from rn_cte rr
                  where rr.QueueName=r.QueueName
                        and rr.rn_qp=1
                        and rr.rn_q>r.rn_q) v(max_dt, max_prm) 
where rn_q=1 
      and rn_qp=1
order by QueueName, dt;

This reply was modified 2 years, 8 months ago by Steve Collins.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können