March 4, 2015 at 6:00 am
I'm looking to identify the people who visited an office more than once and the dates that they visited. For example I have a table that looks like the following:
Name |Office Visit Date
-----------------------------
John| 2002-01-23
Mary| 2003-02-04
Fred| 2002-11-02
Jane| 2012-06-05
John| 2003-07-12
Mary| 2004-10-12
Fred| 2011-03-20
John| 2009-02-14
I want to display the following:
Name |Office Visit 1 |Office Visit 2 |Office Visit 3
----------------------------------------------------------------------
John|2002-01-23| 2003-07-12| 2009-02-14
Mary| 2003-02-04| 2004-10-12|
Fred| 2002-11-02| 2011-03-20
Note that Jane does not show up because she has only visited the office once and everybody else has visited the office at least two times.
March 4, 2015 at 6:48 am
Quick cross-tab solution, should be enough to get you passed this hurdle
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.TBL_SAMPLE_VISIT') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_VISIT;
CREATE TABLE dbo.TBL_SAMPLE_VISIT
(
SV_NAME VARCHAR(50) NOT NULL
,SV_DATE DATE NOT NULL
,CONSTRAINT PK_DBO_TBL_SAMPLE_VISIT_SV_NAME_SV_DATE PRIMARY KEY CLUSTERED (SV_NAME ASC,SV_DATE ASC)
);
INSERT INTO dbo.TBL_SAMPLE_VISIT (SV_NAME,SV_DATE)
VALUES
('John','2002-01-23')
,('Mary','2003-02-04')
,('Fred','2002-11-02')
,('Jane','2012-06-05')
,('John','2003-07-12')
,('Mary','2004-10-12')
,('Fred','2011-03-20')
,('John','2009-02-14')
;
;WITH BASE_DATA AS
(
SELECT
SV.SV_NAME
,SV.SV_DATE
,ROW_NUMBER() OVER
(
PARTITION BY SV.SV_NAME
ORDER BY SV.SV_DATE
) AS SV_RID
,COUNT(*) OVER
(
PARTITION BY SV.SV_NAME
) AS SV_CNT
FROM dbo.TBL_SAMPLE_VISIT SV
)
SELECT
BD.SV_NAME
,MAX(CASE WHEN BD.SV_RID = 1 THEN BD.SV_DATE END ) AS [Office Visit 1]
,MAX(CASE WHEN BD.SV_RID = 2 THEN BD.SV_DATE END ) AS [Office Visit 2]
,MAX(CASE WHEN BD.SV_RID = 3 THEN BD.SV_DATE END ) AS [Office Visit 3]
,MAX(CASE WHEN BD.SV_RID = 4 THEN BD.SV_DATE END ) AS [Office Visit 4]
,MAX(CASE WHEN BD.SV_RID = 5 THEN BD.SV_DATE END ) AS [Office Visit 5]
FROM BASE_DATA BD
WHERE BD.SV_CNT > 1
GROUP BY BD.SV_NAME,BD.SV_CNT
ORDER BY BD.SV_CNT DESC;
Results
SV_NAME Office Visit 1 Office Visit 2 Office Visit 3 Office Visit 4 Office Visit 5
-------- -------------- -------------- -------------- -------------- --------------
John 2002-01-23 2003-07-12 2009-02-14 NULL NULL
Fred 2002-11-02 2011-03-20 NULL NULL NULL
Mary 2003-02-04 2004-10-12 NULL NULL NULL
March 4, 2015 at 7:11 am
Thanks a bunch, that is just what I needed
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply