I have one column named as Time with another column named as ID.
I want to divide the column Time in two different columns as InTime & OutTime.
But the important thing is that if the count of that ID present in a ID column is even, the corresponding value present at that ID of column Time will be inserted in a column named as OutTime & if the count of that ID is odd the corresponding value present at that ID of column Time will be inserted in a column named as InTime. Please help me
Its Urgent..
Please help me as I'm new in this field.....
THANK YOU in Advance.
Riddhi ValechaPosted May 14, 2013, 12:19 AM
Get the ID,
Check whether it is Odd or Even.
Insert date accordingly.
Hope this helps.
Jignesh TrivediPosted May 14, 2013, 12:03 AM
this is some what difficult
but try
select t.Id,t.time as Intime, t1.time as outTime from (
(select ROW_NUMBER() OVER(ORDER BY id)row,* from #temp)) t
Left outer join
(
select ROW_NUMBER() OVER(ORDER BY id)row,* from #temp
) t1 on t.Id = t1.Id and t.row = (t1.row - 1)
hope this will help you.
MakPosted May 13, 2013, 11:54 AM
ie if values are like this
Insert #temp values(1,'09:30')
Insert #temp values(1,'12:30')
Insert #temp values(1,'18:30')
I want data as
ID Intime Outtime
1 09:30 12:30
1 Null 18:30
Please Reply..
Jignesh TrivediPosted May 13, 2013, 2:12 AM
try
create table #temp
(
Id int,
time Time,
)
select * from #temp
Insert #temp values(1,'09:30')
Insert #temp values(2,'06:30')
Insert #temp values(3,'09:30')
Insert #temp values(4,'06:30')
Insert #temp values(5,'09:30')
Insert #temp values(6,'06:30')
select Id,(case when (Id%2)=0 then null else time end) as inTime,(case when (Id%2)=1 then null else time end)as OutTime from #temp
MakPosted May 13, 2013, 12:24 AM
ID & Time...
Riddhi ValechaPosted May 13, 2013, 12:10 AM
I think there will be 3 columns in the table-
ID-int;pk
InTime-datetime
OutTime-datetime
----------------------
SQLCommand cmd;
---------------------
1)select max(id) from table;
int id = cnvert.toint32(cmd.executescalar());
if(id % 2 == 0) // even
{
string s="update table set OutTime=getdate() where ID=id";
}
else if(id % 2 != 0)
{
string s="update table set InTime=getdate() where ID=id";
}
-----------
I am assuming that you know how to set SQLConnection ; SQLCommand and other things. I have set the business logic