Zwracanie rekordu z dwóch tabel zawierających się w dacie.

Cześć wszystkim... Mam taki problem do ogarnięcia mam dwie tabele w pierwszej są Id inwestycji + aktualny etap + data_modyfikacji w drugiej natomiast rejestrowane są natomiast wartości historyczne jak etapy się zmieniały i kiedy (MS SQL 2016):

tabela_no1

Id_inw| Etap|data_modyfikacji
---------------- | -------------------
1 | Etap 3 | 2018-06-01
2 | Etap 2 | 2018-05-01
3 | Etap 1 | 2018-06-01

tabela_no2_historia

Id | Id_inw | Etap | data_modyfikacji
---------------- | -------------------
1 | 1 | Etap 1 | 2018-02-01
2 | 1 | Etap 2 | 2018-04-01
3 | 2 | Etap 1 | 2018-04-01

I teraz chciałbym np określić stan jaki jest na dla poszczególnych ID czyli wynik na 01-06-2018 będzie

Id_inw| Etap|data_modyfikacji
---------------- | -------------------
1 | Etap 3 | 2018-06-01
2 | Etap 2 | 2018-05-01
3 | Etap 1 | 2018-06-01

Problemem dla mnie jest jak wydobyć jak wygląda stan np. na 01-05-2018 czyli::

Id_inw| Etap|data_modyfikacji
---------------- | -------------------
1 | Etap 2 | 2018-04-01
2 | Etap 2 | 2018-05-01

Z góry dziękuje za pomoc.

Połącz te tabelę unionem, później wykorzystaj funkcje LEAD (https://docs.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql?view=sql-server-2017) aby wyciągnąć do kiedy trwa dany etap i zadaj odpowidni warunek WHERE

with cAll as (
select 
    Id_inw
    ,Etap
    ,data_modyfikacji 
from 
    glowna
union all
select 
    Id_inw
    ,Etap
    ,data_modyfikacji 
from 
    historia
), daneoddo as (
select 
    Id_inw
    ,Etap
    ,data_modyfikacji Od
    ,dateadd(d,-1,lead(data_modyfikacji,1,GETDATE()+100) over (partition by id_inw order by data_modyfikacji)) do
 from 
    cAll  
)

select * from daneoddo 
where '2018-05-28' between Od and do

http://sqlfiddle.com/#!18/1a80c/4

select * from tabela_no2_historia where
id in (select max(id) from tabela_no2_historia where data_modyfikacji <= '2018-05-28' group by id_inw)

o ile id w tabela_no2_historia rośnie wraz z data_modyfikacji.

@Panczo: Jestem w ciężkim szoku że SQL można takie rzeczy ogarniać ;) nie doceniałem tego języka powiem szczerze... Dzięki za pomoc. Trzeba będzie się wziąć za porządną naukę na stare lata ;)

Witam ponownie nie będę zakładać nowego tematu... bo wracam do tego samego przykładu. Chciałbym wklecić w zapytanie od @Panczo "drobną" modyfikacje mianowicie by dla wartości historycznych pokazywało mi wartość liczbową, ilość inwestycji oraz procent. Na podstawie tabeli którą przygotował @Panczo dodałem dane i wykonałem zapytanie dla sytuacji bieżącej, a chciałbym dla historycznej korzystając z tego wzorca powyżej od @Panczo. Z góry dziękuje za pomoc.
http://sqlfiddle.com/#!18/a2304/6

Dokładnie tak samo jak twoje zapytanie:

WITH CALL AS (
SELECT 
    Id_inw
    ,Etap
    ,data_modyfikacji
    ,wartosc
FROM 
    glowna
UNION ALL
SELECT 
    Id_inw
    ,Etap
    ,data_modyfikacji 
    ,wartosc
FROM 
    historia
), daneoddo AS (
SELECT 
    Id_inw
    ,Etap
    ,wartosc
    ,data_modyfikacji Od
    ,dateadd(d,-1,lead(data_modyfikacji,1,GETDATE()+100) OVER (partition BY id_inw ORDER BY data_modyfikacji)) do
 FROM 
    CALL  
), dt as (
  SELECT * FROM daneoddo 
WHERE '2018-05-28' BETWEEN Od AND do)
select 
 etap
 ,count(id_inw) Liczba
 ,sum(wartosc) wartosc
 ,STR(ROUND(Count(*)*100.0/di.i,2),6,2) + '%' AS Procent
 from
 dt
 inner join (select count(*) i from dt) di on 1=1
 group by etap,di.i

Unikaj podzapytań dla liczenia ciągle tej samej wartości.

http://sqlfiddle.com/#!18/b7af7/6

Próbuję zrozumieć działanie tego zapytania i wprowadziłem parę modyfikacji do struktury bazy (w drugiej tabeli miałem powieloną wartość inwestycji z pierwszej tabeli wiec postanowiłem ją usunąć w drugiej) oraz w nazewnictwie. Rozumiem że w pierwszej kolejności wykonywana jest UNION ALL z dwóch tabel główna i historia. Następnie zwraca rekordy zawierające się Od.... Do... spełniające warunek nie przekroczenia daty 2018-05-28. I następnie wylicza wartości odpowiednio grupując dane z wcześniej trzymanych zapytań ale cosik to mi nie prądzi ;(.
http://sqlfiddle.com/#!18/8f2ae/7

Jeszcze chciałem się dowiedzieć czy mój zapis jest prawidłowy dla wyliczenia % wartosci:

STR(ROUND(SUM(wartosc)*100.0/(SELECT SUM(wartosc) FROM glowna),2),6,2) + '%' AS Procent_wartosci_inw

Rozumuję to tak że dla sumy wartości spełniającej warunek odpowiednio pogrupowanej ma podzielić przez ogólną sumę wartości.

No to po kolei:

cALL - robi uniona czyli łaczy tabelę główną z historyczną, ty masz błąd bo z głównej pobierasz o jedną kolumnę więcej: wartość (której nie masz w kolumnie historia), a liczba kolumn musi być taka sama dla każdego zapytania w unionie.
daneoddo wykorzystują funkcje LEAD (https://docs.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql?view=sql-server-2017) aby wyznaczyć datę do czyli czyli lead pobiera datę modyfikacji, później odejmujemy od tej daty 1 dzień, aby stworzyć zakresy dat w jakich obowiązywała dana "wersja" rekordu.
dt to już założenie filtru na datę, aby pobrać dane na konkretny dzień, dodatkowo join z tabelą główną, aby mieć kolumnę wartość

Samo zapytanie ma dodatkowo cross joina który liczy wartości globalne dla ilości i wartości, całość można tak:

WITH CALL AS (
     SELECT Id id_inw,
            Etap,
            data_modyfikacji data_zmiany,
            wartosc
     FROM glowna
     UNION ALL
     SELECT Id_inw,
            Etap,
            data_zmiany,
            0
     FROM historia
)
,daneoddo AS (
    SELECT Id_inw,
	   Etap,
	   data_zmiany Od,
	   DATEADD(d, -1, LEAD(data_zmiany, 1, GETDATE() + 100) OVER(PARTITION BY id_inw ORDER BY data_zmiany)) do,
	   wartosc
    FROM CALL
)
,dt AS (
     SELECT d.id_inw,
            d.etap,
            d.od,
            d.do,
            g.wartosc
     FROM daneoddo d
          INNER JOIN glowna g ON g.id = d.id_inw
     WHERE '2018-05-28' BETWEEN Od AND do
)
SELECT 
    dt.etap,
    COUNT(id_inw) Liczba,
    SUM(wartosc) Wartosc_inw,
    STR(ROUND(SUM(wartosc) * 100.0 / di.w, 2), 6, 2)+'%' AS Procent_wartosci_inw,
    STR(ROUND(COUNT(*) * 100.0 / di.i, 2), 6, 2)+'%' AS Procent_liczby
FROM 
    dt
    CROSS JOIN(SELECT 
				COUNT(*) i,
				SUM(wartosc) w
			 FROM 
				dt) di
GROUP BY 
    dt.etap,
    di.i,
    di.w;

http://sqlfiddle.com/#!18/8f2ae/23

Dzięki za wyjaśnienie teraz przynajmniej wiem grze grzebnąć ;)
No to już ostatnie pytanie ;) dodałem grupowanie po roku oraz miesiącu:
http://sqlfiddle.com/#!18/8f2ae/34

Ale od razu zrodziło mi się pytanie jak rozpisać to na poszczególne miesiące ale w takim układzie że np.
dla miesiąca lipca mam rozpisane wszystkie 4 etapy jak tutaj:
http://sqlfiddle.com/#!18/8f2ae/36
dla czerwca
http://sqlfiddle.com/#!18/8f2ae/38

Po prostu rozpisać wszystkie lata/miesiące i wszystkie etapy dla danego miesiąca w układzie jaki był stan na koniec każdego miesiąca.

Drugie pytanie właściwie chciałem się upewnić czy jeśli chcę zawrzeć klauzulę WHERE to wpisuję ją tutaj jak poniżej?? czy wcześniej już na etapie złączenia UNION wykluczam?? np:

WHERE '2018-07-28' BETWEEN Od AND do AND (d.Etap = 'Etap1' OR d.Etap = 'Etap2')

Wygenerowanie danych na koniec każdego miesiąca, sprowadza się do myku stworzenia "tabeli" z ostatnimi datami:

Za ostatnie 2 lata (24 miesiące)

SELECT DISTINCT 
	   cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dateadd(m,number*-1,GETDATE()))+1,0)) as date) dm
    FROM 
	   master..[spt_values] 
    WHERE 
	   number BETWEEN 0 AND 24

I teraz wykorzystenie tego do połaczenia z tabelą daneoddo, zwróć uwagę na warunek sprzężenia: datym.dm between d.Od and d.do co da ci w wyniku tyle rekordów ile masz miesięcy w zadanym okresie od-do
.
Całość:

WITH CALL AS (
     SELECT Id id_inw,
            Etap,
            data_modyfikacji data_zmiany,
            wartosc
     FROM glowna
     UNION ALL
     SELECT Id_inw,
            Etap,
            data_zmiany,
            0
     FROM historia
)
,daneoddo AS (
    SELECT Id_inw,
       Etap,
       data_zmiany Od,
       DATEADD(d, -1, LEAD(data_zmiany, 1, GETDATE() + 100) OVER(PARTITION BY id_inw ORDER BY data_zmiany)) do,
       wartosc
    FROM CALL
)
,datym as (
    SELECT DISTINCT 
	   cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dateadd(m,number*-1,GETDATE()))+1,0)) as date) dm
    FROM 
	   master..[spt_values] 
    WHERE 
	   number BETWEEN 0 AND 24
)
,dt AS (
     SELECT d.id_inw,
            d.etap,
            d.od,
            d.do,
            g.wartosc,
		  year(dm) y,
		  MONTH(dm) m
     FROM daneoddo d
		 inner join datym on datym.dm between d.Od and d.do
          INNER JOIN glowna g ON g.id = d.id_inw
)
SELECT
    dt.y,
    dt.m, 
    dt.etap,
    COUNT(id_inw) Liczba,
    SUM(wartosc) Wartosc_inw,
    STR(ROUND(SUM(wartosc) * 100.0 / di.w, 2), 6, 2)+'%' AS Procent_wartosci_inw,
    STR(ROUND(COUNT(*) * 100.0 / di.i, 2), 6, 2)+'%' AS Procent_liczby
FROM 
    dt
    INNER JOIN(SELECT 
               Y,
               M,
                COUNT(*) i,
                SUM(wartosc) w
             FROM 
                dt
              GROUP BY 
                Y,
                M) di ON DI.M=DT.M AND DI.Y=DT.Y
GROUP BY 
    dt.etap,
    di.i,
    di.w,
    dt.y,
    dt.m
ORDER BY 
    dt.Y
   ,dt.M   

Zwróć uwagę, że nie ma już CROSS JOIN, Ponieważ ilości i sumy globalne musimy wyliczyć na dany rok i miesiąc

http://sqlfiddle.com/#!18/8f2ae/59

Zmieniłem widok datym który uwzględnia wszystkie miesiące z historii, bez wskazywania na sztywno:

SELECT DISTINCT 
	   cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dateadd(m,number*-1,GETDATE()))+1,0)) as date) dm
    FROM 
	   master..[spt_values] 
    WHERE 
	   number BETWEEN 0 AND (select datediff(m,min(data_zmiany),getdate()) from historia)

http://sqlfiddle.com/#!18/8f2ae/64

Muszę na spokojnie to sobie rozpisać i poczytać przez weekend... ;)
@Panczo powinieneś jakieś kursy prowadzić z SQL'a ;)

No to przyszła mi do głowy ostatnia statystyka mianowicie mam datę rozpoczęcia inwestycji i datę zakończenia inwestycji.
Chciałbym rozpisać wszystkie inwestycje na poszczególne miesiące czyli załóżmy na dzień 01-07-2017 były 3 inwestycje czyli zawierające się miedzy dwoma datami rozpoczęcia i zakończenia inwestycji.

(1, '2018-01-11', '2018-06-18', 12),
(2, '2017-03-15', '2018-06-17', 24),
(3, '2017-03-14', '2018-06-13', 36),

W pierwszym kroku muszę policzyć ile miesięcy trwa inwestycja dla pierwszego rekordu będzie to 18 miesięcy trwania inwestycji i teraz wartość 12 dzielę przez 18 czyli wychodzi mi 0,66, dl;a drugiego przypadku będzie to 24/16=1,5 a dla trzeciego będzie to wynik 36/16=2,25. I teraz chcę pogrupować to p[o miesiącach i zsumować wynik miesięczny dla poszczególnych inwestycji czyli 0,66+1,5+2,25=4,41
Reasumując finalnie pragnąłbym osiągnąć taki rezultat o ile to możliwe załóżmy pokaż mi inwestycje od 01-01-2017 do 30-06-2018:

rok| miesiąc|Liczba inwestycji|suma_wartość
---------------- | -------------------
2017 | 01 | 1 | 1,5
2017 | 02 | 1 | 1,5
2017 | 03 | 3 | 4,41
2017 | 04 | 3 | 4,41
2017 | 05 | 3 | 4,41
2017 | 06 | 3 | 4,41
2017 | 07 | 3 | 4,41
...|...|...|...
2018| 06 | 3 | 4,41

Przygotowałem odpowiedni SqlFiddle ;)
http://sqlfiddle.com/#!18/6f156b

Strasznie jesteś nieprecyzyjny. Piszesz o inwestycjach na dzień 2017-07-01 i w przykładzie podajesz:

(1, '2018-01-11', '2018-06-18', 12)

Może ja nie rozumiem, ale dla mnie to w 2017 tej inwestycji jeszcze nie było, dalej:

W pierwszym kroku muszę policzyć ile miesięcy trwa inwestycja dla pierwszego rekordu będzie to 18 miesięcy trwania inwestycji

no dla mnie w pierwszym rekordzie to jest 6 (lub 5 zależy jak liczyć) miesięcy.

Jeżeli jednak dobrze zrozumiałem to chcesz mieć pogrupowane po kolumnie miesiąć i rok, gdzie policzysz sumę ilorazów wartość/czastrwania inwestycji w miesiącach) jak i policzysz liczbe inwestycji.

Można tak:

with datym as (SELECT DISTINCT 
       CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dateadd(m,NUMBER*-1,GETDATE()))+1,0)) AS DATE) dm
    FROM 
       master..[spt_values] 
    WHERE 
       NUMBER BETWEEN 0 AND (SELECT datediff(m,MIN(data_rozpoczecia),getdate()) FROM glowna)
),dt as (
     SELECT d.id,
            data_rozpoczecia od,
            data_zakonczenia do,
            wartosc,
          YEAR(dm) y,
          MONTH(dm) m,
          cast(wartosc as numeric(18,2))/(datediff(m,data_rozpoczecia,data_zakonczenia)+1.00) wm
     FROM glowna d
         INNER JOIN datym ON datym.dm BETWEEN d.data_rozpoczecia AND d.data_zakonczenia
  where datym.dm between '2017-01-01' and  '2018-06-30'
       
)
  
select 
    y
    ,m
    ,count(id) ilosc_inwestycji
    ,sum(wm)
from dt
group by 
    y
    ,m
order by y,m

http://sqlfiddle.com/#!18/b1eda/6

Tak mój błąd z ta wartością. Ale cosik to nie tak prądzi bo wystarczy że dam jedną inwestycję w przód i rozpisuje mi tylko do czerwca tego roku zamiast do 2020 jak ustawiłem:

http://sqlfiddle.com/#!18/2df0a/1

OK już rozkminiłem chyba ;)
http://sqlfiddle.com/#!18/2df0a/2

Teraz próbuję zmienić sobie to na kwartały ale niektóre pozycje mi podwaja nie wiedzieć czemu ;(
http://sqlfiddle.com/#!18/2df0a/11

Odkurzę trochę temat mój bo napotkałem na problem mianowicie de facto nic nie zrobiłem a po dopisaniu sobie paru inwestycji do mojej bazy przestała mi działać ta funkcja ;( i mam taki komunikat:

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Warning: Null value is eliminated by an aggregate or other SET operation.

with datym as (SELECT DISTINCT 
       CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dateadd(m,NUMBER*-1,'2020-06-30'))+1,0)) AS DATE) dm
    FROM 
       master..[spt_values] 
    WHERE 
       NUMBER BETWEEN 0 AND (SELECT datediff(m,MIN([Data rozpoczęcia]),'2020-06-30') FROM [NaukaSQL].[dbo].[Testowa11])
),dt as (
     SELECT d.id,
            [Data rozpoczęcia] od,
            [Data zakończenia] do,
            [Wartość liczbowa],
          YEAR(dm) y,
          MONTH(dm) m,
          cast([Wartość liczbowa] as numeric(18,2))/(datediff(m,[Data rozpoczęcia],[Data zakończenia])+1.00) wm
     FROM [NaukaSQL].[dbo].[Testowa11] d
         INNER JOIN datym ON datym.dm BETWEEN d.[Data rozpoczęcia] AND d.[Data zakończenia]
  where datym.dm between '2017-01-01' and  '2020-06-30' AND [Data rozpoczęcia] is not null and [Data zakończenia] is not null and [Wartość liczbowa] > 0
       
)
  
select 
    y
    ,m
    ,count(id) ilosc_inwestycji
    ,SUM(wm)
from dt
group by 
    y
    ,m
order by y,m

Kombinuje na wszystkie sposoby i nic... ;( jak usunę z SELECT sumowanie kolumny SUM(wm) to ilości inwestycji mi zlicza i wszystkie wyniki się ładnie wyświetlają.
Ale jak tylko dodać sumowanie to niestety nie przekazuje sumy z zapytania:

cast([Wartość liczbowa] as numeric(18,2))/(datediff(m,[Data rozpoczęcia],[Data zakończenia])+1.00) wm

Nie wiem co jest powodem tego komunikatu.... Z góry dziękuje za pomoc.

No jak, przecież jak byk masz napisane... Divide by zero error encountered.. Czyli błąd dzielenia przez 0. Dzielenie masz tylko w jednym miejscu...

CAST([Wartość liczbowa] AS NUMERIC(18,2))/(datediff(m,[DATA rozpoczęcia],[DATA zakończenia])+1.00) wm

czyli ten datediff musi ci zwracać -1, aby cały mianownik był równy 0. Czyli masz gdzieś skopane data_rozpoczecia/data_zakonczenia. Aż się prosi o CHECK Constraint