MSSQL Daty pomiędzy datami

Cześć,

pierwszy raz tutaj :-)

Mam problem z napisaniem kodu. Błagam Was o pomoc! Siedzę nad tym 3 miesiące.

Mianowicie mam tabelę T_STATUS_HISTORY (liczba wierszy może być nieskończona dla danego klucza "IssueKey" oczywiście w praktyce raczej jest ich <100):

IssueKey;ChangeDate;NewValue

ABC-1;2021-10-01 1000;Status1

ABC-1;2021-10-01 1100;Status1

ABC-1;2021-10-10 0900;Status3

ABC-1;2021-10-10 1600;Status2

ABC-1;2021-10-11 1200;Status0

ABC-1;2021-10-15 2000;Status1

ABC-1;2021-10-20 2000;Status2

ABC-1;2021-10-25 2000;Status3

ABC-1;2021-10-29 2000;Status0

Jest to tabela przechowująca zmiany statusów dla IssueKey.

Każdy wpis oznacza jakąś zmianę statusu (np. z Status1 w dniu "2021-10-01 1100" na Status3 w dniu "2021-10-10 0900" itd).

Potrzebuję poznać status na koniec każdego dnia, ale również status pomiędzy tymi datami na każdy z tych dni na jego koniec.

Czyli wynikiem powinno być :

ABC-1;2021-10-01 1100;Status1

ABC-1;2021-10-02 1100;Status1

ABC-1;2021-10-03 1100;Status1

ABC-1;2021-10-04 1100;Status1

ABC-1;2021-10-05 1100;Status1

ABC-1;2021-10-06 1100;Status1

ABC-1;2021-10-07 1100;Status1

ABC-1;2021-10-08 1100;Status1

ABC-1;2021-10-09 1100;Status1

ABC-1;2021-10-10 1600;Status2

ABC-1;2021-10-11 1200;Status0

ABC-1;2021-10-12 1200;Status0

ABC-1;2021-10-13 1200;Status0

ABC-1;2021-10-14 1200;Status0

ABC-1;2021-10-15 2000;Status1

ABC-1;2021-10-16 2000;Status1

ABC-1;2021-10-17 2000;Status1

ABC-1;2021-10-18 2000;Status1

ABC-1;2021-10-19 2000;Status1

ABC-1;2021-10-20 2000;Status2

ABC-1;2021-10-21 2000;Status2

ABC-1;2021-10-22 2000;Status2

ABC-1;2021-10-23 2000;Status2

ABC-1;2021-10-24 2000;Status2

ABC-1;2021-10-25 2000;Status3

ABC-1;2021-10-26 2000;Status3

ABC-1;2021-10-27 2000;Status3

ABC-1;2021-10-28 2000;Status3

ABC-1;2021-10-29 2000;Status0

JAK TO ZROBIĆ ??

with  T_STATUS_HISTORY_EOD as (
select *,  ROW_NUMBER() OVER(PARTITION BY CONVERT(DATE, IssueDate) ORDER BY  IssueDate desc) 
    AS R  from T_STATUS_HISTORY  
)
select * from T_STATUS_HISTORY_EOD  where r = 1 

Ta wybierzesz ostatni rekord dla dnia. Jak masz w tabeli jakiś klucz (np ten IssueKey) to powinien on też się znaleźć w PARTITION By. A po co ci dni, jakich brakuje? Może możesz to wygenerować na froncie? Takie rzeczy nie są mocną stroną SQLa.

Czyli wynikiem powinno być :

No niekoniecznie można określić daty od-do i pytać o datę w okresie:

With enddayvalue as (select 
	IssueKey
	,cast(ChangeDate as date) ChangeDate 
	,NewValue
FROM
	(select *, ROW_NUMBER() over (partition by issuekey, cast(changeDate as date) order by changeDate desc) r from T_STATUS_HISTORY) t
where
	r = 1)
, ValuePeriods as (
select 
		IssueKey
		,ChangeDate DateFrom
		,LEAD ( Dateadd(d,-1,ChangeDate),1 ,getdate()) OVER ( Partition by IssueKey order by changeDate ) DateTo
		,NewValue
from 
	enddayvalue)


select * from ValuePeriods where '2021-11-03' between DateFrom and DateTo

Jeżeli jednak koniecznie chcesz mieć osobny wpis dla każdego dnia to:

With enddayvalue as (select 
	IssueKey
	,cast(ChangeDate as date) ChangeDate 
	,NewValue
FROM
	(select *, ROW_NUMBER() over (partition by issuekey, cast(changeDate as date) order by changeDate desc) r from T_STATUS_HISTORY) t
where
	r = 1)
, ValuePeriods as (
select 
		IssueKey
		,ChangeDate DateFrom
		,LEAD ( Dateadd(d,-1,ChangeDate),1 ,getdate()) OVER ( Partition by IssueKey order by changeDate ) DateTo
		,NewValue
from 
	enddayvalue)
, vdays as (SELECT 
    DATEADD(DAY,number,(select MIN(datefrom) from ValuePeriods)) [Date]
FROM 
    master..spt_values
WHERE 
    type = 'P'
	and number between 0 and (select datediff(d,MIN(datefrom),MAX(dateto)) from ValuePeriods))

select 
	IssueKey
	, d.date as ChangeDate
	, NewValue 
from 
	ValuePeriods p 
	inner join vdays d on d.date between p.datefrom and p.DateTo 

Nie zadziała jeżeli różnica minimalnej date z bieżącą przekroczy ilość w tabeli spt_values

@Panczo: Cześć, spore opóźnienie w odpowiedzi.... sorki.
Nie do końca działa, tzn. działa ale zakres dat jest za duży. Pierwsza wartość powinna wynosić daty to pierwsza data w ABC-1;2021-10-01 1100;Status1 tymczasem otrzymuję inną poza zakresem.

To trochę ponad moje siły....
Bardzo Ci dziękuję za pomoc, może uda Ci się zerknąć na to.

@Panczo:

@Kenedor: Oryginalna tabela :
IssueKey ChangeDate Status
OPDB-11906 2022-01-04 1614.090 W trakcie
OPDB-11906 2022-01-04 1640.907 Oczekuje na informację
OPDB-11906 2022-01-10 0929.193 W trakcie
OPDB-11906 2022-01-10 0910.860 Oczekuje na informację
OPDB-11906 2022-01-10 1203.083 Zakończone
OPDB-11906 2022-01-11 0907.683 W trakcie
OPDB-11906 2022-01-11 0923.140 WSTRZYMANE
OPDB-11906 2022-01-12 1209.190 Zakończone

Jeżeli dobrze rozumiem to zmiany wymaga ta linijka:

,LEAD ( Dateadd(d,-1,ChangeDate),1 ,getdate()) OVER ( Partition by IssueKey order by changeDate ) DateTo

Porównaj:

http://sqlfiddle.com/#!18/09fc0/3