question

haneefa avatar image
haneefa asked

Creating SQL view from an existing table

I have a table in a sql database as shown below.

I need to create a view to pop up data shown below

The value First In time column is First Transaction of the day with Functionkey feild value ='F1'

The value Last Out time column is Last Transaction of the day with Functionkey feild value ='F2'.

Please help me

Thanks in advance!

sql server 2014views
log-table.jpg (84.5 KiB)
view.jpg (20.4 KiB)
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Mukesh_Kumar avatar image
Mukesh_Kumar answered

You can try like this. hope you can get your required result. functionkey column has common value so you can add or ignore it in result.

create table #tbl (transactiontime datetime, userid int, trerminalid int, fnkey int)
insert into #tbl (transactiontime,userid,trerminalid,fnkey)
select '2019-04-12 05:01:00.000',1,11,0
union
select '2019-04-12 08:01:00.000',1,11,0
union
select '2019-04-12 18:01:00.000',1,11,0
union
select '2019-04-12 05:02:00.000',2,11,0
union
select '2019-04-12 10:01:00.000',2,11,0
union
select '2019-04-12 15:17:00.000',2,11,0
union
select '2019-04-12 18:37:00.000',2,11,0
union
select '2019-04-12 05:00:00.000',3,11,0
union
select '2019-04-12 05:31:00.000',3,11,0
union
select '2019-04-12 15:46:00.000',3,11,0
union
select '2019-04-12 18:48:00.000',3,11,0
union
select '2019-04-12 05:15:00.000',4,11,0
union
select '2019-04-12 17:55:00.000',4,11,0 select * from #tbl
select convert(date,transactiontime) As TDate,userid,min(transactiontime) InTime,Max(transactiontime) OutTime From #tbl
Group by convert(date,transactiontime),userid

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

haneefa avatar image haneefa commented ·

Thank you for your reply, Its working fine. but the result should be based on the Function key column.Value in In time column should come only if the Function key column value F1.

Value in Out time column should come only if the Function key column value F2.

Thanks in advance.

0 Likes 0 ·
Mukesh_Kumar avatar image Mukesh_Kumar haneefa commented ·
@haneefa

Thanks for sharing your comments, as per image attached in query FunctionKey column has same value. You can check it with add this column in groupby clouse.

Regards

Mukesh

0 Likes 0 ·
haneefa avatar image
haneefa answered

Thank you for your reply, Its working fine. but the result should be based on the Function key column.Value in In time column should come only if the Function key column value F1.

Value in Out time column should come only if the Function key column value F2.

Thanks in advance.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.