If I understand the problem correctly, a start date means the preceeding date in not in the table; an end date is the last successive date from a start date in the table or the start date if there are no successive dates.
2014-01-01 is a start date because 2013-12-31 doesn't exist; 2014-01-01 is the end date because 2014-01-02 doesn't exist.
2014-01-04 is a start date because 2014-01-03 doesn't exist, giving 2014-01-06 as the end date.
And so on...
Given that, provide the SQL statement you are using and someone may help; don't ask them to write everything for you.
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.
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.
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 ?
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
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.
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[^]
/* 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----Minorifstay>5 and <=15days----Mediumifstay>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
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('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'endas'type_ailment'
from patient_master p,room_allocation r
when (release_date-adm_date)<=5 then minor
when (release_date-adm_date)>5 andwhen (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
when (release_date-adm_date)<=5then'minor'when((release_date-adm_date)>5 and (release_date-adm_date)<=15)then'medium'else'Major'endas'type_ailment'frompatient_masterp,room_allocationrwhen(release_date-adm_date)<=5thenminorwhen(release_date-adm_date)>5 and when (release_date-adm_date)<=15thenmedfrompatient_masterp,room_allocationrwherep.pid=r.pid;
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.