Meaning of a sql query [Resolved]

Posted by Cibin under Sql Server on 7/30/2013 | Points: 10 | Views : 2915 | Status : [Member] | Replies : 20
Hello,

Can anyone Please tell me the meaning of this sql query " SELECT Min(CHECKTIME) StartTime, UpTime EndTime,
DateDiff(MI,Min(CHECKTIME),UpTime)
FROM (
SELECT Down.CHECKTIME,
(
SELECT Top 1 Up.CHECKTIME
FROM sample Up
WHERE Up.CHECKTIME > Down.CHECKTIME
AND Up.CHECKTYPE = 'O'
ORDER BY Up.CHECKTIME ASC) UpTime
FROM sample Down
WHERE Down.CHECKTYPE = 'I' ) X
GROUP BY UpTime
ORDER BY UpTime "

Please Help me ,, I don't know how this code works....

Thanks in advance....




Responses

Posted by: Bandi on: 7/30/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
[code]I'm trying to explain with the sample data....
Check the below input & output data....
Input is of Employee's attendance data... First four rows is for 1st Jan,2013 and last 2 rows for 2nd Jan, 2013..
--INPUT
EID Date CheckTime CheckType
26359 2013-01-01 2013-01-01 09:50:00.000 IN
26359 2013-01-01 2013-01-01 11:47:00.000 OUT
26359 2013-01-01 2013-01-01 17:21:00.000 IN
26359 2013-01-01 2013-01-01 18:40:00.000 OUT
26359 2013-01-02 2013-01-02 09:40:00.000 IN
26359 2013-01-02 2013-01-02 18:55:00.000 OUT

-- To produce Checkin & CheckOut timings and also duration of IN time of a employee based on CheckTime column & CheckType
eid date timein timeout DurationInMinutes
26359 2013-01-01 2013-01-01 09:50:00.000 2013-01-01 11:47:00.000 117
26359 2013-01-01 2013-01-01 17:21:00.000 2013-01-01 18:40:00.000 79
26359 2013-01-02 2013-01-02 09:40:00.000 2013-01-02 18:55:00.000 555[/code]

Did you got the purpose of the above query? If yes please mark as Answer

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kmandapalli on: 7/30/2013 [Member] Silver | Points: 50

Up
0
Down

Resolved
Hi,

In the sql query that you have described, there are a total of three queries that get excuted.
Step1:
Intially, the inner most query gets excuted ie,
SELECT Top 1 Up.CHECKTIME
FROM sample Up
WHERE Up.CHECKTIME > Down.CHECKTIME
AND Up.CHECKTYPE = 'O'
ORDER BY Up.CHECKTIME ASC
meaning of this is,
sample is a table present in the database and Up is the aliaz given to the table sample table.
Now, performs orderby, and then it selects the top 1 record which satisfies the where condition

Step 2:
SELECT Down.CHECKTIME,
(
SELECT Top 1 Up.CHECKTIME
FROM sample Up
WHERE Up.CHECKTIME > Down.CHECKTIME
AND Up.CHECKTYPE = 'O'
ORDER BY Up.CHECKTIME ASC) UpTime
FROM sample Down
WHERE Down.CHECKTYPE = 'I' )

Again, here the table used is sample and the aliaz name given to it is Down.
From this it will select the CheckTime, UpTime where the checkType = "I"

Step 3:
Then executes the final query

Mark as answer if satisfied...........


Regards,
Shree M.



Kavya Shree Mandapalli

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 7/30/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
There is SQL syntax problem in your query.. That should be as follows:
"SELECT 

Min(CHECKTIME) StartTime
,UpTime EndTime
,SUM(DateDiff(MI,Min(CHECKTIME),UpTime)) OVER(PARTITION BY CAST(UpTime AS DATE) ) durationPerDay
FROM ( SELECT
Down.CHECKTIME
,( SELECT Top 1 Up.CHECKTIME
FROM sample Up
WHERE Up.CHECKTIME > Down.CHECKTIME
AND Up.CHECKTYPE = 'O'
ORDER BY Up.CHECKTIME ASC) UpTime
FROM sample Down
WHERE Down.CHECKTYPE = 'I'
) X
GROUP BY UpTime
"


-- Alternate is as follows
Try this...
";WITH cte as 

( SELECT
Down.CHECKTIME
,( SELECT Top 1 Up.CHECKTIME
FROM sample Up
WHERE Up.CHECKTIME > Down.CHECKTIME
AND Up.CHECKTYPE = 'O'
ORDER BY Up.CHECKTIME ASC
) UpTime
FROM sample Down
WHERE Down.CHECKTYPE = 'I' )
SELECT Min(CHECKTIME) StartTime
,UpTime EndTime
,SUM(DateDiff(MI,Min(CHECKTIME),UpTime)) OVER(PARTITION BY CAST(UpTime AS DATE) ) durationPerDay
FROM cte
GROUP BY UpTime "



Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 7/31/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
This piece of code contains the Addition logic....
SUM(DateDiff(MI,Min(CHECKTIME),UpTime)) OVER(PARTITION BY CAST(UpTime AS DATE) ) 

DateDiff(MI,Min(CHECKTIME),UpTime)
Gives the number of minutes between startTime(CheckInTime) and endTime(CheckOutTime)
SUM ( Above NumberOfMintues ) OVER( PARTITION BY CAST(UpTime AS DATE)  )

Sum() OVER(PARTITION BY ...) will give you the sum/addition of minutes grouping by Check IN dates... Here PARTITION BY is same as GROUP BY clause..
Finally you will get sum of minutes for each CHECK IN date.....

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Cibin on: 7/30/2013 [Member] Starter | Points: 25

Up
0
Down

From These answers am satisfied ,, But my Another question Is ,,, i want to calculate the total minutes from this in the order of date...


Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 7/30/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Do you have separate Date column?
what is the datatype of CHECKTIME?
If You have separate Date column in the table, just replace the following highlighted code
SELECT 

Min(CHECKTIME) StartTime
,UpTime EndTime
,SUM(DateDiff(MI,Min(CHECKTIME),UpTime))) OVER(PARTITION BY DateColumn )
FROM ...............

Otherwise put CAST( CheckTime AS DATE) instead of DateColumn ( in the case of CHECKTIME DATETIME and there is no separate Date Column)

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Cibin on: 7/30/2013 [Member] Starter | Points: 25

Up
0
Down

No there is no separate date column ,, only one column CHECKTIME,, its datatype is datetime2(0)

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Cibin on: 7/30/2013 [Member] Starter | Points: 25

Up
0
Down


In this code there is a extra bracket,,,, Can't execute it


Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 7/30/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
SELECT 

Min(CHECKTIME) StartTime
,UpTime EndTime
,SUM(DateDiff(MI,Min(CHECKTIME),UpTime)) OVER(PARTITION BY CAST(UpTime AS DATE) )
FROM ...............


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Cibin on: 7/30/2013 [Member] Starter | Points: 25

Up
0
Down

But one more doubt ,, if there is any need to save the 1st query values into a table....

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 7/30/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
I think no need to store into another table...
Why you asked this one.. Is there any specific reason for you? Please mark as answer for my earlier reply if you sorted out the problem

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kmandapalli on: 7/30/2013 [Member] Silver | Points: 25

Up
0
Down
Hi,

if at all you need to save then you write the query as below,
Insert into tableName
SELECT Top 1 Up.CHECKTIME
FROM sample Up
WHERE Up.CHECKTIME > Down.CHECKTIME
AND Up.CHECKTYPE = 'O'
ORDER BY Up.CHECKTIME ASC

Thats it...

Mark as answer if satisfied..............

Thank You,
Shree M.

Kavya Shree Mandapalli

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Cibin on: 7/30/2013 [Member] Starter | Points: 25

Up
0
Down

Mr.Chandu,

Yes one specific reason for that, Because When execute the Query it shows an error....
"
SELECT

Min(CHECKTIME) StartTime

,UpTime EndTime

,SUM(DateDiff(MI,Min(CHECKTIME),UpTime)) OVER(PARTITION BY CAST(UpTime AS DATE) )

FROM SELECT Min(CHECKTIME) StartTime, UpTime EndTime,
DateDiff(MI,Min(CHECKTIME),UpTime)
FROM (
SELECT Down.CHECKTIME,
(
SELECT Top 1 Up.CHECKTIME
FROM sample Up
WHERE Up.CHECKTIME > Down.CHECKTIME
AND Up.CHECKTYPE = 'O'
ORDER BY Up.CHECKTIME ASC) UpTime
FROM sample Down
WHERE Down.CHECKTYPE = 'I' ) X
GROUP BY UpTime
ORDER BY UpTime

"
ERROR :


Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'SELECT'.




Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 7/30/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi Cibin,
Check out the above solution.. ( made small modification to work proper)...
Mark As Answer if it helps you

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Cibin on: 7/31/2013 [Member] Starter | Points: 25

Up
0
Down

Thanks Chandu ,, Now its Working....
Thank You Very Much

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 7/31/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Welcome Cibin

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Cibin on: 7/31/2013 [Member] Starter | Points: 25

Up
0
Down


Hi, Chandu,

If you don't mind can you explain its working. Where is the addition takes place in this query.

Thanks In Advance

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Cibin on: 7/31/2013 [Member] Starter | Points: 25

Up
0
Down
Thanks Chandu,,
My goodness, i forget that statement SUM().....Now my doubts cleared ... Once again Thanking you... See you soon with another doubt.. k

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 7/31/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
You are very welcome

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Adlena on: 8/1/2013 [Member] Starter | Points: 25

Up
0
Down
i have just completed my PHp and Sql course, i am sure this forum goone be very helpful for me for further progress in my course work






spanishprograms [url=http://www.spanishprograms.com]http://www.spanishprograms.com [/url]
pass4sure - cissp training dumps [url=http://www.pass4sure.com/CISSP-Certifications.html]pass4sure - cissp training dumps [/url]
pass4sure ccie course [url=http://www.pass4sure.com/CCIE.html]pass4sure ccie course [/url]
pass4sure ccnp dumps [url=http://www.pass4sure.com/CCNP.html]pass4sure ccnp dumps [/url]
pass4sure cissp course [url=http://www.pass4sure.com/CISSP.html]pass4sure cissp course [/url]
pass4sure ccna training [url=http://www.pass4sure.com/CCNA.html]pass4sure ccna training [/url]


adlena

Cibin, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response