Question

Locked

query help

By hameethasheik ·
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

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Theres a few ways of doing this

by Tony Hopkinson In reply to query help

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 = 1 Group By UserNo,Edate

Insert #TempLinks2
Select UserNo,Convert(DateTime,Convert(Int,Tdate)) as EDate,Max(Tdate) From Linked Where

Back to Web Development Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums