1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA MACRO I need sql query

Discussion in 'VBA Macros' started by trprasad78, Aug 17, 2017.

  1. trprasad78

    trprasad78 Member

    Messages:
    148
    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.
    Pic1.jpg
    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.

    Attached Files:

  2. trprasad78

    trprasad78 Member

    Messages:
    148
    If above explanation is not clear ? please ask me.
    I stuck in that
  3. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,914
    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.
  4. p45cal

    p45cal Well-Known Member

    Messages:
    923
    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:
    Code (vb):
    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.

    Attached Files:

  5. trprasad78

    trprasad78 Member

    Messages:
    148
    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
  6. trprasad78

    trprasad78 Member

    Messages:
    148
    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
  7. p45cal

    p45cal Well-Known Member

    Messages:
    923
    You said:
    Aren't there more days off than that?:
    upload_2017-8-20_9-35-45.png

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

    Attached Files:

    Last edited: Aug 20, 2017
    trprasad78 likes this.
  8. trprasad78

    trprasad78 Member

    Messages:
    148
    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
    [​IMG]
  9. p45cal

    p45cal Well-Known Member

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

    dan_l Active Member

    Messages:
    727
    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.
  11. p45cal

    p45cal Well-Known Member

    Messages:
    923
    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?;
    upload_2017-8-20_16-55-32.png
    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 likes this.
  12. trprasad78

    trprasad78 Member

    Messages:
    148
    ll
    Good question, we not think about that ,tomorrow i check and update you.
  13. trprasad78

    trprasad78 Member

    Messages:
    148
    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.
  14. p45cal

    p45cal Well-Known Member

    Messages:
    923
    This is getting complicated.
    If the data looks like this:
    upload_2017-8-21_13-44-54.png
    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?
  15. trprasad78

    trprasad78 Member

    Messages:
    148
    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.
  16. p45cal

    p45cal Well-Known Member

    Messages:
    923
    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.
    trprasad78 likes this.
  17. trprasad78

    trprasad78 Member

    Messages:
    148
    Thank you for your support ,will wait.
  18. trprasad78

    trprasad78 Member

    Messages:
    148
    Hi @p45cal any good news....
  19. p45cal

    p45cal Well-Known Member

    Messages:
    923
    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 likes this.
  20. trprasad78

    trprasad78 Member

    Messages:
    148
    Yes we need to check the last day of the previous month to confirm the WEEKOFF-OF treat as LOP
  21. p45cal

    p45cal Well-Known Member

    Messages:
    923
    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?
  22. trprasad78

    trprasad78 Member

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

    Thank you so much for your time. :)
  23. trprasad78

    trprasad78 Member

    Messages:
    148
    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.
  24. trprasad78

    trprasad78 Member

    Messages:
    148
    any other alternative way ? can't be done by VBA?
    Last edited: Aug 30, 2017
  25. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,914
    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.

Share This Page