How to write sub Query using IF conditions

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi All,

I have one table and multiple columns my table data like this
tblEmployeeTimeCard

RecID |EmployeeID|clientID |FromTime              |ToTime                 |AutoDetect 
1     |  1       |1        |2013-04-05 08:10:00AM |2013 -04-05 12:00:00PM |1
2     |  1       |1        |2013-04-05 13:00:00PM |2013-04-05 19:10:00PM  |1
3     |  1       |1        |2013-04-05 20:00:00PM |2013-04-05 23:45:00 pM |1

the above rows are single Employee work stats report. my question is
1)how to get the total(SUM) working hours in particular day.
2)Employee works above 6hrs in particular Day then Every 6hr detect the 30 mints form total hours
(i.e- Subtract the detect mints form (sum)total working hrs when autoDetect= 1)

how to write the query for get the total working hours with in the single row.

please help me..

Thanks and Regards

Posted 20-May-13 4:05am

Santhosh23

Updated 20-May-13 10:19am

Maciej Los

v2

Add a Solution

1 solution

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)

gvprabu · Accepted Answer · 2013-05-20T05:57:00

Solution 1

Hi,

check the following Code

SQL

-- Question 1 Answer
SELECT E.EmployeeID,E.Day, (E.TotalTime/60) 'TotalHours', (E.TotalTime%60) 'Minites'
FROM (SELECT EmployeeID,CONVERT(VARCHAR(10),FromTime,102) 'Day',SUM(DATEDIFF(mi,FromTime,ToTime)) 'TotalTime'
      FROM tblEmployeeTimeCard
      GROUP BY EmployeeID,CONVERT(VARCHAR(10),FromTime,102))E

-- Question 1&2 Answer
SELECT E.EmployeeID,E.Day, (E.TotalTime/60) 'TotalHours', (E.TotalTime%60) 'Minites',
CASE WHEN (E.TotalTime/60)>=6 THEN E.TotalTime - (30* ROUND((E.TotalTime/60*6),1))
     ELSE (E.TotalTime/60) END 'TotalWorkHours'
FROM (SELECT EmployeeID,CONVERT(VARCHAR(10),FromTime,102) 'Day',SUM(DATEDIFF(mi,FromTime,ToTime)) 'TotalTime'
      FROM tblEmployeeTimeCard
      GROUP BY EmployeeID,CONVERT(VARCHAR(10),FromTime,102))E

For ur Question 2, I am not getting. give some more details about that. if possible send some sample data. if access over lap like (From date is 2013 -04-05 11:55:00PM to 2013-04-05 19:10:00PM)
then we can't able to fine the time difference. So check the code for Question No 1 and give the feedback.

Regards,
GVPrabu

Posted 20-May-13 5:57am

gvprabu

Updated 20-May-13 21:35pm

v2

Comments

Maciej Los 20-May-13 16:35pm

It should works!
+5

Santhosh23 21-May-13 3:00am

hi prabu..
thanks for replay
your query is working fine. thank you so much...

Santhosh23 21-May-13 3:06am

my second questions is how to detect the hours from TotalHours. i.e
employee working 14hours in single day. so my question is..
after every 6 hours subtract the 30 mints from TotalHours. employee worked on 14hrs in the Day. so Subtract the 60 mints in total hours. how to write the query for subtract detect hours and get the reaming hours..?
please help me prabhu..
thanks

gvprabu 21-May-13 3:22am

ok fine, I ll check and get back to u soon :-)

gvprabu 21-May-13 3:36am

hi,
Check my solution, I updated. I can't able to check this bcos in my machine I don't have SQL Server. :-) :-) :-)

Santhosh23 21-May-13 5:05am

hi prabhu..
just am modify single line in your query now its working fine..

CASE WHEN (E.TotalTime/60)>=6 THEN E.TotalTime - (30* ROUND((E.TotalTime/60*6),1))
replace with

CASE WHEN (E.TotalTime/60)>=6 THEN E.TotalTime - (30* ROUND(((E.TotalTime/60)/6),1))

than q so much.. prabu..
i have any doubt in sql server query. i will contact with u..

thanks and regards

gvprabu 21-May-13 5:47am

yes correct, I missed bracket "()", try this also
CASE WHEN (E.TotalTime/60)>=6 THEN E.TotalTime - (30* ROUND((E.TotalTime/(60*6)),1))
Both also same divide by two numbers or multiple by that number and divide one time.
Ex 12 divide by 2 then divide by 3 = 12/2=6 6/3= Ans :2 same as 12/(2*3) = 12/6 = 2
:-) :-) :-)

Santhosh23 21-May-13 6:55am

good logic..
nice..

Santhosh23 24-May-13 5:04am

hi prabu..
i want i more query using Case statement using case
above query inner join with anther table then i didn't get any rows. then how to update one table row value. if else after inner join display any rows no need to update query.

how it possible please help me..
thanks and regards

SELECT E.EmployeeID,E.Day, (E.TotalTime/60) 'TotalHours', (E.TotalTime%60) 'Minites',
CASE WHEN (E.TotalTime/60)>=6 THEN E.TotalTime - (30* ROUND((E.TotalTime/60*6),1))
ELSE (E.TotalTime/60) END 'TotalWorkHours'

gvprabu 24-May-13 5:49am

If you ll use INNER JOIN, ur Tables have proper data. If table 2 didn't have any records for the join condition then u can't get the records.

gvprabu 24-May-13 5:50am

if u ll use more JOIN also not a problem, If ur joining table have data.

Santhosh23 24-May-13 6:54am

hi prabhu how to get the days between two dates
my query like this
select ((DATEDIFF(d,2013-01-25,2013-03-25))) but output shows -2, i given yyyy-mm-dd format
what is error. how to get total days

Santhosh23 24-May-13 7:32am

ok prabu i solved my problem. am not mention single code. this is error in my query

gvprabu 24-May-13 7:34am

Just now I saw, ok good

gvprabu 24-May-13 7:37am

Hi,
Check the following link... in Code Project
http://www.codeproject.com/Articles/566542/Date-and-Time-Data-Types-and-Functions-SQL-Server
Like this u ll get so many articles...

Santhosh23 28-May-13 7:54am

Hi Prabu..
i have small doubt in sql query. am repeat the while loop in my query. same query executes 10 times. each and every time get some value. how to show total some value(all of some values) or how to sum all some values and display as anther column
please give me any ideas.

thanks and regards

gvprabu 28-May-13 8:46am

Hi,

Use Variables, If another column means.... I am not getting your Question.

Santhosh23 28-May-13 9:33am

ya i tried declare variables. but not get total some value. another column means. display total some value with in same table.? its possible.?

gvprabu 28-May-13 9:55am

shall u use like SELECT @VariableName=ISNULL(@VariableName,0)+SUM(ColumnName) FROM YourQuery

SELECT Column_List, @VariableName
FROM YourQuery

Santhosh23 11-Jun-13 5:35am

Hi Prabu.. How r u..?
I have Employee Time sheet table in this table Form-time and to-time and Admin-Approve-on three columns are there. you are given good solution for me. same like that
1) Admin approve 10 records at a time based on employee request different dates. how to get the employee working dates when admin approve on date.
2) suppose am get 5 the 5 different days then how to get the time record for each and every day..?
3) how to get the working hours. above 6 hrs work detect the 30 mints.

please prabu give me any suggestions.?
thanks and regards

gvprabu 11-Jun-13 5:45am

For
1) Admin Approve Details, U have any indicator in same table, then we can use that.
2) I am not clear about your question.
3) I am not clear about your question.(This also same like that right)

Santhosh23 11-Jun-13 6:35am

ya default value is NULL. when admin approve employee request that time am inserted.
employee send request for admin approval (2013-6-5 & 2013-6-7) but admin approved date is 2013-6-9.
when ever am select the admin approved date ex - 2013-6-9 then shows the working dates. this is question 1

and
2) display the the total working hours day wise. (2013-6-5 working 7hours) (2013-6-5 working 5 hours)
this 2 query example

3)now detect the 30 mints every 6 hours day wise.

gvprabu 11-Jun-13 6:43am

So Qn 2,3...
Date Work Hours Accrual work Hours
2013-06-10 8 7.5
Right

Santhosh23 11-Jun-13 6:45am

yup excalty.. prabu...

Santhosh23 11-Jun-13 6:46am

did you get the question 1.. idea..?

gvprabu 11-Jun-13 6:51am

not yet.... :-)

Santhosh23 11-Jun-13 6:54am

am passing the admin approve date then display the employee working days or request days.

gvprabu 11-Jun-13 7:07am

so in that same query u need this all out put or this is different query

gvprabu 11-Jun-13 7:07am

if its different query means u ll use that condition in case Statement

Santhosh23 11-Jun-13 7:12am

yup prabu.. i tried with sub query's. but i got the error like
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

gvprabu 11-Jun-13 7:15am

yes , Subquery it will work for one values while u ll use <,>,<=,>=,
So just try with 'IN' Operator.
Send ur Query ... what u tried.?

Santhosh23 11-Jun-13 7:20am

Declare @Days datetime
Begin

if((select Distinct DATEADD(dd, 0, DATEDIFF(dd, 0,SubmittedOn)) as WorkingDate
--SubmittedOn
from dbo.tblEmployeeTimeCards where
EmployeeID=71 and ClientID=20
and convert(Datetime,convert(char,AdminApporveOn,20),20) between
convert(Datetime,convert(char,'6-7-2013 7:00:00',20),20) AND convert(Datetime,convert(char,'6-21-2013 6:59:00',20),20)) is Not null)
Begin

END

END

just am tried in normal way.. please check my query

Santhosh23 11-Jun-13 7:22am

if(@Mode=5) -- Get Total hours In Particular day with employee and ClientID (Client)
BEGIN
DECLARE @FromDate4 DateTime
DECLARE @RegHrs Int
SET @FromDate1 = @From
WHILE @FromDate1 <= @To
BEGIN
IF((select IsDeduct30minforevery6hours from dbo.tblEmployeeWagesFromClient where EmployeeID=@EmployeeID AND EmployerID=@ClientID)=0)
Begin

SELECT DISTINCT EmployeeName,E.EmployeeID,E.Wage, ClientID, (TotalTime/60) 'TotalHours', (TotalTime%60) 'Minites',((TotalTime/60)*60+(TotalTime%60) ) 'TotalMints',
((TotalTime/60)*60+(TotalTime%60) )'Remaing Mintus',
(Convert(varchar,((E.TotalTime - (30* ROUND(((E.TotalTime/60)/6),2)))/60)))+':'+(convert(varchar,((E.TotalTime - (30* ROUND(((E.TotalTime/60)/6),2)))%60))) 'Display',
(((E.TotalTime/60)*60+(E.TotalTime%60))*((E.Wage)/60)) 'PayPeriod',
CASE WHEN (TotalTime='' or Totaltime=0) THEN
(0)
ELSE (0) END 'Detect Mintus'

FROM (
SELECT DISTINCT Emp.EMPLOYEE_FIRSTNAME+' '+Emp.EMPLOYEE_LASTNAME 'EmployeeName',
CONVERT(decimal,EWC.ForHourRate) 'Wage', ETC.EmployeeID,ClientID,SUM(DATEDIFF(mi,FromTime,ToTime)) 'TotalTime'
FROM tblEmployeeTimeCards ETC INNER jOIN
EMPLOYEE_DETAILS Emp on Emp.EMPLOYEE_ID= ETC.EmployeeID inner join
tblEmployeeWagesFromClient EWC on EWC.EmployeeID=ETC.EmployeeID and EWC.EmployerID=ETC.ClientID

where ETC.EmployeeID=@EmployeeID and ETC.ClientID=@ClientID and
convert(Datetime,convert(char(10),ETC.AdminApporveOn,103),103) between convert(Datetime,convert(char(10),@From,103),103) AND convert(Datetime,convert(char(10),@To,103),103)
and
CONVERT(datetime, CONVERT(VARCHAR(10),etC.AdminApporveOn, 112)) = DATEADD(dd, 0, DATEDIFF(dd, 0,@FromDate1))
and ETC.IsAdminApporve=1 and ETC.AdminApporveOn is not null

GROUP BY ETC.EmployeeID,ETC.clientID,Emp.EMPLOYEE_FIRSTNAME,Emp.EMPLOYEE_LASTNAME,EWC.ForHourRate )E
WHERE E.EmployeeID=@EmployeeID and ClientID=@ClientID
SET @FromDate1 =DATEADD(d,1,@FromDate1)

END
ELSE
BEGIN

SELECT DISTINCT EmployeeName, E.EmployeeID,ClientID,Wage, (TotalTime/60) 'TotalHours', (TotalTime%60) 'Minites',((TotalTime/60)*60+(TotalTime%60) ) 'TotalMints',

CASE WHEN (E.TotalTime/60)>=6 THEN E.TotalTime - (30* ROUND(((E.TotalTime/60)/6),1))
ELSE ((TotalTime/60)*60+(TotalTime%60) ) END 'Remaing Mintus',
CASE WHEN (E.TotalTime/60)>=6 THEN (30* ROUND(((E.TotalTime/60)/6),1))
ELSE 0 END 'Detect Mintus',

CASE WHEN (E.TotalTime/60)>=6 THEN (Convert(varchar,((E.TotalTime - (30* ROUND(((E.TotalTime/60)/6),2)))/60)))+':'+(convert(varchar,((E.TotalTime - (30* ROUND(((E.TotalTime/60)/6),2)))%60)))
ELSE (Convert(varchar,((E.TotalTime - (30* ROUND(((E.TotalTime/60)/6),2)))/60)))+':'+(convert(varchar,((E.TotalTime - (30* ROUND(((E.TotalTime/60)/6),2)))%60))) END 'Display',
CASE WHEN ((E.Wage != 0 Or E.Wage !='') and (E.TotalTime/60)>=6 )
THEN (E.TotalTime - (30* ROUND(((E.TotalTime/60)/6),1)))*((Wage)/60)
ELSE (((E.TotalTime/60)*60+(E.TotalTime%60))*((Wage)/60) ) END 'PayPeriod'
FROM (
SELECT DISTINCT Emp.EMPLOYEE_FIRSTNAME+' '+Emp.EMPLOYEE_LASTNAME 'EmployeeName',
CONVERT(decimal,EWC.ForHourRate) 'Wage', ETC.EmployeeID,ClientID,SUM(DATEDIFF(mi,FromTime,ToTime)) 'TotalTime'
FROM tblEmployeeTimeCards ETC inner join
EMPLOYEE_DETAILS Emp on Emp.EMPLOYEE_ID= ETC.EmployeeID inner join
tblEmployeeWagesFromClient EWC on EWC.EmployeeID=ETC.EmployeeID and EWC.EmployerID=ETC.ClientID

where ETC.EmployeeID=@EmployeeID and ETC.ClientID=@ClientID and
CONVERT(datetime, CONVERT(VARCHAR(10),etC.AdminApporveOn, 112)) = DATEADD(dd, 0, DATEDIFF(dd, 0,@FromDate1))
AND
convert(Datetime,convert(char(10),ETC.AdminApporveOn,103),103) between convert(Datetime,convert(char(10),@From,103),103) AND convert(Datetime,convert(char(10),@To,103),103)
and IsAdminApporve=1 and AdminApporveOn is not null
GROUP BY ETC.EmployeeID,ETC.clientID,Emp.EMPLOYEE_FIRS

gvprabu 11-Jun-13 7:30am

Hi frieend,
I am not able to understand, because all the scrips in single line.
Pls make some alignment and send to me

Santhosh23 11-Jun-13 7:24am

its my original query..
in mode 5 am get the total working hours admin approved date.

gvprabu 11-Jun-13 7:27am

U need to check... if that query have any values then do some process else some Process right. For this u can use EXISTS as like below.
Declare @Days datetime
Begin
if EXISTS (select Distinct DATEADD(dd, 0, DATEDIFF(dd, 0,SubmittedOn)) as WorkingDate --SubmittedOn
from dbo.tblEmployeeTimeCards
where EmployeeID=71 and ClientID=20
and convert(Datetime,convert(char,AdminApporveOn,20),20) between convert(Datetime,convert(char,'6-7-2013 7:00:00',20),20) AND convert(Datetime,convert(char,'6-21-2013 6:59:00',20),20)
)
BEGIN
-- Your SQL Code
END

END

Santhosh23 11-Jun-13 7:38am

i get the 4 days in sub query how to write query for get the total hours per day wise.
how to repeat the loop.

gvprabu 11-Jun-13 7:59am

I am not getting u. Sub Query means
date Hours
2013-06-10 6
2013-06-10 2 ...
Like this right

Santhosh23 11-Jun-13 8:54am

if condition query get 4days. how to use 4days(if condition days). how to repeat the loop and get the total hours in particular day wise (above 4 days)

Santhosh23 11-Jun-13 9:13am

my queries are..
1) First get the Employee Working days between admin approve on days.
2) Second get the total hours for based on above days.
3) detect the hours more than 6 hours.

its possible for single query above requirements.?

gvprabu 11-Jun-13 10:00am

1) Is not a problem right
2) Make GROUP BY Empid and Day then find the Day wise Total work hours.
3) use CASE Statement and do the 6 hrs calculation

gvprabu 11-Jun-13 10:01am

sorry yar.. I don't have SQL server in my machine. So I can't able to check any query with sample data... If possible send the Table script with data

Santhosh23 11-Jun-13 11:19am

give me your email id i will send the table script data

gvprabu 11-Jun-13 11:21am

venkateshprabu.g@gmail.com

Santhosh23 12-Jun-13 2:03am

check your mail and See above Mode 5 stored procedure

Santhosh23 2-Jul-13 7:46am

hi prabu.. how r u.?

how to get the top 10 records from two tables. order by last-modification desc .

its possible..?
please help me prabhu..
thanks and regards

gvprabu 2-Jul-13 10:06am

Last Modification Desc means, give some sample. Its datetime or number value.?