pleae see the DDL and logic it require for this query

please assist me here,

create table Student

CREATE TABLE [dbo].[StudentNew] (

[Sno] VARCHAR(3) NOT NULL,

[Sname] VARCHAR(30) NOT NULL,

[LastGameDate] Date NULL,

[DateofBirth] Date NULL,

[Points] money NULL,

CONSTRAINT [PK_StudentNew] PRIMARY KEY CLUSTERED ([Sno],[Sname]) )

CREATE TABLE [dbo].[StudentOffers] (

[Sno] VARCHAR(3) NOT NULL,

[Sname] VARCHAR(30) NULL,

[DoB] Date NULL,

[City] VARCHAR(30) NULL,

[Fee] money NULL,

[State] VARCHAR(20) NULL,

)

Insert into StudentOffers values (1,'AAA','1/1/1987','NewYork',0,'NY')

Insert into StudentOffers values (2,'BBB','2/22/1986','Norwlak',5600,'CT')

Insert into StudentOffers values (3,'CCC','3/13/1989','Bridgeport',4400,'CT')

Insert into StudentOffers values (4,'DDD','4/24/1984','Stamford',6000,'CT')

Insert into StudentOffers values (5,'EEE','5/15/1987','Norwich',4000,'CT')

Insert into StudentOffers values (6,'FFF','6/5/1980','Secausus',3500,'NJ')

Insert into StudentOffers values (7,'GGG','7/22/1980','Newark',23000,'NJ')

Insert into StudentOffers values (8,'HHH','6/15/1990','Secausus',NULL,'NJ')

Insert into StudentOffers values (9,'III','7/17/1900','Newark',NULL,'NJ')

---Target Table All changes our Query Insert/Update will affect this table only

Insert into StudentNEW values (1,'AAA',null,'1/1/1987',null)

Insert into StudentNew values (2,'BBB',null,'2/22/1986',null)

Insert into StudentNew values (3,'CCC','4/19/2013','3/13/1989',100)

Insert into StudentNew values (4,'DDD','3/22/2013','4/24/1984',300)

Insert into StudentNew values (5,'EEE','2/23/2013','5/15/1987',250)

For Matched Records in StudentNew Table (based on sno,sname)

check LastGameDate column has no value

if Fee from studentOffers is > 0 then update points column with fee column, and update lastgamedate column to current week friday date

example: 1, has 0 value in fee so no action but sno=2 has value in fee so points = fee and then lastGamedate = current weekk friday

check LastGameDate column has value + 7 weeks > today then

if points column < Fee from studentOffers then update points column with fee column

example: sno with 3,4 has GameLastDate '4/19/2013', '3/22/2013' + 49 days (7 weeks) which is > today, check points < fee column so update points = fee

check LastGameDate column has value + 7 weeks < today then

No Action required.

example: sno with 5 has GameLastDate '2/23/2013' + 49 days (7 weeks) which is 2013-04-06 < today, so no action on sno=5

For NON Matched Records in StudentNew Table (based on sno,sname)

---Below Records Not exist in target Table, also null in fee Insert will happen with nulls in Lastgamedate, points columns

sno 8, 9 has no fee value (null) then insert total_summary_nfs studentnew table with null vlaues in lastdatecolumn & points

----Below Records not exist in target table,Fee > 0 so insert will happen with values

sno 6, 7 not exist in studentnew table also has fee value so insert with values LastGameDate = Current week friday

expected Results after the insert/update query runs in StudentNew Table

---Update

Insert into StudentNEW values (1,'AAA',null,'1/1/1987',null)

Insert into StudentNew values (2,'BBB','5/10/2013','2/22/1986',5600)

Insert into StudentNew values (3,'CCC','4/19/2013','3/13/1989',4400)

Insert into StudentNew values (4,'DDD','3/22/2013','4/24/1984',6000)

Insert into StudentNew values (5,'EEE','2/23/2013','5/15/1987',250)

---Insert

Insert into StudentOffers values (6,'FFF','5/10/2013','6/5/1980',3500)

Insert into StudentOffers values (7,'GGG','5/10/2013','7/22/1980',23000)

Insert into StudentOffers values (8,'HHH',null,'6/15/1990',NULL)

Insert into StudentOffers values (9,'III',null,'7/17/1900',NULL)

Thanks in advance

asittti