SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 ow to Insert/Upadte data based on exist in mathcin
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dhani
Posting Yak Master

132 Posts

Posted - 05/07/2013 :  15:55:07  Show Profile  Reply with Quote
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 - 05/07/2013 :  17:18:56  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 05/07/2013 :  19:15:06  Show Profile  Visit chadmat's Homepage  Reply with Quote
I can't really tell for sure, but it looks like you want a Merge Statement.

-Chad
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000