kanchan 58240 (7/29/2013)
Hi,Thanks for all your replies. Here is the query worked as required.
select t.cardno ,dateadd(dd, -datediff(dd, t.datetime1, 1), 1) AS date,
(select SUBSTRING(CONVERT(varchar, min(st.datetime1), 100), 13, 2) + ':' + SUBSTRING(CONVERT(varchar,min(st.datetime1), 100), 16, 2) + ' ' + SUBSTRING(CONVERT(varchar, min(st.datetime1), 100), 18, 2) 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 SUBSTRING(CONVERT(varchar, max(st.datetime1), 100), 13, 2) + ':' + SUBSTRING(CONVERT(varchar,max(st.datetime1), 100), 16, 2) + ' ' + SUBSTRING(CONVERT(varchar, max(st.datetime1), 100), 18, 2) 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') and group by t.cardno, dateadd(dd, -datediff(dd, t.datetime1, 1), 1)
Thanks for posting your solution. However this is not correct. It can't possibly work because you have
AND GROUP BY
Perhaps if you post your actual code for your solution we can offer a better alternative than multiple subselects each with lots of string manipulation.
_______________________________________________________________
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/