Transact Sql Column not null
Right now, I have a view that looks like this:
CREATE VIEW [dbo].[vwContaminationHistory]
AS
SELECT
ISNULL(ROW_NUMBER() OVER (ORDER BY DetectorID), 9999) AS Row,
MAX(DetectorID) AS DetectorID,
MIN(TIMESTAMP) AS TIMESTAMP,
MAX(ScannerType) AS ScannerType,
MAX(Pollution) AS Pollution,
MAX(VFactor) AS VFactor
FROM
ContaminationHistory
GROUP BY
DetectorID, ScannerType,
YEAR(TIMESTAMP), MONTH(TIMESTAMP), DAY(TIMESTAMP),
DATEPART(HOUR, TIMESTAMP)
GO
This returns these rows:
Row DetectorID TimeStamp ScannerType Pollution VFactor
---------------------------------------------------------------------------------
2 948 2017-03-17 12:44:34.0000000 +01:00 hbd1 0 99
3 948 2017-03-17 13:01:49.0000000 +01:00 hbd1 0 99
4 948 2017-03-17 14:04:29.0000000 +01:00 hbd1 0 99
5 948 2017-03-17 12:44:34.0000000 +01:00 hbd2 0 99
6 948 2017-03-17 13:01:49.0000000 +01:00 hbd2 0 99
It returns 1 row for every new hour.
Now, the column Row is Not Null. This is good. However I want the same for the column TimeStamp.
What I tried to do is:
CREATE VIEW [dbo].[vwContaminationHistorydateee]
AS
SELECT
ISNULL(ROW_NUMBER() OVER (ORDER BY DetectorID), 9999) AS Row,
MAX(DetectorID) AS DetectorID,
ISNULL(MIN(TIMESTAMP) OVER (ORDER BY TIMESTAMP), '1900-01-01 12:00:00.0000000 +00:00') AS TIMESTAMP,
MAX(ScannerType) AS ScannerType,
MAX(Pollution) AS Pollution,
MAX(VFactor) AS VFactor
FROM
ContaminationHistory
GROUP BY
DetectorID, ScannerType,
YEAR(TIMESTAMP), MONTH(TIMESTAMP), DAY(TIMESTAMP),
DATEPART(HOUR, TIMESTAMP)
GO
However, when I excecute this, I get an error:
Msg 8120, Level 16, State 1, Procedure vwContaminationHistorydateeee, Line 15
Column 'ContaminationHistory.TimeStamp' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
When I remove the Year, Month, Day and Hour group by, and replace them with TimeStamp. the view works. But then it does not group by Timestamp, and it will give me for example 15 results per hour. While I want 1.
How can I turn the TimeStamp column into NOT NULL and keep the group by clause as I have it now?
Update:
I changed the view to:
CREATE VIEW [dbo].[vwContaminationHistorydate2]
AS
SELECT
ISNULL(ROW_NUMBER() OVER (ORDER BY DetectorID), 9999) AS Row,
MAX(DetectorID) as DetectorID,
ISNULL(MIN(TimeStamp) OVER (ORDER BY TimeStamp), '1900-01-01 12:00:00.0000000 +00:00') as TimeStamp,
MAX(ScannerType) AS ScannerType,
MAX(Pollution) AS Pollution,
MAX(VFactor) AS VFactor
FROM
ContaminationHistory
GROUP BY
DetectorID, ScannerType,
YEAR(ISNULL(TIMESTAMP,'1900-01-01 12:00:00.0000000 +00:00')),
MONTH(ISNULL(TIMESTAMP,'1900-01-01 12:00:00.0000000 +00:00')),
DAY(ISNULL(TIMESTAMP,'1900-01-01 12:00:00.0000000 +00:00')),
DATEPART(HOUR, ISNULL(TIMESTAMP,'1900-01-01 12:00:00.0000000 +00:00'))
GO
And still get the error:
Msg 8120, Level 16, State 1, Procedure vwContaminationHistorydate2, Line 14
Column 'ContaminationHistory.TimeStamp' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
When i run this view:
CREATE VIEW [dbo].[vwContaminationHistory2]
AS
SELECT ISNULL(ROW_NUMBER() OVER (ORDER BY DetectorID), 9999) AS Row,
MAX(DetectorID) AS DetectorID,
MIN(ISNULL(TIMESTAMP,'1900-01-01 12:00:00.0000000 +00:00')) AS TIMESTAMP,
MAX(ScannerType) AS ScannerType,
MAX(Pollution) AS Pollution,
MAX(VFactor) AS VFactor
FROM ContaminationHistory
GROUP BY DetectorID,
ScannerType,
YEAR(ISNULL(TIMESTAMP,'1900-01-01 12:00:00.0000000 +00:00')),
MONTH(ISNULL(TIMESTAMP,'1900-01-01 12:00:00.0000000 +00:00')),
DAY(ISNULL(TIMESTAMP,'1900-01-01 12:00:00.0000000 +00:00')),
DATEPART(HOUR, ISNULL(TIMESTAMP,'1900-01-01 12:00:00.0000000 +00:00'))
GO
The results are correct. However the row TimeStamp is set to null, and i want it to be Not Null.
Try This and hope it works for you
CREATE VIEW [dbo].[vwContaminationHistory]
AS
SELECT ISNULL(ROW_NUMBER() OVER (ORDER BY DetectorID), 9999) AS Row,
MAX(DetectorID) AS DetectorID,
ISNULL(MIN(ISNULL(TIMESTAMP,'1900-01-01 12:00:00.0000000 +00:00')),'1900-01-01 12:00:00.0000000 +00:00') AS TIMESTAMP,
MAX(ScannerType) AS ScannerType,
MAX(Pollution) AS Pollution,
MAX(VFactor) AS VFactor
FROM ContaminationHistory
GROUP BY DetectorID,
ScannerType,
YEAR(ISNULL(TIMESTAMP,'1900-01-01 12:00:00.0000000 +00:00')),
MONTH(ISNULL(TIMESTAMP,'1900-01-01 12:00:00.0000000 +00:00')),
DAY(ISNULL(TIMESTAMP,'1900-01-01 12:00:00.0000000 +00:00')),
DATEPART(HOUR, ISNULL(TIMESTAMP,'1900-01-01 12:00:00.0000000 +00:00'))
GO
Or you could just do this
SELECT Row,
DetectorID,
ISNULL(TIMESTAMP, '1900-01-01 12:00:00.0000000 +00:00') AS TIMESTAMP,
ScannerType,
Pollution,
VFactor
FROM [dbo].[vwContaminationHistory]
Sample Run --
;WITH cte_SampleData(Row,DetectorID,TimeStamp,ScannerType,Pollution,VFactor) AS
(
SELECT 2,948,'2017-03-17 12:44:34.0000000 +01:00','hbd1',0,99 UNION ALL
SELECT 3,948,'2017-03-17 13:01:49.0000000 +01:00','hbd1',0,99 UNION ALL
SELECT 4,948,'2017-03-17 14:04:29.0000000 +01:00','hbd1',0,99 UNION ALL
SELECT 5,948,'2017-03-17 12:44:34.0000000 +01:00','hbd2',0,99 UNION ALL
SELECT 5,948,NULL,'hbd2',0,99 UNION ALL
SELECT 6,948,'2017-03-17 13:01:49.0000000 +01:00','hbd2',0,99
)
--SELECT * FROM cte_SampleData
SELECT ISNULL(ROW_NUMBER() OVER (ORDER BY DetectorID), 9999) AS Row,
MAX(DetectorID) AS DetectorID,
ISNULL(MIN(ISNULL(TIMESTAMP,'1900-01-01 12:00:00.0000000 +00:00')),'1900-01-01 12:00:00.0000000 +00:00') AS TIMESTAMP,
MAX(ScannerType) AS ScannerType,
MAX(Pollution) AS Pollution,
MAX(VFactor) AS VFactor
FROM cte_SampleData
GROUP BY DetectorID,
ScannerType,
YEAR(ISNULL(TIMESTAMP,'1900-01-01 12:00:00.0000000 +00:00')),
MONTH(ISNULL(TIMESTAMP,'1900-01-01 12:00:00.0000000 +00:00')),
DAY(ISNULL(TIMESTAMP,'1900-01-01 12:00:00.0000000 +00:00')),
DATEPART(HOUR, ISNULL(TIMESTAMP,'1900-01-01 12:00:00.0000000 +00:00'))