SQL Puzzle | The Separation Puzzle – 1

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The Separation Puzzle – 1 [ Separate Out Integer and Character Data from a column ]

In this SQL Puzzle, you have a table with a Varchar column. This column contains integer and character values. We need to write the logic to separate out integer and character data into 2 different columns.

So effective we will have 3 columns in the output i.e. Original Data, Integer Data, Character Data.

Please check out the sample input values and sample expected output below.

Sample Input

Value
Pawan111000024
PawanSh0000111
PawanSh0000111
RameshSh00001111111

Expected Output

Ints chars
Pawan 111000024
PawanSh 0000111
PawanSh 0000111
RameshSh 00001111111

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE SeperateNumbersAndCharcters
(
	Value VARCHAR(100)
)
GO

INSERT INTO SeperateNumbersAndCharcters
VALUES
('Pawan111000024'),
('PawanSh0000111'),
('PawanSh0000111'),
('RameshSh00001111111')
GO

CREATE NONCLUSTERED INDEX Ix_Value ON SeperateNumbersAndCharcters ( Value )

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution 1 , Using Numbers Table – Set Based Approach


--

SELECT 
        SUBSTRING(Value, 0, PATINDEX('%[0-9]%', Value)) Ints,
        SUBSTRING(Value, PATINDEX('%[0-9]%', Value), LEN(Value)) chars
FROM    SeperateNumbersAndCharcters

--

[Note the above will not if you have number and characters mixed with each other . For Example – 23874283bsdjfhsjadkhfas09 , For that please refer below -]

Ref – https://msbiskills.com/2016/06/03/sql-puzzle-the-separation-puzzle/

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

SQL Puzzle | No of Active Users at each time in the System

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

SQL Puzzle | Update Data that with a string contains apostrophe

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Update Data that with a string contains apostrophe

Write a T-SQL that updates a column with a string that contains apostrophe. Basically we are expecting an update statement here.

For more please check out the sample input and the expected output.

Sample Inputs

column1
blah
Pawan

Expected Output

column1
/tƒÉÄk…Bz-ñõ’âÜ(sÀ\ÿ$K’ylôû
Pawan

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 tests
(
	column1 VARCHAR(MAX)
)
GO

INSERT INTO tests VALUES ('blah'),('Pawan')

--

SOLUTION – 1


--

UPDATE a
SET a.column1 =  '/tƒÉÄk…Bz–ñõ''âÜ(sÀ\ÿ$K''ylôû'
FROM tests a
WHERE column1 = 'blah'

--

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

SQL Puzzle | The XML Path Puzzle

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The XML Path Puzzle

Write a T-SQL that gives us data in the expected format. Basically we have to get first name, middle and last name tilt separated for each Id and lineId

For more please check out the sample input and the expected output.

Sample Inputs

ID LineID F1 F2 F3
1 1 Joe Thomas Smith
1 1 David Harry Jones
1 2 Joe Thomas Smith
1 2 David Hary Jones
1 3 Joe Thomas Smith
1 4 Joe Thomas Smith
1 4 David Harry Jones
1 4 Carlos Jay Smith
2 1 Jane Janet Garcia

Expected Output

ID LineID F1 F2 F3 R
1 1 Joe Thomas Smith Joe~Thomas~Smith| David~Harry~Jones
1 1 David Harry Jones Joe~Thomas~Smith| David~Harry~Jones
1 2 Joe Thomas Smith Joe~Thomas~Smith| David~Hary~Jones
1 2 David Hary Jones Joe~Thomas~Smith| David~Hary~Jones
1 3 Joe Thomas Smith Joe~Thomas~Smith
1 4 Joe Thomas Smith Joe~Thomas~Smith| David~Harry~Jones| Carlos~Jay~Smith
1 4 David Harry Jones Joe~Thomas~Smith| David~Harry~Jones| Carlos~Jay~Smith
1 4 Carlos Jay Smith Joe~Thomas~Smith| David~Harry~Jones| Carlos~Jay~Smith
2 1 Jane Janet Garcia Jane~Janet~Garcia

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 testPivot
(
	 ID     TINYINT 
	,LineID TINYINT 
	,F1     VARCHAR(25)
	,F2     VARCHAR(25)     
	,F3     VARCHAR(25)
)
GO

INSERT INTO testPivot VALUES
(1,   1,       'Joe'   , 'Thomas',    'Smith'),
(1,   1,       'David' , 'Harry'  ,   'Jones'),
(1,   2,       'Joe'   , 'Thomas'  ,  'Smith'),
(1,   2,       'David' , 'Hary'    ,  'Jones'),
(1,   3,       'Joe'   , 'Thomas'  ,  'Smith'),
(1,   4,       'Joe'   , 'Thomas'  ,  'Smith'),
(1,   4,       'David' , 'Harry'   ,  'Jones'),
(1,   4,       'Carlos', 'Jay'     ,  'Smith'),
(2,   1,       'Jane',   'Janet'   ,  'Garcia')
GO

--

SOLUTION – 1


--



SELECT m.ID, m.LineID , m.F1 , m.F2 , m.F3 ,  STUFF 
                ((
					SELECT '| ' + m2.N
					FROM (SELECT *, CONCAT(F1,'~',F2,'~',F3) N FROM testPivot) m2
					WHERE ( m.LineID = m2.LineID AND m.ID = m2.ID )
					FOR XML PATH('')
					) ,1,2,'') 
                R
FROM
(
	SELECT *, CONCAT(F1,'~',F2,'~',F3) N FROM testPivot
)m

--


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

SQL Puzzle | First and Second Record – Pivot Format

Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | First and Second Record – Pivot Format

Write a T-SQL to fetch first and Second Record in Pivot Format.
For more please check out the sample input and the expected output.

Sample Inputs

PrimaryKey Score DateField
1 50 2016-01-01
2 12 2016-01-01
1 75 2016-01-12
1 80 2016-01-15
2 35 2016-01-16

Expected Output

PrimaryKey Score1 Score2 DateField1 DateField2
1 80 75 2016-01-15 2016-01-12
2 35 12 2016-01-16 2016-01-01

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 aTest
(	
	  PrimaryKey INT
	, Score INT
	, DateField DATE
)
GO


INSERT INTO aTest VALUES
(1, 50, '1/1/2016'),
(2, 12, '1/1/2016'),
(1, 75, '1/12/2016'),
(1, 80, '1/15/2016'),
(2, 35, '1/16/2016')
GO

--

SOLUTION – 1


--

;WITH CTE AS
(
	SELECT * FROM 
	(
		SELECT DISTINCT PrimaryKey 
		FROM aTest
	)p
	CROSS APPLY
	(
		SELECT TOP 2 Score , DateField 
		FROM aTest b
		WHERE b.PrimaryKey = p.PrimaryKey
		ORDER BY b.DateField DESC
	)k
)
,CTE1 AS 
(
	SELECT *, CONCAT('Score',rnk) c1 , CONCAT('DateField',rnk) c2 FROM 
	(
		SELECT *, ROW_NUMBER() OVER (PARTITION BY PRIMARYKey Order BY DateField DESC) rnk
		FROM CTE 
	)k
)
SELECT PrimaryKey , MAX([Score1])[Score1],MAX([Score2])[Score2],MAX(DateField1)DateField1,MAX(DateField2)DateField2 FROM CTE1
PIVOT ( MAX(Score) FOR c1 IN ([Score1],[Score2]) )y1
PIVOT ( MAX(DateField) FOR c2 IN (DateField1,DateField2) )y1
GROUP BY PrimaryKey 

--

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