Sql-server – Check 1 Table If No Data Check Second Table

sql-server-2008t-sql

I am passing into a stored procedure two variables @begindate & @enddate. I need to check firsttabletocheck and get a count of where csdate is between @begindate and @enddate.

I then need to check and see if any of the dates between @begindate & @enddate Not Exists in firsttabletocheck I need to query secondtabletocheck to get a count from that.

I can write my code to do 1 table, but not quiet sure how to write it to do the other. For example this is my current syntax that only checks 1 table, how can I add in the check for the second table?

Declare @d1 datetime, @d2 datetime, @numreturned int
Set @d1 = '01/01/2015'
Set @d2 = '01/12/2015'
Set @numreturned = 0

Set @numreturned = (Select COUNT(*) 
                    FROM firsttabletocheck 
                    where csdate BETWEEN @begindate and @enddate)

How can I add my not exist here to check if the date does not exist in the above table then check this table?

For example let's say that all dates in the date range exist in firsttabletocheck EXCEPT 01/11/2015 then I need to query the second table to check if 01/11/2015 exists in that table.

Best Answer

You could try something like this query:

Declare @d1 date, @d2 date, @numreturned int
Set @begindate = '01/01/2015'
Set @enddate = '01/12/2015'
Set @numreturned = 0

SELECT @numreturned = COUNT(DISTINCT CAST(csdate as date)) 
FROM firsttabletocheck 
WHERE csdate >= @begindate and csdate < @enddate;

IF DATEDIFF(day, @begindate, @enddate) > @numreturned
BEGIN 
    SELECT DISTINCT CAST(csdate as date))
    FROM secondtabletocheck 
    WHERE csdate > @begindate <= @enddate
        AND CAST(csdate as date) NOT IN (
            SELECT DISTINCT csdate = CAST(csdate as date)
            FROM firsttabletocheck 
            WHERE csdate >= @begindate and csdate < @enddate
        )
END

I am using CAST(csdate as date) everywhere because you datatype seems to be datetime. If you are not using the time part, you should consider changing the type to date. This will very likely leads to table scan with the second query.

I added a distinct because I don't know whether you have duplicates or several rows on the same day. If there is only 1 row per day, this is not needed.

I replaced the between by >= and <. (see @ypercubeᵀᴹ comments)

I am not sure what sort of output is needed precisely. You will have to adapt this query to your needs or add sample data and sample output. Right now it only show csdate missing from firsttabletocheck but present in secondtabletocheck.

Related Question