0
0

Little help from you talented techies?


 invite response                
2020 Dec 15, 1:38pm   6,902 views  74 comments

by HeadSet   ➕follow (1)   💰tip   ignore  

Anyone on Patnet able to give me an idea on how to do the following?



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.

« First        Comments 35 - 74 of 74        Search these comments

35   Tenpoundbass   2020 Dec 16, 1:11pm  

HeadSet says
OK, I may be doing the dbo source (FROM) wrong:
HeadSet says
Oops, I see that I named the column "VehicleNumber: and not "Cab"


Ah you da man!

Nice how did it work out for you?
36   HeadSet   2020 Dec 16, 1:11pm  

Tenpoundbass says
Then gives you data that looks like this..



That is what I need! I just got to take out the errors I made copying your design.
37   Tenpoundbass   2020 Dec 16, 1:16pm  

HeadSet says
That is what I need! I just got to take out the errors I made copying your design.


It went exactly as I described it off the top of my head further back in the thread.

I can still do this shit, I'm just disgusted where I'm at now. I work with a bunch of know it all non technical Millennials that poke their finger in every pie.
This project would have taken a month, and it would have came down to my first thought in the meeting, that was shot down by everyone else with no stake in actually doing the task. Software is not a democracy, let the damn people who know their shit, do their damn job!(A rant directed at my current situation not you).
38   Tenpoundbass   2020 Dec 16, 1:19pm  

If you have to much trouble, run this script then import the csv into it .

Or use it to name your table and column names

CREATE TABLE [dbo].[cabdata](
[Cab] [varchar](50) NULL,
[LeaseStartDate] [datetime] NULL,
[LeaseEndDate] [datetime] NULL
) ON [PRIMARY]

GO
39   Tenpoundbass   2020 Dec 16, 1:33pm  

HeadSet says
OK, I may be doing the dbo source (FROM) wrong:


@Headset The code I first pasted Patnet strips out the * and put a blank while it made the text in between bold.
or if you look at the 4th and 3rd line to the bottom in your picture, it says "Select (removed by Patnet) From" change that to "Select (asterisks symbol) From" in both of those statements.
I repasted the code, in post 30, recopy it then change the values to match your picture.
Either rename your column vehiclenumber to cab or change it in the script.
40   HeadSet   2020 Dec 16, 1:34pm  

OK, I am exposing my stupid here (for all Patnet to see), but why does it show invalid column names when the columns are correct? (Red squiggly underlines)

41   Tenpoundbass   2020 Dec 16, 1:42pm  

Replace "Cab" in the rest of the script to "VehicleNumber"
42   HeadSet   2020 Dec 16, 1:45pm  

Got it working! Thanks!. Just hope I do not remind you of your co-workers.
43   Tenpoundbass   2020 Dec 16, 1:45pm  

Also look at your script on second line that starts with ",case" somehow the word "When" got swapped out for "b" put When back.
44   HeadSet   2020 Dec 16, 1:48pm  

To me, this pic is better looking than any of the babes on that NSFW thread

45   Tenpoundbass   2020 Dec 16, 1:57pm  

HeadSet says
Just hope I do not remind you of your co-workers.


NO not at all, it was just a thought I had. As lately I'm so disgusted because everyone and everything is in the way of me getting my job done. When a problem comes up, that I would solve and turn around in minutes, hours or days. By time everyone has stepped on it and poked their finger in the pie, it's three months later, and I can't bring my self to even do it. I made that comment, because I have no desire to code for the last 2 years because of it. I wasn't sure if I'm burned out and a Dinosaur(in programming terms) or if was because I'm just disgusted with where I work and the turkeys I work with. Everyone thinks I'm busy as hell, but in reality, I'm waiting for all of the committees, and political campaigns to run their course to derive at the same conclusion I came at in the initial meeting, or in my response to their initial request.

Thanks for giving me something to do, I actually felt like doing.
46   HeadSet   2020 Dec 16, 2:17pm  

Thanks for giving me something to do, I actually felt like doing.

Your welcome! Now I will look over your code in detail and transpose it to Access. I really appreciate this, it will save a ton of work.
48   richwicks   2020 Dec 16, 2:24pm  

Tenpoundbass says
As lately I'm so disgusted because everyone and everything is in the way of me getting my job done. When a problem comes up, that I would solve and turn around in minutes, hours or days. By time everyone has stepped on it and poked their finger in the pie, it's three months later, and I can't bring my self to even do it.


Change jobs.

I burn out, I'm fucking crispy fried now. Everything is a roadblock. New job, fun new interesting, old job, boring stale and I'm just doing maintenance.

I worked at a company that I replaced myself with a script with. I was like "fire me please, you don't need me, this script does my job now" - they wouldn't. I was literally getting up every day wanting to die rather than go to work sitting in front of a computer running data through a script. It was a nightmare.

Next time I'm off, I'm taking 6 months off to do all the crap I've been wanting to do, but have been blocked at my job from doing. I can't work on two entirely different projects at the same time - I need time to concentrate on what I want to work on. When I'm sick of that, not only will I have a cool product, I'll be sick of working on my cool product.

Unless I have something to figure out, I hate my job. I need to have a challenge. Perfecting code is no challenge. Tweaking code is no challenge.
49   Tenpoundbass   2020 Dec 16, 2:40pm  

richwicks says
Change jobs.

I burn out, I'm fucking crispy fried now. Everything is a roadblock. New job, fun new interesting, old job, boring stale and I'm just doing maintenance.


I want to go in business, either manufacturing or Restaurant. I only say Restaurant, solely due to the fact, that the Plandemic has wiped out a lot of competition that was in some of the most choice locations. There's a Diner near me that has been in my town since the early 50's. Jack's Diner.
Location is one of the most biggest drivers an eatery's success. I drove by it a few weeks ago, and saw a sign saying it was available. I got to thinking about it for a few days. Then drove by to get the info to inquire on it. I saw they had a website so I started there. It said it was fully functional rented as is ready to go, for a little over 5 grand a month. I thought that was outrageous. But I was talking to other people, who reckon pre Corona they had to be cranking out a couple grand a day. I drove by a few days ago, and it said, grand reopening coming soon. It's a great in and out location. Something like that could have worked. I wouldn't open a sandwich joint in an empty strip mall. This time last year, that's all you could find.

I think manufacturing will boom, if we have the right people in office, willing to bring our supply chain back. Maybe even farming,
50   Tenpoundbass   2020 Dec 16, 2:50pm  

Sounds like me a lot.

but I don't want to be in technology or programming. I'm tired of the pointlessness of it. No matter what you make and what you make it for. It will be obsolete in a few years. Libraries deprecated and OS they were written for end of life cycle.
I still drive by buildings I once put carpet or tile in(in my distant past career), its nice to see something that is still there. All of the cool programming shit I've done for companies over the last 30 years, I don't think one single one of them are still using any of it. Companies gone, or their whole infrastructure changed, changed ERP or CRMs. In fact, our company is in the process of moving everything into a cloud based drag and drop doohickey. They want me to help port programs I had to write the code for, because their business requirements and flow of the process, isn't something you find in drag and drop cloud based doohickeys. I'll help them the best I can, but the processes I wrote for them, wasn't even something they could port over to NetSuite, they tried for a year and half.

richwicks says
I was literally getting up every day wanting to die rather than go to work sitting in front of a computer running data through a script. It was a nightmare.

Next time I'm off, I'm taking 6 months off to do all the crap I've been wanting to do, but have been blocked at my job from doing. I can't work on two entirely different projects at the same time - I need time to concentrate on what I want to work on. When I'm sick of that, not only will I have a cool product, I'll be sick of working on my cool product.
51   richwicks   2020 Dec 16, 2:53pm  

HeadSet says
Thanks for giving me something to do, I actually felt like doing.

Your welcome! Now I will look over your code in detail and transpose it to Access. I really appreciate this, it will save a ton of work.


Problem solved? Am I off the hook?
52   richwicks   2020 Dec 16, 3:09pm  

Tenpoundbass says
but I don't want to be in technology or programming. I'm tired of the pointlessness of it. No matter what you make and what you make it for. It will be obsolete in a few years. Libraries deprecated and OS they were written for end of life cycle.


THIS is why I want time off.

30 years eh? What about bringing a bunch of stuff back? Remember ytalk? Remember when email ran on your computer? This cloud shit is just another way to censor and eavesdrop. It's another thin-client attempt. I'm disgusted by companies that say shit like "Google's terms of service say they will protect our data, and if they don't we can sue them". Yeah, good luck suing Google. Like when Dr. DOSS sued Microsoft, or Spyglass did, or Stacker did.

When people get burned, and they will get burned, it's going to swing back hard to private solutions in house. The trick is you have to drop a box in there, and it has to work.

None of this SLACK bullshit, no fucking Zoom, no Skype. P2P E2E encrypted, private, locally controlled systems. No GitHub, not even a remove server to run a website on. All inhouse, all completely controlled, totally monitored. Not hard to do, but time consuming to do.

We keep building newer, worse, less secure solutions that are not within our control. Corporations think this is cheaper and better. It is frustrating. When I was in college I worked on a DEC/VMS system that handled over 10,000 students doing work on it. We all had email, Usenet, could talk to anybody across the world. Unfettered access, no censorship, private accounts, later private webpages. I can do that on a raspberry pi now.

The absolute raw power we have today is mindblowing. The number of protocols that have been discarded is also mindblowing. There's a whole sea of "obsolete" tools to pick from. Imagine buying $100 box, plugging it in, and you have a server up and running that has all the capabilities of everything you access now. Why have a page on Facebook when you can just have a page on your box sitting at home? Can't censor that can you? Facebook won't "accidentally" change your settings so private information suddenly becomes public. No need for 3rd party trust.
53   HeadSet   2020 Dec 16, 4:45pm  

Tenpoundbass says
Then gives you data that looks like this..



TPB, I really appreciate the help, but there may be a tweak missing. For example Cab 3 was out from Dec 1 thru 5:

3,10/31/2020,11/6/2020
3,11/7/2020,11/13/2020
3,11/14/2020,11/20/2020
3,11/21/2020,11/27/2020
3,11/28/2020,12/5/2020 <<<<<<< (out from 28 Nov thru Dec 5th)

but the grid shows Cab 3 was out on Dec 5 only.

Also, Cab 3 above was out everyday in Nov, as were some other cabs, but none show on the grid to be out on any day that month past Nov 8th.

Hopefully this is an easy fix. Know that I appreciate you efforts so far, so can understand if you are tired of working on it. I know I cannot figure it out.
54   mell   2020 Dec 16, 6:00pm  

richwicks says
Tenpoundbass says
but I don't want to be in technology or programming. I'm tired of the pointlessness of it. No matter what you make and what you make it for. It will be obsolete in a few years. Libraries deprecated and OS they were written for end of life cycle.


THIS is why I want time off.

30 years eh? What about bringing a bunch of stuff back? Remember ytalk? Remember when email ran on your computer? This cloud shit is just another way to censor and eavesdrop. It's another thin-client attempt. I'm disgusted by companies that say shit like "Google's terms of service say they will protect our data, and if they don't we can sue them". Yeah, good luck suing Google. Like when Dr. DOSS sued Microsoft, or Spyglass did, or Stacker did.

When people get burned, and they will get burned, it's going to swing back hard to private solutions in house. The trick is you have to drop a b...


You should always build your own home control system, and if a company wants real safety and privacy they need to host their own HW and secure it. Of course it's not trivial since the cloud gives you automatic disaster fault-tolerance, but you would need to deploy and manage hardware at different regions. I don't think today's tech work is necessarily boring, the paradigm shifts are interesting, you can use lambdas or serverless container technology such as Fargate or you can use ECS. Of course every cloud provider has similar technology and there exist infrastructure-as-code tools such as Terraform which can leverage your coding and network skills onto any cloud provider. What's far more bothersome to many than the eternal shifting and refactoring which is just a sign of our rapidly changing times and technologies is the explosion in HR and political correctness and diversity, when proficient devs are laid off due to cost reasons but a "diversity" team is formed at the sane time. In that respect Covid actually was beneficial, I didn't mind the office for socializing with my favorite gang, but the intra-office PC and HR bullshit has significantly lessened since the remote era as it's hard to offend anyone from the confines of your home - just don't jerk off on a Zoom meeting like that clinton news network douchenozzle.
55   Tenpoundbass   2020 Dec 16, 6:09pm  

Note to @HeadSet find and replace ~ with * due to Patrick bold text trick, it removes them from the code. So I replaced them with ~



DECLARE @CabTemp TABLE(Cab INT, DayMarker INT, StartMonth VARCHAR(12));

INSERT INTO @CabTemp
Select [ScratchPad].[dbo].[cabdata].CAB, DatePart(Day, datetable.Date), DATENAME(MONTH, datetable.Date) From [ScratchPad].[dbo].[cabdata] cross join
(
select DATEADD(day,-(a.a + (10 ~ b.a) + (100 ~ c.a)),getdate()) AS Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a

cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b

cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) datetable
where datetable.Date between LeaseStartDate and LeaseEndDate
order by Cab, datetable.Date DESC

Select ~ from
(select Cab, Cab as CabCount, DayMarker, StartMonth from @CabTemp ) t
PIVOT (MAX(CabCount) FOR DayMarker
IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])) AS pvt
57   HeadSet   2020 Dec 16, 6:35pm  

Wow, that was fast. Works perfectly. Was this even much of a challenge?

I just had to modify
(select DATEADD(day,-(a.a + (10 b.a) + (100 c.a)),getdate()) AS Date
to put in the couple of * that Patnet took out.
58   Tenpoundbass   2020 Dec 16, 6:37pm  

HeadSet says
Wow, that was fast. Works perfectly. Was this even much of a challenge?


No it was easier than what I did before. I don't know why I was just marking the date range, starting from 1 for every record.
59   HeadSet   2020 Dec 16, 6:38pm  

Sweet!!!

60   HeadSet   2020 Dec 16, 6:47pm  

I am going through the original code, how did you get the data to sort by month and then cab? I see no "sort" statements, so is that a function of the Select that makes the pivot table?
61   Tenpoundbass   2020 Dec 16, 6:55pm  

Add this to the bottom

62   Tenpoundbass   2020 Dec 16, 7:02pm  

Oh you mean on the original code I posted, the results sorted. I don't know why sometimes shit sorts properly on its own, but other times you've got to explicitly set it.
63   HeadSet   2020 Dec 16, 7:06pm  

Tenpoundbass says
Oh you mean on the original code I posted, the results sorted. I don't know why sometimes shit sorts properly on its own, but other times you've got to explicitly set it.


Yes, I was wondering how the original code sorted. I guess it just knew what you wanted!
64   Blue   2020 Dec 18, 10:03am  

# Not sure if you are done otherwise here is one way to do it in python
# tested with both in/out xl and csv formats ex:
# python cabs.py --infile input.csv --outfile out.xlsx
# python cabs.py --infile input..xlsx --outfile out.csv
# make sure you have pandas installed in your system (recommend to use python 3)
# if not run the following command
# pip3 install pandas
# some time it may need additional modules like xlrd odfpy etc. in that case run
# pip3 install xlrd odfpy
# todo: it needs to add month-year on top of each month series
#
# added #s to the begining of each line to preserve the code indentation
# so need to remove them to get the code back
# if you use vim editor, try :1,$s/####/(type 4 spaces)/g
# let me know if you run into issues
#
#
import pandas as pd
import argparse


def process_cab_usage(infile=None, outfile=None):
####'''
####infile(str): csv or xl file with cab operating periods in the following format.
############cabM strt_dt end_dt
############...
############cabN strt_dt end_dt
####outfile(str): csv or xl file with cab operating periods in the following format.
############date dt1 dt2 ...
############cabM ''|* ...
############cabN ''|* ...
############note: '' - not used, '*' - used
####'''
####df = pd.read_csv(infile) if infile.endswith(
########('.csv', '.CSV')) else pd.read_excel(infile)
####cabs = sorted(set(df['Cab']))
####lease_start = pd.Series.min(df['LeaseStartDate'])
####lease_end = pd.Series.max(df['LeaseEndDate'])
####lease_range = pd.date_range(lease_start, lease_end)

##### mark the days for each cab in service
####dt_cab_used = {i: set() for i in cabs}
####for i in range(len(df)):
########for d in pd.date_range(df['LeaseStartDate'][i], df['LeaseEndDate'][i]):
############dt_cab_used[df['Cab'][i]].add(d)

##### create output data frame
####df_out = {'date': lease_range}
####for cab in cabs:
########df_out[f'cab{cab}'] = [''] len(lease_range) #add a star before len if you do not see it
####for idx, day in enumerate(lease_range):
########for cab in cabs:
############if day in dt_cab_used[cab]:
################df_out[f'cab{cab}'][idx] = '
' # add a star in single quotes if you do not see it
####df_out['date'] = lease_range.strftime('%d') # or '%m-%d'

####df_w = pd.DataFrame(df_out)
##### Transpose DataFrame
####df_w = df_w.transpose()
####if outfile.endswith(('.csv', '.CSV')):
########df_w.to_csv(outfile, index=True)
####else:
########writer = pd.ExcelWriter(outfile)
########df_w.to_excel(writer, index=True)
########writer.save()


if _name_ == "__main__": # make sure to have 2 under scores before and after the "name"
####parser = argparse.ArgumentParser()
####parser.add_argument('--infile', help='Cab log file')
####parser.add_argument('--outfile', help='Cab usage file')
####args = parser.parse_args()
####process_cab_usage(infile=args.infile, outfile=args.outfile)
65   Blue   2020 Dec 18, 10:06am  

well, tried best to preserve the format while posting. Follow the instruction/comments to restore the code back. Hope you can fix it with your IDE/editor.
66   Tenpoundbass   2020 Dec 18, 10:09am  

Nice job, why Pandas did you use Kubernetes?
67   Blue   2020 Dec 18, 11:56am  

Tenpoundbass says
Nice job, why Pandas did you use Kubernetes?

good question, kubernetes is a orchestration system. For data processing pandas has a lot of good features compare to alternative packages.
68   HeadSet   2020 Dec 18, 3:02pm  

Thanks, Blue

Python is a bit above my skill set, but I will still try to load and walk through that anyway. What TPB did works, and although I never could have written that code, I understand SQL enough to use and modify it.

And thanks again guys. Automating this task with 5 Fleets of cabs will save a ton of work.
69   richwicks   2020 Dec 22, 4:24pm  

To HeadSet


@HeadSet - you still need my solution, or have you got a solution?
70   HeadSet   2020 Dec 22, 4:51pm  

richwicks says
To HeadSet


@HeadSet - you still need my solution, or have you got a solution?


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.
71   richwicks   2020 Dec 22, 4:59pm  

HeadSet says
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  

richwicks says
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  

@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.
74   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?

« First        Comments 35 - 74 of 74        Search these comments

Please register to comment:

api   best comments   contact   latest images   memes   one year ago   random   suggestions