Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Filter out records from table Expand / Collapse
Author
Message
Posted Friday, February 7, 2014 5:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 4, 2014 12:54 PM
Points: 296, Visits: 1,057
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
Post #1539114
Posted Friday, February 7, 2014 6:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:37 AM
Points: 2,290, Visits: 2,749
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’! **
Post #1539121
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse