set dateformat dmy declare DateBegin varchar 32 01 06 2019 DateEnd var

 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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
set dateformat dmy
declare
@DateBegin varchar(32) = '01.06.2019',
@DateEnd varchar(32) = '30.06.2019',
@Text varchar(8000) = '',
@Type varchar(32) = 'Списание', -- Тип: Списание, Приобретение
@isGroupBy tinyint = 0, -- Группировка?
@isOrderBy tinyint = 0, -- Сортировка?
@isOverall tinyint = 1, -- Спецодежда?
@SelectFieldNames varchar(256) = 'nom.Kod,nom.NameAn,nom.Prizn,reg.Summa',--'sum(reg.Summa)',--,nom.Kod,nom.Prizn', --'nom.Kod,nom.NameAn,nom.Prizn,reg.Summa',
@GroupFieldNames varchar(256) = 'nom.Kod,nom.NameAn,nom.Prizn,reg.Summa',
@OrderFieldNames varchar(256) = 'sum(reg.Summa)'
set @Text = 'SELECT ' + @SelectFieldNames + '
FROM RGMatDvizhMat reg
INNER JOIN MBAnalit nom
on nom.Analit = reg.NaimMC
INNER JOIN MBPlanAcc Debet
on Debet.Acc = reg.BuhAcc
INNER JOIN MBPlanAcc Kredet
on Kredet.Acc = reg.BnkAcc
INNER JOIN MBAnalit TipSpis
on reg.TipSpisNom = TipSpis.Analit
WHERE (reg.DatReg >= ''' + @DateBegin + ''' and reg.DatReg <= ''' + @DateEnd + ''') and
reg.GruppaMC not in (2900757, 2900761, 2900759) and isnull(nom.Prizn, 0) = 3'
if (@Type = 'Списание')
set @Text = @Text + ' and ltrim(Debet.Kod) like ''10%'' and ltrim(Kredet.Kod) like ''2%'' '
if (@Type = 'Приобретение')
set @Text = @Text + ' and ltrim(Debet.Kod) = ''10.130'' and ltrim(Kredet.Kod) = ''60.201'' '
if (@isGroupBy = 1)
set @Text = @Text + ' Group by ' + @GroupFieldNames
if (@isOrderBy = 1)
set @Text = @Text + ' Order by ' + @OrderFieldNames
if (@isOverall = 1)
set @Text = replace(@Text,'reg.GruppaMC not in','reg.GruppaMC in')
print @Text
execute(@Text)