April 9, 2010 at 10:46 am
I have a table ABC and B
Structure of the table ABC:
NAme Datatype
ID int Unique column,
Name varchar(20),
a varchar(20),
C varchar(20),
s varchar(20),
z int
Structure of the table B:
NAme Datatype
ID int Unique column,
Name varchar(20),
Date datetime,
a varchar(20),
c varchar(20),
s varchar(20),
z int
Now there is a file with the above column names which will give us monthly data for the customername(name) and others based on customerID(ID) and gets loaded in to table B on daily basis.
Now i need to write an update statement if any of the columns like name ,a and s changes i need to update the columns for given ID which is a unique column in table ABC and ID column in table B is unique for only monthly data .
Update table ABC
set
Name= B.Name,
A=B.A,
C=B.C,
S=B.S,
z=B.z
from table B AS B
where
ID = B.ID
and(Name<> B.Name or
A<>B.A or
C<>B.C or
S<>B.S or
z<>B.z)
since , i will get unique values for ID in table B only for 1 month ,which i need to update the statement using date column in table B like this:
Declare
@FirstDay DateTime,
@LastDay DateTime
set @FirstDay= DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0)
SET @LastDay = CONVERT(VARCHAR(10), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)), 111)
Update table ABC
set
Name= B.Name,
A=B.A,
C=B.C,
S=B.S,
z=B.z
from table B AS B
where
ID = B.ID
and(Name<> B.Name or
A<>B.A or
C<>B.C or
S<>B.S or
z<>B.z)
and b.date >=@FirstDay
and b.date <=@LastDay
which will give me the first day of current month and last day of current month which will only gives me one month data,i am fine with this until i came to know that we might receive the files from distributors next month ,or after 2 month or after 3 month but whenever they give us the data they are going to in monthly format .example if they are going to give to us the data next month they are going to give it in two files one is current month another is next month and everything gets loaded into table B ..
now how can i get only monthly data for my update statement since ID(table B) is unique for only 1 month .
Can anyone tell me how can i update a row if it is from current month i want to see only current month data and date column will give the date in table B.
Can anyone please tell me how can i get monthly data depends on whatever month we are in and depending on the row and date column in table B .
i appreciate your help.
April 9, 2010 at 4:49 pm
datepart() function would do it 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 9, 2010 at 5:05 pm
itskumar2004
Please follow the first link in my signature for posting information in a reasonably consumable format, i.e. Create table statements, sample data (be careful NOT to use real names nor addresses we do NOT want personal data to be available on the web, for those individuals who might attempt to harm / exploit the identified individuals).
Posting in the requested manner will assist you in getting a tested solution.
April 12, 2010 at 8:33 am
datepart() function would do it
I am Updating the table in a cursor which is in stored procedure (developed by someone).
here the table B is used as cursor which will manipulate data row by row.
my question is when it is manipulating data row by row how can check for unique month rows ..
Since in table B Data is unique for only one month.
please let me know if anyone don't get what i am trying to say
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply