patrick.net
An Antidote to Corporate Media
1,192,892 comments by 13,872 users - porkchopXpress, richwicks, stereotomy online now
invite response« First « Previous Comments 26 - 65 of 74 Next » Last » Search these comments
26
Tenpoundbass
2020 Dec 16, 7:07am
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]
,[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
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
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
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
richwicks
2020 Dec 16, 10:31am
HeadSet says
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.
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
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
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
32
Tenpoundbass
2020 Dec 16, 12:00pm
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.
Download SQL Express it's free, and is the most powerful way to rip data like this.
35
Tenpoundbass
2020 Dec 16, 1:11pm
Tenpoundbass
2020 Dec 16, 1:11pm
HeadSet says
Ah you da man!
Nice how did it work out for you?
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
HeadSet
2020 Dec 16, 1:11pm
Tenpoundbass says
That is what I need! I just got to take out the errors I made copying your design.
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
Tenpoundbass
2020 Dec 16, 1:16pm
HeadSet says
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).
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
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
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
Tenpoundbass
2020 Dec 16, 1:33pm
HeadSet says
@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.
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
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)
42
HeadSet
2020 Dec 16, 1:45pm
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
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
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
Tenpoundbass
2020 Dec 16, 1:57pm
HeadSet says
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.
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
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.
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
richwicks
2020 Dec 16, 2:24pm
Tenpoundbass 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 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.
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
Tenpoundbass
2020 Dec 16, 2:40pm
richwicks says
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,
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
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
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
richwicks
2020 Dec 16, 2:53pm
HeadSet says
Problem solved? Am I off the hook?
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
richwicks
2020 Dec 16, 3:09pm
Tenpoundbass says
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.
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
HeadSet
2020 Dec 16, 4:45pm
Tenpoundbass says
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.
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
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.
Tenpoundbass saysbut 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
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
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
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.
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
Tenpoundbass
2020 Dec 16, 6:37pm
HeadSet says
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.
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.
60
HeadSet
2020 Dec 16, 6:47pm
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?
62
Tenpoundbass
2020 Dec 16, 7:02pm
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
HeadSet
2020 Dec 16, 7:06pm
Tenpoundbass says
Yes, I was wondering how the original code sorted. I guess it just knew what you wanted!
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)
# 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.
« First « Previous Comments 26 - 65 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.