June 24, 2017 at 7:32 pm
Below I've commented out what is failing to work. By fail, i mean no result set.
when i substitute o.Datekey for '2017-05-17' i get no result set, when I leave hardcoded '2017-05-17' I get a result set.
this returns results
THIS WORKS
use Sandbox
go
select AlertName, a.issuestarttime, a.resolutiontime, a.DateKey,o.datekey, AppName
from Alerts a
join [dbo].[Alert_application] aset on a.alertid = aset.alertid
join [dbo].[Applications] app on aset.applicationid = app.applicationid
join (
select distinct Datekey, Tool from [dbo].OT_Jacob
where Tool = 'StrikeOne'
) o on o.Datekey = a.[DateKey] and app.AppName = o.Tool
where [issuestarttime] <= '2017-05-17'
and [resolutiontime] >= '2017-05-17'
I need it to work this way:
use Sandbox
go
select AlertName, a.issuestarttime, a.resolutiontime, a.DateKey,o.datekey, AppName
from Alerts a
join [dbo].[Alert_application] aset on a.alertid = aset.alertid
join [dbo].[Applications] app on aset.applicationid = app.applicationid
join (
select distinct Datekey, Tool from [dbo].OT_Jacob
where Datekey = '2017-05-17'
and Tool = 'StrikeOne'
) o on o.Datekey = a.[DateKey] and app.AppName = o.Tool
where [issuestarttime] <= o.Datekey
and [resolutiontime] >= o.Datekey
If i run subquery alone, it returns resultsselect distinct Datekey, Tool from [dbo].OT_Jacob
where Datekey = '20170517'
and Tool = 'StrikeOne'
DDL
CREATE TABLE [dbo].[OT_J](
[Tool] [varchar](20) NULL,
[applicationid] [int] NULL,
[DayMonth] [nvarchar](37) NULL,
[MonthYear] [nvarchar](61) NULL,
[Year] [nvarchar](30) NULL,
[cte_start_date] [datetime] NULL,
[DateKey] [date] NULL,
[HoursDown] [int] NULL,
[MonthYearSortCol] [int] NULL,
[DayMonthSortCol] [varchar](37) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'01-Jun-2017', N'June-2017', N'2017', CAST(N'2017-06-01T00:00:00.000' AS DateTime), CAST(N'2017-06-01' AS Date), 1, 6, N'06/01/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'01-May-2017', N'May-2017', N'2017', CAST(N'2017-05-01T00:00:00.000' AS DateTime), CAST(N'2017-05-01' AS Date), 1, 5, N'05/01/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'02-Jun-2017', N'June-2017', N'2017', CAST(N'2017-06-02T00:00:00.000' AS DateTime), CAST(N'2017-06-02' AS Date), 1, 6, N'06/02/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'02-May-2017', N'May-2017', N'2017', CAST(N'2017-05-02T00:00:00.000' AS DateTime), CAST(N'2017-05-02' AS Date), 1, 5, N'05/02/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'03-Jun-2017', N'June-2017', N'2017', CAST(N'2017-06-03T00:00:00.000' AS DateTime), CAST(N'2017-06-03' AS Date), 1, 6, N'06/03/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'03-May-2017', N'May-2017', N'2017', CAST(N'2017-05-03T00:00:00.000' AS DateTime), CAST(N'2017-05-03' AS Date), 1, 5, N'05/03/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'04-Jun-2017', N'June-2017', N'2017', CAST(N'2017-06-04T00:00:00.000' AS DateTime), CAST(N'2017-06-04' AS Date), 1, 6, N'06/04/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'04-May-2017', N'May-2017', N'2017', CAST(N'2017-05-04T00:00:00.000' AS DateTime), CAST(N'2017-05-04' AS Date), 1, 5, N'05/04/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'05-Jun-2017', N'June-2017', N'2017', CAST(N'2017-06-05T00:00:00.000' AS DateTime), CAST(N'2017-06-05' AS Date), 1, 6, N'06/05/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'05-May-2017', N'May-2017', N'2017', CAST(N'2017-05-05T00:00:00.000' AS DateTime), CAST(N'2017-05-05' AS Date), 1, 5, N'05/05/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'06-Jun-2017', N'June-2017', N'2017', CAST(N'2017-06-06T00:00:00.000' AS DateTime), CAST(N'2017-06-06' AS Date), 1, 6, N'06/06/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'06-May-2017', N'May-2017', N'2017', CAST(N'2017-05-06T00:00:00.000' AS DateTime), CAST(N'2017-05-06' AS Date), 1, 5, N'05/06/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'07-Jun-2017', N'June-2017', N'2017', CAST(N'2017-06-07T00:00:00.000' AS DateTime), CAST(N'2017-06-07' AS Date), 1, 6, N'06/07/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'07-May-2017', N'May-2017', N'2017', CAST(N'2017-05-07T00:00:00.000' AS DateTime), CAST(N'2017-05-07' AS Date), 1, 5, N'05/07/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'08-Jun-2017', N'June-2017', N'2017', CAST(N'2017-06-08T00:00:00.000' AS DateTime), CAST(N'2017-06-08' AS Date), 1, 6, N'06/08/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'08-May-2017', N'May-2017', N'2017', CAST(N'2017-05-08T00:00:00.000' AS DateTime), CAST(N'2017-05-08' AS Date), 1, 5, N'05/08/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'09-Jun-2017', N'June-2017', N'2017', CAST(N'2017-06-09T00:00:00.000' AS DateTime), CAST(N'2017-06-09' AS Date), 1, 6, N'06/09/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'09-May-2017', N'May-2017', N'2017', CAST(N'2017-05-09T00:00:00.000' AS DateTime), CAST(N'2017-05-09' AS Date), 1, 5, N'05/09/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'10-Jun-2017', N'June-2017', N'2017', CAST(N'2017-06-10T00:00:00.000' AS DateTime), CAST(N'2017-06-10' AS Date), 1, 6, N'06/10/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'10-May-2017', N'May-2017', N'2017', CAST(N'2017-05-10T00:00:00.000' AS DateTime), CAST(N'2017-05-10' AS Date), 1, 5, N'05/10/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'11-Jun-2017', N'June-2017', N'2017', CAST(N'2017-06-11T00:00:00.000' AS DateTime), CAST(N'2017-06-11' AS Date), 1, 6, N'06/11/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'11-May-2017', N'May-2017', N'2017', CAST(N'2017-05-11T00:00:00.000' AS DateTime), CAST(N'2017-05-11' AS Date), 1, 5, N'05/11/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'12-Jun-2017', N'June-2017', N'2017', CAST(N'2017-06-12T00:00:00.000' AS DateTime), CAST(N'2017-06-12' AS Date), 1, 6, N'06/12/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'12-May-2017', N'May-2017', N'2017', CAST(N'2017-05-12T00:00:00.000' AS DateTime), CAST(N'2017-05-12' AS Date), 1, 5, N'05/12/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'13-May-2017', N'May-2017', N'2017', CAST(N'2017-05-13T00:00:00.000' AS DateTime), CAST(N'2017-05-13' AS Date), 1, 5, N'05/13/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'14-May-2017', N'May-2017', N'2017', CAST(N'2017-05-14T00:00:00.000' AS DateTime), CAST(N'2017-05-14' AS Date), 1, 5, N'05/14/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'15-May-2017', N'May-2017', N'2017', CAST(N'2017-05-15T00:00:00.000' AS DateTime), CAST(N'2017-05-15' AS Date), 1, 5, N'05/15/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'16-Apr-2017', N'April-2017', N'2017', CAST(N'2017-04-16T00:00:00.000' AS DateTime), CAST(N'2017-04-16' AS Date), 1, 4, N'04/16/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'16-May-2017', N'May-2017', N'2017', CAST(N'2017-05-16T00:00:00.000' AS DateTime), CAST(N'2017-05-16' AS Date), 1, 5, N'05/16/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'17-Apr-2017', N'April-2017', N'2017', CAST(N'2017-04-17T00:00:00.000' AS DateTime), CAST(N'2017-04-17' AS Date), 1, 4, N'04/17/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'17-May-2017', N'May-2017', N'2017', CAST(N'2017-05-17T00:00:00.000' AS DateTime), CAST(N'2017-05-17' AS Date), 1, 5, N'05/17/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'18-Apr-2017', N'April-2017', N'2017', CAST(N'2017-04-18T00:00:00.000' AS DateTime), CAST(N'2017-04-18' AS Date), 1, 4, N'04/18/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'18-May-2017', N'May-2017', N'2017', CAST(N'2017-05-18T00:00:00.000' AS DateTime), CAST(N'2017-05-18' AS Date), 1, 5, N'05/18/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'19-Apr-2017', N'April-2017', N'2017', CAST(N'2017-04-19T00:00:00.000' AS DateTime), CAST(N'2017-04-19' AS Date), 1, 4, N'04/19/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'19-May-2017', N'May-2017', N'2017', CAST(N'2017-05-19T00:00:00.000' AS DateTime), CAST(N'2017-05-19' AS Date), 1, 5, N'05/19/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'20-Apr-2017', N'April-2017', N'2017', CAST(N'2017-04-20T00:00:00.000' AS DateTime), CAST(N'2017-04-20' AS Date), 1, 4, N'04/20/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'20-May-2017', N'May-2017', N'2017', CAST(N'2017-05-20T00:00:00.000' AS DateTime), CAST(N'2017-05-20' AS Date), 1, 5, N'05/20/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'21-Apr-2017', N'April-2017', N'2017', CAST(N'2017-04-21T00:00:00.000' AS DateTime), CAST(N'2017-04-21' AS Date), 1, 4, N'04/21/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'21-May-2017', N'May-2017', N'2017', CAST(N'2017-05-21T00:00:00.000' AS DateTime), CAST(N'2017-05-21' AS Date), 1, 5, N'05/21/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'22-Apr-2017', N'April-2017', N'2017', CAST(N'2017-04-22T00:00:00.000' AS DateTime), CAST(N'2017-04-22' AS Date), 1, 4, N'04/22/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'22-May-2017', N'May-2017', N'2017', CAST(N'2017-05-22T00:00:00.000' AS DateTime), CAST(N'2017-05-22' AS Date), 1, 5, N'05/22/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'23-Apr-2017', N'April-2017', N'2017', CAST(N'2017-04-23T00:00:00.000' AS DateTime), CAST(N'2017-04-23' AS Date), 1, 4, N'04/23/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'23-May-2017', N'May-2017', N'2017', CAST(N'2017-05-23T00:00:00.000' AS DateTime), CAST(N'2017-05-23' AS Date), 1, 5, N'05/23/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'24-Apr-2017', N'April-2017', N'2017', CAST(N'2017-04-24T00:00:00.000' AS DateTime), CAST(N'2017-04-24' AS Date), 1, 4, N'04/24/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'24-May-2017', N'May-2017', N'2017', CAST(N'2017-05-24T00:00:00.000' AS DateTime), CAST(N'2017-05-24' AS Date), 1, 5, N'05/24/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'25-Apr-2017', N'April-2017', N'2017', CAST(N'2017-04-25T00:00:00.000' AS DateTime), CAST(N'2017-04-25' AS Date), 1, 4, N'04/25/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'25-May-2017', N'May-2017', N'2017', CAST(N'2017-05-25T00:00:00.000' AS DateTime), CAST(N'2017-05-25' AS Date), 1, 5, N'05/25/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'26-Apr-2017', N'April-2017', N'2017', CAST(N'2017-04-26T00:00:00.000' AS DateTime), CAST(N'2017-04-26' AS Date), 1, 4, N'04/26/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'26-May-2017', N'May-2017', N'2017', CAST(N'2017-05-26T00:00:00.000' AS DateTime), CAST(N'2017-05-26' AS Date), 1, 5, N'05/26/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'27-Apr-2017', N'April-2017', N'2017', CAST(N'2017-04-27T00:00:00.000' AS DateTime), CAST(N'2017-04-27' AS Date), 1, 4, N'04/27/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'27-May-2017', N'May-2017', N'2017', CAST(N'2017-05-27T00:00:00.000' AS DateTime), CAST(N'2017-05-27' AS Date), 1, 5, N'05/27/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'28-Apr-2017', N'April-2017', N'2017', CAST(N'2017-04-28T00:00:00.000' AS DateTime), CAST(N'2017-04-28' AS Date), 1, 4, N'04/28/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'28-May-2017', N'May-2017', N'2017', CAST(N'2017-05-28T00:00:00.000' AS DateTime), CAST(N'2017-05-28' AS Date), 1, 5, N'05/28/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'29-Apr-2017', N'April-2017', N'2017', CAST(N'2017-04-29T00:00:00.000' AS DateTime), CAST(N'2017-04-29' AS Date), 1, 4, N'04/29/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'29-May-2017', N'May-2017', N'2017', CAST(N'2017-05-29T00:00:00.000' AS DateTime), CAST(N'2017-05-29' AS Date), 1, 5, N'05/29/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'30-Apr-2017', N'April-2017', N'2017', CAST(N'2017-04-30T00:00:00.000' AS DateTime), CAST(N'2017-04-30' AS Date), 1, 4, N'04/30/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'30-May-2017', N'May-2017', N'2017', CAST(N'2017-05-30T00:00:00.000' AS DateTime), CAST(N'2017-05-30' AS Date), 1, 5, N'05/30/2017 12:00:00 AM')
GO
INSERT [dbo].[OT_J] ([Tool], [applicationid], [DayMonth], [MonthYear], [Year], [cte_start_date], [DateKey], [HoursDown], [MonthYearSortCol], [DayMonthSortCol]) VALUES (N'StrikeOne', 3000, N'31-May-2017', N'May-2017', N'2017', CAST(N'2017-05-31T00:00:00.000' AS DateTime), CAST(N'2017-05-31' AS Date), 1, 5, N'05/31/2017 12:00:00 AM')
GO
--ALERTS TABLE
CREATE TABLE Alerts (
AlertID int,
AlertName varchar(100),
ApplicationName varchar(50),
IssueStartTime datetime,
ResolutionTime datetime,
DateKey date,
SeverityID int)
INSERT INTO Alerts (AlertID,AlertName,ApplicationName,IssueStartTime,ResolutionTime, Datekey, SeverityID)
VALUES
(160922, 'Can''t login', 'Shapeeze', '2017-05-08 12:00:00.000', '2017-05-23 18:00:00.000', '20170508', '1000'),
(160923, 'Screens freezing', 'Shapeeze', '2017-05-29 12:00:00.000', '2017-06-03 10:00:00.000', '20170529', '1002'),
(150319, 'Errors Submitting Forms','Shapeeze', '2017-05-23 06:00:00.000', '2017-05-29 13:00:00.000', '20170523', '1001'),
(210522, 'Server down', 'Napson', '2017-05-01 03:00:00.000','2017-05-01 23:00:00.000', '20170501', '1002'),
(210525, 'Updates failing', 'Napson', '2017-05-30 03:00:00.000','2017-06-10 12:00:00.000', '20170530', '1002'),
(120311, 'Customers Being Misrouted', 'Strikeone','2017-05-15 16:00:00.000', '2017-05-19 09:00:00.000', '20170515', '1001'),
(120313, 'Customers Deadending', 'Strikeone','2017-04-15 16:00:00.000', '2017-06-12 09:00:00.000', '20170415','1002'),
(043293, 'Calls dropping', 'VeritVru','2017-04-12 03:00:00.000', '2017-04-12 09:00:00.000', '20170412','1000'),
(043297, 'Static on calls', 'VeritVru','2017-04-27 03:00:00.000', '2017-05-06 09:00:00.000', '20170427','1002'),
(043298, 'Reboot', 'VeritVru','2017-05-27 03:00:00.000', '2017-05-27 04:00:00.000', '20170527','2222'),
(043299, 'Upgrade', 'VeritVru','2017-04-30 00:00:00.000', '2017-04-30 01:00:00.000', '20170430','1111');
--ALERTS APPLICATION
CREATE TABLE Alert_Application (
AlertID int,
ApplicationID int);
INSERT INTO Alert_Application
VALUES
(160922, 1001),
(160923, 1001),
(150319, 1001),
(210522, 2000),
(210525, 2000),
(120311, 3000),
(120313, 3000),
(43293, 4000),
(43297, 4000),
(043298, 4000),
(043299, 4000);
--APPLICATION
CREATE TABLE Applications (
ApplicationID int,
AppName varchar(20))
INSERT INTO Applications
VALUES
(1001, 'Shapeeze'),
(2000, 'Napson'),
(3000, 'StrikeOne'),
(4000, 'VeritVRU'),
(5000, 'Snickerdoodle');
--Quote me
June 24, 2017 at 9:56 pm
With the sample data you provided, I don't get results from either form, whether the date is hardcoded or pulled from OT_Jacob, primarily because there's only one row in that table's sample data that has a DateKey matching anything in the Alerts table.
Since that one row is not for the StrikeOne app, and is also not 20170517, nothing gets returned from either query with this sample data.
Whatever you're seeing must be due to the specific data you're querying. Without being able to reproduce the behavior with your sample data, I could only guess that the results you're getting from the first query are results where the DateKey is NOT 20170517.
The second query restricts results to rows having a DateKey of 20170517, while the first does not, so that would be my guess, but it's tough to say for sure without being able to reproduce the behavior.
Cheers!
June 24, 2017 at 10:24 pm
You're right. I just wasn't seeing it.
In this dataset if I want the subquery and outerquery to return results I need to join on YYYYMM instead of Datekey which is YYYYMMDD.
Thanks for looking at this, Jacob.
--Quote me
June 25, 2017 at 8:35 am
Happy to help, and glad you got it figured out!
Cheers!
June 25, 2017 at 2:59 pm
Things like “day–month†are called interval data types in the ANSI ISO standards. But they’re not character strings! If you ever read the ANSI standards, you know that we don’t use the embedded T syntax for timestamps. We had to settle on one and only one display format so we did that with the space (frankly, I think we were wrong on that and should of gone with the embedded T).
The data element named “date_key†is absurd; a date is a unit of temporal measurement in a key is how a data element is used in a particular table. Therefore, “_key†is metadata and would never be part of a valid data element name.
Identifiers, such as application_id, cannot be numerics because you do know math on them. What do you think the square root of your credit card number means?
Finally, a table by definition, must have a key and you don’t any or REFERENCES among the tables. I am guessing that you need something like this to start, until we get alert codes.
CREATE TABLE Alert_Events
(application_id CHAR(20) NOT NULL
REFERENCES Applications(application_id)
ON UPDATE CASCADE
ON DELETE CASCADE
something_date DATE NOT NULL
down_hours INTEGER DEFAULT 0 NOT NULL
CHECK (down_hours >= 0));
INSERT INTO Foobar
VALUES
('3000', '2017-05-01', 0),
('3000', '2017-05-02', 0),
('3000', '2017-05-03', 0),
('3000', '2017-05-04', 0),
('3000', '2017-05-05', 0),
('3000', '2017-05-06', 0),
('3000', '2017-05-07', 0),
('3000', '2017-05-08', 0),
('3000', '2017-05-09', 0),
('3000', '2017-05-10', 0),
('3000', '2017-06-01', 0),
('3000', '2017-06-02', 0),
('3000', '2017-06-03', 0),
('3000', '2017-06-04', 0),
('3000', '2017-06-05', 0)
('3000', '2017-06-06', 0),
('3000', '2017-06-07', 0),
('3000', '2017-06-08', 0),
('3000', '2017-06-09', 0),
('3000', '2017-06-10', 0);
CREATE TABLE Applications
(application_id CHAR(20) NOT NULL PRIMARY KEY,
application_name VARCHAR(20) NOT NULL);
INSERT INTO Applications
VALUES
('1001', 'Shapeeze'),
('2000', 'Napson'),
('3000', 'StrikeOne'),
('4000', 'VeritVRU'),
('5000', 'Snickerdoodle');
Why is the date separated from the alert event?
Please post DDL and follow ANSI/ISO standards when asking for help.
June 25, 2017 at 2:59 pm
Things like “day–month†are called interval data types in the ANSI ISO standards. But they’re not character strings! If you ever read the ANSI standards, you know that we don’t use the embedded T syntax for timestamps. We had to settle on one and only one display format so we did that with the space (frankly, I think we were wrong on that and should of gone with the embedded T).
The data element named “date_key†is absurd; a date is a unit of temporal measurement in a key is how a data element is used in a particular table. Therefore, “_key†is metadata and would never be part of a valid data element name.
Identifiers, such as application_id, cannot be numerics because you do know math on them. What do you think the square root of your credit card number means?
Finally, a table by definition, must have a key and you don’t any or REFERENCES among the tables. I am guessing that you need something like this to start, until we get alert codes.
CREATE TABLE Alert_Events
(application_id CHAR(20) NOT NULL
REFERENCES Applications(application_id)
ON UPDATE CASCADE
ON DELETE CASCADE
something_date DATE NOT NULL
down_hours INTEGER DEFAULT 0 NOT NULL
CHECK (down_hours >= 0));
INSERT INTO Foobar
VALUES
('3000', '2017-05-01', 0),
('3000', '2017-05-02', 0),
('3000', '2017-05-03', 0),
('3000', '2017-05-04', 0),
('3000', '2017-05-05', 0),
('3000', '2017-05-06', 0),
('3000', '2017-05-07', 0),
('3000', '2017-05-08', 0),
('3000', '2017-05-09', 0),
('3000', '2017-05-10', 0),
('3000', '2017-06-01', 0),
('3000', '2017-06-02', 0),
('3000', '2017-06-03', 0),
('3000', '2017-06-04', 0),
('3000', '2017-06-05', 0)
('3000', '2017-06-06', 0),
('3000', '2017-06-07', 0),
('3000', '2017-06-08', 0),
('3000', '2017-06-09', 0),
('3000', '2017-06-10', 0);
CREATE TABLE Applications
(application_id CHAR(20) NOT NULL PRIMARY KEY,
application_name VARCHAR(20) NOT NULL);
INSERT INTO Applications
VALUES
('1001', 'Shapeeze'),
('2000', 'Napson'),
('3000', 'StrikeOne'),
('4000', 'VeritVRU'),
('5000', 'Snickerdoodle');
Why is the date separated from the alert event?
Please post DDL and follow ANSI/ISO standards when asking for help.
June 25, 2017 at 3:39 pm
jcelko212 32090 - Sunday, June 25, 2017 2:59 PMIdentifiers, such as application_id, cannot be numerics because you do know math on them.
Please post the ANSI./ISO standard and paragraph where it states such a thing. Otherwise, please stop posting such nonsense.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 26, 2017 at 1:25 pm
Jeff Moden - Sunday, June 25, 2017 3:39 PMjcelko212 32090 - Sunday, June 25, 2017 2:59 PMIdentifiers, such as application_id, cannot be numerics because you do know math on them.
Please post the ANSI./ISO standard and paragraph where it states such a thing. Otherwise, please stop posting such nonsense.
This convention is not in ANSI/ISO standards, nor should it be.. This deals with how something is used not what it is by its nature. Would you expect a standard specifies machine screws to discuss the mechanics of them in particular machines? No. This is from the metadata people and their standards. I can't find the documents right off hand, but the rules were pretty straightforward and based on mathematical conventions. Exact numerics represent magnitudes and quantities (the basic definition you used to find it arithmetic books starting in the late 1700s), ordinal numbers (sequence), dimensionless measurements on some specific scale, and probably some other stuff I'm forgetting. The important characteristics is that computations must make sense, if it's numeric. That's why I demonstrate they can't be used as identifier with my example of "the square root of your credit card number" in my post.
This is also why I differentiate strongly between a string of digits, known as a "tag number" in data modeling, and a real numeric value. It's also why I urge people who are designing encoding schemes to follow the ISO convention of fixed length fields, and leading zeros. I want to be able to do a simple regular expression to validate and encoding. Look at all of the ISO encoding standards, and you'll see where I got this. Even more so all of them are designed on Unicode, which has a subset that must be in all languages. The consist of the simple Latin alphabet, the digits and a minimal set of punctuation marks.
Jeff, by now you ought to know I "Mr. Standards", uber alles 😉
Please post DDL and follow ANSI/ISO standards when asking for help.
June 26, 2017 at 1:34 pm
jcelko212 32090 - Monday, June 26, 2017 1:25 PMJeff Moden - Sunday, June 25, 2017 3:39 PMjcelko212 32090 - Sunday, June 25, 2017 2:59 PMIdentifiers, such as application_id, cannot be numerics because you do know math on them.
Please post the ANSI./ISO standard and paragraph where it states such a thing. Otherwise, please stop posting such nonsense.
This convention is not in ANSI/ISO standards, nor should it be.. This deals with how something is used not what it is by its nature. Would you expect a standard specifies machine screws to discuss the mechanics of them in particular machines? No. This is from the metadata people and their standards. I can't find the documents right off hand, but the rules were pretty straightforward and based on mathematical conventions. Exact numerics represent magnitudes and quantities (the basic definition you used to find it arithmetic books starting in the late 1700s), ordinal numbers (sequence), dimensionless measurements on some specific scale, and probably some other stuff I'm forgetting. The important characteristics is that computations must make sense, if it's numeric. That's why I demonstrate they can't be used as identifier with my example of "the square root of your credit card number" in my post.
This is also why I differentiate strongly between a string of digits, known as a "tag number" in data modeling, and a real numeric value. It's also why I urge people who are designing encoding schemes to follow the ISO convention of fixed length fields, and leading zeros. I want to be able to do a simple regular expression to validate and encoding. Look at all of the ISO encoding standards, and you'll see where I got this. Even more so all of them are designed on Unicode, which has a subset that must be in all languages. The consist of the simple Latin alphabet, the digits and a minimal set of punctuation marks.
Jeff, by now you ought to know I "Mr. Standards", uber alles 😉
"Mr. Standards" ? Yes. Uber alles - yeah, to the point of intentional foolishness. I'm not sure why anyone listens any more, as you do a better job of convincing people that standards are rigid non-negotiable pains in your butt than the useful guidelines they are supposed to be. Don't be so "uber alles" and folks might actually be more willing to listen. I've been done listening to you for quite some time, and frankly, I'm sick of your "holier than thou" attitude about standards. There are real-world situations all over the place that while they might have benefited from standardization had the developers at the time had the knowledge and understanding, but don't because those people didn't have the proper skills. That reality isn't going to just magically "go away". You might want to try dealing with the world as it is instead of dreaming all the time about how you want it to be. End of rant.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 27, 2017 at 8:10 pm
jcelko212 32090 - Monday, June 26, 2017 1:25 PMJeff Moden - Sunday, June 25, 2017 3:39 PMjcelko212 32090 - Sunday, June 25, 2017 2:59 PMIdentifiers, such as application_id, cannot be numerics because you do know math on them.
Please post the ANSI./ISO standard and paragraph where it states such a thing. Otherwise, please stop posting such nonsense.
This convention is not in ANSI/ISO standards, nor should it be.. This deals with how something is used not what it is by its nature. Would you expect a standard specifies machine screws to discuss the mechanics of them in particular machines? No. This is from the metadata people and their standards. I can't find the documents right off hand, but the rules were pretty straightforward and based on mathematical conventions. Exact numerics represent magnitudes and quantities (the basic definition you used to find it arithmetic books starting in the late 1700s), ordinal numbers (sequence), dimensionless measurements on some specific scale, and probably some other stuff I'm forgetting. The important characteristics is that computations must make sense, if it's numeric. That's why I demonstrate they can't be used as identifier with my example of "the square root of your credit card number" in my post.
This is also why I differentiate strongly between a string of digits, known as a "tag number" in data modeling, and a real numeric value. It's also why I urge people who are designing encoding schemes to follow the ISO convention of fixed length fields, and leading zeros. I want to be able to do a simple regular expression to validate and encoding. Look at all of the ISO encoding standards, and you'll see where I got this. Even more so all of them are designed on Unicode, which has a subset that must be in all languages. The consist of the simple Latin alphabet, the digits and a minimal set of punctuation marks.
Jeff, by now you ought to know I "Mr. Standards", uber alles 😉
In other words, it's your opinion and you apply things as you see fit when it suits you. Exact numerics are quite useful and very appropriate as "IDs" especially when they represent a surrogate key. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
June 27, 2017 at 8:57 pm
Re: "Things like “day–month” are called interval data types in the ANSI ISO standards. " I did not know that. Will you share some good on line (free) reads about this?
Re: "The data element named “date_key” is absurd; a date is a unit of temporal measurement in a key is how a data element is used in a particular table. Therefore, “_key” is metadata and would never be part of a valid data element name." I did not know Datekey name is absurd. I thought it was a convention in Dimensional modelling to use YYYYMMDD having INT datatype, as a key column.
Re: "Identifiers, such as application_id, cannot be numerics because you do know math on them" I didn't realize I had applied numeric datatype anywhere to an id column. I know about not doing that.
Re: "Finally, a table by definition, must have a key and you don’t any or REFERENCES among the tables. I am guessing that you need something like this to start, until we get alert codes." Yes, that's right. I do not bother to make it have referential integrity when posting about an unrelated non-modelling type of issue. Seriously, it can be a lot of work to provide DDL to illustrate a problem.
Joe Celko, I hope you reply. Then I'll know you were trying to be helpful. You've been on my tail on this forum about my DDL before and I have never been in the frame of mind at the time, rather focused on the actual problem.
--Quote me
June 28, 2017 at 1:06 am
The data element named “date_key” is absurd; a date is a unit of temporal measurement in a key is how a data element is used in a particular table. Therefore, “_key” is metadata and would never be part of a valid data element name.
Seems then that we could not name columns with the ID suffix either?
No more column names like ... BookID, BookName, BookDescription, BookLongName, ... . Those suffixes after Book are not permissible as you see it?
Identifiers, such as application_id, cannot be numerics because you do know math on them. What do you think the square root of your credit card number means?
I did not see the post contain a number type that equivicates to the "real" number of mathematics. The OP was using int to create the ID data types. You wont see an ID like 2.3
----------------------------------------------------
June 28, 2017 at 12:22 pm
MMartin1 - Wednesday, June 28, 2017 1:06 AMSeems then that we could not name columns with the ID suffix either?
No more column names like ... BookID, BookName, BookDescription, BookLongName, ... . Those suffixes after Book are not permissible as you see it?Actually, the suffixes are how ISO 11179 names data elements.. They are called "attribute properties" and are required to make a complete data element name. However, your "book_id" is bad because there is an industry-standard for books called the ISBN (international standard book number), and that's what you should be using instead of inventing your own identifiers.
I did not see the post contain a number type that equivocates to the "real" number of mathematics. The OP was using int to create the ID data types. You wont see an ID like 2.3
one of my favorite T-shirts right now reads, "on a scale from 1 to 10, what color is your favorite letter of the alphabet?"
This clearly points out the problems of using the wrong data type for various attributes and therefore picking the wrong data type in your programming language. As it turns out you actually will see encodings that use a decimal. Have you ever been to a library and looked the Dewey decimal system used for the catalogs in the United States and other Western countries? The point is that the punctuation, like slashes, dashes and periods, are not numeric symbols. They are punctuation marks
Please post DDL and follow ANSI/ISO standards when asking for help.
June 28, 2017 at 2:27 pm
polkadot - Saturday, June 24, 2017 7:32 PMPlease get a book on basic data modeling and RDBMS. There is no table in your attempt at DDL. 1can never be a table, thanks to its incredibly bad design. I’m going to give you the benefit of the doubt and assume that table names is well understood in your trade. Otherwise it be a horrible violation of ISO 11179 rules. Just like the rest of your column names are. Just consider “yearâ€; that name is the name of the data element in your model, but in RDBMS. It is the name of the unit of temporal measurement. I doubt that it is really 30 Chinese Unicode characters long. All of your temporal columns in this mess are redundant; the first day when we were discussing databases in class? The goal of databases over filesystems was to reduce redundancy, not increase it. Another rule from the first part of the class would have been the data elements do not change names from table to table. But in your case, sometimes an application can be a tool!Things like “day–month†are called interval data types in the ANSI ISO standards. But they’re not character strings! If you ever read the ANSI standards, you know that we don’t use the embedded T syntax for timestamps. We had to settle on one and only one display format so we did that with the space (frankly, I think we were wrong on that and should of gone with the embedded T).
CASCADE DELETE? you are infering that that is a desired business behavior, i'd mention it's benefits but not try to infer it's the gospe, it depends on the business model, and not some vague standard.
The data element named “date_key†is absurd; a date is a unit of temporal measurement in a key is how a data element is used in a particular table. Therefore, “_key†is metadata and would never be part of a valid data element name.
Identifiers, such as application_id, cannot be numerics because you do know math on them. What do you think the square root of your credit card number means?
Finally, a table by definition, must have a key and you don’t any or REFERENCES among the tables. I am guessing that you need something like this to start, until we get alert codes.
CREATE TABLE Alert_Events
(application_id CHAR(20) NOT NULL
REFERENCES Applications(application_id)
ON UPDATE CASCADE
ON DELETE CASCADE
something_date DATE NOT NULL
down_hours INTEGER DEFAULT 0 NOT NULL
CHECK (down_hours >= 0));INSERT INTO Foobar
VALUES
('3000', '2017-05-01', 0),
('3000', '2017-05-02', 0),
('3000', '2017-05-03', 0),
('3000', '2017-05-04', 0),
('3000', '2017-05-05', 0),
('3000', '2017-05-06', 0),
('3000', '2017-05-07', 0),
('3000', '2017-05-08', 0),
('3000', '2017-05-09', 0),
('3000', '2017-05-10', 0),
('3000', '2017-06-01', 0),
('3000', '2017-06-02', 0),
('3000', '2017-06-03', 0),
('3000', '2017-06-04', 0),
('3000', '2017-06-05', 0)
('3000', '2017-06-06', 0),
('3000', '2017-06-07', 0),
('3000', '2017-06-08', 0),
('3000', '2017-06-09', 0),
('3000', '2017-06-10', 0);CREATE TABLE Applications
(application_id CHAR(20) NOT NULL PRIMARY KEY,
application_name VARCHAR(20) NOT NULL);INSERT INTO Applications
VALUES
('1001', 'Shapeeze'),
('2000', 'Napson'),
('3000', 'StrikeOne'),
('4000', 'VeritVRU'),
('5000', 'Snickerdoodle');Why is the date separated from the alert event?
I just wanted to point out that you once again you post untested code that will not run or pass a syntax check. you reference tables that don't exist, and have a few syntax errors as well.
i have yet to see you post code that is worth anything as far as a working example.
add that to your work in a vacuum ansi standards nonsense, along with poor attempt at advice(advice? more like belittling), I once again shake my head and wonder if you have any real world experience, or if it is all scholarly theory.
you angrily shaking your fist at people for not following whatever you think they should do, and then not even being able to put a coherent example together to explain what you mean hurts your credibility.
Get a book? with your rants about ansi standards make sure i would never touch an article or book written by you.
the DateKey complaint? It is very common to see integer values in the YYMMDD format as a primary key of a table; it's like you've never seen a data warehouse before.
the numeric Id complaint? doing math on an ID, WTH are you talking about? the applicationID in the example is clearly not a credit card, so don't infer anything otherwise, that was ridiculous. A numeric(12) or decimal(12) with no scale allows larger values than an int. Again, it's like you have no real world experience, or are trapped in a non-SQL Server language and are trying to translate other practices.
horrible violation of ISO 11179 rules? bull. table names are to have meaning to the business, and they clearly do.
one and only one display format? another opinion that is outside of the realm of the real world.
a table by definition, must have a key? SQL is nice about things like that,like Miranda rights, "if you cannot afford a key, one will be provided for you before querying". FK References are nice, and probably exist in the real tables, but for the purposes of his example,w here he probably excluded dozens of other columns, for an online example it's perfectly acceptable.
The online persona you project undoubtedly keeps opportunities away and damages your reputation. But you've heard that enough that you clearly don't care about something like that.
Lowell
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply