July 18, 2019 at 1:32 am
Hi.
I am wanting to know if there is a way to make this query a range.
This is the main component:
dd_user_wave_57_syn a, dd_user_log_wave_57 b
I have to change each time I want to refer to a new location, so for example
dd_user_wave_58_syn a, dd_user_log_wave_58 b -- then once done go to the next one
dd_user_wave_59_syn a, dd_user_log_wave_59 b -- and so on
See full SQL below, any advice on how to make it a range of wave numbers would be great instead of 1 by 1.
select a.tls_id, a.processed_user, a.action, a.comments, a.processed_date,
b.RECORD_OPEN_DATE, b.RECORD_CLOSE_DATE
from dd_user_wave_57_syn a, dd_user_log_wave_57 b
where
a.trs = b.TRS
and a.processed_user = b.PROCESSED_USER
and action not like '%ss'
and a.processed_user in (select USERNAME from dwd_user where dwd_user.GROUPS = 'DAN')
and to_char(a.PROCESSED_DATE, 'MMYYYY') = '042019' ----- Change the month as per request e.g. 062018
order by processed_date desc
July 18, 2019 at 9:49 am
You can get the table names from INFORMATION_SCHEMA.TABLES
DECLARE @myCursor cursor;
SET @myCursor = cursor
FOR SELECT t1.TABLE_NAME,
t2.TABLE_NAME
FROM information_schema.tables t1
INNER JOIN information_schema.tables t2
ON t2.TABLE_NAME LIKE 'dd_user_wave_%_syn'
AND REPLACE(REPLACE(t2.TABLE_NAME, 'dd_user_wave_', ''), '_syn', '') = REPLACE(t1.TABLE_NAME, 'dd_user_log_wave_', '')
WHERE t1.TABLE_NAME LIKE 'dd_user_wave_%_syn';
DECLARE @t1TableName sysname, @t2TableName sysname;
DECLARE @slq nvarchar(MAX);
OPEN @myCursor;
FETCH NEXT FROM @myCursor INTO @t1TableName, @t2TableName;
WHILE @@FETCH_STATUS = 0 BEGIN
SET @sql = 'select a.tls_id, a.processed_user, a.action, a.comments, a.processed_date,
b.RECORD_OPEN_DATE, b.RECORD_CLOSE_DATE
from ' + @t1TableName + ' a, ' + @t2TableName + 'b
where
a.trs = b.TRS
and a.processed_user = b.PROCESSED_USER
and action not like ''%ss''
and a.processed_user in (select USERNAME from dwd_user where dwd_user.GROUPS = ''DAN'')
and to_char(a.PROCESSED_DATE, ''MMYYYY'') = ''042019'' ----- Change the month as per request e.g. 062018
order by processed_date desc'
PRINT @sql
--EXEC(@sql) -- uncomment EXEC
END
CLOSE @myCursor
DEALLOCATE @myCursor
July 18, 2019 at 11:04 am
You can get the table names from INFORMATION_SCHEMA.TABLE
Seems this could be even better if you get rid of the Cursor, use a proper join, quote the objects and use sp_executesql instead (so that the query can be parametrised for the value of PROCESSED_DATE). Also, the dynamic query uses the function to_char, which doesn't exist in SQL Server. I've changed that part of the WHERE to use proper date logic and parametrised it.
I believe this does what you're after (can't test, due to lack of objects, and sample data):
DECLARE @DateFrom date, @DateTo date;
SET @DateFrom = '20190401'; --Change to relevant start date
SET @DateTo = DATEADD(MONTH,1,@DateTo); --Note this assume the prior month will be the first
DECLARE @SQL nvarchar(MAX);
SET @SQL = STUFF((SELECT NCHAR(13) + NCHAR(10) +
N'SELECT a.tls_id,' + NCHAR(13) + NCHAR(10) +
N' a.processed_user,' + NCHAR(13) + NCHAR(10) +
N' a.action,' + NCHAR(13) + NCHAR(10) +
N' a.comments,' + NCHAR(13) + NCHAR(10) +
N' a.processed_date,' + NCHAR(13) + NCHAR(10) +
N' b.RECORD_OPEN_DATE,' + NCHAR(13) + NCHAR(10) +
N' b.RECORD_CLOSE_DATE' + NCHAR(13) + NCHAR(10) +
N'FROM ' + QUOTENAME(t1.TABLE_SCHEMA) + N'.' + QUOTENAME(t1.TABLE_NAME) + N' a' + NCHAR(13) + NCHAR(10) +
N' JOIN '+ QUOTENAME(t2.TABLE_SCHEMA) + N'.' + QUOTENAME(t2.TABLE_NAME) + N' b ON a.TRS = b.TRS' + NCHAR(13) + NCHAR(10) +
N'WHERE a.processed_user = b.PROCESSED_USER' + NCHAR(13) + NCHAR(10) +
N' AND action NOT LIKE ''%ss''' + NCHAR(13) + NCHAR(10) +
N' AND a.processed_user IN (SELECT du.USERNAME FROM dwd_user du WHERE du.GROUPS = ''DAN'')' + NCHAR(13) + NCHAR(10) + --This might be better as an EXISTS
N' AND a.PROCESSED_DATE >= @DateFrom' + NCHAR(13) + NCHAR(10) +
N' AND a.PROCESSED_DATE < @DateTo' + NCHAR(13) + NCHAR(10) +
N'ORDER BY a.PROCESSED_DATE DESC;'
FROM INFORMATION_SCHEMA.TABLES t1
INNER JOIN INFORMATION_SCHEMA.TABLES t2 ON t2.TABLE_NAME LIKE N'dd_user_wave_%_syn'
AND REPLACE(REPLACE(t2.TABLE_NAME, N'dd_user_wave_', N''), N'_syn', N'') = REPLACE(t1.TABLE_NAME, N'dd_user_log_wave_', '')
WHERE t1.TABLE_NAME LIKE N'dd_user_wave_%_syn'
FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,2,N'');
PRINT @SQL;
EXEC sp_executesql @SQL, N'@DateFrom date, @DateTo date', @DateFrom, @DateTo;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 18, 2019 at 3:19 pm
Maybe something like datepart
and to_char(a.PROCESSED_DATE, 'MMYYYY') = '042019' ----- Change the month as per request e.g. 062018
AND (DATEPART(year, a.PROCESSED_DATE) = @year and DATEPART(month, a.PROCESSED_DATE) = @month)
Code-Blooded
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply