Rows duplicating for each person

Question

Rows duplicating for each person

b.grove

Old Hand

Points: 304
More actions
May 12, 2016 at 11:29 am

#310628

Hi,
I'm at a wall and reaching out for some ideas. I have put together SQL 2012 code to pull the date, version and where the documents where signed; of 3 documents (BL2, DB, MWH) document for each person who has accessed our warehouse and the access level. The rows duplicate for each version of the signed document. I need it to filter on the latest version of each of the 3 documents the person has to sign. A picture of the results are attached and the code is below.
*/Selects the columns from tables/*
SELECT DISTINCT
PersonData.PersonId,
Request.RequestId,
Request.RequestStartDateTime,
Request.RequestEndDateTime,
Request.RequestTimestamp as [Actual End Date],
*/This section identifies which document and places the date the document was signed in a column called Most Recent * Signed. It creates a column for the date signed, the version signed and the location where the document was signed/*
CASE WHEN dss.DocumentType = 'BL2'
THEN dss.SignedDate
ELSE 0
END AS [Most Recent BL2 Signed],
CASE WHEN dss.DocumentType = 'DB'
THEN dss.SignedDate
ELSE 0
END AS [Most Recent DB Signed],
CASE WHEN dss.DocumentType = 'MWH'
THEN dss.SignedDate
ELSE 0
END AS [Most Recent MWH Signed],
CASE WHEN dss.DocumentType = 'BL2'
THEN dss.SignedDocumentVersion
ELSE 0
END AS [Most Recent BL2 Version Signed],
CASE WHEN dss.DocumentType = 'DB'
THEN dss.SignedDocumentVersion
ELSE 0
END AS [Most Recent DB Version Signed],
CASE WHEN dss.DocumentType = 'MWH'
THEN dss.SignedDocumentVersion
ELSE 0
END AS [Most Recent MWH Version Signed],
CASE WHEN dss.DocumentType = 'BL2' AND dss.DocumentSignMedium IN ('Electronic', 'AutoSign')
THEN 'Digital'
END AS [Most Recent BL2 Location Signed],
CASE WHEN dss.DocumentType = 'DB' AND dss.DocumentSignMedium IN ('Electronic', 'AutoSign')
THEN 'Digital'
END AS [Most Recent DB Location Signed],
CASE WHEN dss.DocumentType = 'MWH' AND dss.DocumentSignMedium IN ('Electronic', 'AutoSign')
THEN 'Digital'
END AS [Most Recent MWH Location Signed],
*/This section tells us what access level or key group the person has and creates a column called 'Access Level'/*
CASE WHEN ah.HardkeyGroupId IN ('616595', '616597', '616598', '616601', '616639', '617343', '628123')
THEN hkg.HardKeyGroupName
END AS [Access Level],
*/This section renames the results in the column 'Access Level' to either Access Level or Hard Key. Many of the access level and key group names are hard to understand. The results are placed in a column called 'Access Level Type/*
CASE WHEN ah.AccessLevelId in ('646901','646968','646971','646973','646976','647357','647446','668221')
THEN 'Access Level'
WHEN ah.HardkeyGroupId in ('616595', '616597', '616598', '616601', '616639', '617343', '628123')
THEN 'Hard Key'
END AS [Access Level Type]
*/This section is the Request Table and all the tables that have a piece of information we need for the report*/
FROM
dbo.Request req
INNER JOIN dbo.People_Request preq
ON req.RequestId = dbo.People_Request.RequestID
INNER JOIN dbo.PersonData pd
ON dbo.People_Request.PersonDataId = pd.PersonId
INNER JOIN dbo.ApprovalHistory ah
ON req.RequestId = dbo.ApprovalHistory.RequestId
INNER JOIN dbo.DocumentSignedStatus dss
ON pd.PersonId = dss.PersonDataID
INNER JOIN dbo.AccessLevel al
ON dbo.ApprovalHistory.AccessLevelId = dbo.AccessLevel.AccessLevelId
INNER JOIN dbo.HardKeyGroup hkg
ON dbo.ApprovalHistory.HardKeyGroupID = dbo.HardKeyGroup.HardKeyGroupId
INNER JOIN dbo.Company c
ON pd.CompanyId = c.CompanyId
WHERE
req.LocationId IN ('80','88');

Viewing 15 posts - 1 through 15 (of 22 total)

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

Hello and welcome to SSC. Please note the link in my signature line for the best way to get help here. If you can include the DDL for the tables, some sample data and what the desired result should be I'm sure we can get this sorted out.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

pietlinden SSC Guru Points: 63503 More actions · Answer 2

"I need it to filter on the latest version of each of the 3 documents the person has to sign."

but there's no MAX(Date) anywhere... how are you returning the latest version of each document?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Thought I'd reformat your code some and replace the 3-part naming convention you used with 2-part naming using table aliases. This is important as Microsoft has deprecated 3-part and 4-part naming conventions in the SELECT list. I also thought I would put in some consistency in your coding instead of having some upper case, some proper case. I also went with all square brackets for your column aliases. Last, but not least, I terminated your query with a semicolon. Really should get in this habit as Microsoft has also deprecated NOT terminating statements with a semicolon.

Here is the reformatted code:

SELECT DISTINCT

pd.PersonId,

req.RequestId,

req.RequestStartDateTime,

req.RequestEndDateTime,

req.RequestTimestamp AS [Actual End Date],

CASE WHEN dss.DocumentType = 'BL2'

THEN dss.SignedDate

ELSE 0

END AS [Most Recent BL2 Signed],

CASE WHEN dss.DocumentType = 'DB'

THEN dss.SignedDate

ELSE 0

END AS [Most Recent DB Signed],

CASE WHEN dss.DocumentType = 'MWH'

THEN dss.SignedDate

ELSE 0

END AS [Most Recent MWH Signed],

CASE WHEN dss.DocumentType = 'BL2'

THEN dss.SignedDocumentVersion

ELSE 0

END AS [Most Recent BL2 Version Signed],

CASE WHEN dss.DocumentType = 'DB'

THEN dss.SignedDocumentVersion

ELSE 0

END AS [Most Recent DB Version Signed],

CASE WHEN dss.DocumentType = 'MWH'

THEN dss.SignedDocumentVersion

ELSE 0

END AS [Most Recent MWH Version Signed],

CASE WHEN dss.DocumentType = 'NDA' AND dss.DocumentSignMedium IN ('Electronic', 'AutoSign')

THEN 'Digital'

END AS [Most Recent BL2 Location Signed],

CASE WHEN dss.DocumentType = 'DB' AND dss.DocumentSignMedium IN ('Electronic', 'AutoSign')

THEN 'Digital'

END AS [Most Recent DB Location Signed],

CASE WHEN dss.DocumentType = 'MWH' AND dss.DocumentSignMedium IN ('Electronic', 'AutoSign')

THEN 'Digital'

END AS [Most Recent MWH Location Signed],

CASE WHEN al.AccessLevelName

WHEN ah.HardkeyGroupId IN ('616595', '616597', '616598', '616601', '616639', '617343', '628123')

THEN hkg.HardKeyGroupName

END AS [Access Level],

CASE WHEN ah.AccessLevelId in ('646901','646968','646971','646973','646976','647357','647446','668221')

THEN 'Access Level'

WHEN ah.HardkeyGroupId in ('616595', '616597', '616598', '616601', '616639', '617343', '628123')

THEN 'Hard Key'

END AS [Access Level Type]

FROM

dbo.Request req

INNER JOIN dbo.People_Request preq

ON req.RequestId = dbo.People_Request.RequestID

INNER JOIN dbo.PersonData pd

ON dbo.People_Request.PersonDataId = pd.PersonId

INNER JOIN dbo.ApprovalHistory ah

ON req.RequestId = dbo.ApprovalHistory.RequestId

INNER JOIN dbo.DocumentSignedStatus dss

ON pd.PersonId = dss.PersonDataID

INNER JOIN dbo.AccessLevel al

ON dbo.ApprovalHistory.AccessLevelId = dbo.AccessLevel.AccessLevelId

INNER JOIN dbo.HardKeyGroup hkg

ON dbo.ApprovalHistory.HardKeyGroupID = dbo.HardKeyGroup.HardKeyGroupId

INNER JOIN dbo.Company c

ON pd.CompanyId = c.CompanyId

WHERE

req.LocationId IN ('80','88');

Eric M Russell SSC Guru Points: 125561 More actions · Answer 4

It's standard practice in a star schema modeled data warehouse, where rows are versioned in what's called a 'slowly changing dimension' or 'shapshot' fashion, to have a column indicating (ie: IsCurrent = 1) which row contains the most recent historical version of an entity or fact. However, if all you have to go by are dates, then you'll have to resort to some form of inline ranking and then filter the resultset to contain only those rows top 1 ranked rows within each group.

Lookup usage of the DENSE_RANK() or ROW_NUMBER() functions.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

b.grove Old Hand Points: 304 More actions · Answer 5

I would love to use a Max() function but I have 6 statements I need to use the Max() function on. When I use it on just one of the statements

CASE WHEN DocumentSignedstatus.DocumentType = 'BL2' then Max(DocumentSignedStatus.SignedDate) else 0 end as [Most Recent NDA Signed]

I get the following error message

"Msg 8120, Level 16, State 1, Line 2

Column 'PersonData.BadgeNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

SQL seems to want me to put all of my columns names in the GROUP BY clause. I'm not sure how to get around this.

My select statement is:

PersonData.BadgeNumber,

PersonData.FirstName,

PersonData.LastName,

PersonData.PersonEmail as [Alias/Email],

Request.EscortAlias,

Company.CompanyName,

PersonData.ManagerEmail,

Request.RequestId,

Request.RequestStartDateTime,

Request.RequestEndDateTime,

Request.RequestTimestamp as [Actual End Date],

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

b.grove (5/12/2016)
I would love to use a Max() function but I have 6 statements I need to use the Max() function on. When I use it on just one of the statements
CASE WHEN DocumentSignedstatus.DocumentType = 'BL2' then Max(DocumentSignedStatus.SignedDate) else 0 end as [Most Recent NDA Signed]
I get the following error message
"Msg 8120, Level 16, State 1, Line 2
Column 'PersonData.BadgeNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
SQL seems to want me to put all of my columns names in the GROUP BY clause. I'm not sure how to get around this.
My select statement is:
PersonData.BadgeNumber,
PersonData.FirstName,
PersonData.LastName,
PersonData.PersonEmail as [Alias/Email],
Request.EscortAlias,
Company.CompanyName,
PersonData.ManagerEmail,
Request.RequestId,
Request.RequestStartDateTime,
Request.RequestEndDateTime,
Request.RequestTimestamp as [Actual End Date],

Since we can't see what you see you need to help us more. The DDL (CREATE TABLE statement) for the table(s) involved (can be shortened to just the columns required for the query); sample data, not PRODUCTION data, (as INSERT INTO statements) that is representative of your problem domain for the table(s) involved; expected results based on the sample data.

Test all DDL in an empty database prior to posting to be sure it all works.

b.grove Old Hand Points: 304 More actions · Answer 7

Hi, the table that holds the document information has a RevisionNO column.

The range of revisions are from 1 to 27. Some people have 1. Some have 5. Some have 27 revisions

What would the syntax look like to see the LAST revision?

Would it be apart of the where statement?

I did try the LAST() but did not work.

R.P.Rozema SSChampion Points: 12317 More actions · Answer 8

Lynn Pettis (5/12/2016)
Thought I'd reformat your code some and replace the 3-part naming convention you used with 2-part naming using table aliases. This is important as Microsoft has deprecated 3-part and 4-part naming conventions in the SELECT list. I also thought I would put in some consistency in your coding instead of having some upper case, some proper case. I also went with all square brackets for your column aliases. Last, but not least, I terminated your query with a semicolon. Really should get in this habit as Microsoft has also deprecated NOT terminating statements with a semicolon.
Here is the reformatted code:
SELECT DISTINCT
pd.PersonId,
req.RequestId,
req.RequestStartDateTime,
req.RequestEndDateTime,
req.RequestTimestamp AS [Actual End Date],
CASE WHEN dss.DocumentType = 'BL2'
THEN dss.SignedDate
ELSE 0
END AS [Most Recent BL2 Signed],
CASE WHEN dss.DocumentType = 'DB'
THEN dss.SignedDate
ELSE 0
END AS [Most Recent DB Signed],
CASE WHEN dss.DocumentType = 'MWH'
THEN dss.SignedDate
ELSE 0
END AS [Most Recent MWH Signed],
CASE WHEN dss.DocumentType = 'BL2'
THEN dss.SignedDocumentVersion
ELSE 0
END AS [Most Recent BL2 Version Signed],
CASE WHEN dss.DocumentType = 'DB'
THEN dss.SignedDocumentVersion
ELSE 0
END AS [Most Recent DB Version Signed],
CASE WHEN dss.DocumentType = 'MWH'
THEN dss.SignedDocumentVersion
ELSE 0
END AS [Most Recent MWH Version Signed],
CASE WHEN dss.DocumentType = 'NDA' AND dss.DocumentSignMedium IN ('Electronic', 'AutoSign')
THEN 'Digital'
END AS [Most Recent BL2 Location Signed],
CASE WHEN dss.DocumentType = 'DB' AND dss.DocumentSignMedium IN ('Electronic', 'AutoSign')
THEN 'Digital'
END AS [Most Recent DB Location Signed],
CASE WHEN dss.DocumentType = 'MWH' AND dss.DocumentSignMedium IN ('Electronic', 'AutoSign')
THEN 'Digital'
END AS [Most Recent MWH Location Signed],
CASE WHEN al.AccessLevelName
WHEN ah.HardkeyGroupId IN ('616595', '616597', '616598', '616601', '616639', '617343', '628123')
THEN hkg.HardKeyGroupName
END AS [Access Level],
CASE WHEN ah.AccessLevelId in ('646901','646968','646971','646973','646976','647357','647446','668221')
THEN 'Access Level'
WHEN ah.HardkeyGroupId in ('616595', '616597', '616598', '616601', '616639', '617343', '628123')
THEN 'Hard Key'
END AS [Access Level Type]
FROM
dbo.Request req
INNER JOIN dbo.People_Request preq
ON req.RequestId = dbo.People_Request.RequestID
INNER JOIN dbo.PersonData pd
ON dbo.People_Request.PersonDataId = pd.PersonId
INNER JOIN dbo.ApprovalHistory ah
ON req.RequestId = dbo.ApprovalHistory.RequestId
INNER JOIN dbo.DocumentSignedStatus dss
ON pd.PersonId = dss.PersonDataID
INNER JOIN dbo.AccessLevel al
ON dbo.ApprovalHistory.AccessLevelId = dbo.AccessLevel.AccessLevelId
INNER JOIN dbo.HardKeyGroup hkg
ON dbo.ApprovalHistory.HardKeyGroupID = dbo.HardKeyGroup.HardKeyGroupId
INNER JOIN dbo.Company c
ON pd.CompanyId = c.CompanyId
WHERE
req.LocationId IN ('80','88');

You forgot some in the join clauses :hehe:

SELECT DISTINCT

pd.PersonId,

req.RequestId,

req.RequestStartDateTime,

req.RequestEndDateTime,

req.RequestTimestamp AS [Actual End Date],

CASE WHEN dss.DocumentType = 'BL2'

THEN dss.SignedDate

ELSE 0

END AS [Most Recent BL2 Signed],

CASE WHEN dss.DocumentType = 'DB'

THEN dss.SignedDate

ELSE 0

END AS [Most Recent DB Signed],

CASE WHEN dss.DocumentType = 'MWH'

THEN dss.SignedDate

ELSE 0

END AS [Most Recent MWH Signed],

CASE WHEN dss.DocumentType = 'BL2'

THEN dss.SignedDocumentVersion

ELSE 0

END AS [Most Recent BL2 Version Signed],

CASE WHEN dss.DocumentType = 'DB'

THEN dss.SignedDocumentVersion

ELSE 0

END AS [Most Recent DB Version Signed],

CASE WHEN dss.DocumentType = 'MWH'

THEN dss.SignedDocumentVersion

ELSE 0

END AS [Most Recent MWH Version Signed],

CASE WHEN dss.DocumentType = 'NDA' AND dss.DocumentSignMedium IN ('Electronic', 'AutoSign')

THEN 'Digital'

END AS [Most Recent BL2 Location Signed],

CASE WHEN dss.DocumentType = 'DB' AND dss.DocumentSignMedium IN ('Electronic', 'AutoSign')

THEN 'Digital'

END AS [Most Recent DB Location Signed],

CASE WHEN dss.DocumentType = 'MWH' AND dss.DocumentSignMedium IN ('Electronic', 'AutoSign')

THEN 'Digital'

END AS [Most Recent MWH Location Signed],

CASE WHEN al.AccessLevelName

WHEN ah.HardkeyGroupId IN ('616595', '616597', '616598', '616601', '616639', '617343', '628123')

THEN hkg.HardKeyGroupName

END AS [Access Level],

CASE WHEN ah.AccessLevelId in ('646901','646968','646971','646973','646976','647357','647446','668221')

THEN 'Access Level'

WHEN ah.HardkeyGroupId in ('616595', '616597', '616598', '616601', '616639', '617343', '628123')

THEN 'Hard Key'

END AS [Access Level Type]

FROM

dbo.Request req

INNER JOIN dbo.People_Request preq

ON req.RequestId = preq.RequestID

INNER JOIN dbo.PersonData pd

ON preq.PersonDataId = pd.PersonId

INNER JOIN dbo.ApprovalHistory ah

ON req.RequestId = ah.RequestId

INNER JOIN dbo.DocumentSignedStatus dss

ON pd.PersonId = dss.PersonDataID

INNER JOIN dbo.AccessLevel al

ON ah.AccessLevelId = al.AccessLevelId

INNER JOIN dbo.HardKeyGroup hkg

ON ah.HardKeyGroupID = hkg.HardKeyGroupId

INNER JOIN dbo.Company c

ON pd.CompanyId = c.CompanyId

WHERE

req.LocationId IN ('80','88');

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

R.P.Rozema SSChampion Points: 12317 More actions · Answer 9

Hello b.grove,

Which of those tables are 'the 3 document tables' you refer to? And what are the column names that indicate their version?

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

drew.allen SSC Guru Points: 76985 More actions · Answer 10

b.grove (5/12/2016)
Hi, the table that holds the document information has a RevisionNO column.
The range of revisions are from 1 to 27. Some people have 1. Some have 5. Some have 27 revisions
What would the syntax look like to see the LAST revision?
Would it be apart of the where statement?
I did try the LAST() but did not work.

The standard practice is to use a CTE with a ROW_NUMBER() with an ORDER BY specified in such a way that the desired record is first within each partition and then select the record with the row number = 1.

In some cases, a CROSS APPLY with a TOP(1) will also be very efficient, but it doesn't sound like your data will meet the conditions where this will be the case.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

R.P.Rozema (5/13/2016)
Lynn Pettis (5/12/2016)
Thought I'd reformat your code some and replace the 3-part naming convention you used with 2-part naming using table aliases. This is important as Microsoft has deprecated 3-part and 4-part naming conventions in the SELECT list. I also thought I would put in some consistency in your coding instead of having some upper case, some proper case. I also went with all square brackets for your column aliases. Last, but not least, I terminated your query with a semicolon. Really should get in this habit as Microsoft has also deprecated NOT terminating statements with a semicolon.
Here is the reformatted code:
SELECT DISTINCT
pd.PersonId,
req.RequestId,
req.RequestStartDateTime,
req.RequestEndDateTime,
req.RequestTimestamp AS [Actual End Date],
CASE WHEN dss.DocumentType = 'BL2'
THEN dss.SignedDate
ELSE 0
END AS [Most Recent BL2 Signed],
CASE WHEN dss.DocumentType = 'DB'
THEN dss.SignedDate
ELSE 0
END AS [Most Recent DB Signed],
CASE WHEN dss.DocumentType = 'MWH'
THEN dss.SignedDate
ELSE 0
END AS [Most Recent MWH Signed],
CASE WHEN dss.DocumentType = 'BL2'
THEN dss.SignedDocumentVersion
ELSE 0
END AS [Most Recent BL2 Version Signed],
CASE WHEN dss.DocumentType = 'DB'
THEN dss.SignedDocumentVersion
ELSE 0
END AS [Most Recent DB Version Signed],
CASE WHEN dss.DocumentType = 'MWH'
THEN dss.SignedDocumentVersion
ELSE 0
END AS [Most Recent MWH Version Signed],
CASE WHEN dss.DocumentType = 'NDA' AND dss.DocumentSignMedium IN ('Electronic', 'AutoSign')
THEN 'Digital'
END AS [Most Recent BL2 Location Signed],
CASE WHEN dss.DocumentType = 'DB' AND dss.DocumentSignMedium IN ('Electronic', 'AutoSign')
THEN 'Digital'
END AS [Most Recent DB Location Signed],
CASE WHEN dss.DocumentType = 'MWH' AND dss.DocumentSignMedium IN ('Electronic', 'AutoSign')
THEN 'Digital'
END AS [Most Recent MWH Location Signed],
CASE WHEN al.AccessLevelName
WHEN ah.HardkeyGroupId IN ('616595', '616597', '616598', '616601', '616639', '617343', '628123')
THEN hkg.HardKeyGroupName
END AS [Access Level],
CASE WHEN ah.AccessLevelId in ('646901','646968','646971','646973','646976','647357','647446','668221')
THEN 'Access Level'
WHEN ah.HardkeyGroupId in ('616595', '616597', '616598', '616601', '616639', '617343', '628123')
THEN 'Hard Key'
END AS [Access Level Type]
FROM
dbo.Request req
INNER JOIN dbo.People_Request preq
ON req.RequestId = dbo.People_Request.RequestID
INNER JOIN dbo.PersonData pd
ON dbo.People_Request.PersonDataId = pd.PersonId
INNER JOIN dbo.ApprovalHistory ah
ON req.RequestId = dbo.ApprovalHistory.RequestId
INNER JOIN dbo.DocumentSignedStatus dss
ON pd.PersonId = dss.PersonDataID
INNER JOIN dbo.AccessLevel al
ON dbo.ApprovalHistory.AccessLevelId = dbo.AccessLevel.AccessLevelId
INNER JOIN dbo.HardKeyGroup hkg
ON dbo.ApprovalHistory.HardKeyGroupID = dbo.HardKeyGroup.HardKeyGroupId
INNER JOIN dbo.Company c
ON pd.CompanyId = c.CompanyId
WHERE
req.LocationId IN ('80','88');
You forgot some in the join clauses :hehe:
SELECT DISTINCT
pd.PersonId,
req.RequestId,
req.RequestStartDateTime,
req.RequestEndDateTime,
req.RequestTimestamp AS [Actual End Date],
CASE WHEN dss.DocumentType = 'BL2'
THEN dss.SignedDate
ELSE 0
END AS [Most Recent BL2 Signed],
CASE WHEN dss.DocumentType = 'DB'
THEN dss.SignedDate
ELSE 0
END AS [Most Recent DB Signed],
CASE WHEN dss.DocumentType = 'MWH'
THEN dss.SignedDate
ELSE 0
END AS [Most Recent MWH Signed],
CASE WHEN dss.DocumentType = 'BL2'
THEN dss.SignedDocumentVersion
ELSE 0
END AS [Most Recent BL2 Version Signed],
CASE WHEN dss.DocumentType = 'DB'
THEN dss.SignedDocumentVersion
ELSE 0
END AS [Most Recent DB Version Signed],
CASE WHEN dss.DocumentType = 'MWH'
THEN dss.SignedDocumentVersion
ELSE 0
END AS [Most Recent MWH Version Signed],
CASE WHEN dss.DocumentType = 'NDA' AND dss.DocumentSignMedium IN ('Electronic', 'AutoSign')
THEN 'Digital'
END AS [Most Recent BL2 Location Signed],
CASE WHEN dss.DocumentType = 'DB' AND dss.DocumentSignMedium IN ('Electronic', 'AutoSign')
THEN 'Digital'
END AS [Most Recent DB Location Signed],
CASE WHEN dss.DocumentType = 'MWH' AND dss.DocumentSignMedium IN ('Electronic', 'AutoSign')
THEN 'Digital'
END AS [Most Recent MWH Location Signed],
CASE WHEN al.AccessLevelName
WHEN ah.HardkeyGroupId IN ('616595', '616597', '616598', '616601', '616639', '617343', '628123')
THEN hkg.HardKeyGroupName
END AS [Access Level],
CASE WHEN ah.AccessLevelId in ('646901','646968','646971','646973','646976','647357','647446','668221')
THEN 'Access Level'
WHEN ah.HardkeyGroupId in ('616595', '616597', '616598', '616601', '616639', '617343', '628123')
THEN 'Hard Key'
END AS [Access Level Type]
FROM
dbo.Request req
INNER JOIN dbo.People_Request preq
ON req.RequestId = preq.RequestID
INNER JOIN dbo.PersonData pd
ON preq.PersonDataId = pd.PersonId
INNER JOIN dbo.ApprovalHistory ah
ON req.RequestId = ah.RequestId
INNER JOIN dbo.DocumentSignedStatus dss
ON pd.PersonId = dss.PersonDataID
INNER JOIN dbo.AccessLevel al
ON ah.AccessLevelId = al.AccessLevelId
INNER JOIN dbo.HardKeyGroup hkg
ON ah.HardKeyGroupID = hkg.HardKeyGroupId
INNER JOIN dbo.Company c
ON pd.CompanyId = c.CompanyId
WHERE
req.LocationId IN ('80','88');

Must have fat fingered a couple of find and replaces and didn't catch it, thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

Once again with feeling:

Since we can't see what you see you need to help us more. The DDL (CREATE TABLE statement) for the table(s) involved (can be shortened to just the columns required for the query); sample data, not PRODUCTION data, (as INSERT INTO statements) that is representative of your problem domain for the table(s) involved; expected results based on the sample data.

Test all DDL in an empty database prior to posting to be sure it all works.

b.grove Old Hand Points: 304 More actions · Answer 13

Hi, Thank you for helping. I really need another pair of eyes to look at this and offer suggestions. I have tried everything, code wise to get this to work.

I'm not sure how to display on this site; the table columns, so I will separate them with "|"

My initial submission has an attachment showing the results of my code.

Here are the DOC table columns....

1037586 | 926903 | BL2 | 2015-10-26 | 1 | AutoSign | 1

1041206 | 926903 | MWH | 2016-01-18 | 2 | Electronic | 3

1037591 | 926903 | DB | 2016-01-18 | 9 | Electronic | 3

Here are the Request table columns...

1148737 | 2015-11-16 | 9999-12-31 | 2015-11-16 | Approved | 3 | 7 | 88

Here are the Person table columns...

926903 | 12345 | John | Doe | J.Doe@comcast.net | BoxesRus | 28

Here are the ApprovalHistory table columns....

ApprovalHistoryId | RequestId | AccessLevelId | HardKeyGroupId

1480764 |1148737 | -1 | 616597

1480763 | 1148737 |646968 | -1

Here are the AccessLevel table columns

AccessLevelId | AccessLevelName

-1 | NoAccessLevel

616597 | Site Services

646968 | HBHKGTK5 Site Services

Here are the Company table columns

CompanyId | Company Name

456 | BoxesRus

Here is the connecting table between the Request Table and Person table called People_Request table

RequestID | PersondataID

1148737 | 926903

Here is the Request table Columns

b.grove Old Hand Points: 304 More actions · Answer 14

Hi Drew,

Thanks for the ideas. I just posted the tables I'm using and how the columns are shown in each table and the some data in each column. Maybe that will offer some fuel for more ideas.

Thank you for you help