I want to get the records for a month of January'2017 and here is the one row sample of my huge table
Table:
CUSTTRIPID TPTRIPID CUSTOMERID ENTRY_TOLLTXNID EXIT_TOLLTXNID ENTRYTRIPDATETIME EXITTRIPDATETIME TRIPIDENTMETHOD TRIPCHARGETYPE ENTRYLANEID ENTRYPLAZAID EXITLANEID EXITPLAZAID VEHICLENUMBER VEHICLESTATE VEHICLECLASS VEHICLEID TAGREFID TOLLAMOUNT FEEAMOUNTS DISCOUNTSAMOUNT OUTSTANDINGAMOUNT TRIPSTAGEID TRIPSTATUSID TRIPSTATUSDATE POSTEDDATE PAYMENTSTATUSID CREATEDDATE CREATEDUSER UPDATEDDATE UPDATEDUSER REFLINKTRIPID PLAZA
39038181 34040337 20272679 35126165 35126165 2017-05-04 08:15:54.000 2017-05-04 08:15:54.000 980 P NULL NULL NULL NULL KA03D8435 KA VC10 272614 34161FA8203289720249F4C0 30.00 0.00 0.00 30.00 3 1 2017-05-04 08:31:54.657 2017-05-04 08:31:54.657 456 2017-05-04 08:32:11.130 TRIPPOST 2017-05-04 08:32:11.130 TRIPPOST 0 BangaloreNelamangalaTollPlaza
I have applied this query to get the Date part because the requirement was to retrieve CUSTOMERID
more than 2 records with time (of a month).
Query:
SELECT TOP (10) [CUSTOMERID],
CONVERT(VARCHAR(20), [ENTRYTRIPDATETIME], 101) AS DATEPART,
CONVERT(VARCHAR(20), [ENTRYTRIPDATETIME], 108) AS TIMEPART
FROM [ISSUER].[TOLLPLUS].[TP_CUSTOMERTRIPS]
GROUP BY [CUSTOMERID],[ENTRYTRIPDATETIME]
HAVING COUNT(*) > 2;
Result:
CUSTOMERID DATEPART TIMEPART
20010438 12/16/2016 22:16:55
20015278 1/28/2017 5:18:20
20108092 2/10/2017 0:37:09
20018833 1/27/2017 4:53:32
20199820 1/27/2017 5:34:24
20124122 2/1/2017 2:47:01
20240710 3/21/2017 13:02:18
20143691 3/21/2017 8:08:03
20074212 12/15/2016 7:28:56
20114518 12/18/2016 6:11:31
20009754 12/17/2016 20:38:40
20072106 3/17/2017 19:37:20
20092287 1/28/2017 10:54:06
20055028 1/27/2017 4:36:51
20003676 4/23/2017 1:34:11
Now how to get the result for a month, assume for January 2017 ?
Best Answer
An example can be found in the fiddle here (month) and here (year)!