есть запрос,куда добавляю новую колонку ДатаОтчета.Для ВводОстатковТМЦ нет в Скуле и в 1С 7.7 поле ДатаОтчета,из чего просто добавляю |' ' as DataSklad,(DataSklad=ДатаОтчета),но мне ее надо преобразовать как дату вот так:cast('17530101' as DateTime)--выражение для пустой даты.В документах где ДатаОтчета Пустое Значение в Скул попадает 1723-01-01 00:00:00
http://clip2net.com/s/1MVJs


|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,
|cast('17530101' as DateTime) 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,
|DocHeader.SP6551 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
|"