Saya memiliki tabel berikut dengan Mobil. Orang-orang memiliki Mobil standar, tetapi kadang-kadang (Ketika mobil masuk ke garasi atau rusak) mereka mendapatkan mobil lain. Di negara saya Anda harus membayar pajak untuk mengendarai mobil perusahaan, jadi penting KETIKA mobil dikemudikan dan mobil MANA yang dikendarai. Jika mobil cadangan adalah mobil yang lebih murah, Anda membayar pajak lebih sedikit pada periode tersebut.

Tabelnya tidak terlalu sulit:

CREATE TABLE dbo.CARUSAGE
(
    ID int IDENTITY(1,1) NOT NULL,
    StartDate datetime NULL,
    EndDate datetime NULL,
    CarID int NULL,
    UserID int NULL,
    TypeID int NULL
);

INSERT dbo.CARUSAGE (StartDate, EndDate, CarID, UserID, TypeID) 
VALUES ('2020-04-19', NULL, 2, 2606, 1)
       ,('2020-07-07', '2020-10-07', 3, 2606, 2)
       ,('2020-10-12', '2020-10-31', 4, 2606, 2)

TypeID:
TypeID 1 = Standar.
TypeID 2 = Cadangan.

Mobil standar seperti benang. Anda selalu memiliki mobil standar kecuali Anda menggunakan cadangan.

Mobil standar EndDate adalah NULL saat masih mengemudi.

Saya suka mencapai set hasil seperti ini:

+----------+--------+---------+---------+-------------+-------------+
|  NewID  |  CarID |  UserID |  TypeID |   StartDate |     EndDate |
+----------+--------+---------+---------+-------------+-------------+
|        1 |      2 |    2606 |       1 | 2020-04-19  | 2020-07-06  |
|        2 |      3 |    2606 |       2 | 2020-07-07  | 2020-10-06  |
|        3 |      2 |    2606 |       1 | 2020-10-07  | 2020-10-11  |
|        4 |      4 |    2606 |       2 | 2020-10-12  | 2020-10-30  |
|        5 |      2 |    2606 |       1 | 2020-10-31  | GetDate()   |
|          |        |         |         |             |             |
+----------+--------+---------+---------+-------------+-------------+

Pertanyaan tambahan berdasarkan di atas:

Meskipun GMB menjawab pertanyaan saya dengan sangat baik, saya lupa menyebutkan satu hal yang membuat pertanyaannya sedikit lebih rumit.

Dimungkinkan untuk menambahkan mobil 'standar' baru. Ketika mobil 'standar' baru ditambahkan maka mobil 'standar' asli mendapat tanggal akhir. Tabel sumber terlihat seperti:

INSERT dbo.CARUSAGE (StartDate, EndDate, CarID, UserID, TypeID) 
VALUES ('2020-04-19', '2020-11-04', 2, 2606, 1)
       ,('2020-07-07', '2020-10-07', 3, 2606, 2)
       ,('2020-10-12', '2020-10-31', 4, 2606, 2)
       ,('2020-11-05', null, 5, 2606, 1)
       ,('2020-11-09', '2020-11-14', 6, 2606, 2)

Prosesnya harus sama. selalu ada mobil 'standar'. Hasil seleksi akan terlihat seperti ini

+----------+--------+---------+---------+-------------+-------------+
|  NewID  |  CarID |  UserID |  TypeID |   StartDate |     EndDate |
+----------+--------+---------+---------+-------------+-------------+
|        1 |      2 |    2606 |       1 | 2020-04-19  | 2020-07-06  |
|        2 |      3 |    2606 |       2 | 2020-07-07  | 2020-10-06  |
|        3 |      2 |    2606 |       1 | 2020-10-07  | 2020-10-11  |
|        4 |      4 |    2606 |       2 | 2020-10-12  | 2020-10-30  |
|        5 |      2 |    2606 |       1 | 2020-10-31  | 2020-11-04  |
|        6 |      5 |    2606 |       1 | 2020-11-05  | 2020-11-08  |
|        7 |      6 |    2606 |       2 | 2020-11-09  | 2020-11-13  |
|        8 |      5 |    2606 |       1 | 2020-11-14  | GetDate()   |
+----------+--------+---------+---------+-------------+-------------+

Terima kasih sebelumnya.

1
Martin Jansen 20 November 2020, 12:54

1 menjawab

Jawaban Terbaik

Jika saya mengikuti Anda dengan benar, Anda dapat membatalkan tanggal mulai dan berakhir, dan mengisi "celah" dengan catatan cadangan, seperti:

select t.carid, c.userid,t.typeid, t.dt as startdt,
    lead(dateadd(day, -1, t.dt), 1, convert(date, getdate())) over(partition by c.userid order by t.dt) as enddt
from carusage c
cross apply (select carid from carusage c1 where c1.userid = c.userid and c1.typeid = 1) s
cross apply (values (c.startdate, c.typeid, c.carid), (c.enddate, 1, s.carid)) t(dt, typeid, carid)
where t.dt is not null
order by t.dt

Demo di DB Fiddlde:

carid | userid | typeid | startdt                 | enddt                  
----: | -----: | -----: | :---------------------- | :----------------------
    2 |   2606 |      1 | 2020-04-19 00:00:00.000 | 2020-07-06 00:00:00.000
    3 |   2606 |      2 | 2020-07-07 00:00:00.000 | 2020-10-06 00:00:00.000
    2 |   2606 |      1 | 2020-10-07 00:00:00.000 | 2020-10-11 00:00:00.000
    4 |   2606 |      2 | 2020-10-12 00:00:00.000 | 2020-10-30 00:00:00.000
    2 |   2606 |      1 | 2020-10-31 00:00:00.000 | 2020-11-20 00:00:00.000
0
GMB 20 November 2020, 10:30