1

Topic: Cyclic request from several identical bases

Hello, prompt how to implement in following request a cycle.
Every day new basis Statistics_20170901, Statistics_20170902, Statistics_20170903 etc. is formed
It is necessary to update request where the data from new basis every day is added.
select
U.Name as [Gos. Number]
,S01.TotalKm as [Run (on September, 1st)]
,S02.TotalKm as [Run (on September, 2nd)]
,S03.TotalKm as [Run (on September, 3rd)]
,S04.TotalKm as [Run (on September, 4th)]
from Statistics_20170901.dbo. Units as U
LEFT JOIN Statistics_20170901.dbo. UnitStatistics as S01 ON (U.ID=S01.UnitId)
INNER JOIN Statistics_20170902.dbo. UnitStatistics as S02 ON (U.ID=S02.UnitId)
INNER JOIN Statistics_20170903.dbo. UnitStatistics as S03 ON (U.ID=S03.UnitId)
INNER JOIN Statistics_20170904.dbo. UnitStatistics as S04 ON (U.ID=S04.UnitId)
Whether it is possible to implement request with a following condition? To try  bases with a title from Statistics_20170901 to Statistics_20170930 and if the necessary basis it is not created yet to pass it in request without an error.

2

Re: Cyclic request from several identical bases

RC88;
Build dynamic request and all.

3

Re: Cyclic request from several identical bases

Vorontsov wrote:

RC88;
Build dynamic request and all.

Pavel Vorontsov;
And it is possible, please, more in detail. With an example it is desirable.

4

Re: Cyclic request from several identical bases

RC88 wrote:

it is passed...
Pavel Vorontsov;
And it is possible, please, more in detail. With an example it is desirable.

Example:
exec (' dynamic request and all ');

5

Re: Cyclic request from several identical bases

declare @d1 datetime, @d2 datetime, @str as nvarchar (4000);
select @d1 = ' 20170901 ', @d2 = ' 20170930 ';
with ns as (select n = number from dbo. Numbers where number <datediff (day, @d1, @d2) + 1)
, ds as (select *, dt = convert (nvarchar (32), dateadd (day, n, @d1), 112) from ns)
, bs as (select *
, [table] = N'Statistics _ ' + dt +N '.dbo. UnitStatistics'
, tAlias = N'S' + cast (n as nvarchar (16))
, cAlias = N ' [Run (' + dt +N ')]'
from ds
)
, vs as (select * from bs where object_id ([table]) is not null)
select @str = ' select U.Name as [Gos. Number]'
+ (select N ', ' + cAlias + N ' = ' + tAlias + N '.TotalKm ' from vs order by n for xml path ("))
+ N ' from Statistics _ ' + convert (nvarchar (32), @d1, 112) + N '.dbo. Units as U'
+ (select N ' left outer join ' + [table] + N ' as ' + tAlias + N ' on U.ID = ' + tAlias + N '.UnitId ' from vs order by n for xml path ("))
from vs;
select @str;
exec (@str);

6

Re: Cyclic request from several identical bases

aleks222;
Thanks.
Truth created basis Numbers in it table Numbers and 30 values in summary produces 1 line with NULL