2

I have a table that looks like this:

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 want to retrieve CUSTOMERID more than 2 records with time so I have written this query and got the result.

Main Query:

SELECT   TOP (10) [CUSTOMERID] , [ENTRYTRIPDATETIME]  
FROM     [ISSUER].[TOLLPLUS].[TP_CUSTOMERTRIPS] 
GROUP BY [CUSTOMERID],[ENTRYTRIPDATETIME] 
HAVING   COUNT(*) > 2

Result:

CUSTOMERID  ENTRYTRIPDATETIME
20063381    2016-12-08 00:24:35.000
20010438    2016-12-16 22:16:55.000
20015278    2017-01-28 05:18:20.000
20108092    2017-02-10 00:37:09.000
20013275    2017-01-06 07:42:05.000
20018833    2017-01-27 04:53:32.000
20113994    2017-01-27 01:13:44.000
20199820    2017-01-27 05:34:24.000
20124122    2017-02-01 02:47:01.000
20240710    2017-03-21 13:02:18.000

But my requirement is to split the Date and Time in the abobe result, I mean there should be 3 Columns, so my question is where I will be able to write below query in my Main Query?

CONVERT(VARCHAR(10),[ENTRYTRIPDATETIME],101) as DatePart,
CONVERT(VARCHAR(10),[ENTRYTRIPDATETIME],108) as TimePart
2

You almost get it:

SELECT   TOP (10) [CUSTOMERID], 
         CONVERT(VARCHAR(20), [ENTRYTRIPDATETIME], 101) AS DATEPART,
         CONVERT(VARCHAR(20), [ENTRYTRIPDATETIME], 108) AS TIMEPART
FROM     [TP_CUSTOMERTRIPS] 
GROUP BY [CUSTOMERID],[ENTRYTRIPDATETIME] 
HAVING   COUNT(*) > 2;
SELECT   TOP (10) [CUSTOMERID], 
         CONVERT(VARCHAR(20), [ENTRYTRIPDATETIME], 101) AS DATEPART,
         CONVERT(VARCHAR(20), [ENTRYTRIPDATETIME], 108) AS TIMEPART
FROM     [TP_CUSTOMERTRIPS] 
GROUP BY [CUSTOMERID],[ENTRYTRIPDATETIME] 
HAVING   COUNT(*) > 2;
GO
CUSTOMERID | DATEPART   | TIMEPART
---------: | :--------- | :-------
  20063381 | 12/08/2016 | 00:24:35
  20010438 | 12/16/2016 | 22:16:55
  20013275 | 01/06/2017 | 07:42:05
  20113994 | 01/27/2017 | 01:13:44
  20018833 | 01/27/2017 | 04:53:32
  20199820 | 01/27/2017 | 05:34:24
  20015278 | 01/28/2017 | 05:18:20
  20124122 | 02/01/2017 | 02:47:01
  20108092 | 02/10/2017 | 00:37:09
  20240710 | 03/21/2017 | 13:02:18

db<>fiddle here

2

Another way:

SELECT   TOP (10) [CUSTOMERID], 
         [DATEPART] = CONVERT(date, [ENTRYTRIPDATETIME]),
         [TIMEPART] = CONVERT(time, [ENTRYTRIPDATETIME])
FROM     [ISSUER].[TOLLPLUS].[TP_CUSTOMERTRIPS] 
GROUP BY [CUSTOMERID],[ENTRYTRIPDATETIME] 
HAVING   COUNT(*) > 2;

Also, why can't the presentation layer do this?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.