|
Hi.
I have just starting learning database so this would be a rather basic question but I can't figure out how to achieve this.
I have a table in database in which there is a column named Date where I store a date( Just the date in the format dd/mm/yy) and there is another column named IntegerColumn in which initially I am storing the integer value 0.
Question is
How to increment the interger value 0 by 1 in IntegerColumn when the date in Date is less then the current date.
Note that the data type for the column storing the date is nvarchar(50) and I am keeping in check the date format i.e dd/mm/yy through code behind in my asp.net application. And additionally I am using SQL Job Scheduling for the query to recur each time SQL Server finds the date less then the current date.
I have done this much...
DECLARE @IncrementValue int
SET @IncrementValue = 1
UPDATE tableA SET IntegerColumn = IntegerColumn + @IncrementValue
WHERE Date < GETDATE()
|
|
|
|
|
I would recommend that you change the datatype of your date column to datetime - that's what it's there fore!
Also, give your column a more meaningful name then Date - that is a reserved word and may cause you trouble in the future.
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Chris thanks for the reply. Chris I infact changed the datatype to date and additionally my column name is actually ReturnDate not Date, I had chose just a random name. But still when I execute the query it does not increment the value in Column. Any more suggesstions ?
|
|
|
|
|
If you are using SQL Server, you don't need to run a job to do this - you can have a computed column.
Create a column Called Days (or whatever you want to call it). Do not set a data type. In the computed column formula enter
datediff(d,[DateColumn], Getdate()) - this calculates the number of days between the [DateColumn] and teh current date
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
You avoided a nasty situation (and comment) by changing to date format in your database, ALWAYS store date in the correct format.
If you go down the path you describe, your are going to have to run the query EVERY day. As Chris suggested use a virtual computed column, I would use a view for this. Do not store the value, a view will automatically calc every time you access it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks RAH for the reply. Firstly I agree with the format thing. Secondly I want the increment to be totally automatic and should occur without any interference( i mean without accessing it ). I hope I understood you right in this because I have never used a virtual computed column or a view before. Do the ones you have suggested do it automatically. If they do so i'll surely have a try.
|
|
|
|
|
As I said earlier, using a computed column you do not need a job to run at all
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
hilbiazhar wrote: I want the increment to be totally automatic
Title claims otherwise.
hilbiazhar wrote: should occur without any interference( i mean without accessing it
A computed column would be calculated when it's value is requested. Why should it be without any interference? If you don't look at the number, then what is the use of incrementing it in the first place?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Oh i got it now. Actually I had not understood it properly. Thanks for making it clear anyway. Definitely this would be a good approach than keeping SQL Job Schedules like you said.
|
|
|
|
|
/* 2:Display the patient_id, patient_name, phone number( +91-999-999-9999 format) , type_of_ailment .
type of ailement is decided with the number of days stay in hospital.
if stay <=5 ---- Minor
if stay >5 and <=15 days ----Medium
if stay >15 days ---- Major */
I have 2 tables room_allocation and patient_master where pid is the primary key of patient_master table and it also happens to be foreign key in room_allocation table.
1)Columns of patient_master table are(pid Varchar(5) Primary Key,
name Varchar(20) Not Null,
age int Not Null,
weight int Not Null,
gender Varchar(10) Not null,
address Varchar(50) Not Null,
phoneno varchar(10) Not Null,
disease Varchar(50) Not Null,
doctorid Varchar(5) Not Null,
constraint patient_master_doctorid_fk foreign key(doctorid) references doctor_master(doctorid))
2)columns of room_allocation table are
(room_no varchar(5),
pid varchar(5),
adm_date date,
release_date date,
constraint room_allocation_room_no_fk foreign key(room_no) references room_master(room_no),
constraint room_allocation_pid_fk foreign key(pid) references patient_master(pid))
--------------------------------------------------------------------------
I have written the following querry for above question but it gave a syantax error.
-------------------------------------------------------------------------
select p.pid,p.name,concat(
case (release_date-adm_date)
when (release_date-adm_date)<=5 then
when ((release_date-adm_date)>5 and (release_date-adm_date)<=15) then
else
end as
from patient_master p,room_allocation r
when (release_date-adm_date)<=5 then minor
when (release_date-adm_date)>5 and when (release_date-adm_date)<=15 then med
from patient_master p,room_allocation r
where p.pid=r.pid ;
|
|
|
|
|
Look at your query closely, and you will see that you have duplicated the when clauses in the second half of the query
select p.pid,p.name,concat('91-',p.phoneno),
case (release_date-adm_date)
when (release_date-adm_date)<=5 then 'minor'
when ((release_date-adm_date)>5 and (release_date-adm_date)<=15) then 'medium'
else 'Major'
end as 'type_ailment'
from patient_master p,room_allocation r
when (release_date-adm_date)<=5 then minor
when (release_date-adm_date)>5 and when (release_date-adm_date)<=15 then med
from patient_master p,room_allocation r
where p.pid=r.pid ;
Everyone dies - but not everyone lives
|
|
|
|
|
Thanx for replying.I have duplicated the when clauses but I need to test for each condition so what can be the exact alternative.I tried removing the second when clause and replaced it with else clause but still it does not work.Should I go for nested if.
|
|
|
|
|
select p.pid,p.name,concat('91-',p.phoneno),
case
when (release_date-adm_date)<=5 then 'minor'
when (release_date-adm_date)<=15 then 'medium'
else 'Major'
end as 'type_ailment'
from patient_master p,room_allocation r
where p.pid=r.pid
|
|
|
|
|
Thank You very much it worked.
|
|
|
|
|
Good day to all,
Why tinyint(3) of mysql is treated as sbyte in .net(c#).
I cant find any official datatype mapping for .net and mysql out there.
|
|
|
|
|
|
sorry but still dont get it.
example: var t = typeof(reader[0]);
the "t" is a type of sbyte, i was expecting it to be int or short.
could be the value matter also?
BTW i dont use (example: reader[0].toInt()) for a purpose.
|
|
|
|
|
The link I gave you shows the mapping: tinyint maps to a single byte which, in computer memory, is a signed byte. This allows you to promote it to an integer without the necessity of any data conversion.
Explained in slightly more detail at http://dev.mysql.com/doc/refman/5.1/en/integer-types.html[^].
|
|
|
|
|
ohh thank you so much, now i understand it.
|
|
|
|
|
I'm experimenting with having multiple threads performing INSERTs into a table (no SELECTs, UPDATEs, or DELETEs) and I'm curious how the various IsolationLevels[^] affect performance. I have tried a few and seen no big differences. The documentation and what other information I have seen online (including here) has always seemed pretty vague to me -- and really only talks about reads (which aren't pertinent in this case).
Anyone have any insight on how IsolationLevel affects INSERTs?
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
PIEBALDconsult wrote: and really only talks about reads
Because that's what could give trouble; if thread 1 does an partial update on a table, while thread 2 is reading that table - do you want the old or the new values in your result for thread 2?
I don't see any problems when multiple threads insert data. That is, if they are values - if you are reading within that insert-statement (a select, like say, the count of this table), then you might have above problem again.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Eddy Vluggen wrote: if they are values
Right, just simple insertion of values, no reading.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
Rec_ID Rec_Name Dist_ID
1 A 1
2 B 1
1 AA 2
2 AB 2
3 C 2
How can i write a Insert sql script for this table, eg: i should insert Rec_ID 3 while inserting Record for Dist_ID 1 and also Rec_ID 4 for Dist_ID 2, Suppose if many users inserting from different places, how to handle this situation. what is the best method? I have following 2 ideas, which is right idea or is there any other method to implement this
1) Select Max(Rec_ID) for the district, suppose if 2 people inserting at the same time will be a issue.
2) Put a Lock in the Stored Procedure
|
|
|
|
|
Specifying Rec_ID as an identity[^] column may do all of that work for you.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
I Cannot set it as Identity column, because based on the District ID should get the Next Rec_ID
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
