sqlms-accessgroup-bypivottranspose
Sql TRANSFORM with calculation time in MS Access
I'm Trying to make Sql TRANSFORM with calculation time in column in and out.
I want the answer in 1 query and I don't want to use the function in ms access because I want to use the sql in vb.net
Please Guide me
Thanks
Table ABSEN
| DATE | TIME | INOUT | STATUS | LOCATION |
|---|---|---|---|---|
| 26-Aug-24 | 08:00:00 | IN | PRESENT | BOJONG |
| 26-Aug-24 | 17:00:00 | OUT | PRESENT | BOJONG |
| 27-Aug-24 | 08:00:00 | IN | PRESENT | BOJONG |
| 27-Aug-24 | 17:00:00 | OUT | PRESENT | BOJONG |
| 28-Aug-24 | 08:00:00 | IN | PRESENT | BOJONG |
| 28-Aug-24 | 17:00:00 | OUT | PRESENT | BOJONG |
| 29-Aug-24 | 08:00:00 | IN | PRESENT | BOJONG |
| 29-Aug-24 | 17:00:00 | OUT | PRESENT | BOJONG |
| 30-Aug-24 | 08:00:00 | IN | PRESENT | BOJONG |
| 30-Aug-24 | 17:00:00 | OUT | PRESENT | BOJONG |
| 31-Aug-24 | 08:20:00 | IN | PRESENT | BOJONG |
| 31-Aug-24 | 17:00:00 | OUT | PRESENT | BOJONG |
| 01-Sep-24 | 0 | IN | NOT PRESENT | BOJONG |
| 01-Sep-24 | 0 | OUT | NOT PRESENT | BOJONG |
Table MASTERDAYS
| LOCATION | DAY | DEFREST | DEFIN |
|---|---|---|---|
| BOJONG | Monday | 01:00:00 | 08:00:00 |
| BOJONG | Tuesday | 01:00:00 | 08:00:00 |
| BOJONG | Wednesday | 01:00:00 | 08:00:00 |
| BOJONG | Thursday | 01:00:00 | 08:00:00 |
| BOJONG | Friday | 01:30:00 | 08:00:00 |
| BOJONG | Saturday | 01:00:00 | 08:00:00 |
| BOJONG | Sunday | 00:00:00 | 00:00:00 |
TRANSFORM Max(ABSEN.Time) AS MaxOfTIME
SELECT ABSEN.Date AS [DATE],
Format(ABSEN.Date,'dddd') AS DAYS,
ABSEN.STATUS AS STATUS,
IIF(ABSEN.STATUS = 'NOT PRESENT', '',MASTERDAYS.DEFREST) AS DEFREST
FROM ABSEN INNER JOIN MASTERDAYS AS MASTERDAYS ON (MASTERDAYS.DAY = FORMAT(ABSEN.DATE,'dddd')) AND (MASTERDAYS.LOCATION = ABSEN.LOCATION)
GROUP BY ABSEN.Date,
Format(ABSEN.Date,'dddd'),
ABSEN.STATUS, MASTERDAYS.DEFREST
PIVOT ABSEN.INOUT In ('IN','OUT');
Result From Code
| DATE | DAYS | STATUS | DEFREST | IN | OUT |
|---|---|---|---|---|---|
| 26-Aug-24 | Monday | PRESENT | 01:00:00 | 08:00:00 | 17:00:00 |
| 27-Aug-24 | Tuesday | PRESENT | 01:00:00 | 08:00:00 | 17:00:00 |
| 28-Aug-24 | Wednesday | PRESENT | 01:00:00 | 08:00:00 | 17:00:00 |
| 29-Aug-24 | Thursday | PRESENT | 01:00:00 | 08:00:00 | 17:00:00 |
| 30-Aug-24 | Friday | PRESENT | 01:30:00 | 08:00:00 | 17:00:00 |
| 31-Aug-24 | Saturday | PRESENT | 01:30:00 | 08:00:00 | 17:00:00 |
| 01-Sep-24 | Sunday | NOT PRESENT | 0 | 0 |
Desired output
DUROFWORK = (ABSEN.Time IN - ABSEN.Time OUT - MASTERDAYS.DEFREST) LATEIN = (ABSEN.Time IN - MASTERDAYS.DEFIN)
For status NOT PRESENT I want to make blank for DEFREST,IN,OUT,DUROFWORK,LATEIN
| DATE | DAYS | STATUS | DEFREST | IN | OUT | DUROFWORK | LATEIN |
|---|---|---|---|---|---|---|---|
| 26-Aug-24 | Monday | PRESENT | 01:00:00 | 08:00:00 | 17:00:00 | 08:00:00 | |
| 27-Aug-24 | Tuesday | PRESENT | 01:00:00 | 08:00:00 | 17:00:00 | 08:00:00 | |
| 28-Aug-24 | Wednesday | PRESENT | 01:00:00 | 08:00:00 | 17:00:00 | 08:00:00 | |
| 29-Aug-24 | Thursday | PRESENT | 01:00:00 | 08:00:00 | 17:00:00 | 08:00:00 | |
| 30-Aug-24 | Friday | PRESENT | 01:30:00 | 08:00:00 | 17:00:00 | 07:30:00 | |
| 31-Aug-24 | Saturday | PRESENT | 01:30:00 | 08:00:00 | 17:00:00 | 06:10:00 | 00:20:00 |
| 01-Sep-24 | Sunday | NOT PRESENT |
Info data type
Public Class DTOABSENTRANSFORMREPORT
Public Property [DATE] As DateTime
Public Property DAYS As String
Public Property STATUS As String
Public Property DEFREST As String
Public Property TIME As String
Public Property [IN] As String
Public Property OUT As String
Public Property DUROFWORK As String
Public Property LATEIN As String
End Class
Update testing table as on 29-08-2024
Table ABSEN
| ID | DATE | TIME | INOUT | STATUS | LOCATION | OPTION |
|---|---|---|---|---|---|---|
| 1000 | 26-Aug-24 | 08:00:00 | IN | PRESENT | BOJONG | |
| 1000 | 26-Aug-24 | 17:00:00 | OUT | PRESENT | BOJONG | |
| 1000 | 27-Aug-24 | 08:00:00 | IN | PRESENT | BOJONG | |
| 1000 | 27-Aug-24 | 17:00:00 | OUT | PRESENT | BOJONG | |
| 1000 | 28-Aug-24 | 08:00:00 | IN | PRESENT | BOJONG | |
| 1000 | 28-Aug-24 | 17:00:00 | OUT | PRESENT | BOJONG | |
| 1000 | 29-Aug-24 | 08:00:00 | IN | PRESENT | BOJONG | |
| 1000 | 29-Aug-24 | 17:00:00 | OUT | PRESENT | BOJONG | |
| 1000 | 30-Aug-24 | 08:00:00 | IN | PRESENT | BOJONG | |
| 1000 | 30-Aug-24 | 17:00:00 | OUT | PRESENT | BOJONG | |
| 1000 | 31-Aug-24 | 08:20:00 | IN | PRESENT | BOJONG | |
| 1000 | 31-Aug-24 | 17:00:00 | OUT | PRESENT | BOJONG | |
| 1000 | 01-Sep-24 | 0 | IN | NOT PRESENT | BOJONG | |
| 1000 | 01-Sep-24 | 0 | OUT | NOT PRESENT | BOJONG | |
| 1001 | 26-Aug-24 | 08:00:00 | IN | PRESENT | OTHERS1 | |
| 1001 | 26-Aug-24 | 17:00:00 | OUT | PRESENT | OTHERS1 | |
| 1001 | 27-Aug-24 | 08:00:00 | IN | PRESENT | OTHERS1 | |
| 1001 | 27-Aug-24 | 17:00:00 | OUT | PRESENT | OTHERS1 | |
| 1001 | 28-Aug-24 | 08:00:00 | IN | PRESENT | OTHERS1 | |
| 1001 | 28-Aug-24 | 17:00:00 | OUT | PRESENT | OTHERS1 | |
| 1001 | 29-Aug-24 | 08:00:00 | IN | PRESENT | OTHERS1 | |
| 1001 | 29-Aug-24 | 17:00:00 | OUT | PRESENT | OTHERS1 | |
| 1001 | 30-Aug-24 | 08:00:00 | IN | PRESENT | OTHERS1 | |
| 1001 | 30-Aug-24 | 17:00:00 | OUT | PRESENT | OTHERS1 | |
| 1001 | 31-Aug-24 | 08:20:00 | IN | PRESENT | OTHERS1 | |
| 1001 | 31-Aug-24 | 17:00:00 | OUT | PRESENT | OTHERS1 | |
| 1001 | 01-Sep-24 | 0 | IN | NOT PRESENT | OTHERS1 | |
| 1001 | 01-Sep-24 | 0 | OUT | NOT PRESENT | OTHERS1 | |
| 1001 | 01-Sep-24 | 0 | OUT | NOT PRESENT | OTHERS1 | NO |
| 1002 | 26-Aug-24 | 08:00:00 | IN | PRESENT | BOJONG | |
| 1002 | 26-Aug-24 | 17:00:00 | OUT | PRESENT | BOJONG | |
| 1002 | 27-Aug-24 | 08:00:00 | IN | PRESENT | BOJONG | |
| 1002 | 27-Aug-24 | 17:00:00 | OUT | PRESENT | BOJONG | |
| 1002 | 28-Aug-24 | 08:00:00 | IN | PRESENT | BOJONG | |
| 1002 | 28-Aug-24 | 17:00:00 | OUT | PRESENT | BOJONG | |
| 1002 | 29-Aug-24 | 08:00:00 | IN | PRESENT | BOJONG | |
| 1002 | 29-Aug-24 | 17:00:00 | OUT | PRESENT | BOJONG | |
| 1002 | 30-Aug-24 | 08:00:00 | IN | PRESENT | BOJONG | |
| 1002 | 30-Aug-24 | 17:00:00 | OUT | PRESENT | BOJONG | |
| 1002 | 31-Aug-24 | 08:20:00 | IN | PRESENT | BOJONG | |
| 1002 | 31-Aug-24 | 17:00:00 | OUT | PRESENT | BOJONG | |
| 1002 | 01-Sep-24 | 0 | IN | NOT PRESENT | BOJONG | |
| 1002 | 01-Sep-24 | 0 | OUT | NOT PRESENT | BOJONG |
Table MASTERDAYS
| LOCATION | DAY | DEFREST | DEFIN |
|---|---|---|---|
| BOJONG | Monday | 01:00:00 | 08:00:00 |
| BOJONG | Tuesday | 01:00:00 | 08:00:00 |
| BOJONG | Wednesday | 01:00:00 | 08:00:00 |
| BOJONG | Thursday | 01:00:00 | 08:00:00 |
| BOJONG | Friday | 01:30:00 | 08:00:00 |
| BOJONG | Saturday | 01:00:00 | 08:00:00 |
| BOJONG | Sunday | 00:00:00 | 00:00:00 |
| OTHERS1 | Monday | 01:00:00 | 07:00:00 |
| OTHERS1 | Tuesday | 01:00:00 | 07:00:00 |
| OTHERS1 | Wednesday | 01:00:00 | 07:00:00 |
| OTHERS1 | Thursday | 01:00:00 | 07:00:00 |
| OTHERS1 | Friday | 01:30:00 | 07:00:00 |
| OTHERS1 | Saturday | 01:00:00 | 07:00:00 |
| OTHERS1 | Sunday | 00:00:00 | 07:00:00 |
Table MASTERID
| ID | NAMEID | LOCATION | POSITIONID |
|---|---|---|---|
| 1000 | A | BOJONG | STAFF |
| 1001 | B | OTHERS1 | STAFF |
| 1002 | C | BOJONG | STAFF |
SELECT
AIn.ID AS [ID]
, AIn.Date AS [DATE]
, MD.day AS [Day of Week]
, MI.NAMEID
, AIn.status
, IIF(AIn.STATUS = 'NOT PRESENT', '',MD.DEFREST) AS DEFREST
, IIF(AIn.STATUS = 'NOT PRESENT', '',AIn.TIME) AS [IN]
, IIF(AIn.STATUS = 'NOT PRESENT', '',AOut.TIME) AS OUT
, IIF(AIn.STATUS = 'NOT PRESENT', '',
dateadd("s", datediff("s", AIn.TIME, AOut.TIME)-datediff("s", 0, MD.DefRest), 0)) AS DUROFWORK
, IIF(cdate(AIn.[TIME]) > cdate(MD.DEFIN), format(dateadd("s", datediff("s", cdate(MD.DEFIN),
cdate(AIn.[TIME])),0),"hh:nn:ss"),'') AS LATEIN
FROM ((ABSEN AS AIn
INNER JOIN MASTERDAYS AS MD ON AIn.LOCATION = MD.LOCATION)
INNER JOIN MASTERID AS MI ON MI.ID=AIn.ID)
LEFT JOIN (SELECT *
FROM ABSEN AS AOut1
WHERE AOut1.INOUT='OUT') AS AOut ON (AOut.LOCATION=AIn.LOCATION) AND (AOut.DATE=AIn.DATE)
WHERE format(AIn.Date,"dddd")=MD.day
AND AIn.INOUT='IN' AND AIn.OPTION IS NULL AND AOut.OPTION IS NULL AND MI.POSITIONID='STAFF';
Result from code
Desired Result
| ID | DATE | DAYS | NAMEID | STATUS |DEFREST |IN |OUT |DUROFWORK|LATEIN |
| -----| -------- | -------- | --------| -------- |-------- |-------- |-------- |-------- |-------- |
| 1000 | 26-Aug-24| Monday | A | PRESENT | 01:00:00| 08:00:00| 17:00:00| 08:00:00| |
| 1000 | 27-Aug-24| Tuesday | A | PRESENT | 01:00:00| 08:00:00| 17:00:00| 08:00:00| |
| 1000 | 28-Aug-24| Wednesday| A | PRESENT | 01:00:00| 08:00:00| 17:00:00| 08:00:00| |
| 1000 | 29-Aug-24| Thursday | A | PRESENT | 01:00:00| 08:00:00| 17:00:00| 08:00:00| |
| 1000 | 30-Aug-24| Friday | A | PRESENT | 01:30:00| 08:00:00| 17:00:00| 07:30:00| |
| 1000 | 31-Aug-24| Saturday | A | PRESENT | 01:30:00| 08:00:00| 17:00:00| 06:10:00|00:20:00 |
| 1000 | 01-Sep-24| Sunday | A | NOT PRESENT| | | | | |
| 1001 | 26-Aug-24| Monday | B | PRESENT | 01:00:00| 08:00:00| 17:00:00| 08:00:00|01:00:00 |
| 1001 | 27-Aug-24| Tuesday | B | PRESENT | 01:00:00| 08:00:00| 17:00:00| 08:00:00|01:00:00 |
| 1001 | 28-Aug-24| Wednesday| B | PRESENT | 01:00:00| 08:00:00| 17:00:00| 08:00:00|01:00:00 |
| 1001 | 29-Aug-24| Thursday | B | PRESENT | 01:00:00| 08:00:00| 17:00:00| 08:00:00|01:00:00 |
| 1001 | 30-Aug-24| Friday | B | PRESENT | 01:30:00| 08:00:00| 17:00:00| 07:30:00|01:00:00 |
| 1001 | 31-Aug-24| Saturday | B | PRESENT | 01:30:00| 08:00:00| 17:00:00| 06:10:00|00:20:00 |
| 1001 | 01-Sep-24| Sunday | B | NOT PRESENT| | | | | |
| 1002 | 26-Aug-24| Monday | C | PRESENT | 01:00:00| 08:00:00| 17:00:00| 08:00:00| |
| 1002 | 27-Aug-24| Tuesday | C | PRESENT | 01:00:00| 08:00:00| 17:00:00| 08:00:00| |
| 1002 | 28-Aug-24| Wednesday| C | PRESENT | 01:00:00| 08:00:00| 17:00:00| 08:00:00| |
| 1002 | 29-Aug-24| Thursday | C | PRESENT | 01:00:00| 08:00:00| 17:00:00| 08:00:00| |
| 1002 | 30-Aug-24| Friday | C | PRESENT | 01:30:00| 08:00:00| 17:00:00| 07:30:00| |
| 1002 | 31-Aug-24| Saturday | C | PRESENT | 01:30:00| 08:00:00| 17:00:00| 06:10:00|00:20:00 |
| 1002 | 01-Sep-24| Sunday | C | NOT PRESENT| | | | | |
Problem Solved AND (AOut.ID=AIn.ID)
SELECT
AIn.ID AS [ID]
, AIn.Date AS [DATE]
, MD.day AS [Day of Week]
, MI.NAMEID
, AIn.status
, IIF(AIn.STATUS = 'NOT PRESENT', '',MD.DEFREST) AS DEFREST
, IIF(AIn.STATUS = 'NOT PRESENT', '',AIn.TIME) AS [IN]
, IIF(AIn.STATUS = 'NOT PRESENT', '',AOut.TIME) AS OUT
, IIF(AIn.STATUS = 'NOT PRESENT', '',
dateadd("s", datediff("s", AIn.TIME, AOut.TIME)-datediff("s", 0, MD.DefRest), 0)) AS DUROFWORK
, IIF(cdate(AIn.[TIME]) > cdate(MD.DEFIN), format(dateadd("s", datediff("s", cdate(MD.DEFIN),
cdate(AIn.[TIME])),0),"hh:nn:ss"),'') AS LATEIN
FROM ((ABSEN AS AIn
INNER JOIN MASTERDAYS AS MD ON AIn.LOCATION = MD.LOCATION)
INNER JOIN MASTERID AS MI ON MI.ID=AIn.ID)
LEFT JOIN (SELECT *
FROM ABSEN AS AOut1
WHERE AOut1.INOUT='OUT') AS AOut ON (AOut.LOCATION=AIn.LOCATION) AND (AOut.DATE=AIn.DATE) AND (AOut.ID=AIn.ID)
WHERE format(AIn.Date,"dddd")=MD.day
AND AIn.INOUT='IN' AND AIn.OPTION IS NULL AND AOut.OPTION IS NULL AND MI.POSITIONID='STAFF';
Solution
If you can give up using TRANSFORM; then you can use something like:
SELECT
AIn.Date AS [DATE]
, MD.day AS [Day of Week]
, MI.NAMEID
, AIn.status
, IIF(AIn.STATUS = 'NOT PRESENT', '',MD.DEFREST) AS DEFREST
, IIF(AIn.STATUS = 'NOT PRESENT', '',AIn.TIME) AS [IN]
, IIF(AIn.STATUS = 'NOT PRESENT', '',AOut.TIME) AS OUT
, IIF(AIn.STATUS = 'NOT PRESENT', '',
dateadd("s", datediff("s", AIn.TIME, AOut.TIME)-datediff("s", 0, MD.DefRest), 0)) AS DUROFWORK
, IIF(cdate(AIn.[TIME]) > cdate(MD.DEFIN), format(dateadd("s", datediff("s", cdate(MD.DEFIN),
cdate(AIn.[TIME])),0),"hh:nn:ss"),'') AS LATEIN
FROM ((ABSEN AS AIn
INNER JOIN MASTERDAYS AS MD ON AIn.LOCATION = MD.LOCATION)
INNER JOIN MASTERID AS MI ON MI.ID=AIn.ID)
LEFT JOIN (SELECT *
FROM ABSEN AS AOut1
WHERE AOut1.INOUT='OUT') AS AOut
ON (AOut.LOCATION=AIn.LOCATION)
AND (AOut.DATE=AIn.DATE)
AND (AOut.ID=AIn.ID)
WHERE format(AIn.Date,"dddd")=MD.day
AND AIn.INOUT='IN';
I assumed that your 'other' table NAM In my case I held the 'time-only' columns in 'ShortText' (you may need to adjust, if yours is different.
- Math.Sin() gives incorrect value
- How to run my python script when the sunOS is start booting
- Express-session: not resetting cookie expiration on each request
- Getting a stack overflow exception when normalizing a vector
- Edit default summary function in R gives error for multiple variables
- What was a For loop? Why isn't it needed in R?
- How to use download button in shiny and save results in various formats (csv, texte, pdf, spss...)?
- Why are there two assignment operators, `<-` and `->` in R?
- lm()$assign: what is it?
- How to get the value of list(...) in R and S functions
- Design matrix for MLM from library(lme4) with fixed and random effects
- how to generate elements not included in my sample
- Create a matrix with gradually changing values without a for loop
- Emacs ESS and S-plus ( S+ ) 8.1 compatability
- How to lag date-index in a time-series in R?
- Nonlinear regression in R / S
- Calling R from S-Plus?