February 9, 2017 at 1:55 am
Hi ,
Below is my query,
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#promoter_details','U') IS NOT NULL
DROP TABLE #promoter_details
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#login_info','U') IS NOT NULL
DROP TABLE #login_info
--===== Create the test table with
CREATE TABLE #promoter_details
(
ID INT,
country nvarchar(50)
)
--===== Create the test table with
CREATE TABLE #login_info
(
log_id int not null primary key clustered,
emp_id int,
login_server_time DATETIME
)
--===== Insert the test data into the test table
INSERT INTO #login_info
(log_id, emp_id, login_server_time)
SELECT '20556','110','2016-05-26 02:21:52.333' UNION ALL
SELECT '20562','110','2016-05-26 13:21:30.907' UNION ALL
SELECT '20566','110','2016-05-26 13:27:36.720' UNION ALL
SELECT '20567','110','2016-05-26 13:30:35.770' UNION ALL
SELECT '20568','110','2016-05-26 13:47:48.777' UNION ALL
SELECT '20569','110','2016-05-26 13:49:40.160' UNION ALL
SELECT '20570','110','2016-05-26 13:51:12.417' UNION ALL
SELECT '20571','110','2016-05-26 13:53:31.723' UNION ALL
SELECT '20573','110','2016-05-26 13:55:48.133' UNION ALL
SELECT '20574','110','2016-05-26 13:56:54.077' UNION ALL
SELECT '20575','110','2016-05-26 13:57:38.700' UNION ALL
SELECT '20576','110','2016-05-26 13:58:32.350' UNION ALL
SELECT '20577','110','2016-05-26 14:01:48.603' UNION ALL
SELECT '20585','110','2016-05-26 14:37:32.553' UNION ALL
SELECT '20612','110','2016-05-26 16:54:29.360' UNION ALL
SELECT '20613','110','2016-05-26 16:57:13.247' UNION ALL
SELECT '20614','1962','2016-05-26 17:02:03.337' UNION ALL
SELECT '20653','1962','2016-05-27 14:53:32.830' UNION ALL
SELECT '20656','110','2016-05-27 15:14:27.080' UNION ALL
SELECT '20657','1962','2016-05-27 15:14:44.617' UNION ALL
SELECT '20658','110','2016-05-27 15:15:55.050' UNION ALL
SELECT '20659','43','2016-05-27 15:16:37.000' UNION ALL
SELECT '20660','110','2016-05-27 15:16:49.177' UNION ALL
SELECT '20661','110','2016-05-27 15:26:18.103' UNION ALL
SELECT '20662','110','2016-05-27 15:30:38.870' UNION ALL
SELECT '20663','110','2016-05-27 15:36:04.810' UNION ALL
SELECT '20665','1962','2016-05-27 15:46:16.280' UNION ALL
SELECT '20666','43','2016-05-27 15:49:21.577' UNION ALL
SELECT '20667','120','2016-05-27 16:14:20.553' UNION ALL
SELECT '20679','120','2016-05-27 21:36:45.197' UNION ALL
SELECT '20688','120','2016-05-28 16:29:04.550' UNION ALL
SELECT '20692','120','2016-05-28 20:32:27.183' UNION ALL
SELECT '20696','1956','2016-05-29 13:20:50.353' UNION ALL
SELECT '20697','1956','2016-05-29 13:21:41.087' UNION ALL
SELECT '20699','110','2016-05-29 13:27:36.513' UNION ALL
SELECT '20700','110','2016-05-29 13:53:27.057' UNION ALL
SELECT '20701','1964','2016-05-29 13:55:42.140' UNION ALL
SELECT '20703','1964','2016-05-29 13:57:45.157' UNION ALL
SELECT '20706','110','2016-05-29 13:59:38.447' UNION ALL
SELECT '20707','110','2016-05-29 13:59:44.237' UNION ALL
SELECT '20710','1958','2016-05-29 14:04:10.753' UNION ALL
SELECT '20711','1964','2016-05-29 14:04:36.880' UNION ALL
SELECT '20740','1962','2016-05-31 13:07:58.397' UNION ALL
SELECT '20743','110','2016-05-31 16:34:29.710' UNION ALL
SELECT '20744','120','2016-05-31 16:45:33.700' UNION ALL
SELECT '20745','120','2016-05-31 16:56:56.250' UNION ALL
SELECT '20746','120','2016-05-31 17:02:21.593' UNION ALL
SELECT '20747','120','2016-05-31 17:23:08.490' UNION ALL
SELECT '20748','1962','2016-05-31 17:26:46.267' UNION ALL
SELECT '20749','1956','2016-05-31 17:33:12.497' UNION ALL
SELECT '20750','1956','2016-05-31 17:36:02.473' UNION ALL
SELECT '20760','110','2016-05-31 19:18:48.930' UNION ALL
SELECT '20761','110','2016-05-31 19:23:27.770' UNION ALL
SELECT '20714','2028','2016-05-30 12:50:27.777' UNION ALL
SELECT '20738','110','2016-05-30 22:08:39.223' UNION ALL
SELECT '20763','110','2016-05-31 19:54:10.630' UNION ALL
SELECT '20776','110','2016-05-31 20:38:23.927' UNION ALL
SELECT '20777','2028','2016-05-31 20:38:25.483' UNION ALL
SELECT '20778','2028','2016-05-31 20:40:26.950' UNION ALL
SELECT '20779','2028','2016-05-31 20:44:47.757' UNION ALL
SELECT '20780','2028','2016-05-31 20:47:30.233' UNION ALL
SELECT '20781','2028','2016-05-31 20:55:40.677' UNION ALL
SELECT '20785','2028','2016-05-31 21:11:40.543' UNION ALL
SELECT '20786','110','2016-05-31 21:18:46.697' UNION ALL
SELECT '20787','110','2016-05-31 21:32:53.400' UNION ALL
SELECT '20788','3364','2016-05-31 21:38:24.773' UNION ALL
SELECT '20789','3364','2016-05-31 21:58:50.213' UNION ALL
SELECT '20790','3364','2016-05-31 21:59:42.630' UNION ALL
SELECT '20792','3364','2016-05-31 22:17:17.483' UNION ALL
SELECT '20793','110','2016-05-31 22:21:02.957' UNION ALL
SELECT '20798','110','2016-06-01 12:15:12.383' UNION ALL
SELECT '20799','2028','2016-06-01 12:29:24.630' UNION ALL
SELECT '20800','110','2016-06-01 12:45:27.283' UNION ALL
SELECT '20801','110','2016-06-01 12:54:59.460' UNION ALL
SELECT '20803','3364','2016-06-01 13:17:33.310' UNION ALL
SELECT '20804','1959','2016-06-01 13:18:16.410' UNION ALL
SELECT '20805','110','2016-06-01 13:18:58.550' UNION ALL
SELECT '20806','3364','2016-06-01 13:26:53.257' UNION ALL
SELECT '20807','110','2016-06-01 13:28:55.053' UNION ALL
SELECT '20809','110','2016-06-01 13:47:36.390' UNION ALL
SELECT '21037','3364','2016-06-04 15:14:39.143' UNION ALL
SELECT '21071','1956','2016-06-04 19:36:41.087' UNION ALL
SELECT '21072','1956','2016-06-04 19:48:01.700' UNION ALL
SELECT '21073','1956','2016-06-04 19:51:36.667' UNION ALL
SELECT '21074','1962','2016-06-04 20:10:04.297' UNION ALL
SELECT '21075','1958','2016-06-04 20:29:23.123' UNION ALL
SELECT '21076','1958','2016-06-04 20:29:34.703' UNION ALL
SELECT '20725','110','2016-05-30 17:27:06.920' UNION ALL
SELECT '20726','110','2016-05-30 17:40:03.937' UNION ALL
SELECT '20837','110','2016-06-01 16:47:05.120' UNION ALL
SELECT '20961','110','2016-06-02 20:56:07.213' UNION ALL
SELECT '21040','1964','2016-06-04 15:49:37.077' UNION ALL
SELECT '21041','1958','2016-06-04 17:12:18.243' UNION ALL
SELECT '21042','1958','2016-06-04 17:17:31.980' UNION ALL
SELECT '21043','1958','2016-06-04 17:17:38.993' UNION ALL
SELECT '21044','1958','2016-06-04 17:17:57.810' UNION ALL
SELECT '21045','1958','2016-06-04 17:20:41.463' UNION ALL
SELECT '21046','1958','2016-06-04 17:22:20.280' UNION ALL
SELECT '21047','1958','2016-06-04 17:22:30.317' UNION ALL
SELECT '21048','1958','2016-06-04 17:22:35.840' UNION ALL
SELECT '21049','1958','2016-06-04 17:22:40.140' UNION ALL
SELECT '21050','1958','2016-06-04 17:25:21.620' UNION ALL
SELECT '21051','1958','2016-06-04 17:25:25.900' UNION ALL
SELECT '21052','1962','2016-06-04 17:25:56.410' UNION ALL
SELECT '21053','1958','2016-06-04 17:27:52.113' UNION ALL
SELECT '21054','1958','2016-06-04 17:28:03.993' UNION ALL
SELECT '21055','1958','2016-06-04 17:28:45.077' UNION ALL
SELECT '21056','1958','2016-06-04 17:29:15.657' UNION ALL
SELECT '21057','1958','2016-06-04 17:29:25.173' UNION ALL
SELECT '21061','1958','2016-06-04 17:39:30.023' UNION ALL
SELECT '21062','1958','2016-06-04 17:42:33.717' UNION ALL
SELECT '21063','1956','2016-06-04 17:43:06.080' UNION ALL
SELECT '21064','48','2016-06-04 17:50:36.013' UNION ALL
SELECT '21065','48','2016-06-04 17:53:01.410' UNION ALL
SELECT '21066','48','2016-06-04 17:53:37.750' UNION ALL
SELECT '21067','1764','2016-06-04 17:53:55.310' UNION ALL
SELECT '21068','1958','2016-06-04 18:41:18.303' UNION ALL
SELECT '21069','1958','2016-06-04 18:41:32.283' UNION ALL
SELECT '21070','110','2016-06-04 18:44:16.343' UNION ALL
SELECT '21077','1958','2016-06-04 20:29:41.557' UNION ALL
SELECT '21078','1962','2016-06-04 20:30:42.263' UNION ALL
SELECT '21079','1956','2016-06-04 20:48:02.770' UNION ALL
SELECT '21080','110','2016-06-04 20:48:46.310' UNION ALL
SELECT '21081','1958','2016-06-04 20:49:05.773' UNION ALL
SELECT '21082','1958','2016-06-04 20:49:08.433' UNION ALL
SELECT '21083','1958','2016-06-04 20:49:09.460' UNION ALL
SELECT '21084','1958','2016-06-04 20:49:10.437' UNION ALL
SELECT '21085','1958','2016-06-04 20:49:11.510' UNION ALL
SELECT '21086','1958','2016-06-04 20:49:12.437' UNION ALL
SELECT '21087','1958','2016-06-04 20:49:13.327' UNION ALL
SELECT '21088','1958','2016-06-04 20:49:14.233' UNION ALL
SELECT '21089','1958','2016-06-04 20:49:15.047' UNION ALL
SELECT '21090','1956','2016-06-04 20:49:50.993' UNION ALL
SELECT '21091','110','2016-06-04 20:55:38.873' UNION ALL
SELECT '21092','3373','2016-06-04 20:57:47.060' UNION ALL
SELECT '21093','1958','2016-06-04 20:58:54.033' UNION ALL
SELECT '21094','1958','2016-06-04 21:07:03.807' UNION ALL
SELECT '21095','1958','2016-06-04 21:07:13.450' UNION ALL
SELECT '21096','1958','2016-06-04 21:07:16.970' UNION ALL
SELECT '21097','1958','2016-06-04 21:07:20.360' UNION ALL
SELECT '21098','3373','2016-06-04 21:09:39.760' UNION ALL
SELECT '21099','1958','2016-06-04 21:10:11.863' UNION ALL
SELECT '21100','1958','2016-06-04 21:10:17.477' UNION ALL
SELECT '21101','110','2016-06-04 21:13:14.483' UNION ALL
SELECT '21102','3373','2016-06-04 21:14:58.387' UNION ALL
SELECT '21112','3364','2016-06-07 12:27:46.100' UNION ALL
SELECT '21113','3364','2016-06-07 12:28:47.947' UNION ALL
SELECT '21114','3364','2016-06-07 12:30:40.730' UNION ALL
SELECT '21117','110','2016-06-07 12:54:39.310' UNION ALL
SELECT '21118','110','2016-06-07 13:04:20.597' UNION ALL
SELECT '21120','110','2016-06-07 13:36:04.683' UNION ALL
SELECT '20822','110','2016-06-01 14:36:37.123' UNION ALL
SELECT '20823','3364','2016-06-01 14:37:54.943' UNION ALL
SELECT '20824','3364','2016-06-01 14:41:41.000'
--===== Insert the test data into the test table
INSERT INTO #promoter_details
(ID, country)
SELECT '110','IN' UNION ALL
SELECT '339','IN' UNION ALL
SELECT '374','IN' UNION ALL
SELECT '375','IN' UNION ALL
SELECT '1958','KR' UNION ALL
SELECT '377','IN' UNION ALL
SELECT '378','IN' UNION ALL
SELECT '379','IN' UNION ALL
SELECT '380','IN' UNION ALL
SELECT '3373','TH' UNION ALL
SELECT '382','IN' UNION ALL
SELECT '383','IN' UNION ALL
SELECT '384','IN' UNION ALL
SELECT '2050','TC' UNION ALL
SELECT '2051','TC' UNION ALL
SELECT '2052','TC' UNION ALL
SELECT '2053','TC' UNION ALL
SELECT '2054','TC' UNION ALL
SELECT '2055','TC' UNION ALL
SELECT '2056','TC' UNION ALL
SELECT '2057','TC' UNION ALL
SELECT '2058','TC' UNION ALL
SELECT '2059','TC' UNION ALL
SELECT '2060','TC' UNION ALL
SELECT '2061','TC' UNION ALL
SELECT '2062','TC' UNION ALL
SELECT '2063','TC' UNION ALL
SELECT '2064','TC' UNION ALL
SELECT '2065','TC' UNION ALL
SELECT '2066','TC' UNION ALL
SELECT '2067','TC' UNION ALL
SELECT '2068','TC' UNION ALL
SELECT '2069','TC' UNION ALL
SELECT '2070','TC' UNION ALL
SELECT '2071','TC' UNION ALL
SELECT '2072','TC' UNION ALL
SELECT '2073','TC' UNION ALL
SELECT '2074','TC' UNION ALL
SELECT '2075','TC' UNION ALL
SELECT '2076','TC' UNION ALL
SELECT '2077','TC' UNION ALL
SELECT '2078','TC' UNION ALL
SELECT '2079','TC' UNION ALL
SELECT '2080','TC' UNION ALL
SELECT '2081','TC' UNION ALL
SELECT '2082','TC' UNION ALL
SELECT '2083','TC' UNION ALL
SELECT '2084','TC' UNION ALL
SELECT '2085','TC' UNION ALL
SELECT '2086','IN' UNION ALL
SELECT '2087','PH' UNION ALL
SELECT '2088','MY' UNION ALL
SELECT '2089','MY' UNION ALL
SELECT '3374','PH'
select p.id,p.country,li.login_server_time as Login_frequency
from #promoter_details p inner join #login_info li on p.id=li.emp_id
The output is,
id country Login_frequency
110 IN 2016-05-26 02:21:52.333
110 IN 2016-05-26 13:21:30.907
110 IN 2016-05-26 13:27:36.720
110 IN 2016-05-26 13:30:35.770
110 IN 2016-05-26 13:47:48.777
110 IN 2016-05-26 13:49:40.160
110 IN 2016-05-26 13:51:12.417
110 IN 2016-05-26 13:53:31.723
110 IN 2016-05-26 13:55:48.133
110 IN 2016-05-26 13:56:54.077
110 IN 2016-05-26 13:57:38.700
110 IN 2016-05-26 13:58:32.350
110 IN 2016-05-26 14:01:48.603
110 IN 2016-05-26 14:37:32.553
110 IN 2016-05-26 16:54:29.360
110 IN 2016-05-26 16:57:13.247
110 IN 2016-05-27 15:14:27.080
110 IN 2016-05-27 15:15:55.050
110 IN 2016-05-27 15:16:49.177
110 IN 2016-05-27 15:26:18.103
110 IN 2016-05-27 15:30:38.870
110 IN 2016-05-27 15:36:04.810
110 IN 2016-05-29 13:27:36.513
110 IN 2016-05-29 13:53:27.057
110 IN 2016-05-29 13:59:38.447
110 IN 2016-05-29 13:59:44.237
1958 KR 2016-05-29 14:04:10.753
110 IN 2016-05-30 17:27:06.920
110 IN 2016-05-30 17:40:03.937
110 IN 2016-05-30 22:08:39.223
110 IN 2016-05-31 16:34:29.710
110 IN 2016-05-31 19:18:48.930
110 IN 2016-05-31 19:23:27.770
110 IN 2016-05-31 19:54:10.630
110 IN 2016-05-31 20:38:23.927
110 IN 2016-05-31 21:18:46.697
110 IN 2016-05-31 21:32:53.400
110 IN 2016-05-31 22:21:02.957
110 IN 2016-06-01 12:15:12.383
110 IN 2016-06-01 12:45:27.283
110 IN 2016-06-01 12:54:59.460
110 IN 2016-06-01 13:18:58.550
110 IN 2016-06-01 13:28:55.053
110 IN 2016-06-01 13:47:36.390
110 IN 2016-06-01 14:36:37.123
110 IN 2016-06-01 16:47:05.120
110 IN 2016-06-02 20:56:07.213
1958 KR 2016-06-04 17:12:18.243
1958 KR 2016-06-04 17:17:31.980
1958 KR 2016-06-04 17:17:38.993
1958 KR 2016-06-04 17:17:57.810
1958 KR 2016-06-04 17:20:41.463
1958 KR 2016-06-04 17:22:20.280
1958 KR 2016-06-04 17:22:30.317
1958 KR 2016-06-04 17:22:35.840
1958 KR 2016-06-04 17:22:40.140
1958 KR 2016-06-04 17:25:21.620
1958 KR 2016-06-04 17:25:25.900
1958 KR 2016-06-04 17:27:52.113
1958 KR 2016-06-04 17:28:03.993
1958 KR 2016-06-04 17:28:45.077
1958 KR 2016-06-04 17:29:15.657
1958 KR 2016-06-04 17:29:25.173
1958 KR 2016-06-04 17:39:30.023
1958 KR 2016-06-04 17:42:33.717
1958 KR 2016-06-04 18:41:18.303
1958 KR 2016-06-04 18:41:32.283
110 IN 2016-06-04 18:44:16.343
1958 KR 2016-06-04 20:29:23.123
1958 KR 2016-06-04 20:29:34.703
1958 KR 2016-06-04 20:29:41.557
110 IN 2016-06-04 20:48:46.310
1958 KR 2016-06-04 20:49:05.773
1958 KR 2016-06-04 20:49:08.433
1958 KR 2016-06-04 20:49:09.460
1958 KR 2016-06-04 20:49:10.437
1958 KR 2016-06-04 20:49:11.510
1958 KR 2016-06-04 20:49:12.437
1958 KR 2016-06-04 20:49:13.327
1958 KR 2016-06-04 20:49:14.233
1958 KR 2016-06-04 20:49:15.047
110 IN 2016-06-04 20:55:38.873
3373 TH 2016-06-04 20:57:47.060
3373 PH 2016-06-04 20:57:47.060
1958 KR 2016-06-04 20:58:54.033
1958 KR 2016-06-04 21:07:03.807
1958 KR 2016-06-04 21:07:13.450
1958 KR 2016-06-04 21:07:16.970
1958 KR 2016-06-04 21:07:20.360
3373 TH 2016-06-04 21:09:39.760
3373 PH 2016-06-04 21:09:39.760
1958 KR 2016-06-04 21:10:11.863
1958 KR 2016-06-04 21:10:17.477
110 IN 2016-06-04 21:13:14.483
3373 TH 2016-06-04 21:14:58.387
3373 PH 2016-06-04 21:14:58.387
110 IN 2016-06-07 12:54:39.310
110 IN 2016-06-07 13:04:20.597
110 IN 2016-06-07 13:36:04.683
Need to select distinct value of Login_frquency according to entry per day,
id country Login_frequency
110 IN 16
110 IN 6
110 IN 4
1958 KR 1
110 IN 3
110 IN 8
110 IN 8
110 IN 1
1958 KR 39
3373 TH 2
3373 TH 1
3373 PH 3
110 IN 7
How to achieve this?
Thanks in Advance,
Regards,
Poornima
February 9, 2017 at 2:08 am
Quick suggestion
😎
SELECT
PD.ID
,PD.country
,COUNT(*) AS Login_frequency
,CONVERT(DATE,LI.login_server_time,0) AS Login_Date
FROM #promoter_details PD
INNER JOIN #login_info LI
ON PD.ID = LI.emp_id
GROUP BY PD.ID
,PD.country
,CONVERT(DATE,LI.login_server_time,0);
Output
ID country Login_frequency Login_Date
----------- --------- --------------- ----------
110 IN 16 2016-05-26
110 IN 6 2016-05-27
110 IN 4 2016-05-29
110 IN 3 2016-05-30
110 IN 8 2016-05-31
110 IN 8 2016-06-01
110 IN 1 2016-06-02
110 IN 4 2016-06-04
110 IN 3 2016-06-07
1958 KR 1 2016-05-29
1958 KR 39 2016-06-04
3373 PH 3 2016-06-04
3373 TH 3 2016-06-04
February 9, 2017 at 2:38 am
Thanks Eirikur Eiriksson.
Is it possible to group by Id and bring the output like this,
ID country Login_frequency
--- ------------- ------------------------
110 IN 53
1958 KR 40
3373 PH 3
Regards,
Poornima
February 9, 2017 at 2:48 am
Simply remove the date from the group by clause
😎
SELECT
PD.ID
,PD.country
,COUNT(*) AS Login_frequency
FROM #promoter_details PD
INNER JOIN #login_info LI
ON PD.ID = LI.emp_id
GROUP BY PD.ID
,PD.country;
Output
ID country Login_frequency
----------- ---------- ---------------
110 IN 53
1958 KR 40
3373 PH 3
3373 TH 3
February 9, 2017 at 3:02 am
Thanks Eirikur Eiriksson.
I got my result..
Really this forum is very helpful..
Regards,
Poornima
February 10, 2017 at 5:18 am
poornima.s_pdi - Thursday, February 9, 2017 3:02 AMThanks Eirikur Eiriksson.
I got my result..
Really this forum is very helpful..Regards,
Poornima
You are very welcome.
😎
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply