May 13, 2016 at 9:15 am
b.grove (5/13/2016)
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....
DocumentSignedStatusId | PersonDataID | DocumentType | Signed Date | SignedDocumentVersion | DocumentSignMedium | RevisionNo
1037586 | 926903 | BL2 | 2015-10-26 | 1 | AutoSign | 1
1041206 | 926903 | MWH | 2016-01-18 | 2 | Electronic | 3
1037591 | 902603 | DB | 2016-01-18 | 9 | Electronic | 3
Here are the Request table columns...
RequestId | RequestStartDate | RequestEndDate | RequestTimeStamp | State | RequestTypeId | RevisionNO |LocationId
1148737 | 2015-11-16 | 9999-12-31 | 2015-11-16 | Approved | 3 | 7 | 88
Here are the Person table columns...
PersonId | BadgeNumber | FirstName | LastName | PersonEmail | CompanyId |RevisionNo |
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 Request table Columns
Okay, if you can't provide this:
CREATE TABEL dbo.MyTable (
col1 int,
col2 varchar(16),
...);
and this:
INSERT INTO dbo.MyTable (col1, col2, ...)
VALUES (1,'aa',...), (2,'bb',...), (3,'cc',...),....;
I really can't help since I am a visual problem solver and need to see a starting point and the expected ending point to help solve the problem.
May 13, 2016 at 9:16 am
I understand but I don't have create table access only readonly. So I don't have the insert code you are looking for. I did just post a representation of each table and the columns in each table.
May 13, 2016 at 9:23 am
b.grove (5/13/2016)
I understand but I don't have create table access only readonly. So I don't have the insert code you are looking for. I did just post a representation of each table and the columns in each table.
go to this site
http://www.convertcsv.com/csv-to-sql.htm
copy and paste .....voila:
CREATE TABLE DOC(
DocumentSignedStatusId INTEGER NOT NULL
,PersonDataID INTEGER NOT NULL
,DocumentType VARCHAR(11) NOT NULL
,Signed_Date DATE NOT NULL
,SignedDocumentVersion INTEGER NOT NULL
,DocumentSignMedium VARCHAR(25) NOT NULL
,RevisionNo INTEGER NOT NULL
);
INSERT INTO DOC(DocumentSignedStatusId,PersonDataID,DocumentType,Signed_Date,SignedDocumentVersion,DocumentSignMedium,RevisionNo) VALUES
(1037586,926903,'BL2','2015-10-26',1,'AutoSign',1)
,(1041206,926903,'MWH','2016-01-18',2,'Electronic',3)
,(1037591,902603,'DB','2016-01-18',9,'Electronic',3);
select * from DOC
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 13, 2016 at 9:38 am
I'm confused. I already have a document table with the exact same data in it called Dcoument Signed
So why create another document table duplicating the existing Document Signed table?
May 13, 2016 at 9:52 am
b.grove (5/13/2016)
I'm confused. I already have a document table with the exact same data in it called Dcoument SignedSo why create another document table duplicating the existing Document Signed table?
Not asking you to create duplicate tables, asking you to provide us with the DDL for the tables, sample data for the tables, expected results based on the sample data so that we can setup a sandbox database that reflects your environment so we can work with your code to generate the output you are looking for but are having problems developing.
May 13, 2016 at 9:53 am
b.grove (5/13/2016)
I'm confused. I already have a document table with the exact same data in it called Dcoument SignedSo why create another document table duplicating the existing Document Signed table?
the post was not for you to create on your system, but so that you provide the necessary info for us to replicate.
please read this article that explains far better than I
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 17, 2016 at 4:32 am
I have some idea of where you're probably going wrong, but indeed, having your tables in a test database on my machine would make it so much easier to show it to you. So please do the following: for each of the tables used in your query, right click the tabel name in SSMS' object explorer and select 'script table as' -> 'Create To' -> 'clipboard' and paste all of them in one new query window. Then post that script in this topic (enclosed in [ code="sql"]code here[ /code], but without the space after [) so that we can recreate your tables in our machines.
I think you've got multiple problems in this query, the first you have identified yourself:
1 - you need to find the "latest version" per document, but also
2 - you've got multiple documents per request (you say there are 3, but I see 4 different designators in your example query: BL2, DB, MWH and NDA)
You'll need to address problem 2 first, then finding the latest version for each of the documents will be a next task:
One of the tables you've listed probably has 3 (or 4) rows per request, as suggested by your "each of the 3 documents the person has to sign". You will need 3 instances of that particular table in your query. This is why it is important to use the correct notation. Just for the case of this example, I assume it is the dbo.DocumentSignedStatus that has multiple rows per request. I'm guessing this is the table, as this has a column 'DocumentType' that seems to be used as a designator. If my guess is wrong you would need to use some other table, this is just to demonstrate how to retrieve the 4 different rows from the same table dbo.DocumentSignedStatus in one query:
select
pd.PersonID,
dssBL2.SignedDate AS [Most Recent BL2 Signed],
dssDB.dss.SignedDate AS [Most Recent DB Signed],
dssMWH.SignedDate AS [Most Recent MWH Signed],
dssBL2.SignedDocumentVersion AS [Most Recent BL2 Version Signed],
dssDB.SignedDocumentVersion AS [Most Recent DB Version Signed],
dssMWH.SignedDocumentVersion AS [Most Recent MWH Version Signed],
case when dssNDA.DocumentSignMedium IN ('Electronic', 'AutoSign') then 'Digital' else 'Not digital' end AS [Most Recent BL2 Location Signed],
case when dssDB.DocumentSignMedium IN ('Electronic', 'AutoSign') then 'Digital' else 'Not digital' end AS [Most Recent DB Location Signed],
case when dssMWH.DocumentSignMedium IN ('Electronic', 'AutoSign') then 'Digital' else 'Not digital' end AS [Most Recent MWH Location Signed]
from dbo.PersonData pd
left outer join dbo.DocumentSignedStatus dssBL2 ON (dssBL2.PersonDataID = pd.PersonId and dssBL2.DocumentType = 'BL2')
left outer join dbo.DocumentSignedStatus dssDB ON (dssDB.PersonDataID = pd.PersonId and dssDB.DocumentType = 'DB')
left outer join dbo.DocumentSignedStatus dssMWH ON (dssMWH.PersonDataID = pd.PersonId and dssMWH.DocumentType = 'MWH')
left outer join dbo.DocumentSignedStatus dssNDA ON (dssNDA.PersonDataID = pd.PersonId and dssNDA.DocumentType = 'NDA')
I'm using left outer joins to avoid excluding any requests for which 1 or more documents are missing, cause that would mean we would be ignoring the documents that are available for the request.
May 17, 2016 at 11:31 am
Hi R.P., here are all the tables I'm using but I'm not using all of the columns as you can tell.
Also for problem #2 change acronym 'NDA' to BL2. So there should only be 3 documents. Thank you again for your wisdom. Right now I'm looking at last_Value but have not gotten the code to work yet.
USE [DCAT_ReadOnly]
GO
/****** Object: Table [dbo].[DocumentSignedStatus] Script Date: 5/17/2016 10:19:10 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DocumentSignedStatus](
[DocumentSignedStatusId] [bigint] IDENTITY(1,1) NOT NULL,
[LocationId] [bigint] NOT NULL,
[PersonDataId] [bigint] NOT NULL,
[CheckAndSetSendMail] [smallint] NULL,
[SendEmail] [smallint] NULL,
[DocumentSignedAuditStr] [nvarchar](256) NULL,
[SignMarkedBy] [nvarchar](100) NULL,
[DocumentType] [nvarchar](50) NULL,
[SignedDate] [datetime] NULL,
[SignedDocumentVersion] [nvarchar](256) NULL,
[DocumentSignMedium] [nvarchar](100) NULL,
[State] [nvarchar](50) NOT NULL,
[Reason] [nvarchar](50) NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[CreatedBy] [nvarchar](100) NOT NULL,
[LastModifiedDate] [datetime] NOT NULL,
[LastModifiedBy] [nvarchar](100) NOT NULL,
[RevisionNo] [int] NOT NULL,
[ReSignDocumentComments] [nvarchar](max) NULL,
CONSTRAINT [PK_DocumentSignedStatus] PRIMARY KEY CLUSTERED
(
[DocumentSignedStatusId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[DocumentSignedStatus] WITH CHECK ADD CONSTRAINT [FK_DocumentSignedStatus_DocumentSignedStatus] FOREIGN KEY([LocationId])
REFERENCES [dbo].[Location] ([LocationId])
GO
ALTER TABLE [dbo].[DocumentSignedStatus] CHECK CONSTRAINT [FK_DocumentSignedStatus_DocumentSignedStatus]
GO
ALTER TABLE [dbo].[DocumentSignedStatus] WITH CHECK ADD CONSTRAINT [FK_DocumentSignedStatus_PersonData] FOREIGN KEY([PersonDataId])
REFERENCES [dbo].[PersonData] ([PersonId])
GO
ALTER TABLE [dbo].[DocumentSignedStatus] CHECK CONSTRAINT [FK_DocumentSignedStatus_PersonData]
GO
USE [DCAT_ReadOnly]
GO
/****** Object: Table [dbo].[ApprovalHistory] Script Date: 5/17/2016 10:26:05 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ApprovalHistory](
[ApprovalHistoryId] [bigint] IDENTITY(1,1) NOT NULL,
[RequestId] [bigint] NOT NULL,
[AccessLevelId] [bigint] NOT NULL,
[ApproverGroupId] [bigint] NOT NULL,
[ApproverID] [bigint] NOT NULL,
[ApprovalDateTime] [datetime] NOT NULL,
[ApprovalLevel] [int] NOT NULL,
[IsVerbal] [bit] NOT NULL,
[ApproverComment] [ntext] NULL,
[ActionId] [int] NOT NULL,
[State] [nvarchar](50) NOT NULL,
[Reason] [nvarchar](50) NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[CreatedBy] [nvarchar](100) NOT NULL,
[LastModifiedDate] [datetime] NOT NULL,
[LastModifiedBy] [nvarchar](100) NOT NULL,
[HardKeyGroupId] [bigint] NOT NULL,
CONSTRAINT [PK_ApprovalHistories] PRIMARY KEY CLUSTERED
(
[ApprovalHistoryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[ApprovalHistory] ADD CONSTRAINT [DF_ApprovalHistory_HardKeyGroupId] DEFAULT ((-1)) FOR [HardKeyGroupId]
GO
ALTER TABLE [dbo].[ApprovalHistory] WITH CHECK ADD CONSTRAINT [FK_ApprovalHistory_AccessLevel] FOREIGN KEY([AccessLevelId])
REFERENCES [dbo].[AccessLevel] ([AccessLevelId])
GO
ALTER TABLE [dbo].[ApprovalHistory] CHECK CONSTRAINT [FK_ApprovalHistory_AccessLevel]
GO
ALTER TABLE [dbo].[ApprovalHistory] WITH CHECK ADD CONSTRAINT [FK_ApprovalHistory_Approver] FOREIGN KEY([ApproverID])
REFERENCES [dbo].[Approver] ([ApproverId])
GO
ALTER TABLE [dbo].[ApprovalHistory] CHECK CONSTRAINT [FK_ApprovalHistory_Approver]
GO
ALTER TABLE [dbo].[ApprovalHistory] WITH CHECK ADD CONSTRAINT [FK_ApprovalHistory_ApproverGroup] FOREIGN KEY([ApproverGroupId])
REFERENCES [dbo].[ApproverGroup] ([ApproverGroupId])
GO
ALTER TABLE [dbo].[ApprovalHistory] CHECK CONSTRAINT [FK_ApprovalHistory_ApproverGroup]
GO
ALTER TABLE [dbo].[ApprovalHistory] WITH CHECK ADD CONSTRAINT [FK_ApprovalHistory_HardKeyGroup] FOREIGN KEY([HardKeyGroupId])
REFERENCES [dbo].[HardKeyGroup] ([HardkeyGroupId])
GO
ALTER TABLE [dbo].[ApprovalHistory] CHECK CONSTRAINT [FK_ApprovalHistory_HardKeyGroup]
GO
ALTER TABLE [dbo].[ApprovalHistory] WITH CHECK ADD CONSTRAINT [FK_ApprovalHistory_Request] FOREIGN KEY([RequestId])
REFERENCES [dbo].[Request] ([RequestId])
GO
ALTER TABLE [dbo].[ApprovalHistory] CHECK CONSTRAINT [FK_ApprovalHistory_Request]
GO
USE [DCAT_ReadOnly]
GO
/****** Object: Table [dbo].[People_Request] Script Date: 5/17/2016 10:26:47 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[People_Request](
[PeopleInRequestId] [bigint] IDENTITY(1,1) NOT NULL,
[RequestID] [bigint] NOT NULL,
[TemporaryBadgeNumber] [bigint] NULL,
[PersonDataId] [bigint] NOT NULL,
[IsAccessControlUpdated] [bit] NULL,
[VehicleLicenseNumber] [nvarchar](50) NULL,
[TerminateReason] [nvarchar](256) NULL,
[AttentionRequired] [smallint] NULL,
[State] [nvarchar](50) NOT NULL,
[Reason] [nvarchar](50) NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[CreatedBy] [nvarchar](100) NOT NULL,
[LastModifiedDate] [datetime] NOT NULL,
[LastModifiedBy] [nvarchar](100) NOT NULL,
[RevisionNo] [int] NOT NULL,
[EscortPersonId] [bigint] NULL,
[ReassignBadgeReason] [nvarchar](50) NULL,
[ReassignBadgeComments] [nvarchar](1000) NULL,
[IsAVInspectionRequired] [bit] NOT NULL,
[IsReInspectionRequired] [bit] NOT NULL,
CONSTRAINT [PK_PeopleIn_Request] PRIMARY KEY CLUSTERED
(
[PeopleInRequestId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[People_Request] ADD CONSTRAINT [DF_People_Request_EscortPersonId] DEFAULT ((-1)) FOR [EscortPersonId]
GO
ALTER TABLE [dbo].[People_Request] ADD DEFAULT ((0)) FOR [IsAVInspectionRequired]
GO
ALTER TABLE [dbo].[People_Request] ADD DEFAULT ((0)) FOR [IsReInspectionRequired]
GO
ALTER TABLE [dbo].[People_Request] WITH CHECK ADD CONSTRAINT [FK_People_Request_PersonData] FOREIGN KEY([PersonDataId])
REFERENCES [dbo].[PersonData] ([PersonId])
GO
ALTER TABLE [dbo].[People_Request] CHECK CONSTRAINT [FK_People_Request_PersonData]
GO
ALTER TABLE [dbo].[People_Request] WITH CHECK ADD CONSTRAINT [FK_People_Request_Request] FOREIGN KEY([RequestID])
REFERENCES [dbo].[Request] ([RequestId])
GO
ALTER TABLE [dbo].[People_Request] CHECK CONSTRAINT [FK_People_Request_Request]
GO
USE [DCAT_ReadOnly]
GO
/****** Object: Table [dbo].[Request] Script Date: 5/17/2016 10:27:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Request](
[RequestId] [bigint] IDENTITY(1,1) NOT NULL,
[RequestTypeId] [bigint] NOT NULL,
[RequestStartDateTime] [datetime] NOT NULL,
[RequestEndDateTime] [datetime] NULL,
[ChangedByEmail] [nvarchar](100) NULL,
[RequestTimeStamp] [datetime] NULL,
[EscortAlias] [nvarchar](30) NULL,
[BusinessJustification] [ntext] NULL,
[RequestorEmail] [nvarchar](100) NULL,
[VerbalApproverAlias] [nvarchar](100) NULL,
[RequestComment] [ntext] NULL,
[IsPlanned] [bit] NULL,
[RequestorFirstName] [nvarchar](100) NULL,
[RequestorLastName] [nvarchar](100) NULL,
[IsImmediateRequest] [bit] NULL,
[State] [nvarchar](50) NOT NULL,
[Reason] [nvarchar](50) NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[CreatedBy] [nvarchar](100) NOT NULL,
[LastModifiedDate] [datetime] NOT NULL,
[LastModifiedBy] [nvarchar](100) NOT NULL,
[RevisionNo] [int] NOT NULL,
[LocationId] [bigint] NOT NULL,
[RequestHistory] [ntext] NULL,
[ContactNotification] [nvarchar](1000) NULL,
[AdditionalComments] [nvarchar](max) NULL,
[HiPriority] [bit] NOT NULL,
[EffectiveOLA] [int] NOT NULL,
CONSTRAINT [PK_Request] PRIMARY KEY CLUSTERED
(
[RequestId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Request] ADD CONSTRAINT [DF_Request_HiPriority] DEFAULT ((0)) FOR [HiPriority]
GO
ALTER TABLE [dbo].[Request] ADD CONSTRAINT [DF_Request_EffectiveOLA] DEFAULT ((-1)) FOR [EffectiveOLA]
GO
ALTER TABLE [dbo].[Request] WITH CHECK ADD CONSTRAINT [FK_Request_Location] FOREIGN KEY([LocationId])
REFERENCES [dbo].[Location] ([LocationId])
GO
ALTER TABLE [dbo].[Request] CHECK CONSTRAINT [FK_Request_Location]
GO
ALTER TABLE [dbo].[Request] WITH CHECK ADD CONSTRAINT [FK_Request_RequestType] FOREIGN KEY([RequestTypeId])
REFERENCES [dbo].[RequestType] ([RequestTypeId])
GO
ALTER TABLE [dbo].[Request] CHECK CONSTRAINT [FK_Request_RequestType]
GO
USE [DCAT_ReadOnly]
GO
/****** Object: Table [dbo].[Company] Script Date: 5/17/2016 10:28:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Company](
[CompanyId] [bigint] IDENTITY(1,1) NOT NULL,
[CompanyName] [nvarchar](256) NOT NULL,
[CreatedBy] [nvarchar](100) NULL,
[CreatedDate] [datetime] NOT NULL,
[LastModifiedDate] [datetime] NOT NULL,
[LastModifiedBy] [nvarchar](100) NOT NULL,
[RevisionNo] [int] NOT NULL,
CONSTRAINT [PK_Companies] PRIMARY KEY CLUSTERED
(
[CompanyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [DCAT_ReadOnly]
GO
/****** Object: Table [dbo].[PersonData] Script Date: 5/17/2016 10:30:06 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PersonData](
[PersonId] [bigint] IDENTITY(1,1) NOT NULL,
[BadgeNumber] [bigint] NULL,
[FirstName] [nvarchar](100) NOT NULL,
[LastName] [nvarchar](100) NOT NULL,
[LegalName] [nvarchar](200) NULL,
[BirthDay] [int] NULL,
[BirthMonth] [int] NULL,
[IsMSEmployee] [bit] NULL,
[ManagerEmail] [nvarchar](100) NULL,
[PersonEmail] [nvarchar](100) NULL,
[PreferredName] [nvarchar](100) NULL,
[CompanyId] [bigint] NULL,
[IsBlackListed] [bit] NOT NULL,
[IsExternalUser] [bit] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[CreatedBy] [nvarchar](100) NOT NULL,
[LastModifiedDate] [datetime] NOT NULL,
[LastModifiedBy] [nvarchar](100) NOT NULL,
[RevisionNo] [int] NOT NULL,
[State] [nvarchar](50) NOT NULL,
[Reason] [nvarchar](50) NOT NULL,
[FTESponsor] [nvarchar](100) NULL,
[PersonnelNumber] [int] NOT NULL,
[SignDocumentComments] [nvarchar](max) NULL,
[GTCitizenship] [bit] NULL,
[LanguageCode] [nvarchar](10) NULL,
CONSTRAINT [PK_PersonData] PRIMARY KEY CLUSTERED
(
[PersonId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[PersonData] ADD DEFAULT ((-1)) FOR [PersonnelNumber]
GO
ALTER TABLE [dbo].[PersonData] WITH CHECK ADD CONSTRAINT [FK_PersonData_Company] FOREIGN KEY([CompanyId])
REFERENCES [dbo].[Company] ([CompanyId])
GO
ALTER TABLE [dbo].[PersonData] CHECK CONSTRAINT [FK_PersonData_Company]
GO
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply