Web Development

Ask something
Question Locked

query help

0 helpful
2 replies

by hameethasheik

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have the
Table Name: Linked

Table structure:

Eventno Number
Userno number
Tdate date/time
code number

EventNO USERNO Tdate CODE
485120 244 11/2/2005 10:30:00 AM 2
485122 383 11/2/2005 10:31:00 AM 2
485124 119 11/2/2005 10:33:00 AM 2
485126 129 11/2/2005 10:34:00 AM 6
485128 129 11/2/2005 10:36:00 AM 1
485130 110 11/2/2005 10:37:00 AM 6
485132 110 11/2/2005 10:38:00 AM 1
485134 136 11/2/2005 10:38:00 AM 4
485136 110 11/2/2005 10:43:00 AM 1
485138 110 11/2/2005 10:46:00 AM 1
485141 94 11/2/2005 10:48:00 AM 1
485143 94 11/2/2005 10:49:00 AM 3
485145 115 11/2/2005 10:52:00 AM 6

In this table Tdate field have time and date value
I need a query to retreive a maximum time when the code=2 and minimum time when the code=1 group by the userno and datevalue of Tdate.
Atlast I need a answer

Userno tdate minimum_time maximum_time
.
.
.

for all userno

2 replies

by tony hopkinson

Here's the long winded but easy to understand way

Create Table #TempLinks1(UserNo int null,
Tdate Datetime null,minimum_time datetime null)
Create Table #TempLinks(UserNo int null,
Tdate Datetime null,maximum_time datetime null)

Insert #TempLinks
Select UserNo,Convert(DateTime,Convert(Int,Tdate)) as EDate,Min(Tdate) From Linked Where [Code] = 1 Group By UserNo,Edate

Insert #TempLinks2
Select UserNo,Convert(DateTime,Convert(Int,Tdate)) as EDate,Max(Tdate) From Linked Where [Code] = 2 Group By UserNo,Edate

Select UserNo,TDate,Minimum_Date,Maximum_Date
From #TempLinks1 T1,#TempLinks2 T2
Where T1.UserNo = T2.UserNo and T1.Tdate=T2.Tdate
Drop Table #TempLinks1
Drop Table #TempLinks2

If you need to get mins without maxes or vice versa you'll need to outer join them.

Have't syntax checked this but it should be right in principle.

This discussion is closed to new replies.