AGeek AGeek - 24 days ago 9
SQL Question

SQL Query for range of dates

Lets frame the question again---

table1{date, bID, sName, fID}
{11/05,B1,A1,P1}
{12/05,B2,A2,P2}
{13/05,B1,A3,P1}
{15/05,B3,A4,P1}
{16/05,B1,A5,P2}
{19/05,B1,A6,P2}


This is the table and the data stored in the table is also specified...

Now the query that i want is that:-

Depending of fId (lets say, P1 is selected) it should display the data from table say from 11/05-17/05 (no date should be missed). The data retrieved is as follows:-

11/05,B1,A1
12/05,--,--
13/05,B1,A3
14/05,--,--
15/05,B3,A4
16/05,--,--
17/05,--,--


The data retrieved for a particular fID(say P1) is displayed.. Explaning the result...

1) it displayed all data from 11/05-17/05 where fId is P1, if there is no date in the database, then also it should display null value for that date (i.e.14/05 date was not there in database, but still it displayed with a null value)..

2) if fId for that particular date is not P1, then also it store a null value in result set..

Atlast the data is retrieved in result set,, and processed further..

So i want to write the query for this problemm,, is it possible..

Answer Source

No code here, just my thoughts.

You need to create a temporary table with dates ranging from your begin date to an end date, inclusive. And then left join table1 with that temporary table on date column plus add where fID = ?.