Una Query con DateTime nel Where clause

martedì 27 marzo 2012 - 15.57
Tag Elenco Tags  SQL Server 2005

aabruzzese Profilo | Junior Member


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

> 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

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

>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

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

>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


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

>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

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
Partecipa anche tu! Registrati!
Hai bisogno di aiuto ?
Perchè non ti registri subito?

Dopo esserti registrato potrai chiedere
aiuto sul nostro Forum oppure aiutare gli altri

Consulta le Stanze disponibili.

Registrati ora !
Copyright © dotNetHell.it 2002-2017
Running on Windows Server 2008 R2 Standard, SQL Server 2012 & ASP.NET 3.5