Select record value based on date range

Question

I have the following two tables:

ITEM AREA YEAR  MONTH   DAY QTY OL  EXQTY   EXOL    CREATE DATE UPDATE
------------------------------------------------------------------------    
ABC789  XYZ1234 2012    6   22  0   0   0   0   2012/07/04  2012/09/03
ABC789  XYZ1234 2012    6   23  0   0   0   0   2012/07/04  2012/09/03
ABC789  XYZ1234 2012    6   24  0   0   0   0   2012/07/04  2012/09/03
ABC789  XYZ1234 2012    6   25  0   0   0   0   2012/07/04  2012/09/03
ABC789  XYZ1234 2012    6   26  0   0   0   0   2012/07/04  2012/09/03
ABC789  XYZ1234 2012    6   27  0   0   0   0   2012/07/04  2012/09/03

and

STDATE      EDDATE       FYYEAR  FYMONTH
---------------------------------------
2012/04/23  2012/05/27   2012      11
2012/05/28  2012/06/24   2012      12
2012/06/25  2012/07/22   2013       1
2012/07/23  2012/08/26   2013       2

I need to create a new view with all the data from the first table as well as the FYYEAR and FYMONTH from the second table. The criteria is that if the sale date in the first table (YEAR MONTH DAY) is between the STDATE and EDDATE in the second table, then the correct FYYEAR and FYMONTH should be selected.

podiluska · Accepted Answer · 2012-09-25 09:35:06Z

2

select table1.*, fyyear, fymonth
from table1 
    inner join table2 
    on convert(date,convert(varchar(4),table1.year)+'-'+convert(varchar(4),table1.month)+'-'+convert(varchar(4),table1.day),120)
    between table2.stdate and table2.eddate

answered Sep 25, 2012 at 9:35

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joe G Joseph · Accepted Answer · 2012-09-25 09:42:12Z

0

try this:

select t1.*,
       FYYEAR ,
       FYMONTH 
from   tbl_1 t1
join   tbl_2 t2
on     cast([YEAR] as char(4))+'-'+
       cast([MONTH] as varchar(2))+'-'+
       cast([DAY] as varchar(2)) 
       between STDATE and EDDATE

answered Sep 25, 2012 at 9:42

Joe G Joseph

24.2k5 gold badges58 silver badges58 bronze badges

Collectives™ on Stack Overflow

Select record value based on date range

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related