VBA MACRO I need sql query

[trprasad78]

Hi

I need sql query in VBA MACRO, data maintain in EXCEL

I will open output file and run the macro, data has to fetch from Master file and update in output file.

attached
MasterFile.xlsx
Output.xlsx = > Expected out as no.of days LOP (LOSS of pay)


Get LOSS of pay days.
Booking date" Should fall in given date (From and To date)
"ApprovedStatus" Consider only "Approval"
"PayTypeName" consider "UNPAID-ABS" & "WeekOFF-OF"

Please refer attached screen shot for sample filter.

I filtered
Booking Date = "1-07-2017" to "30-07-2017"
CreatedBy = "testtimeoff4"
ApprovalStatus = "Approved"
PayTypeName = "UNPAID-ABS" & "WEEKOFF-OF"

if you note 20th & 21st July = UNPAID-ABS
NEXT 2 days Weekly off (Weekoff-OF)
24TH July he took leave.

In this case we need consider 2 weekoff as Unpaid leave.

result should be
Testtimeoff4 = 5 days LOP (20 - 24th July 2017 )
( 8hrs = 1 day)

Please do the needful, if need any clarification ,please reply back.

 

[trprasad78]

If above explanation is not clear ? please ask me.
I stuck in that

 

[Chihiro]

What have you got so far? Do you know SQL at all?

From reading your description, you only want 5 days LOP returned for the employee mentioned?

If so, really, SQL isn't the best approach. As well, your description/criteria is somewhat disjointed and am not sure of the exact criteria of what's to be considered and what's not to be considered.

 

[p45cal]

In the attached is a new query at cell G1, a button and a data validation choice in cell D1.
The query will not work until you adjust the Connection String in that query to give the location and name of your Master excel file.

The idea is to choose a month in D1 and click the button. In this case your data only returns results for July; all other months don't return any rows at all.
The SQL statement goes something like this:

SELECT a.createdBy AS `Emp Name`, SUM(a.hours)/8 AS LOP FROM `Sheet1$` AS a WHERE ((a.payTypeName='WEEKOFF-OF') OR (a.payTypeName='UNPAID-ABS')) AND (a.approvalStatus='APPROVED') AND (a.bookingDate>={ts '2017-07-01 00:00:00'} And a.bookingDate<={ts '2017-07-31 00:00:00'}) GROUP BY a.createdBy ORDER BY a.createdBy

but the macro tweaks this.

 

[trprasad78]

In the attached is a new query at cell G1, a button and a data validation choice in cell D1.
The query will not work until you adjust the Connection String in that query to give the location and name of your Master excel file.

The idea is to choose a month in D1 and click the button. In this case your data only returns results for July; all other months don't return any rows at all.
The SQL statement goes something like this:

SELECT a.createdBy AS `Emp Name`, SUM(a.hours)/8 AS LOP FROM `Sheet1$` AS a WHERE ((a.payTypeName='WEEKOFF-OF') OR (a.payTypeName='UNPAID-ABS')) AND (a.approvalStatus='APPROVED') AND (a.bookingDate>={ts '2017-07-01 00:00:00'} And a.bookingDate<={ts '2017-07-31 00:00:00'}) GROUP BY a.createdBy ORDER BY a.createdBy

but the macro tweaks this.

Thank you so much .

testtimeoff4
LOP Should be 5

actual unpaid leave testtimeoff4 is 3 days (20th,21st & 24th July)
If unpaid leave fall before and after Weeklyoff, then weeklyoff also consider as Unpaid leave.

In this case.
He took leave
20th July 2017 = UNPAID-ABS
21st July 2017 = UNPAID-ABS
22nd July 2017 = WEEKOFF-OF
23rd July 2017 = WEEKOFF-OF
24th July 2017 = UNPAID-ABS

SO we need to consider "Testtimeoff4" LOP = 5
in case if he came on 24th july 2017 we consider LOP = 2

same way if he came on 21st july and 24th is unpaid leave then also we take 2 LOP (20th & 24th)

Hope I am clear.

Thank you so much for your time

 

[trprasad78]

What have you got so far? Do you know SQL at all?

From reading your description, you only want 5 days LOP returned for the employee mentioned?

If so, really, SQL isn't the best approach. As well, your description/criteria is somewhat disjointed and am not sure of the exact criteria of what's to be considered and what's not to be considered.

I had already few set of SQL query to calculate/fetch data from master file like maternity leave,paternity leave etc...
But i find difficult to calculate LOP

Please check my previous reply with @p45cal in that i explained detail.

thank you for your time.
please do the needful

 

[p45cal]

You said:

I filtered
Booking Date = "1-07-2017" to "30-07-2017"

Aren't there more days off than that?:


If you want more control over the dates then see attached in the vicinity of J1:K4.

 

[trprasad78]

You said:Aren't there more days off than that?:
View attachment 44727

If you want more control over the dates then see attached in the vicinity of J1:K4.

Which you had mentioned is weekOFF.

But i need LOP = Loss of pay = UNPAID-ABS = if unpaid leave fall in before and after WEEKOFF-OF , then WEEKOFF-OF also consider as LOP

In this case.
He took leave
20th July 2017 = UNPAID-ABS
21st July 2017 = UNPAID-ABS
22nd July 2017 = WEEKOFF-OF
23rd July 2017 = WEEKOFF-OF
24th July 2017 = UNPAID-ABS

SO we need to consider "Testtimeoff4" LOP = 5
in case if he came on 24th july 2017 we consider LOP = 2

same way if he came on 21st july and 24th is unpaid leave then also we take 2 LOP (20th & 24th)
refer the below screen shot

 

[p45cal]

I'm not clever enough to devise a SQL query to do that - although I'd be interested to see one.

 

[dan_l]

Maybe a guru level sql guy could do something with this. My approach might start with sql to fetch the right data but for adding logic to the output you might be best off with a recordset. You could pretty reliably get the data, loop through it and account for various conditions, and construct an output.

 

[p45cal]

you might be best off with a recordset. You could pretty reliably get the data, loop through it and account for various conditions, and construct an output.

That's a good idea.
We'd need very clear and comprehensive guidelines though.
I'm guessing that the UNPAID-ABS would have to occur immediately before and after the WEEKOFF-OF; do weekends matter/interfere? What happens, as is the case with the example, when one of the UNPAID-ABS is only 4 hours?;

the two 4 hour shifts looks like they might be morning and afternoon shifts. There's nothing to distinguish them, so if testtimeoff4 worked the morning shift, but took the afternoon shift as UNPAID-ABS that would still mean the prior WEEKOFF-OF would count towards LOP? It would show the same filtered list regardless of which shift he took off work.

 

[trprasad78]

ll

That's a good idea.
We'd need very clear and comprehensive guidelines though.
I'm guessing that the UNPAID-ABS would have to occur immediately before and after the WEEKOFF-OF; do weekends matter/interfere? What happens, as is the case with the example, when one of the UNPAID-ABS is only 4 hours?;
View attachment 44747
the two 4 hour shifts looks like they might be morning and afternoon shifts. There's nothing to distinguish them, so if testtimeoff4 worked the morning shift, but took the afternoon shift as UNPAID-ABS that would still mean the prior WEEKOFF-OF would count towards LOP? It would show the same filtered list regardless of which shift he took off work.

Good question, we not think about that ,tomorrow i check and update you.

 

[trprasad78]

That's a good idea.
We'd need very clear and comprehensive guidelines though.
I'm guessing that the UNPAID-ABS would have to occur immediately before and after the WEEKOFF-OF; do weekends matter/interfere? What happens, as is the case with the example, when one of the UNPAID-ABS is only 4 hours?;
View attachment 44747
the two 4 hour shifts looks like they might be morning and afternoon shifts. There's nothing to distinguish them, so if testtimeoff4 worked the morning shift, but took the afternoon shift as UNPAID-ABS that would still mean the prior WEEKOFF-OF would count towards LOP? It would show the same filtered list regardless of which shift he took off work.

Even if the peron come in half day we should not consider LOP .weekly as consider as weekoff.
If the person not come on full day then we consider WEEKOFF as UNPAID-ABS (LOP).

Thank you for your support.

 

[p45cal]

This is getting complicated.
If the data looks like this:

where there's only one 4 hour shift showing, can we assume, because of the absence of another 4 hour shift at work, that the person came in to work on the 24/7/2017? Or do we need to start looking at other payTypeNames to establish presence at work on a partcular day?

 

[trprasad78]

This is getting complicated.
If the data looks like this:
View attachment 44772
where there's only one 4 hour shift showing, can we assume, because of the absence of another 4 hour shift at work, that the person came in to work on the 24/7/2017? Or do we need to start looking at other payTypeNames to establish presence at work on a partcular day?

In my file 24/07/2017 2 entry. In case record missing please include.
Even if we have missing record it has to consider has LOP IF 24/07/2017 UNPAID-ABS FOR 4hours.

Default it has to check next 4 hours, if any one 4 hours not UNPAID-ABS (I mean worked half day), we should not consider WeekOFF as LOP.

Hope you understand, sorry for bad English.

Thank so much for your time.

 

[p45cal]

In my file 24/07/2017 2 entry. In case record missing please include.

Yes, I deliberately removed it to present you with a scenario.

Even if we have missing record it has to consider has LOP IF 24/07/2017 UNPAID-ABS FOR 4hours.

Understood. Except if…

check next 4 hours, if any one 4 hours not UNPAID-ABS (I mean worked half day), we should not consider WeekOFF as LOP.

This means the SQL query cannot filter on payTypeName, since we need to check for the existence of something which is neither UNPAID-ABS not WEEKOFF-OF on the same date.

This is getting complicated. Don't expect an answer today.

 

[trprasad78]

Yes, I deliberately removed it to present you with a scenario.
Understood. Except if…
This means the SQL query cannot filter on payTypeName, since we need to check for the existence of something which is neither UNPAID-ABS not WEEKOFF-OF on the same date.

This is getting complicated. Don't expect an answer today.

Thank you for your support ,will wait.

 

[trprasad78]

Hi @p45cal any good news....

 

[p45cal]

I'll have a few questions.
You appear to do this by the month. If the 1st and 2nd of the month are both WORKOFF-OF, and the 3rd of the month is UNPAID-ABS, do we need to look at the last day of the previous month to determine whether the WORKOFF-OF needs to be treated as LOP?
There'll be more questions.

 

[trprasad78]

I'll have a few questions.
You appear to do this by the month. If the 1st and 2nd of the month are both WORKOFF-OF, and the 3rd of the month is UNPAID-ABS, do we need to look at the last day of the previous month to determine whether the WORKOFF-OF needs to be treated as LOP?
There'll be more questions.

Yes we need to check the last day of the previous month to confirm the WEEKOFF-OF treat as LOP

 

[p45cal]

I propose NOT to consider WEEKOFF-OF entries of less than 8 hours per day to be considered as LOP.
I will not consider UNPAID-ABS entries less than 8 hours in a day the day before (or after) a contiguous sequence of WEEKOFF-OF entries as reason to consider those WEEKOFF-OF entries to be LOP.
This is because we cannot tell the difference between morning and afternoon shifts on those days (as discussed earlier).

Question:
Can Approved entries (ANY payNameType), for one person, for one day, add up to more than 8 hours?

 

[trprasad78]

I propose NOT to consider WEEKOFF-OF entries of less than 8 hours per day to be considered as LOP.
I will not consider UNPAID-ABS entries less than 8 hours in a day the day before (or after) a contiguous sequence of WEEKOFF-OF entries as reason to consider those WEEKOFF-OF entries to be LOP.
This is because we cannot tell the difference between morning and afternoon shifts on those days (as discussed earlier).

Question:
Can Approved entries (ANY payNameType), for one person, for one day, add up to more than 8 hours?

Never go beyond 8 hours a day. Good idea to calculate based on 8 hours.

Thank you so much for your time.

 

[trprasad78]

Hi @p45cal Hope you doing good.
at any chance it will complete tomorrow(Monday)?
sorry to push you, if you able to finish please let me know.

Thank you.

 

[trprasad78]

any other alternative way ? can't be done by VBA?

 

[Chihiro]

It can be done in VBA. Just that SQL query isn't well suited for something like this. As it will at least require multiple select and nested queries.

You can, use Scripting.Dictionary and other methods to bring in the data, but then I'm still confused about your criteria and what will be your final output.

It may be easier to set up formula/flag in source file and query based on that flag column.