kanchan 58240 (7/29/2013)
Hi Thanks for the reply.Yes. from the query we need to remove 'and'.
The column datetime1 in both the table is of type datetime.
The values which are present are like '2013-02-04 10:45:00.000', '2013-02-10 18:45:00.000', '2013-04-01 09:20:00.000', etc. From this value, I want to retrieve in the form of hours and minute.
The end result values will be
10:45, 18:45, 09:20, etc.
Could you please help me out to get the result in the above form?
Thanks in advance.
I formatted this a bit so it is more legible. I also removed all of the date formatting. Honestly, your date formatting should be left to the front end instead of doing it in sql.
select t.cardno ,dateadd(dd, -datediff(dd, t.datetime1, 1), 1) AS date,
(
select min(st.datetime1)
from view1 st
where t.cardno = st.cardno
and dateadd(dd, -datediff(dd, t.datetime1, 1), 1) = dateadd(dd, -datediff(dd, st.datetime1, 1), 1)
and st.action = '1'
)as InTime,
(
select max(st.datetime1)
from view1 st
where t.cardno = st.cardno
and dateadd(dd, -datediff(dd, t.datetime1, 1), 1) = dateadd(dd, -datediff(dd, st.datetime1, 1), 1)
and st.action = '2'
)as OutTime
from view1 t
--where t.cardno in ('111111','222222')
group by t.cardno, dateadd(dd, -datediff(dd, t.datetime1, 1), 1)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/