Имя: Пароль:
1C
1C 7.7
 v7: не работает запрос
,
☑
0 Popkorm
 
08.04.12
✎
11:53
В запрос добавил строку ДатаОтчета,все ссылающие строки SQL на ДатаОтчета имеет формат datatime,при выполнении запроса ошибка такая:
State 22007, native 241, message [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string.
Синтаксическая ошибка преобразования даты из строки символов.


|Select
|Docs.PrihRash as [ВидДвиженияПриходРасход],
|Docs.VidOper as [ВидОперацииСТоваром],
|Docs.Project as [Проект $Справочник.Проекты],
|Docs.Org as [Организация $Справочник.Фирмы],
|Docs.Sklad as [Склад $Справочник.Склады],
|Docs.DataSklad as [ДатаОтчета],
|Docs.Kontr as [Контрагент $Справочник.Контрагенты],
|Docs.KontrSklad as [КонтрагентСклад $Справочник.Склады],
|Docs.Nomenkl as [Номенклатура $Справочник.Номенклатура],
|Sum(Docs.Kolvo) as [Количество],
|Sum(Docs.Summa) as [Сумма]
|From
|(
|Select -- ВводОстатковТМЦ
|DocHeader.IdDoc as IdDoc,
|'Приход' as PrihRash,
|'НачальныйОстаток' as VidOper,
|Journ.SP798 as Project,
|Journ.SP4056 as Org,
|DocHeader.SP3941 as Sklad,
|'' as DataSklad,
|'' as Kontr,
|'' as KontrSklad,
|DocTable.SP3944 as Nomenkl,
|DocTable.SP3945 * Fenix_TD.dbo.fn_NotNull(DocTable.SP3947) as Kolvo,
|DocTable.SP3948 as Summa
|From
|_1sjourn as Journ
|inner join DH3957 as DocHeader on DocHeader.IdDoc = Journ.IdDoc
|inner join DT3957 as DocTable on DocTable.IdDoc = Journ.IdDoc
|Where Left(Journ.DATE_TIME_IDDOC,8) = '" + Формат(Источник,"ДГГГГММДД") + "'
|And Journ.CLOSED = 1
|And DocHeader.SP3935 = '   2Y7   ' -- Вид операции ОстаткиТМЦ

|Union All
|Select -- ВыпускПродукции
|DocHeader.IdDoc as IdDoc,
|'Приход' as PrihRash,
|'КупляПродажа' as VidOper,
|Journ.SP798 as Project,
|Journ.SP4056 as Org,
|DocHeader.SP6883 as Sklad,
|DocHeader.SP6887 as DataSklad,  
|'' as Kontr,
|'' as KontrSklad,
|DocTable.SP6889 as Nomenkl,
|DocTable.SP6890 * Fenix_TD.dbo.fn_NotNull(DocTable.SP6892) as Kolvo,
|DocTable.SP6894 * Fenix_TD.dbo.fn_NotNull(DocHeader.SP6885) as Summa
|From
|_1sjourn as Journ
|inner join DH6898 as DocHeader on DocHeader.IdDoc = Journ.IdDoc
|inner join DT6898 as DocTable on DocTable.IdDoc = Journ.IdDoc
|Where Left(Journ.DATE_TIME_IDDOC,8) = '" + Формат(Источник,"ДГГГГММДД") + "'
|And Journ.CLOSED = 1

|Union All
|Select -- ПоступлениеТМЦ
|DocHeader.IdDoc as IdDoc,
|'Приход' as PrihRash,
|Case
|When Journ.SP798 in (" + СтрокаПроектВозврат + ") Then 'Возврат'
|Else 'КупляПродажа'
|End as VidOper,
|Journ.SP798 as Project,
|Journ.SP4056 as Org,
|DocHeader.SP1565 as Sklad,
|DocHeader.SP1555 as Kontr,
|DocHeader.SP6561 as DataSklad,
|'' as KontrSklad,
|DocTable.SP1569 as Nomenkl,
|DocTable.SP1570 * Fenix_TD.dbo.fn_NotNull(DocTable.SP1572) as Kolvo,
|(Case
|    When DocHeader.SP1561 = 1 and DocHeader.SP1562 = 0 Then DocTable.SP1574 + DocTable.SP1575 -- Если УчитыватьНДС и СуммаВклНДС Тогда + СуммаНДС
|Else DocTable.SP1574
|end) * Fenix_TD.dbo.fn_NotNull(DocHeader.SP1558) as Summa
|From
|_1sjourn as Journ
|inner join DH1582 as DocHeader on DocHeader.IdDoc = Journ.IdDoc
|inner join DT1582 as DocTable on DocTable.IdDoc = Journ.IdDoc
|Where Left(Journ.DATE_TIME_IDDOC,8) = '" + Формат(Источник,"ДГГГГММДД") + "'
|And Journ.CLOSED = 1

|Union All
|Select -- ПоступлениеТМЦИмпорт
|DocHeader.IdDoc as IdDoc,
|'Приход' as PrihRash,
|'КупляПродажа' as VidOper,
|Journ.SP798 as Project,
|Journ.SP4056 as Org,
|DocHeader.SP3281 as Sklad,
|DocHeader.SP3282 as Kontr,
|DocHeader.SP6563 as DataSklad,
|'' as KontrSklad,
|DocTable.SP3298 as Nomenkl,
|DocTable.SP3299 * Fenix_TD.dbo.fn_NotNull(DocTable.SP3301) as Kolvo,
|DocTable.SP3303 * Fenix_TD.dbo.fn_NotNull(DocHeader.SP3285) + DocTable.SP3305 + DocTable.SP3307 as Summa -- + СуммаПошлиныРуб + СуммаНДСРуб
|From
|_1sjourn as Journ
|inner join DH3311 as DocHeader on DocHeader.IdDoc = Journ.IdDoc
|inner join DT3311 as DocTable on DocTable.IdDoc = Journ.IdDoc
|Where Left(Journ.DATE_TIME_IDDOC,8) = '" + Формат(Источник,"ДГГГГММДД") + "'
|And Journ.CLOSED = 1

|Union All
|Select -- Реализация
|DocHeader.IdDoc as IdDoc,
|'Расход' as PrihRash,
|'КупляПродажа' as VidOper,
|Journ.SP798 as Project,
|Journ.SP4056 as Org,
|DocHeader.SP1593 as Sklad,
|DocHeader.SP1583 as Kontr,
|DocHeader.SP6510 as DataSklad,
|'' as KontrSklad,
|DocTable.SP1599 as Nomenkl,
|DocTable.SP1600 * Fenix_TD.dbo.fn_NotNull(DocTable.SP1602) as Kolvo,
|(Case
|When DocHeader.SP1589 = 1 and DocHeader.SP1590 = 0 Then DocTable.SP1604 + DocTable.SP1605 -- Если УчитыватьНДС и СуммаВклНДС Тогда + СуммаНДС
|Else DocTable.SP1604
|end) * Fenix_TD.dbo.fn_NotNull(DocHeader.SP1586) as Summa
|From
|_1sjourn as Journ
|inner join DH1611 as DocHeader on DocHeader.IdDoc = Journ.IdDoc
|inner join DT1611 as DocTable on DocTable.IdDoc = Journ.IdDoc
|Where Left(Journ.DATE_TIME_IDDOC,8) = '" + Формат(Источник,"ДГГГГММДД") + "'
|And Journ.CLOSED = 1

|Union All
|Select -- ВозвратПоставщику
|DocHeader.IdDoc as IdDoc,
|'Расход' as PrihRash,
|'Возврат' as VidOper,
|Journ.SP798 as Project,
|Journ.SP4056 as Org,
|DocHeader.SP1667 as Sklad,
|DocHeader.SP1657 as Kontr,
|DocHeader.SP6545 as DataSklad,
|'' as KontrSklad,
|DocTable.SP1672 as Nomenkl,
|DocTable.SP1673 * Fenix_TD.dbo.fn_NotNull(DocTable.SP1675) as Kolvo,
|(Case
|When DocHeader.SP1663 = 1 and DocHeader.SP1664 = 0 Then DocTable.SP1677 + DocTable.SP1678 -- Если УчитыватьНДС и СуммаВклНДС Тогда + СуммаНДС
|Else DocTable.SP1677
|end) * Fenix_TD.dbo.fn_NotNull(DocHeader.SP1660) as Summa
|From
|_1sjourn as Journ
|inner join DH1684 as DocHeader on DocHeader.IdDoc = Journ.IdDoc
|inner join DT1684 as DocTable on DocTable.IdDoc = Journ.IdDoc
|Where Left(Journ.DATE_TIME_IDDOC,8) = '" + Формат(Источник,"ДГГГГММДД") + "'
|And Journ.CLOSED = 1

|Union All
|Select -- ВозвратОтПокупателя
|DocHeader.IdDoc as IdDoc,
|'Приход' as PrihRash,
|'Возврат' as VidOper,
|Journ.SP798 as Project,
|Journ.SP4056 as Org,
|DocHeader.SP1639 as Sklad,
|DocHeader.SP1629 as Kontr,
|DocHeader.SP6543 as DataSklad,
|'' as KontrSklad,
|DocTable.SP1644 as Nomenkl,
|DocTable.SP1645 * Fenix_TD.dbo.fn_NotNull(DocTable.SP1647) as Kolvo,
|(Case
|When DocHeader.SP1635 = 1 and DocHeader.SP1636 = 0 Then DocTable.SP1649 + DocTable.SP1650 -- Если УчитыватьНДС и СуммаВклНДС Тогда + СуммаНДС
|Else DocTable.SP1649
|end) * Fenix_TD.dbo.fn_NotNull(DocHeader.SP1632) as Summa
|From
|_1sjourn as Journ
|inner join DH1656 as DocHeader on DocHeader.IdDoc = Journ.IdDoc
|inner join DT1656 as DocTable on DocTable.IdDoc = Journ.IdDoc
|Where Left(Journ.DATE_TIME_IDDOC,8) = '" + Формат(Источник,"ДГГГГММДД") + "'
|And Journ.CLOSED = 1

|Union All
|Select -- ОприходованиеТМЦ
|DocHeader.IdDoc as IdDoc,
|'Приход' as PrihRash,
|'ИзлишкиНедостачи' as VidOper,
|Journ.SP798 as Project,
|Journ.SP4056 as Org,
|DocHeader.SP2094 as Sklad,
|'' as DataSklad,
|'' as Kontr,
|'' as KontrSklad,
|DocTable.SP2098 as Nomenkl,
|DocTable.SP2099 * Fenix_TD.dbo.fn_NotNull(DocTable.SP2101) as Kolvo,
|0 as Summa
|From
|_1sjourn as Journ
|inner join DH2106 as DocHeader on DocHeader.IdDoc = Journ.IdDoc
|inner join DT2106 as DocTable on DocTable.IdDoc = Journ.IdDoc
|Where Left(Journ.DATE_TIME_IDDOC,8) = '" + Формат(Источник,"ДГГГГММДД") + "'
|And Journ.CLOSED = 1

|Union All
|Select -- СписаниеТМЦ
|DocHeader.IdDoc as IdDoc,
|'Расход' as PrihRash,
|'ИзлишкиНедостачи' as VidOper,
|Journ.SP798 as Project,
|Journ.SP4056 as Org,
|DocHeader.SP1777 as Sklad,
|DocHeader.SP6570 as DataSklad,
|'' as Kontr,
|'' as KontrSklad,
|DocTable.SP1782 as Nomenkl,
|DocTable.SP1783 * Fenix_TD.dbo.fn_NotNull(DocTable.SP1785) as Kolvo,
|0 as Summa
|From
|_1sjourn as Journ
|inner join DH1790 as DocHeader on DocHeader.IdDoc = Journ.IdDoc
|inner join DT1790 as DocTable on DocTable.IdDoc = Journ.IdDoc
|Where Left(Journ.DATE_TIME_IDDOC,8) = '" + Формат(Источник,"ДГГГГММДД") + "'
|And Journ.CLOSED = 1

|Union All
|Select -- ПеремещениеТМЦ
|DocHeader.IdDoc as IdDoc,
|'Расход' as PrihRash,
|'Перемещение' as VidOper,
|Journ.SP798 as Project,
|Journ.SP4056 as Org,
|DocHeader.SP3078 as Sklad,
|DocHeader.SP6511 as DataSklad,
|'' as Kontr,
|DocHeader.SP1615 as KontrSklad,
|DocTable.SP1620 as Nomenkl,
|DocTable.SP1621 * Fenix_TD.dbo.fn_NotNull(DocTable.SP1623) as Kolvo,
|0 as Summa
|From
|_1sjourn as Journ
|inner join DH1628 as DocHeader on DocHeader.IdDoc = Journ.IdDoc
|inner join DT1628 as DocTable on DocTable.IdDoc = Journ.IdDoc
|Where Left(Journ.DATE_TIME_IDDOC,8) = '" + Формат(Источник,"ДГГГГММДД") + "'
|And Journ.CLOSED = 1
|And DocHeader.SP3078 <> DocHeader.SP1615

|Union All
|Select -- ПеремещениеТМЦ (Приход)
|DocHeader.IdDoc as IdDoc,
|'Приход' as PrihRash,
|'Перемещение' as VidOper,
|Journ.SP798 as Project,
|DocHeader.SP6511 as DataSklad,
|Journ.SP4056 as Org,
|DocHeader.SP1615 as Sklad,
|'' as Kontr, -- СкладОтправитель
|DocHeader.SP3078 as KontrSklad,
|DocTable.SP1620 as Nomenkl,
|DocTable.SP1621 * Fenix_TD.dbo.fn_NotNull(DocTable.SP1623) as Kolvo,
|0 as Summa
|From
|_1sjourn as Journ
|inner join DH1628 as DocHeader on DocHeader.IdDoc = Journ.IdDoc
|inner join DT1628 as DocTable on DocTable.IdDoc = Journ.IdDoc
|Where Left(Journ.DATE_TIME_IDDOC,8) = '" + Формат(Источник,"ДГГГГММДД") + "'
|And Journ.CLOSED = 1
|And DocHeader.SP3078 <> DocHeader.SP1615
|) as Docs
|Group By
|Docs.PrihRash,
|Docs.DataSklad,
|Docs.VidOper,
|Docs.Project,
|Docs.Org,
|Docs.Sklad,
|Docs.Kontr,
|Docs.KontrSklad,
|Docs.Nomenkl
|";
1 vicof
 
08.04.12
✎
12:02
Сочувствую
2 Popkorm
 
08.04.12
✎
12:15
(1)по умничать зашел?!
3 vicof
 
08.04.12
✎
12:15
(2) ага, кстати, "поумничать" слитно пишется
4 Torquader
 
08.04.12
✎
12:18
Есть мнение, что DateTime сначала нужно преобразовать в строку через CAST, а уже после этого резать от него кусок, а ещё лучше преобразовать в дату и сравнивать, так как дата в строку от региональных настроек зависит.
5 МихаилМ
 
08.04.12
✎
12:33
(0)
если запрос для оперативного учета тогда забыли maxdop 1
иначе
забыли  nolock


Как Вы думаете? таблицы лучше соединять по первичному ключу или по внешнему?
6 Popkorm
 
08.04.12
✎
12:42
(4)"а ещё лучше преобразовать в дату и сравнивать, так как дата в строку от региональных настроек зависит."
примерчик можно?!
7 Torquader
 
08.04.12
✎
12:46
Вместо
Where Left(Journ.DATE_TIME_IDDOC,8) = '" + Формат(Источник,"ДГГГГММДД")
Написать Cast(Journ.DATE_TIME_IDDOC As Date)='"+String(Источник)+"'
Чтобы дату сравнивать с датой.
8 МихаилМ
 
08.04.12
✎
12:55
(7)
оба варианта ересь богомерзкая ламерная
тк отменяют использование индекса
те приводят к fullscan.
9 Anton_2011
 
08.04.12
✎
12:57
Попкорм, забей на семёрку. Сейчас все на восьмерку дрочат во благо компании 1Ц
10 Злой Бобр
 
08.04.12
✎
12:58
(0) Where Left(Journ.DATE_TIME_IDDOC,8) = '" + Формат(Источник,"ДГГГГММДД") + "'
Where Journ.DATE_TIME_IDDOC BETWEEN :НачДата AND :КонДата~

И вообще запрос написан криво. Хотя чему я удивляюсь ...
11 Anton_2011
 
08.04.12
✎
13:01
Ну зачем косатыли к 77 прикручивать в видк 1С++? Переходите на 8! Там СКД, там УФ - дрочите!
12 МихаилМ
 
08.04.12
✎
13:06
модераторы забаньте пожалуйста (11)
13 Torquader
 
08.04.12
✎
13:06
(11) Если есть рабочая система на 77, то перенести её на 8 будет не так просто, и ради одного запроса явно не стоит этого делать.
14 Anton_2011
 
08.04.12
✎
13:10
Модераторы, мишуток забаньте пожалуйста, а то они очень рьяно отсасывают у 1Ц.
(13) Согласен. Но в 8-ке, удобные запросы. И без костылей. И не ради одного запроса, а ради комфорта в будущем.
15 Злой Бобр
 
08.04.12
✎
13:13
(14) Да вы уже тогда друг друга баньте, только не тут. А предлагать сменить одни костыли на другие - это как то мазохизмом отдает.
16 Popkorm
 
08.04.12
✎
13:16
(7)
State 42000, native 170, message [Microsoft][ODBC SQL Server Driver][SQL Server]Line 33: Incorrect syntax near 'Cast'.
|Select
|Docs.PrihRash as [ВидДвиженияПриходРасход],
|Docs.VidOper as [ВидОперацииСТоваром],
|Docs.Project as [Проект $Справочник.Проекты],
|Docs.Org as [Организация $Справочник.Фирмы],
|Docs.Sklad as [Склад $Справочник.Склады],
|Docs.DataSklad as [ДатаОтчета],
|Docs.Kontr as [Контрагент $Справочник.Контрагенты],
|Docs.KontrSklad as [КонтрагентСклад $Справочник.Склады],
|Docs.Nomenkl as [Номенклатура $Справочник.Номенклатура],
|Sum(Docs.Kolvo) as [Количество],
|Sum(Docs.Summa) as [Сумма]
|From
|(
|Select -- ВводОстатковТМЦ
|DocHeader.IdDoc as IdDoc,
|'Приход' as PrihRash,
|'НачальныйОстаток' as VidOper,
|Journ.SP798 as Project,
|Journ.SP4056 as Org,
|DocHeader.SP3941 as Sklad,
|'' as DataSklad,
|'' as Kontr,
|'' as KontrSklad,
|DocTable.SP3944 as Nomenkl,
|DocTable.SP3945 * Fenix_TD.dbo.fn_NotNull(DocTable.SP3947) as Kolvo,
|DocTable.SP3948 as Summa
|From
|_1sjourn as Journ
|inner join DH3957 as DocHeader on DocHeader.IdDoc = Journ.IdDoc
|inner join DT3957 as DocTable on DocTable.IdDoc = Journ.IdDoc
|Cast(Journ.DATE_TIME_IDDOC As Date)='"+String(Источник)+"'
//|Where Left(Journ.DATE_TIME_IDDOC,8) = '" + Формат(Источник,"ДГГГГММДД") + "'
|And Journ.CLOSED = 1
|And DocHeader.SP3935 = '   2Y7   ' -- Вид операции ОстаткиТМЦ

|Union All
|Select -- ВыпускПродукции
|DocHeader.IdDoc as IdDoc,
|'Приход' as PrihRash,
|'КупляПродажа' as VidOper,
|Journ.SP798 as Project,
|Journ.SP4056 as Org,
|DocHeader.SP6883 as Sklad,
|DocHeader.SP6887 as DataSklad,  
|'' as Kontr,
|'' as KontrSklad,
|DocTable.SP6889 as Nomenkl,
|DocTable.SP6890 * Fenix_TD.dbo.fn_NotNull(DocTable.SP6892) as Kolvo,
|DocTable.SP6894 * Fenix_TD.dbo.fn_NotNull(DocHeader.SP6885) as Summa
|From
|_1sjourn as Journ
|inner join DH6898 as DocHeader on DocHeader.IdDoc = Journ.IdDoc
|inner join DT6898 as DocTable on DocTable.IdDoc = Journ.IdDoc
|Cast(Journ.DATE_TIME_IDDOC As Date)='"+String(Источник)+"'
//|Where Left(Journ.DATE_TIME_IDDOC,8) = '" + Формат(Источник,"ДГГГГММДД") + "'
|And Journ.CLOSED = 1

|Union All
|Select -- ПоступлениеТМЦ
|DocHeader.IdDoc as IdDoc,
|'Приход' as PrihRash,
|Case
|When Journ.SP798 in (" + СтрокаПроектВозврат + ") Then 'Возврат'
|Else 'КупляПродажа'
|End as VidOper,
|Journ.SP798 as Project,
|Journ.SP4056 as Org,
|DocHeader.SP1565 as Sklad,
|DocHeader.SP1555 as Kontr,
|DocHeader.SP6561 as DataSklad,
|'' as KontrSklad,
|DocTable.SP1569 as Nomenkl,
|DocTable.SP1570 * Fenix_TD.dbo.fn_NotNull(DocTable.SP1572) as Kolvo,
|(Case
|    When DocHeader.SP1561 = 1 and DocHeader.SP1562 = 0 Then DocTable.SP1574 + DocTable.SP1575 -- Если УчитыватьНДС и СуммаВклНДС Тогда + СуммаНДС
|Else DocTable.SP1574
|end) * Fenix_TD.dbo.fn_NotNull(DocHeader.SP1558) as Summa
|From
|_1sjourn as Journ
|inner join DH1582 as DocHeader on DocHeader.IdDoc = Journ.IdDoc
|inner join DT1582 as DocTable on DocTable.IdDoc = Journ.IdDoc
|Cast(Journ.DATE_TIME_IDDOC As Date)='"+String(Источник)+"'
//|Where Left(Journ.DATE_TIME_IDDOC,8) = '" + Формат(Источник,"ДГГГГММДД") + "'
|And Journ.CLOSED = 1

|Union All
|Select -- ПоступлениеТМЦИмпорт
|DocHeader.IdDoc as IdDoc,
|'Приход' as PrihRash,
|'КупляПродажа' as VidOper,
|Journ.SP798 as Project,
|Journ.SP4056 as Org,
|DocHeader.SP3281 as Sklad,
|DocHeader.SP3282 as Kontr,
|DocHeader.SP6563 as DataSklad,
|'' as KontrSklad,
|DocTable.SP3298 as Nomenkl,
|DocTable.SP3299 * Fenix_TD.dbo.fn_NotNull(DocTable.SP3301) as Kolvo,
|DocTable.SP3303 * Fenix_TD.dbo.fn_NotNull(DocHeader.SP3285) + DocTable.SP3305 + DocTable.SP3307 as Summa -- + СуммаПошлиныРуб + СуммаНДСРуб
|From
|_1sjourn as Journ
|inner join DH3311 as DocHeader on DocHeader.IdDoc = Journ.IdDoc
|inner join DT3311 as DocTable on DocTable.IdDoc = Journ.IdDoc
|Cast(Journ.DATE_TIME_IDDOC As Date)='"+String(Источник)+"'
//|Where Left(Journ.DATE_TIME_IDDOC,8) = '" + Формат(Источник,"ДГГГГММДД") + "'
|And Journ.CLOSED = 1

|Union All
|Select -- Реализация
|DocHeader.IdDoc as IdDoc,
|'Расход' as PrihRash,
|'КупляПродажа' as VidOper,
|Journ.SP798 as Project,
|Journ.SP4056 as Org,
|DocHeader.SP1593 as Sklad,
|DocHeader.SP1583 as Kontr,
|DocHeader.SP6510 as DataSklad,
|'' as KontrSklad,
|DocTable.SP1599 as Nomenkl,
|DocTable.SP1600 * Fenix_TD.dbo.fn_NotNull(DocTable.SP1602) as Kolvo,
|(Case
|When DocHeader.SP1589 = 1 and DocHeader.SP1590 = 0 Then DocTable.SP1604 + DocTable.SP1605 -- Если УчитыватьНДС и СуммаВклНДС Тогда + СуммаНДС
|Else DocTable.SP1604
|end) * Fenix_TD.dbo.fn_NotNull(DocHeader.SP1586) as Summa
|From
|_1sjourn as Journ
|inner join DH1611 as DocHeader on DocHeader.IdDoc = Journ.IdDoc
|inner join DT1611 as DocTable on DocTable.IdDoc = Journ.IdDoc
|Cast(Journ.DATE_TIME_IDDOC As Date)='"+String(Источник)+"'
//|Where Left(Journ.DATE_TIME_IDDOC,8) = '" + Формат(Источник,"ДГГГГММДД") + "'
|And Journ.CLOSED = 1

|Union All
|Select -- ВозвратПоставщику
|DocHeader.IdDoc as IdDoc,
|'Расход' as PrihRash,
|'Возврат' as VidOper,
|Journ.SP798 as Project,
|Journ.SP4056 as Org,
|DocHeader.SP1667 as Sklad,
|DocHeader.SP1657 as Kontr,
|DocHeader.SP6545 as DataSklad,
|'' as KontrSklad,
|DocTable.SP1672 as Nomenkl,
|DocTable.SP1673 * Fenix_TD.dbo.fn_NotNull(DocTable.SP1675) as Kolvo,
|(Case
|When DocHeader.SP1663 = 1 and DocHeader.SP1664 = 0 Then DocTable.SP1677 + DocTable.SP1678 -- Если УчитыватьНДС и СуммаВклНДС Тогда + СуммаНДС
|Else DocTable.SP1677
|end) * Fenix_TD.dbo.fn_NotNull(DocHeader.SP1660) as Summa
|From
|_1sjourn as Journ
|inner join DH1684 as DocHeader on DocHeader.IdDoc = Journ.IdDoc
|inner join DT1684 as DocTable on DocTable.IdDoc = Journ.IdDoc
|Cast(Journ.DATE_TIME_IDDOC As Date)='"+String(Источник)+"'
//|Where Left(Journ.DATE_TIME_IDDOC,8) = '" + Формат(Источник,"ДГГГГММДД") + "'
|And Journ.CLOSED = 1

|Union All
|Select -- ВозвратОтПокупателя
|DocHeader.IdDoc as IdDoc,
|'Приход' as PrihRash,
|'Возврат' as VidOper,
|Journ.SP798 as Project,
|Journ.SP4056 as Org,
|DocHeader.SP1639 as Sklad,
|DocHeader.SP1629 as Kontr,
|DocHeader.SP6543 as DataSklad,
|'' as KontrSklad,
|DocTable.SP1644 as Nomenkl,
|DocTable.SP1645 * Fenix_TD.dbo.fn_NotNull(DocTable.SP1647) as Kolvo,
|(Case
|When DocHeader.SP1635 = 1 and DocHeader.SP1636 = 0 Then DocTable.SP1649 + DocTable.SP1650 -- Если УчитыватьНДС и СуммаВклНДС Тогда + СуммаНДС
|Else DocTable.SP1649
|end) * Fenix_TD.dbo.fn_NotNull(DocHeader.SP1632) as Summa
|From
|_1sjourn as Journ
|inner join DH1656 as DocHeader on DocHeader.IdDoc = Journ.IdDoc
|inner join DT1656 as DocTable on DocTable.IdDoc = Journ.IdDoc
|Cast(Journ.DATE_TIME_IDDOC As Date)='"+String(Источник)+"'
//|Where Left(Journ.DATE_TIME_IDDOC,8) = '" + Формат(Источник,"ДГГГГММДД") + "'
|And Journ.CLOSED = 1

|Union All
|Select -- ОприходованиеТМЦ
|DocHeader.IdDoc as IdDoc,
|'Приход' as PrihRash,
|'ИзлишкиНедостачи' as VidOper,
|Journ.SP798 as Project,
|Journ.SP4056 as Org,
|DocHeader.SP2094 as Sklad,
|'' as DataSklad,
|'' as Kontr,
|'' as KontrSklad,
|DocTable.SP2098 as Nomenkl,
|DocTable.SP2099 * Fenix_TD.dbo.fn_NotNull(DocTable.SP2101) as Kolvo,
|0 as Summa
|From
|_1sjourn as Journ
|inner join DH2106 as DocHeader on DocHeader.IdDoc = Journ.IdDoc
|inner join DT2106 as DocTable on DocTable.IdDoc = Journ.IdDoc
|Cast(Journ.DATE_TIME_IDDOC As Date)='"+String(Источник)+"'
//|Where Left(Journ.DATE_TIME_IDDOC,8) = '" + Формат(Источник,"ДГГГГММДД") + "'
|And Journ.CLOSED = 1

|Union All
|Select -- СписаниеТМЦ
|DocHeader.IdDoc as IdDoc,
|'Расход' as PrihRash,
|'ИзлишкиНедостачи' as VidOper,
|Journ.SP798 as Project,
|Journ.SP4056 as Org,
|DocHeader.SP1777 as Sklad,
|DocHeader.SP6570 as DataSklad,
|'' as Kontr,
|'' as KontrSklad,
|DocTable.SP1782 as Nomenkl,
|DocTable.SP1783 * Fenix_TD.dbo.fn_NotNull(DocTable.SP1785) as Kolvo,
|0 as Summa
|From
|_1sjourn as Journ
|inner join DH1790 as DocHeader on DocHeader.IdDoc = Journ.IdDoc
|inner join DT1790 as DocTable on DocTable.IdDoc = Journ.IdDoc
|Cast(Journ.DATE_TIME_IDDOC As Date)='"+String(Источник)+"'
//|Where Left(Journ.DATE_TIME_IDDOC,8) = '" + Формат(Источник,"ДГГГГММДД") + "'
|And Journ.CLOSED = 1

|Union All
|Select -- ПеремещениеТМЦ
|DocHeader.IdDoc as IdDoc,
|'Расход' as PrihRash,
|'Перемещение' as VidOper,
|Journ.SP798 as Project,
|Journ.SP4056 as Org,
|DocHeader.SP3078 as Sklad,
|DocHeader.SP6511 as DataSklad,
|'' as Kontr,
|DocHeader.SP1615 as KontrSklad,
|DocTable.SP1620 as Nomenkl,
|DocTable.SP1621 * Fenix_TD.dbo.fn_NotNull(DocTable.SP1623) as Kolvo,
|0 as Summa
|From
|_1sjourn as Journ
|inner join DH1628 as DocHeader on DocHeader.IdDoc = Journ.IdDoc
|inner join DT1628 as DocTable on DocTable.IdDoc = Journ.IdDoc
|Cast(Journ.DATE_TIME_IDDOC As Date)='"+String(Источник)+"'
//|Where Left(Journ.DATE_TIME_IDDOC,8) = '" + Формат(Источник,"ДГГГГММДД") + "'
|And Journ.CLOSED = 1
|And DocHeader.SP3078 <> DocHeader.SP1615

|Union All
|Select -- ПеремещениеТМЦ (Приход)
|DocHeader.IdDoc as IdDoc,
|'Приход' as PrihRash,
|'Перемещение' as VidOper,
|Journ.SP798 as Project,
|DocHeader.SP6511 as DataSklad,
|Journ.SP4056 as Org,
|DocHeader.SP1615 as Sklad,
|'' as Kontr, -- СкладОтправитель
|DocHeader.SP3078 as KontrSklad,
|DocTable.SP1620 as Nomenkl,
|DocTable.SP1621 * Fenix_TD.dbo.fn_NotNull(DocTable.SP1623) as Kolvo,
|0 as Summa
|From
|_1sjourn as Journ
|inner join DH1628 as DocHeader on DocHeader.IdDoc = Journ.IdDoc
|inner join DT1628 as DocTable on DocTable.IdDoc = Journ.IdDoc
|Cast(Journ.DATE_TIME_IDDOC As Date)='"+String(Источник)+"'
//|Where Left(Journ.DATE_TIME_IDDOC,8) = '" + Формат(Источник,"ДГГГГММДД") + "'
|And Journ.CLOSED = 1
|And DocHeader.SP3078 <> DocHeader.SP1615
|) as Docs
|Group By
|Docs.PrihRash,
|Docs.DataSklad,
|Docs.VidOper,
|Docs.Project,
|Docs.Org,
|Docs.Sklad,
|Docs.Kontr,
|Docs.KontrSklad,
|Docs.Nomenkl
|";
17 Злой Бобр
 
08.04.12
✎
13:41
(16) Ну и ?.. В (10) я тебе написал как поменять условие на дату. Только неговори что неработает.
18 Mikeware
 
08.04.12
✎
14:10
А пользоваться метапарсером - религия мешает?
зы. даже разбираться в коде не стал....