Substract pairs in SQL

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hello Guys,

I have to extend an existing database application for time tracking. The database design is not that nice so I have to handle a lot with SQL statements.
We have a table like this:
(Date= ddMMyyyy Time= hhmmss)

Username      Indicator      Date      Time<br />
User 1        Check-in       26012017  075598<br />
User 2        Check-in       26012017  075652<br />
User 3        Check-in       26012017  075865<br />
User 1        Check-out      26012017  083652<br />
User 3        Check-out      26012017  083743<br />
User 3        Check-in       26012017  083856<br />
User 1        Check-in       26012017  085536

at the beginning of the workday the user has to check in, at the end of the day the user has to check out, for every cigarette or coffee the user has to check out and check in again.
I want to get the overall difference between two pairs but I dont get it how to manage it.

Thank you,
Vince

What I have tried:

Sum-function, difference Funktion with only two pairs by hand but I need more

Posted 25-Jan-17 22:01pm

Haechtsuppe

Updated 25-Jan-17 23:54pm

Add a Solution

Comments

Peter Leow 26-Jan-17 5:53am

Substract what from what? What constitutes a pair? I supposed the date and time are in varchar, if so, this table is really suck.

Haechtsuppe 26-Jan-17 7:14am

yep it's a 'char' field, based on Stone-aged AS400 table. Nobody cared about those Problems in these days and now in 2017 a mid-twenty aged developer has to handle schemes which are older than me :-|

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)

User 10734264 · Accepted Answer · 2017-01-25T23:54:00

Solution 1

Please check the following sql query which will show the each user check in and check out details

select username,date,time as checkin, 0 as checkout, 0 as diff 
into #temp from tablename as t
where t.indicator='Check-in'  


update t set t.checkout=isnull((select top 1 a.time from tablename as a where a.username=t.username and a.date=t.date and a.indicator='Check-out'  and a.time>t.checkin order by checkin desc),0)
from #temp as t   

update t set t.diff=(checkout-checkin)
from #temp as t 

select * from #temp

Posted 25-Jan-17 23:54pm

User 10734264

Comments

Haechtsuppe 26-Jan-17 6:44am

Oh well thank you, that works so far, but I also extended my web Service with the latest check-out entry, than calculate and write it back to temporary table.

"SELECT TOP 1 time FROM tblmyTable where username = '" + username + "' AND date = '"+ int.Parse(time.ToString("ddMMyyyy"))+"' AND indicator='check-out' ORDER BY time DESC;";

but your solution also works for me with a few customizations.
Thank you

Richard Deeming 26-Jan-17 8:48am

That code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]