dotNetHell.it - Forum - Una Query con DateTime nel Where clause

aabruzzese Profilo | Junior Member

89 messaggi | Data Invio: mar 27 mar 2012 - 15:57

Ciao a Tutti o una domanda su una Query con DataTime:

Diciammo cosi:

Una tabella SQL di statistice:

Statistic_Time type datetime
Some_ Mumber type int

La query che sto provando di finire:

SELECT TOP (9) Statistic_Time , Some_Number
FROM STATS
WHERE XXXXXXXX AND (DATEPART(dw, statistic_Time) = @giornodisettimana)

Il XXXXX = le prime 9 righe della tavola dove la Statistic_Time e esstrate sulla base di questa query:

set @Giorno_Ora_Inizio = (select TOP (1) DATEADD(D, 0, DATEDIFF(D, 0, Statistic_Time )) from STATS
WHERE (DATEPART(dw, Statistic_Time) = @giornodisettimana));

Questa query resulta = @Giorno_Ora_Inizio = 2011-10-24 00:00:00

Poi con questo voglio mandare la Query con il XXXX che mi risulta

Statistic_Time == 2011-10-24 00:00:00
2011-10-17 00:00:00
2011-10-10 00:00:00
2011-10-03 00:00:00
2011-09-XX 00:00:00

Fina 9 settimana sul stesso giorno ... tutti le righe son di Lunedi or Martedi etc..etc..

Lo so che in Italiano non va bene ma cmq, lo scrivo in Inglese si bosogno.

Ciao
Angelo

Angelo Abruzzese

lbenaglia Profilo | Guru

5.625 messaggi | Data Invio: mar 27 mar 2012 - 18:05

> Ciao a Tutti o una domanda su una Query con DataTime:

Ciao Angelo,

Posta i comandi di CREATE TABLE ed INSERT INTO ed il result set atteso con quei dati.

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/

aabruzzese Profilo | Junior Member

89 messaggi | Data Invio: mer 28 mar 2012 - 16:24

CREATE TABLE [dbo].[STATS](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[StatType] [int] NOT NULL,
[StatisticTime] [datetime] NOT NULL,
[HowMany] [int] NOT NULL
)

Lorenzo, ti scrivo in Inglese perche mi va un po meglio cosi:

This is a private Third Party Database so I do not have access to any of the insert functions, the main idea here is that I am trying to parse the table to extract all the records that match a WeekDay at a given HH:MM:SS.

So just imagine the table is populated like this:

1 5 2012-01-09 00:00:00 200
1 5 2012-01-09 00:01:00 175
1 5 2012-01-09 00:02:00 200
1 5 2012-01-09 00:03:00 175
1 5 2012-01-09 00:04:00 200
1 5 2012-01-09 00:05:00 175
..
..
..
1 5 2012-01-09 00:23:59 175
1 5 2012-01-10 00:00:00 364
1 5 2012-01-10 00:01:00 154

So lets say for the sake of illustration 2012-01-09 happens to be a Monday

My query needs to extract all records in the Database that occur on a Monday starting from the most recent entry
found for the [StatType] and by matching hh:mm:00

So basically the query would look for all entries

Where StatType = XX AND
DatePart DAY OF WEEK of StatisticTime is equal to (3) AND
DatePart HH:MM:SS of Statistictime is equal to the HH:MM:SS of Statistictime of the First Record that was found earlier
FOR X number of weeks backwards.

Spero che cosi sia pui facile di capire il cosa voglio fare.

Angelo

Angelo Abruzzese

lbenaglia Profilo | Guru

5.625 messaggi | Data Invio: mer 28 mar 2012 - 17:03

>Spero che cosi sia pui facile di capire il cosa voglio fare.

Angelo,

can you post some INSERT commands and the desired result set with those rows?

Thanks,

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/

aabruzzese Profilo | Junior Member

89 messaggi | Data Invio: mer 28 mar 2012 - 17:21

Ciao Lorenzo,

I do not have access to the actual Insert Commands being used by the third party application, I only have access at a query level.

So basically I am pulling records not inserting any, as I showed you in the structure the field is a DateTime field

CREATE TABLE [dbo].[STATS](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[StatType] [int] NOT NULL,
[StatisticTime] [datetime] NOT NULL,
[HowMany] [int] NOT NULL
)

A typical insert as scripted by the SQL server management tool .. not the actual application:

INSERT INTO [POSDB].[dbo].[STATS]
([StatType]
,[StatisticTime]
,[HowMany]
)
VALUES
(<StatType, int,>
,<StatisticTime, datetime,>
,<HowMany, int,>
)

So if you view it in a Grid Control you would see...

ID  Stat Type                    StatTime                            How Many 
0  21                             2012-03-12 19:49:00   21  
1  21                             2012-03-12 19:48:00   120
2  21                             2012-03-12 19:47:00   221
3  21                             2012-03-12 19:46:00   231
4  21                             2012-03-12 19:45:00   156
5  21                             2012-03-12 19:44:00   44
6  21                             2012-03-12 19:43:00   891
7  21                             2012-03-12 19:42:00   990
8  21                             2012-03-12 19:41:00   100
9  21                             2012-03-12 19:40:00   120

Keep in mind there are millions of records here, we keep several weeks of data and that runs into the millions of transactions.

Spero che cosi va meglio ...

Angelo

Angelo Abruzzese

lbenaglia Profilo | Guru

5.625 messaggi | Data Invio: mer 28 mar 2012 - 21:39

>Spero che cosi va meglio ...
No, non ci siamo

Without some sample rows and the desired result set WITH THOSE ROWS I'm not able to suggest you any solution.

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/

aabruzzese Profilo | Junior Member

89 messaggi | Data Invio: gio 29 mar 2012 - 00:56

Lorenzo,

What I showed you there in a grid format is sample data...

What is it that is missing?

Anyways tomorrow I will post a part of the solution so you can understand but really
I don't see how it is only as clear as mud.

:)

Angelo Abruzzese

lbenaglia Profilo | Guru

5.625 messaggi | Data Invio: gio 29 mar 2012 - 10:04

>What I showed you there in a grid format is sample data...
>
> What is it that is missing?
The result set desired with those rows.

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/

aabruzzese Profilo | Junior Member

89 messaggi | Data Invio: gio 29 mar 2012 - 14:45

Ok here it is in simple terms ...

The Result set I want to see ...

All the Rows in the Stats Time are organized by a DateTime column .. so using the Day of Week Part of that column I will return all rows that match a specific day of the week and for a given minute of the day as that stats are collected on a minute by minute interval.

set @NextIntervalHourMinuteSeconds = (select TOP (1) DATEADD(D, 0, DATEDIFF(D, 0, StatisticTime)) from STATS
WHERE (StatType= @StatType) AND
(DATEPART(dw, StatisticTime) = @dayoftheweek ));

WHILE @StatLoopCount <= (24 * 60 / @interval)
BEGIN
SELECT TOP(@howmanyweekstogobackwards)
[ID],
[StatType],
[StatisticTime],
[HowMany]
FROM [STATS]
WHERE (StatType = @StatType) AND
(DATEPART(dw, StatisticTime) = @dayoftheweek )AND
(CONVERT(VARCHAR(8), StatisticTime, 108) = CONVERT(VARCHAR(8), @NextIntervalHourMinuteSeconds, 108)) ;

SET @StatLoopCount = @StatLoopCount +1 ;
SET @NextIntervalHourMinuteSeconds = DATEADD(minute,@interval,@NextIntervalHourMinuteSeconds);

-- Show the calculate interval on the output ---
SELECT @StatLoopCount as LOOPINDEX;
SELECT @NextIntervalHourMinuteSeconds as NextInterval;

END -- Fin boucle WHILE interval

So basically this select query in a loop will render all the records in the table that are for a StatType = 1, Dayoftheweek = 3, NextIntervalHourMinuteSeconds = 00:00:00.000 and then incremement by 5 minutes for each loop .. so on the next pass it will be 00:05:00.0000 and so on until 23:55:00.000

It is a cursor loop on the StatType (Driving Table) then a select clause to return the most recent row for that StatType ...say 2012-03-29 00:00:00.000 and then find top 9 records for that StatType going backwards for 9 weeks with matching HH:MM:SS.000

so imagine the result is .. On the first Pass of the While Loop ...

ID Stat Type StatTime How Many
0 21 2012-03-29 00:00:00.000 21
1 21 2012-03-22 00:00:00.000 120
2 21 2012-03-15 00:00:00.000 221
3 21 2012-03-01 00:00:00.000 231
4 21 2012-02-24 00:00:00.000 21
6 21 2012-02-17 00:00:00.000 891
7 21 2012-02-10 00:00:00.000 990
8 21 2012-02-03 00:00:00.000 150
9 21 2012-01-26 00:00:00.000 223

on the Second pass ...

ID Stat Type StatTime How Many
0 21 2012-03-29 00:05:00.000 221
1 21 2012-03-22 00:05:00.000 130
2 21 2012-03-15 00:05:00.000 261
3 21 2012-03-01 00:05:00.000 631
4 21 2012-02-24 00:05:00.000 1921
6 21 2012-02-17 00:05:00.000 8391
7 21 2012-02-10 00:05:00.000 9590
8 21 2012-02-03 00:05:00.000 1510
9 21 2012-01-26 00:05:00.000 12223

etc..etc.. until I get to the last iteration of the loop at 23:55:00.000

I hope that makes it clear and besides this loop construct seems to work for now, next I am going to add the AVG and STD Deveation functions
and need to figure out how to remove the min and max from the result set when calculating the values.

Angelo

Angelo Abruzzese