1

I have a table with following structure

 transaction_id    user_id     date_column   
   1                  1        01-08-2011     
   2                  2        01-08-2011    
   3                  1        02-08-2011   
   4                  1        03-08-2011

There can be at-max only one entry for each user on each date.

How can get all rows where user_id is not present for specific date range. So for above table with user_id= 2 and date range 01-08-2011 to 03-08-2011, I want 

result
02-08-2011
03-08-2011

Right now, I am using for loop to loop over all dates in given date range. This is working fine with small date range, but I think it will become resource heavy for large one.

Pradeep
  • 1,254
  • 1
  • 22
  • 41
  • Create a table containing the dates. Select from this table, left join your data table, null columns are the dates with no data. – Dan Grossman Aug 23 '11 at 09:05
  • @Dan Thanks for your help, but here date-range will be variable. It may happen that, not a single user_id present on that particular date, so selecting all date in this table is also not an option. – Pradeep Aug 23 '11 at 09:35
  • @Dan Are you suggesting that I should create table dynamically with all the Date before joining? – Pradeep Aug 23 '11 at 09:41
  • Yes, that's what I suggested. You write a loop from the earliest date you care about to the latest date you care about (something like 100 years in the future when nobody will use your app anymore) and insert each into a table of dates. It's a few lines of code, will only take you a minute to write, then you can join that table whenever you need a row for every date in any query. – Dan Grossman Aug 24 '11 at 08:40

1 Answers1

2

As suggested in a comment, create a table with the dates of interest (I'll call it datesofinterest). Every date from your date range needs to be put into this table. 

datesofinterest table
--------------
date
--------------
01-08-2011
02-08-2011
03-08-2011

Then the datesofinterest table needs to be joined with all the userids -- this is the set of all possible combinations of dates-of-interest and userids.

Now you have to remove all those dates-of-interest/userids that are currently in your original table to get your final answer.

In relational algebra, it'd be something like:

(datesofinterest[date] x transaction[user_id]) - (transaction[date_column, user_id])

This page may help with translating '-' to SQL. Generating dates to populate the datesofinterest table can be done in SQL, manually, or with a helper program (perl's DateTime)

Community
  • 1
  • 1
Matt Fenwick
  • 48,199
  • 22
  • 128
  • 192
  • Thanks. Mostly I will go with this, but as I am using ms-access in C#, I can not use solution provided using T-SQL. Is there any way to create `datesofinterest` other than a loop? – Pradeep Aug 23 '11 at 11:50
  • There will definitely be a translation from relational algebra to any tool. Creating the datesofinterest table can be done either using C# or by manually accessing the database, depending on the specific needs of your solution (i.e. whether the date range changes every time you need to run the query). – Matt Fenwick Aug 23 '11 at 12:19
  • Yes JOIN can be performed but I am talking about generating datesofinterest table. – Pradeep Aug 23 '11 at 13:55
  • I will move on with your suggested logic, use `JOIN` for getting relations. And for generating `datesofinterest` I will add row with empty user_id and date if that date is not present. If you have any better Idea for later (generating `dateofinterset` let me know, I will try to add that changes afterward) – Pradeep Aug 24 '11 at 00:27