datedifference from two different tables
-
Hi, I want to find the date difference of date1 and date2. But these two colums are present in different tables. I have used joins but not getting proper results because the no of rows i am getting is the cartesian product of the two tables. I am includeing two tables and the result i have got after using joins. Please tell the right way to do this. Tran_type Partno Description Transactiondate Quantity Rate Balqty --------- ------ ----------- ------------------- ------------ --------- ------- R ABC NULL 2005-01-01 00:00:00 15.0 20.0 15.0 R ABC NULL 2005-04-02 00:00:00 15.0 30.0 15.0 R ABC NULL 2005-09-01 00:00:00 50.0 30.0 50.0 (3 row(s) affected) Tran_type Partno Description Transactiondate Quantity Rate Balqty ---------- ------ ----------- ------------------- ----------- --------- -------- S ABC NULL 2005-06-03 00:00:00 5.0 25.0 NULL S ABC NULL 2005-07-01 00:00:00 5.0 10.0 NULL S ABC NULL 2006-06-01 00:00:00 15.0 50.0 NULL (3 row(s) affected) tran_type transactiondate transactiondate tran_type Date diff --------- --------------------------- --------------------------- ---------- ----------- R 2005-01-01 00:00:00 2005-06-03 00:00:00 S 153 R 2005-04-02 00:00:00 2005-06-03 00:00:00 S 62 R 2005-09-01 00:00:00 2005-06-03 00:00:00 S -90 R 2005-01-01 00:00:00 2005-07-01 00:00:00 S 181 R 2005-04-02 00:00:00 2005-07-01 00:00:00 S 90 R 2005-09-01 00:00:00 2005-07-01 00:00:00 S -62 R 2005-01-01 00:00:00 2006-06-01 00:00:00 S 516 R 2005-04-02 00:00:00 2006-06-01 00:00:00 S 425 R 2005-09-01 00:00:00 2006-06-01 00:00:00 S 273 (9 row(s) affected)
Chaitra N
-
Hi, I want to find the date difference of date1 and date2. But these two colums are present in different tables. I have used joins but not getting proper results because the no of rows i am getting is the cartesian product of the two tables. I am includeing two tables and the result i have got after using joins. Please tell the right way to do this. Tran_type Partno Description Transactiondate Quantity Rate Balqty --------- ------ ----------- ------------------- ------------ --------- ------- R ABC NULL 2005-01-01 00:00:00 15.0 20.0 15.0 R ABC NULL 2005-04-02 00:00:00 15.0 30.0 15.0 R ABC NULL 2005-09-01 00:00:00 50.0 30.0 50.0 (3 row(s) affected) Tran_type Partno Description Transactiondate Quantity Rate Balqty ---------- ------ ----------- ------------------- ----------- --------- -------- S ABC NULL 2005-06-03 00:00:00 5.0 25.0 NULL S ABC NULL 2005-07-01 00:00:00 5.0 10.0 NULL S ABC NULL 2006-06-01 00:00:00 15.0 50.0 NULL (3 row(s) affected) tran_type transactiondate transactiondate tran_type Date diff --------- --------------------------- --------------------------- ---------- ----------- R 2005-01-01 00:00:00 2005-06-03 00:00:00 S 153 R 2005-04-02 00:00:00 2005-06-03 00:00:00 S 62 R 2005-09-01 00:00:00 2005-06-03 00:00:00 S -90 R 2005-01-01 00:00:00 2005-07-01 00:00:00 S 181 R 2005-04-02 00:00:00 2005-07-01 00:00:00 S 90 R 2005-09-01 00:00:00 2005-07-01 00:00:00 S -62 R 2005-01-01 00:00:00 2006-06-01 00:00:00 S 516 R 2005-04-02 00:00:00 2006-06-01 00:00:00 S 425 R 2005-09-01 00:00:00 2006-06-01 00:00:00 S 273 (9 row(s) affected)
Chaitra N
-
select 'Difference in Date'=datediff(dd,r.rdate,i.idate) from receive r,issue i where r.partno=i.partno
Chaitra N