SQL Query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Query help

Author

Topic

gindaph
Starting Member

3 Posts

Posted - 2014-12-15 : 22:06:45

Is there any way we can query below scenario.

We are querying only 1 table

We want to show a report where in we are receiving messages and we want to show only if we recieve same messages within 5 minutes, then the result is 1 same message.

Muj9
Yak Posting Veteran

75 Posts

Posted - 2014-12-16 : 04:55:06

so when you receive a message is this stored in your table as a column and same messages with in 5 mins of reciving the first message?

it would be easier if you could do a expected result. some thing like this:-

Expected Result
-------------------------------------------

Message date
1 01/01/2014

i don't know what you are expecting? need more information

Muj9
Yak Posting Veteran

75 Posts

Posted - 2014-12-16 : 05:53:11

I am not sure if this is what you are after but see below:-

Create Table MessageTest
(
ID int identity(1,1),
Message1 varchar(max),
Date_time datetime
)

insert into MessageTest (Message1,Date_time)
select 'TEST1',getdate() UNION ALL
select 'TEST2',getdate() UNION ALL
select 'TEST3',getdate() UNION ALL
select 'TEST2',getdate()

update MessageTest
set Date_time = DateADD(mi, +4, Current_TimeStamp)
where id = 4

--------------------------------------

;with cte as (
select
ID
,Message1
,Date_time
,row_number() over (PARTITION by Message1 order by Date_time ) as RN
from MessageTest )
select
*
into Testa
from cte where rn = 1
---------------------------------------
;with cte as (
select
ID
,Message1
,Date_time
,row_number() over (PARTITION by Message1 order by Date_time ) as RN
from MessageTest )
select
*
into Testb
from cte where rn > 1
----------------------------------------
select
a.ID
,a.Message1
,a.Date_time
,b.Message1 as K2
,b.Date_time as DT
into TestC
from Testa a
left join Testb b on a.Message1 = b.Message1
-------------------------------------------
drop table TestD
select *,DATEDIFF(MINUTE,Date_time,DT) as Mins_Diff
,case when DATEDIFF(MINUTE,Date_time,DT) <= 5 then 1 else 0 end as In5Min
into TestD
from TestC
--------------------------------
Drop Table TestA,TestB,TestC
--------------------------------

select
a.*,b.In5Min
from MessageTest a
left join TestD b on a.Message1 = b.K2 and a.Date_time = b.DT

/*
---------------------------------
Drop Table MessageTest,TestD
--------------------------------
*/

Muj9
Yak Posting Veteran

75 Posts

Posted - 2014-12-16 : 06:02:42

your table

ID Message1 Date_time
1 TEST1 2014-12-16 11:01:54.440
2 TEST2 2014-12-16 11:01:54.440
3 TEST3 2014-12-16 11:01:54.440
4 TEST2 2014-12-16 11:05:54.440

Final Result

ID Message1 Date_time In5Min
1 TEST1 2014-12-16 11:01:54.440 NULL
2 TEST2 2014-12-16 11:01:54.440 NULL
3 TEST3 2014-12-16 11:01:54.440 NULL
4 TEST2 2014-12-16 11:05:54.440 1

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-12-16 : 12:18:28

For performance, you'll want the messages table clustered on datetime.


SELECT m1.*
FROM messages m1
WHERE
    EXISTS(
        SELECT 1
        FROM messages m2
        WHERE
            m2.datetime >= m1.datetime AND
            m2.datetime <= DATEADD(MINUTE, 5, m1.datetime) AND
            m2.message = m1.message
        )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources