How to store the result of two queries in a datatable ?

Question

0.00/5 (No votes)

See more:

I have got two queries for the same access 2010 database.
the first query is to retrieve the EmpID and InTime by using the where condition as InOut="0" and teh second query to retrieve the EmpID and the OutTime with condition Where InOut="1".
the query 1 as follows:(which is assigned to a string variable)

SQL

SELECT  Format(Min(Trans.Dt),"MM/dd/yyyy HH:mm:ss") AS InTime, Trans.EmpID FROM Trans WHERE (Format(Trans.Dt,"Short Date") in ('8/25/2014') and Trans.InOut="0") GROUP BY Format(Trans.Dt,"Short Date"), Trans.EmpID;

The query 2 as follows:(which is assigned to a string variable)

SQL

SELECT  Format(Max(Trans.Dt),"MM/dd/yyyy HH:mm:ss") AS OutTime, Trans.EmpID FROM Trans WHERE (Format(Trans.Dt,"Short Date") in ('8/25/2014') and Trans.InOut="1") GROUP BY Format(Trans.Dt,"Short Date"), Trans.EmpID;

now I have created two dbReader respectively as OLEDataReader for executing the 2 queries.
The I need a datatable that displays EmpID ,InTime (from dbReader1 ie.,query1) and OutTime(from dbReader2 ie.,Query2).

Thanks in advance!!!

Posted 5-Sep-14 2:06am

sai sruthi

Updated 5-Sep-14 2:18am

_Asif_

v2

Add a Solution

Comments

MairajAhmed 5-Sep-14 8:16am

You can temp table in sql and return combined data in one query.

_Asif_ 5-Sep-14 8:21am

Why can't you tweak your SQL a bit and UNION both result? This way you have only one result which means one DataTable?

sai sruthi 5-Sep-14 8:28am

I Don't Know how to use UNION with these two queries and can you please help me?

sai sruthi 5-Sep-14 8:35am

The way OriginalGriff said was tried but the thing is I need InTime where InOut=0 and OutTime where InOut=1 but in that case I can give only one Where condition for InOut.

Sinisa Hajnal 5-Sep-14 8:37am

What if you have two ins or two outs for the same empID? What should be the output?

sai sruthi 5-Sep-14 9:31am

the output should have the min of InTime and Max of Outtime

3 solutions

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)

PhilLenoir · Answer 1 · 2014-09-05T02:38:00

The following gives you all the results with your in and out times in one query:

SELECT  Format(Min(Trans.Dt),"MM/dd/yyyy HH:mm:ss") AS InOutTime, Trans.EmpID FROM Trans WHERE (Format(Trans.Dt,"Short Date") in ('8/25/2014') and Trans.InOut="0") GROUP BY Format(Trans.Dt,"Short Date"), Trans.EmpID
UNION ALL
SELECT  Format(Max(Trans.Dt),"MM/dd/yyyy HH:mm:ss") AS InOutTime, Trans.EmpID FROM Trans WHERE (Format(Trans.Dt,"Short Date") in ('8/25/2014') and Trans.InOut="1") GROUP BY Format(Trans.Dt,"Short Date"), Trans.EmpID;

This one gives you the in and out time inseparate columns:

SELECT  Format(Min(Trans.Dt),"MM/dd/yyyy HH:mm:ss") AS InTime,'' AS OutTime Trans.EmpID FROM Trans WHERE (Format(Trans.Dt,"Short Date") in ('8/25/2014') and Trans.InOut="0") GROUP BY Format(Trans.Dt,"Short Date"), Trans.EmpID
UNION ALL
SELECT  Format(Max(Trans.Dt),'' As InTime, "MM/dd/yyyy HH:mm:ss") AS OutTime, Trans.EmpID FROM Trans WHERE (Format(Trans.Dt,"Short Date") in ('8/25/2014') and Trans.InOut="1") GROUP BY Format(Trans.Dt,"Short Date"), Trans.EmpID;

Sinisa Hajnal · Answer 2 · 2014-09-07T02:34:00

This should give you in and out times in single row for each EmpID

SQL

SELECT EmpID, MIN(InTime) as InTime, Max(OutTime) as OutTime
FROM
(
SELECT  Format(Min(Trans.Dt),"MM/dd/yyyy HH:mm:ss") AS InTime,'' AS OutTime Trans.EmpID FROM Trans WHERE (Format(Trans.Dt,"Short Date") in ('8/25/2014') and Trans.InOut="0") GROUP BY Format(Trans.Dt,"Short Date"), Trans.EmpID
UNION ALL
SELECT  Format(Max(Trans.Dt),'' As InTime, "MM/dd/yyyy HH:mm:ss") AS OutTime, Trans.EmpID FROM Trans WHERE (Format(Trans.Dt,"Short Date") in ('8/25/2014') and Trans.InOut="1") GROUP BY Format(Trans.Dt,"Short Date"), Trans.EmpID;
)
GROUP BY EmpID

If this solution solves your problem, please take some time to accept it so others can benefit. Thank you

Enrique J. Gonzalez Fernandez · Answer 3 · 2014-09-05T07:33:00

Solution 4

Try this

SELECT Format(Max(Trans.Dt),"MM/dd/yyyy HH:mm:ss") AS OutTime, Format(Min(Trans.Dt),"MM/dd/yyyy HH:mm:ss") AS InTime, Trans.EmpID FROM Trans  WHERE (Format(Trans.Dt,"Short Date") in ('8/25/2014') and (Trans.InOut="0" or Trans.InOut="1"))  GROUP BY Format(Trans.Dt,"Short Date"), Trans.EmpID;

Posted 5-Sep-14 7:33am

Enrique J. Gonzalez Fernandez

Updated 5-Sep-14 7:36am

v2

Comments

sai sruthi 8-Sep-14 0:58am

It works but I am getting the intime and outtime as same but really thanks for your solution

How to store the result of two queries in a datatable ?

3 solutions

Solution 2

Solution 5

Solution 4

Add your solution here

Preview 0