patrick.net

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.

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

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.

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.

Sweet!!!

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?

Add this to the bottom

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.

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!

# 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)

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.

Nice job, why Pandas did you use Kubernetes?

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.

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.

To HeadSet


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

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.

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.

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.

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

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