1

Topic: Question on a SQL query

Hello!
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
,,,,,,SKU
001,00111,0, 02.01.2018,03.01.2018, SKU1
002,00111,1,01_001,03.01.2018,04.01.2018,SKU2
003,00111,2,02_002,04.01.2018,05.01.2018,SKU3
004,00111,3,00004,05.01.2018,06.01.2018,SKU4
004,00111,3,00004,05.01.2018,06.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
, ,,
00001,00111,01_001,02_001
00002,00111,01_001,02_002
00003,00111,01_002,02_003
00004,00111,01_002,02_002
[spoiler It is necessary to receive such table] ,,,SKU,
001,00111,00001, SKU1,02.01.2018
001,00111,00001, SKU1,03.01.2018
001,00111,00002, SKU1,02.01.2018
001,00111,00002, SKU1,03.01.2018
001,00111,00003, SKU1,02.01.2018
001,00111,00003, SKU1,03.01.2018
001,00111,00004, SKU1,02.01.2018
001,00111,00004, SKU1,03.01.2018
002,00111,00001, SKU2,03.01.2018
002,00111,00001, SKU2,04.01.2018
002,00111,00002, SKU2,03.01.2018
002,00111,00002, SKU2,04.01.2018
003,00111,00002, SKU3,04.01.2018
003,00111,00002, SKU3,05.01.2018
003,00111,00004, SKU3,04.01.2018
003,00111,00004, SKU3,05.01.2018
004,00111,00004, SKU4,05.01.2018
004,00111,00004, SKU4,06.01.2018
004,00111,00004, SKU1,05.01.2018
004,00111,00004, SKU1,06.01.2018
[/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.
Why so?
Whether and probably to do without @? All the same initially it is not known what initial and finite dates should be in this table.
[/spoiler]

2

Re: Question on a SQL query

In my opinion, in the second table field SKU is missed... Without it not so grows together.

3

Re: Question on a SQL query

[spoiler the Request to remove from monitors of cats and children, pregnant both feeding women, and other mentally unstable persons]

DECLARE @ TABLE
(KodMeroprijatija varchar (3);
The legal person varchar (5);
 integer;
 varchar (8);
 date;
 date;
SKU varchar (4))
;
INSERT INTO
@
VALUES
(' 001 ', ' 00111 ', 0, NULL, ' 20180102 ', ' 20180103 ', ' SKU1 ');
(' 002 ', ' 00111 ', 1, ' 01_001 ', ' 20180103 ', ' 20180104 ', ' SKU2 ');
(' 003 ', ' 00111 ', 2, ' 02_002 ', ' 20180104 ', ' 20180105 ', ' SKU3 ');
(' 004 ', ' 00111 ', 3, ' 00004 ', ' 20180105 ', ' 20180106 ', ' SKU4 ');
(' 004 ', ' 00111 ', 3, ' 00004 ', ' 20180105 ', ' 20180106 ', ' SKU1 ')
;
DECLARE @ TABLE
(KodTT varchar (5);
The legal person varchar (5);
 varchar (6);
 varchar (6))
;
INSERT INTO
@
VALUES
(' 00001 ', ' 00111 ', ' 01_001 ', ' 02_001 ');
(' 00002 ', ' 00111 ', ' 01_001 ', ' 02_002 ');
(' 00003 ', ' 00111 ', ' 01_002 ', ' 02_003 ');
(' 00004 ', ' 00111 ', ' 01_002 ', ' 02_002 ')
;
WITH
t AS (
SELECT
mm. [];
tt. [Legal person];
tt. [];
mm. [SKU];
[Date] = mm. [];
mm. []
FROM
@ mm
INNER JOIN @ tt ON (
1 = CASE
WHEN mm. [] = 0 AND tt. [Legal person] = mm. [Legal person] THEN 1
WHEN mm. [] = 1 AND tt. [Legal person] = mm. [Legal person] AND tt. [] = mm. [] THEN 1
WHEN mm. [] = 2 AND tt. [Legal person] = mm. [Legal person] AND tt. [] = mm. [] THEN 1
WHEN mm. [] = 3 AND tt. [Legal person] = mm. [Legal person] AND tt. [] = mm. [] THEN 1
END)
UNION ALL
SELECT
t. [];
t. [Legal person];
t. [];
t. [SKU];
[Date] = DATEADD (DAY, 1, t. [Date]);
t. []
FROM
t
WHERE
t. [Date] <t. [KonetsMeroprijatija]
)
SELECT
[];
[Legal person];
[];
[SKU];
[Date]
FROM
t
ORDER BY
1, 2, 3, 4, 5
OPTION (
MAXRECURSION 0)

[/spoiler]

4

Re: Question on a SQL query

Akina wrote:

In my opinion, in the second table field SKU is missed... Without it not so grows together.

The second table displays the list of a TT and has no communication with SKU. The table displays which TT what legal body concerns in group for first sign and in what group for second sign, and. Then by means of a field "the communication Type" and "the TT Code" from the first table are pulled out the TT codes. A field "TT Code" from the first table, it not only the TT code, but also the Codes of fields "PervyjPriznakObedinenija" "VtorojPriznakObedinenija".

5

Re: Question on a SQL query

Ruslan Damirovich , I a condition tt. [Legal person] = mm. [Legal person] in section ON carried out for limits CASE...

6

Re: Question on a SQL query

Akina wrote:

Ruslan Damirovich , I a condition tt. [Legal person] = mm. [Legal person] in section ON carried out for limits CASE...

When we will optimize - undoubtedly.
But generally and in null information about indexes - near birds.

7

Re: Question on a SQL query

Ruslan Damirovich;
Thanks, I assort!

8

Re: Question on a SQL query

ferzmikk wrote:

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.
Why so?

It to understand still

9

Re: Question on a SQL query

Damirovich wrote:

is passed...
When we will optimize - undoubtedly.
But generally and in null information about indexes - near birds.

And where to carry out for limits CASE? In WHERE to jostle?

10

Re: Question on a SQL query

Ruslan Damirovich;
[spoiler]

...
WITH
t AS (
SELECT
mm. [];
tt. [Legal person];
tt. [];
mm. [SKU];
>>> [Date] <<<= mm. [];
mm. []
FROM
...
UNION ALL
SELECT
t. [];
t. [Legal person];
t. [];
t. [SKU];
>>> [Date] <<<= DATEADD (DAY, 1, t.>>> [Date] <<<);
t. []
FROM
t
WHERE
t.>>> [date] <<<<t. [KonetsMeroprijatija]
)
...

[/spoiler]
[Date] to UNION ALL turns out is appropriated once. The cycle further begins: [Date] from function DATEADD and WHERE undertakes from last [Date], and new [Date] after UNION ALL SELECT further is appropriated. Similarly on the following iteration. Truly I understand?

11

Re: Question on a SQL query

Yes, normal such CTE with hierarchy

12

Re: Question on a SQL query

ferzmikk wrote:

it is passed...
It to understand still

[@].

Or to apply  that is better