Click here to Skip to main content
14,868,514 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am trying to combine 4 different select statement from one table into one datatable using visual studio 2008 but I can only add them as new row. This is the code that I am doing:

What I have tried:

I already tried datatable.merge and it still add as new row. Please help!!

VB
Try
           conn1.Open()
           query1 = "SELECT USERID, CHECKDATE,MIN(CHECKTIME) AS [AM IN] FROM CHECKINOUT WHERE " _
           & "USERID = 562 AND CHECKTYPE = 'Check In' GROUP BY USERID,CHECKDATE"
           query2 = "SELECT MIN(CHECKTIME) AS [LUNCH OUT] FROM CHECKINOUT WHERE USERID = 562 " _
           & "AND CHECKTYPE = 'Break Out' GROUP BY USERID,CHECKDATE"
           query3 = "SELECT MAX(CHECKTIME) AS [LUNCH IN] FROM CHECKINOUT WHERE USERID = 562 " _
           & "AND CHECKTYPE = 'Break In' GROUP BY USERID, CHECKDATE"
           query4 = "SELECT MAX(CHECKTIME) AS [CHECK OUT] FROM CHECKINOUT WHERE USERID = 562 " _
           & "AND CHECKTYPE = 'Check Out' GROUP BY USERID, CHECKDATE"

           mainTable.Columns.Add("ID")
           mainTable.Columns.Add("DATE")
           mainTable.Columns.Add("CHECK IN")
           mainTable.Columns.Add("LUNCH OUT")
           mainTable.Columns.Add("LUNCH IN")
           mainTable.Columns.Add("CHECK OUT")

           cmd = New OleDbCommand(query1, conn1)
           Dim reader As OleDbDataReader
           reader = cmd.ExecuteReader

           While reader.Read
               Dim irow As DataRow = mainTable.NewRow
               irow("ID") = reader.Item(0)
               irow("DATE") = DateTime.Parse(reader.Item(1)).ToString("MM/dd/yyyy")
               irow("CHECK IN") = DateTime.Parse(reader.Item(2)).ToString("HH:mm tt")
               mainTable.Rows.Add(irow)
           End While

           reader.Close()
           cmd.Dispose()

           cmd2 = New OleDbCommand(query2, conn1)
           Dim reader2 As OleDbDataReader
           reader2 = cmd2.ExecuteReader

           While reader2.Read
               Dim irow As DataRow = mainTable.NewRow
               irow("LUNCH OUT") = DateTime.Parse(reader2.Item(0)).ToString("HH:mm tt")
               mainTable.Rows.Add(irow)
           End While

           conn1.Close()

       Catch ex As Exception
           MsgBox(ex.Message)
       End Try
       Return mainTable
Posted
Updated 29-Sep-19 2:36am

If you would like to run 4 queries, you have to do that in a loop. But(!)... i'd suggest to change a set of queries into one. It would be something like this:
SQL
SELECT DISTINCT USERID, CHECKDATE, 
    [CheckIn] = MIN(CASE WHEN CHECKTYPE = 'Check In' THEN CHECKTIME ELSE NULL END),
    [LunchIN] = MIN(CASE WHEN CHECKTYPE = 'Lunch In' THEN CHECKTIME ELSE NULL END),
    [LunchOut] = MAX(CASE WHEN CHECKTYPE = 'Lunch Out' THEN CHECKTIME ELSE NULL END),
    [CheckOut] = MAX(CASE WHEN CHECKTYPE = 'Check Out' THEN CHECKTIME ELSE NULL END)
FROM CHECKINOUT
WHERE WHERE USERID = 562
GROUP BY USERID, CHECKDATE


ANother way is to use PIVOT[^].
   
Comments
RaydenFox 28-Sep-19 2:19am
   
I tried your answer but I am having syntax error missing operator in [CheckIn] = MIN(CASE WHEN CHECKTYPE = 'Check In' THEN CHECKTIME ELSE NULL END)
RaydenFox 28-Sep-19 4:24am
   
Thanks for your suggestion and changed your given code to IIF statement and now it works , the only problem that i have right now is when null value is passed on variable that will be formatted to date it shows "Conversion of type DBNull to string is not allowed"
by the way for future references and might help others this is the code that I have done

SELECT CHECKINOUT.USERID,CHECKINOUT.CHECKDATE,
MIN(IIF(CHECKINOUT.CHECKTYPE='Check In',CHECKINOUT.CHECKTIME,NULL) ) AS 'AM IN',
MIN(IIF(CHECKINOUT.CHECKTYPE='Break Out',CHECKINOUT.CHECKTIME,NULL) ) AS 'LUNCH OUT',
MAX(IIF(CHECKINOUT.CHECKTYPE='Break In',CHECKINOUT.CHECKTIME,NULL) ) AS 'LUNCH IN',
MAX(IIF(CHECKINOUT.CHECKTYPE='Check Out',CHECKINOUT.CHECKTIME,NULL) ) AS 'PM OUT'
FROM CHECKINOUT WHERE CHECKINOUT.USERID = 563
GROUP BY CHECKINOUT.USERID, CHECKINOUT.CHECKDATE
Maciej Los 30-Sep-19 5:54am
   
You're very welcome.
Since all of your data has a common link, i.e., userID, you may wish to make an series of JOIN's on the table.
SQL
SELECT A.userID, A.checkDate, A.CheckIN, B.LunchIn, C.LunchOut, D.CheckOut
FROM A 
LEFT JOIN CHECKIN B ON A.userid = b.userID
LEFT JOIN CHECKIN C ON A.userid = c.userID
LEFT JOIN CHECKIN D ON A.userid = d.userID
Two notes:
- I am assuming you cannot checkout, lunchin, lunch out unless you first check in as you can't do any of these until your actually "get there"
- Using LEFT JOIN, NULL's are returned automatically where there is not match.
   
Comments
RaydenFox 28-Sep-19 2:21am
   
The 4 queries are from one table I only want to get the different time of each date by using MIN and MAX
Thanks for your suggestion and changed your given code to IIF statement and now it works , the only problem that i have right now is when null value is passed on variable that will be formatted to date it shows "Conversion of type DBNull to string is not allowed"
by the way for future references and might help others this is the code that I have done

SELECT CHECKINOUT.USERID,CHECKINOUT.CHECKDATE,
MIN(IIF(CHECKINOUT.CHECKTYPE='Check In',CHECKINOUT.CHECKTIME,NULL) ) AS 'AM IN',
MIN(IIF(CHECKINOUT.CHECKTYPE='Break Out',CHECKINOUT.CHECKTIME,NULL) ) AS 'LUNCH OUT',
MAX(IIF(CHECKINOUT.CHECKTYPE='Break In',CHECKINOUT.CHECKTIME,NULL) ) AS 'LUNCH IN',
MAX(IIF(CHECKINOUT.CHECKTYPE='Check Out',CHECKINOUT.CHECKTIME,NULL) ) AS 'PM OUT'
FROM CHECKINOUT WHERE CHECKINOUT.USERID = 563
GROUP BY CHECKINOUT.USERID, CHECKINOUT.CHECKDATE
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900