Database

SELECT     
	CASE WHEN PTT.POSTransactionTypeCode = 'A' THEN 'A' 
		WHEN PTT.POSTransactionTypeCode = 'C' THEN 'C'
		WHEN PTT.POSTransactionTypeCode = 'R' THEN 'R'
		WHEN PTT.POSTransactionTypeCode = 'I' THEN 'C' 
	END AS TransactionType, 
	CASE WHEN PTT.POSTransactionTypeCode = 'A' THEN 'A'
		WHEN PTT.POSTransactionTypeCode = 'C' THEN 'A'
		WHEN PTT.POSTransactionTypeCode = 'R' THEN 'A'
		WHEN PTT.POSTransactionTypeCode = 'I' THEN 'I' 
	END AS ProviderStatus, 
-- right format. uncomment when the PIN numbers are fixed.
'00000' + C.ProviderNbr + SPACE(1) AS ContractNbr,
-- right format. uncomment when sending the provider numbers that were added wrongly.
--'000000' + C.ContractNbr AS ContractNbr,
	'01' AS Other, 
	'000000' AS PIN, 
	UPPER(LEFT(C.ProviderName, 28)) AS ContractName,
	'PROGRAM DIRECTOR' + SPACE(8) AS ContractAddressAttention,
	UPPER(LEFT(A.StreetAddress1, 24)) AS Address1, 
	UPPER(LEFT(A.StreetAddress2, 24)) AS Address2,
	UPPER(LEFT(A.City, 17)) AS City,
	UPPER(S.PK_Geographic_Location_Code) AS State,
	LEFT(A.Zip1, 9) AS ZipCd,
	SPACE(12) AS ErrorCode2,
	SPACE(8) AS Filler
FROM         
	POSPINTransaction AS E INNER JOIN
	Provider AS C ON E.FKProviderId = C.PKProviderId INNER JOIN
	ProviderDate AS PD ON dbo.Udf_GetPKProviderDate(C.PKProviderId) = PD.PKContractDate INNER JOIN
	ProviderAddress AS PA ON PA.FKProviderId=C.PKProviderId INNER JOIN
	[Address] AS A ON A.PKAddressId = PA.FKAddressId INNER JOIN
	--ParentTypeLKP AS PT ON A.FKParentType = PT.PKParentType AND PT.ParentTypeCd = 'usrContract' INNER JOIN
	[State] AS S ON A.FK_State = S.PKStateId INNER JOIN
	POSTransactionTypeLKP PTT ON E.FKPOSTransactionTypeId=PTT.PKPOSTransactionTypeId
WHERE     
	(PA.IsCurrentAddress = 1) AND (E.TransactionSentDate IS NULL) AND (E.TransactionReceivedDate IS NULL) AND (E.FKPOSTransactionTypeId IN (SELECT PKPOSTransactionTypeId FROM POSTransactionTypeLKP WHERE POSTransactionTypeCode IN ('A', 'C', 'R'))) OR
	(PA.IsCurrentAddress = 1) AND (E.TransactionSentDate IS NULL) AND (E.TransactionReceivedDate IS NULL) AND (E.FKPOSTransactionTypeId = (SELECT Top 1 PKPOSTransactionTypeId FROM POSTransactionTypeLKP WHERE POSTransactionTypeCode = 'I')) AND (PD.EffectiveDateTo IS NULL) OR
	(PA.IsCurrentAddress = 1) AND (E.TransactionSentDate IS NULL) AND (E.TransactionReceivedDate IS NULL) AND (PD.EffectiveDateTo <= GETDATE())
UNION
SELECT     
	CASE WHEN PTT.POSTransactionTypeCode = 'A' THEN 'A' 
		WHEN PTT.POSTransactionTypeCode = 'C' THEN 'C' 
		WHEN PTT.POSTransactionTypeCode = 'R' THEN 'R' 
		WHEN PTT.POSTransactionTypeCode= 'I' THEN 'C' 
	END AS TransactionType, 
	CASE WHEN PTT.POSTransactionTypeCode = 'A' THEN 'A' 
		WHEN PTT.POSTransactionTypeCode = 'C' THEN 'A' 
		WHEN PTT.POSTransactionTypeCode = 'R' THEN 'A' 
		WHEN PTT.POSTransactionTypeCode = 'I' THEN 'I' 
	END AS ProviderStatus, SP.NPINumber AS ContractNbr,
	'01' AS Other,
	'000000' AS PIN,
	UPPER(LEFT(SP.FirstName, 28)) AS ContractName,
	'PROGRAM DIRECTOR' + SPACE(8) AS ContractAddressAttention,
	UPPER(LEFT(ADDR.StreetAddress1, 24)) AS Address1, 
	UPPER(LEFT(ADDR.StreetAddress2, 24)) AS Address2,
	UPPER(LEFT(ADDR.City, 17)) AS City,
	UPPER(ST.PK_Geographic_Location_Code) AS State,
	LEFT(ADDR.Zip1, 9) AS ZipCd,
	SPACE(12) AS ErrorCode2,
	SPACE(8) AS Filler
FROM         
	POSPINTransaction AS EDS INNER JOIN
	NPIAssociation NA ON NA.FKParentId=EDS.FKProviderId AND NA.FKParentTypeLKPId = (SELECT Min(PKParentTypeLKPId) FROM ParentTypeLKP WHERE ParentCode='PRV') INNER JOIN 
	NPI AS SP ON SP.PKNPIId = NA.FKNPIId LEFT OUTER JOIN
	POSTransactionError AS EDSERR ON EDSERR.FKPOSPINTransactionId = EDS.PKPOSPINTransactionId LEFT OUTER JOIN
	POSTransactionErrorLKP AS EDSERRCD ON EDSERRCD.PKPOSTransactionErrorLKPId = EDSERR.FKPOSTransactionErrorLKPId LEFT OUTER JOIN
	NPIAddress AS NPIADDR ON NPIADDR.FKNPIId = SP.PKNPIId AND NPIADDR.IsCurrentAddress = 1 INNER JOIN
	[Address] AS ADDR ON ADDR.PKAddressId=NPIADDR.FKAddressId INNER JOIN
	[State] AS ST ON ADDR.FK_State = ST.PKStateId INNER JOIN
	County AS CO ON CO.PKCountyId = ADDR.FK_County INNER JOIN
	POSTransactionTypeLKP PTT ON EDS.FKPOSTransactionTypeId=PTT.PKPOSTransactionTypeId
WHERE     
	(EDS.TransactionSentDate IS NULL) AND (EDS.TransactionReceivedDate IS NULL) AND 
	(EDS.FKPOSTransactionTypeId IN (SELECT PKPOSTransactionTypeId FROM POSTransactionTypeLKP WHERE POSTransactionTypeCode IN ('A', 'C', 'R')) 
	OR	EDS.FKPOSTransactionTypeId = (SELECT Top 1 PKPOSTransactionTypeId FROM POSTransactionTypeLKP WHERE POSTransactionTypeCode = 'I'))

D:\abc\WebSite\ASP_NET\TTGS\TTGS\App_Data
Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

BUTTONS:

OK

Reaching out to the community to pressure test our internal thinking.

We are building a simplified business intelligence platform that will aggregate metrics (i.e. traffic, backlinks) and text list (i.e search keywords, used technologies) from several data providers.

The data will be somewhat loosely structured and may change over time with vendors potentially changing their response formats.

Data volume may be long term 100,000 rows x 25 input vectors.

Data would be updated and read continuously but not at massive concurrent volume.

We'd expect to need to do some ETL transformations on the gathered data from partners along the way to the UI (e.g show trending information over the past five captured data points).

We'd want to archive every single data snapshot (i.e. version it) vs just storing the most current data point.

The persistence technology should be readily available through AWS.

Our assumption is our requirements lend themselves best towards DynamoDB (vs Amazon Neptune or Redshift or Aurora).

Is that fair to assume? Are there any other questions / information I can provide to elicit input from this community?