Geek asks and answers

Query Records and Time Block Group

I have an application that can be run several times a day. Each run leads to the fact that the data is written to the table to report events that occurred. The main report table looks something like this:

Id    SourceId    SourceType    DateCreated
5048  433         FILE          5/17/2011 9:14:12 AM
5049  346         FILE          5/17/2011 9:14:22 AM
5050  444         FILE          5/17/2011 9:14:51 AM
5051  279         FILE          5/17/2011 9:15:02 AM
5052  433         FILE          5/17/2011 12:34:12 AM
5053  346         FILE          5/17/2011 12:34:22 AM
5054  444         FILE          5/17/2011 12:34:51 AM
5055  279         FILE          5/17/2011 12:35:02 AM

I can say that there were two runs, but I would like to be able to query the date range, the number of times the process was running. I would like to receive a request that will start the process and the number of files in the group. This query causes what I want in terms of what I can see, what day and hour and how many files were launched, but not quite as I would like. And he would not fit the runs that took place from 8:58 to 9:04, for example. He will also group runs that started at 9:02 and 9:15, for example.

Select dateadd(day,0,datediff(day,0,DateCreated)) as [Date], datepart(hour, DateCreated) as [Hour], Count(*) [File Count]
From   MyReportTable
Where DateCreated between '5/4/2011' and '5/18/2011'
    and SourceType = 'File'
Group By dateadd(day,0,datediff(day,0,DateCreated)), datepart(hour, DateCreated)
Order By dateadd(day,0,datediff(day,0,DateCreated)), datepart(hour, DateCreated)

, , , , , . .

!

+3
2

, , Id . , 1 , , . , , , .

.

SELECT mrtB.Id, mrtB.DateCreated
FROM MyReportTable AS mrtA
INNER JOIN MyReportTable AS mrtB
    ON (mrtA.Id + 1) = mrtB.Id
WHERE DateDiff(mi, mrtA.DateCreated, mrtB.DateCreated) >= 1

DataRunStarts

, ,

SELECT drsA.Id AS StartID, drsA.DateCreated, Min(drsB.Id) AS ExcludedEndId
FROM DataRunStarts AS drsA, DataRunStarts AS drsB
WHERE (((drsB.Id)>[drsA].[id]))
GROUP BY drsA.Id, drsA.DateCreated

DataRunGroups. "", , , , .

DataRunGroups MyReportTable

SELECT DataRunGroups.StartID, Count(MyReportTable.Id) AS CountOfRecords
FROM DataRunGroups, MyReportTable
WHERE (((MyReportTable.Id)>=[StartId] And (MyReportTable.Id)<[ExcludedEndId]))
GROUP BY DataRunGroups.StartID;

DataRunCounts

DataRunGroups DataRunCounts , .

SELECT DataRunGroups.DateCreated, DataRunCounts.CountOfRecords
FROM DataRunGroups
INNER JOIN DataRunCounts
    ON DataRunGroups.StartID = DataRunCounts.StartID;

, . , , id, id. , DataRunGroups DataRunGroups. , DataRunGroups, , .

+2

:

SELECT
    Count(Id), 
    DATEPART(year, DateCreated) As yr, 
    DATEPART(month, DateCreated) As mth, 
    DATEPART(day, DateCreated) As day, 
    DATEPART(Hour, DateCreated) as hr, 
    DATEPART(minute, DateCreated) as mnt
FROM 
    MyReportTable
WHERE DateCreated between '5/4/2011' and '5/18/2011'
    and SourceType = 'File'
GROUP BY 
    DATEPART(year, DateCreated), 
    DATEPART(month, DateCreated), 
    DATEPART(day, DateCreated), 
    DATEPART(Hour, DateCreated),
    DATEPART(minute, DateCreated)
ORDER BY 
    DATEPART(year, DateCreated),
    DATEPART(month, DateCreated), 
    DATEPART(day, DateCreated), 
    DATEPART(Hour, DateCreated),
    DATEPART(minute, DateCreated)

Edit

15- ,

(DATEPART(minute, DateCreated)/15)

( +1 , 1,2,3,4).

+3

More articles:


All Articles