Изявление на Access SQL: ако няма валидни записи, върнете последния

Опитвам се да получа SQL оператор, който решава следния проблем.

Имам таблица "календар", която включва само една колона "дата". Тази таблица има 12 записа за всеки месец през 2019 г. (31.01.2019 г., 28.02.2019 г. и т.н.). Втората таблица „стойности“ (която получавам от ERP система) има три колони „от“, „до“ и „сума“ (напр. 01.01.2019, 06.30.2019, 50 и 08.01.2019, 08.31.2019, 100).

Имам това просто изявление, което проверява кой запис е валиден на конкретната дата:

SELECT Calendar.Date, Values.From, Values.To, Values.Amount
FROM Calendar, [Values]
WHERE Calendar.Date >= Values.From 
  AND Calendar.Date <= Values.To;

Няма валиден запис (в таблицата "стойности") за юли, септември, октомври, ноември и декември.

В случай че няма валиден запис, трябва да се използва последният запис. През юли ще бъде 50, а за септември, октомври ... ще бъде 100.

Опитах подзаявка и оставих съединения, но така и не получих желания резултат.

Някой има ли идея или по-добро решение за този проблем. Оценявам всяка подкрепа


sql left-join ms-access
person Paintitblack3k    schedule 22.02.2019    source източник
comment
Примерни данни и желани резултати биха помогнали.   -  person Gordon Linoff    schedule 23.02.2019
comment
ufile.io/qqitk   -  person Paintitblack3k    schedule 23.02.2019

Отговори (2)


arrow_upward
0
arrow_downward

Мисля, че търсите допълнително присъединяване към таблицата Values, което ще върне последния запис преди текущата дата. Когато първият (LEFT) JOIN не успее, можете да използвате резултата, върнат от втория.

За да намерим последния запис преди текущата дата, можем да използваме условие NOT EXISTS със свързана подзаявка.

SELECT 
    c.Date, 
    Nz(v.From, v1.From) AS [From], 
    Nz(v.To, v1.To) AS [To], 
    Nz(v.Amount, v1.Amount) AS [Amount]
FROM Calendar AS c
LEFT JOIN [Values] AS v 
    ON c.Date >= v.From AND c.Date <= v.To
LEFT JOIN [Values] AS v1 
    ON v1.To < c.Date
    AND NOT EXISTS (
        SELECT 1 FROM [Values] v2 WHERE v2.To < c.Date AND v2.To > v1.To
    )

PS: от дълго време в SQL е добра практика да се избягват старите неявни JOINs и винаги да се използват изрични JOINs.

person GMB    schedule 22.02.2019
comment
Винаги получавам синтактична грешка, но не мога да видя причината. - person Paintitblack3k; 23.02.2019
comment
@Paintitblack3k: коя точно грешка получаваш? - person GMB; 23.02.2019
comment
@Paintitblack3k : Поправих проблем в имената на колоните, кажете ми дали това работи за вас сега... Благодаря! - person GMB; 23.02.2019
comment
Получавам Syntaxerror (липсващ оператор) в ''. (дано го преведох точно). Може би зависи от версията на MS Access? - person Paintitblack3k; 23.02.2019

arrow_upward
0
arrow_downward

Можете да го направите с LEFT JOIN и подзаявка, за да получите последната сума:

SELECT c.Date, v.From, v.To, 
Nz(
  v.Amount, 
  (SELECT MAX([Values].Amount) FROM [Values] WHERE [Values].From = 
    (SELECT MAX([Values].From) FROM [Values] WHERE [Values].From <= c.Date))
) AS Amount
FROM Calendar AS c LEFT JOIN [Values] AS v
ON c.Date>=v.From AND c.Date<=v.To;
person forpas    schedule 22.02.2019
comment
Това работи доста добре. Утре ще го подчертая с някой друг случай. С случая, който описах, работи страхотно. - person Paintitblack3k; 23.02.2019
comment
SELECT c.Date, v.From, v.To, Nz( v.Amount, (SELECT Top 1 [Values].Amount FROM [Values] WHERE [Values].From <=c.Date ORDER BY v.From DESC) ) AS Amount FROM Calendar AS c LEFT JOIN [Values] AS v ON (c.Date>=v.From) AND (c.Date<=v.To); Какво ще кажете за само една подзаявка с TOP 1 и ORDER BY [Values].FROM DESC. Това не работи, но защо? - person Paintitblack3k; 23.02.2019
comment
TOP 1 има за цел да върне набор от резултати за ACCESS, използвайте MAX() без ORDER BY:SELECT c.Date, v.From, v.To, Nz( v.Amount, (SELECT MAX( [Values].Amount) FROM [Values] WHERE [Values].From <=c.Date)) AS Amount FROM Calendar AS c LEFT JOIN [Values] AS v ON (c.Date>=v.From) AND (c.Date<=v.To); - person forpas; 23.02.2019