CODEWITHSUNDEEP
sql-serverjoin
Eclipse
Eclipse

Reputation: 309

mssql join criteria with the same table

Just need a little help. so I have this calendar table here sqlfiddle.com/#!3/5d8a9 There are 2 flags (M, W) indicating Month or Week. I need to do a join with TBL2 (below) that has a weekly date field. so I will join TBL2 on the weekstartdate with Cal table on start date (with W flag) but I need to return the startdate with the 'M' flag if the weekstartdate falls between start and end date. Does it make sense?

TBL2:

prod_id        weeknum      wkstartdate     postingDate     qty
----------------------------------------------------------------
1043890         5           2015-01-25      2016-01-18      3
1043890         6           2015-02-01      2016-01-18      6
1043890         7           2015-02-08      2016-01-18      2
1043890         8           2015-02-15      2016-01-18      0
...
1043890         50          2015-12-06      2016-01-18      1
1043890         51          2015-12-13      2016-01-18      2
1043890         52          2015-12-20      2016-01-18      7


Desired Result:
==================

prod_id       weeknum      wkstartdate     postingDate     qty   Period
----------------------------------------------------------------------------
1043890         5           2015-01-25      2016-01-18      3    2015-01-25
1043890         6           2015-02-01      2016-01-18      6    2015-01-25
1043890         7           2015-02-08      2016-01-18      2    2015-01-25
1043890         8           2015-02-15      2016-01-18      0    2015-01-25
1043890         10          2015-03-01      2016-01-18      0    2015-03-01
...
1043890         50          2015-12-06      2016-01-18      1    2015-11-22
1043890         51          2015-12-13      2016-01-18      2    2015-11-22
1043890         52          2015-12-20      2016-01-18      7    2015-11-22

If it makes sense, I'm trying to check if weekstartdate falls between a range of dates (startdate-enddate) with a flag of M, then bring the startdate and either update that record in the temp table or create a temp table with the new column added to each record.

Thanks.

Upvotes: 1

Views: 37

Answers (1)

cherry
cherry

Reputation: 127

what I understood is you trying to join table2 on weekstarttable with cal table on startdate and you want to modify flag if week start date falls between start date and end date. if got it correct, try the following query.

select t2.prod_id,t2.weeknum,c.startdate
       ,case when t2.wkstartdate between c.startdate and c.enddate
        then 'M' else null end modifiedflag, 
        c.flag as originalflag 
from   cal c,table2 t2 
where  c.flag ='W' and t2.wkstartdate = c.startdate
       and t2.wkstartdate between c.startdate and c.enddate;

Upvotes: 1

Related Questions