SQL Server Performance Forum – Threads Archive

Grouping on view

Hello all,
The hiring of a DBA for more advance application was the best thing for this project, and he did solve my problem at the time. However my customer want to change a query in the view. I had the grouping of the date time to the quarter of the hour. So that when the date ime insert format was "Date 08:00:45", the view would show "Date 08:00:00". Well, now the date/time is inserted after the 1 min "Date 08:01:15" and the view would show "Date 08:01:00". Well, I need the view to show "Date 08:00:00". Here is the code which was written by the DBA. (im still a student with this)
SELECT STR(YEAR(date), 4, 0) + ‘-‘ + REPLACE(STR(MONTH(date), 2, 0), ‘ ‘, ‘0’) + ‘-‘ + REPLACE(STR(DAY(date), 2, 0), ‘ ‘, ‘0’) + ‘ ‘ + REPLACE(STR(DatePart(hh, date), 2, 0), ‘ ‘, ‘0’) + ‘:’ + REPLACE(STR(DatePart(n, date), 2, 2), ‘ ‘, ‘0’) as Date,
max(Pressure) as Pressure, max(Temps) as Temps, max (Humidity) as Humidity, max (Occupied)
as Occupied, Room
FROM v24hrSolids
GROUP BY STR(YEAR(date), 4, 0) + ‘-‘ + REPLACE(STR(MONTH(date), 2, 0), ‘ ‘, ‘0’) + ‘-‘ + REPLACE(STR(DAY(date), 2, 0), ‘ ‘, ‘0’) + ‘ ‘ + REPLACE(STR(DatePart(hh, date), 2, 0), ‘ ‘, ‘0’) + ‘:’ + REPLACE(STR(DatePart(n, date), 2, 2), ‘ ‘, ‘0’), Room
I did try the "insert code" button, however not sure how to do that correctly.
Thanks The
Bugster

Do you need the view to show "Date 08:00:00" until the time is 9 and so on? Date/Time Show
Date 08:00:45 Date 08:00:00
Date 08:01:15 Date 08:00:00
Date 08:45:00 Date 08:00:00
Date 08:59:45 Date 08:00:00
Date 09:00:15 Date 09:00:00
CanadaDBA

The data is inserted with a date time around every 15 minutes. When data is collected every quarter of the hour, sometimes the date ime exeeds the 1 min mark, however still needs to be changed for view to the quarter of hour. (I have yet to see the data insert more than 2 min past the quarter hour in 3 years). So the date/time show: Insert Show
Date 08:00:00 Date 08:00:00
Date 08:01:01 Date 08:00:00
Date 08:15:40 Date 08:15:00
Date 08:16:01 Date 08:15:00
Date 08:30:26 Date 08:30:00
Date 08:31:04 Date 08:30:00 I hope this was a better explanation than before.
I greatly appriciate the assistance on this matter. The
Bugster

The following script is not a solution for your case. You need a GROUP BY command but it gives an idea to find the final solution.
DECLARE @dDate DateTime, @vDate VarChar(50)
SET @dDate = GetDate() SET @vDate = STR(YEAR(@dDate), 4, 0) + ‘-‘ + REPLACE(STR(Month(@dDate), 2, 0), ‘ ‘, ‘0’) + ‘-‘ + REPLACE(STR(Day(@dDate), 2, 0), ‘ ‘, ‘0’) + ‘ ‘ + REPLACE(STR(DatePart(hh, @dDate), 2, 0), ‘ ‘, ‘0’) + ‘:’ + REPLACE(STR(DatePart(n, @dDate), 2, 2), ‘ ‘, ‘0’) SELECT @vDate, SubString(@vDate, 1, 14) +
CASE WHEN SubString(@vDate, 15, 2) < 15 THEN ’00’
WHEN SubString(@vDate, 15, 2) >= 15 AND SubString(@vDate, 15, 2) < 30 THEN ’15’
WHEN SubString(@vDate, 15, 2) >= 30 AND SubString(@vDate, 15, 2) < 45 THEN ’30’
WHEN SubString(@vDate, 15, 2) >= 45 THEN ’45’
END + ‘:00’
CanadaDBA

Thank you….I will do some more research now and hopefully apply this to a solution. So much to learn. So little time.
Thanks again…. The
Bugster

If I understand this correctly, you are trying to display your data in 15 minutes buckets, right? If so, see if this helps:
DROP TABLE #time
CREATE TABLE #time
(
ID INT IDENTITY
, dt DATETIME
, Someothercolumn INT
)
INSERT INTO #time
SELECT’20050427 08:00:00′, 1
UNION ALL
SELECT’20050427 08:01:00′, 2
UNION ALL
SELECT’20050427 08:14:00′, 1
UNION ALL
SELECT ‘20050427 08:15:00’, 20206
UNION ALL
SELECT ‘20050427 08:15:00’, 1
UNION ALL
SELECT’20050427 10:55:00′, NULL
UNION ALL
SELECT ‘20050427 10:56:00’, 20212 SELECT MIN(dt) StartOfPeriod, MAX(dt) EndOfPeriod, COUNT(SomeOtherColumn) No_#, MIN(Someothercolumn) SomeOtherColumn
FROM #time t
GROUP BY DATEADD(n,-DATEPART(n,dt)%15,dt), SomeOtherColumn
ORDER BY StartOfPeriod StartOfPeriod EndOfPeriod No_# SomeOtherColumn
—————————————————— —————————————————— ———– —————
2005-04-27 08:00:00.000 2005-04-27 08:14:00.000 2 1
2005-04-27 08:01:00.000 2005-04-27 08:01:00.000 1 2
2005-04-27 08:15:00.000 2005-04-27 08:15:00.000 1 20206
2005-04-27 08:15:00.000 2005-04-27 08:15:00.000 1 1
2005-04-27 10:55:00.000 2005-04-27 10:55:00.000 0 NULL
2005-04-27 10:56:00.000 2005-04-27 10:56:00.000 1 20212 (6 row(s) affected) You notice, that no data contains a seconds time part. If your data however, contain such a time part, you must change the statement to
INSERT INTO #time SELECT ‘20050427 12:56:01’, 20212
INSERT INTO #time SELECT ‘20050427 12:59:59’, 20212 SELECT MIN(dt) StartOfPeriod, MAX(dt) EndOfPeriod, COUNT(SomeOtherColumn) No_#, MIN(Someothercolumn) SomeOtherColumn
FROM #time t
GROUP BY DATEADD(n,-DATEPART(n,DATEADD(second,-DATEPART(second,dt),dt))%15,DATEADD(second,-DATEPART(second,dt),dt)), SomeOtherColumn
ORDER BY StartOfPeriod StartOfPeriod EndOfPeriod No_# SomeOtherColumn
—————————————————— —————————————————— ———– —————
2005-04-27 08:00:00.000 2005-04-27 08:14:00.000 2 1
2005-04-27 08:01:00.000 2005-04-27 08:01:00.000 1 2
2005-04-27 08:15:00.000 2005-04-27 08:15:00.000 1 20206
2005-04-27 08:15:00.000 2005-04-27 08:15:00.000 1 1
2005-04-27 10:55:00.000 2005-04-27 10:55:00.000 0 NULL
2005-04-27 10:56:00.000 2005-04-27 10:56:00.000 1 20212
2005-04-27 12:56:01.000 2005-04-27 12:59:59.000 2 20212 (7 row(s) affected) HTH —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

tada!
Yes, that is what I am attempting to accomlish. I will work on this.
Thanks alot you all!
again….lol The
Bugster

]]>