Need to find new vehicle service records for last six months

Question

Need to find new vehicle service records for last six months

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

randyetheridge SSCommitted Points: 1703 More actions · Answer 1

actually I am going to repost the data again. because I did not do it correctly the first person to help just used consecutive dates. that will not work for the example. So hold on and give me another 10 minutes and I am going to attempt to post correctly my data and requirements.

randyetheridge SSCommitted Points: 1703 More actions · Answer 2

ok so I understand how to do this better. I have two tables. vehiclemaintenance and vehicle table

this is the data for vehiclemaintenance

Create Table VehicleMaintenance(

Unitid interger not null

,servicedate datetime not null

,type varchar(13) not null

,Amt numeric(5,2) not null);

Insert Into vehiclemaintenance (unitid,servicedate,type,amt) values

(1,'2012-01-09','AirFilter',19.01)

(1,'2012-01-23','Oil',34.33)

(1,'2012-02-23','Tire',57.77)

(1,'2012-03-05','Oil',40.8)

(1,'2012-03-17','Oil',45.65)

(1,'2012-04-01','battery',36.76)

(1,'2012-04-18','battery',19.01)

(1,'2012-04-19','sparkplugs',65.36)

(1,'2012-05-25','Tire',13.32)

(1,'2012-06-11','Oil',5.23)

(1,'2012-06-29','brakes',4.47)

(1,'2012-08-14','brakes',9.31)

(1,'2012-10-30','brakes',12.51)

(1,'2012-11-03','radiatorflush',11.7)

(1,'2012-11-24','sparkplugs',53.73)

(1,'2012-11-26','Tire',18.17)

(1,'2012-12-08','battery',14.12)

(1,'2012-12-17','radiatorflush',2)

(1,'2013-03-25','Tire',39.99)

(1,'2013-03-28','battery',47.26)

(1,'2013-04-28','AirFilter',15.78)

(1,'2013-06-21','sparkplugs',23.34)

(1,'2013-07-13','radiatorflush',29.48)

(1,'2013-07-28','radiatorflush',52.92)

(1,'2013-08-02','AirFilter',3.62)

(1,'2013-08-08','Oil',12.95)

(1,'2013-08-08','brakes',8.51)

(1,'2013-09-16','AirFilter',1.23)

(1,'2013-10-05','Oil',17.36)

(1,'2013-10-07','Tire',44.84)

(1,'2013-10-11','brakes',43.22)

(1,'2013-10-24','brakes',47.26)

(1,'2013-10-28','radiatorflush',53.73)

(1,'2013-10-30','radiatorflush',0.38)

(1,'2013-11-07','AirFilter',31.91)

(1,'2013-11-21','AirFilter',22.25)

(1,'2013-12-29','AirFilter',43.22)

(1,'2014-01-04','radiatorflush',3.65)

(1,'2014-01-20','Oil',27.87)

(1,'2014-01-27','AirFilter',14.74)

(1,'2014-02-03','AirFilter',51.3)

(1,'2014-02-21','radiatorflush',28.67)

(1,'2014-04-05','sparkplugs',21.4)

(1,'2014-04-15','Oil',56.15)

(1,'2014-04-16','brakes',54.54)

(1,'2014-09-04','radiatorflush',14.12)

(1,'2014-09-26','radiatorflush',19.01)

(1,'2014-10-15','sparkplugs',23.86)

(1,'2014-10-22','Tire',3.65)

(1,'2014-12-23','Oil',17.4)

(1,'2015-01-05','Tire',21.44)

(1,'2015-02-26','Tire',1.19)

(1,'2015-03-31','sparkplugs',27.06)

(1,'2015-04-22','Tire',4.47)

(1,'2015-04-23','battery',49.69)

(1,'2015-07-22','battery',18.21)

(1,'2015-10-23','AirFilter',39.99)

(1,'2015-11-06','AirFilter',45.65)

(1,'2015-12-11','Oil',49.45)

(1,'2015-12-21','sparkplugs',14.12)

(1,'2015-12-24','battery',14.74)

(1,'2016-01-28','AirFilter',12.55)

(1,'2016-03-22','Tire',27.87)

(2,'2012-01-10','radiatorflush',15.78)

(2,'2012-01-17','battery',7.7)

(2,'2012-02-20','Oil',11.7)

(2,'2012-02-23','sparkplugs',7.7)

(2,'2012-03-26','brakes',12.95)

(2,'2012-04-07','battery',49.45)

(2,'2012-05-11','battery',21.4)

(2,'2012-05-30','Tire',28.67)

(2,'2012-06-07','brakes',23.86)

(2,'2012-06-15','brakes',30.29)

(2,'2012-11-13','brakes',3.65)

(2,'2012-11-14','Tire',47.26)

(2,'2012-12-09','AirFilter',44.84)

(2,'2013-01-19','Tire',56.15)

(2,'2013-03-10','AirFilter',23.34)

(2,'2013-04-27','brakes',58.58)

(2,'2013-05-18','Oil',52.11)

(2,'2013-05-19','radiatorflush',19.82)

(2,'2013-05-28','radiatorflush',25.44)

(2,'2013-06-04','AirFilter',38.37)

(2,'2013-07-22','Oil',49.69)

(2,'2013-09-07','sparkplugs',8.47)

(2,'2013-09-13','sparkplugs',2.81)

(2,'2013-11-03','brakes',13.32)

(2,'2013-12-05','sparkplugs',13.36)

(2,'2013-12-05','Tire',5.27)

(2,'2013-12-22','Tire',17.36)

(2,'2014-01-09','sparkplugs',37.56)

(2,'2014-01-13','battery',30.29)

(2,'2014-01-27','brakes',40.8)

(2,'2014-02-16','battery',58.58)

(2,'2014-03-09','battery',13.32)

(2,'2014-03-25','battery',37.56)

(2,'2014-05-02','battery',8.47)

(2,'2014-05-04','radiatorflush',40.8)

(2,'2014-07-16','battery',41.61)

(2,'2014-08-18','brakes',35.14)

(2,'2014-10-23','sparkplugs',3.65)

(2,'2014-10-23','brakes',48.07)

(2,'2015-05-04','Tire',52.11)

(2,'2015-06-11','Tire',3.66)

(2,'2015-07-25','radiatorflush',52.11)

(2,'2015-11-24','sparkplugs',16.55)

(2,'2015-12-28','brakes',24.63)

(2,'2016-02-04','sparkplugs',16.59)

(2,'2016-02-19','brakes',57.77)

(2,'2016-03-03','Tire',18.97)

(3,'2012-01-17','AirFilter',18.21)

(3,'2012-01-19','AirFilter',20.59)

(3,'2012-02-07','Oil',14.97)

(3,'2012-02-09','battery',2.81)

(3,'2012-03-14','sparkplugs',23.06)

(3,'2012-04-11','Tire',22.25)

(3,'2012-05-09','brakes',2.04)

(3,'2012-05-14','brakes',19.78)

(3,'2012-06-10','Oil',23.06)

(3,'2012-07-03','Oil',56.96)

(3,'2012-09-06','radiatorflush',23.34)

(3,'2012-09-08','brakes',10.12)

(3,'2012-09-09','Tire',49.45)

(3,'2012-10-22','sparkplugs',14.74)

(3,'2012-10-30','Tire',65.36)

(3,'2012-11-22','sparkplugs',48.07)

(3,'2012-12-01','battery',23.06)

(3,'2013-01-16','Oil',38.37)

(3,'2013-01-19','brakes',36.76)

(3,'2013-03-14','AirFilter',14.74)

(3,'2013-03-20','radiatorflush',9.31)

(3,'2013-07-17','radiatorflush',16.59)

(3,'2013-10-18','Oil',28.67)

(3,'2014-02-10','Oil',15.74)

(3,'2014-02-27','Tire',23.82)

(3,'2014-04-01','sparkplugs',10.08)

(3,'2014-06-19','battery',14.74)

(3,'2014-06-21','brakes',8.47)

(3,'2014-06-28','AirFilter',6.89)

(3,'2014-08-15','sparkplugs',2.85)

(3,'2014-08-19','Oil',12.55)

(3,'2014-09-01','AirFilter',26.25)

(3,'2014-09-11','battery',17.4)

(3,'2014-11-04','Oil',35.14)

(3,'2014-12-06','Oil',12.51)

(3,'2014-12-12','radiatorflush',39.99)

(3,'2015-01-22','sparkplugs',8.51)

(3,'2015-02-18','brakes',2)

(3,'2015-03-22','brakes',20.63)

(3,'2015-05-28','Oil',15.78)

(3,'2015-06-03','battery',8.51)

(3,'2015-06-13','sparkplugs',31.91)

(3,'2015-07-03','battery',3.65)

(3,'2015-07-27','battery',52.92)

(3,'2015-08-23','sparkplugs',5.23)

(3,'2015-10-09','brakes',6.85)

(3,'2015-10-14','AirFilter',6.08)

(3,'2015-10-14','battery',43.22)

(3,'2016-01-13','sparkplugs',3.65)

(3,'2016-03-11','Tire',19.82)

(4,'2012-01-03','Oil',23.34)

(4,'2012-01-04','sparkplugs',44.03)

(4,'2012-02-06','brakes',52.11)

(4,'2012-03-24','brakes',48.88)

(4,'2012-04-11','radiatorflush',7.66)

(4,'2012-06-09','radiatorflush',17.36)

(4,'2012-07-14','Oil',10.93)

(4,'2012-09-11','AirFilter',39.99)

(4,'2012-11-01','sparkplugs',33.52)

(4,'2012-11-02','radiatorflush',18.17)

(4,'2012-11-28','battery',9.31)

(4,'2012-12-01','radiatorflush',13.32)

(4,'2013-04-15','AirFilter',0.38)

(4,'2013-05-08','battery',42.41)

(4,'2013-07-04','Tire',39.99)

(4,'2013-11-25','battery',2)

(4,'2013-12-02','Tire',12.51)

(4,'2014-01-21','radiatorflush',59.39)

(4,'2014-02-22','AirFilter',17.36)

(4,'2014-02-23','brakes',15.78)

(4,'2014-06-04','Tire',46.46)

(4,'2014-06-23','battery',18.97)

(4,'2014-07-16','radiatorflush',39.99)

(4,'2014-07-22','radiatorflush',58.58)

(4,'2014-09-04','battery',53.73)

(4,'2014-11-20','radiatorflush',23.34)

(4,'2014-12-06','Oil',6.85)

(4,'2014-12-11','sparkplugs',49.69)

(4,'2014-12-19','Oil',65.36)

(4,'2014-12-29','sparkplugs',17.4)

(4,'2015-01-04','AirFilter',17.4)

(4,'2015-01-22','battery',6.89)

(4,'2015-02-24','AirFilter',50.5)

(4,'2015-02-26','brakes',31.1)

(4,'2015-03-23','battery',20.63)

(4,'2015-03-27','brakes',1.19)

(4,'2015-03-31','battery',48.07)

(4,'2015-05-07','radiatorflush',51.3)

(4,'2015-05-16','sparkplugs',25.44)

(4,'2015-06-09','battery',14.93)

(4,'2015-07-15','brakes',39.99)

(4,'2015-08-09','sparkplugs',50.5)

(4,'2015-10-08','Oil',44.03)

(4,'2015-10-30','sparkplugs',12.95)

(4,'2016-01-24','brakes',29.48)

(5,'2012-01-18','Tire',34.33)

(5,'2012-01-24','radiatorflush',18.97)

(5,'2012-02-27','sparkplugs',26.25)

(5,'2012-04-30','brakes',18.17)

(5,'2012-05-04','Oil',23.82)

(5,'2012-05-11','AirFilter',16.55)

(5,'2012-05-25','Oil',23.34)

(5,'2012-10-17','AirFilter',39.18)

(5,'2012-11-07','Oil',39.99)

(5,'2012-11-30','battery',14.97)

(5,'2012-12-08','battery',48.88)

(5,'2013-02-14','Oil',39.18)

(5,'2013-03-02','AirFilter',49.69)

(5,'2013-03-10','battery',12.55)

(5,'2013-05-06','battery',3.65)

(5,'2013-06-16','sparkplugs',65.36)

(5,'2013-06-17','radiatorflush',57.77)

(5,'2013-06-25','AirFilter',5.23)

(5,'2013-07-18','AirFilter',27.87)

(5,'2013-08-02','AirFilter',65.36)

(5,'2013-08-12','Tire',40.8)

(5,'2013-08-22','Oil',22.21)

(5,'2013-09-27','brakes',14.12)

(5,'2013-12-12','AirFilter',4.43)

(5,'2014-01-01','brakes',12.55)

(5,'2014-01-20','AirFilter',56.96)

(5,'2014-01-26','Oil',1.23)

(5,'2014-02-13','AirFilter',27.06)

(5,'2014-03-26','battery',24.63)

(5,'2014-05-02','battery',25.44)

(5,'2014-08-10','brakes',19.82)

(5,'2014-09-07','battery',1.23)

(5,'2014-09-10','battery',65.36)

(5,'2014-09-25','brakes',42.41)

(5,'2014-10-06','Tire',35.95)

(5,'2014-10-11','radiatorflush',6.85)

(5,'2014-11-17','battery',3.62)

(5,'2014-12-28','Tire',24.63)

(5,'2015-01-26','brakes',37.56)

(5,'2015-02-24','Oil',23.02)

(5,'2015-04-11','battery',2.85)

(5,'2015-04-21','Oil',5.27)

(5,'2015-04-22','Tire',10.93)

(5,'2015-04-30','Oil',46.46)

(5,'2015-05-07','sparkplugs',56.15)

(5,'2015-05-08','AirFilter',14.93)

(5,'2015-07-19','AirFilter',0.42)

(5,'2015-08-08','radiatorflush',14.16)

(5,'2015-09-10','sparkplugs',39.18)

(5,'2015-10-13','Oil',18.17)

(5,'2015-10-23','brakes',21.44)

(5,'2015-10-29','AirFilter',22.21)

(5,'2015-11-12','AirFilter',2.04)

(5,'2016-01-23','Oil',57.77)

(5,'2016-02-06','brakes',7.7)

(6,'2015-12-13','radiatorflush',30.29)

(6,'2016-02-06','Tire',7.66)

(6,'2016-03-06','radiatorflush',36.76)

(6,'2016-03-12','brakes',26.25)

(6,'2016-03-14','brakes',52.92)

(7,'2012-01-03','AirFilter',23.34)

(7,'2012-01-28','Oil',18.21)

(7,'2012-03-06','battery',31.1)

(7,'2012-03-17','AirFilter',11.74)

(7,'2012-05-04','brakes',3.65)

(7,'2012-05-04','Tire',10.12)

(7,'2012-05-12','radiatorflush',49.45)

(7,'2012-06-18','Oil',6.89)

(7,'2012-08-07','Tire',16.55)

(7,'2012-08-21','radiatorflush',23.82)

(7,'2012-09-30','Oil',65.36)

(7,'2012-09-30','Tire',15.78)

(7,'2012-11-02','radiatorflush',41.61)

(7,'2012-11-17','Oil',50.5)

(7,'2013-01-17','brakes',14.74)

(7,'2013-02-04','Tire',58.58)

(7,'2013-04-29','brakes',14.74)

(7,'2013-06-08','AirFilter',54.54)

(7,'2013-07-21','battery',14.16)

(7,'2013-07-26','radiatorflush',10.12)

(7,'2013-08-12','Tire',0.42)

(7,'2013-11-09','brakes',31.91)

(7,'2013-12-18','Tire',6.04)

(7,'2013-12-19','Oil',49.45)

(7,'2014-01-27','AirFilter',37.56)

(7,'2014-02-03','radiatorflush',21.44)

(7,'2014-03-02','Tire',49.45)

(7,'2014-03-14','radiatorflush',5.27)

(7,'2014-03-29','sparkplugs',18.21)

(7,'2014-04-08','battery',32.71)

(7,'2014-04-20','Tire',45.65)

(7,'2014-05-28','AirFilter',48.88)

(7,'2014-06-03','radiatorflush',8.51)

(7,'2014-08-02','Tire',6.85)

(7,'2014-11-10','Oil',21.44)

(7,'2014-11-27','sparkplugs',6.08)

(7,'2015-01-21','Oil',39.99)

(7,'2015-05-13','sparkplugs',0.42)

(7,'2015-05-16','AirFilter',32.71)

(7,'2015-07-19','Oil',6.04)

(7,'2015-11-13','Oil',22.25)

(7,'2015-11-29','Oil',12.95)

(7,'2015-12-10','radiatorflush',35.95)

(7,'2015-12-14','radiatorflush',49.45)

(7,'2015-12-15','radiatorflush',3.66)

(7,'2016-01-31','brakes',18.97)

(7,'2016-02-07','Oil',21.4)

(8,'2012-03-06','sparkplugs',23.34)

(8,'2012-03-21','radiatorflush',10.93)

(8,'2012-04-12','Tire',5.23)

(8,'2012-05-04','radiatorflush',34.33)

(8,'2012-07-14','AirFilter',33.52)

(8,'2012-10-23','AirFilter',10.93)

(8,'2012-11-13','radiatorflush',20.63)

(8,'2012-11-14','brakes',19.01)

(8,'2012-11-17','radiatorflush',8.47)

(8,'2012-12-29','brakes',12.95)

(8,'2013-01-02','Tire',20.63)

(8,'2013-01-31','brakes',2.81)

(8,'2013-02-27','sparkplugs',44.84)

(8,'2013-03-11','sparkplugs',19.01)

(8,'2013-06-12','brakes',39.99)

(8,'2013-07-05','Tire',30.29)

(8,'2013-08-13','Tire',23.34)

(8,'2013-10-03','sparkplugs',19.82)

(8,'2013-10-29','AirFilter',56.15)

(8,'2013-11-08','Oil',27.06)

(8,'2013-11-17','battery',23.86)

(8,'2013-12-20','brakes',3.66)

(8,'2014-01-02','sparkplugs',22.25)

(8,'2014-02-27','radiatorflush',13.36)

(8,'2014-03-08','brakes',7.66)

(8,'2014-04-12','brakes',59.39)

(8,'2014-06-10','Tire',56.96)

(8,'2014-06-18','Tire',39.18)

(8,'2014-07-01','Tire',14.16)

(8,'2014-07-07','Tire',39.99)

(8,'2014-08-31','Oil',44.84)

(8,'2014-09-02','sparkplugs',11.74)

(8,'2014-10-04','battery',44.03)

(8,'2014-10-25','sparkplugs',22.21)

(8,'2014-10-31','Tire',9.31)

(8,'2014-12-03','AirFilter',5.27)

(8,'2015-01-28','sparkplugs',19.78)

(8,'2015-02-28','Tire',29.48)

(8,'2015-03-11','sparkplugs',14.16)

(8,'2015-06-27','Tire',33.52)

(8,'2015-07-27','radiatorflush',2.85)

(8,'2015-08-13','AirFilter',23.86)

(8,'2015-08-27','brakes',14.93)

(8,'2015-09-09','AirFilter',39.99)

(8,'2015-09-30','sparkplugs',59.39)

(8,'2015-10-05','AirFilter',15.74)

(8,'2015-11-21','Tire',35.14)

(8,'2015-11-28','Tire',14.97)

(8,'2016-01-20','sparkplugs',36.76)

(8,'2016-03-05','battery',20.59)

(8,'2016-04-10','AirFilter',12.95)

(9,'2015-10-12','AirFilter',44.03)

(9,'2015-10-30','sparkplugs',38.37)

(9,'2015-11-04','Tire',50.5)

(9,'2015-11-17','battery',4.43)

(9,'2015-11-28','battery',7.66)

(9,'2015-12-22','battery',10.08)

(9,'2015-12-26','Oil',10.12)

(9,'2015-12-30','brakes',46.46)

(9,'2016-02-19','sparkplugs',55.35)

(9,'2016-03-04','sparkplugs',1.23)

(9,'2016-03-31','Tire',11.74)

(10,'2012-01-09','Oil',0.42)

(10,'2012-02-08','sparkplugs',3.62)

(10,'2012-02-10','brakes',35.95)

(10,'2012-03-12','Oil',4.43)

(10,'2012-03-25','brakes',14.97)

(10,'2012-04-30','battery',19.78)

(10,'2012-09-06','AirFilter',55.35)

(10,'2012-10-19','radiatorflush',56.96)

(10,'2012-11-01','AirFilter',12.95)

(10,'2012-11-19','brakes',20.59)

(10,'2012-11-22','Oil',33.52)

(10,'2012-12-15','Oil',6.08)

(10,'2013-01-11','AirFilter',21.4)

(10,'2013-03-22','radiatorflush',12.51)

(10,'2013-04-19','radiatorflush',48.07)

(10,'2013-05-14','Tire',11.7)

(10,'2013-06-12','radiatorflush',42.41)

(10,'2013-07-30','Oil',29.48)

(10,'2013-08-19','radiatorflush',6.04)

(10,'2013-09-25','Tire',2)

(10,'2013-11-03','radiatorflush',24.63)

(10,'2013-11-29','AirFilter',34.33)

(10,'2014-02-13','sparkplugs',42.41)

(10,'2014-04-04','AirFilter',7.7)

(10,'2014-06-08','AirFilter',65.36)

(10,'2014-07-14','AirFilter',10.08)

(10,'2014-09-30','brakes',14.16)

(10,'2014-10-03','AirFilter',11.7)

(10,'2014-10-24','sparkplugs',12.55)

(10,'2014-11-05','battery',38.37)

(10,'2014-11-29','sparkplugs',12.95)

(10,'2015-02-23','brakes',9.28)

(10,'2015-04-11','radiatorflush',2.81)

(10,'2015-05-02','battery',54.54)

(10,'2015-05-03','radiatorflush',19.78)

(10,'2015-08-07','battery',31.91)

(10,'2015-09-06','battery',3.66)

(10,'2015-09-08','radiatorflush',35.14)

(10,'2015-09-13','Oil',1.19)

(10,'2015-11-25','battery',27.06)

(10,'2016-01-18','radiatorflush',22.25)

(10,'2016-02-25','battery',59.39)

This is the data for vehicletable

Create Table VehicleTable(

Unitid interger not null

,VehicleName varchar(25) not null);

Insert Into vehicletable (unitid,vehiclename) values

(1,'FreightLiner C3000')

,(2,'Mack 8000')

,(3,'Volvo VNL350')

,(4,'Mack 8000')

,(5,'KenWorth T2000')

,(6,'Peterbuilt 379')

,(7,'FreightLiner Cascadia')

,(8,'International Pro Star')

,(9,'Volvo VNL 690')

,(10,'Western Star 490')

so what I need is for any vehicle that has had work done in the last 6 months, but no work prior to that to be identified as NewVehicle. Any vehicle that has had work done recently but also had work done in the prior year is an existing vehicle.

this would be the correct answer set

I could not figure out how to past the answer set in with tabs, so I attached the answer set. In the answer set I show no first sales date for existing vehicles, that is optional, I do not care if it displays or not. I did it in my example answer set to show that only one date is correct for that field. For example unitid 6 has 5 dates, but the earliest is 12/13/2015 so that is the date that matters.

when I run this code I get the correct answer set for 6 and 9 (in this example data the only two new vehicles)

Select VSA3.UnitID,VT1.VehicleName, Sum(VSA3.Amt)AS SixMonthsCosts,VSA3.type,VM1.FirstMaintenaceDateinLast6Months,'NewVehicle' AS Status

From (

Select VSA1.UnitID,sum(VSA1.Amt) AS CostAmt,min(VSA1.Servicedate) AS FirstMaintenaceDateinLast6Months

FROM Test.VehicleMaintenance VSA1

Where Cast (VSA1.Servicedate AS DATE) between DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY, -(DAY(GETDATE())), GETDATE())) - 5, 0) and DATEADD(DAY, -(DAY(GETDATE())), GETDATE())

AND NOT EXISTS (Select *

FROM Test.VehicleMaintenance VSA2

WHERE VSA1.UnitID=VSA2.UnitID AND Cast (VSA2.Servicedate AS DATE) between DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 12, 0) and DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 6, 0))

group by VSA1.UnitID) AS VM1

LEFT JOIN Test.VehicleMaintenance VSA3

ON VSA3.UnitID = VM1.UnitID

LEFT JOIN Test.VehicleTable VT1 on VT1.UnitID=VSA3.UnitID

WHERE Cast (VSA3.Servicedate AS DATE) between DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY, -(DAY(GETDATE())), GETDATE())) - 11, 0) and DATEADD(DAY, -(DAY(GETDATE())), GETDATE())

Group by VSA3.UnitID,VT1.VehicleName,VSA3.Type,VM1.FirstMaintenaceDateinLast6Months

randyetheridge SSCommitted Points: 1703 More actions · Answer 3

this is the full code. it is now working, but for existingvehicles it brings back multiple dates and I only want the earliest date (like new vehicles) In addition I am not sure if this is the most efficient code to do what I want

Select VSA3.UnitID,VT1.VehicleName, Sum(VSA3.Amt)AS SixMonthsCosts,VSA3.type,VM1.FirstMaintenaceDateinLast6Months,'NewVehicle' AS Status

From (

Select VSA1.UnitID,sum(VSA1.Amt) AS CostAmt,min(VSA1.Servicedate) AS FirstMaintenaceDateinLast6Months

FROM Test.VehicleMaintenance VSA1

Where Cast (VSA1.Servicedate AS DATE) between DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY, -(DAY(GETDATE())), GETDATE())) - 5, 0) and DATEADD(DAY, -(DAY(GETDATE())), GETDATE())

AND NOT EXISTS (Select *

FROM Test.VehicleMaintenance VSA2

WHERE VSA1.UnitID=VSA2.UnitID AND Cast (VSA2.Servicedate AS DATE) between DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 12, 0) and DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 6, 0))

group by VSA1.UnitID) AS VM1

LEFT JOIN Test.VehicleMaintenance VSA3

ON VSA3.UnitID = VM1.UnitID

LEFT JOIN Test.VehicleTable VT1 on VT1.UnitID=VSA3.UnitID

WHERE Cast (VSA3.Servicedate AS DATE) between DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY, -(DAY(GETDATE())), GETDATE())) - 11, 0) and DATEADD(DAY, -(DAY(GETDATE())), GETDATE())

Group by VSA3.UnitID,VT1.VehicleName,VSA3.Type,VM1.FirstMaintenaceDateinLast6Months

UNION ALL

Select VSA3.UnitID,VT1.VehicleName,Sum(VSA3.Amt)AS SixMonthsCosts,VSA3.Type,min(VSA3.ServiceDate) AS FirstMaintenaceDateinLast6Months,'ExtVehicle' AS Status

FROM Test.VehicleMaintenance VSA3

LEFT JOIN Test.VehicleTable VT1 on VT1.UnitID=VSA3.UnitID

Where VSA3.UnitID NOT IN (

(Select VSA1.UnitID

FROM Test.VehicleMaintenance VSA1

Where Cast (VSA1.Servicedate AS DATE) between DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY, -(DAY(GETDATE())), GETDATE())) - 5, 0) and DATEADD(DAY, -(DAY(GETDATE())), GETDATE())

AND NOT EXISTS (Select *

FROM Test.VehicleMaintenance VSA2

WHERE VSA1.UnitID=VSA2.UnitID AND Cast (VSA2.Servicedate AS DATE) between DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 12, 0) and DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 6, 0))

group by VSA1.UnitID))

Group by VSA3.UnitID,VSA3.Type,VT1.VehicleName

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 4

A couple things.

1) The posted script for creating the data won't parse (a typo in "integer" and no commas separating the VALUES for the first INSERT).

2) Both the total costs and first maintenance columns in the result set mention the last 6 months, but both seem to be for all time. That is, the last 6 months cost is just the cost for that vehicle and type going back indefinitely; it is not restricted to the last 6 months. Ditto for the first maintenance date; it's not the first maintenance date in the last 6 months. Instead it's the first maintenance date, period.

Is that behavior intended and the columns misleadingly name, or are the results actually not what you intended?

This can be done much more simply, but I'd like to be clear on the requirements first.

Cheers!

randyetheridge SSCommitted Points: 1703 More actions · Answer 5

A couple things.

1) The posted script for creating the data won't parse (a typo in "integer" and no commas separating the VALUES for the first INSERT).

2) Both the total costs and first maintenance columns in the result set mention the last 6 months, but both seem to be for all time. That is, the last 6 months cost is just the cost for that vehicle and type going back indefinitely; it is not restricted to the last 6 months. Ditto for the first maintenance date; it's not the first maintenance date in the last 6 months. Instead it's the first maintenance date, period.

Is that behavior intended and the columns misleadingly name, or are the results actually not what you intended?

This can be done much more simply, but I'd like to be clear on the requirements first

1. well shoot I had the , For some reason it did not come over in the past. sorry about that.

2. Yes my code is incorrect for the cost it should only be the last 6 months costs.

2. For the date of the existing customers Yes it will be the original date and that will be prior to the last 6 months. Again having the date for existing vehicles is not a necessity. I just have it in mine because I could not figure out how to get rid of it. But if it is easy to include, it would be a nice feature to have. But as you noted the date will be the first date period.

3. Yes I figure there must be a more efficient method, but I have not been able to figure it out. I am looking at your first code.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 6

No problem. It's easy to get turned around when working on a problem like this 🙂

Here's one try. Based on the verbal description of the rules I think this is right, but it can always be tweaked if not.

SELECT vt.UnitID,

vt.VehicleName,

six_months_costs=SUM(

CASE WHEN vm.servicedate>=DATEADD(mm,DATEDIFF(mm,0,GETDATE())-6,0)

THEN vm.amt

ELSE 0

END

),

vm.[type],

[Status]=CASE WHEN first_maint.firstmaintenance>=DATEADD(mm,DATEDIFF(mm,0,GETDATE())-6,0)

THEN 'NewVehicle'

ELSE 'ExtVehicle'

END,

first_maintenance_date=first_maint.firstmaintenance

FROM VehicleMaintenance vm

INNER JOIN

VehicleTable vt ON vm.Unitid=vt.Unitid

INNER JOIN

(

SELECT unitid,

firstmaintenance=MIN(servicedate)

FROM VehicleMaintenance

GROUP BY unitid

)first_maint ON vt.Unitid=first_maint.Unitid

GROUP BY vt.Unitid,vt.VehicleName,

vm.type,

first_maint.firstmaintenance;

Also, if you could edit the code you posted and put it in code tags (you can type them in or select them from the area to the left of the text area when posting), that would help cut down on all the vertical space it occupies.

Cheers!

randyetheridge SSCommitted Points: 1703 More actions · Answer 7

so yes that worked, and as you noted so much simpler than my solution. I will "reverse engineer' you SQL statement to understand how it is working.

Not sure I understand what you mean by "code tag" but I will figure it out.

Again thanks for your patience, I now know how to post a complex question in the correct fashion the first time, again many thanks.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 8

I'm glad that seems to be working for you!

By "code tags" I just mean putting before your code, and [/code!] after it (with the '!' removed from both; I added that so you could see the tags without their functionality kicking in). That formats it like my code in the previous post, which has a few advantages (in this case, the main one is that code samples that otherwise take a large amount of vertical space on the page are now just in a scrollable window).

If after pondering it for a bit some parts of the query are still unclear, just ask and we'll be happy to help.

Cheers!

EDIT: Fixed a typo.

randyetheridge SSCommitted Points: 1703 More actions · Answer 9

got it thanks, I will post here to practice, Again many thanks

randyetheridge SSCommitted Points: 1703 More actions · Answer 10

just a test of the tags

code=SQL

Select FSA3.CustAccount,CT1.WIKA_DUNBRAD, Sum(FSA3.LineAmount)AS Last12MonthsSalesAmt,FSA3.ITEMID,x.FirstSaleDateinLast12Months,'NewCustomer' AS Status

From (

Select Cast (getdate() AS DATE) AS SnapShotDateCustaccount,FSA1.CUSTACCOUNT,sum(FSA1.lineamount) AS AmtLocalCurrency,sum(FSA1.lineamount) AS AmtUSD,min(FSA1.invoicedate) AS FirstSaleDateinLast12Months,DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY, -(DAY(GETDATE())), GETDATE())) - 11, 0) AS TwelveMonthsAgo,DATEADD(DAY, -(DAY(GETDATE())), GETDATE()) AS LastMonth

FROM Fact_SalesActual FSA1

Where Cast (FSA1.Invoicedate AS DATE) between DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY, -(DAY(GETDATE())), GETDATE())) - 11, 0) and DATEADD(DAY, -(DAY(GETDATE())), GETDATE())

AND NOT EXISTS (Select *

FROM Fact_SalesActual FSA2

WHERE FSA1.CustAccount=FSA2.CustAccount AND Cast (FSA2.Invoicedate AS DATE) between DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 36, 0) and DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 12, 0))

group by FSA1.Custaccount) X

LEFT JOIN FACT_SalesActual FSA3

ON FSA3.CustAccount = x.CUSTACCOUNT

LEFT JOIN dbo.custtable CT1 on CT1.Accountnum=FSA3.CustAccount

WHERE Cast (FSA3.Invoicedate AS DATE) between DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY, -(DAY(GETDATE())), GETDATE())) - 11, 0) and DATEADD(DAY, -(DAY(GETDATE())), GETDATE())

Group by FSA3.CustAccount,CT1.WIKA_DunBrad,FSA3.Itemid,x.FirstSaleDateinLast12Months

UNION ALL

Select FSA3.CustAccount,CT1.WIKA_DUNBRAD,Sum(FSA3.LineAmount)AS SixMonthsCosts,FSA3.ITEMID,min(FSA3.InvoiceDate) AS FirstSaleDateinLast12Months,'ExistingCustomer' AS Status

FROM Fact_SalesActual FSA3

LEFT JOIN Custtable CT1 on CT1.AccountNum=FSA3.CustAccount

Where FSA3.CustAccount NOT IN (

(Select FSA1.CustAccount

FROM Fact_SalesActual FSA1

Where Cast (FSA1.InvoiceDate AS DATE) between DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY, -(DAY(GETDATE())), GETDATE())) - 11, 0) and DATEADD(DAY, -(DAY(GETDATE())), GETDATE())

AND NOT EXISTS (Select *

FROM Fact_SalesActual FSA2

Where Cast (FSA2.InvoiceDate AS DATE) between DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 36, 0) and DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 12, 0))

group by FSA1.CustAccount))

Group by FSA3.CustAccount,FSA3.ItemId,CT1.WIKA_DunBrad

/code

randyetheridge SSCommitted Points: 1703 More actions · Answer 11

just test code tags

Select FSA3.CustAccount,CT1.WIKA_DUNBRAD, Sum(FSA3.LineAmount)AS Last12MonthsSalesAmt,FSA3.ITEMID,x.FirstSaleDateinLast12Months,'NewCustomer' AS Status

From (

Select Cast (getdate() AS DATE) AS SnapShotDateCustaccount,FSA1.CUSTACCOUNT,sum(FSA1.lineamount) AS AmtLocalCurrency,sum(FSA1.lineamount) AS AmtUSD,min(FSA1.invoicedate) AS FirstSaleDateinLast12Months,DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY, -(DAY(GETDATE())), GETDATE())) - 11, 0) AS TwelveMonthsAgo,DATEADD(DAY, -(DAY(GETDATE())), GETDATE()) AS LastMonth

FROM Fact_SalesActual FSA1

Where Cast (FSA1.Invoicedate AS DATE) between DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY, -(DAY(GETDATE())), GETDATE())) - 11, 0) and DATEADD(DAY, -(DAY(GETDATE())), GETDATE())

AND NOT EXISTS (Select *

FROM Fact_SalesActual FSA2

WHERE FSA1.CustAccount=FSA2.CustAccount AND Cast (FSA2.Invoicedate AS DATE) between DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 36, 0) and DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 12, 0))

group by FSA1.Custaccount) X

LEFT JOIN FACT_SalesActual FSA3

ON FSA3.CustAccount = x.CUSTACCOUNT

LEFT JOIN dbo.custtable CT1 on CT1.Accountnum=FSA3.CustAccount

WHERE Cast (FSA3.Invoicedate AS DATE) between DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY, -(DAY(GETDATE())), GETDATE())) - 11, 0) and DATEADD(DAY, -(DAY(GETDATE())), GETDATE())

Group by FSA3.CustAccount,CT1.WIKA_DunBrad,FSA3.Itemid,x.FirstSaleDateinLast12Months

UNION ALL

Select FSA3.CustAccount,CT1.WIKA_DUNBRAD,Sum(FSA3.LineAmount)AS SixMonthsCosts,FSA3.ITEMID,min(FSA3.InvoiceDate) AS FirstSaleDateinLast12Months,'ExistingCustomer' AS Status

FROM Fact_SalesActual FSA3

LEFT JOIN Custtable CT1 on CT1.AccountNum=FSA3.CustAccount

Where FSA3.CustAccount NOT IN (

(Select FSA1.CustAccount

FROM Fact_SalesActual FSA1

Where Cast (FSA1.InvoiceDate AS DATE) between DATEADD(mm, DATEDIFF(mm, 0, DATEADD(DAY, -(DAY(GETDATE())), GETDATE())) - 11, 0) and DATEADD(DAY, -(DAY(GETDATE())), GETDATE())

AND NOT EXISTS (Select *

FROM Fact_SalesActual FSA2

Where Cast (FSA2.InvoiceDate AS DATE) between DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 36, 0) and DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 12, 0))

group by FSA1.CustAccount))

Group by FSA3.CustAccount,FSA3.ItemId,CT1.WIKA_DunBrad