Comments 1 - 40 of 74 Next » Last » Search these comments
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.
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.
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.
So those numbers at the top are the date range between the two dates in the data?
HeadSet says2. 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?
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.
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.
@richwicks
Thanks! I am real curious in seeing how that is done, especially the logic.
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.
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.
richwicks saysTo 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.
I am familiar with VBA used in Access, so in the meantime I will install perl and check out a few tutorials.
Can you post the input file in CSV format. At least some chunk like100 lines of it is too large, for reasonable testing.
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.
Also, workers wanted to have "MS Office experience" on resumes so they complained until the owner made me buy Office.
OK, I may be doing the dbo source (FROM) wrong:HeadSet says
Oops, I see that I named the column "VehicleNumber: and not "Cab"
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.
OK, I may be doing the dbo source (FROM) wrong:
Comments 1 - 40 of 74 Next » Last » 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.