Sql-server – Join tables where the table name changes every day

sql serversql-server-2008t-sql

I need some help in figuring out how to join tables to my query. The table names change every day as the date is used in the table name and older tables are dropped as they are no longer needed.

My query looks for "scans" that have data in the database and are in the process of being processed (for a lack of a better word).

My current query is:

USE RetinaCSDatabase
SELECT S.ScanID [Scan ID], S.PolicyName [Scan], DATEADD(hh, +2, S.StartTime) [Scan Start], A.AssetName [Scanner], COUNT(AIPS.AssetIPID) [Hosts], S.TransactionGroup [Transaction Group ID]
FROM Scan S (NOLOCK)
LEFT JOIN Scanner SC (NOLOCK) ON S.ScannerID = SC.ScannerID
LEFT JOIN Asset A (NOLOCK) ON SC.AssetID = A.AssetID
LEFT JOIN Asset_IP_Scan AIPS (NOLOCK) ON S.ScanID = AIPS.ScanID
WHERE S.ProcessStatus < 2
GROUP BY S.ScanID, S.PolicyName, S.StartTime, A.AssetName, S.TransactionGroup
ORDER BY S.StartTime ASC, S.PolicyName ASC, A.AssetName ASC

What I need to join is the table(s) with the data still requiring processing. Those tables start with the name "evt_scan_" and then the date. So, a full table name would be "evt_scan_20171222" for example. The data for a scan performed on the 22nd of December 2017 will have data in the table "evt_scan_20171222" and possibly in the following days table, "evt_scan_20171223". So, I need my query first to check if the table exists and then join it to do a count and also to check if the following days table exists, join it to do another count and add up the 2 counts to show the outstanding records requiring processing.

If the table names were static I would join them with this query:

LEFT JOIN evt_scan_20171222 evt1 ON S.TransactionGroup = evt1.evtTransactionGroup

It would also require a WHERE statement returning only records where evtStatus = 0.

Thanks

Best Answer

Use a synonym.

USE YourDatabaseName;
DECLARE @sql nvarchar(max) = N'CREATE SYNONYM
  dbo.EventScan FOR dbo.evt_scan_'
  + CONVERT(CHAR(8), GETDATE(), 112);
EXEC sys.sp_executesql @sql;

Then your code can always reference dbo.EventScan.

Now, schedule a job to run every night at 12:00:01 AM (or create a DDL trigger on table creation and when the new table name matches the expected pattern, or put code in whatever creates those new tables each day) that does this:

USE YourDatabaseName;
DROP SYNONYM dbo.EventScan;
— repeat code from above

Related Solutions

Sql-server – Execution Plan not matching Stored Proc

Your code says you are doing a LEFT OUTER JOIN, but are you really? Your very first WHERE clause (as well as each that follow) filters rows for inclusion from the outer table:

WHERE     (A.[Operator_Name] LIKE '%'+ @OName +'%')

This turns your LEFT OUTER JOIN into an INNER JOIN, whether you meant it or not. Perhaps you meant to move those filters to the ON clause.

Of course I would probably remove the non-sargeable MONTH() function from the JOIN, since this is going to force a complete scan on the remote table, and write it this way instead - also eliminating the join to your local months table:

DECLARE 
  @OName      VARCHAR(50) = 'John', 
  @Start_Date DATE = NULL,--'20120101', -- use safe date formats 
  @End_Date   DATE = '20121231'; -- use semi-colons

-- deal with NULLs here so the query is simpler:

SELECT @Start_Date = COALESCE(@Start_Date, '20010101'),
       @End_Date   = COALESCE(@End_Date, CURRENT_TIMESTAMP);

;WITH n(n) AS 
(
  -- get the # of months you need instead of relying on your Connector table:
  SELECT TOP (DATEDIFF(MONTH, @Start_Date, @End_Date)+1) Number
    FROM master..spt_values
    WHERE [type] = N'P' AND Number >= 0
    ORDER BY Number
), m(m) AS
(
  -- convert those to months:
  SELECT DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Start_Date), 0))
  FROM n
)
SELECT 
  m.m, 
  MonthName = DATENAME(MONTH, m.m),
  [Widget Count] = COUNT(w.Widget_ID)
FROM m
LEFT OUTER JOIN [Server].[Database].dbo.Widget AS w -- meaningful alias
ON 
  w.Widget_Date >= m.m
  AND w.Widget_Date < DATEADD(MONTH, 1, m.m) -- open-ended date range query
  AND w.Operator_Name LIKE '%' + @OName + '%'
GROUP BY m.m
ORDER BY m.m;

Just ignore the m column in the output (you can't order by it without including it in the output, and it doesn't make sense to order by the name).

Another suggestion: don't use 'single quotes' as alias delimiters. Forms of this are deprecated and it also makes column alias look like string literals. Use [square brackets] instead.

T-sql – Left join on one or the other column without table-scanning

When you have two separate indexes on transact and on transactchild the server can't use them efficiently when searching for (t.rid=blk.transact OR t.rid=blk.transactchild).

The classic way to handle this type of OR search is to split it into two simple searches and then UNION results together.

So, you can try something like this:

SELECT ....
FROM ...
LEFT JOIN table2 blk ON (t.rid=blk.transact)

UNION

SELECT ....
FROM ...
LEFT JOIN table2 blk ON (t.rid=blk.transactchild)

In this case individual simple query should be able to use corresponding index, but the extra UNION step can be quite expensive, especially if it requires a sort.

If your data is such that you know that UNION ALL would produce correct result, then use UNION ALL, which is much more efficient than UNION. UNION eliminates duplicates, which is expensive. UNION ALL simply outputs results of two queries one after another.

If values in blk.transact do not overlap with values in blk.transactchild, then you can use UNION ALL.

Best Answer

Related Solutions

Sql-server – Execution Plan not matching Stored Proc

T-sql – Left join on one or the other column without table-scanning

Related Question