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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -