SQL Query Assistance Required

Question

SQL Query Assistance Required

troy1w1w

SSC Enthusiast

Points: 114
More actions
July 18, 2019 at 1:32 am

#3663978

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 1

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

Thom A SSC Guru Points: 99130 More actions · Answer 2

Jonathan AC Roberts wrote:

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

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 3

Thom A wrote:

the dynamic query uses the function to_char, which doesn't exist in SQL Server.

Looks like it might be some Oracle SQL.

delizat4g Old Hand Points: 369 More actions · Answer 4

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