erikTsomik
asked on
sql calculate reminders
I need to create a query about car service reminders. The car can have 3 type of reminders: 2- Registration, 3 - Emission Test, 4 - State Inspection
The car table has the last date the task was performed.
CarActivitiees table has all prams repeat very 2 years , remind 10 days before it is due and so on
carActivities dataStructure
id int primary key
activityID int 0 - oil change, 1-oil Change; 2 - Registration; 3-Emission;4-State Inspection
repeatNumber int (every 2 days, or every 2 years )
repeatCycle int (every 2 days, or every 2 years )
remindNumber int (every 2 days, or every 2 years )
remindCycle int (every 2 days, or every 2 years )
carKey int the car we are dealing with
The car table has the last date the task was performed.
CarActivitiees table has all prams repeat very 2 years , remind 10 days before it is due and so on
carActivities dataStructure
id int primary key
activityID int 0 - oil change, 1-oil Change; 2 - Registration; 3-Emission;4-State Inspection
repeatNumber int (every 2 days, or every 2 years )
repeatCycle int (every 2 days, or every 2 years )
remindNumber int (every 2 days, or every 2 years )
remindCycle int (every 2 days, or every 2 years )
carKey int the car we are dealing with
That would be vastly easier to do if you provided an actual table definition (CREATE TABLE ...) for carActivities and car. Can't even tell the date column(s) to use from what you posted.
ASKER
CREATE TABLE [dbo].[carActivities](
[id] [int] IDENTITY(1,1) NOT NULL,
[activityID] [int] NOT NULL,
[repeatNumber] [int] NOT NULL,
[repeatCycle] [int] NOT NULL,
[remindNumber] [int] NOT NULL,
[remindCycle] [int] NOT NULL,
[carKey] [int] NOT NULL,
CONSTRAINT [PK_car_activities] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1-oil Change; 2 - Registration; 3-Emission;4-State Inspection' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'carActivities', @level2type=N'COLUMN',@level2name=N'activityID'
GO
The car table has the last date the task was performed.
What is that table's structure? Is there an activity id on it as well? Or are all the dates de-normalized into one row?
ASKER
here is table car Structure . The fields that I am interesting in are [regdate] ,[emissiondate], [inspectiondate], and oilChangeMilage which is the last milage when the oil chnage was done. The dates are denormalize into 1 row
CREATE TABLE [dbo].[car](
[carkey] [int] IDENTITY(1,1) NOT NULL,
[carnumber] [char](10) NOT NULL,
[locationkey] [int] NULL,
[description] [varchar](50) NULL,
[tagnumber] [varchar](10) NULL,
[note] [varchar](255) NULL,
[carType] [varchar](10) NULL,
[carActiveIn] [bit] NULL,
[carServiceIn] [bit] NULL,
[originstate] [varchar](2) NULL,
[sessiontype] [varchar](10) NULL,
[modelyear] [int] NULL,
[make] [varchar](20) NULL,
[model] [varchar](20) NULL,
[vin] [varchar](50) NULL,
[regdate] [datetime] NULL,
[emissiondate] [datetime] NULL,
[inspectiondate] [datetime] NULL,
[carstatus] [varchar](10) NULL,
[instructorkey] [int] NULL,
[retired] [char](1) NULL,
[isstatic] [char](1) NULL,
[oilChangeMilage] [int] NULL,
CONSTRAINT [PK_car] PRIMARY KEY NONCLUSTERED
(
[carkey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ASKER
Any suggestions
repeatNumber int (every 2 days, or every 2 years )
repeatCycle int (every 2 days, or every 2 years )
remindNumber int (every 2 days, or every 2 years )
remindCycle int (every 2 days, or every 2 years )
Please be more specific about these fields. What do they mean? Types of Units (minutes/hours/days/etc.)?
ASKER
repeatnumber (1,2,3,4 and so on )is just a number repeatCycle is a unit it is measure on (days,month,weeks,years )
ASKER
Are there any suggestions?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
repeat Cycle is 0- miles, 1 - day,2,weeks,3 - month,4 -- Year
ASKER