patrick.net
An Antidote to Corporate Media
1,197,060 comments by 14,096 users - Al_Sharpton_for_President, RayAmerica, Tenpoundbass online now
invite response« First « Previous Comments 71 - 74 of 74 Search these comments
71
richwicks
2020 Dec 22, 4:59pm
richwicks
2020 Dec 22, 4:59pm
HeadSet says
No problem. I have the solution basically done, but it requires multiple steps. I took your spreadsheet, turned it into a csv file, used perl to convert the dates to the number of days from the beginning of the year (1-365, sometimes 366 if it's a leap year) made an array for each cab that was filled info for the days it was leased out, and then stopped at converting it back to months/days - but that's what I had left to do, but it seemed you had a solution and it's frankly less messy than my solution.
It was limited to working on 1 year at a time.
Fun side project. Just wanted to make certain you were set, because it's not much work to complete it - but it's work to use it.
I am currently actively using TPB' s SQL solution, and it works great. I am also looking over what Blue produced. Therefore I no longer need a solution, but I appreciate that you would have put forth the effort.
No problem. I have the solution basically done, but it requires multiple steps. I took your spreadsheet, turned it into a csv file, used perl to convert the dates to the number of days from the beginning of the year (1-365, sometimes 366 if it's a leap year) made an array for each cab that was filled info for the days it was leased out, and then stopped at converting it back to months/days - but that's what I had left to do, but it seemed you had a solution and it's frankly less messy than my solution.
It was limited to working on 1 year at a time.
Fun side project. Just wanted to make certain you were set, because it's not much work to complete it - but it's work to use it.
72
Tenpoundbass
2020 Dec 22, 5:15pm
Tenpoundbass
2020 Dec 22, 5:15pm
richwicks says
Yes it was a fun exercise I would recommend anyone giving it a go, just to sharpen their skills. Date manipulation is always a mad skill, you don't need until you need it.
The drawback of the solution I created. It creates a list of past dates going three years back. Where as before I was getting the count of days in the range provided in the dates, and then had to create dates out of that range. I would have had to account for Leap year and other special considerations. So that script would have grown as bugs became apparent.
The second solution just generated a dynamic list of dates, so I didn't have to worry about it. As it allowed the SQL built in GetDate figure out those dates.
So if someone needs this script for booking a range of dates in the future, then they should go with my first solution.
It was limited to working on 1 year at a time.
Fun side project. Just wanted to make certain you were set, because it's not much work to complete it - but it's work to use it.
Yes it was a fun exercise I would recommend anyone giving it a go, just to sharpen their skills. Date manipulation is always a mad skill, you don't need until you need it.
The drawback of the solution I created. It creates a list of past dates going three years back. Where as before I was getting the count of days in the range provided in the dates, and then had to create dates out of that range. I would have had to account for Leap year and other special considerations. So that script would have grown as bugs became apparent.
The second solution just generated a dynamic list of dates, so I didn't have to worry about it. As it allowed the SQL built in GetDate figure out those dates.
So if someone needs this script for booking a range of dates in the future, then they should go with my first solution.
73
HeadSet
2022 Oct 11, 1:17pm
HeadSet
2022 Oct 11, 1:17pm
@Tenpoundbass
Just a quick question on that great SQL code you did for me almost 2 years ago:
It seems that the "where datetable.Date between LeaseStartDate and LeaseEndDate" does not include the LeaseEndDate itself. The SQL documentation says that a BETWEEN statement is inclusive of the last date, so I am not sure here. Maybe there is something else I am not seeing.
I really appreciate what you have done so far, and if you want to help here.
Just a quick question on that great SQL code you did for me almost 2 years ago:
It seems that the "where datetable.Date between LeaseStartDate and LeaseEndDate" does not include the LeaseEndDate itself. The SQL documentation says that a BETWEEN statement is inclusive of the last date, so I am not sure here. Maybe there is something else I am not seeing.
I really appreciate what you have done so far, and if you want to help here.
74
HeadSet
2022 Oct 15, 5:38pm
HeadSet
2022 Oct 15, 5:38pm
@Tenpoundbass
I got it sorted. Updated that WHERE line I pointed at using datepart function. Thanks again for writing that original code.
In your original code line (select DATEADD(day,-(a.a + (10 b.a) + (100 c.a)),getdate()) AS Date , what is the a.a and b.a and c.a? Is that a way to set a reference start date?
I got it sorted. Updated that WHERE line I pointed at using datepart function. Thanks again for writing that original code.
In your original code line (select DATEADD(day,-(a.a + (10 b.a) + (100 c.a)),getdate()) AS Date , what is the a.a and b.a and c.a? Is that a way to set a reference start date?
« First « Previous Comments 71 - 74 of 74 Search these comments
I cannot figure this out in Access tools/VBA or Excel tools/VBA. I really just need to know how many days each cab was leased out for the month, and I think this grid is the way to do it. For example, a cab that was leased out from 1-8 Nov and then from 8-15 Nov was leased for 15 days. Output is any way to show Cab 1 for 24 days, Cab 3 for 30 days, Cab 6 at 4 days, and so on. I do not want to double count dates that overlap.
Any ideas, to at least point me at what logic to use? Does not have to be Access or Excel.