Filter out records from table

Question

Post reply

Filter out records from table

PSB

SSChampion

Points: 13195
More actions
February 7, 2014 at 5:55 am

#289331

Hi,
I have a table where I want to filter out some records.
I want to get maximum OF enddate BY USER AND HName WHERE description = Client
FOR USER 'dk' I want to return the record WHERE EndDate ='2015-06-27 23:59:59.000' AND Description = 'Client' AND HName = 'CR-GINGA'
FOR USER 'dk' I want to return the record WHERE EndDate ='2015-06-27 23:59:59.000' AND Description = 'Client' AND HName = 'CR-ABCED'
FOR USER 'ff' I want to return the record WHERE EndDate ='2016-01-14 23:59:59.000' AND Description = 'Client' AND HName = 'HN-FRNNN'
CREATE TABLE #Info
(
HName VARCHAR(100),
USERName VARCHAR(10),
StartDate DATETIME,
ENdDAte DATETIME,
DESCRIPTION VARCHAR(100),
STag VARCHAR(10)
)
INSERT INTO #Info
( HName ,
USERName ,
StartDate ,
ENdDAte ,
DESCRIPTION ,
STag
)
VALUES ( 'CR-GINGA' , -- HName - varchar(100)
'dk' , -- USERName - varchar(10)
'2012-06-27 00:00:00.000' , -- StartDate - datetime
'2013-06-27 23:59:59.000' , -- ENdDAte - datetime
'Client' , -- DESCRIPTION - varchar(100)
'ABX123' -- STag - varchar(10)
)
INSERT INTO #Info
( HName ,
USERName ,
StartDate ,
ENdDAte ,
DESCRIPTION ,
STag
)
VALUES ( 'CR-GINGA' , -- HName - varchar(100)
'dk' , -- USERName - varchar(10)
'2013-06-28 00:00:00.000' , -- StartDate - datetime
'2015-06-27 23:59:59.000' , -- ENdDAte - datetime
'Client' , -- DESCRIPTION - varchar(100)
'ABX123' -- STag - varchar(10)
)
INSERT INTO #Info
( HName ,
USERName ,
StartDate ,
ENdDAte ,
DESCRIPTION ,
STag
)
VALUES ( 'CR-GINGA' , -- HName - varchar(100)
'dk' , -- USERName - varchar(10)
'2012-06-27 00:00:00.000' , -- StartDate - datetime
'2015-06-27 23:59:59.000' , -- ENdDAte - datetime
'Comp Care' , -- DESCRIPTION - varchar(100)
'ABX123' -- STag - varchar(10)
)
INSERT INTO #Info
( HName ,
USERName ,
StartDate ,
ENdDAte ,
DESCRIPTION ,
STag
)
VALUES ( 'CR-GINGA' , -- HName - varchar(100)
'dk' , -- USERName - varchar(10)
'2012-06-27 00:00:00.000' , -- StartDate - datetime
'2013-06-27 23:59:59.000' , -- ENdDAte - datetime
'Next business' , -- DESCRIPTION - varchar(100)
'ABX123' -- STag - varchar(10)
)
INSERT INTO #Info
( HName ,
USERName ,
StartDate ,
ENdDAte ,
DESCRIPTION ,
STag
)
VALUES ( 'CR-GINGA' , -- HName - varchar(100)
'dk' , -- USERName - varchar(10)
'2013-06-28 00:00:00.000' , -- StartDate - datetime
'2015-06-27 23:59:59.000' , -- ENdDAte - datetime
'Next businessNext business' , -- DESCRIPTION - varchar(100)
'ABX123' -- STag - varchar(10)
)
INSERT INTO #Info
( HName ,
USERName ,
StartDate ,
ENdDAte ,
DESCRIPTION ,
STag
)
VALUES ( 'CR-ABCED' , -- HName - varchar(100)
'dk' , -- USERName - varchar(10)
'2012-06-27 00:00:00.000' , -- StartDate - datetime
'2013-06-27 23:59:59.000' , -- ENdDAte - datetime
'Client' , -- DESCRIPTION - varchar(100)
'ABX123' -- STag - varchar(10)
)
INSERT INTO #Info
( HName ,
USERName ,
StartDate ,
ENdDAte ,
DESCRIPTION ,
STag
)
VALUES ( 'CR-ABCED' , -- HName - varchar(100)
'dk' , -- USERName - varchar(10)
'2013-06-28 00:00:00.000' , -- StartDate - datetime
'2015-06-27 23:59:59.000' , -- ENdDAte - datetime
'Client' , -- DESCRIPTION - varchar(100)
'ABX123' -- STag - varchar(10)
)
INSERT INTO #Info
( HName ,
USERName ,
StartDate ,
ENdDAte ,
DESCRIPTION ,
STag
)
VALUES ( 'CR-ABCED' , -- HName - varchar(100)
'dk' , -- USERName - varchar(10)
'2013-06-27 00:00:00.000' , -- StartDate - datetime
'2015-06-27 23:59:59.000' , -- ENdDAte - datetime
'Comp Care' , -- DESCRIPTION - varchar(100)
'ABX123' -- STag - varchar(10)
)
INSERT INTO #Info
( HName ,
USERName ,
StartDate ,
ENdDAte ,
DESCRIPTION ,
STag
)
VALUES ( 'HN-FRNNN' , -- HName - varchar(100)
'ff' , -- USERName - varchar(10)
'2014-01-15 00:00:00.000' , -- StartDate - datetime
'2016-01-14 23:59:59.000' , -- ENdDAte - datetime
'Client' , -- DESCRIPTION - varchar(100)
'DCFTT1' -- STag - varchar(10)
)
INSERT INTO #Info
( HName ,
USERName ,
StartDate ,
ENdDAte ,
DESCRIPTION ,
STag
)
VALUES ( 'HN-FRNNN' , -- HName - varchar(100)
'ff' , -- USERName - varchar(10)
'2013-01-14 00:00:00.000' , -- StartDate - datetime
'2014-01-14 23:59:59.000' , -- ENdDAte - datetime
'Client' , -- DESCRIPTION - varchar(100)
'DCFTT1' -- STag - varchar(10)
)
INSERT INTO #Info
( HName ,
USERName ,
StartDate ,
ENdDAte ,
DESCRIPTION ,
STag
)
VALUES ( 'HN-FRNNN' , -- HName - varchar(100)
'ff' , -- USERName - varchar(10)
'2013-01-14 00:00:00.000' , -- StartDate - datetime
'2016-01-14 23:59:59.000' , -- ENdDAte - datetime
'Comp Care' , -- DESCRIPTION - varchar(100)
'DCFTT1' -- STag - varchar(10)
)
INSERT INTO #Info
( HName ,
USERName ,
StartDate ,
ENdDAte ,
DESCRIPTION ,
STag
)
VALUES ( 'HN-FRNNN' , -- HName - varchar(100)
'ff' , -- USERName - varchar(10)
'2013-01-14 00:00:00.000' , -- StartDate - datetime
'2014-01-14 23:59:59.000' , -- ENdDAte - datetime
'Next Business' , -- DESCRIPTION - varchar(100)
'DCFTT1' -- STag - varchar(10)
)
INSERT INTO #Info
( HName ,
USERName ,
StartDate ,
ENdDAte ,
DESCRIPTION ,
STag
)
VALUES ( 'HN-FRNNN' , -- HName - varchar(100)
'ff' , -- USERName - varchar(10)
'2014-01-15 00:00:00.000' , -- StartDate - datetime
'2016-01-14 23:59:59.000' , -- ENdDAte - datetime
'Next Business' , -- DESCRIPTION - varchar(100)
'DCFTT1' -- STag - varchar(10)
)
SELECT * FROM #Info
DROP TABLE #Info
Thanks,
PSB

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33506 More actions · Answer 1

select HName, USERname, max(EndDate) as max_enddate

from #Info

where Description = 'Client'

group by HName, USERname

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **