CODEWITHSUNDEEP
sqlsql-server-2008daterange
Flashgordan
Flashgordan

Reputation: 63

Select record value based on date range

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.

Upvotes: 3

Views: 435

Answers (2)

Joe G Joseph
Joe G Joseph

Reputation: 24046

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

Upvotes: 0

podiluska
podiluska

Reputation: 51494

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

Upvotes: 2

Related Questions