problem in filtering rows from two tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

problem in filtering rows from two tables

Author

Topic

san79
Starting Member

42 Posts

Posted - 2008-12-16 : 07:23:55

dear all,
sorry if this is very naive, but i dont know how to do it.
i am having two tables registration and regisdates
the fields in the registration tables are
rno number PK
rdt datetime
sdt datetime
edt datetime
custname varchar

the fields in regisdates are
rno number fk to registration.rno
regisdate datetime

basically the customer register in the registration table, he is assigned to use the facility between the sdt and edt
then he have to choose certain dates between the sdt and edt to avail and these dates are stored in the regisdates table.
now the problem is i wish to take date wise registered customer list.
i wish the customers in regisdates table should be appear only on the registered date in the report, currently i am using between keyword to filter records in the registration table sdt and edt fields. the customer who is availed choose date option is also displayed with other customers. i want to show him only on the particular registered dates.
any suggestions is greatly appreciated
thanks and regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 07:44:35

[code]
SELECT r.*
FROM registration r
LEFT JOIN regisdates rd
ON rd.rno=r.rno
AND rd.regisdate BETWEEN sdt AND DATEADD(dd,1,edt)
WHERE (rd.rno IS NULL
OR rd.regisdate = @YourDate)
AND r.rdt=@YourDate
[/code]
@YourDate is date passed

san79
Starting Member

42 Posts

Posted - 2008-12-16 : 07:57:31

sir,
will try and get back
thankyou very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 08:02:15

yup...no probs
you're welcome

san79
Starting Member

42 Posts

Posted - 2008-12-17 : 22:59:38

sir,
i have sorted out the issue partially using your query and partially using rowdatabound in gridview
but i feel that the actually i would like to know is there is a single query available to represent the data the data in the table look like this table name is tblregistration
rno rdate sdt edt custname
1 12/17/2008 12/17/2008 12/20/2008 xyz
2 12/18/2008 12/18/2008 1/18/2009 abc

and the tbldates is having values like this
rno sdate
2 12/20/2008
2 12/30/2008
2 12/1/2009
2 1/15/2009

now when i display the customer for date 19/12/2008 then receiptno 1 should only appear because it is assumed that if the receiptno dosenot have value in tbldates then he is considered to be using the facility throughout sdt and edt.
now when i display the customer for date 20/12/2008 then receiptno 1 and 2 should appear because the he is specified his pref in tbldates table. i think i am little bit clear now. please advice me if i have to change the structure of the tables or what should i do to get it done or if it is possible in single query.sorry for the bit long mail.
thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 23:45:54

[code]SELECT r.*
FROM registration r
LEFT JOIN regisdates rd
ON rd.rno=r.rno
AND rd.sdate BETWEEN r.sdt AND r.edt
WHERE ( rd.sdate = @YourDate
OR @YourDate BETWEEN r.sdt AND r.edt)[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources