SQL Query Assistance Required

  • troy1w1w

    Valued Member

    Points: 62

    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

  • Jonathan AC Roberts

    SSCoach

    Points: 17010

    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: 98461

    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.

  • Jonathan AC Roberts

    SSCoach

    Points: 17010

    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

    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 5 (of 5 total)

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