搜索记录并在sql数据库中的行中插入新值。 [英] Search the record and insert new values in rows in sql database.

问题描述

我有一张桌子签出



栏名：



EMPID

CHECKTIME

费用

公司通行证

总额


现在我必须写一个查询来检查从检查时间开始的时间，如果它是> 7：00：00和< 12：00：00然后将10插入列COST和20列公司通行证和总计成本+公司通过列总计即= 30，



否则，如果检查时间> 12：00：00，COST = 20且公司通行证= 30且总计= COST + COMPANY PASS。

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.

推荐答案

这里没有显示字段的数据类型。

如果有效，请参阅以下查询。

Hi,

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

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)

如果您正在考虑将EMPID作为条件然后检查以下查询。

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

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) > CAST('12:00:00' AS DATETIME)
   AND EMPID=@EMPID

这两个查询都将执行，其中一个将更新EMPID的值。





另一种方法是使用if和else条件。

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


Another way is using if and else conditions.

如果你想插入然后使用..

Hi,

if you want to insert then use..

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

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

这篇关于搜索记录并在sql数据库中的行中插入新值。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！