ow to Insert/Upadte data based on exist in mathcin

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

ow to Insert/Upadte data based on exist in mathcin

Author

Topic

dhani
Posting Yak Master

132 Posts

Posted - 2013-05-07 : 15:55:07

Hello There,

please see the ddl, and detailed explain of outome please help me here,. to get the results

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)

1)
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

2)

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)

please share your ideas

Thanks in advance
asitti

dhani
Posting Yak Master

132 Posts

Posted - 2013-05-07 : 17:18:56

Hello All,

please see the below individual statements for my ddl,

--Update Statement
UPDATE a
set Points=
case when (a.lastGamedate is null and b.fee>0) then b.fee
when (a.lastGamedate is not null and dateadd(DAY,49,lastgamedate) >= getdate() and a.Points < b.Fee ) then b.Fee
end,
LastGameDate = case when (a.lastGamedate is null and b.fee>0) then DATEADD (D, -1 * DatePart (WEEKDAY , GETDATE()) + 7, getdate()) end
from StudentNew a inner join StudentOffers b on a.Sno =b.Sno and a.Sname =b.Sname

---Insert Statement
Insert into StudentNew
Select b.Sno ,b.Sname , case when (b.Fee>0) then DATEADD (D, -1 * DatePart (WEEKDAY , GETDATE()) + 7, getdate()) else null end ,b.DoB , case when ( b.Fee >0) then b.Fee else null end
from StudentNew a right join StudentOffers b on a.Sno =b.Sno and a.Sname =b.Sname
where a.Sno is null and a.Sname is null

please assist me how can i make this into single statement as effective one,

also if you observe somehow i am not feeling these statements the way i wrote is not good looking (even though it is functioning corretly) i mean for example rather than going the whole table read evey time can i include where clause in update statement so that it is efficient)please suggest me the changes

please kindly help me

thanks in advance
Dhani

chadmat
The Chadinator

1974 Posts

Posted - 2013-05-07 : 19:15:06

I can't really tell for sure, but it looks like you want a Merge Statement.

-Chad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources