Topic: Question on a SQL query
There is a field TipSvjaziTT and has 4 values:
0 is on all to the person
1 is to first sign of join of a TT
2 is to second sign of join of a TT
3 is on one TT
There is a first table. The list action with participating SKU
001,00111,0, 02.01.2018,03.01.2018, SKU1
Than one can participate in one action more SKU, but the periods identical. Lines with the codes action 004 see.
There is a second table. The TT list
[spoiler It is necessary to receive such table] ,,,SKU,
[/spoiler] Connection becomes so that the table with following fields turned out: Meroprijatie+JurLitso+KodTT (which gets through communication type) +SKU (which participates in action) + (which gets to the action period).
Started to write request and it was tangled.
1. How much I correctly understand that at the first stage we create the first intermediate table: on each action we create a line SKU+. At the second stage we create the second intermediate table: on each action there are lines immediately participating TT which have been selected by means of types of communication. At the third stage - we Connect the first and second intermediate table. For the given task on such algorithm it is necessary to solve?
2. For the first stage I try to use such idea . That that is impossible. How correctly to write request?
[spoiler SQL- with the initial data]
declare @ table (KodMeroprijatija varchar (3); The legal person varchar (5); integer; varchar (8); date; date; SKU varchar (4)) insert into @ select ' 001 ', ' 00111 ', 0, null, ' 1/2/2018 ', ' 1/3/2018 ', ' SKU1' insert into @ select ' 002 ', ' 00111 ', 1, ' 01_001 ', ' 1/3/2018 ', ' 1/4/2018 ', ' SKU2' insert into @ select ' 003 ', ' 00111 ', 2, ' 02_002 ', ' 1/4/2018 ', ' 1/5/2018 ', ' SKU3' insert into @ select ' 004 ', ' 00111 ', 3, ' 00004 ', ' 1/5/2018 ', ' 1/6/2018 ', ' SKU4' insert into @ select ' 004 ', ' 00111 ', 3, ' 00004 ', ' 1/5/2018 ', ' 1/6/2018 ', ' SKU1' /*Select * FROM @*/ declare @ table (KodTT varchar (5); The legal person varchar (5); varchar (6); varchar (6)) insert into @ select ' 00001 ', ' 00111 ', ' 01_001 ', ' 02_001' insert into @ select ' 00002 ', ' 00111 ', ' 01_001 ', ' 02_002' insert into @ select ' 00003 ', ' 00111 ', ' 01_002 ', ' 02_003' insert into @ select ' 00004 ', ' 00111 ', ' 01_002 ', ' 02_002' /*Select * FROM @*/ declare @ table (Date date) declare @ datetime = ' 1/1/2018' while @ <= ' 1/10/2018' begin insert @ (Date) select @ set @ = dateadd (dd, 1, @) end /*select * from @*/ select ; The legal person; SKU FROM @ outer apply (Select Date FROM @ where . Date> = SpisokMeroprijaty. AND . Date <= SpisokMeroprijaty. ) AS the Table
If to write so
where >>> <<<PromezhutochnajaTablitsaDaty. Date> = >>>@<<<. AND >>> <<<PromezhutochnajaTablitsaDaty. Date <= >>>@<<<.) AS the Table
that writes that it is necessary to declare scalar variables. And if to remove a sign writes that was not possible to anchor the composite identifier.
Whether and probably to do without @? All the same initially it is not known what initial and finite dates should be in this table.