Search the record and insert new values in rows in sql database.

Question

0.00/5 (No votes)

See more:

I have one table checkinout

column name:

EMPID
CHECKTIME
cost
company pass
total

now i have to write a query to search the time from checktime if it is >7:00:00 and <12:00:00 then insert 10 into column COST and 20 Column COMPANY PASS and total of COST+COMPANY PASS into Column Total ie=30,

else if the checktime >12:00:00 the COST=20 and COMPANY PASS=30 and total =COST+COMPANY PASS.

Posted 31-Jul-13 23:30pm

Nawab Ahmad

Add a Solution

Comments

Nelek 1-Aug-13 5:36am

What have you tried?[^]

Nawab Ahmad 1-Aug-13 5:37am

i don't know to use if condition with sql query......

If u know plz help..

[no name] 1-Aug-13 5:52am

Then read the documentation.

2 solutions

Solution 2

Hi,

if you want to insert then use..

SQL

insert into tablename
select [EMPID],[CHECKTIME]
,case when datepart(HOUR,[CHECKTIME])>7 and datepart(HOUR,[CHECKTIME]) < 12 then 10 when datepart(HOUR,[CHECKTIME])>12 then 20 else [cost] end as [cost]
,case when datepart(HOUR,[CHECKTIME])>7 and datepart(HOUR,[CHECKTIME])< 12 then 20 when datepart(HOUR,[CHECKTIME])>12 then 30 else [company pass] end as [company pass]
,case when datepart(HOUR,[CHECKTIME])>7 and datepart(HOUR,[CHECKTIME]) < 12 then 30 when datepart(HOUR,[CHECKTIME])>12 then 50 else [Total ] end as [Total ]
from checkinout

and if you need to update

SQL

update checkinout
set cost= case when datepart(HOUR,[CHECKTIME])>7 and datepart(HOUR,[CHECKTIME]) < 12 then 10 when datepart(HOUR,[CHECKTIME])>12 then 20 else [cost] end
,[companypass]=case when datepart(HOUR,[CHECKTIME])>7 and datepart(HOUR,[CHECKTIME])< 12 then 20 when datepart(HOUR,[CHECKTIME])>12 then 30 else [company pass]  end
,[Total]=case when datepart(HOUR,[CHECKTIME])>7 and datepart(HOUR,[CHECKTIME]) < 12 then 30 when datepart(HOUR,[CHECKTIME])>12 then 50 [Total ] end

Posted 31-Jul-13 23:54pm

Adarsh chauhan

Updated 1-Aug-13 0:26am

v3

Comments

Nawab Ahmad 1-Aug-13 6:08am

My CHECKTIME value look like this 3/02/2013 7:54
but i want to take only time and insert the value....

Adarsh chauhan 1-Aug-13 6:15am

i have updated my query now check

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Harshil_Raval · Accepted Answer · 2013-07-31T23:44:00

Solution 1

Hi,

Here you have not shown datatype of fields.
See following query, if it works.

SQL

UPDATE checkinout
SET    cost=10,companypass=20,total=30
WHERE  CAST(CHECKTIME AS DATETIME) BETWEEN CAST('7:00:00' AS DATETIME) AND CAST('12:00:00' AS DATETIME)

UPDATE checkinout
SET    cost=20,companypass=30,total=50
WHERE  CAST(CHECKTIME AS DATETIME) > CAST('12:00:00' AS DATETIME)

If you are considering EMPID as where conditions then check following query.

SQL

UPDATE checkinout
SET    cost=10,companypass=20,total=30
WHERE  CAST(CHECKTIME AS DATETIME) BETWEEN CAST('7:00:00' AS DATETIME) AND CAST('12:00:00' AS DATETIME) AND EMPID=@EMPID

UPDATE checkinout
SET    cost=20,companypass=30,total=50
WHERE  CAST(CHECKTIME AS DATETIME) &gt; CAST('12:00:00' AS DATETIME)
   AND EMPID=@EMPID

This both query will execute and one of them will update value for EMPID.

Another way is using if and else conditions.

Posted 31-Jul-13 23:44pm

Harshil_Raval

Updated 31-Jul-13 23:51pm

v3

Comments

Nawab Ahmad 1-Aug-13 6:00am

Thanks ..
its working...
but one problem is there....
checktime = DATETIME formate
i have value in checktime= 6/02/2013 7:24

so, if i want to execute this code i have to mention the date also...but i have to do it by checking time only.....

Harshil_Raval 1-Aug-13 6:13am

hi, then change CAST(CHECKTIME AS DATETIME) to CAST(CONVERT(VARCHAR,CHECKTIME,108) AS DATETIME) and whole query will remain same.

Nawab Ahmad 1-Aug-13 6:20am

Thanx....
working perfectly....

Nawab Ahmad 1-Aug-13 6:22am

INSERT INTO Canteen(EMPID,CHECKTIME,CHECKTYPE,VERIFYCODE,SENSORID,LOGID) SELECT * from checkinout where sensorid='4'

i am using this code to fetch data from table where sensorid=4
so what i have to modify if i want to fetch data from two sensorid 3 and 4...

Harshil_Raval 1-Aug-13 6:26am

check this, INSERT INTO Canteen(EMPID,CHECKTIME,CHECKTYPE,VERIFYCODE,SENSORID,LOGID) SELECT * from checkinout where sensorid in ('4','3')

Nawab Ahmad 1-Aug-13 6:34am

yaa..
thanx again it working....

Nawab Ahmad 2-Aug-13 3:35am

Hi,
I am displaying a record in listview of VB 6.0 and the the total of listview items in textbox.
i can able to export the listview item in Excel Sheet but able to export the textbox data in same Excel Sheet.
PLz help..

Harshil_Raval 2-Aug-13 6:31am

Hi, I am not working in vb. So, to get help post this as seperate question so that someone can help you.

Nawab Ahmad 2-Aug-13 6:04am

select EMPID,Emp_Contrii,Comp_Contri,total
from Canteen where EMPID='187'
union all
select Null,sum(Emp_Contrii),sum(Comp_Contri),sum(total)
from Canteen

This query is working fine in place of Null 2nd last line i want to be display as Grand Total..

Harshil_Raval 2-Aug-13 6:32am

I can not understand your need here. Please describe it little bit more, or create new question for it.

Nawab Ahmad 2-Aug-13 6:43am

the above code is working fine and it is displaying the sum also but in last line
select Null,sum(Emp_Contrii),sum(Comp_Contri),sum(total)

instead of Null i want to display Grand total....

Harshil_Raval 2-Aug-13 7:08am

You mean to say, column name to Grand total, then select Null As GrandTotal,sum(Emp_Contrii),sum(Comp_Contri),sum(total)

Nawab Ahmad 3-Aug-13 0:57am

I have a table Canteen it contain one column Checktime format Datetime where date and time will come and sit after the employee show his card.
Checktime= 6/30/2010 07:31

Now employee will show his card number of time...But i want to fetch checktime record when he first time show his card between 7:00 to 8:00 and 12:00 to 1:00
means i want to show only to time the first checktime value will be the first record between 7 to 8 and first record between 12 to 1.