SQL Puzzle | No of User Active at each time in the System
Write a T-SQL that to get count number of User Active at each time in the System.
For more please check out the sample input and the expected output.
Sample Inputs
LoginID |
LoginTime |
LogoutTime |
10410199 |
2016-11-28 11:00:11.000 |
2016-11-28 11:00:13.000 |
2699724 |
2016-11-28 11:00:00.000 |
2016-11-28 11:00:13.000 |
2699725 |
2016-11-28 11:00:04.000 |
2016-11-28 11:00:20.000 |
2699723 |
2016-11-28 11:00:00.000 |
2016-11-28 11:00:25.000 |
2699733 |
2016-11-28 11:00:25.000 |
2016-11-28 11:00:25.000 |
2699736 |
2016-11-28 11:00:27.000 |
2016-11-28 11:00:27.000 |
2699729 |
2016-11-28 11:00:19.000 |
2016-11-28 11:00:31.000 |
2699730 |
2016-11-28 11:00:20.000 |
2016-11-28 11:00:34.000 |
2699726 |
2016-11-28 11:00:11.000 |
2016-11-28 11:00:36.000 |
2699731 |
2016-11-28 11:00:22.000 |
2016-11-28 11:00:38.000 |
2699739 |
2016-11-28 11:00:34.000 |
2016-11-28 11:00:48.000 |
2699740 |
2016-11-28 11:00:40.000 |
2016-11-28 11:00:53.000 |
2699735 |
2016-11-28 11:00:27.000 |
2016-11-28 11:00:58.000 |
2699732 |
2016-11-28 11:00:23.000 |
2016-11-28 11:01:01.000 |
2699747 |
2016-11-28 11:01:03.000 |
2016-11-28 11:01:03.000 |
2699743 |
2016-11-28 11:00:51.000 |
2016-11-28 11:01:04.000 |
2699745 |
2016-11-28 11:00:53.000 |
2016-11-28 11:01:06.000 |
2699737 |
2016-11-28 11:00:30.000 |
2016-11-28 11:01:08.000 |
2699734 |
2016-11-28 11:00:25.000 |
2016-11-28 11:01:11.000 |
10410197 |
2016-11-28 11:00:05.000 |
2016-11-28 11:00:19.000 |
10410204 |
2016-11-28 11:00:25.000 |
2016-11-28 11:00:25.000 |
10410198 |
2016-11-28 11:00:10.000 |
2016-11-28 11:00:25.000 |
10410200 |
2016-11-28 11:00:12.000 |
2016-11-28 11:00:26.000 |
10410205 |
2016-11-28 11:00:27.000 |
2016-11-28 11:00:27.000 |
10410202 |
2016-11-28 11:00:20.000 |
2016-11-28 11:00:34.000 |
10410207 |
2016-11-28 11:00:38.000 |
2016-11-28 11:00:52.000 |
10410210 |
2016-11-28 11:01:03.000 |
2016-11-28 11:01:03.000 |
10410211 |
2016-11-28 11:01:13.000 |
2016-11-28 11:01:13.000 |
2699748 |
2016-11-28 11:01:03.000 |
2016-11-28 11:01:13.000 |
2699749 |
2016-11-28 11:01:08.000 |
2016-11-28 11:01:21.000 |
2699742 |
2016-11-28 11:00:45.000 |
2016-11-28 11:01:21.000 |
2699751 |
2016-11-28 11:01:14.000 |
2016-11-28 11:01:27.000 |
2699756 |
2016-11-28 11:01:28.000 |
2016-11-28 11:01:29.000 |
2699757 |
2016-11-28 11:01:28.000 |
2016-11-28 11:01:29.000 |
2699744 |
2016-11-28 11:00:52.000 |
2016-11-28 11:01:37.000 |
2699754 |
2016-11-28 11:01:27.000 |
2016-11-28 11:01:40.000 |
2699755 |
2016-11-28 11:01:28.000 |
2016-11-28 11:01:41.000 |
2699746 |
2016-11-28 11:00:53.000 |
2016-11-28 11:01:42.000 |
2699764 |
2016-11-28 11:01:52.000 |
2016-11-28 11:01:52.000 |
2699760 |
2016-11-28 11:01:41.000 |
2016-11-28 11:01:54.000 |
2699761 |
2016-11-28 11:01:43.000 |
2016-11-28 11:01:58.000 |
2699750 |
2016-11-28 11:01:09.000 |
2016-11-28 11:01:59.000 |
2699753 |
2016-11-28 11:01:22.000 |
2016-11-28 11:02:01.000 |
2699763 |
2016-11-28 11:01:52.000 |
2016-11-28 11:02:06.000 |
2699765 |
2016-11-28 11:01:55.000 |
2016-11-28 11:02:11.000 |
10410201 |
2016-11-28 11:00:20.000 |
2016-11-28 11:01:18.000 |
10410209 |
2016-11-28 11:00:58.000 |
2016-11-28 11:01:27.000 |
10410214 |
2016-11-28 11:01:28.000 |
2016-11-28 11:01:29.000 |
10410217 |
2016-11-28 11:01:50.000 |
2016-11-28 11:01:50.000 |
10410218 |
2016-11-28 11:01:52.000 |
2016-11-28 11:01:52.000 |
2699766 |
2016-11-28 11:01:58.000 |
2016-11-28 11:02:15.000 |
2699768 |
2016-11-28 11:02:05.000 |
2016-11-28 11:02:20.000 |
2699769 |
2016-11-28 11:02:07.000 |
2016-11-28 11:02:23.000 |
2699775 |
2016-11-28 11:02:26.000 |
2016-11-28 11:02:26.000 |
2699771 |
2016-11-28 11:02:20.000 |
2016-11-28 11:02:33.000 |
2699772 |
2016-11-28 11:02:22.000 |
2016-11-28 11:02:36.000 |
2699777 |
2016-11-28 11:02:29.000 |
2016-11-28 11:02:44.000 |
2699779 |
2016-11-28 11:02:35.000 |
2016-11-28 11:02:48.000 |
2699728 |
2016-11-28 11:00:18.000 |
2016-11-28 11:02:50.000 |
2699784 |
2016-11-28 11:02:42.000 |
2016-11-28 11:02:51.000 |
2699787 |
2016-11-28 11:02:48.000 |
2016-11-28 11:02:58.000 |
2699776 |
2016-11-28 11:02:28.000 |
2016-11-28 11:02:59.000 |
2699785 |
2016-11-28 11:02:44.000 |
2016-11-28 11:02:59.000 |
2699786 |
2016-11-28 11:02:48.000 |
2016-11-28 11:03:01.000 |
2699794 |
2016-11-28 11:03:05.000 |
2016-11-28 11:03:05.000 |
2699774 |
2016-11-28 11:02:26.000 |
2016-11-28 11:03:06.000 |
2699795 |
2016-11-28 11:03:06.000 |
2016-11-28 11:03:06.000 |
2699796 |
2016-11-28 11:03:09.000 |
2016-11-28 11:03:09.000 |
2699789 |
2016-11-28 11:02:54.000 |
2016-11-28 11:03:09.000 |
2699782 |
2016-11-28 11:02:40.000 |
2016-11-28 11:03:11.000 |
10410224 |
2016-11-28 11:02:23.000 |
2016-11-28 11:02:23.000 |
10410222 |
2016-11-28 11:02:11.000 |
2016-11-28 11:02:25.000 |
10410225 |
2016-11-28 11:02:26.000 |
2016-11-28 11:02:26.000 |
10410215 |
2016-11-28 11:01:46.000 |
2016-11-28 11:02:30.000 |
10410208 |
2016-11-28 11:00:48.000 |
2016-11-28 11:02:32.000 |
10410212 |
2016-11-28 11:01:23.000 |
2016-11-28 11:02:48.000 |
10410230 |
2016-11-28 11:03:05.000 |
2016-11-28 11:03:05.000 |
10410231 |
2016-11-28 11:03:06.000 |
2016-11-28 11:03:06.000 |
10410223 |
2016-11-28 11:02:12.000 |
2016-11-28 11:03:07.000 |
10410228 |
2016-11-28 11:02:58.000 |
2016-11-28 11:03:08.000 |
10410232 |
2016-11-28 11:03:09.000 |
2016-11-28 11:03:09.000 |
1232441 |
2016-11-28 11:03:09.000 |
2016-11-28 11:03:09.000 |
1232442 |
2016-11-28 11:03:15.000 |
2016-11-28 11:03:24.000 |
1232444 |
2016-11-28 11:03:35.000 |
2016-11-28 11:03:35.000 |
1232445 |
2016-11-28 11:03:42.000 |
2016-11-28 11:03:42.000 |
1232446 |
2016-11-28 11:03:42.000 |
2016-11-28 11:03:42.000 |
2699780 |
2016-11-28 11:02:35.000 |
2016-11-28 11:03:14.000 |
2699790 |
2016-11-28 11:03:02.000 |
2016-11-28 11:03:15.000 |
2699793 |
2016-11-28 11:03:04.000 |
2016-11-28 11:03:15.000 |
2699781 |
2016-11-28 11:02:37.000 |
2016-11-28 11:03:18.000 |
2699778 |
2016-11-28 11:02:34.000 |
2016-11-28 11:03:20.000 |
2699791 |
2016-11-28 11:03:03.000 |
2016-11-28 11:03:22.000 |
2699800 |
2016-11-28 11:03:22.000 |
2016-11-28 11:03:27.000 |
2699797 |
2016-11-28 11:03:16.000 |
2016-11-28 11:03:33.000 |
2699805 |
2016-11-28 11:03:35.000 |
2016-11-28 11:03:35.000 |
2699792 |
2016-11-28 11:03:04.000 |
2016-11-28 11:03:40.000 |
2699810 |
2016-11-28 11:03:42.000 |
2016-11-28 11:03:42.000 |
2699811 |
2016-11-28 11:03:42.000 |
2016-11-28 11:03:42.000 |
2699801 |
2016-11-28 11:03:30.000 |
2016-11-28 11:03:45.000 |
2699727 |
2016-11-28 11:00:11.000 |
2016-11-28 11:03:47.000 |
2699738 |
2016-11-28 11:00:33.000 |
2016-11-28 11:03:47.000 |
2699804 |
2016-11-28 11:03:35.000 |
2016-11-28 11:03:49.000 |
2699814 |
2016-11-28 11:03:50.000 |
2016-11-28 11:03:50.000 |
2699812 |
2016-11-28 11:03:44.000 |
2016-11-28 11:03:53.000 |
2699809 |
2016-11-28 11:03:41.000 |
2016-11-28 11:03:54.000 |
2699803 |
2016-11-28 11:03:32.000 |
2016-11-28 11:03:55.000 |
2699802 |
2016-11-28 11:03:31.000 |
2016-11-28 11:03:55.000 |
2699813 |
2016-11-28 11:03:48.000 |
2016-11-28 11:04:01.000 |
2699818 |
2016-11-28 11:04:03.000 |
2016-11-28 11:04:03.000 |
2699819 |
2016-11-28 11:04:05.000 |
2016-11-28 11:04:05.000 |
2699808 |
2016-11-28 11:03:40.000 |
2016-11-28 11:04:11.000 |
2699820 |
2016-11-28 11:04:11.000 |
2016-11-28 11:04:11.000 |
10410229 |
2016-11-28 11:02:59.000 |
2016-11-28 11:03:28.000 |
10410235 |
2016-11-28 11:03:35.000 |
2016-11-28 11:03:35.000 |
10410234 |
2016-11-28 11:03:11.000 |
2016-11-28 11:03:40.000 |
10410236 |
2016-11-28 11:03:35.000 |
2016-11-28 11:03:40.000 |
10410238 |
2016-11-28 11:03:42.000 |
2016-11-28 11:03:42.000 |
10410239 |
2016-11-28 11:03:42.000 |
2016-11-28 11:03:42.000 |
10410233 |
2016-11-28 11:03:09.000 |
2016-11-28 11:03:43.000 |
10410240 |
2016-11-28 11:03:50.000 |
2016-11-28 11:03:51.000 |
10410241 |
2016-11-28 11:04:03.000 |
2016-11-28 11:04:03.000 |
10410242 |
2016-11-28 11:04:05.000 |
2016-11-28 11:04:05.000 |
10410219 |
2016-11-28 11:01:58.000 |
2016-11-28 11:04:09.000 |
10410245 |
2016-11-28 11:04:11.000 |
2016-11-28 11:04:11.000 |
2699815 |
2016-11-28 11:03:57.000 |
2016-11-28 11:04:12.000 |
2699817 |
2016-11-28 11:04:03.000 |
2016-11-28 11:04:19.000 |
2699821 |
2016-11-28 11:04:12.000 |
2016-11-28 11:04:27.000 |
2699788 |
2016-11-28 11:02:52.000 |
2016-11-28 11:04:33.000 |
2699828 |
2016-11-28 11:04:35.000 |
2016-11-28 11:04:35.000 |
2699823 |
2016-11-28 11:04:28.000 |
2016-11-28 11:04:38.000 |
2699834 |
2016-11-28 11:04:36.000 |
2016-11-28 11:04:46.000 |
2699831 |
2016-11-28 11:04:36.000 |
2016-11-28 11:04:46.000 |
2699827 |
2016-11-28 11:04:34.000 |
2016-11-28 11:04:53.000 |
2699836 |
2016-11-28 11:04:38.000 |
2016-11-28 11:04:54.000 |
2699829 |
2016-11-28 11:04:36.000 |
2016-11-28 11:05:01.000 |
2699838 |
2016-11-28 11:04:50.000 |
2016-11-28 11:05:03.000 |
2699822 |
2016-11-28 11:04:24.000 |
2016-11-28 11:05:04.000 |
2699837 |
2016-11-28 11:04:47.000 |
2016-11-28 11:05:06.000 |
2699835 |
2016-11-28 11:04:37.000 |
2016-11-28 11:05:07.000 |
2699825 |
2016-11-28 11:04:30.000 |
2016-11-28 11:05:08.000 |
2699843 |
2016-11-28 11:05:00.000 |
2016-11-28 11:05:08.000 |
2699842 |
2016-11-28 11:04:56.000 |
2016-11-28 11:05:11.000 |
1232448 |
2016-11-28 11:04:35.000 |
2016-11-28 11:04:35.000 |
1232450 |
2016-11-28 11:04:42.000 |
2016-11-28 11:05:14.000 |
1232453 |
2016-11-28 11:05:16.000 |
2016-11-28 11:05:16.000 |
10410246 |
2016-11-28 11:04:19.000 |
2016-11-28 11:04:33.000 |
10410248 |
2016-11-28 11:04:35.000 |
2016-11-28 11:04:35.000 |
10410244 |
2016-11-28 11:04:11.000 |
2016-11-28 11:04:40.000 |
10410249 |
2016-11-28 11:04:38.000 |
2016-11-28 11:04:48.000 |
10410252 |
2016-11-28 11:04:54.000 |
2016-11-28 11:05:10.000 |
10410255 |
2016-11-28 11:05:16.000 |
2016-11-28 11:05:16.000 |
2699852 |
2016-11-28 11:05:15.000 |
2016-11-28 11:05:16.000 |
2699846 |
2016-11-28 11:05:03.000 |
2016-11-28 11:05:16.000 |
2699844 |
2016-11-28 11:05:01.000 |
2016-11-28 11:05:17.000 |
2699830 |
2016-11-28 11:04:36.000 |
2016-11-28 11:05:20.000 |
2699832 |
2016-11-28 11:04:36.000 |
2016-11-28 11:05:20.000 |
2699847 |
2016-11-28 11:05:05.000 |
2016-11-28 11:05:20.000 |
2699807 |
2016-11-28 11:03:39.000 |
2016-11-28 11:05:21.000 |
2699848 |
2016-11-28 11:05:07.000 |
2016-11-28 11:05:23.000 |
2699833 |
2016-11-28 11:04:36.000 |
2016-11-28 11:05:23.000 |
2699841 |
2016-11-28 11:04:54.000 |
2016-11-28 11:05:26.000 |
2699826 |
2016-11-28 11:04:34.000 |
2016-11-28 11:05:27.000 |
2699845 |
2016-11-28 11:05:01.000 |
2016-11-28 11:05:32.000 |
2699853 |
2016-11-28 11:05:20.000 |
2016-11-28 11:05:35.000 |
2699850 |
2016-11-28 11:05:10.000 |
2016-11-28 11:05:41.000 |
2699849 |
2016-11-28 11:05:09.000 |
2016-11-28 11:05:58.000 |
2699857 |
2016-11-28 11:05:44.000 |
2016-11-28 11:06:00.000 |
2699859 |
2016-11-28 11:06:01.000 |
2016-11-28 11:06:02.000 |
2699862 |
2016-11-28 11:06:06.000 |
2016-11-28 11:06:06.000 |
1232454 |
2016-11-28 11:05:26.000 |
2016-11-28 11:05:35.000 |
1232452 |
2016-11-28 11:05:15.000 |
2016-11-28 11:05:35.000 |
1232451 |
2016-11-28 11:05:14.000 |
2016-11-28 11:06:05.000 |
1232449 |
2016-11-28 11:04:38.000 |
2016-11-28 11:06:14.000 |
10410253 |
2016-11-28 11:05:01.000 |
2016-11-28 11:05:20.000 |
10410256 |
2016-11-28 11:05:17.000 |
2016-11-28 11:05:31.000 |
10410243 |
2016-11-28 11:04:07.000 |
2016-11-28 11:05:35.000 |
10410257 |
2016-11-28 11:05:23.000 |
2016-11-28 11:05:37.000 |
10410254 |
2016-11-28 11:05:08.000 |
2016-11-28 11:05:37.000 |
10410258 |
2016-11-28 11:05:29.000 |
2016-11-28 11:05:50.000 |
10410250 |
2016-11-28 11:04:38.000 |
2016-11-28 11:06:00.000 |
10410259 |
2016-11-28 11:05:32.000 |
2016-11-28 11:06:01.000 |
10410264 |
2016-11-28 11:06:02.000 |
2016-11-28 11:06:02.000 |
10410227 |
2016-11-28 11:02:56.000 |
2016-11-28 11:06:05.000 |
10410265 |
2016-11-28 11:06:06.000 |
2016-11-28 11:06:06.000 |
10410237 |
2016-11-28 11:03:41.000 |
2016-11-28 11:06:06.000 |
10410263 |
2016-11-28 11:06:00.000 |
2016-11-28 11:06:14.000 |
2699855 |
2016-11-28 11:05:31.000 |
2016-11-28 11:06:15.000 |
2699860 |
2016-11-28 11:06:03.000 |
2016-11-28 11:06:16.000 |
2699856 |
2016-11-28 11:05:37.000 |
2016-11-28 11:06:22.000 |
2699863 |
2016-11-28 11:06:08.000 |
2016-11-28 11:06:24.000 |
2699875 |
2016-11-28 11:06:27.000 |
2016-11-28 11:06:27.000 |
2699865 |
2016-11-28 11:06:12.000 |
2016-11-28 11:06:28.000 |
2699876 |
2016-11-28 11:06:28.000 |
2016-11-28 11:06:28.000 |
2699877 |
2016-11-28 11:06:28.000 |
2016-11-28 11:06:29.000 |
2699866 |
2016-11-28 11:06:14.000 |
2016-11-28 11:06:30.000 |
2699868 |
2016-11-28 11:06:17.000 |
2016-11-28 11:06:30.000 |
2699881 |
2016-11-28 11:06:31.000 |
2016-11-28 11:06:32.000 |
2699882 |
2016-11-28 11:06:32.000 |
2016-11-28 11:06:32.000 |
2699869 |
2016-11-28 11:06:17.000 |
2016-11-28 11:06:32.000 |
2699871 |
2016-11-28 11:06:23.000 |
2016-11-28 11:06:36.000 |
2699864 |
2016-11-28 11:06:10.000 |
2016-11-28 11:06:40.000 |
2699874 |
2016-11-28 11:06:26.000 |
2016-11-28 11:06:42.000 |
2699880 |
2016-11-28 11:06:31.000 |
2016-11-28 11:06:42.000 |
2699887 |
2016-11-28 11:06:42.000 |
2016-11-28 11:06:42.000 |
2699824 |
2016-11-28 11:04:30.000 |
2016-11-28 11:06:43.000 |
2699879 |
2016-11-28 11:06:31.000 |
2016-11-28 11:06:47.000 |
2699883 |
2016-11-28 11:06:32.000 |
2016-11-28 11:06:47.000 |
2699858 |
2016-11-28 11:05:58.000 |
2016-11-28 11:06:49.000 |
2699870 |
2016-11-28 11:06:21.000 |
2016-11-28 11:06:51.000 |
2699889 |
2016-11-28 11:06:46.000 |
2016-11-28 11:06:53.000 |
2699867 |
2016-11-28 11:06:15.000 |
2016-11-28 11:06:54.000 |
2699872 |
2016-11-28 11:06:25.000 |
2016-11-28 11:07:03.000 |
2699854 |
2016-11-28 11:05:21.000 |
2016-11-28 11:07:04.000 |
2699884 |
2016-11-28 11:06:34.000 |
2016-11-28 11:07:05.000 |
2699895 |
2016-11-28 11:07:05.000 |
2016-11-28 11:07:05.000 |
2699885 |
2016-11-28 11:06:36.000 |
2016-11-28 11:07:07.000 |
2699878 |
2016-11-28 11:06:29.000 |
2016-11-28 11:07:08.000 |
2699897 |
2016-11-28 11:07:11.000 |
2016-11-28 11:07:11.000 |
1232443 |
2016-11-28 11:03:24.000 |
2016-11-28 11:06:44.000 |
1232456 |
2016-11-28 11:07:11.000 |
2016-11-28 11:07:11.000 |
10410261 |
2016-11-28 11:05:48.000 |
2016-11-28 11:06:24.000 |
10410268 |
2016-11-28 11:06:27.000 |
2016-11-28 11:06:27.000 |
10410270 |
2016-11-28 11:06:28.000 |
2016-11-28 11:06:28.000 |
10410267 |
2016-11-28 11:06:24.000 |
2016-11-28 11:06:38.000 |
10410260 |
2016-11-28 11:05:41.000 |
2016-11-28 11:06:41.000 |
10410269 |
2016-11-28 11:06:28.000 |
2016-11-28 11:06:42.000 |
10410275 |
2016-11-28 11:06:42.000 |
2016-11-28 11:06:42.000 |
10410266 |
2016-11-28 11:06:21.000 |
2016-11-28 11:06:42.000 |
10410271 |
2016-11-28 11:06:30.000 |
2016-11-28 11:06:44.000 |
10410279 |
2016-11-28 11:06:51.000 |
2016-11-28 11:06:52.000 |
10410274 |
2016-11-28 11:06:42.000 |
2016-11-28 11:06:56.000 |
10410276 |
2016-11-28 11:06:47.000 |
2016-11-28 11:07:01.000 |
10410277 |
2016-11-28 11:06:48.000 |
2016-11-28 11:07:02.000 |
10410283 |
2016-11-28 11:07:05.000 |
2016-11-28 11:07:05.000 |
10410272 |
2016-11-28 11:06:40.000 |
2016-11-28 11:07:10.000 |
10410286 |
2016-11-28 11:07:11.000 |
2016-11-28 11:07:11.000 |
2699892 |
2016-11-28 11:06:57.000 |
2016-11-28 11:07:13.000 |
2699899 |
2016-11-28 11:07:26.000 |
2016-11-28 11:07:26.000 |
2699886 |
2016-11-28 11:06:38.000 |
2016-11-28 11:07:27.000 |
2699888 |
2016-11-28 11:06:44.000 |
2016-11-28 11:07:29.000 |
2699898 |
2016-11-28 11:07:17.000 |
2016-11-28 11:07:32.000 |
2699861 |
2016-11-28 11:06:03.000 |
2016-11-28 11:07:33.000 |
2699894 |
2016-11-28 11:07:02.000 |
2016-11-28 11:07:33.000 |
2699891 |
2016-11-28 11:06:57.000 |
2016-11-28 11:07:34.000 |
2699900 |
2016-11-28 11:07:28.000 |
2016-11-28 11:07:42.000 |
2699901 |
2016-11-28 11:07:31.000 |
2016-11-28 11:07:44.000 |
2699902 |
2016-11-28 11:07:33.000 |
2016-11-28 11:07:48.000 |
2699905 |
2016-11-28 11:07:37.000 |
2016-11-28 11:07:50.000 |
2699907 |
2016-11-28 11:07:38.000 |
2016-11-28 11:07:54.000 |
2699903 |
2016-11-28 11:07:33.000 |
2016-11-28 11:08:02.000 |
2699909 |
2016-11-28 11:07:48.000 |
2016-11-28 11:08:03.000 |
2699904 |
2016-11-28 11:07:35.000 |
2016-11-28 11:08:06.000 |
2699908 |
2016-11-28 11:07:44.000 |
2016-11-28 11:08:08.000 |
10410285 |
2016-11-28 11:07:08.000 |
2016-11-28 11:07:23.000 |
10410290 |
2016-11-28 11:07:26.000 |
2016-11-28 11:07:26.000 |
10410288 |
2016-11-28 11:07:13.000 |
2016-11-28 11:07:27.000 |
10410282 |
2016-11-28 11:07:05.000 |
2016-11-28 11:07:34.000 |
10410262 |
2016-11-28 11:05:56.000 |
2016-11-28 11:07:36.000 |
10410284 |
2016-11-28 11:07:07.000 |
2016-11-28 11:07:36.000 |
10410281 |
2016-11-28 11:06:57.000 |
2016-11-28 11:08:02.000 |
10410278 |
2016-11-28 11:06:50.000 |
2016-11-28 11:08:06.000 |
10410292 |
2016-11-28 11:07:54.000 |
2016-11-28 11:08:08.000 |
10410296 |
2016-11-28 11:08:17.000 |
2016-11-28 11:08:17.000 |
2699916 |
2016-11-28 11:08:17.000 |
2016-11-28 11:08:17.000 |
2699910 |
2016-11-28 11:07:56.000 |
2016-11-28 11:08:27.000 |
2699918 |
2016-11-28 11:08:20.000 |
2016-11-28 11:08:36.000 |
2699920 |
2016-11-28 11:08:34.000 |
2016-11-28 11:08:48.000 |
2699914 |
2016-11-28 11:08:08.000 |
2016-11-28 11:08:50.000 |
2699921 |
2016-11-28 11:08:35.000 |
2016-11-28 11:08:50.000 |
2699922 |
2016-11-28 11:08:37.000 |
2016-11-28 11:08:52.000 |
2699919 |
2016-11-28 11:08:22.000 |
2016-11-28 11:08:53.000 |
2699915 |
2016-11-28 11:08:16.000 |
2016-11-28 11:08:54.000 |
2699929 |
2016-11-28 11:08:55.000 |
2016-11-28 11:08:55.000 |
2699917 |
2016-11-28 11:08:19.000 |
2016-11-28 11:08:56.000 |
2699925 |
2016-11-28 11:08:45.000 |
2016-11-28 11:08:58.000 |
2699851 |
2016-11-28 11:05:15.000 |
2016-11-28 11:09:00.000 |
2699931 |
2016-11-28 11:09:03.000 |
2016-11-28 11:09:03.000 |
2699927 |
2016-11-28 11:08:52.000 |
2016-11-28 11:09:05.000 |
2699926 |
2016-11-28 11:08:50.000 |
2016-11-28 11:09:05.000 |
2699928 |
2016-11-28 11:08:52.000 |
2016-11-28 11:09:07.000 |
10410293 |
2016-11-28 11:07:56.000 |
2016-11-28 11:08:18.000 |
10410295 |
2016-11-28 11:08:06.000 |
2016-11-28 11:08:35.000 |
10410294 |
2016-11-28 11:08:01.000 |
2016-11-28 11:08:50.000 |
10410299 |
2016-11-28 11:08:36.000 |
2016-11-28 11:08:50.000 |
10410300 |
2016-11-28 11:08:50.000 |
2016-11-28 11:08:52.000 |
10410302 |
2016-11-28 11:08:55.000 |
2016-11-28 11:08:55.000 |
10410298 |
2016-11-28 11:08:27.000 |
2016-11-28 11:08:56.000 |
10410304 |
2016-11-28 11:09:03.000 |
2016-11-28 11:09:03.000 |
10410291 |
2016-11-28 11:07:48.000 |
2016-11-28 11:09:12.000 |
1232459 |
2016-11-28 11:09:03.000 |
2016-11-28 11:09:03.000 |
2699930 |
2016-11-28 11:08:57.000 |
2016-11-28 11:09:28.000 |
2699840 |
2016-11-28 11:04:53.000 |
2016-11-28 11:09:31.000 |
2699933 |
2016-11-28 11:09:31.000 |
2016-11-28 11:09:37.000 |
2699932 |
2016-11-28 11:09:23.000 |
2016-11-28 11:09:39.000 |
2699935 |
2016-11-28 11:09:37.000 |
2016-11-28 11:09:43.000 |
2699937 |
2016-11-28 11:09:44.000 |
2016-11-28 11:09:44.000 |
2699940 |
2016-11-28 11:09:50.000 |
2016-11-28 11:09:50.000 |
2699938 |
2016-11-28 11:09:46.000 |
2016-11-28 11:09:52.000 |
2699934 |
2016-11-28 11:09:35.000 |
2016-11-28 11:09:52.000 |
2699936 |
2016-11-28 11:09:40.000 |
2016-11-28 11:09:55.000 |
2699941 |
2016-11-28 11:09:52.000 |
2016-11-28 11:09:58.000 |
1232447 |
2016-11-28 11:04:06.000 |
2016-11-28 11:09:32.000 |
1232461 |
2016-11-28 11:09:44.000 |
2016-11-28 11:09:44.000 |
10410301 |
2016-11-28 11:08:53.000 |
2016-11-28 11:09:22.000 |
10410311 |
2016-11-28 11:09:44.000 |
2016-11-28 11:09:44.000 |
10410213 |
2016-11-28 11:01:24.000 |
2016-11-28 11:09:45.000 |
10410220 |
2016-11-28 11:01:58.000 |
2016-11-28 11:09:46.000 |
10410313 |
2016-11-28 11:09:51.000 |
2016-11-28 11:09:51.000 |
10410309 |
2016-11-28 11:09:39.000 |
2016-11-28 11:09:53.000 |
10410308 |
2016-11-28 11:09:28.000 |
2016-11-28 11:09:57.000 |
Expected Output
AtThisTime |
LoggedInUsersCount |
2016-11-28 11:01:00.000 |
14 |
2016-11-28 11:02:00.000 |
13 |
2016-11-28 11:03:00.000 |
17 |
2016-11-28 11:04:00.000 |
11 |
2016-11-28 11:05:00.000 |
27 |
2016-11-28 11:06:00.000 |
22 |
2016-11-28 11:07:00.000 |
21 |
2016-11-28 11:08:00.000 |
15 |
2016-11-28 11:09:00.000 |
11 |
Rules/Restrictions
- The challenge is to do it with T-SQL statements only.
- Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com
Script | use below script to create table and insert some sample data
--
CREATE TABLE Yourtabk
(
LoginID BIGINT ,
LoginTime DATETIME,
LogoutTime DATETIME,
)
GO
INSERT INTO Yourtabk VALUES
( 10410199 ,'2016-11-28 11:00:11','2016-11-28 11:00:13'),
( 2699724 ,'2016-11-28 11:00:00','2016-11-28 11:00:13'),
( 2699725 ,'2016-11-28 11:00:04','2016-11-28 11:00:20'),
( 2699723 ,'2016-11-28 11:00:00','2016-11-28 11:00:25'),
( 2699733 ,'2016-11-28 11:00:25','2016-11-28 11:00:25'),
( 2699736 ,'2016-11-28 11:00:27','2016-11-28 11:00:27'),
( 2699729 ,'2016-11-28 11:00:19','2016-11-28 11:00:31'),
( 2699730 ,'2016-11-28 11:00:20','2016-11-28 11:00:34'),
( 2699726 ,'2016-11-28 11:00:11','2016-11-28 11:00:36'),
( 2699731 ,'2016-11-28 11:00:22','2016-11-28 11:00:38'),
( 2699739 ,'2016-11-28 11:00:34','2016-11-28 11:00:48'),
( 2699740 ,'2016-11-28 11:00:40','2016-11-28 11:00:53'),
( 2699735 ,'2016-11-28 11:00:27','2016-11-28 11:00:58'),
( 2699732 ,'2016-11-28 11:00:23','2016-11-28 11:01:01'),
( 2699747 ,'2016-11-28 11:01:03','2016-11-28 11:01:03'),
( 2699743 ,'2016-11-28 11:00:51','2016-11-28 11:01:04'),
( 2699745 ,'2016-11-28 11:00:53','2016-11-28 11:01:06'),
( 2699737 ,'2016-11-28 11:00:30','2016-11-28 11:01:08'),
( 2699734 ,'2016-11-28 11:00:25','2016-11-28 11:01:11'),
( 10410197 ,'2016-11-28 11:00:05','2016-11-28 11:00:19'),
( 10410204 ,'2016-11-28 11:00:25','2016-11-28 11:00:25'),
( 10410198 ,'2016-11-28 11:00:10','2016-11-28 11:00:25'),
( 10410200 ,'2016-11-28 11:00:12','2016-11-28 11:00:26'),
( 10410205 ,'2016-11-28 11:00:27','2016-11-28 11:00:27'),
( 10410202 ,'2016-11-28 11:00:20','2016-11-28 11:00:34'),
( 10410207 ,'2016-11-28 11:00:38','2016-11-28 11:00:52'),
( 10410210 ,'2016-11-28 11:01:03','2016-11-28 11:01:03'),
( 10410211 ,'2016-11-28 11:01:13','2016-11-28 11:01:13'),
( 2699748 ,'2016-11-28 11:01:03','2016-11-28 11:01:13'),
( 2699749 ,'2016-11-28 11:01:08','2016-11-28 11:01:21'),
( 2699742 ,'2016-11-28 11:00:45','2016-11-28 11:01:21'),
( 2699751 ,'2016-11-28 11:01:14','2016-11-28 11:01:27'),
( 2699756 ,'2016-11-28 11:01:28','2016-11-28 11:01:29'),
( 2699757 ,'2016-11-28 11:01:28','2016-11-28 11:01:29'),
( 2699744 ,'2016-11-28 11:00:52','2016-11-28 11:01:37'),
( 2699754 ,'2016-11-28 11:01:27','2016-11-28 11:01:40'),
( 2699755 ,'2016-11-28 11:01:28','2016-11-28 11:01:41'),
( 2699746 ,'2016-11-28 11:00:53','2016-11-28 11:01:42'),
( 2699764 ,'2016-11-28 11:01:52','2016-11-28 11:01:52'),
( 2699760 ,'2016-11-28 11:01:41','2016-11-28 11:01:54'),
( 2699761 ,'2016-11-28 11:01:43','2016-11-28 11:01:58'),
( 2699750 ,'2016-11-28 11:01:09','2016-11-28 11:01:59'),
( 2699753 ,'2016-11-28 11:01:22','2016-11-28 11:02:01'),
( 2699763 ,'2016-11-28 11:01:52','2016-11-28 11:02:06'),
( 2699765 ,'2016-11-28 11:01:55','2016-11-28 11:02:11'),
( 10410201 ,'2016-11-28 11:00:20','2016-11-28 11:01:18'),
( 10410209 ,'2016-11-28 11:00:58','2016-11-28 11:01:27'),
( 10410214 ,'2016-11-28 11:01:28','2016-11-28 11:01:29'),
( 10410217 ,'2016-11-28 11:01:50','2016-11-28 11:01:50'),
( 10410218 ,'2016-11-28 11:01:52','2016-11-28 11:01:52'),
( 2699766 ,'2016-11-28 11:01:58','2016-11-28 11:02:15'),
( 2699768 ,'2016-11-28 11:02:05','2016-11-28 11:02:20'),
( 2699769 ,'2016-11-28 11:02:07','2016-11-28 11:02:23'),
( 2699775 ,'2016-11-28 11:02:26','2016-11-28 11:02:26'),
( 2699771 ,'2016-11-28 11:02:20','2016-11-28 11:02:33'),
( 2699772 ,'2016-11-28 11:02:22','2016-11-28 11:02:36'),
( 2699777 ,'2016-11-28 11:02:29','2016-11-28 11:02:44'),
( 2699779 ,'2016-11-28 11:02:35','2016-11-28 11:02:48'),
( 2699728 ,'2016-11-28 11:00:18','2016-11-28 11:02:50'),
( 2699784 ,'2016-11-28 11:02:42','2016-11-28 11:02:51'),
( 2699787 ,'2016-11-28 11:02:48','2016-11-28 11:02:58'),
( 2699776 ,'2016-11-28 11:02:28','2016-11-28 11:02:59'),
( 2699785 ,'2016-11-28 11:02:44','2016-11-28 11:02:59'),
( 2699786 ,'2016-11-28 11:02:48','2016-11-28 11:03:01'),
( 2699794 ,'2016-11-28 11:03:05','2016-11-28 11:03:05'),
( 2699774 ,'2016-11-28 11:02:26','2016-11-28 11:03:06'),
( 2699795 ,'2016-11-28 11:03:06','2016-11-28 11:03:06'),
( 2699796 ,'2016-11-28 11:03:09','2016-11-28 11:03:09'),
( 2699789 ,'2016-11-28 11:02:54','2016-11-28 11:03:09'),
( 2699782 ,'2016-11-28 11:02:40','2016-11-28 11:03:11'),
( 10410224 ,'2016-11-28 11:02:23','2016-11-28 11:02:23'),
( 10410222 ,'2016-11-28 11:02:11','2016-11-28 11:02:25'),
( 10410225 ,'2016-11-28 11:02:26','2016-11-28 11:02:26'),
( 10410215 ,'2016-11-28 11:01:46','2016-11-28 11:02:30'),
( 10410208 ,'2016-11-28 11:00:48','2016-11-28 11:02:32'),
( 10410212 ,'2016-11-28 11:01:23','2016-11-28 11:02:48'),
( 10410230 ,'2016-11-28 11:03:05','2016-11-28 11:03:05'),
( 10410231 ,'2016-11-28 11:03:06','2016-11-28 11:03:06'),
( 10410223 ,'2016-11-28 11:02:12','2016-11-28 11:03:07'),
( 10410228 ,'2016-11-28 11:02:58','2016-11-28 11:03:08'),
( 10410232 ,'2016-11-28 11:03:09','2016-11-28 11:03:09'),
( 1232441 ,'2016-11-28 11:03:09','2016-11-28 11:03:09'),
( 1232442 ,'2016-11-28 11:03:15','2016-11-28 11:03:24'),
( 1232444 ,'2016-11-28 11:03:35','2016-11-28 11:03:35'),
( 1232445 ,'2016-11-28 11:03:42','2016-11-28 11:03:42'),
( 1232446 ,'2016-11-28 11:03:42','2016-11-28 11:03:42'),
( 2699780 ,'2016-11-28 11:02:35','2016-11-28 11:03:14'),
( 2699790 ,'2016-11-28 11:03:02','2016-11-28 11:03:15'),
( 2699793 ,'2016-11-28 11:03:04','2016-11-28 11:03:15'),
( 2699781 ,'2016-11-28 11:02:37','2016-11-28 11:03:18'),
( 2699778 ,'2016-11-28 11:02:34','2016-11-28 11:03:20'),
( 2699791 ,'2016-11-28 11:03:03','2016-11-28 11:03:22'),
( 2699800 ,'2016-11-28 11:03:22','2016-11-28 11:03:27'),
( 2699797 ,'2016-11-28 11:03:16','2016-11-28 11:03:33'),
( 2699805 ,'2016-11-28 11:03:35','2016-11-28 11:03:35'),
( 2699792 ,'2016-11-28 11:03:04','2016-11-28 11:03:40'),
( 2699810 ,'2016-11-28 11:03:42','2016-11-28 11:03:42'),
( 2699811 ,'2016-11-28 11:03:42','2016-11-28 11:03:42'),
( 2699801 ,'2016-11-28 11:03:30','2016-11-28 11:03:45'),
( 2699727 ,'2016-11-28 11:00:11','2016-11-28 11:03:47'),
( 2699738 ,'2016-11-28 11:00:33','2016-11-28 11:03:47'),
( 2699804 ,'2016-11-28 11:03:35','2016-11-28 11:03:49'),
( 2699814 ,'2016-11-28 11:03:50','2016-11-28 11:03:50'),
( 2699812 ,'2016-11-28 11:03:44','2016-11-28 11:03:53'),
( 2699809 ,'2016-11-28 11:03:41','2016-11-28 11:03:54'),
( 2699803 ,'2016-11-28 11:03:32','2016-11-28 11:03:55'),
( 2699802 ,'2016-11-28 11:03:31','2016-11-28 11:03:55'),
( 2699813 ,'2016-11-28 11:03:48','2016-11-28 11:04:01'),
( 2699818 ,'2016-11-28 11:04:03','2016-11-28 11:04:03'),
( 2699819 ,'2016-11-28 11:04:05','2016-11-28 11:04:05'),
( 2699808 ,'2016-11-28 11:03:40','2016-11-28 11:04:11'),
( 2699820 ,'2016-11-28 11:04:11','2016-11-28 11:04:11'),
( 10410229 ,'2016-11-28 11:02:59','2016-11-28 11:03:28'),
( 10410235 ,'2016-11-28 11:03:35','2016-11-28 11:03:35'),
( 10410234 ,'2016-11-28 11:03:11','2016-11-28 11:03:40'),
( 10410236 ,'2016-11-28 11:03:35','2016-11-28 11:03:40'),
( 10410238 ,'2016-11-28 11:03:42','2016-11-28 11:03:42'),
( 10410239 ,'2016-11-28 11:03:42','2016-11-28 11:03:42'),
( 10410233 ,'2016-11-28 11:03:09','2016-11-28 11:03:43'),
( 10410240 ,'2016-11-28 11:03:50','2016-11-28 11:03:51'),
( 10410241 ,'2016-11-28 11:04:03','2016-11-28 11:04:03'),
( 10410242 ,'2016-11-28 11:04:05','2016-11-28 11:04:05'),
( 10410219 ,'2016-11-28 11:01:58','2016-11-28 11:04:09'),
( 10410245 ,'2016-11-28 11:04:11','2016-11-28 11:04:11'),
( 2699815 ,'2016-11-28 11:03:57','2016-11-28 11:04:12'),
( 2699817 ,'2016-11-28 11:04:03','2016-11-28 11:04:19'),
( 2699821 ,'2016-11-28 11:04:12','2016-11-28 11:04:27'),
( 2699788 ,'2016-11-28 11:02:52','2016-11-28 11:04:33'),
( 2699828 ,'2016-11-28 11:04:35','2016-11-28 11:04:35'),
( 2699823 ,'2016-11-28 11:04:28','2016-11-28 11:04:38'),
( 2699834 ,'2016-11-28 11:04:36','2016-11-28 11:04:46'),
( 2699831 ,'2016-11-28 11:04:36','2016-11-28 11:04:46'),
( 2699827 ,'2016-11-28 11:04:34','2016-11-28 11:04:53'),
( 2699836 ,'2016-11-28 11:04:38','2016-11-28 11:04:54'),
( 2699829 ,'2016-11-28 11:04:36','2016-11-28 11:05:01'),
( 2699838 ,'2016-11-28 11:04:50','2016-11-28 11:05:03'),
( 2699822 ,'2016-11-28 11:04:24','2016-11-28 11:05:04'),
( 2699837 ,'2016-11-28 11:04:47','2016-11-28 11:05:06'),
( 2699835 ,'2016-11-28 11:04:37','2016-11-28 11:05:07'),
( 2699825 ,'2016-11-28 11:04:30','2016-11-28 11:05:08'),
( 2699843 ,'2016-11-28 11:05:00','2016-11-28 11:05:08'),
( 2699842 ,'2016-11-28 11:04:56','2016-11-28 11:05:11'),
( 1232448 ,'2016-11-28 11:04:35','2016-11-28 11:04:35'),
( 1232450 ,'2016-11-28 11:04:42','2016-11-28 11:05:14'),
( 1232453 ,'2016-11-28 11:05:16','2016-11-28 11:05:16'),
( 10410246 ,'2016-11-28 11:04:19','2016-11-28 11:04:33'),
( 10410248 ,'2016-11-28 11:04:35','2016-11-28 11:04:35'),
( 10410244 ,'2016-11-28 11:04:11','2016-11-28 11:04:40'),
( 10410249 ,'2016-11-28 11:04:38','2016-11-28 11:04:48'),
( 10410252 ,'2016-11-28 11:04:54','2016-11-28 11:05:10'),
( 10410255 ,'2016-11-28 11:05:16','2016-11-28 11:05:16'),
( 2699852 ,'2016-11-28 11:05:15','2016-11-28 11:05:16'),
( 2699846 ,'2016-11-28 11:05:03','2016-11-28 11:05:16'),
( 2699844 ,'2016-11-28 11:05:01','2016-11-28 11:05:17'),
( 2699830 ,'2016-11-28 11:04:36','2016-11-28 11:05:20'),
( 2699832 ,'2016-11-28 11:04:36','2016-11-28 11:05:20'),
( 2699847 ,'2016-11-28 11:05:05','2016-11-28 11:05:20'),
( 2699807 ,'2016-11-28 11:03:39','2016-11-28 11:05:21'),
( 2699848 ,'2016-11-28 11:05:07','2016-11-28 11:05:23'),
( 2699833 ,'2016-11-28 11:04:36','2016-11-28 11:05:23'),
( 2699841 ,'2016-11-28 11:04:54','2016-11-28 11:05:26'),
( 2699826 ,'2016-11-28 11:04:34','2016-11-28 11:05:27'),
( 2699845 ,'2016-11-28 11:05:01','2016-11-28 11:05:32'),
( 2699853 ,'2016-11-28 11:05:20','2016-11-28 11:05:35'),
( 2699850 ,'2016-11-28 11:05:10','2016-11-28 11:05:41'),
( 2699849 ,'2016-11-28 11:05:09','2016-11-28 11:05:58'),
( 2699857 ,'2016-11-28 11:05:44','2016-11-28 11:06:00'),
( 2699859 ,'2016-11-28 11:06:01','2016-11-28 11:06:02'),
( 2699862 ,'2016-11-28 11:06:06','2016-11-28 11:06:06'),
( 1232454 ,'2016-11-28 11:05:26','2016-11-28 11:05:35'),
( 1232452 ,'2016-11-28 11:05:15','2016-11-28 11:05:35'),
( 1232451 ,'2016-11-28 11:05:14','2016-11-28 11:06:05'),
( 1232449 ,'2016-11-28 11:04:38','2016-11-28 11:06:14'),
( 10410253 ,'2016-11-28 11:05:01','2016-11-28 11:05:20'),
( 10410256 ,'2016-11-28 11:05:17','2016-11-28 11:05:31'),
( 10410243 ,'2016-11-28 11:04:07','2016-11-28 11:05:35'),
( 10410257 ,'2016-11-28 11:05:23','2016-11-28 11:05:37'),
( 10410254 ,'2016-11-28 11:05:08','2016-11-28 11:05:37'),
( 10410258 ,'2016-11-28 11:05:29','2016-11-28 11:05:50'),
( 10410250 ,'2016-11-28 11:04:38','2016-11-28 11:06:00'),
( 10410259 ,'2016-11-28 11:05:32','2016-11-28 11:06:01'),
( 10410264 ,'2016-11-28 11:06:02','2016-11-28 11:06:02'),
( 10410227 ,'2016-11-28 11:02:56','2016-11-28 11:06:05'),
( 10410265 ,'2016-11-28 11:06:06','2016-11-28 11:06:06'),
( 10410237 ,'2016-11-28 11:03:41','2016-11-28 11:06:06'),
( 10410263 ,'2016-11-28 11:06:00','2016-11-28 11:06:14'),
( 2699855 ,'2016-11-28 11:05:31','2016-11-28 11:06:15'),
( 2699860 ,'2016-11-28 11:06:03','2016-11-28 11:06:16'),
( 2699856 ,'2016-11-28 11:05:37','2016-11-28 11:06:22'),
( 2699863 ,'2016-11-28 11:06:08','2016-11-28 11:06:24'),
( 2699875 ,'2016-11-28 11:06:27','2016-11-28 11:06:27'),
( 2699865 ,'2016-11-28 11:06:12','2016-11-28 11:06:28'),
( 2699876 ,'2016-11-28 11:06:28','2016-11-28 11:06:28'),
( 2699877 ,'2016-11-28 11:06:28','2016-11-28 11:06:29'),
( 2699866 ,'2016-11-28 11:06:14','2016-11-28 11:06:30'),
( 2699868 ,'2016-11-28 11:06:17','2016-11-28 11:06:30'),
( 2699881 ,'2016-11-28 11:06:31','2016-11-28 11:06:32'),
( 2699882 ,'2016-11-28 11:06:32','2016-11-28 11:06:32'),
( 2699869 ,'2016-11-28 11:06:17','2016-11-28 11:06:32'),
( 2699871 ,'2016-11-28 11:06:23','2016-11-28 11:06:36'),
( 2699864 ,'2016-11-28 11:06:10','2016-11-28 11:06:40'),
( 2699874 ,'2016-11-28 11:06:26','2016-11-28 11:06:42'),
( 2699880 ,'2016-11-28 11:06:31','2016-11-28 11:06:42'),
( 2699887 ,'2016-11-28 11:06:42','2016-11-28 11:06:42'),
( 2699824 ,'2016-11-28 11:04:30','2016-11-28 11:06:43'),
( 2699879 ,'2016-11-28 11:06:31','2016-11-28 11:06:47'),
( 2699883 ,'2016-11-28 11:06:32','2016-11-28 11:06:47'),
( 2699858 ,'2016-11-28 11:05:58','2016-11-28 11:06:49'),
( 2699870 ,'2016-11-28 11:06:21','2016-11-28 11:06:51'),
( 2699889 ,'2016-11-28 11:06:46','2016-11-28 11:06:53'),
( 2699867 ,'2016-11-28 11:06:15','2016-11-28 11:06:54'),
( 2699872 ,'2016-11-28 11:06:25','2016-11-28 11:07:03'),
( 2699854 ,'2016-11-28 11:05:21','2016-11-28 11:07:04'),
( 2699884 ,'2016-11-28 11:06:34','2016-11-28 11:07:05'),
( 2699895 ,'2016-11-28 11:07:05','2016-11-28 11:07:05'),
( 2699885 ,'2016-11-28 11:06:36','2016-11-28 11:07:07'),
( 2699878 ,'2016-11-28 11:06:29','2016-11-28 11:07:08'),
( 2699897 ,'2016-11-28 11:07:11','2016-11-28 11:07:11'),
( 1232443 ,'2016-11-28 11:03:24','2016-11-28 11:06:44'),
( 1232456 ,'2016-11-28 11:07:11','2016-11-28 11:07:11'),
( 10410261 ,'2016-11-28 11:05:48','2016-11-28 11:06:24'),
( 10410268 ,'2016-11-28 11:06:27','2016-11-28 11:06:27'),
( 10410270 ,'2016-11-28 11:06:28','2016-11-28 11:06:28'),
( 10410267 ,'2016-11-28 11:06:24','2016-11-28 11:06:38'),
( 10410260 ,'2016-11-28 11:05:41','2016-11-28 11:06:41'),
( 10410269 ,'2016-11-28 11:06:28','2016-11-28 11:06:42'),
( 10410275 ,'2016-11-28 11:06:42','2016-11-28 11:06:42'),
( 10410266 ,'2016-11-28 11:06:21','2016-11-28 11:06:42'),
( 10410271 ,'2016-11-28 11:06:30','2016-11-28 11:06:44'),
( 10410279 ,'2016-11-28 11:06:51','2016-11-28 11:06:52'),
( 10410274 ,'2016-11-28 11:06:42','2016-11-28 11:06:56'),
( 10410276 ,'2016-11-28 11:06:47','2016-11-28 11:07:01'),
( 10410277 ,'2016-11-28 11:06:48','2016-11-28 11:07:02'),
( 10410283 ,'2016-11-28 11:07:05','2016-11-28 11:07:05'),
( 10410272 ,'2016-11-28 11:06:40','2016-11-28 11:07:10'),
( 10410286 ,'2016-11-28 11:07:11','2016-11-28 11:07:11'),
( 2699892 ,'2016-11-28 11:06:57','2016-11-28 11:07:13'),
( 2699899 ,'2016-11-28 11:07:26','2016-11-28 11:07:26'),
( 2699886 ,'2016-11-28 11:06:38','2016-11-28 11:07:27'),
( 2699888 ,'2016-11-28 11:06:44','2016-11-28 11:07:29'),
( 2699898 ,'2016-11-28 11:07:17','2016-11-28 11:07:32'),
( 2699861 ,'2016-11-28 11:06:03','2016-11-28 11:07:33'),
( 2699894 ,'2016-11-28 11:07:02','2016-11-28 11:07:33'),
( 2699891 ,'2016-11-28 11:06:57','2016-11-28 11:07:34'),
( 2699900 ,'2016-11-28 11:07:28','2016-11-28 11:07:42'),
( 2699901 ,'2016-11-28 11:07:31','2016-11-28 11:07:44'),
( 2699902 ,'2016-11-28 11:07:33','2016-11-28 11:07:48'),
( 2699905 ,'2016-11-28 11:07:37','2016-11-28 11:07:50'),
( 2699907 ,'2016-11-28 11:07:38','2016-11-28 11:07:54'),
( 2699903 ,'2016-11-28 11:07:33','2016-11-28 11:08:02'),
( 2699909 ,'2016-11-28 11:07:48','2016-11-28 11:08:03'),
( 2699904 ,'2016-11-28 11:07:35','2016-11-28 11:08:06'),
( 2699908 ,'2016-11-28 11:07:44','2016-11-28 11:08:08'),
( 10410285 ,'2016-11-28 11:07:08','2016-11-28 11:07:23'),
( 10410290 ,'2016-11-28 11:07:26','2016-11-28 11:07:26'),
( 10410288 ,'2016-11-28 11:07:13','2016-11-28 11:07:27'),
( 10410282 ,'2016-11-28 11:07:05','2016-11-28 11:07:34'),
( 10410262 ,'2016-11-28 11:05:56','2016-11-28 11:07:36'),
( 10410284 ,'2016-11-28 11:07:07','2016-11-28 11:07:36'),
( 10410281 ,'2016-11-28 11:06:57','2016-11-28 11:08:02'),
( 10410278 ,'2016-11-28 11:06:50','2016-11-28 11:08:06'),
( 10410292 ,'2016-11-28 11:07:54','2016-11-28 11:08:08'),
( 10410296 ,'2016-11-28 11:08:17','2016-11-28 11:08:17'),
( 2699916 ,'2016-11-28 11:08:17','2016-11-28 11:08:17'),
( 2699910 ,'2016-11-28 11:07:56','2016-11-28 11:08:27'),
( 2699918 ,'2016-11-28 11:08:20','2016-11-28 11:08:36'),
( 2699920 ,'2016-11-28 11:08:34','2016-11-28 11:08:48'),
( 2699914 ,'2016-11-28 11:08:08','2016-11-28 11:08:50'),
( 2699921 ,'2016-11-28 11:08:35','2016-11-28 11:08:50'),
( 2699922 ,'2016-11-28 11:08:37','2016-11-28 11:08:52'),
( 2699919 ,'2016-11-28 11:08:22','2016-11-28 11:08:53'),
( 2699915 ,'2016-11-28 11:08:16','2016-11-28 11:08:54'),
( 2699929 ,'2016-11-28 11:08:55','2016-11-28 11:08:55'),
( 2699917 ,'2016-11-28 11:08:19','2016-11-28 11:08:56'),
( 2699925 ,'2016-11-28 11:08:45','2016-11-28 11:08:58'),
( 2699851 ,'2016-11-28 11:05:15','2016-11-28 11:09:00'),
( 2699931 ,'2016-11-28 11:09:03','2016-11-28 11:09:03'),
( 2699927 ,'2016-11-28 11:08:52','2016-11-28 11:09:05'),
( 2699926 ,'2016-11-28 11:08:50','2016-11-28 11:09:05'),
( 2699928 ,'2016-11-28 11:08:52','2016-11-28 11:09:07'),
( 10410293 ,'2016-11-28 11:07:56','2016-11-28 11:08:18'),
( 10410295 ,'2016-11-28 11:08:06','2016-11-28 11:08:35'),
( 10410294 ,'2016-11-28 11:08:01','2016-11-28 11:08:50'),
( 10410299 ,'2016-11-28 11:08:36','2016-11-28 11:08:50'),
( 10410300 ,'2016-11-28 11:08:50','2016-11-28 11:08:52'),
( 10410302 ,'2016-11-28 11:08:55','2016-11-28 11:08:55'),
( 10410298 ,'2016-11-28 11:08:27','2016-11-28 11:08:56'),
( 10410304 ,'2016-11-28 11:09:03','2016-11-28 11:09:03'),
( 10410291 ,'2016-11-28 11:07:48','2016-11-28 11:09:12'),
( 1232459 ,'2016-11-28 11:09:03','2016-11-28 11:09:03'),
( 2699930 ,'2016-11-28 11:08:57','2016-11-28 11:09:28'),
( 2699840 ,'2016-11-28 11:04:53','2016-11-28 11:09:31'),
( 2699933 ,'2016-11-28 11:09:31','2016-11-28 11:09:37'),
( 2699932 ,'2016-11-28 11:09:23','2016-11-28 11:09:39'),
( 2699935 ,'2016-11-28 11:09:37','2016-11-28 11:09:43'),
( 2699937 ,'2016-11-28 11:09:44','2016-11-28 11:09:44'),
( 2699940 ,'2016-11-28 11:09:50','2016-11-28 11:09:50'),
( 2699938 ,'2016-11-28 11:09:46','2016-11-28 11:09:52'),
( 2699934 ,'2016-11-28 11:09:35','2016-11-28 11:09:52'),
( 2699936 ,'2016-11-28 11:09:40','2016-11-28 11:09:55'),
( 2699941 ,'2016-11-28 11:09:52','2016-11-28 11:09:58'),
( 1232447 ,'2016-11-28 11:04:06','2016-11-28 11:09:32'),
( 1232461 ,'2016-11-28 11:09:44','2016-11-28 11:09:44'),
( 10410301 ,'2016-11-28 11:08:53','2016-11-28 11:09:22'),
( 10410311 ,'2016-11-28 11:09:44','2016-11-28 11:09:44'),
( 10410213 ,'2016-11-28 11:01:24','2016-11-28 11:09:45'),
( 10410220 ,'2016-11-28 11:01:58','2016-11-28 11:09:46'),
( 10410313 ,'2016-11-28 11:09:51','2016-11-28 11:09:51'),
( 10410309 ,'2016-11-28 11:09:39','2016-11-28 11:09:53'),
( 10410308 ,'2016-11-28 11:09:28','2016-11-28 11:09:57')
--
—
SOLUTION – 1
--
DECLARE @start AS DATETIME
SELECT @start = MIN(LoginTime) FROM Yourtabk
;WITH SingleDigits(Number) AS
(
SELECT Number
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
(9), (0)) AS X(Number)
)
,Series AS
(
SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number
from
SingleDigits as d1,
SingleDigits as d2,
SingleDigits as d3,
SingleDigits as d4
)
SELECT Number AtThisTime, COUNT(LoginID) LoggedInUsersCount FROM
(
SELECT DATEADD(minute, Number ,@start) Number FROM Series
WHERE Number > 0 AND DATEADD(minute, Number ,@start) <= ( SELECT MAX(LogoutTime) LogoutTime FROM Yourtabk )
)p INNER JOIN Yourtabk ON Number BETWEEN LoginTime AND LogoutTime
GROUP BY Number
ORDER BY Number
--
|
Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.
Enjoy !!! Keep Learning
Pawan Khowal
Http://MSBISkills.com