« prev   random   next »

1
0

Little help from you talented techies?


1,809 views  72 comments             share      

by HeadSet   $0.10 total tips   💰tip   follow   2020 Dec 15, 1:38pm  

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.

Comments 1 - 40 of 72   Next »    Last »

1   richwicks   2020 Dec 15, 1:43pm  

I'd convert it to CSV, then use perl (or some other script) to create a new CSV.

What data goes in the second grid? Oh I see - you said the number of days the cab was leased.

Do you have to do this for a ton of data, or just one page? If it's just one page and once, there's no point in scripting it.
2   HeadSet   2020 Dec 15, 1:53pm  

richwicks says
I'd convert it to CSV, then use perl (or some other script) to create a new CSV.

What data goes in the second grid? Oh I see - you said the number of days the cab was leased.

Do you have to do this for a ton of data, or just one page? If it's just one page and once, there's no point in scripting it.


I have to do this every month for insurance, We get a break on days the cabs are not leased. So every month I get a list of start and end dates and I am trying find a way to group and count days leased by cab.
3   theoakman   2020 Dec 15, 2:13pm  

I think there is a transpose option in cut and paste in Excel
4   Tenpoundbass   2020 Dec 15, 2:17pm  

You need to transpose it to a Pivot table.

SELECT * FROM
(
SELECT DynamicData.Row_ID, DynamicData.RefID, DynamicData.DynamicFieldValue, DynamicFields.DynFieldName
FROM DynamicData INNER JOIN
DynamicFields ON DynamicData.DynamicFieldID = DynamicFields.DynamicFieldID where DynamicFields.DynamicTableID = 2
)t
PIVOT (Max(DynamicFieldValue) FOR DynFieldName
IN ([CampaignID],[Notes],[FirstName],[LastName],[Address],[City],[State],[Zip])) AS pvt


COnverts data like this

DynamicDataID DynamicFieldID DynamicFieldValue RefID Row_ID
28 4 Jack 0 C8AA0F84-9D66-4F9A-8CC6-086D5069122D
29 5 Wilson 0 C8AA0F84-9D66-4F9A-8CC6-086D5069122D
30 6 2434 Dixie Highway 0 C8AA0F84-9D66-4F9A-8CC6-086D5069122D
31 7 Hollywood 0 C8AA0F84-9D66-4F9A-8CC6-086D5069122D
32 8 Alabama 0 C8AA0F84-9D66-4F9A-8CC6-086D5069122D
33 9 34234 0 C8AA0F84-9D66-4F9A-8CC6-086D5069122D
234080 4 Bob 0 92C8B83A-3FF8-4190-88FB-CCA85341F26D
234081 5 Montgomery 0 92C8B83A-3FF8-4190-88FB-CCA85341F26D
234082 6 2342 Grant st 0 92C8B83A-3FF8-4190-88FB-CCA85341F26D
234083 7 Coral Springs 0 92C8B83A-3FF8-4190-88FB-CCA85341F26D
234084 8 FL 0 92C8B83A-3FF8-4190-88FB-CCA85341F26D
234085 9 33020 0 92C8B83A-3FF8-4190-88FB-CCA85341F26D
234086 10 124 0 92C8B83A-3FF8-4190-88FB-CCA85341F26D
234087 4 Frank 0 385B8877-73AF-4D02-847A-91A437990212
234088 5 New Guy 0 385B8877-73AF-4D02-847A-91A437990212
234089 6 2342 Hollywood blvd 0 385B8877-73AF-4D02-847A-91A437990212
234090 7 Hollywood 0 385B8877-73AF-4D02-847A-91A437990212
234091 8 Florida 0 385B8877-73AF-4D02-847A-91A437990212
234092 9 33020 0 385B8877-73AF-4D02-847A-91A437990212

To This

Row_ID RefID CampaignID Notes FirstName LastName Address City State Zip
735DD8D9-9DD9-4C0C-8F7C-2BFA3205EF7E 0 2332 NULL Joe Blow huscow thoo California 234232
3C82D341-79D9-4511-9855-4B45239A7236 0 5434 NULL Jack Wilson 2423 N 8th street Hollywood Florida 34213
4783E7DD-5FEC-4958-A507-0843B1DDAC25 0 4 NULL Billy Bass 1233 Hillbilly way Davie Florida 32345
C4A4E475-0C65-44E6-A8A7-DB649072F2C8 0 234322 NULL Andy Gripe 432 Adams street Hollywood Florida 33020
D6C9859E-5A31-4685-A58B-E499425AC145 0 None NULL Thomas Hickory 3453 Fullertone road Harbor Alaska 43234
5   Tenpoundbass   2020 Dec 15, 2:29pm  

Where are the pivot values coming from are those Days of the week?
And if so, do you need it broke down for each month?

If so you could do Union queries one for each month.
Or you could do a roll up query to group it by the month.
6   HeadSet   2020 Dec 15, 2:45pm  

Tenpoundbass says
Where are the pivot values coming from are those Days of the week?
And if so, do you need it broke down for each month?

If so you could do Union queries one for each month.
Or you could do a roll up query to group it by the month.


I am not looking at Days of the Week per se, I just need to extract the number of days the cab was on a lease that month. So I need to just count the days between the date ranges and add them up, sorted by cab. What makes it challenging is:

1. Some lease date ranges overlap. For example, Cab 7 is leased from 1-8 Nov, then leased from 8-15 Nov, That is 15 days because the 2 periods share a day. But for the same date range amount that does not overlap, for example Cab 7 leased from 1-8 Nov, then 10-17 Nov would be 16 days. Therefore, I cannot just subtract beginning date from end date.

2. Lease date ranges do not all start and end during the calendar month, So for Cab 9 that leased Oct 29 to Nov 5, that would be 5 days in Nov.
7   Tenpoundbass   2020 Dec 15, 2:53pm  

So those numbers at the top are the date range between the two dates in the data?

HeadSet says
2. Lease date ranges do not all start and end during the calendar month, So for Cab 9 that leased Oct 29 to Nov 5, that would be 5 days in Nov.


Ah so the October grid would have the 3 days out of the 8?
8   HeadSet   2020 Dec 15, 2:58pm  

Tenpoundbass says
So those numbers at the top are the date range between the two dates in the data?

HeadSet says
2. Lease date ranges do not all start and end during the calendar month, So for Cab 9 that leased Oct 29 to Nov 5, that would be 5 days in Nov.


Ah so the October grid would have the 3 days out of the 8?


Yes, the numbers in the grid are the dates in the date range.

Yes the Oct grid would have 3 days out of the 8.

Putting it on a grid is my feeble attempt to solve the overlap and month issue. I am not looking for a grid per se.
9   Tenpoundbass   2020 Dec 15, 3:03pm  

Then you would want to first output that data into another table first with the Daterange(date1 and date2) Days, based on the Datepart(month) on each side. so if Start date is different than the End Date. I would get the days for October start date to the last day in October, and November then get the days in that date range to the start of the month for the end date. I would write a C# app for something like this. Sure it's over kill but I can do it, and have the tools, and by time I wrote all of the temp tables, variables, and Common table expressions in SQL, I could have wrote less code in C# and did it quicker.
10   HeadSet   2020 Dec 15, 3:22pm  

Tenpoundbass says
Then you would want to first output that data into another table first with the Daterange(date1 and date2) Days, based on the Datepart(month) on each side. so if Start date is different than the End Date. I would get the days for October start date to the last day in October, and November then get the days in that date range to the start of the month for the end date. I would write a C# app for something like this. Sure it's over kill but I can do it, and have the tools, and by time I wrote all of the temp tables, variables, and Common table expressions in SQL, I could have wrote less code in C# and did it quicker.


Wow, I have nowhere near that talent. I can do the extract of days using the DateRange/Datepart type functions in Excel, but I cannot figure out how cannot group and total those dates by cab. I could import the table into Access, but that would still double count overlapping dates.

What would the output of what you are talking about look like, would it be something like:

Cab 1 - 21
Cab 2 - 15
Cab 4 - 30

and so on?
11   richwicks   2020 Dec 15, 3:27pm  

HeadSet says
I have to do this every month for insurance, We get a break on days the cabs are not leased. So every month I get a list of start and end dates and I am trying find a way to group and count days leased by cab.


If you don't find a solution by the end of the week, I'll write a script for you. I'll need an actual example spreadsheet through - doesn't have to contain real data. I'll arrange a way for you to contact me.
12   HeadSet   2020 Dec 15, 3:31pm  

@richwicks

Thanks! I am real curious in seeing how that is done, especially the logic.
13   richwicks   2020 Dec 15, 3:43pm  

HeadSet says
@richwicks

Thanks! I am real curious in seeing how that is done, especially the logic.


OK, I'll do it anyway, but you'll have to wait until the weekend.

https://samoyed.dynu.net/~headset/cgi-bin/uploadFile.cgi

You can upload files there, but you will not be able to retrieve or delete files from there. Please don't upload anything illegal or even anything in poor taste.

If you know any coding, I'll just post the code with logic. Not making any promises either, I might be very busy this weekend. Another thing I realized, if the lease date starts on October but ends in November, should the number of days be how long the cab was leased out in total, or just for that month?
14   HeadSet   2020 Dec 15, 4:57pm  

Thanks again, richwicks.

I uploaded the excel file with the dates ranges for Nov (includes leases that start in Oct and those that start in Nov and end in Dec). And believe me, I would not upload anything illegal or in poor taste. If the lease date starts in Oct, and ends in Nov, I would only want the actual days in Nov for the November report.
15   richwicks   2020 Dec 15, 5:29pm  

HeadSet says
Thanks again, richwicks.

I uploaded the excel file with the dates ranges for Nov (includes leases that start in Oct and those that start in Nov and end in Dec). And believe me, I would not upload anything illegal or in poor taste. If the lease date starts in Oct, and ends in Nov, I would only want the actual days in Nov for the November report.


I am sorry! Can you upload that again?

I set the wrong permissions on the directory. I noticed the file wasn't there, debugged, and realized I should be better about doing proper setup. The file did "upload" but didn't make it to my disk. Sorry about that.
16   richwicks   2020 Dec 15, 6:13pm  

To HeadSet


OK, got it that time. I see it's in xlsx - I may be able to read it directly rather doing conversion on it. Earliest ETA is Monday of next week.
17   HeadSet   2020 Dec 15, 6:18pm  

richwicks says
To HeadSet


OK, got it that time. I see it's in xlsx - I may be able to read it directly rather doing conversion on it. Earliest ETA is Monday of next week.


Thanks again, I really appreciate this.
18   Tenpoundbass   2020 Dec 15, 6:36pm  

What happens when the months go beyond the next, can there be a record with 12 month span or more?
Would each month for that cab have the 20 or 30 or 28 days checked with a *
Also what about the gap between years, especially the two joining months?
19   HeadSet   2020 Dec 15, 7:11pm  

The maximum lease in a record is one week (which is 8 calendar days since the day the cab is taken out is included as a day driven).

The idea is to count the days driven per cab during that calendar month, so each cab would have the *.

It is by month, so it does not matter if that month is Jan or Dec.
20   richwicks   2020 Dec 15, 7:25pm  

HeadSet says
richwicks says
To HeadSet


OK, got it that time. I see it's in xlsx - I may be able to read it directly rather doing conversion on it. Earliest ETA is Monday of next week.


Thanks again, I really appreciate this.


No problem, but somebody may find a better solution than I do before I complete the little puzzle.

I've disabled the webpage by the way. I'm rather paranoid about leaving access open to my server. Not unduly either, I can see attempts at gaining access is constant. Most of them are just bots.

I know perl, and a bunch of other languages, but I rarely work with spreadsheets. When all you have is a hammer, everything looks like a nail.

However, the only solution I see is parsing all the data, and filling in an array as you go. I will produce a csv (comma separated value) file and that will be my input as well. You can export to a csv from excel, but you lose equations and just end up with data. Comma is the most used delimiter, but you can use other ones as well [:|.$%] - something that doesn't show up in the data if you have commas in your data.

If nobody comes up with a better solution, I'll give you the perl script. You run under windows, so you'll have to install a perl interpreter to use it yourself. There's a few.

Many people consider perl to be obsolete, it's just out of fashion - it's an EXTREMELY powerful language for data processing, but it's also quite an ugly language and I don't consider it a language that can be "bug free" because nobody fully understands the capability of the language. I'm good with perl, but there's an infinite amount of ways to do the same thing. If I read my friend's perl code, I can easily get lost, and it's the same for him to read mine.

I'll show you my steps so you can duplicate in the event nobody comes up with a better solution. If you're familiar with coding, I can get you started with perl. I used to hate perl, but it's far more powerful than C for doing some things, like your problem.
21   HeadSet   2020 Dec 15, 7:44pm  

I am familiar with VBA used in Access, so in the meantime I will install perl and check out a few tutorials.
22   richwicks   2020 Dec 15, 10:20pm  

HeadSet says
I am familiar with VBA used in Access, so in the meantime I will install perl and check out a few tutorials.


Well, I don't know dick about either of those, so I don't have any idea what kind of learning curve it is to do perl for you. The most difficult thing with perl is regular expressions, which I will make use of to solve your problem. I'm also going to make many assumptions in the data as well, which is poor coding but if the user is aware of it, it's fine.

In any case, I'll annotate (comment) the code well and explain what I'm doing. Your problem isn't too difficult, but I have to make use of some libraries to get the lengths of a month, the number of days in a month, and I'm not going to be efficient doing it, brute force. It will be simple code, but not efficient.

Perl is largely designed to be throwaway scripts. You write a script, solve a problem, and then pack it away or, frequently, just delete it. The entire thing is a big hack language to solve problems. It's very terse for the solutions it fits.

Again, earliest ETA is Monday, next week.
23   Blue   2020 Dec 16, 2:29am  

Can you post the input file in CSV format. At least some chunk like100 lines of it is too large, for reasonable testing.
24   WookieMan   2020 Dec 16, 3:05am  

22 Comments and this didn't turn political....? lol. Good on you guys for trying to help out a fellow Patnet user. Glad to see all hope isn't lost for the human race.
25   richwicks   2020 Dec 16, 6:34am  

Blue says
Can you post the input file in CSV format. At least some chunk like100 lines of it is too large, for reasonable testing.


I'll give you that data - as CSV but the file has a lot of entries. I could only post a portion of it, this is about 1/4 of the file but gives you enough to work off from I think. Let's see if you come up with a better solution than I do.

BTW @HeadSet - ever heard of LibreOffice? https://www.libreoffice.org/ Microsoft has become a real asshole with MS Office, LibreOffice is a similar office suite, but it's GPL - basically no cost, but also no support. I used LibreOffice to read your file. Many people think it's a bit more primitive than MS Office, but also many people find it adequate for general use. You could save your company a lot of money IF it can be used as a replacement for MS Office. You may not be able to though, so you have to play with it for a bit before thinking of making the switch.

Cab,LeaseStartDate,LeaseEndDate
1,10/26/2020,11/2/2020
1,11/2/2020,11/9/2020
1,11/9/2020,11/16/2020
1,11/16/2020,11/23/2020
1,11/23/2020,11/30/2020
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
5,10/27/2020,11/3/2020
5,11/3/2020,11/10/2020
5,11/10/2020,11/17/2020
5,11/17/2020,11/24/2020
5,11/24/2020,12/1/2020
6,10/28/2020,11/4/2020
10,10/29/2020,10/30/2020
10,10/30/2020,10/31/2020
10,10/31/2020,10/31/2020
10,10/31/2020,11/1/2020
10,11/2/2020,11/9/2020
10,11/7/2020,11/14/2020
10,11/14/2020,11/16/2020
10,11/16/2020,11/23/2020
10,11/23/2020,11/24/2020
10,11/25/2020,11/25/2020
10,11/27/2020,11/28/2020
10,11/28/2020,11/28/2020
10,11/28/2020,11/29/2020
12,10/27/2020,11/2/2020
12,11/3/2020,11/10/2020
12,11/10/2020,11/17/2020
12,11/17/2020,11/24/2020
12,11/24/2020,12/1/2020
16,10/26/2020,11/2/2020
16,11/2/2020,11/9/2020
16,11/9/2020,11/16/2020
16,11/16/2020,11/23/2020
16,11/23/2020,11/30/2020
17,10/27/2020,11/3/2020
17,11/3/2020,11/9/2020
17,11/10/2020,11/17/2020
17,11/17/2020,11/24/2020
17,11/25/2020,11/26/2020
18,11/18/2020,11/21/2020
18,11/23/2020,11/30/2020
19,10/27/2020,11/3/2020
19,11/3/2020,11/9/2020
19,11/10/2020,11/17/2020
19,11/17/2020,11/24/2020
19,11/24/2020,12/1/2020
22,10/27/2020,11/2/2020
22,11/3/2020,11/10/2020
22,11/10/2020,11/16/2020
22,11/17/2020,11/24/2020
22,11/24/2020,11/30/2020
23,11/1/2020,11/1/2020
23,11/2/2020,11/2/2020
23,11/2/2020,11/9/2020
23,11/9/2020,11/16/2020
23,11/16/2020,11/23/2020
23,11/23/2020,11/30/2020
24,10/26/2020,11/2/2020
24,11/2/2020,11/9/2020
24,11/9/2020,11/16/2020
24,11/16/2020,11/23/2020
24,11/23/2020,11/30/2020
26,10/29/2020,11/5/2020
26,11/5/2020,11/12/2020
26,11/12/2020,11/19/2020
26,11/19/2020,11/26/2020
26,11/26/2020,12/3/2020
27,10/29/2020,11/5/2020
27,11/5/2020,11/12/2020
27,11/12/2020,11/19/2020
27,11/19/2020,11/26/2020
27,11/26/2020,12/3/2020
28,10/26/2020,11/2/2020
28,11/2/2020,11/9/2020
28,11/9/2020,11/16/2020
28,11/16/2020,11/23/2020
28,11/23/2020,11/24/2020
32,10/29/2020,11/3/2020
32,11/5/2020,11/11/2020
32,11/11/2020,11/17/2020
32,11/19/2020,11/25/2020
32,11/26/2020,12/3/2020
33,10/27/2020,11/3/2020
33,11/3/2020,11/10/2020
33,11/10/2020,11/17/2020
33,11/17/2020,11/24/2020
33,11/24/2020,12/1/2020
36,10/27/2020,10/27/2020
36,11/2/2020,11/2/2020
36,11/7/2020,11/11/2020
36,11/11/2020,11/18/2020
36,11/18/2020,11/25/2020
36,11/27/2020,11/27/2020
36,11/28/2020,11/29/2020
36,11/30/2020,11/30/2020
36,11/30/2020,12/3/2020
36,12/3/2020,12/3/2020
36,12/4/2020,12/5/2020
36,12/4/2020,12/5/2020
36,12/5/2020,12/5/2020
37,10/31/2020,11/7/2020
37,11/7/2020,11/14/2020
37,11/14/2020,11/21/2020
37,11/21/2020,11/28/2020
37,11/28/2020,12/5/2020
38,10/27/2020,11/3/2020
38,11/3/2020,11/10/2020
38,11/10/2020,11/17/2020
38,11/17/2020,11/24/2020
38,11/24/2020,12/1/2020
40,11/10/2020,11/16/2020
40,11/16/2020,11/23/2020
40,11/23/2020,11/30/2020
41,10/29/2020,11/5/2020
41,11/5/2020,11/12/2020
41,11/12/2020,11/19/2020
41,11/19/2020,11/26/2020
41,11/26/2020,12/3/2020
42,10/29/2020,11/5/2020
42,11/5/2020,11/12/2020
42,11/12/2020,11/19/2020
42,11/19/2020,11/26/2020
42,11/26/2020,12/3/2020
45,10/26/2020,10/30/2020
45,11/2/2020,11/7/2020
45,11/9/2020,11/14/2020
45,11/16/2020,11/21/2020
45,11/23/2020,11/28/2020
45,11/30/2020,12/5/2020
46,10/29/2020,11/5/2020
46,11/6/2020,11/13/2020
46,11/14/2020,11/18/2020
46,11/18/2020,11/18/2020
46,11/19/2020,11/19/2020
46,11/19/2020,11/24/2020
46,11/26/2020,12/3/2020
46,12/3/2020,12/4/2020
46,12/4/2020,12/4/2020
46,12/4/2020,12/5/2020
46,12/5/2020,12/5/2020
46,12/5/2020,12/6/2020
46,12/6/2020,12/6/2020
49,10/30/2020,10/30/2020
49,10/31/2020,10/31/2020
49,10/31/2020,11/1/2020
49,11/2/2020,11/2/2020
49,11/2/2020,11/3/2020
49,11/3/2020,11/3/2020
49,11/4/2020,11/5/2020
49,11/5/2020,11/5/2020
49,11/6/2020,11/6/2020
49,11/6/2020,11/13/2020
49,11/13/2020,11/18/2020
49,11/18/2020,11/19/2020
49,11/19/2020,11/19/2020
49,11/20/2020,11/20/2020
49,11/20/2020,11/21/2020
49,11/20/2020,11/21/2020
49,11/21/2020,11/22/2020
49,11/23/2020,11/23/2020
49,11/23/2020,11/24/2020
49,11/24/2020,11/30/2020
49,11/30/2020,12/3/2020
49,12/3/2020,12/3/2020
49,12/3/2020,12/4/2020
49,12/4/2020,12/4/2020
49,12/4/2020,12/5/2020
49,12/5/2020,12/5/2020
49,12/6/2020,12/6/2020
52,10/26/2020,10/26/2020
52,10/26/2020,10/27/2020
52,10/27/2020,10/27/2020
52,10/29/2020,10/29/2020
52,10/29/2020,10/30/2020
52,10/30/2020,10/30/2020
52,10/30/2020,10/31/2020
52,10/31/2020,10/31/2020
52,11/2/2020,11/2/2020
52,11/2/2020,11/3/2020
52,11/5/2020,11/12/2020
52,11/12/2020,11/19/2020
52,11/19/2020,11/26/2020
52,11/26/2020,12/3/2020
54,10/26/2020,11/2/2020
54,11/2/2020,11/9/2020
54,11/9/2020,11/16/2020
54,11/16/2020,11/23/2020
54,11/23/2020,11/30/2020
55,10/26/2020,11/2/2020
55,11/1/2020,11/8/2020
55,11/9/2020,11/16/2020
55,11/16/2020,11/23/2020
55,11/23/2020,11/30/2020
57,10/29/2020,11/5/2020
58,10/28/2020,10/28/2020
58,10/29/2020,10/29/2020
58,10/29/2020,10/30/2020
58,10/30/2020,11/6/2020
58,11/6/2020,11/6/2020
58,11/6/2020,11/7/2020
58,11/7/2020,11/7/2020
58,11/7/2020,11/8/2020
58,11/8/2020,11/8/2020
58,11/8/2020,11/8/2020
58,11/9/2020,11/9/2020
58,11/10/2020,11/16/2020
58,11/17/2020,11/24/2020
58,11/24/2020,11/24/2020
58,11/25/2020,11/25/2020
58,11/25/2020,11/25/2020
58,11/26/2020,11/26/2020
58,11/27/2020,11/27/2020
58,11/27/2020,11/28/2020
58,11/28/2020,11/28/2020
58,11/29/2020,11/29/2020
59,10/26/2020,10/26/2020
59,10/27/2020,10/27/2020
59,10/29/2020,10/30/2020
59,10/30/2020,10/30/2020
59,11/2/2020,11/2/2020
59,11/3/2020,11/3/2020
59,11/4/2020,11/7/2020
59,11/12/2020,11/13/2020
60,10/27/2020,11/3/2020
60,11/30/2020,12/1/2020
60,12/1/2020,12/1/2020
60,12/1/2020,12/2/2020
61,10/27/2020,11/3/2020
61,11/3/2020,11/10/2020
61,11/10/2020,11/17/2020
61,11/17/2020,11/24/2020
61,11/24/2020,12/1/2020
62,10/26/2020,10/26/2020
62,10/26/2020,11/2/2020
62,11/2/2020,11/2/2020
62,11/3/2020,11/9/2020
62,11/9/2020,11/10/2020
62,11/11/2020,11/12/2020
62,11/12/2020,11/12/2020
62,11/13/2020,11/19/2020
62,11/19/2020,11/20/2020
62,11/20/2020,11/21/2020
62,11/21/2020,11/21/2020
62,11/21/2020,11/22/2020
62,11/23/2020,11/23/2020
62,11/24/2020,11/24/2020
64,10/29/2020,11/5/2020
64,11/5/2020,11/12/2020
64,11/12/2020,11/19/2020
64,11/19/2020,11/26/2020
64,11/26/2020,12/3/2020
65,10/26/2020,11/2/2020
65,11/2/2020,11/9/2020
65,11/9/2020,11/16/2020
65,11/16/2020,11/23/2020
65,11/23/2020,11/24/2020
65,11/24/2020,12/1/2020
65,12/1/2020,12/2/2020
65,12/2/2020,12/2/2020
67,10/30/2020,10/31/2020
67,10/31/2020,11/1/2020
67,10/31/2020,11/1/2020
67,11/2/2020,11/2/2020
67,11/2/2020,11/2/2020
67,11/2/2020,11/2/2020
67,11/3/2020,11/3/2020
67,11/4/2020,11/4/2020
68,10/26/2020,11/2/2020
68,11/2/2020,11/9/2020
68,11/9/2020,11/16/2020
68,11/16/2020,11/23/2020
68,11/23/2020,11/30/2020
70,10/26/2020,11/2/2020
70,11/2/2020,11/9/2020
70,11/9/2020,11/16/2020
70,11/16/2020,11/23/2020
70,11/23/2020,11/30/2020
74,10/27/2020,10/27/2020
74,10/28/2020,10/28/2020
74,10/28/2020,10/29/2020
74,11/3/2020,11/3/2020
74,11/3/2020,11/10/2020
74,11/10/2020,11/17/2020
74,11/17/2020,11/24/2020
74,11/24/2020,12/1/2020
83,10/29/2020,11/5/2020
83,11/5/2020,11/12/2020
83,11/12/2020,11/19/2020
83,11/19/2020,11/19/2020
83,11/24/2020,11/26/2020
83,11/26/2020,12/3/2020
84,10/28/2020,11/3/2020
84,11/4/2020,11/11/2020
84,11/11/2020,11/18/2020
84,11/18/2020,11/25/2020
84,11/25/2020,12/2/2020
86,10/30/2020,11/5/2020
86,11/6/2020,11/12/2020
86,11/13/2020,11/20/2020
86,11/20/2020,11/26/2020
86,11/27/2020,12/4/2020
90,10/27/2020,11/3/2020
90,11/3/2020,11/10/2020
90,11/10/2020,11/17/2020
90,11/17/2020,11/24/2020
90,11/24/2020,12/1/2020
92,10/26/2020,11/2/2020
92,11/2/2020,11/9/2020
92,11/9/2020,11/16/2020
92,11/16/2020,11/23/2020
92,11/23/2020,11/30/2020
94,10/26/2020,11/2/2020
94,11/2/2020,11/9/2020
94,11/9/2020,11/16/2020
94,11/16/2020,11/23/2020
94,11/23/2020,11/30/2020
96,10/27/2020,11/3/2020
96,11/3/2020,11/10/2020
96,11/10/2020,11/17/2020
96,11/17/2020,11/23/2020
98,11/19/2020,11/20/2020
98,11/23/2020,11/24/2020
98,11/28/2020,11/29/2020
98,11/30/2020,12/1/2020
98,12/1/2020,12/1/2020
99,10/27/2020,10/29/2020
99,10/30/2020,11/3/2020
99,11/3/2020,11/10/2020
99,11/10/2020,11/17/2020
99,11/17/2020,11/19/2020
99,11/19/2020,11/19/2020
99,11/19/2020,11/20/2020
99,11/20/2020,11/20/2020
99,11/24/2020,11/23/2020
26   Tenpoundbass   2020 Dec 16, 7:07am  

SELECT [Cab]
,[LeaseStartDate]
,[LeaseEndDate]
,DATEDIFF(DAY,LeaseStartDate, LeaseEndDate) + 1 as Interval
,case when DATEPART(MONTH, LeaseStartDate) <> DATEPART(MONTH, LeaseEndDate) then DATEDIFF(DAY,LeaseStartDate, EOMONTH(LeaseStartDate)) + 1 else DATEDIFF(DAY,LeaseStartDate, LeaseEndDate) + 1 end as StartDateInterval
,case when DATEPART(MONTH, LeaseStartDate) <> DATEPART(MONTH, LeaseEndDate) then DATEDIFF(DAY, DATEADD(DAY, 1, EOMONTH(LeaseEndDate, -1)) , LeaseEndDate) + 1 else 0 end as EndtDateInterval
,DATENAME(MONTH, LeaseStartDate) as StartMonth
,DATENAME(MONTH, LeaseEndDate) as EndMonth
FROM [ScratchPad].[dbo].[cabdata]
27   Tenpoundbass   2020 Dec 16, 7:07am  

Cab LeaseStartDate LeaseEndDate Interval StartDateInterval EndtDateInterval StartMonth EndMonth
1 2020-10-26 00:00:00.000 2020-11-02 00:00:00.000 8 6 2 October November
1 2020-11-02 00:00:00.000 2020-11-09 00:00:00.000 8 8 0 November November
1 2020-11-09 00:00:00.000 2020-11-16 00:00:00.000 8 8 0 November November
1 2020-11-16 00:00:00.000 2020-11-23 00:00:00.000 8 8 0 November November
1 2020-11-23 00:00:00.000 2020-11-30 00:00:00.000 8 8 0 November November
3 2020-10-31 00:00:00.000 2020-11-06 00:00:00.000 7 1 6 October November
3 2020-11-07 00:00:00.000 2020-11-13 00:00:00.000 7 7 0 November November
3 2020-11-14 00:00:00.000 2020-11-20 00:00:00.000 7 7 0 November November
3 2020-11-21 00:00:00.000 2020-11-27 00:00:00.000 7 7 0 November November
3 2020-11-28 00:00:00.000 2020-12-05 00:00:00.000 8 3 5 November December
5 2020-10-27 00:00:00.000 2020-11-03 00:00:00.000 8 5 3 October November
5 2020-11-03 00:00:00.000 2020-11-10 00:00:00.000 8 8 0 November November
5 2020-11-10 00:00:00.000 2020-11-17 00:00:00.000 8 8 0 November November
5 2020-11-17 00:00:00.000 2020-11-24 00:00:00.000 8 8 0 November November
5 2020-11-24 00:00:00.000 2020-12-01 00:00:00.000 8 7 1 November December
6 2020-10-28 00:00:00.000 2020-11-04 00:00:00.000 8 4 4 October November
10 2020-10-29 00:00:00.000 2020-10-30 00:00:00.000 2 2 0 October October
10 2020-10-30 00:00:00.000 2020-10-31 00:00:00.000 2 2 0 October October
10 2020-10-31 00:00:00.000 2020-10-31 00:00:00.000 1 1 0 October October
10 2020-10-31 00:00:00.000 2020-11-01 00:00:00.000 2 1 1 October November
10 2020-11-02 00:00:00.000 2020-11-09 00:00:00.000 8 8 0 November November
10 2020-11-07 00:00:00.000 2020-11-14 00:00:00.000 8 8 0 November November
10 2020-11-14 00:00:00.000 2020-11-16 00:00:00.000 3 3 0 November November
10 2020-11-16 00:00:00.000 2020-11-23 00:00:00.000 8 8 0 November November
10 2020-11-23 00:00:00.000 2020-11-24 00:00:00.000 2 2 0 November November
10 2020-11-25 00:00:00.000 2020-11-25 00:00:00.000 1 1 0 November November
10 2020-11-27 00:00:00.000 2020-11-28 00:00:00.000 2 2 0 November November
10 2020-11-28 00:00:00.000 2020-11-28 00:00:00.000 1 1 0 November November
10 2020-11-28 00:00:00.000 2020-11-29 00:00:00.000 2 2 0 November November
12 2020-10-27 00:00:00.000 2020-11-02 00:00:00.000 7 5 2 October November
12 2020-11-03 00:00:00.000 2020-11-10 00:00:00.000 8 8 0 November November
12 2020-11-10 00:00:00.000 2020-11-17 00:00:00.000 8 8 0 November November
12 2020-11-17 00:00:00.000 2020-11-24 00:00:00.000 8 8 0 November November
12 2020-11-24 00:00:00.000 2020-12-01 00:00:00.000 8 7 1 November December
16 2020-10-26 00:00:00.000 2020-11-02 00:00:00.000 8 6 2 October November
16 2020-11-02 00:00:00.000 2020-11-09 00:00:00.000 8 8 0 November November
16 2020-11-09 00:00:00.000 2020-11-16 00:00:00.000 8 8 0 November November
16 2020-11-16 00:00:00.000 2020-11-23 00:00:00.000 8 8 0 November November
16 2020-11-23 00:00:00.000 2020-11-30 00:00:00.000 8 8 0 November November
17 2020-10-27 00:00:00.000 2020-11-03 00:00:00.000 8 5 3 October November
17 2020-11-03 00:00:00.000 2020-11-09 00:00:00.000 7 7 0 November November
17 2020-11-10 00:00:00.000 2020-11-17 00:00:00.000 8 8 0 November November
17 2020-11-17 00:00:00.000 2020-11-24 00:00:00.000 8 8 0 November November
17 2020-11-25 00:00:00.000 2020-11-26 00:00:00.000 2 2 0 November November
18 2020-11-18 00:00:00.000 2020-11-21 00:00:00.000 4 4 0 November November
18 2020-11-23 00:00:00.000 2020-11-30 00:00:00.000 8 8 0 November November
19 2020-10-27 00:00:00.000 2020-11-03 00:00:00.000 8 5 3 October November
19 2020-11-03 00:00:00.000 2020-11-09 00:00:00.000 7 7 0 November November
19 2020-11-10 00:00:00.000 2020-11-17 00:00:00.000 8 8 0 November November
19 2020-11-17 00:00:00.000 2020-11-24 00:00:00.000 8 8 0 November November
19 2020-11-24 00:00:00.000 2020-12-01 00:00:00.000 8 7 1 November December
22 2020-10-27 00:00:00.000 2020-11-02 00:00:00.000 7 5 2 October November
22 2020-11-03 00:00:00.000 2020-11-10 00:00:00.000 8 8 0 November November
22 2020-11-10 00:00:00.000 2020-11-16 00:00:00.000 7 7 0 November November
22 2020-11-17 00:00:00.000 2020-11-24 00:00:00.000 8 8 0 November November
22 2020-11-24 00:00:00.000 2020-11-30 00:00:00.000 7 7 0 November November
23 2020-11-01 00:00:00.000 2020-11-01 00:00:00.000 1 1 0 November November
23 2020-11-02 00:00:00.000 2020-11-02 00:00:00.000 1 1 0 November November
23 2020-11-02 00:00:00.000 2020-11-09 00:00:00.000 8 8 0 November November
23 2020-11-09 00:00:00.000 2020-11-16 00:00:00.000 8 8 0 November November
23 2020-11-16 00:00:00.000 2020-11-23 00:00:00.000 8 8 0 November November
23 2020-11-23 00:00:00.000 2020-11-30 00:00:00.000 8 8 0 November November
24 2020-10-26 00:00:00.000 2020-11-02 00:00:00.000 8 6 2 October November
24 2020-11-02 00:00:00.000 2020-11-09 00:00:00.000 8 8 0 November November
24 2020-11-09 00:00:00.000 2020-11-16 00:00:00.000 8 8 0 November November
24 2020-11-16 00:00:00.000 2020-11-23 00:00:00.000 8 8 0 November November
24 2020-11-23 00:00:00.000 2020-11-30 00:00:00.000 8 8 0 November November
26 2020-10-29 00:00:00.000 2020-11-05 00:00:00.000 8 3 5 October November
26 2020-11-05 00:00:00.000 2020-11-12 00:00:00.000 8 8 0 November November
26 2020-11-12 00:00:00.000 2020-11-19 00:00:00.000 8 8 0 November November
26 2020-11-19 00:00:00.000 2020-11-26 00:00:00.000 8 8 0 November November
26 2020-11-26 00:00:00.000 2020-12-03 00:00:00.000 8 5 3 November December
27 2020-10-29 00:00:00.000 2020-11-05 00:00:00.000 8 3 5 October November
27 2020-11-05 00:00:00.000 2020-11-12 00:00:00.000 8 8 0 November November
27 2020-11-12 00:00:00.000 2020-11-19 00:00:00.000 8 8 0 November November
27 2020-11-19 00:00:00.000 2020-11-26 00:00:00.000 8 8 0 November November
27 2020-11-26 00:00:00.000 2020-12-03 00:00:00.000 8 5 3 November December
28 2020-10-26 00:00:00.000 2020-11-02 00:00:00.000 8 6 2 October November
28 2020-11-02 00:00:00.000 2020-11-09 00:00:00.000 8 8 0 November November
28 2020-11-09 00:00:00.000 2020-11-16 00:00:00.000 8 8 0 November November
28 2020-11-16 00:00:00.000 2020-11-23 00:00:00.000 8 8 0 November November
28 2020-11-23 00:00:00.000 2020-11-24 00:00:00.000 2 2 0 November November
32 2020-10-29 00:00:00.000 2020-11-03 00:00:00.000 6 3 3 October November
32 2020-11-05 00:00:00.000 2020-11-11 00:00:00.000 7 7 0 November November
32 2020-11-11 00:00:00.000 2020-11-17 00:00:00.000 7 7 0 November November
32 2020-11-19 00:00:00.000 2020-11-25 00:00:00.000 7 7 0 November November
32 2020-11-26 00:00:00.000 2020-12-03 00:00:00.000 8 5 3 November December
33 2020-10-27 00:00:00.000 2020-11-03 00:00:00.000 8 5 3 October November
33 2020-11-03 00:00:00.000 2020-11-10 00:00:00.000 8 8 0 November November
33 2020-11-10 00:00:00.000 2020-11-17 00:00:00.000 8 8 0 November November
33 2020-11-17 00:00:00.000 2020-11-24 00:00:00.000 8 8 0 November November
33 2020-11-24 00:00:00.000 2020-12-01 00:00:00.000 8 7 1 November December
36 2020-10-27 00:00:00.000 2020-10-27 00:00:00.000 1 1 0 October October
36 2020-11-02 00:00:00.000 2020-11-02 00:00:00.000 1 1 0 November November
36 2020-11-07 00:00:00.000 2020-11-11 00:00:00.000 5 5 0 November November
36 2020-11-11 00:00:00.000 2020-11-18 00:00:00.000 8 8 0 November November
36 2020-11-18 00:00:00.000 2020-11-25 00:00:00.000 8 8 0 November November
36 2020-11-27 00:00:00.000 2020-11-27 00:00:00.000 1 1 0 November November
36 2020-11-28 00:00:00.000 2020-11-29 00:00:00.000 2 2 0 November November
36 2020-11-30 00:00:00.000 2020-11-30 00:00:00.000 1 1 0 November November
36 2020-11-30 00:00:00.000 2020-12-03 00:00:00.000 4 1 3 November December
36 2020-12-03 00:00:00.000 2020-12-03 00:00:00.000 1 1 0 December December
36 2020-12-04 00:00:00.000 2020-12-05 00:00:00.000 2 2 0 December December
36 2020-12-04 00:00:00.000 2020-12-05 00:00:00.000 2 2 0 December December
36 2020-12-05 00:00:00.000 2020-12-05 00:00:00.000 1 1 0 December December
37 2020-10-31 00:00:00.000 2020-11-07 00:00:00.000 8 1 7 October November
37 2020-11-07 00:00:00.000 2020-11-14 00:00:00.000 8 8 0 November November
37 2020-11-14 00:00:00.000 2020-11-21 00:00:00.000 8 8 0 November November
37 2020-11-21 00:00:00.000 2020-11-28 00:00:00.000 8 8 0 November November
37 2020-11-28 00:00:00.000 2020-12-05 00:00:00.000 8 3 5 November December
38 2020-10-27 00:00:00.000 2020-11-03 00:00:00.000 8 5 3 October November
38 2020-11-03 00:00:00.000 2020-11-10 00:00:00.000 8 8 0 November November
38 2020-11-10 00:00:00.000 2020-11-17 00:00:00.000 8 8 0 November November
38 2020-11-17 00:00:00.000 2020-11-24 00:00:00.000 8 8 0 November November
38 2020-11-24 00:00:00.000 2020-12-01 00:00:00.000 8 7 1 November December
40 2020-11-10 00:00:00.000 2020-11-16 00:00:00.000 7 7 0 November November
40 2020-11-16 00:00:00.000 2020-11-23 00:00:00.000 8 8 0 November November
40 2020-11-23 00:00:00.000 2020-11-30 00:00:00.000 8 8 0 November November
41 2020-10-29 00:00:00.000 2020-11-05 00:00:00.000 8 3 5 October November
41 2020-11-05 00:00:00.000 2020-11-12 00:00:00.000 8 8 0 November November
41 2020-11-12 00:00:00.000 2020-11-19 00:00:00.000 8 8 0 November November
41 2020-11-19 00:00:00.000 2020-11-26 00:00:00.000 8 8 0 November November
41 2020-11-26 00:00:00.000 2020-12-03 00:00:00.000 8 5 3 November December
42 2020-10-29 00:00:00.000 2020-11-05 00:00:00.000 8 3 5 October November
42 2020-11-05 00:00:00.000 2020-11-12 00:00:00.000 8 8 0 November November
42 2020-11-12 00:00:00.000 2020-11-19 00:00:00.000 8 8 0 November November
42 2020-11-19 00:00:00.000 2020-11-26 00:00:00.000 8 8 0 November November
42 2020-11-26 00:00:00.000 2020-12-03 00:00:00.000 8 5 3 November December
45 2020-10-26 00:00:00.000 2020-10-30 00:00:00.000 5 5 0 October October
45 2020-11-02 00:00:00.000 2020-11-07 00:00:00.000 6 6 0 November November
45 2020-11-09 00:00:00.000 2020-11-14 00:00:00.000 6 6 0 November November
45 2020-11-16 00:00:00.000 2020-11-21 00:00:00.000 6 6 0 November November
45 2020-11-23 00:00:00.000 2020-11-28 00:00:00.000 6 6 0 November November
45 2020-11-30 00:00:00.000 2020-12-05 00:00:00.000 6 1 5 November December
46 2020-10-29 00:00:00.000 2020-11-05 00:00:00.000 8 3 5 October November
46 2020-11-06 00:00:00.000 2020-11-13 00:00:00.000 8 8 0 November November
46 2020-11-14 00:00:00.000 2020-11-18 00:00:00.000 5 5 0 November November
46 2020-11-18 00:00:00.000 2020-11-18 00:00:00.000 1 1 0 November November
46 2020-11-19 00:00:00.000 2020-11-19 00:00:00.000 1 1 0 November November
46 2020-11-19 00:00:00.000 2020-11-24 00:00:00.000 6 6 0 November November
46 2020-11-26 00:00:00.000 2020-12-03 00:00:00.000 8 5 3 November December
46 2020-12-03 00:00:00.000 2020-12-04 00:00:00.000 2 2 0 December December
46 2020-12-04 00:00:00.000 2020-12-04 00:00:00.000 1 1 0 December December
46 2020-12-04 00:00:00.000 2020-12-05 00:00:00.000 2 2 0 December December
46 2020-12-05 00:00:00.000 2020-12-05 00:00:00.000 1 1 0 December December
46 2020-12-05 00:00:00.000 2020-12-06 00:00:00.000 2 2 0 December December
46 2020-12-06 00:00:00.000 2020-12-06 00:00:00.000 1 1 0 December December
49 2020-10-30 00:00:00.000 2020-10-30 00:00:00.000 1 1 0 October October
49 2020-10-31 00:00:00.000 2020-10-31 00:00:00.000 1 1 0 October October
49 2020-10-31 00:00:00.000 2020-11-01 00:00:00.000 2 1 1 October November
49 2020-11-02 00:00:00.000 2020-11-02 00:00:00.000 1 1 0 November November
49 2020-11-02 00:00:00.000 2020-11-03 00:00:00.000 2 2 0 November November
49 2020-11-03 00:00:00.000 2020-11-03 00:00:00.000 1 1 0 November November
49 2020-11-04 00:00:00.000 2020-11-05 00:00:00.000 2 2 0 November November
49 2020-11-05 00:00:00.000 2020-11-05 00:00:00.000 1 1 0 November November
49 2020-11-06 00:00:00.000 2020-11-06 00:00:00.000 1 1 0 November November
49 2020-11-06 00:00:00.000 2020-11-13 00:00:00.000 8 8 0 November November
49 2020-11-13 00:00:00.000 2020-11-18 00:00:00.000 6 6 0 November November
49 2020-11-18 00:00:00.000 2020-11-19 00:00:00.000 2 2 0 November November
49 2020-11-19 00:00:00.000 2020-11-19 00:00:00.000 1 1 0 November November
49 2020-11-20 00:00:00.000 2020-11-20 00:00:00.000 1 1 0 November November
49 2020-11-20 00:00:00.000 2020-11-21 00:00:00.000 2 2 0 November November
49 2020-11-20 00:00:00.000 2020-11-21 00:00:00.000 2 2 0 November November
49 2020-11-21 00:00:00.000 2020-11-22 00:00:00.000 2 2 0 November November
49 2020-11-23 00:00:00.000 2020-11-23 00:00:00.000 1 1 0 November November
49 2020-11-23 00:00:00.000 2020-11-24 00:00:00.000 2 2 0 November November
49 2020-11-24 00:00:00.000 2020-11-30 00:00:00.000 7 7 0 November November
49 2020-11-30 00:00:00.000 2020-12-03 00:00:00.000 4 1 3 November December
49 2020-12-03 00:00:00.000 2020-12-03 00:00:00.000 1 1 0 December December
49 2020-12-03 00:00:00.000 2020-12-04 00:00:00.000 2 2 0 December December
49 2020-12-04 00:00:00.000 2020-12-04 00:00:00.000 1 1 0 December December
49 2020-12-04 00:00:00.000 2020-12-05 00:00:00.000 2 2 0 December December
49 2020-12-05 00:00:00.000 2020-12-05 00:00:00.000 1 1 0 December December
49 2020-12-06 00:00:00.000 2020-12-06 00:00:00.000 1 1 0 December December
52 2020-10-26 00:00:00.000 2020-10-26 00:00:00.000 1 1 0 October October
52 2020-10-26 00:00:00.000 2020-10-27 00:00:00.000 2 2 0 October October
52 2020-10-27 00:00:00.000 2020-10-27 00:00:00.000 1 1 0 October October
52 2020-10-29 00:00:00.000 2020-10-29 00:00:00.000 1 1 0 October October
52 2020-10-29 00:00:00.000 2020-10-30 00:00:00.000 2 2 0 October October
52 2020-10-30 00:00:00.000 2020-10-30 00:00:00.000 1 1 0 October October
52 2020-10-30 00:00:00.000 2020-10-31 00:00:00.000 2 2 0 October October
52 2020-10-31 00:00:00.000 2020-10-31 00:00:00.000 1 1 0 October October
52 2020-11-02 00:00:00.000 2020-11-02 00:00:00.000 1 1 0 November November
52 2020-11-02 00:00:00.000 2020-11-03 00:00:00.000 2 2 0 November November
52 2020-11-05 00:00:00.000 2020-11-12 00:00:00.000 8 8 0 November November
52 2020-11-12 00:00:00.000 2020-11-19 00:00:00.000 8 8 0 November November
52 2020-11-19 00:00:00.000 2020-11-26 00:00:00.000 8 8 0 November November
52 2020-11-26 00:00:00.000 2020-12-03 00:00:00.000 8 5 3 November December
54 2020-10-26 00:00:00.000 2020-11-02 00:00:00.000 8 6 2 October November
54 2020-11-02 00:00:00.000 2020-11-09 00:00:00.000 8 8 0 November November
54 2020-11-09 00:00:00.000 2020-11-16 00:00:00.000 8 8 0 November November
54 2020-11-16 00:00:00.000 2020-11-23 00:00:00.000 8 8 0 November November
54 2020-11-23 00:00:00.000 2020-11-30 00:00:00.000 8 8 0 November November
55 2020-10-26 00:00:00.000 2020-11-02 00:00:00.000 8 6 2 October November
55 2020-11-01 00:00:00.000 2020-11-08 00:00:00.000 8 8 0 November November
55 2020-11-09 00:00:00.000 2020-11-16 00:00:00.000 8 8 0 November November
55 2020-11-16 00:00:00.000 2020-11-23 00:00:00.000 8 8 0 November November
55 2020-11-23 00:00:00.000 2020-11-30 00:00:00.000 8 8 0 November November
57 2020-10-29 00:00:00.000 2020-11-05 00:00:00.000 8 3 5 October November
58 2020-10-28 00:00:00.000 2020-10-28 00:00:00.000 1 1 0 October October
58 2020-10-29 00:00:00.000 2020-10-29 00:00:00.000 1 1 0 October October
58 2020-10-29 00:00:00.000 2020-10-30 00:00:00.000 2 2 0 October October
58 2020-10-30 00:00:00.000 2020-11-06 00:00:00.000 8 2 6 October November
58 2020-11-06 00:00:00.000 2020-11-06 00:00:00.000 1 1 0 November November
58 2020-11-06 00:00:00.000 2020-11-07 00:00:00.000 2 2 0 November November
58 2020-11-07 00:00:00.000 2020-11-07 00:00:00.000 1 1 0 November November
58 2020-11-07 00:00:00.000 2020-11-08 00:00:00.000 2 2 0 November November
58 2020-11-08 00:00:00.000 2020-11-08 00:00:00.000 1 1 0 November November
58 2020-11-08 00:00:00.000 2020-11-08 00:00:00.000 1 1 0 November November
28   HeadSet   2020 Dec 16, 10:20am  

richwicks says

BTW @HeadSet - ever heard of LibreOffice?

I love LibreOffice, and before that OpenOffice. Any "missing" feature needed could be added with plug-ins. I had LibreOffice on all worker stations for years. However, I had to install MS Office because puds would send us Excel files with a formatting that LibreOffice could not read. These were usually files from government agencies, where the formatting was just for looks (like cool titles) and nothing of substance. Also, workers wanted to have "MS Office experience" on resumes so they complained until the owner made me buy Office. By the way, unlike Excel, LibreOffice Calc does not have a file size limit. I was able to extract data from an old legacy dBase3 file into Calc to parse it down. Excel would only be able to pull the first third or so before maxing out, and truncate the rest.

Also, if one want to write a user manual or any other complex document with pics and charts, LibreOffice Write is far superior to Word. With LibreOffice, you can adjust a graphic without scrambling the adjacent text formatting. With Word, moving a graphic even slightly can jumble up the text formatting for several pages.
29   richwicks   2020 Dec 16, 10:31am  

HeadSet says
Also, workers wanted to have "MS Office experience" on resumes so they complained until the owner made me buy Office.


Baabaa black sheep have you any wool?...

MS Office has moved to a rental option now. It frustrates me to no end how dumb companies are. We're going to get our asses handed to us because of this thinking. Other nations will bypass this garbage and will leapfrog our entire nation in technology.

There will be no real improvements in MS Office (not that there needs to be anyhow), but LibreOffice will just get faster, smaller, and better. MS Office will eventually become entirely incompatible with it, and that will create a rift, but one thing I've learned in 30 years of engineering is that it may take a long time, but the cheapest solution wins, not the best. Over time LibreOffice won't only be the cheapest solution, it will be the best solution. Same with Linux.
30   Tenpoundbass   2020 Dec 16, 11:54am  

DECLARE @CabTemp TABLE(Cab INT, [LeaseStartDate] DateTime, LeaseEndDate DateTime, StartDateInterval INT , EndtDateInterval INT, StartMonth VARCHAR(12), EndMonth VARCHAR(12))

INSERT INTO @CabTemp
SELECT [Cab]
,[LeaseStartDate]
,[LeaseEndDate]
,case when DATEPART(MONTH, LeaseStartDate) <> DATEPART(MONTH, LeaseEndDate) then DATEDIFF(DAY,LeaseStartDate, EOMONTH(LeaseStartDate)) + 1 else DATEDIFF(DAY,LeaseStartDate, LeaseEndDate) + 1 end as StartDateInterval
,case when DATEPART(MONTH, LeaseStartDate) <> DATEPART(MONTH, LeaseEndDate) then DATEDIFF(DAY, DATEADD(DAY, 1, EOMONTH(LeaseEndDate, -1)) , LeaseEndDate) + 1 else 0 end as EndtDateInterval
,DATENAME(MONTH, LeaseStartDate) as StartMonth
,DATENAME(MONTH, LeaseEndDate) as EndMonth
FROM [ScratchPad].[dbo].[cabdata];

With CTE as( Select Cab, Cab as CabCount, StartDateInterval, StartMonth from @CabTemp
Union
Select Cab, Cab as CabCount, EndtDateInterval, EndMonth from @CabTemp WHere EndtDateInterval > 0)
Select * from
(select Cab, CabCount, StartDateInterval, StartMonth from CTE ) t
PIVOT (MAX(CabCount) FOR StartDateInterval
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
31   Tenpoundbass   2020 Dec 16, 11:58am  

Then gives you data that looks like this..

32   Tenpoundbass   2020 Dec 16, 12:00pm  

In your data you don't have any date ranges beyond 8, the rest of the data is null. The other thing, is to format the Cab to be "*" as I'm showing the Cab number in the grid

Download SQL Express it's free, and is the most powerful way to rip data like this.
33   HeadSet   2020 Dec 16, 12:55pm  

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


34   HeadSet   2020 Dec 16, 1:09pm  

Oops, I see that I named the column "VehicleNumber: and not "Cab"
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)

Comments 1 - 40 of 72   Next »    Last »

Please register to comment:

about   best comments   contact   latest images   one year ago   suggestions