Post reply

What sites didn't report on what days?

  • February 16, 2012 at 2:23 pm

    #264746

    Hello. Given a list of SiteId/date combinations and a date tally table I'm trying to figure out what dates in 2012 that sites are missing data for.

    CREATE TABLE #AllDates(SiteDate smalldatetime)

    INSERT #AllDates

    SELECT '1/1/12' union

    SELECT '1/2/12' union

    SELECT '1/3/12' union

    SELECT '1/4/12'

    etc...

    CREATE TABLE #SitesAndDates(SiteId int, SiteDate smalldatetime)

    INSERT #SitesAndDates

    SELECT 1, '1/1/12' UNION

    SELECT 1, '1/3/12' UNION

    SELECT 1, '1/4/12' UNION

    SELECT 2, '1/2/12' UNION

    SELECT 2, '1/3/12' UNION

    SELECT 2, '1/4/12'

    etc...

    The result here would be that Site 1 didn't report 1/2 and Site 2 missing 1/1. Obviously can't just left join #Alldates to #SitesAndDates on SiteDate. I did it a different way using row_number() but felt like I was cheating 😀

    Ken

  • February 16, 2012 at 2:27 pm

    #1448162

    you need to be able to also join against a master list of all sites.

    when you cross join dates and sites, you get all possible values...which you can then join to teh list of data and find the missing items.

    then it's a lit easier:

    SELECT *

    FROM #AllDates

    CROSS JOIN #AllSites

    LEFT OUTER JOIN #SitesAndDates

    ON #AllDates.AllDates = #SitesAndDates.SiteDate

    AND #AllSites.SiteId =#SitesAndDates.SiteId

    WHERE #SitesAndDates.SiteID IS NULL

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 16, 2012 at 3:03 pm

    #1448185

    Thanks Lowell. That Cross Join is helpful.

    Ken

Viewing 3 posts - 1 through 2 (of 2 total)

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