April 19, 2016 at 3:34 pm
I have a freight delivery company. Need to look at the last six months of service records and then sum up the costs. The hard part has been identifying the new service vehicles. So if a vehicle has had it's first service in the last 6 months then it is new, but if it had service prior to that then it is an existing vehicle. This is primarily due to the rebuild and service time (up to 8 months) that a fleet truck can be out of service with no repairs.
any way attached is an excel file with the table field names, example data and expected results and a word document with my attempt. The SQL statement is interesting, if I run the separate sections, it actually returns the correct data. But if I run the complete statement I do not get the correct data set.
any help is appreciated.
April 20, 2016 at 8:23 am
First, it's helpful to give some mockup like this, using some sample data:
CREATE TABLE Service
(
UnitID INT
, Servicedate DATE
, ServiceType VARCHAR(200)
, Amount money
);
GO
INSERT INTO dbo.Service
VALUES
(1, '2012/1/9', 'AirFilter', 19.01)
, (1, '3/22/2016', 'Tire', 27.87)
, (2, '1/10/2012', 'radiatorflush', 15.78)
, (2, '1/17/2012', 'battery', 7.7)
, (9,'3/31/2016', 'Tire',11.74)
, (10, '1/9/2012', 'Oil',0.42)
GO
Next, show what you tried and the results. It's easier to do that here, with the plain code format.
When you say the pieces run correctly, what pieces? What sections return the correct data?
April 20, 2016 at 8:32 am
I attached a word document with my SQL code and an excel file with table data and expected results? When I say the pieces run, I mean if I run the first statement it returns the data set for unitId 6 & 9. When I run the SQL statement AFTER the UNION ALL, it returns the answer set for unitid 1,2,3,4,5,7,8 &10
But when I run the whole statement I only get the answer set for 6 & 9. Plus it runs a very long time so I am thinking there is a more efficient coding process.
I hope this helps.
April 20, 2016 at 8:38 am
The excel data doesn't allow anyone to set this up, nor does the Word document make it easy to understand what you did.
It's helpful to say, I run this:
select unitid
from vehiclemaintenance
and it works, but when I add in this:
union all
select unitid from newmaintenance
where id = 1
it doesn't work.
You're asking for help, so help us help you.
In terms of the UNION not working, I can only guess that somehow you are executing things incorrectly or you've got some parents that don't match up. The query is a bit of a mess, and the more you can simplify, the better. UNION ALL should just put two result sets together.
April 20, 2016 at 9:24 am
randyetheridge (4/20/2016)
I attached a word document with my SQL code and an excel file with table data and expected results? When I say the pieces run, I mean if I run the first statement it returns the data set for unitId 6 & 9. When I run the SQL statement AFTER the UNION ALL, it returns the answer set for unitid 1,2,3,4,5,7,8 &10But when I run the whole statement I only get the answer set for 6 & 9. Plus it runs a very long time so I am thinking there is a more efficient coding process.
I hope this helps.
first off I suggest you read this article and repost some data that we can easily use
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
looking at your excel file.....you have provided two distinct sets of columns... cols A-D I assume are a data set, but for which table?........cols H-L I assume are your expected results...is this correct?
If this is correct I dont see how you are calculating the "Amt Last Six Month"....just a quick filter in excel determines this isnt correct (try unitid = '1' and 'Airfilter')....you appear to have summed all records for these filters not last six months.
I hope you appreciate that posting excel/word docs into a SQL forum and expecting others to do your basic work for a question, results in very few responses
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 20, 2016 at 11:58 am
In an effort to help you help us and also so that hopefully you will learn with any future posts that you make....here is my "guess" at what your sample table "VehicleMaintenance" is......
if this is correct can we move on to you please providing the necessary additional columns and tables that the word doc SQL references eg table "VehicleTable "
USE Tempdb
GO
IF OBJECT_ID('tempdb..VehicleMaintenance', 'U') IS NOT NULL
DROP TABLE tempdb..VehicleMaintenance ;
CREATE TABLE VehicleMaintenance(
unitid INTEGER 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-10','Oil',34.33)
,(1,'2012-01-11','Tire',57.77)
,(1,'2012-01-12','Oil',40.8)
,(1,'2012-01-13','Oil',45.65)
,(1,'2012-01-14','battery',36.76)
,(1,'2012-01-15','battery',19.01)
,(1,'2012-01-16','sparkplugs',65.36)
,(1,'2012-01-17','Tire',13.32)
,(1,'2012-01-18','Oil',5.23)
,(1,'2012-01-19','brakes',4.47)
,(1,'2012-01-20','brakes',9.31)
,(1,'2012-01-21','brakes',12.51)
,(1,'2012-01-22','radiatorflush',11.7)
,(1,'2012-01-23','sparkplugs',53.73)
,(1,'2012-01-24','Tire',18.17)
,(1,'2012-01-25','battery',14.12)
,(1,'2012-01-26','radiatorflush',2)
,(1,'2012-01-27','Tire',39.99)
,(1,'2012-01-28','battery',47.26)
,(1,'2012-01-29','AirFilter',15.78)
,(1,'2012-01-30','sparkplugs',23.34)
,(1,'2012-01-31','radiatorflush',29.48)
,(1,'2012-02-01','radiatorflush',52.92)
,(1,'2012-02-02','AirFilter',3.62)
,(1,'2012-02-03','Oil',12.95)
,(1,'2012-02-04','brakes',8.51)
,(1,'2012-02-05','AirFilter',1.23)
,(1,'2012-02-06','Oil',17.36)
,(1,'2012-02-07','Tire',44.84)
,(1,'2012-02-08','brakes',43.22)
,(1,'2012-02-09','brakes',47.26)
,(1,'2012-02-10','radiatorflush',53.73)
,(1,'2012-02-11','radiatorflush',0.38)
,(1,'2012-02-12','AirFilter',31.91)
,(1,'2012-02-13','AirFilter',22.25)
,(1,'2012-02-14','AirFilter',43.22)
,(1,'2012-02-15','radiatorflush',3.65)
,(1,'2012-02-16','Oil',27.87)
,(1,'2012-02-17','AirFilter',14.74)
,(1,'2012-02-18','AirFilter',51.3)
,(1,'2012-02-19','radiatorflush',28.67)
,(1,'2012-02-20','sparkplugs',21.4)
,(1,'2012-02-21','Oil',56.15)
,(1,'2012-02-22','brakes',54.54)
,(1,'2012-02-23','radiatorflush',14.12)
,(1,'2012-02-24','radiatorflush',19.01)
,(1,'2012-02-25','sparkplugs',23.86)
,(1,'2012-02-26','Tire',3.65)
,(1,'2012-02-27','Oil',17.4)
,(1,'2012-02-28','Tire',21.44)
,(1,'2012-02-29','Tire',1.19)
,(1,'2012-03-01','sparkplugs',27.06)
,(1,'2012-03-02','Tire',4.47)
,(1,'2012-03-03','battery',49.69)
,(1,'2012-03-04','battery',18.21)
,(1,'2012-03-05','AirFilter',39.99)
,(1,'2012-03-06','AirFilter',45.65)
,(1,'2012-03-07','Oil',49.45)
,(1,'2012-03-08','sparkplugs',14.12)
,(1,'2012-03-09','battery',14.74)
,(1,'2012-03-10','AirFilter',12.55)
,(1,'2012-03-11','Tire',27.87)
,(2,'2012-03-12','radiatorflush',15.78)
,(2,'2012-03-13','battery',7.7)
,(2,'2012-03-14','Oil',11.7)
,(2,'2012-03-15','sparkplugs',7.7)
,(2,'2012-03-16','brakes',12.95)
,(2,'2012-03-17','battery',49.45)
,(2,'2012-03-18','battery',21.4)
,(2,'2012-03-19','Tire',28.67)
,(2,'2012-03-20','brakes',23.86)
,(2,'2012-03-21','brakes',30.29)
,(2,'2012-03-22','brakes',3.65)
,(2,'2012-03-23','Tire',47.26)
,(2,'2012-03-24','AirFilter',44.84)
,(2,'2012-03-25','Tire',56.15)
,(2,'2012-03-26','AirFilter',23.34)
,(2,'2012-03-27','brakes',58.58)
,(2,'2012-03-28','Oil',52.11)
,(2,'2012-03-29','radiatorflush',19.82)
,(2,'2012-03-30','radiatorflush',25.44)
,(2,'2012-03-31','AirFilter',38.37)
,(2,'2012-04-01','Oil',49.69)
,(2,'2012-04-02','sparkplugs',8.47)
,(2,'2012-04-03','sparkplugs',2.81)
,(2,'2012-04-04','brakes',13.32)
,(2,'2012-04-05','sparkplugs',13.36)
,(2,'2012-04-06','Tire',5.27)
,(2,'2012-04-07','Tire',17.36)
,(2,'2012-04-08','sparkplugs',37.56)
,(2,'2012-04-09','battery',30.29)
,(2,'2012-04-10','brakes',40.8)
,(2,'2012-04-11','battery',58.58)
,(2,'2012-04-12','battery',13.32)
,(2,'2012-04-13','battery',37.56)
,(2,'2012-04-14','battery',8.47)
,(2,'2012-04-15','radiatorflush',40.8)
,(2,'2012-04-16','battery',41.61)
,(2,'2012-04-17','brakes',35.14)
,(2,'2012-04-18','sparkplugs',3.65)
,(2,'2012-04-19','brakes',48.07)
,(2,'2012-04-20','Tire',52.11)
,(2,'2012-04-21','Tire',3.66)
,(2,'2012-04-22','radiatorflush',52.11)
,(2,'2012-04-23','sparkplugs',16.55)
,(2,'2012-04-24','brakes',24.63)
,(2,'2012-04-25','sparkplugs',16.59)
,(2,'2012-04-26','brakes',57.77)
,(2,'2012-04-27','Tire',18.97)
,(3,'2012-04-28','AirFilter',18.21)
,(3,'2012-04-29','AirFilter',20.59)
,(3,'2012-04-30','Oil',14.97)
,(3,'2012-05-01','battery',2.81)
,(3,'2012-05-02','sparkplugs',23.06)
,(3,'2012-05-03','Tire',22.25)
,(3,'2012-05-04','brakes',2.04)
,(3,'2012-05-05','brakes',19.78)
,(3,'2012-05-06','Oil',23.06)
,(3,'2012-05-07','Oil',56.96)
,(3,'2012-05-08','radiatorflush',23.34)
,(3,'2012-05-09','brakes',10.12)
,(3,'2012-05-10','Tire',49.45)
,(3,'2012-05-11','sparkplugs',14.74)
,(3,'2012-05-12','Tire',65.36)
,(3,'2012-05-13','sparkplugs',48.07)
,(3,'2012-05-14','battery',23.06)
,(3,'2012-05-15','Oil',38.37)
,(3,'2012-05-16','brakes',36.76)
,(3,'2012-05-17','AirFilter',14.74)
,(3,'2012-05-18','radiatorflush',9.31)
,(3,'2012-05-19','radiatorflush',16.59)
,(3,'2012-05-20','Oil',28.67)
,(3,'2012-05-21','Oil',15.74)
,(3,'2012-05-22','Tire',23.82)
,(3,'2012-05-23','sparkplugs',10.08)
,(3,'2012-05-24','battery',14.74)
,(3,'2012-05-25','brakes',8.47)
,(3,'2012-05-26','AirFilter',6.89)
,(3,'2012-05-27','sparkplugs',2.85)
,(3,'2012-05-28','Oil',12.55)
,(3,'2012-05-29','AirFilter',26.25)
,(3,'2012-05-30','battery',17.4)
,(3,'2012-05-31','Oil',35.14)
,(3,'2012-06-01','Oil',12.51)
,(3,'2012-06-02','radiatorflush',39.99)
,(3,'2012-06-03','sparkplugs',8.51)
,(3,'2012-06-04','brakes',2)
,(3,'2012-06-05','brakes',20.63)
,(3,'2012-06-06','Oil',15.78)
,(3,'2012-06-07','battery',8.51)
,(3,'2012-06-08','sparkplugs',31.91)
,(3,'2012-06-09','battery',3.65)
,(3,'2012-06-10','battery',52.92)
,(3,'2012-06-11','sparkplugs',5.23)
,(3,'2012-06-12','brakes',6.85)
,(3,'2012-06-13','AirFilter',6.08)
,(3,'2012-06-14','battery',43.22)
,(3,'2012-06-15','sparkplugs',3.65)
,(3,'2012-06-16','Tire',19.82)
,(4,'2012-06-17','Oil',23.34)
,(4,'2012-06-18','sparkplugs',44.03)
,(4,'2012-06-19','brakes',52.11)
,(4,'2012-06-20','brakes',48.88)
,(4,'2012-06-21','radiatorflush',7.66)
,(4,'2012-06-22','radiatorflush',17.36)
,(4,'2012-06-23','Oil',10.93)
,(4,'2012-06-24','AirFilter',39.99)
,(4,'2012-06-25','sparkplugs',33.52)
,(4,'2012-06-26','radiatorflush',18.17)
,(4,'2012-06-27','battery',9.31)
,(4,'2012-06-28','radiatorflush',13.32)
,(4,'2012-06-29','AirFilter',0.38)
,(4,'2012-06-30','battery',42.41)
,(4,'2012-07-01','Tire',39.99)
,(4,'2012-07-02','battery',2)
,(4,'2012-07-03','Tire',12.51)
,(4,'2012-07-04','radiatorflush',59.39)
,(4,'2012-07-05','AirFilter',17.36)
,(4,'2012-07-06','brakes',15.78)
,(4,'2012-07-07','Tire',46.46)
,(4,'2012-07-08','battery',18.97)
,(4,'2012-07-09','radiatorflush',39.99)
,(4,'2012-07-10','radiatorflush',58.58)
,(4,'2012-07-11','battery',53.73)
,(4,'2012-07-12','radiatorflush',23.34)
,(4,'2012-07-13','Oil',6.85)
,(4,'2012-07-14','sparkplugs',49.69)
,(4,'2012-07-15','Oil',65.36)
,(4,'2012-07-16','sparkplugs',17.4)
,(4,'2012-07-17','AirFilter',17.4)
,(4,'2012-07-18','battery',6.89)
,(4,'2012-07-19','AirFilter',50.5)
,(4,'2012-07-20','brakes',31.1)
,(4,'2012-07-21','battery',20.63)
,(4,'2012-07-22','brakes',1.19)
,(4,'2012-07-23','battery',48.07)
,(4,'2012-07-24','radiatorflush',51.3)
,(4,'2012-07-25','sparkplugs',25.44)
,(4,'2012-07-26','battery',14.93)
,(4,'2012-07-27','brakes',39.99)
,(4,'2012-07-28','sparkplugs',50.5)
,(4,'2012-07-29','Oil',44.03)
,(4,'2012-07-30','sparkplugs',12.95)
,(4,'2012-07-31','brakes',29.48)
,(5,'2012-08-01','Tire',34.33)
,(5,'2012-08-02','radiatorflush',18.97)
,(5,'2012-08-03','sparkplugs',26.25)
,(5,'2012-08-04','brakes',18.17)
,(5,'2012-08-05','Oil',23.82)
,(5,'2012-08-06','AirFilter',16.55)
,(5,'2012-08-07','Oil',23.34)
,(5,'2012-08-08','AirFilter',39.18)
,(5,'2012-08-09','Oil',39.99)
,(5,'2012-08-10','battery',14.97)
,(5,'2012-08-11','battery',48.88)
,(5,'2012-08-12','Oil',39.18)
,(5,'2012-08-13','AirFilter',49.69)
,(5,'2012-08-14','battery',12.55)
,(5,'2012-08-15','battery',3.65)
,(5,'2012-08-16','sparkplugs',65.36)
,(5,'2012-08-17','radiatorflush',57.77)
,(5,'2012-08-18','AirFilter',5.23)
,(5,'2012-08-19','AirFilter',27.87)
,(5,'2012-08-20','AirFilter',65.36)
,(5,'2012-08-21','Tire',40.8)
,(5,'2012-08-22','Oil',22.21)
,(5,'2012-08-23','brakes',14.12)
,(5,'2012-08-24','AirFilter',4.43)
,(5,'2012-08-25','brakes',12.55)
,(5,'2012-08-26','AirFilter',56.96)
,(5,'2012-08-27','Oil',1.23)
,(5,'2012-08-28','AirFilter',27.06)
,(5,'2012-08-29','battery',24.63)
,(5,'2012-08-30','battery',25.44)
,(5,'2012-08-31','brakes',19.82)
,(5,'2012-09-01','battery',1.23)
,(5,'2012-09-02','battery',65.36)
,(5,'2012-09-03','brakes',42.41)
,(5,'2012-09-04','Tire',35.95)
,(5,'2012-09-05','radiatorflush',6.85)
,(5,'2012-09-06','battery',3.62)
,(5,'2012-09-07','Tire',24.63)
,(5,'2012-09-08','brakes',37.56)
,(5,'2012-09-09','Oil',23.02)
,(5,'2012-09-10','battery',2.85)
,(5,'2012-09-11','Oil',5.27)
,(5,'2012-09-12','Tire',10.93)
,(5,'2012-09-13','Oil',46.46)
,(5,'2012-09-14','sparkplugs',56.15)
,(5,'2012-09-15','AirFilter',14.93)
,(5,'2012-09-16','AirFilter',0.42)
,(5,'2012-09-17','radiatorflush',14.16)
,(5,'2012-09-18','sparkplugs',39.18)
,(5,'2012-09-19','Oil',18.17)
,(5,'2012-09-20','brakes',21.44)
,(5,'2012-09-21','AirFilter',22.21)
,(5,'2012-09-22','AirFilter',2.04)
,(5,'2012-09-23','Oil',57.77)
,(5,'2012-09-24','brakes',7.7)
,(6,'2012-09-25','radiatorflush',30.29)
,(6,'2012-09-26','Tire',7.66)
,(6,'2012-09-27','radiatorflush',36.76)
,(6,'2012-09-28','brakes',26.25)
,(6,'2012-09-29','brakes',52.92)
,(7,'2012-09-30','AirFilter',23.34)
,(7,'2012-10-01','Oil',18.21)
,(7,'2012-10-02','battery',31.1)
,(7,'2012-10-03','AirFilter',11.74)
,(7,'2012-10-04','brakes',3.65)
,(7,'2012-10-05','Tire',10.12)
,(7,'2012-10-06','radiatorflush',49.45)
,(7,'2012-10-07','Oil',6.89)
,(7,'2012-10-08','Tire',16.55)
,(7,'2012-10-09','radiatorflush',23.82)
,(7,'2012-10-10','Oil',65.36)
,(7,'2012-10-11','Tire',15.78)
,(7,'2012-10-12','radiatorflush',41.61)
,(7,'2012-10-13','Oil',50.5)
,(7,'2012-10-14','brakes',14.74)
,(7,'2012-10-15','Tire',58.58)
,(7,'2012-10-16','brakes',14.74)
,(7,'2012-10-17','AirFilter',54.54)
,(7,'2012-10-18','battery',14.16)
,(7,'2012-10-19','radiatorflush',10.12)
,(7,'2012-10-20','Tire',0.42)
,(7,'2012-10-21','brakes',31.91)
,(7,'2012-10-22','Tire',6.04)
,(7,'2012-10-23','Oil',49.45)
,(7,'2012-10-24','AirFilter',37.56)
,(7,'2012-10-25','radiatorflush',21.44)
,(7,'2012-10-26','Tire',49.45)
,(7,'2012-10-27','radiatorflush',5.27)
,(7,'2012-10-28','sparkplugs',18.21)
,(7,'2012-10-29','battery',32.71)
,(7,'2012-10-30','Tire',45.65)
,(7,'2012-10-31','AirFilter',48.88)
,(7,'2012-11-01','radiatorflush',8.51)
,(7,'2012-11-02','Tire',6.85)
,(7,'2012-11-03','Oil',21.44)
,(7,'2012-11-04','sparkplugs',6.08)
,(7,'2012-11-05','Oil',39.99)
,(7,'2012-11-06','sparkplugs',0.42)
,(7,'2012-11-07','AirFilter',32.71)
,(7,'2012-11-08','Oil',6.04)
,(7,'2012-11-09','Oil',22.25)
,(7,'2012-11-10','Oil',12.95)
,(7,'2012-11-11','radiatorflush',35.95)
,(7,'2012-11-12','radiatorflush',49.45)
,(7,'2012-11-13','radiatorflush',3.66)
,(7,'2012-11-14','brakes',18.97)
,(7,'2012-11-15','Oil',21.4)
,(8,'2012-11-16','sparkplugs',23.34)
,(8,'2012-11-17','radiatorflush',10.93)
,(8,'2012-11-18','Tire',5.23)
,(8,'2012-11-19','radiatorflush',34.33)
,(8,'2012-11-20','AirFilter',33.52)
,(8,'2012-11-21','AirFilter',10.93)
,(8,'2012-11-22','radiatorflush',20.63)
,(8,'2012-11-23','brakes',19.01)
,(8,'2012-11-24','radiatorflush',8.47)
,(8,'2012-11-25','brakes',12.95)
,(8,'2012-11-26','Tire',20.63)
,(8,'2012-11-27','brakes',2.81)
,(8,'2012-11-28','sparkplugs',44.84)
,(8,'2012-11-29','sparkplugs',19.01)
,(8,'2012-11-30','brakes',39.99)
,(8,'2012-12-01','brakes',49.45)
,(8,'2012-12-02','Tire',30.29)
,(8,'2012-12-03','Tire',23.34)
,(8,'2012-12-04','sparkplugs',19.82)
,(8,'2012-12-05','AirFilter',56.15)
,(8,'2012-12-06','Oil',27.06)
,(8,'2012-12-07','battery',23.86)
,(8,'2012-12-08','brakes',3.66)
,(8,'2012-12-09','sparkplugs',22.25)
,(8,'2012-12-10','radiatorflush',13.36)
,(8,'2012-12-11','brakes',7.66)
,(8,'2012-12-12','brakes',59.39)
,(8,'2012-12-13','Tire',56.96)
,(8,'2012-12-14','Tire',39.18)
,(8,'2012-12-15','Tire',14.16)
,(8,'2012-12-16','Tire',39.99)
,(8,'2012-12-17','Oil',44.84)
,(8,'2012-12-18','sparkplugs',11.74)
,(8,'2012-12-19','battery',44.03)
,(8,'2012-12-20','sparkplugs',22.21)
,(8,'2012-12-21','Tire',9.31)
,(8,'2012-12-22','AirFilter',5.27)
,(8,'2012-12-23','sparkplugs',19.78)
,(8,'2012-12-24','Tire',29.48)
,(8,'2012-12-25','sparkplugs',14.16)
,(8,'2012-12-26','Tire',33.52)
,(8,'2012-12-27','radiatorflush',2.85)
,(8,'2012-12-28','AirFilter',23.86)
,(8,'2012-12-29','brakes',14.93)
,(8,'2012-12-30','AirFilter',39.99)
,(8,'2012-12-31','sparkplugs',59.39)
,(8,'2013-01-01','AirFilter',15.74)
,(8,'2013-01-02','Tire',35.14)
,(8,'2013-01-03','Tire',14.97)
,(8,'2013-01-04','sparkplugs',36.76)
,(8,'2013-01-05','battery',20.59)
,(8,'2013-01-06','AirFilter',12.95)
,(9,'2013-01-07','AirFilter',44.03)
,(9,'2013-01-08','sparkplugs',38.37)
,(9,'2013-01-09','Tire',50.5)
,(9,'2013-01-10','battery',4.43)
,(9,'2013-01-11','battery',7.66)
,(9,'2013-01-12','battery',10.08)
,(9,'2013-01-13','Oil',10.12)
,(9,'2013-01-14','brakes',46.46)
,(9,'2013-01-15','sparkplugs',55.35)
,(9,'2013-01-16','sparkplugs',1.23)
,(9,'2013-01-17','Tire',11.74)
,(10,'2013-01-18','Oil',0.42)
,(10,'2013-01-19','sparkplugs',3.62)
,(10,'2013-01-20','brakes',35.95)
,(10,'2013-01-21','Oil',4.43)
,(10,'2013-01-22','brakes',14.97)
,(10,'2013-01-23','battery',19.78)
,(10,'2013-01-24','AirFilter',55.35)
,(10,'2013-01-25','radiatorflush',56.96)
,(10,'2013-01-26','AirFilter',12.95)
,(10,'2013-01-27','brakes',20.59)
,(10,'2013-01-28','Oil',33.52)
,(10,'2013-01-29','Oil',6.08)
,(10,'2013-01-30','AirFilter',21.4)
,(10,'2013-01-31','radiatorflush',12.51)
,(10,'2013-02-01','radiatorflush',48.07)
,(10,'2013-02-02','Tire',11.7)
,(10,'2013-02-03','radiatorflush',42.41)
,(10,'2013-02-04','Oil',29.48)
,(10,'2013-02-05','radiatorflush',6.04)
,(10,'2013-02-06','Tire',2)
,(10,'2013-02-07','radiatorflush',24.63)
,(10,'2013-02-08','AirFilter',34.33)
,(10,'2013-02-09','sparkplugs',42.41)
,(10,'2013-02-10','AirFilter',7.7)
,(10,'2013-02-11','AirFilter',65.36)
,(10,'2013-02-12','AirFilter',10.08)
,(10,'2013-02-13','brakes',14.16)
,(10,'2013-02-14','AirFilter',11.7)
,(10,'2013-02-15','sparkplugs',12.55)
,(10,'2013-02-16','battery',38.37)
,(10,'2013-02-17','sparkplugs',12.95)
,(10,'2013-02-18','brakes',9.28)
,(10,'2013-02-19','radiatorflush',2.81)
,(10,'2013-02-20','battery',54.54)
,(10,'2013-02-21','radiatorflush',19.78)
,(10,'2013-02-22','battery',31.91)
,(10,'2013-02-23','battery',3.66)
,(10,'2013-02-24','radiatorflush',35.14)
,(10,'2013-02-25','Oil',1.19)
,(10,'2013-02-26','battery',27.06)
,(10,'2013-02-27','radiatorflush',22.25)
,(10,'2013-02-28','battery',59.39);
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 20, 2016 at 12:00 pm
ok, good points let me try this
I know my code is complex, this is probably the most complex task I have tried. Basically I wrote the code using a lot of trial and error. but this is where it is currently at
this statement currently brings back a data set that show unitid's 6 & 9 as NewVehicle. and that is correct.
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 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 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 VehicleMaintenance VSA3
ON VSA3.UnitID = VM1.UnitID
LEFT JOIN 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
then I wrote this statement and it brings back unitid's 1-5,7,8 & 10 as ExtVehicle and is correct. So I basically wrote the two statements independently.
Select VSA3.UnitID,VT1.VehicleName,Sum(VSA3.Amt)AS SixMonthsCosts,VSA3.ITEMID,min(VSA3.ServiceDate) AS FirstMaintenaceDateinLast6Months,'ExtVehicle' AS Status
FROM VehicleMaintenance VSA3
LEFT JOIN VehicleTable VT1 on VT1.UnitID=VSA3.UnitID
Where VSA3.UnitID NOT IN (
(Select VSA1.UnitID
FROM 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 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
The table dbo.VehicleMaintenance is just like the excel file columns A-D
unitid nvarchar(10)
servicedate datetime
type nvarchar(25)
amt decimal(29,12)
the source table is large and I only needed a sample set of data, so I wrote this and copied to excel
Select unitid,servicedate,type,amt
from dbo.VehicleMaintenance
where unitid between '1' and '10'
and I got a data set and copied it to excel
then I manually played with the numbers to create the "correct" answer set.
Then I wrote the code above to try and get the same result.
As I noted I ran the first half of the union statement and got expected results for unitid 6 & 9
I then ran the second half of the SQL statement and got the expected answer set for unitid's 1-5,7,8 & 10
My original second SQL statement was this
Select VSA3.UnitID,VT1.VehicleName,Sum(VSA3.Amt)AS SixMonthsCosts,VSA3.ITEMID,min(VSA3.ServiceDate) AS FirstMaintenaceDateinLast6Months,'ExtVehicle' AS Status
FROM VehicleMaintenance VSA3
LEFT JOIN VehicleTable VT1 on VT1.UnitID=VSA3.UnitID
Where VSA3.UnitID NOT IN (6,9)
group by VSA1.UnitID))
Group by VSA3.UnitID,VSA3.Type,VT1.VehicleName
and this returned the correct answer set for unitid's 1-5,7,8 & 10
I then replaced 6,9 with this subquery statement
(Select VSA1.UnitID
FROM 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 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))
when I run just this subquery I get two lines 6 and 9, so it should be the same net result
but when I run it as Where VSA3.UnitID NOT IN (6,9) it works and I get all unitid's 1 - 10 with 6 & 9 identified as NewVehicle.
but when I replace 6,9 with the subquery I only get answer set for 6 & 9
Is that better information?
April 20, 2016 at 12:02 pm
randyetheridge (4/20/2016)
Is that better information?
see my previous post
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 20, 2016 at 12:22 pm
Ok Yes, your table create and insert statements are exactly correct.
sorry missed the comment about the vehicle table.
CREATE TABLE VehicleTable( unitid INTEGER NOT NULL ,vehiclename VARCHAR(13) 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')
April 20, 2016 at 12:24 pm
Part of the problem is that you are accessing the VehicleMaintenance table SIX times when I think you can do what you need to by replacing your sub-queries with CASE expressions.
I've rewritten your query with CASE expressions, which I believe will get you most of the way to what you want.
SELECT vsa.UnitID,
vt.VehicleName,
vsa.[Type],
MIN(ServiceDate) AS FirstServiceDate,
CASE WHEN MIN(ServiceDate) >= DATEADD(mm, DATEDIFF(mm, '1900-01-01', GETDATE()), '1899-06-01') THEN 'NewVehicle' ELSE 'ExtVehicle' END,
SUM(CASE WHEN vsa.ServiceDate >= DATEADD(mm, DATEDIFF(mm, '1900-01-01', GETDATE()), '1899-01-01') AND vsa.ServiceDate < DATEADD(mm, DATEDIFF(mm, '1900-01-01', GETDATE()), '1900-01-01')
THEN vsa.Amt
END) AS prev_12_month_total
FROM VehicleMaintenance AS vsa
LEFT JOIN VehicleTable AS vt
ON vsa.UnitID = vt.UnitID
GROUP BY vsa.UnitID, vsa.[Type], vt.VehicleName
I've also simplified your date calculations (and used the CHAR() representations instead of the INT values of the dates to make it clearer which reference dates I was using).
All of this in untested, because I didn't have readily consumable data when I started working on this.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 20, 2016 at 12:46 pm
randyetheridge (4/20/2016)
Ok Yes, your table create and insert statements are exactly correct.sorry missed the comment about the vehicle table.
CREATE TABLE VehicleTable( unitid INTEGER NOT NULL ,vehiclename VARCHAR(13) 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')
sorry if I sound pedantic......but did you run this code before you posted?
hint...
CREATE TABLE VehicleTable( unitid INTEGER NOT NULL ,vehiclename VARCHAR(50) NOT NULL );
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 20, 2016 at 1:26 pm
I understand, I will test it, thank you.
April 20, 2016 at 1:44 pm
ok...so we have some test data that we can now all work on....
USE Tempdb
GO
IF OBJECT_ID('tempdb..VehicleMaintenance', 'U') IS NOT NULL
DROP TABLE tempdb..VehicleMaintenance ;
IF OBJECT_ID('tempdb..VehicleTable', 'U') IS NOT NULL
DROP TABLE tempdb..VehicleTable;
CREATE TABLE VehicleMaintenance(
unitid INTEGER 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-10','Oil',34.33)
,(1,'2012-01-11','Tire',57.77)
,(1,'2012-01-12','Oil',40.8)
,(1,'2012-01-13','Oil',45.65)
,(1,'2012-01-14','battery',36.76)
,(1,'2012-01-15','battery',19.01)
,(1,'2012-01-16','sparkplugs',65.36)
,(1,'2012-01-17','Tire',13.32)
,(1,'2012-01-18','Oil',5.23)
,(1,'2012-01-19','brakes',4.47)
,(1,'2012-01-20','brakes',9.31)
,(1,'2012-01-21','brakes',12.51)
,(1,'2012-01-22','radiatorflush',11.7)
,(1,'2012-01-23','sparkplugs',53.73)
,(1,'2012-01-24','Tire',18.17)
,(1,'2012-01-25','battery',14.12)
,(1,'2012-01-26','radiatorflush',2)
,(1,'2012-01-27','Tire',39.99)
,(1,'2012-01-28','battery',47.26)
,(1,'2012-01-29','AirFilter',15.78)
,(1,'2012-01-30','sparkplugs',23.34)
,(1,'2012-01-31','radiatorflush',29.48)
,(1,'2012-02-01','radiatorflush',52.92)
,(1,'2012-02-02','AirFilter',3.62)
,(1,'2012-02-03','Oil',12.95)
,(1,'2012-02-04','brakes',8.51)
,(1,'2012-02-05','AirFilter',1.23)
,(1,'2012-02-06','Oil',17.36)
,(1,'2012-02-07','Tire',44.84)
,(1,'2012-02-08','brakes',43.22)
,(1,'2012-02-09','brakes',47.26)
,(1,'2012-02-10','radiatorflush',53.73)
,(1,'2012-02-11','radiatorflush',0.38)
,(1,'2012-02-12','AirFilter',31.91)
,(1,'2012-02-13','AirFilter',22.25)
,(1,'2012-02-14','AirFilter',43.22)
,(1,'2012-02-15','radiatorflush',3.65)
,(1,'2012-02-16','Oil',27.87)
,(1,'2012-02-17','AirFilter',14.74)
,(1,'2012-02-18','AirFilter',51.3)
,(1,'2012-02-19','radiatorflush',28.67)
,(1,'2012-02-20','sparkplugs',21.4)
,(1,'2012-02-21','Oil',56.15)
,(1,'2012-02-22','brakes',54.54)
,(1,'2012-02-23','radiatorflush',14.12)
,(1,'2012-02-24','radiatorflush',19.01)
,(1,'2012-02-25','sparkplugs',23.86)
,(1,'2012-02-26','Tire',3.65)
,(1,'2012-02-27','Oil',17.4)
,(1,'2012-02-28','Tire',21.44)
,(1,'2012-02-29','Tire',1.19)
,(1,'2012-03-01','sparkplugs',27.06)
,(1,'2012-03-02','Tire',4.47)
,(1,'2012-03-03','battery',49.69)
,(1,'2012-03-04','battery',18.21)
,(1,'2012-03-05','AirFilter',39.99)
,(1,'2012-03-06','AirFilter',45.65)
,(1,'2012-03-07','Oil',49.45)
,(1,'2012-03-08','sparkplugs',14.12)
,(1,'2012-03-09','battery',14.74)
,(1,'2012-03-10','AirFilter',12.55)
,(1,'2012-03-11','Tire',27.87)
,(2,'2012-03-12','radiatorflush',15.78)
,(2,'2012-03-13','battery',7.7)
,(2,'2012-03-14','Oil',11.7)
,(2,'2012-03-15','sparkplugs',7.7)
,(2,'2012-03-16','brakes',12.95)
,(2,'2012-03-17','battery',49.45)
,(2,'2012-03-18','battery',21.4)
,(2,'2012-03-19','Tire',28.67)
,(2,'2012-03-20','brakes',23.86)
,(2,'2012-03-21','brakes',30.29)
,(2,'2012-03-22','brakes',3.65)
,(2,'2012-03-23','Tire',47.26)
,(2,'2012-03-24','AirFilter',44.84)
,(2,'2012-03-25','Tire',56.15)
,(2,'2012-03-26','AirFilter',23.34)
,(2,'2012-03-27','brakes',58.58)
,(2,'2012-03-28','Oil',52.11)
,(2,'2012-03-29','radiatorflush',19.82)
,(2,'2012-03-30','radiatorflush',25.44)
,(2,'2012-03-31','AirFilter',38.37)
,(2,'2012-04-01','Oil',49.69)
,(2,'2012-04-02','sparkplugs',8.47)
,(2,'2012-04-03','sparkplugs',2.81)
,(2,'2012-04-04','brakes',13.32)
,(2,'2012-04-05','sparkplugs',13.36)
,(2,'2012-04-06','Tire',5.27)
,(2,'2012-04-07','Tire',17.36)
,(2,'2012-04-08','sparkplugs',37.56)
,(2,'2012-04-09','battery',30.29)
,(2,'2012-04-10','brakes',40.8)
,(2,'2012-04-11','battery',58.58)
,(2,'2012-04-12','battery',13.32)
,(2,'2012-04-13','battery',37.56)
,(2,'2012-04-14','battery',8.47)
,(2,'2012-04-15','radiatorflush',40.8)
,(2,'2012-04-16','battery',41.61)
,(2,'2012-04-17','brakes',35.14)
,(2,'2012-04-18','sparkplugs',3.65)
,(2,'2012-04-19','brakes',48.07)
,(2,'2012-04-20','Tire',52.11)
,(2,'2012-04-21','Tire',3.66)
,(2,'2012-04-22','radiatorflush',52.11)
,(2,'2012-04-23','sparkplugs',16.55)
,(2,'2012-04-24','brakes',24.63)
,(2,'2012-04-25','sparkplugs',16.59)
,(2,'2012-04-26','brakes',57.77)
,(2,'2012-04-27','Tire',18.97)
,(3,'2012-04-28','AirFilter',18.21)
,(3,'2012-04-29','AirFilter',20.59)
,(3,'2012-04-30','Oil',14.97)
,(3,'2012-05-01','battery',2.81)
,(3,'2012-05-02','sparkplugs',23.06)
,(3,'2012-05-03','Tire',22.25)
,(3,'2012-05-04','brakes',2.04)
,(3,'2012-05-05','brakes',19.78)
,(3,'2012-05-06','Oil',23.06)
,(3,'2012-05-07','Oil',56.96)
,(3,'2012-05-08','radiatorflush',23.34)
,(3,'2012-05-09','brakes',10.12)
,(3,'2012-05-10','Tire',49.45)
,(3,'2012-05-11','sparkplugs',14.74)
,(3,'2012-05-12','Tire',65.36)
,(3,'2012-05-13','sparkplugs',48.07)
,(3,'2012-05-14','battery',23.06)
,(3,'2012-05-15','Oil',38.37)
,(3,'2012-05-16','brakes',36.76)
,(3,'2012-05-17','AirFilter',14.74)
,(3,'2012-05-18','radiatorflush',9.31)
,(3,'2012-05-19','radiatorflush',16.59)
,(3,'2012-05-20','Oil',28.67)
,(3,'2012-05-21','Oil',15.74)
,(3,'2012-05-22','Tire',23.82)
,(3,'2012-05-23','sparkplugs',10.08)
,(3,'2012-05-24','battery',14.74)
,(3,'2012-05-25','brakes',8.47)
,(3,'2012-05-26','AirFilter',6.89)
,(3,'2012-05-27','sparkplugs',2.85)
,(3,'2012-05-28','Oil',12.55)
,(3,'2012-05-29','AirFilter',26.25)
,(3,'2012-05-30','battery',17.4)
,(3,'2012-05-31','Oil',35.14)
,(3,'2012-06-01','Oil',12.51)
,(3,'2012-06-02','radiatorflush',39.99)
,(3,'2012-06-03','sparkplugs',8.51)
,(3,'2012-06-04','brakes',2)
,(3,'2012-06-05','brakes',20.63)
,(3,'2012-06-06','Oil',15.78)
,(3,'2012-06-07','battery',8.51)
,(3,'2012-06-08','sparkplugs',31.91)
,(3,'2012-06-09','battery',3.65)
,(3,'2012-06-10','battery',52.92)
,(3,'2012-06-11','sparkplugs',5.23)
,(3,'2012-06-12','brakes',6.85)
,(3,'2012-06-13','AirFilter',6.08)
,(3,'2012-06-14','battery',43.22)
,(3,'2012-06-15','sparkplugs',3.65)
,(3,'2012-06-16','Tire',19.82)
,(4,'2012-06-17','Oil',23.34)
,(4,'2012-06-18','sparkplugs',44.03)
,(4,'2012-06-19','brakes',52.11)
,(4,'2012-06-20','brakes',48.88)
,(4,'2012-06-21','radiatorflush',7.66)
,(4,'2012-06-22','radiatorflush',17.36)
,(4,'2012-06-23','Oil',10.93)
,(4,'2012-06-24','AirFilter',39.99)
,(4,'2012-06-25','sparkplugs',33.52)
,(4,'2012-06-26','radiatorflush',18.17)
,(4,'2012-06-27','battery',9.31)
,(4,'2012-06-28','radiatorflush',13.32)
,(4,'2012-06-29','AirFilter',0.38)
,(4,'2012-06-30','battery',42.41)
,(4,'2012-07-01','Tire',39.99)
,(4,'2012-07-02','battery',2)
,(4,'2012-07-03','Tire',12.51)
,(4,'2012-07-04','radiatorflush',59.39)
,(4,'2012-07-05','AirFilter',17.36)
,(4,'2012-07-06','brakes',15.78)
,(4,'2012-07-07','Tire',46.46)
,(4,'2012-07-08','battery',18.97)
,(4,'2012-07-09','radiatorflush',39.99)
,(4,'2012-07-10','radiatorflush',58.58)
,(4,'2012-07-11','battery',53.73)
,(4,'2012-07-12','radiatorflush',23.34)
,(4,'2012-07-13','Oil',6.85)
,(4,'2012-07-14','sparkplugs',49.69)
,(4,'2012-07-15','Oil',65.36)
,(4,'2012-07-16','sparkplugs',17.4)
,(4,'2012-07-17','AirFilter',17.4)
,(4,'2012-07-18','battery',6.89)
,(4,'2012-07-19','AirFilter',50.5)
,(4,'2012-07-20','brakes',31.1)
,(4,'2012-07-21','battery',20.63)
,(4,'2012-07-22','brakes',1.19)
,(4,'2012-07-23','battery',48.07)
,(4,'2012-07-24','radiatorflush',51.3)
,(4,'2012-07-25','sparkplugs',25.44)
,(4,'2012-07-26','battery',14.93)
,(4,'2012-07-27','brakes',39.99)
,(4,'2012-07-28','sparkplugs',50.5)
,(4,'2012-07-29','Oil',44.03)
,(4,'2012-07-30','sparkplugs',12.95)
,(4,'2012-07-31','brakes',29.48)
,(5,'2012-08-01','Tire',34.33)
,(5,'2012-08-02','radiatorflush',18.97)
,(5,'2012-08-03','sparkplugs',26.25)
,(5,'2012-08-04','brakes',18.17)
,(5,'2012-08-05','Oil',23.82)
,(5,'2012-08-06','AirFilter',16.55)
,(5,'2012-08-07','Oil',23.34)
,(5,'2012-08-08','AirFilter',39.18)
,(5,'2012-08-09','Oil',39.99)
,(5,'2012-08-10','battery',14.97)
,(5,'2012-08-11','battery',48.88)
,(5,'2012-08-12','Oil',39.18)
,(5,'2012-08-13','AirFilter',49.69)
,(5,'2012-08-14','battery',12.55)
,(5,'2012-08-15','battery',3.65)
,(5,'2012-08-16','sparkplugs',65.36)
,(5,'2012-08-17','radiatorflush',57.77)
,(5,'2012-08-18','AirFilter',5.23)
,(5,'2012-08-19','AirFilter',27.87)
,(5,'2012-08-20','AirFilter',65.36)
,(5,'2012-08-21','Tire',40.8)
,(5,'2012-08-22','Oil',22.21)
,(5,'2012-08-23','brakes',14.12)
,(5,'2012-08-24','AirFilter',4.43)
,(5,'2012-08-25','brakes',12.55)
,(5,'2012-08-26','AirFilter',56.96)
,(5,'2012-08-27','Oil',1.23)
,(5,'2012-08-28','AirFilter',27.06)
,(5,'2012-08-29','battery',24.63)
,(5,'2012-08-30','battery',25.44)
,(5,'2012-08-31','brakes',19.82)
,(5,'2012-09-01','battery',1.23)
,(5,'2012-09-02','battery',65.36)
,(5,'2012-09-03','brakes',42.41)
,(5,'2012-09-04','Tire',35.95)
,(5,'2012-09-05','radiatorflush',6.85)
,(5,'2012-09-06','battery',3.62)
,(5,'2012-09-07','Tire',24.63)
,(5,'2012-09-08','brakes',37.56)
,(5,'2012-09-09','Oil',23.02)
,(5,'2012-09-10','battery',2.85)
,(5,'2012-09-11','Oil',5.27)
,(5,'2012-09-12','Tire',10.93)
,(5,'2012-09-13','Oil',46.46)
,(5,'2012-09-14','sparkplugs',56.15)
,(5,'2012-09-15','AirFilter',14.93)
,(5,'2012-09-16','AirFilter',0.42)
,(5,'2012-09-17','radiatorflush',14.16)
,(5,'2012-09-18','sparkplugs',39.18)
,(5,'2012-09-19','Oil',18.17)
,(5,'2012-09-20','brakes',21.44)
,(5,'2012-09-21','AirFilter',22.21)
,(5,'2012-09-22','AirFilter',2.04)
,(5,'2012-09-23','Oil',57.77)
,(5,'2012-09-24','brakes',7.7)
,(6,'2012-09-25','radiatorflush',30.29)
,(6,'2012-09-26','Tire',7.66)
,(6,'2012-09-27','radiatorflush',36.76)
,(6,'2012-09-28','brakes',26.25)
,(6,'2012-09-29','brakes',52.92)
,(7,'2012-09-30','AirFilter',23.34)
,(7,'2012-10-01','Oil',18.21)
,(7,'2012-10-02','battery',31.1)
,(7,'2012-10-03','AirFilter',11.74)
,(7,'2012-10-04','brakes',3.65)
,(7,'2012-10-05','Tire',10.12)
,(7,'2012-10-06','radiatorflush',49.45)
,(7,'2012-10-07','Oil',6.89)
,(7,'2012-10-08','Tire',16.55)
,(7,'2012-10-09','radiatorflush',23.82)
,(7,'2012-10-10','Oil',65.36)
,(7,'2012-10-11','Tire',15.78)
,(7,'2012-10-12','radiatorflush',41.61)
,(7,'2012-10-13','Oil',50.5)
,(7,'2012-10-14','brakes',14.74)
,(7,'2012-10-15','Tire',58.58)
,(7,'2012-10-16','brakes',14.74)
,(7,'2012-10-17','AirFilter',54.54)
,(7,'2012-10-18','battery',14.16)
,(7,'2012-10-19','radiatorflush',10.12)
,(7,'2012-10-20','Tire',0.42)
,(7,'2012-10-21','brakes',31.91)
,(7,'2012-10-22','Tire',6.04)
,(7,'2012-10-23','Oil',49.45)
,(7,'2012-10-24','AirFilter',37.56)
,(7,'2012-10-25','radiatorflush',21.44)
,(7,'2012-10-26','Tire',49.45)
,(7,'2012-10-27','radiatorflush',5.27)
,(7,'2012-10-28','sparkplugs',18.21)
,(7,'2012-10-29','battery',32.71)
,(7,'2012-10-30','Tire',45.65)
,(7,'2012-10-31','AirFilter',48.88)
,(7,'2012-11-01','radiatorflush',8.51)
,(7,'2012-11-02','Tire',6.85)
,(7,'2012-11-03','Oil',21.44)
,(7,'2012-11-04','sparkplugs',6.08)
,(7,'2012-11-05','Oil',39.99)
,(7,'2012-11-06','sparkplugs',0.42)
,(7,'2012-11-07','AirFilter',32.71)
,(7,'2012-11-08','Oil',6.04)
,(7,'2012-11-09','Oil',22.25)
,(7,'2012-11-10','Oil',12.95)
,(7,'2012-11-11','radiatorflush',35.95)
,(7,'2012-11-12','radiatorflush',49.45)
,(7,'2012-11-13','radiatorflush',3.66)
,(7,'2012-11-14','brakes',18.97)
,(7,'2012-11-15','Oil',21.4)
,(8,'2012-11-16','sparkplugs',23.34)
,(8,'2012-11-17','radiatorflush',10.93)
,(8,'2012-11-18','Tire',5.23)
,(8,'2012-11-19','radiatorflush',34.33)
,(8,'2012-11-20','AirFilter',33.52)
,(8,'2012-11-21','AirFilter',10.93)
,(8,'2012-11-22','radiatorflush',20.63)
,(8,'2012-11-23','brakes',19.01)
,(8,'2012-11-24','radiatorflush',8.47)
,(8,'2012-11-25','brakes',12.95)
,(8,'2012-11-26','Tire',20.63)
,(8,'2012-11-27','brakes',2.81)
,(8,'2012-11-28','sparkplugs',44.84)
,(8,'2012-11-29','sparkplugs',19.01)
,(8,'2012-11-30','brakes',39.99)
,(8,'2012-12-01','brakes',49.45)
,(8,'2012-12-02','Tire',30.29)
,(8,'2012-12-03','Tire',23.34)
,(8,'2012-12-04','sparkplugs',19.82)
,(8,'2012-12-05','AirFilter',56.15)
,(8,'2012-12-06','Oil',27.06)
,(8,'2012-12-07','battery',23.86)
,(8,'2012-12-08','brakes',3.66)
,(8,'2012-12-09','sparkplugs',22.25)
,(8,'2012-12-10','radiatorflush',13.36)
,(8,'2012-12-11','brakes',7.66)
,(8,'2012-12-12','brakes',59.39)
,(8,'2012-12-13','Tire',56.96)
,(8,'2012-12-14','Tire',39.18)
,(8,'2012-12-15','Tire',14.16)
,(8,'2012-12-16','Tire',39.99)
,(8,'2012-12-17','Oil',44.84)
,(8,'2012-12-18','sparkplugs',11.74)
,(8,'2012-12-19','battery',44.03)
,(8,'2012-12-20','sparkplugs',22.21)
,(8,'2012-12-21','Tire',9.31)
,(8,'2012-12-22','AirFilter',5.27)
,(8,'2012-12-23','sparkplugs',19.78)
,(8,'2012-12-24','Tire',29.48)
,(8,'2012-12-25','sparkplugs',14.16)
,(8,'2012-12-26','Tire',33.52)
,(8,'2012-12-27','radiatorflush',2.85)
,(8,'2012-12-28','AirFilter',23.86)
,(8,'2012-12-29','brakes',14.93)
,(8,'2012-12-30','AirFilter',39.99)
,(8,'2012-12-31','sparkplugs',59.39)
,(8,'2013-01-01','AirFilter',15.74)
,(8,'2013-01-02','Tire',35.14)
,(8,'2013-01-03','Tire',14.97)
,(8,'2013-01-04','sparkplugs',36.76)
,(8,'2013-01-05','battery',20.59)
,(8,'2013-01-06','AirFilter',12.95)
,(9,'2013-01-07','AirFilter',44.03)
,(9,'2013-01-08','sparkplugs',38.37)
,(9,'2013-01-09','Tire',50.5)
,(9,'2013-01-10','battery',4.43)
,(9,'2013-01-11','battery',7.66)
,(9,'2013-01-12','battery',10.08)
,(9,'2013-01-13','Oil',10.12)
,(9,'2013-01-14','brakes',46.46)
,(9,'2013-01-15','sparkplugs',55.35)
,(9,'2013-01-16','sparkplugs',1.23)
,(9,'2013-01-17','Tire',11.74)
,(10,'2013-01-18','Oil',0.42)
,(10,'2013-01-19','sparkplugs',3.62)
,(10,'2013-01-20','brakes',35.95)
,(10,'2013-01-21','Oil',4.43)
,(10,'2013-01-22','brakes',14.97)
,(10,'2013-01-23','battery',19.78)
,(10,'2013-01-24','AirFilter',55.35)
,(10,'2013-01-25','radiatorflush',56.96)
,(10,'2013-01-26','AirFilter',12.95)
,(10,'2013-01-27','brakes',20.59)
,(10,'2013-01-28','Oil',33.52)
,(10,'2013-01-29','Oil',6.08)
,(10,'2013-01-30','AirFilter',21.4)
,(10,'2013-01-31','radiatorflush',12.51)
,(10,'2013-02-01','radiatorflush',48.07)
,(10,'2013-02-02','Tire',11.7)
,(10,'2013-02-03','radiatorflush',42.41)
,(10,'2013-02-04','Oil',29.48)
,(10,'2013-02-05','radiatorflush',6.04)
,(10,'2013-02-06','Tire',2)
,(10,'2013-02-07','radiatorflush',24.63)
,(10,'2013-02-08','AirFilter',34.33)
,(10,'2013-02-09','sparkplugs',42.41)
,(10,'2013-02-10','AirFilter',7.7)
,(10,'2013-02-11','AirFilter',65.36)
,(10,'2013-02-12','AirFilter',10.08)
,(10,'2013-02-13','brakes',14.16)
,(10,'2013-02-14','AirFilter',11.7)
,(10,'2013-02-15','sparkplugs',12.55)
,(10,'2013-02-16','battery',38.37)
,(10,'2013-02-17','sparkplugs',12.95)
,(10,'2013-02-18','brakes',9.28)
,(10,'2013-02-19','radiatorflush',2.81)
,(10,'2013-02-20','battery',54.54)
,(10,'2013-02-21','radiatorflush',19.78)
,(10,'2013-02-22','battery',31.91)
,(10,'2013-02-23','battery',3.66)
,(10,'2013-02-24','radiatorflush',35.14)
,(10,'2013-02-25','Oil',1.19)
,(10,'2013-02-26','battery',27.06)
,(10,'2013-02-27','radiatorflush',22.25)
,(10,'2013-02-28','battery',59.39);
CREATE TABLE VehicleTable( unitid INT NOT NULL ,vehiclename VARCHAR(50) 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')
Now
this statement currently brings back a data set that show unitid's 6 & 9 as NewVehicle. and that is correct.
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 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 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 VehicleMaintenance VSA3
ON VSA3.UnitID = VM1.UnitID
LEFT JOIN 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
if I run this against the test data ...it returns nothing, yet you say this is correct?
Drew has already posted some code that may help you...can you please indicate what your expected results are based on the sample data we now have?
thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 20, 2016 at 2:31 pm
yes thank you for your help, I now understand how to post a question in the correct format so someone can create the necessary tables quickly and easily and then I just can post the expected data set. Got it now. Took me a few posts to understand the process, thanks. All my prior posts have been very simple questions, so giving the table data was not necessary. I got it now.
April 20, 2016 at 2:38 pm
randyetheridge (4/20/2016)
yes thank you for your help, I now understand how to post a question in the correct format so someone can create the necessary tables quickly and easily and then I just can post the expected data set. Got it now. Took me a few posts to understand the process, thanks. All my prior posts have been very simple questions, so giving the table data was not necessary. I got it now.
No worries....it will help you get far better and quicker responses in the future...glad to hear that you have taken it on board.
good luck....and keep posting...its amazing the quality of responses you will get from the heavy hitters on this site (not me though :-D)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply