May 6, 2013 at 1:53 pm
Hello All,
please help me with this single select statement,
StudentNew table is exist with below values, (sno and sname is composite primary key)
Sno,
Sname,
LastGameDate,
DateofBirth,
Points
StudentOffers is the table exist with (sno is primary key)
Sno,
Sname,
DoB,
City,
Fee,
State
so in studentofferes is the warehouse table, it has 100K data and it get adds/updates every day,
now my question is, i have a new table StudentNew, it has some data in it (about 5k)
i would like to insert/update to the studentNew table based on SNO and SNAME columns match
If no Sno, Sname matching record in Student Name table,
then if Fee colum > 0
insert into studentNew Table (sno,sname,current week friday date,dob,fee)
else
insert into studentNew Table (sno,sname,null,dob,null)
if matching record found (sno, sname exist) in StudentNew table then
check LastGameDate column has value and +7 weeks to that value is > today then
if points column is null or 0 and Fee from studentOffers is > 0 then update points column with fee column, and update lastgamedate column to current week friday date
if points column value is already > 0 and points < Fee column from studentoffere then update points with fee column
check LastGameDate column has value and +7 weeks to that value is < today then no action
how to write this statement in more optimized manner please help me,
i do have step by step approach (in procedure., step by step checking)
please kindly help me, how to write in very efficient way
Kind Regards, thanks a ton in advance
dhanvi
May 6, 2013 at 2:24 pm
Could Somebody please assist me here,
is it something possible?
any ideas
please,,,
Thanks in advace
dhani
May 6, 2013 at 2:34 pm
Asita, the question itself seems a lot like homework; Also, to get intelligent answers, you really need to provide DDL and sample data, to avoid a lot of assumptions, and to make everything pragmatically clear.
we would love to help you grasp the concepts, that's exactly why so many of us volunteer, but you've got to show us what you've got so far; show your work, and we'll offer pointers and concepts.
for the DDL,for example, i just guess all your columns are varchars, since you didn't say:
CREATE TABLE [dbo].[StudentNew] (
[Sno] VARCHAR(30) NOT NULL,
[Sname] VARCHAR(30) NOT NULL,
[LastGameDate] VARCHAR(30) NULL,
[DateofBirth] VARCHAR(30) NULL,
[Points] VARCHAR(30) NULL,
CONSTRAINT [PK_StudentNew] PRIMARY KEY CLUSTERED ([Sno],[Sname]) )
CREATE TABLE [dbo].[StudentOffers] (
[Sno] VARCHAR(30) NOT NULL,
[Sname] VARCHAR(30) NULL,
[DoB] VARCHAR(30) NULL,
[City] VARCHAR(30) NULL,
[Fee] VARCHAR(30) NULL,
[State] VARCHAR(30) NULL,
CONSTRAINT [PK_StudentOffers] PRIMARY KEY CLUSTERED ([Sno]) )
for the actual questions, you mentioned in BOLD "if matching record found (";
does that mean you are trying to learn how to use the MERGE statement?
Lowell
May 6, 2013 at 2:37 pm
here is a very basic MERGE exampel that might help, and of course you should read up on it in your books and in SQL Books Online:
MERGE INTO dbo.tableA AS Target
USING(
SELECT ITEM, DESCRIPTION FROM dbo.tableB
) AS source
ON (target.ITEM = source.ITEM)
WHEN NOT MATCHED BY TARGET THEN
INSERT (ITEM, [DESCRIPTION])
VALUES (ITEM, DESCRIPTION);
Lowell
May 7, 2013 at 12:31 pm
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
May 7, 2013 at 1:32 pm
please help me
Kind Regards
asittti
May 7, 2013 at 1:42 pm
asita (5/7/2013)
please help meKind Regards
asittti
for me at least...it would help to see what you currently have coded..as per your original comment below.
how to write this statement in more optimized manner please help me,
i do have step by step approach (in procedure., step by step checking)
please kindly help me, how to write in very efficient way
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 7, 2013 at 1:57 pm
Thank you livingston,
i thought it is simple, but when i am writing the code indidvidual update stement it is taking time,
i am sorry it is not ready yet, but i am working on...
please forgive me for wrong wording....
Best Regards
asittti
May 7, 2013 at 3:15 pm
Please Find the individual statements
--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
this works but please assit 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 well) i mean for example rather than going the whole table read eveytime can i include where clause in update statement so that it is efficient
please kindly help me
thanks in advance
May 7, 2013 at 4:41 pm
Could somebody please assist me
if i write update statement without any where clause, it is full table scan,
here the in my exist stable they have about 7 million rows, and also in new table (where update/insert take place) will have these many rows (after first insert/update),
rather than issueing stright update command, can we change the logic (functioanlly same) to include where clause etc. to restrict full table scan etc.,
or if you suggest it is good then i will go ahead
please advice me, also with merge statement
thank you
dhani
May 7, 2013 at 4:58 pm
Finally merge staement worked as below
MERGE studentnew AS stTarg
USING (select [Sno], [Sname] , [DoB],[City],[Fee] , [State]from [StudentOffers]) AS studSrc
ON studSrc.sno=stTarg.sno and studSrc.sname = stTarg.sname
WHEN MATCHED THEN
UPDATE
set stTarg.Points=
case when (stTarg.lastGamedate is null and studSrc.fee>0) then studSrc.fee
when (stTarg.lastGamedate is not null and dateadd(DAY,49,stTarg.[LastGameDate]) >= getdate() and stTarg.Points < studsrc.Fee ) then studsrc.Fee
end,
stTarg.LastGameDate = case when (stTarg.lastGamedate is null and studsrc.fee>0) then DATEADD (D, -1 * DatePart (WEEKDAY , GETDATE()) + 7, getdate()) end
WHEN NOT MATCHED THEN
Insert ([Sno],[Sname], [LastGameDate] , [DateofBirth], [Points])
values (studSrc.Sno ,studSrc.Sname , case when (studSrc.Fee>0) then DATEADD (D, -1 * DatePart (WEEKDAY , GETDATE()) + 7, getdate()) else null end ,studSrc.DoB , case when ( studSrc.Fee >0) then studSrc.Fee else null end)
;
please help me with Update statement is there anyway to wriet case statment to more readability and efficientway of lkogic rather check the same condition again and again etc
please?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply