3

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.

  • If the count from the first query is 0, run the second query? You don't need an exists, as you have already counted the rows that match. – ypercubeᵀᴹ Jan 19 '16 at 14:20
  • @ypercubeᵀᴹ - I need to check each date. If the date does not exist in first table then check the second table for the date. – RashidInman Jan 19 '16 at 14:22
  • What do you mean with "each" date? The starting and ending date? Or all the 335 dates between the two dates? Give us some sample data of the 2 tables and what you want as result. – ypercubeᵀᴹ Jan 19 '16 at 14:24
  • @ypercubeᵀᴹ - all dates between the two dates – RashidInman Jan 19 '16 at 14:24
  • 1
    @ypercubeᵀᴹ - thank you for that link. I had NO idea that was bad practice. – RashidInman Jan 19 '16 at 14:27
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.