|
Вложенный запрос дат для ежедневной выгрузки
#39976568
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Добрый день.
Используя нижеуказанный код, из 1с выгружаю остатки на дату. Как видно из запроса дата пишется руками. Помогите подключить к запросу таблицу дат, что бы выгружалось за период. Условно таблица дат называется tabledate. Как включить ее в запрос, что бы вместо 1 даты, выгружался период. Заранее благодарю.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
exec sp_executesql N'SELECT
T1.Fld18613FinalBalance_ as остатки,
T1.Fld18614FinalBalance_ as цена,
cast(T4._Fld21895RRef as uniqueidentifier) as магазин,
cast(T4._Fld21888RRef as uniqueidentifier) as ном,
,
t3.Fld18613Expense_,
t3.Fld18614Receipt_,
t3.Fld18614Expense_
FROM (SELECT
T2.Fld18605RRef AS Fld18605RRef,
CAST(SUM(T2.Fld18613Balance_ + T2.Fld18613Receipt_ - T2.Fld18613Expense_) AS NUMERIC(29, 3)) AS Fld18613FinalBalance_ as t1,
CAST(SUM(T2.Fld18614Balance_ + T2.Fld18614Receipt_ - T2.Fld18614Expense_) AS NUMERIC(29, 2)) AS Fld18614FinalBalance_ as t2,
FROM (SELECT
T3._Fld18605RRef AS Fld18605RRef,
CAST(SUM(0.0) AS NUMERIC(1, 0)) AS Fld18613Balance_,
CAST(SUM(CASE WHEN T3._RecordKind = 0.0 THEN T3._Fld18613 ELSE 0.0 END) AS NUMERIC(21, 3)) AS Fld18613Receipt_,
CAST(SUM(CASE WHEN T3._RecordKind = 0.0 THEN 0.0 ELSE T3._Fld18613 END) AS NUMERIC(21, 3)) AS Fld18613Expense_,
CAST(SUM(0.0) AS NUMERIC(1, 0)) AS Fld18614Balance_,
CAST(SUM(CASE WHEN T3._RecordKind = 0.0 THEN T3._Fld18614 ELSE 0.0 END) AS NUMERIC(21, 2)) AS Fld18614Receipt_,
CAST(SUM(CASE WHEN T3._RecordKind = 0.0 THEN 0.0 ELSE T3._Fld18614 END) AS NUMERIC(21, 2)) AS Fld18614Expense_
FROM dbo._AccumRg18604 T3
WHERE ((T3._Fld1205 = @P1)) AND (T3._Period <= @P2 AND T3._Active = 0x01)
GROUP BY T3._Fld18605RRef
HAVING (CAST(SUM(0.0) AS NUMERIC(1, 0))) <> 0.0 OR (CAST(SUM(CASE WHEN T3._RecordKind = 0.0 THEN T3._Fld18613 ELSE 0.0 END) AS NUMERIC(21, 3))) <> 0.0 OR (CAST(SUM(CASE WHEN T3._RecordKind = 0.0 THEN 0.0 ELSE T3._Fld18613 END) AS NUMERIC(21, 3))) <> 0.0 OR (CAST(SUM(0.0) AS NUMERIC(1, 0))) <> 0.0 OR (CAST(SUM(CASE WHEN T3._RecordKind = 0.0 THEN T3._Fld18614 ELSE 0.0 END) AS NUMERIC(21, 2))) <> 0.0 OR (CAST(SUM(CASE WHEN T3._RecordKind = 0.0 THEN 0.0 ELSE T3._Fld18614 END) AS NUMERIC(21, 2))) <> 0.0) T2
GROUP BY T2.Fld18605RRef
HAVING (CAST(SUM(T2.Fld18613Balance_ + T2.Fld18613Receipt_ - T2.Fld18613Expense_) AS NUMERIC(29, 3))) <> 0.0 OR (CAST(SUM(T2.Fld18614Balance_ + T2.Fld18614Receipt_ - T2.Fld18614Expense_) AS NUMERIC(29, 2))) <> 0.0) T1
LEFT OUTER JOIN dbo._Reference171 T4
ON (T1.Fld18605RRef = T4._IDRRef) AND (T4._Fld1205 = @P3)',N'@P1 numeric(10),@P2 datetime2(3),@P3 numeric(10)',0,'4020-06-01 23:59:59',0
|
|
|
