Database Layout
Tables
tblCompanies
| Name | Type | NULL | index |
| CompanyName | nvarchar(255) |
| PK |
| DateStamp | datetime | |
tblJobNums
| Name | Type | NULL | index |
| JobNum | varchar(50) |
| PK |
| DateStamp | datetime | |
tblSurveyPosts
| Name | Type | NULL | index |
| GUID | nvarchar(64) | |
| key | nvarchar(255) | |
| value | nvarchar(255) | x |
| timestamp | datetime | |
tblSurveys
| Name | Type | NULL | index |
| SurveyID | int (AUTO) |
| PK |
| SurveyName | nvarchar(255) | |
| SurveyTimestamp | datetime | |
tblSurveysSent
| Name | Type | NULL | index |
| GUID | nvarchar(64) | |
| SurveyID | int | |
| Timestamp | datetime | |
| email | nvarchar(255) | |
| name | nvarchar(255) | x |
| productname | nvarchar(255) | x |
| jobnum | varchar(12) | x |
| clientCompany | nvarchar(255) | x |
| fromemail | nvarchar(255) | x |
| emailSubject | nvarchar(1023) | x |
| emailmessage | text | x |
Views
vw_Corespondance
SELECT email AS ClientEmail, name AS ClientName, productname AS ProductName, jobnum AS JobNumber, clientCompany AS ClientCompany, fromemail AS StaffEmail, CASE WHEN EXISTS (SELECT 1 FROM tblSurveyPosts WHERE tblSurveyPosts.GUID = tblSurveysSent.GUID) THEN 1 ELSE 0 END AS HasResponded FROM dbo.tblSurveysSent
vw_Parameters
SELECT TOP 100 PERCENT dbo.sysobjects.name AS SPname, dbo.syscolumns.name, dbo.systypes.name AS type, dbo.syscolumns.length AS size, dbo.syscolumns.isoutparam FROM dbo.sysobjects INNER JOIN dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id INNER JOIN dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype AND dbo.systypes.name <> 'sysname' WHERE (dbo.sysobjects.xtype = 'P') OR (dbo.sysobjects.xtype = 'U')
vw_aggregates
SELECT dbo.tblSurveys.SurveyName, dbo.tblSurveyPosts.[key], dbo.tblSurveyPosts.[value], COUNT(*) AS number FROM dbo.tblSurveysSent INNER JOIN dbo.tblSurveys ON dbo.tblSurveysSent.SurveyID = dbo.tblSurveys.SurveyID INNER JOIN dbo.tblSurveyPosts ON dbo.tblSurveysSent.GUID = dbo.tblSurveyPosts.GUID GROUP BY dbo.tblSurveyPosts.[key], dbo.tblSurveyPosts.[value], dbo.tblSurveysSent.SurveyID, dbo.tblSurveys.SurveyName
vw_responces
SELECT dbo.tblSurveys.SurveyName, dbo.tblSurveysSent.email AS ClientEmail, dbo.tblSurveysSent.name AS ClientName, dbo.tblSurveysSent.productname AS ProductName, dbo.tblSurveysSent.jobnum AS JobNum, dbo.tblSurveysSent.clientCompany AS ClientCompany, dbo.tblSurveysSent.fromemail AS StaffEmail, dbo.tblSurveyPosts.[key], dbo.tblSurveyPosts.[value], dbo.tblSurveyPosts.[timestamp] FROM dbo.tblSurveyPosts INNER JOIN dbo.tblSurveysSent ON dbo.tblSurveyPosts.GUID = dbo.tblSurveysSent.GUID INNER JOIN dbo.tblSurveys ON dbo.tblSurveysSent.SurveyID = dbo.tblSurveys.SurveyID
