Min,Max for a time Range and Sum of Records for the last date/time

Advertisement
Hi All,
I have a table with the following structure/data:
create table  Events   (
                                    [EventID]       
int                   NOT NULL,
                                    [Title]            
nvarchar(200)  NOT NULL,
                                    [SourceName]  nvarchar(20)    NOT NULL,
                                    [Type]             
int                  NOT NULL,
                                    [eDate]           
datetime
insert into Events values(100, 'Event 1', 'S01', 3,'2014-01-01 00:00:00.000')
insert into Events values(100, 'Event 1', 'S07', 3,'2014-01-01 00:00:00.000')
insert into Events values(100, 'Event 1', 'S08', 3,'2014-01-01 00:00:00.000')
insert into Events values(100, 'Event 1', 'S09', 3,'2014-01-01 00:00:00.000')
insert into Events values(101, 'Event 2', 'S010', 3,'2014-01-01 00:00:00.000')
insert into Events values(102, 'Event 3', 'S03', 3,'2014-01-01 00:00:00.000')
insert into Events values(100, 'Event 1', 'S04', 3,'2014-01-01 00:00:00.000')
insert into Events values(101, 'Event 2', 'S05', 3,'2014-01-01 00:00:00.000')
insert into Events values(102, 'Event 3', 'S06', 3,'2014-01-01 00:00:00.000')
insert into Events values(100, 'Event 1', 'S01', 3,'2014-02-01 00:00:00.000')
insert into Events values(101, 'Event 2', 'S02', 3,'2014-02-01 00:00:00.000')
insert into Events values(102, 'Event 3', 'S03', 3,'2014-02-01 00:00:00.000')
insert into Events values(100, 'Event 1', 'S04', 3,'2014-02-01 00:00:00.000')
insert into Events values(101, 'Event 2', 'S05', 3,'2014-02-01 00:00:00.000')
insert into Events values(102, 'Event 3', 'S06', 3,'2014-02-01 00:00:00.000')
insert into Events values(100, 'Event 1', 'S01', 3,'2014-03-01 00:00:00.000')
insert into Events values(101, 'Event 2', 'S02', 3,'2014-03-01 00:00:00.000')
insert into Events values(102, 'Event 3', 'S03', 3,'2014-03-01 00:00:00.000')
insert into Events values(100, 'Event 1', 'S04', 3,'2014-03-01 00:00:00.000')
insert into Events values(101, 'Event 2', 'S05', 3,'2014-03-01 00:00:00.000')
insert into Events values(102, 'Event 3', 'S06', 3,'2014-03-01 00:00:00.000')
And I wrote the following query:
 select EventID as [Event ID],
       Title, 
       count(distinct(SourceName)) as [Instances], 
       Type,
       min(eDate) as  [First Detected],
       max(eDate) as [Last Detected],
       datediff(d,min(eDate),max(eDate)) as [Delta (days)]
from  Events
where type = 3
group by EventID, Title, Type
having max(eDate) <> min(eDate)
   and max(eDate) =(select top 1 eDate from Events order by eDate desc)
and I get the following results (see the instance number)
Event ID Title         Instances Type First Detected                      Last Detected                    
   Delta (days)
=============================================================================================================================
100         Event 1         5         3    2014-01-01 00:00:00.000     2014-03-01 00:00:00.000    
59
101         Event 2        
3         3    2014-01-01 00:00:00.000     2014-03-01 00:00:00.000     59
102         Event 3        
2         3    2014-01-01 00:00:00.000     2014-03-01 00:00:00.000     59
This is normal for this query however what I need to do is a little different.
In other words, while I need to show when we recorded a specific event first and last time,
I need to display the results for the last date/time when it was recorded. 
For example what I need to provide should look like this:
Event ID  Title                Instances        Type       First Detected                    
Last Detected                          Delta (days)
=============================================================================================================================
100         Event 1            2                   3           
2014-01-01 00:00:00.000     2014-03-01 00:00:00.000      59
101         Event 2            2                   3           
2014-01-01 00:00:00.000     2014-03-01 00:00:00.000      59
102         Event 3           
2                   3            2014-01-01 00:00:00.000     2014-03-01 00:00:00.000     
59
Could you please help me to fix this query?
TIA,
John
Advertisement

Replay

;With cte As
(Select EventID as [Event ID],
Title,
SourceName,
Type,
min(eDate) Over(Partition By EventID, Title, Type) as [First Detected],
max(eDate) Over(Partition By EventID, Title, Type) as [Last Detected],
eDate,
datediff(d,min(eDate) Over(Partition By EventID, Title, Type),max(eDate) Over(Partition By EventID, Title, Type)) as [Delta (days)],
max(eDate) Over() As MaxEDate
from Events
where type = 3)
Select [Event ID],
Title,
COUNT(Distinct SourceName) As Instances,
Type,
[First Detected],
[Last Detected],
[Delta (days)]
From cte
Where eDate = MaxEDate And [First Detected] <> [Last Detected]
Group By [Event ID],
Title,
Type,
[First Detected],
[Last Detected],
[Delta (days)];
Tom
P.S. Thanks for providing the DDL and data.  That is always very helpful.

I am having a lot of trouble downloading itunes.My latest error message is that MediaToolbox.dll is missing. I have been trying to fix this for three days now .And it all started with the last update .Can anyone help?

I am having a lot of trouble downloading itunes.My latest error message is that MediaToolbox.dll is missing. I have been trying to fix this for three days now .And it all started with the last update .Can anyone help?Taken at face value, you're havin

Is anyone else having the finish rub off of the keys on their macbook? I've had mine for about 6 months and several keys look like the finish is wearing through. I went to the local genius bar today and the techs said they'd never seen this before.

Is anyone else having the finish rub off of the keys on their macbook? I've had mine for about 6 months and several keys look like the finish is wearing through. I went to the local genius bar today and the techs said they'd never seen this before.So

Can I view photos and movies from my external hard drive? I used Time Machine and a WD Passport for backup. Since my drive on my MacBook Pro is filling up I was planning to delete some of the movies and photos.

Can I view photos and movies from my external hard drive? I used Time Machine and a WD Passport for backup. Since my drive on my MacBook Pro is filling up I was planning to delete some of the movies and photos. I wanted to see if I could view them fr

Sql statement for retrieving the last update time of a table

Hello all, Can somebody give me an example of sql statement for retrieving the last update time of an oracle table. Thank you IlThanks for the fast replies. It works great when I test it as a sql statement but when trying to populate a datalist with

I want to disable "restore previous session" and enable "History record" at the same time.

I want to disable "restore previous session" and enable "History record" at the same time. Because I don't want others to access my account such as "Gmail", "Facebook". But I want firefox to record my browsing histo

I bought new Ipod touch G4 for my little girl  and she by mistake wipe the Apple store icon

I bought new Ipod touch G4 for my little girl  and she by mistake wipe the Apple store icon I tried to go to reset display but could not bring the App store back to live what can I do to bring the big A blue app icon back in the first pageCheck to ma

HT201303 I never set up any security questions for my Apple ID and I was asked on the app store to sign in and answer my security questions. I never made security questions in the first case. I can't purchase anything without them. What should I do?

I never set up any security questions for my apple ID and I was asked on the app store to sign in and answer my security questions, I never made any in the first place and it came up with questions that I didn't know. I've already been in support and

I've had my HTC Rhyme for about 4 months and it updated automatically to the software that was right before Google Play.  When I try to update to Google Play, it says there's no service, but there is.  What should I do?

I've had my HTC Rhyme for about 4 months and it updated automatically to the software that was right before Google Play.  When I try to update to Google Play, it says there's no service, but there is.  What should I do?    Hello! No Play Store, sound

I have an issue syncing my iPhone 4 with itunes. This has only happened the last few times i have tried but don't know how to resolve it. Please help.

Hi, the last few times i have tried to sync my iPhone 4 to my itunes it won't work. I plug my phone into my computer, to sync any new pictures i have taken or add my new music i have bought on my phone, and hit sync. It appears to start syncing then

Converting Delivery Creation Date/Time into the Local Date/Time

Hi I have two fields like 'Delivery Creation Date' and 'Delivery Creation Time' in ECC system . My user wants the Delivery Creation Date / Delivery Creation Time in Local time of Plants . For example : Delivery    Plant       DelivCreationDate      

When close firefox and reopen, it open all the last webs and popups visited today

I use firefox to work. I'm web developer and in the firefox have instlalled 3 complements and 8 plugs. When use Firefox, and in different tabs open: GMAIL, web normaly, other web, google, conection with my server plesk, ... Normaly use 6 to 8 tabs in

Get the creation date time of the file located in persentation server

Hi Experts, I have a requirement where i need to fetch the creation date time of a file. ex. file path :  C:\Documents and Settings\file.xml I have the file path, i need to know when this file is created. Is there any GUI services or FM to achieve th

How to get sqlplus to print the current date/time

From a unix command line I can type: prompt> date; <any_Cmd>; date This will result in a timestamp being output before and after the command you inserted. Is there a way to make sqlplus print the current date/time after it is done executing an sq

Can I create a Depart Date for a record, from the Arrive Date in the next record?

I have a Customer database with the tables tblCustomers and tblAddress. The tblCustomers has the following fields; CustomerID, CustomerName. The tblAddress has the following fields; CustomerID, Address, Town, County, ArriveDate. When a customer calls

I just opened my mail on icloud and all messages disappeared from the last year! I have one email left in my inbox which is the most recent. I moved to icloud 2 weeks ago and it has been working fine until now. How can I retrieve old messages? Thank you.

i just opened my mail on icloud and all messages disappeared from the last year! I have one email left in my inbox which is the most recent. I moved to icloud 2 weeks ago and it has been working fine until now. How can I retrieve old messages? Thank

When I share a video with fcpx and it goes to Quicktime, the last few seconds goes mute. I checked the clip and sent that the same route and all is fine. It is only when I send the whole 3gb video. Any ideas?

When I share a video with fcpx and it goes to Quicktime, the last few seconds goes mute. I checked the clip and sent that the same route and all is fine. It is only when I send the whole 3gb video. Any ideas?paul71054 wrote: ... Any ideas? add a few

My itunes won't update. It's looking for 10.6.0.40. Last time it tried to update, I downloaded the msi to my desktop and I deleted it within the last six months. Now I can't uninstall or update.

The last time my iTunes couldn't update. I used support from Itunes and downloaded it my desktop. My mistake. Because without thinking, I deleted it within the last six months from my desktop, without thinking I'd need it later. For the past month I'

Best way to secure a Time Capsule and it's files for a house rental.

We're renting our house and have a time capsule that we use for backing up our MacBooks and for our network. We'd like to keep the network available to our renters (as well as the airplay speakers on a couple different APExpress's if possible.)  Any

ITunes (11 I think) in smart playlists and folders won't apply the "Last Played" search criteria in real time.  Please fix!!!

The major iTunes recent makeover, generally *****.  It's ugly to look at; it's grey and blue-grey and more grey.  god, how drab can you get.  You know how in the Finder we can assign colors to files, folders & applications.  It would be fantastic to