public override void Up()
{
//Remove
Sql("DROP View RoutingCounts");
Sql("DROP View _RoutingCounts");
Sql("DROP View Requests");
Sql("DROP VIEW DNS3_Organizations");
Sql("DROP VIEW DNS3_Groups");
Sql("DROP VIEW DNS3_DataMarts");
Sql("DROP VIEW DataMartRequests");
Sql("DROP VIEW vwUsers");
Sql("DROP VIEW vwUsedRequestTypes");
Sql("DROP VIEW UsedRequestTypes");
Sql("DROP VIEW LookUpQueryTypeMetrics_view");
//RequestDataMarts
AddColumn("RequestDataMarts", "ID", c => c.Guid(false, defaultValueSql: "[dbo].[newsqlguid]()"));
DropPrimaryKey("RequestDataMarts");
AddColumn("RequestDataMarts", "RequestID", c => c.Guid(true));
Sql(
"UPDATE RequestDataMarts SET RequestID = (SELECT TOP 1 SID FROM Queries WHERE QueryId = RequestDataMarts.QueryId)");
DropIndex("RequestDataMarts", "_dta_index_QueriesDataMarts_16_1666104976__K1_K3");
DropIndex("RequestDataMarts", "RequestDataMart_Request");
DropColumn("RequestDataMarts", "QueryId");
Sql("DELETE FROM [RequestDataMarts] WHERE [RequestId] IS NULL");
AlterColumn("RequestDataMarts", "RequestID", c => c.Guid(false));
AddPrimaryKey("RequestDataMarts", "ID");
//RequestResponses
Sql("DROP TRIGGER [dbo].[RequestRoutingInstances_InsertUpdateDeleteItem]");
Sql(MigrationHelpers.DropPrimaryKeyScript("RequestResponses"));
DropColumn("RequestResponses", "Id");
RenameColumn("RequestResponses", "SID", "ID");
AddColumn("RequestResponses", "RequestDataMartID", c => c.Guid(true));
Sql(
"UPDATE RequestResponses SET RequestDataMartID = (SELECT ID FROM RequestDataMarts INNER JOIN Queries ON RequestDataMarts.RequestID = Queries.SID WHERE Queries.QueryId = RequestResponses.RequestId AND RequestDataMarts.DataMartID = RequestResponses.DataMartID)");
Sql(MigrationHelpers.DropForeignKeyScript("RequestResponses", "RequestId"));
Sql(MigrationHelpers.DropForeignKeyScript("RequestResponses", "DataMartID"));
DropColumn("RequestResponses", "RequestId");
DropColumn("RequestResponses", "DataMartID");
//Update ResponseGroup information properly with the ResponseGroups being on the Request?
//Create Count column and set it's default value to a query of existing items + 1.
RenameTable("RequestResponses", "RequestDataMartResponses");
AddColumn("RequestDataMartResponses", "Count", c => c.Int(false, defaultValue: 0));
Sql(
"UPDATE RequestDataMartResponses SET Count = (SELECT COUNT(*) FROM RequestDataMartResponses r WHERE r.RequestDataMartID = RequestDataMartResponses.RequestDataMartID AND r.SubmitTime < RequestDataMartResponses.SubmitTime) + 1");
//Create the trigger for inserts that updates the count so it doesn't have to be done in code. This will be set to computed when the entity is updated.
Sql(@"CREATE TRIGGER [dbo].RequestDataMartResponsesInsert
ON [dbo].[RequestDataMartResponses]
AFTER INSERT
AS
BEGIN
UPDATE RequestDataMartResponses SET Count = (SELECT COUNT(*) FROM RequestDataMartResponses r WHERE r.RequestDataMartID = RequestDataMartResponses.RequestDataMartID AND r.SubmitTime < RequestDataMartResponses.SubmitTime) + 1 WHERE RequestDataMartResponses.ID IN (SELECT ID FROM inserted)
END" );
//Drop the isCurrent column as it's no longer needed.
DropColumn("RequestDataMartResponses", "isCurrent");
//RequestDataMartSearchResults
//RequestOrganizationSearchResults
//RequestSearchResults
//RequestRegistrySearchResults
//Take all of these and merge them into a single table. Silly that it's multiple when it's just a ref to the ID.
CreateTable(
"dbo.RequestDataMartResponseSearchResults",
c => new
{
RequestDataMartResponseID = c.Guid(false),
ItemID = c.Guid(false)
})
.PrimaryKey(t => new { t.RequestDataMartResponseID, t.ItemID });
//Because this was done incorrectly and the results should have been per data mart we're going to put the results on the first data mart
Sql(
"INSERT INTO RequestDataMartResponseSearchResults (RequestDataMartResponseID, ItemID) SELECT (SELECT TOP 1 RequestDataMartResponses.ID FROM RequestDataMarts JOIN RequestDataMartResponses ON RequestDataMarts.ID = RequestDataMartResponses.RequestDataMartID WHERE RequestDataMarts.RequestID = Queries.SID) AS RequestDataMartResponseID, ResultDataMartID FROM Queries JOIN RequestDataMartSearchResults ON SearchRequestId = Queries.QueryId");
DropTable("RequestDataMartSearchResults");
Sql(
"INSERT INTO RequestDataMartResponseSearchResults (RequestDataMartResponseID, ItemID) SELECT (SELECT TOP 1 RequestDataMartResponses.ID FROM RequestDataMarts JOIN RequestDataMartResponses ON RequestDataMarts.ID = RequestDataMartResponses.RequestDataMartID WHERE RequestDataMarts.RequestID = Queries.SID) AS RequestDataMartResponseID, ResultOrganizationID FROM Queries JOIN RequestOrganizationSearchResults ON SearchRequestId = Queries.QueryId");
DropTable("RequestOrganizationSearchResults");
Sql(
"INSERT INTO RequestDataMartResponseSearchResults (RequestDataMartResponseID, ItemID) SELECT (SELECT TOP 1 RequestDataMartResponses.ID FROM RequestDataMarts JOIN RequestDataMartResponses ON RequestDataMarts.ID = RequestDataMartResponses.RequestDataMartID WHERE RequestDataMarts.RequestID = Queries.SID) AS RequestDataMartResponseID, ResultRegistryID FROM Queries JOIN RequestRegistrySearchResults ON SearchRequestId = Queries.QueryId");
DropTable("RequestRegistrySearchResults");
Sql(
"INSERT INTO RequestDataMartResponseSearchResults (RequestDataMartResponseID, ItemID) SELECT (SELECT TOP 1 RequestDataMartResponses.ID FROM RequestDataMarts JOIN RequestDataMartResponses ON RequestDataMarts.ID = RequestDataMartResponses.RequestDataMartID WHERE RequestDataMarts.RequestID = Queries.SID) AS RequestDataMartResponseID, (SELECT TOP 1 SID FROM Queries WHERE Queries.QueryId = RequestSearchResults.ResultRequestId) AS ItemID FROM Queries JOIN RequestSearchResults ON SearchRequestId = Queries.QueryId");
DropTable("RequestSearchResults");
//RequestSearchTerms
DropPrimaryKey("RequestSearchTerms", "PK_QuerySearchTerms_SearchTagId");
DropIndex("RequestSearchTerms", "IX_QuerySearchTerms");
DropColumn("RequestSearchTerms", "Id");
AddColumn("RequestSearchTerms", "ID", c => c.Guid(false, defaultValueSql: "[dbo].[newsqlguid]()"));
AddPrimaryKey("RequestSearchTerms", "ID");
AddColumn("RequestSearchTerms", "RequestSID", c => c.Guid(true));
Sql(
"UPDATE RequestSearchTerms SET RequestSID = (SELECT TOP 1 SID FROM Queries WHERE QueryId = RequestSearchTerms.RequestId)");
Sql("DELETE RequestSearchTerms WHERE RequestSID IS NULL");
AlterColumn("RequestSearchTerms", "RequestSID", c => c.Guid(false));
DropColumn("RequestSearchTerms", "RequestId");
RenameColumn("RequestSearchTerms", "RequestSID", "RequestID");
CreateIndex("RequestSearchTerms", "RequestID");
//RequestSharedFolders_Request
Sql(MigrationHelpers.DropPrimaryKeyScript("RequestSharedFolders_Request"));
Sql(MigrationHelpers.DropForeignKeyScript("RequestSharedFolders_Request", "FolderId"));
Sql(MigrationHelpers.DropForeignKeyScript("RequestSharedFolders_Request", "RequestId"));
AddColumn("RequestSharedFolders_Request", "RequestSharedFolderID", c => c.Guid(true));
Sql(
"UPDATE RequestSharedFolders_Request SET RequestSharedFolderID = (SELECT TOP 1 SID from RequestSharedFolders WHERE Id = RequestSharedFolders_Request.FolderId)");
DropColumn("RequestSharedFolders_Request", "FolderId");
AlterColumn("RequestSharedFolders_Request", "RequestSharedFolderID", c => c.Guid(false));
AddColumn("RequestSharedFolders_Request", "RequestSID", c => c.Guid(true));
Sql(
"UPDATE RequestSharedFolders_Request SET RequestSID = (SELECT TOP 1 SID FROM Queries WHERE QueryId = RequestSharedFolders_Request.RequestId)");
DropColumn("RequestSharedFolders_Request", "RequestId");
AlterColumn("RequestSharedFolders_Request", "RequestSID", c => c.Guid(false));
RenameColumn("RequestSharedFolders_Request", "RequestSID", "RequestID");
RenameTable("RequestSharedFolders_Request", "RequestSharedFolderRequests");
//RequestSharedFolders
Sql(MigrationHelpers.DropPrimaryKeyScript("RequestSharedFolders"));
DropColumn("RequestSharedFolders", "Id");
RenameColumn("RequestSharedFolders", "SID", "ID");
AddPrimaryKey("RequestSharedFolders", "ID");
AlterColumn("RequestSharedFolders", "Name", c => c.String(false, 255));
CreateIndex("RequestSharedFolders", "Name");
//Response Groups
AddColumn("ResponseGroups", "SID", c => c.Guid(false, defaultValueSql: "[dbo].[newsqlguid]()"));
AddColumn("RequestDataMartResponses", "ResponseGroupSID", c => c.Guid(true));
Sql(MigrationHelpers.DropForeignKeyScript("RequestDataMartResponses", "ResponseGroupId"));
Sql(MigrationHelpers.DropPrimaryKeyScript("ResponseGroups"));
Sql(
"UPDATE RequestDataMartResponses SET ResponseGroupSID = (SELECT TOP 1 SID FROM ResponseGroups WHERE Id = RequestDataMartResponses.ResponseGroupId) WHERE NOT RequestDataMartResponses.ResponseGroupId IS NULL");
DropColumn("RequestDataMartResponses", "ResponseGroupId");
RenameColumn("RequestDataMartResponses", "ResponseGroupSID", "ResponseGroupID");
DropColumn("ResponseGroups", "Id");
RenameColumn("ResponseGroups", "SID", "ID");
AddPrimaryKey("ResponseGroups", "ID");
//Queries
DropPrimaryKey("Queries", "PK_Queries");
Sql(MigrationHelpers.ClearStatsScript("Queries"));
DropIndex("Queries", "_dta_index_Queries_16_98099390__K1_K30");
DropIndex("Queries", "ix_sid");
RenameColumn("Queries", "SID", "ID");
RenameTable("Queries", "Requests");
AddPrimaryKey("Requests", "ID");
DropIndex("Requests", "Queries_Type");
RenameColumn("Requests", "RequestTypeId", "RequestTypeID");
CreateIndex("Requests", "RequestTypeID");
//Add additional stuff back
//RequestDataMarts
//AddForeignKey("RequestDataMarts", "RequestID", "Requests", "ID", true); --Can't be added because of cycles
//RequestDataMartResponses
Sql(@" CREATE TRIGGER [dbo].[RequestDataMartResponses_InsertUpdateDelete]
ON [dbo].[RequestDataMartResponses]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
IF ((SELECT COUNT(*) FROM inserted) > 0)
UPDATE Requests SET Updated = GETUTCDATE() WHERE Requests.ID IN (SELECT RequestDataMarts.RequestID FROM inserted JOIN RequestDataMarts ON inserted.RequestDataMartID = RequestDataMarts.ID)
END" );
AddPrimaryKey("RequestDataMartResponses", "ID");
AddForeignKey("RequestDataMartResponses", "RequestDataMartID", "RequestDataMarts", "ID", true);
CreateIndex("RequestDataMartResponses", "Count");
CreateIndex("RequestDataMartResponses", new string[] { "RequestDataMartID", "Count" });
//Update the response group FK etc.
Sql(
@"ALTER TABLE RequestDataMartResponses ADD CONSTRAINT FK_RequestDataMartResponses_ResponseGroups_ResponseGroupID FOREIGN KEY (ResponseGroupID) REFERENCES ResponseGroups(ID) ON DELETE SET NULL ON UPDATE CASCADE");
//Search results Add triggers to each of the tables that it could be and delete if ItemID is in on delete
Sql(@" CREATE TRIGGER [dbo].DataMartsDelete
ON [dbo].[DataMarts]
AFTER DELETE
AS
BEGIN
DELETE FROM RequestDataMartResponseSearchResults WHERE ItemID IN (SELECT ID FROM deleted)
END" );
Sql(@" CREATE TRIGGER [dbo].RequestsDelete
ON [dbo].[Requests]
AFTER DELETE
AS
BEGIN
DELETE FROM RequestDataMartResponseSearchResults WHERE ItemID IN (SELECT ID FROM deleted)
DELETE FROM RequestDataMarts WHERE RequestID IN (SELECT ID FROM deleted)
END" );
Sql(@" CREATE TRIGGER [dbo].RegistriesDelete
ON [dbo].[Registries]
AFTER DELETE
AS
BEGIN
DELETE FROM RequestDataMartResponseSearchResults WHERE ItemID IN (SELECT ID FROM deleted)
END" );
Sql(@"ALTER TRIGGER [dbo].[OrganizationDelete]
ON [dbo].[Organizations]
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
UPDATE Organizations SET ParentOrganizationID = NULL WHERE ID IN (SELECT ID FROM deleted)
DELETE FROM Users WHERE OrganizationID IN (SELECT ID FROM deleted)
DELETE FROM RequestDataMartResponseSearchResults WHERE ItemID IN (SELECT ID FROM deleted)
END");
//RequestSearchTerms
AddForeignKey("RequestSearchTerms", "RequestID", "Requests", "ID", true);
//RequestSharedFolderRequests
AddPrimaryKey("RequestSharedFolderRequests", new string[] { "RequestSharedFolderID", "RequestID" });
AddForeignKey("RequestSharedFolderRequests", "RequestSharedFolderID", "RequestSharedFolders", "ID", true);
AddForeignKey("RequestSharedFolderRequests", "RequestID", "Requests", "ID", true);
//RoutingCounts
Sql(@"CREATE VIEW [dbo].[vwRequestCounts]
WITH SCHEMABINDING
AS
SELECT RequestID,
Sum(case when QueryStatusTypeId = 2 then 1 else 0 end) as Submitted,
Sum(case when QueryStatusTypeId = 3 OR QueryStatusTypeId = 14 then 1 else 0 end) as Completed,
Sum(case when QueryStatusTypeId = 4 then 1 else 0 end) as AwaitingRequestApproval,
Sum(case when QueryStatusTypeId = 10 then 1 else 0 end) as AwaitingResponseApproval,
Sum(case when QueryStatusTypeId = 5 then 1 else 0 end) as RejectedRequest,
Sum(case when QueryStatusTypeId = 12 then 1 else 0 end) as RejectedBeforeUploadResults,
Sum(case when QueryStatusTypeId = 13 then 1 else 0 end) as RejectedAfterUploadResults,
COUNT_BIG(*) as Total
FROM
dbo.RequestDataMarts
GROUP BY RequestID");
}
public override void Up()
{
//Removes
Sql("DROP View RoutingCounts");
Sql("DROP View _RoutingCounts");
//DataMartTypes
DropForeignKey("DataMarts", "FK_DataMarts_DataMartTypes");
DropPrimaryKey("DataMartTypes");
DropColumn("DataMarts", "DataMartTypeId");
//QueriesDataMarts
Sql(MigrationHelpers.ClearStatsScript("QueriesDataMarts"));
DropIndex("QueriesDataMarts", "RequestDataMart_DataMart");
DropPrimaryKey("QueriesDataMarts");
DropColumn("QueriesDataMarts", "DataMartId");
//RequestDataMartSearchResults
DropForeignKey("RequestDataMartSearchResults", "FK_dbo.RequestDataMartSearchResults_dbo.DataMarts_ResultDataMartId");
DropForeignKey("RequestDataMartSearchResults", "FK_dbo.RequestDataMartSearchResults_dbo.Queries_SearchRequestId");
Sql("ALTER TABLE RequestDataMartSearchResults DROP CONSTRAINT [PK_dbo.RequestDataMartSearchResults]");
DropIndex("RequestDataMartSearchResults", "IX_ResultDataMartId");
Sql(@"DECLARE @FKName AS nvarchar(max) = (SELECT TOP 1 Tab.CONSTRAINT_NAME from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE
Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Constraint_Type = 'PRIMARY KEY '
AND Col.Table_Name = 'RequestDataMartSearchResults')
DECLARE @Sql AS nvarchar(max) = 'ALTER TABLE RequestDataMartSearchResults DROP CONSTRAINT ' + @FKName
EXECUTE sp_executeSql @Sql");
DropColumn("RequestDataMartSearchResults", "ResultDataMartId");
//RequestRoutingInstances
DropForeignKey("RequestRoutingInstances", "DataMartID", "DataMarts");
Sql(@"DECLARE @FKName AS nvarchar(max) = (SELECT TOP 1
f.name AS ForeignKey
FROM
sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
WHERE OBJECT_NAME(f.parent_object_id) = 'RequestRoutingInstances' AND COL_NAME(fc.parent_object_id,
fc.parent_column_id) = 'DataMartID')
DECLARE @Sql AS nvarchar(max) = 'ALTER TABLE RequestRoutingInstances DROP CONSTRAINT ' + @FKName
EXECUTE sp_executeSql @Sql");
DropColumn("RequestRoutingInstances", "DataMartId");
//Projects_DataMarts
Sql(@"DECLARE @FKName AS nvarchar(max) = (SELECT TOP 1 Tab.CONSTRAINT_NAME from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE
Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Constraint_Type = 'PRIMARY KEY '
AND Col.Table_Name = 'Projects_DataMarts')
DECLARE @Sql AS nvarchar(max) = 'ALTER TABLE Projects_DataMarts DROP CONSTRAINT ' + @FKName
EXECUTE sp_executeSql @Sql");
Sql(@"DECLARE @FKName AS nvarchar(max) = (SELECT TOP 1
f.name AS ForeignKey
FROM
sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
WHERE OBJECT_NAME(f.parent_object_id) = 'Projects_DataMarts' AND COL_NAME(fc.parent_object_id,
fc.parent_column_id) = 'DataMartId')
DECLARE @Sql AS nvarchar(max) = 'ALTER TABLE Projects_DataMarts DROP CONSTRAINT ' + @FKName
EXECUTE sp_executeSql @Sql");
DropColumn("Projects_DataMarts", "DataMartId");
//DataMartAvailabilityPeriods
DropForeignKey("DataMartAvailabilityPeriods", "FK_DataMartAvailabilityPeriods_DataMarts_DataMartId");
AddColumn("DataMartAvailabilityPeriods", "DataMartSID", c => c.Guid(true));
Sql(
"UPDATE DataMartAvailabilityPeriods SET DataMartSID = (SELECT TOP 1 SID FROM DataMarts WHERE DataMartId = DataMartAvailabilityPeriods.DataMartId)");
DropColumn("DataMartAvailabilityPeriods", "DataMartId");
//DataMartInstalledModels
Sql(@"DECLARE @FKName AS nvarchar(max) = (SELECT TOP 1 Tab.CONSTRAINT_NAME from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE
Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Constraint_Type = 'PRIMARY KEY '
AND Col.Table_Name = 'DataMartInstalledModels')
DECLARE @Sql AS nvarchar(max) = 'ALTER TABLE DataMartInstalledModels DROP CONSTRAINT ' + @FKName
EXECUTE sp_executeSql @Sql");
Sql(@"DECLARE @FKName AS nvarchar(max) = (SELECT TOP 1
f.name AS ForeignKey
FROM
sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
WHERE OBJECT_NAME(f.parent_object_id) = 'DataMartInstalledModels' AND COL_NAME(fc.parent_object_id,
fc.parent_column_id) = 'DataMartId')
DECLARE @Sql AS nvarchar(max) = 'ALTER TABLE DataMartInstalledModels DROP CONSTRAINT ' + @FKName
EXECUTE sp_executeSql @Sql");
DropColumn("DataMartInstalledModels", "DataMartId");
//DataMarts
DropPrimaryKey("DataMarts");
DropColumn("DataMarts", "DataMartId");
//Adds
//DataMartTypes
RenameColumn("DataMartTypes", "SID", "ID");
AddPrimaryKey("DataMartTypes", "ID");
//DataMarts
RenameColumn("DataMarts", "SID", "ID");
AddPrimaryKey("DataMarts", "ID");
RenameColumn("DataMarts", "DataMartTypeSID", "DataMartTypeID");
AlterColumn("DataMarts", "DataMartTypeID", c => c.Guid(true));
AddForeignKey("DataMarts", "DataMartTypeID", "DataMartTypes", "ID", false);
CreateIndex("DataMarts", "DataMartTypeID");
//DataMartAvailabilityPeriods
RenameColumn("DataMartAvailabilityPeriods", "DataMartSID", "DataMartID");
AddForeignKey("DataMartAvailabilityPeriods", "DataMartID", "DataMarts", "ID");
//DataMartInstalledModels
RenameColumn("DataMartInstalledModels", "DataMartSID", "DataMartID");
AddPrimaryKey("DataMartInstalledModels", new string[] { "DataMartID", "ModelID" });
AddForeignKey("DataMartInstalledModels", "DataMartID", "DataMarts", "ID", true);
//QueriesDataMarts
RenameTable("QueriesDataMarts", "RequestDataMarts");
RenameColumn("RequestDataMarts", "DataMartSID", "DataMartID");
AddPrimaryKey("RequestDataMarts", new string[] { "QueryId", "DataMartID" });
AddForeignKey("RequestDataMarts", "DataMartID", "DataMarts", "ID", true);
//RequestDataMartSearchResults
RenameColumn("RequestDataMartSearchResults", "ResultDataMartSID", "ResultDataMartID");
AddPrimaryKey("RequestDataMartSearchResults", new string[] { "SearchRequestId", "ResultDatamartID" });
AddForeignKey("RequestDataMartSearchResults", "ResultDatamartID", "DataMarts", "ID", true);
//Request Routing Instances
RenameTable("RequestRoutingInstances", "RequestResponses");
RenameColumn("RequestResponses", "DataMartSID", "DataMartID");
AddForeignKey("RequestResponses", "DataMartID", "DataMarts", "ID", true);
//Projects_DataMarts
RenameTable("Projects_DataMarts", "ProjectDataMarts");
RenameColumn("ProjectDataMarts", "DataMartSID", "DataMartID");
AddPrimaryKey("ProjectDataMarts", new string[] { "ProjectID", "DataMartID" });
AddForeignKey("ProjectDataMarts", "DataMartID", "DataMarts", "ID", true);
//RoutingCounts
Sql(@"create view [dbo].[_RoutingCounts]
with schemabinding
as
select QueryId,
Sum(case when QueryStatusTypeId = 2 then 1 else 0 end) as Submitted,
Sum(case when QueryStatusTypeId = 3 OR QueryStatusTypeId = 14 then 1 else 0 end) as Completed,
Sum(case when QueryStatusTypeId = 4 then 1 else 0 end) as AwaitingRequestApproval,
Sum(case when QueryStatusTypeId = 10 then 1 else 0 end) as AwaitingResponseApproval,
Sum(case when QueryStatusTypeId = 5 then 1 else 0 end) as RejectedRequest,
Sum(case when QueryStatusTypeId = 12 then 1 else 0 end) as RejectedBeforeUploadResults,
Sum(case when QueryStatusTypeId = 13 then 1 else 0 end) as RejectedAfterUploadResults,
COUNT_BIG(*) as Total
from
dbo.RequestDataMarts
group by QueryId");
Sql(@"create view [dbo].[RoutingCounts] as select * from _RoutingCounts
");
}