QESTLab AUCOF WIP CompleteWork - Revision history

David.murphy: Categorised

2007-07-20T01:25:09Z

Categorised

←Older revision		Revision as of 01:25, 20 July 2007
Line 291:		Line 291:
	GO		GO
	</pre>		</pre>

			[[Category:Coffey Pages\|{{PAGENAME}}]]
			[[Category:QESTLab\|{{PAGENAME}}]]

Mark.pritchard at 23:41, 23 October 2006

2006-10-23T23:41:37Z

←Older revision		Revision as of 23:41, 23 October 2006
Line 1:		Line 1:
	<pre>--For a billing document to be picked up, it must have at least one sibling signed test report.		<pre>
			--For a billing document to be picked up, it must have at least one sibling signed test report.
	--That is, the test report must exist at the same level in the hierarcy as the billing document.		--That is, the test report must exist at the same level in the hierarcy as the billing document.
	--There can be more than one test report, but only one is required to be signed for the billing		--There can be more than one test report, but only one is required to be signed for the billing
Line 19:		Line 20:
	CREATE VIEW qestfilterProject_WIP_Complete AS		CREATE VIEW qestfilterProject_WIP_Complete AS
	(		(
	SELECT Charges.~~LabNo~~, Charges.ProjectCode, Charges.ProjectName, Charges.ClientName, Sum(Charges.WIP) AS WIP FROM		SELECT Charges.QESTOwnerLabNo, Charges.ProjectCode, Charges.ProjectName, Charges.ClientName, Sum(Charges.WIP) AS WIP FROM
	(		(
	SELECT DISTINCT DocumentChargeSingle.QestOwnerLabNo As ~~LabNo~~, DocumentChargeSingle.ProjectCode, DocumentChargeSingle.ProjectName,		SELECT DISTINCT DocumentChargeSingle.QestOwnerLabNo As QESTOwnerLabNo, DocumentChargeSingle.ProjectCode, DocumentChargeSingle.ProjectName,
	DocumentChargeSingle.ClientName, DocumentChargeSingle.TotalCharge AS WIP, DocumentCharge.QESTUniqueParentID FROM DocumentChargeSingle		DocumentChargeSingle.ClientName, DocumentChargeSingle.TotalCharge AS WIP, DocumentCharge.QESTUniqueParentID FROM DocumentChargeSingle
	INNER JOIN DocumentCharge ON DocumentChargeSingle.QESTUniqueParentID = DocumentCharge.QESTUniqueID		INNER JOIN DocumentCharge ON DocumentChargeSingle.QESTUniqueParentID = DocumentCharge.QESTUniqueID
Line 27:		Line 28:
	WHERE (DocumentChargeSingle.Printed = 0 OR DocumentChargeSingle.Printed IS NULL) AND (DocumentCertificates.SignatoryID > 0)		WHERE (DocumentChargeSingle.Printed = 0 OR DocumentChargeSingle.Printed IS NULL) AND (DocumentCertificates.SignatoryID > 0)
	) AS Charges		) AS Charges
	Group By Charges.ProjectCode, Charges.ProjectName, Charges.ClientName, Charges.~~LabNo~~		Group By Charges.ProjectCode, Charges.ProjectName, Charges.ClientName, Charges.QESTOwnerLabNo
	)		)
	GO		GO
Line 33:		Line 34:
	CREATE VIEW qestfilterLab_WIP_Complete AS		CREATE VIEW qestfilterLab_WIP_Complete AS
	(		(
	SELECT Charges.~~LabNo~~, Sum(Charges.WIP) AS WIP, Charges.LabCode FROM		SELECT Charges.QESTOwnerLabNo, Sum(Charges.WIP) AS WIP, Charges.LabCode FROM
	(		(
	SELECT DISTINCT DocumentChargeSingle.QestOwnerLabNo As ~~LabNo~~, DocumentChargeSingle.TotalCharge AS WIP,		SELECT DISTINCT DocumentChargeSingle.QestOwnerLabNo As QESTOwnerLabNo, DocumentChargeSingle.TotalCharge AS WIP,
	Laboratory.CounterPrefix AS LabCode, DocumentCharge.QESTUniqueParentID FROM DocumentChargeSingle		Laboratory.CounterPrefix AS LabCode, DocumentCharge.QESTUniqueParentID FROM DocumentChargeSingle
	INNER JOIN Laboratory On DocumentChargeSingle.QESTOwnerLabNo = Laboratory.LabNo		INNER JOIN Laboratory On DocumentChargeSingle.QESTOwnerLabNo = Laboratory.LabNo
Line 42:		Line 43:
	WHERE (DocumentChargeSingle.Printed = 0 OR DocumentChargeSingle.Printed IS NULL) AND (DocumentCertificates.SignatoryID > 0)		WHERE (DocumentChargeSingle.Printed = 0 OR DocumentChargeSingle.Printed IS NULL) AND (DocumentCertificates.SignatoryID > 0)
	) AS Charges		) AS Charges
	Group By Charges.~~LabNo~~, Charges.LabCode		Group By Charges.QESTOwnerLabNo, Charges.LabCode
	)		)
	GO		GO
Line 54:		Line 55:
	--Insert WIP filters to use the WIP_Complete views.		--Insert WIP filters to use the WIP_Complete views.
	INSERT INTO DataFilters(FilterGroup, Name, SQL, Locked, SQLEdit, Grouping, Properties, GroupSQL, SearchCriteria)		INSERT INTO DataFilters(FilterGroup, Name, SQL, Locked, SQLEdit, Grouping, Properties, GroupSQL, SearchCriteria)
	VALUES(90000, 'WIP Complete by Project', 'SELECT qestfilterProject_WIP_Complete.ProjectCode, qestfilterProject_WIP_Complete.ProjectName, qestfilterProject_WIP_Complete.ClientName, qestfilterProject_WIP_Complete.WIP FROM qestfilterProject_WIP_Complete', 0, 0, '', '', 'SELECT Count(qestfilterProject_WIP_Complete.WIP) AS WIP_Count, Avg(qestfilterProject_WIP_Complete.WIP) AS WIP_Avg, Sum(qestfilterProject_WIP_Complete.WIP) AS WIP_Sum, Min(qestfilterProject_WIP_Complete.WIP) AS WIP_Min, Max(qestfilterProject_WIP_Complete.WIP) AS WIP_Max, StDev(qestfilterProject_WIP_Complete.WIP) AS WIP_StDevP, Var(qestfilterProject_WIP_Complete.WIP) AS WIP_VarP FROM qestfilterProject_WIP_Complete', '')		VALUES(90000, 'WIP Complete by Project', 'SELECT qestfilterProject_WIP_Complete.ProjectCode, qestfilterProject_WIP_Complete.ProjectName, qestfilterProject_WIP_Complete.ClientName, qestfilterProject_WIP_Complete.WIP FROM qestfilterProject_WIP_Complete WHERE Not QESTOwnerLabNo IS NULL', 0, 0, '', '', 'SELECT Count(qestfilterProject_WIP_Complete.WIP) AS WIP_Count, Avg(qestfilterProject_WIP_Complete.WIP) AS WIP_Avg, Sum(qestfilterProject_WIP_Complete.WIP) AS WIP_Sum, Min(qestfilterProject_WIP_Complete.WIP) AS WIP_Min, Max(qestfilterProject_WIP_Complete.WIP) AS WIP_Max, StDev(qestfilterProject_WIP_Complete.WIP) AS WIP_StDevP, Var(qestfilterProject_WIP_Complete.WIP) AS WIP_VarP FROM qestfilterProject_WIP_Complete WHERE Not QESTOwnerLabNo IS NULL', '')
	GO		GO
	INSERT INTO DataFilters(FilterGroup, Name, SQL, Locked, SQLEdit, Grouping, Properties, GroupSQL, SearchCriteria)		INSERT INTO DataFilters(FilterGroup, Name, SQL, Locked, SQLEdit, Grouping, Properties, GroupSQL, SearchCriteria)
	VALUES(90000, 'WIP Complete by Lab', 'SELECT qestfilterLab_WIP_Complete.~~LabNo~~, qestfilterLab_WIP_Complete.WIP, qestfilterLab_WIP_Complete.LabCode FROM qestfilterLab_WIP_Complete', 0, 0, '', '', 'SELECT Count(qestfilterLab_WIP_Complete.~~LabNo~~) AS LabNo_Count, Avg(qestfilterLab_WIP_Complete.~~LabNo~~) AS LabNo_Avg, Sum(qestfilterLab_WIP_Complete.~~LabNo~~) AS LabNo_Sum, Min(qestfilterLab_WIP_Complete.~~LabNo~~) AS LabNo_Min, Max(qestfilterLab_WIP_Complete.~~LabNo~~) AS LabNo_Max, StDev(qestfilterLab_WIP_Complete.~~LabNo~~) AS LabNo_StDevP, Var(qestfilterLab_WIP_Complete.~~LabNo~~) AS LabNo_VarP, Count(qestfilterLab_WIP_Complete.WIP) AS WIP_Count, Avg(qestfilterLab_WIP_Complete.WIP) AS WIP_Avg, Sum(qestfilterLab_WIP_Complete.WIP) AS WIP_Sum, Min(qestfilterLab_WIP_Complete.WIP) AS WIP_Min, Max(qestfilterLab_WIP_Complete.WIP) AS WIP_Max, StDev(qestfilterLab_WIP_Complete.WIP) AS WIP_StDevP, Var(qestfilterLab_WIP_Complete.WIP) AS WIP_VarP FROM qestfilterLab_WIP_Complete', '')		VALUES(90000, 'WIP Complete by Lab', 'SELECT qestfilterLab_WIP_Complete.QESTOwnerLabNo, qestfilterLab_WIP_Complete.WIP, qestfilterLab_WIP_Complete.LabCode FROM qestfilterLab_WIP_Complete WHERE Not QESTOwnerLabNo IS NULL', 0, 0, '', '', 'SELECT Count(qestfilterLab_WIP_Complete.QESTOwnerLabNo) AS LabNo_Count, Avg(qestfilterLab_WIP_Complete.QESTOwnerLabNo) AS LabNo_Avg, Sum(qestfilterLab_WIP_Complete.QESTOwnerLabNo) AS LabNo_Sum, Min(qestfilterLab_WIP_Complete.QESTOwnerLabNo) AS LabNo_Min, Max(qestfilterLab_WIP_Complete.QESTOwnerLabNo) AS LabNo_Max, StDev(qestfilterLab_WIP_Complete.QESTOwnerLabNo) AS LabNo_StDevP, Var(qestfilterLab_WIP_Complete.QESTOwnerLabNo) AS LabNo_VarP, Count(qestfilterLab_WIP_Complete.WIP) AS WIP_Count, Avg(qestfilterLab_WIP_Complete.WIP) AS WIP_Avg, Sum(qestfilterLab_WIP_Complete.WIP) AS WIP_Sum, Min(qestfilterLab_WIP_Complete.WIP) AS WIP_Min, Max(qestfilterLab_WIP_Complete.WIP) AS WIP_Max, StDev(qestfilterLab_WIP_Complete.WIP) AS WIP_StDevP, Var(qestfilterLab_WIP_Complete.WIP) AS WIP_VarP FROM qestfilterLab_WIP_Complete WHERE Not QESTOwnerLabNo IS NULL', '')
	GO		GO

Line 67:		Line 68:

	--Insert WIP reports to use the WIP_Complete filters		--Insert WIP reports to use the WIP_Complete filters
	INSERT INTO Reports(QestID, QestCreatedBy, QestCreatedDate, QestModifiedBy, QestModifiedDate, QestOwnerLabNo, Name, ReportGroup, FilterName, Title, SubTitle, DocumentNo, PageWidth, PageHeight, Orientation, PageMarginLeft, PageMarginRight, PageMarginTop, PageMarginBottom, ShowLimits, LimitFontBold, LimitFontItalic, LimitFontColour, LimitShadingColour, LineHeight, TitleFontName, TitleFontSize, TitleFontBold, TitleFontItalic, SubTitleFontName, SubTitleFontSize, SubTitleFontBold, SubTitleFontItalic, HeaderFontName, HeaderFontSize, HeaderFontBold, HeaderFontItalic, DetailFontName, DetailFontSize, DetailFontBold, DetailFontItalic, Fields, Locked, StatsOnly, StatsLine, LineEachRow, GroupNewPage, GroupForLastN, LastN, LastNDateField, Chart, ChartSize1, ChartSize2, ChartSize3, ChartSize4, ChartSize5, Properties, CustomReportObject, Columns, AlternateSpecification, AlternateSpecificationName, SuppressSearchCriteria, DifferenceValues) VALUES(90001, 3, CONVERT(DATETIME, '2006-03-01 8:45:50', 102), 0, CONVERT(DATETIME, '2006-03-01 8:45:50', 102), 0, 'Project WIP Complete Report', 'Billing', 'WIP Complete by Project', '', '', '', 21, 29.7, 1, 1, 1, 1, 1, 0, 0, 0, -1, -1, 210, 'Arial', 8.25, 0, 0, 'Arial', 8.25, 0, 0, 'Arial', 8.25, -1, 0, 'Arial', 8.25, 0, 0, '~~LabNo~~,ProjectCode,ProjectName,ClientName,WIP,', 0, 0, 0, 0, 0, 0, '', '', 0, Null, Null, Null, Null, Null, '', Null, 'START[COLUMN1]		INSERT INTO Reports(QestID, QestCreatedBy, QestCreatedDate, QestModifiedBy, QestModifiedDate, QestOwnerLabNo, Name, ReportGroup, FilterName, Title, SubTitle, DocumentNo, PageWidth, PageHeight, Orientation, PageMarginLeft, PageMarginRight, PageMarginTop, PageMarginBottom, ShowLimits, LimitFontBold, LimitFontItalic, LimitFontColour, LimitShadingColour, LineHeight, TitleFontName, TitleFontSize, TitleFontBold, TitleFontItalic, SubTitleFontName, SubTitleFontSize, SubTitleFontBold, SubTitleFontItalic, HeaderFontName, HeaderFontSize, HeaderFontBold, HeaderFontItalic, DetailFontName, DetailFontSize, DetailFontBold, DetailFontItalic, Fields, Locked, StatsOnly, StatsLine, LineEachRow, GroupNewPage, GroupForLastN, LastN, LastNDateField, Chart, ChartSize1, ChartSize2, ChartSize3, ChartSize4, ChartSize5, Properties, CustomReportObject, Columns, AlternateSpecification, AlternateSpecificationName, SuppressSearchCriteria, DifferenceValues) VALUES(90001, 3, CONVERT(DATETIME, '2006-03-01 8:45:50', 102), 0, CONVERT(DATETIME, '2006-03-01 8:45:50', 102), 0, 'Project WIP Complete Report', 'Billing', 'WIP Complete by Project', '', '', '', 21, 29.7, 1, 1, 1, 1, 1, 0, 0, 0, -1, -1, 210, 'Arial', 8.25, 0, 0, 'Arial', 8.25, 0, 0, 'Arial', 8.25, -1, 0, 'Arial', 8.25, 0, 0, 'QESTOwnerLabNo,ProjectCode,ProjectName,ClientName,WIP,', 0, 0, 0, 0, 0, 0, '', '', 0, Null, Null, Null, Null, Null, '', Null, 'START[COLUMN1]
	Header=Project Code		Header=Project Code
	DataFormat=		DataFormat=
Line 194:		Line 195:
	GO		GO

	INSERT INTO Reports(QestID, QestCreatedBy, QestCreatedDate, QestModifiedBy, QestModifiedDate, QestOwnerLabNo, Name, ReportGroup, FilterName, Title, SubTitle, DocumentNo, PageWidth, PageHeight, Orientation, PageMarginLeft, PageMarginRight, PageMarginTop, PageMarginBottom, ShowLimits, LimitFontBold, LimitFontItalic, LimitFontColour, LimitShadingColour, LineHeight, TitleFontName, TitleFontSize, TitleFontBold, TitleFontItalic, SubTitleFontName, SubTitleFontSize, SubTitleFontBold, SubTitleFontItalic, HeaderFontName, HeaderFontSize, HeaderFontBold, HeaderFontItalic, DetailFontName, DetailFontSize, DetailFontBold, DetailFontItalic, Fields, Locked, StatsOnly, StatsLine, LineEachRow, GroupNewPage, GroupForLastN, LastN, LastNDateField, Chart, ChartSize1, ChartSize2, ChartSize3, ChartSize4, ChartSize5, Properties, CustomReportObject, Columns, AlternateSpecification, AlternateSpecificationName, SuppressSearchCriteria, DifferenceValues) VALUES(90001, 3, CONVERT(DATETIME, '2006-03-01 8:46:14', 102), 0, CONVERT(DATETIME, '2006-03-01 14:31:38', 102), 0, 'Lab WIP Complete Report', 'Billing', 'WIP Complete by Lab', '', '', '', 21, 29.7, 1, 1, 1, 1, 1, 0, 0, 0, -1, -1, 210, 'Arial', 8.25, 0, 0, 'Arial', 8.25, 0, 0, 'Arial', 8.25, -1, 0, 'Arial', 8.25, 0, 0, '~~LabNo~~,WIP,LabCode,', 0, 0, 0, 0, 0, 0, '', '', 0, Null, Null, Null, Null, Null, '', Null, 'START[COLUMN1]		INSERT INTO Reports(QestID, QestCreatedBy, QestCreatedDate, QestModifiedBy, QestModifiedDate, QestOwnerLabNo, Name, ReportGroup, FilterName, Title, SubTitle, DocumentNo, PageWidth, PageHeight, Orientation, PageMarginLeft, PageMarginRight, PageMarginTop, PageMarginBottom, ShowLimits, LimitFontBold, LimitFontItalic, LimitFontColour, LimitShadingColour, LineHeight, TitleFontName, TitleFontSize, TitleFontBold, TitleFontItalic, SubTitleFontName, SubTitleFontSize, SubTitleFontBold, SubTitleFontItalic, HeaderFontName, HeaderFontSize, HeaderFontBold, HeaderFontItalic, DetailFontName, DetailFontSize, DetailFontBold, DetailFontItalic, Fields, Locked, StatsOnly, StatsLine, LineEachRow, GroupNewPage, GroupForLastN, LastN, LastNDateField, Chart, ChartSize1, ChartSize2, ChartSize3, ChartSize4, ChartSize5, Properties, CustomReportObject, Columns, AlternateSpecification, AlternateSpecificationName, SuppressSearchCriteria, DifferenceValues) VALUES(90001, 3, CONVERT(DATETIME, '2006-03-01 8:46:14', 102), 0, CONVERT(DATETIME, '2006-03-01 14:31:38', 102), 0, 'Lab WIP Complete Report', 'Billing', 'WIP Complete by Lab', '', '', '', 21, 29.7, 1, 1, 1, 1, 1, 0, 0, 0, -1, -1, 210, 'Arial', 8.25, 0, 0, 'Arial', 8.25, 0, 0, 'Arial', 8.25, -1, 0, 'Arial', 8.25, 0, 0, 'QESTOwnerLabNo,WIP,LabCode,', 0, 0, 0, 0, 0, 0, '', '', 0, Null, Null, Null, Null, Null, '', Null, 'START[COLUMN1]
	Header=Lab No		Header=Lab No
	DataFormat=		DataFormat=
Line 207:		Line 208:
	StatSum=False		StatSum=False
	StatVarCoef=False		StatVarCoef=False
	FieldName=~~LabNo~~		FieldName=QESTOwnerLabNo
	Alignment=0		Alignment=0
	Divider=2		Divider=2

Mark.pritchard at 03:15, 27 April 2006

2006-04-27T03:15:11Z

←Older revision		Revision as of 03:15, 27 April 2006
Line 1:		Line 1:
	<~~nowiki~~>--For a billing document to be picked up, it must have at least one sibling signed test report.		<pre>--For a billing document to be picked up, it must have at least one sibling signed test report.
	--That is, the test report must exist at the same level in the hierarcy as the billing document.		--That is, the test report must exist at the same level in the hierarcy as the billing document.
	--There can be more than one test report, but only one is required to be signed for the billing		--There can be more than one test report, but only one is required to be signed for the billing
Line 289:		Line 289:
	', 0, '', 0, 0)		', 0, '', 0, 0)
	GO		GO
	</~~nowiki~~>		</pre>

Mark.pritchard at 03:14, 27 April 2006

2006-04-27T03:14:26Z

New page

<nowiki>--For a billing document to be picked up, it must have at least one sibling signed test report.
--That is, the test report must exist at the same level in the hierarcy as the billing document.
--There can be more than one test report, but only one is required to be signed for the billing
--document to be picked up.
--For example, for concrete the test report is attached to the concrete sample group, as is the
--billing document. For workorders, one of the test reports (CWD, DDR) is attached to the workorder,
--as is the billing document.
--Note that a billing document is equivalent to a charge document.

--Drop WIP views if were already added:
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'qestfilterProject_WIP_Complete')
DROP VIEW qestfilterProject_WIP_Complete
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'qestfilterLab_WIP_Complete')
DROP VIEW qestfilterLab_WIP_Complete
GO

CREATE VIEW qestfilterProject_WIP_Complete AS
(
SELECT Charges.LabNo, Charges.ProjectCode, Charges.ProjectName, Charges.ClientName, Sum(Charges.WIP) AS WIP FROM
(
SELECT DISTINCT DocumentChargeSingle.QestOwnerLabNo As LabNo, DocumentChargeSingle.ProjectCode, DocumentChargeSingle.ProjectName,
DocumentChargeSingle.ClientName, DocumentChargeSingle.TotalCharge AS WIP, DocumentCharge.QESTUniqueParentID FROM DocumentChargeSingle
INNER JOIN DocumentCharge ON DocumentChargeSingle.QESTUniqueParentID = DocumentCharge.QESTUniqueID
INNER JOIN DocumentCertificates ON (DocumentCharge.QESTUniqueParentID = DocumentCertificates.QESTUniqueParentID AND DocumentCharge.QESTParentID = DocumentCertificates.QESTParentID)
WHERE (DocumentChargeSingle.Printed = 0 OR DocumentChargeSingle.Printed IS NULL) AND (DocumentCertificates.SignatoryID > 0)
) AS Charges
Group By Charges.ProjectCode, Charges.ProjectName, Charges.ClientName, Charges.LabNo
)
GO

CREATE VIEW qestfilterLab_WIP_Complete AS
(
SELECT Charges.LabNo, Sum(Charges.WIP) AS WIP, Charges.LabCode FROM
(
SELECT DISTINCT DocumentChargeSingle.QestOwnerLabNo As LabNo, DocumentChargeSingle.TotalCharge AS WIP,
Laboratory.CounterPrefix AS LabCode, DocumentCharge.QESTUniqueParentID FROM DocumentChargeSingle
INNER JOIN Laboratory On DocumentChargeSingle.QESTOwnerLabNo = Laboratory.LabNo
INNER JOIN DocumentCharge ON DocumentChargeSingle.QESTUniqueParentID = DocumentCharge.QESTUniqueID
INNER JOIN DocumentCertificates ON (DocumentCharge.QESTUniqueParentID = DocumentCertificates.QESTUniqueParentID AND DocumentCharge.QESTParentID = DocumentCertificates.QESTParentID)
WHERE (DocumentChargeSingle.Printed = 0 OR DocumentChargeSingle.Printed IS NULL) AND (DocumentCertificates.SignatoryID > 0)
) AS Charges
Group By Charges.LabNo, Charges.LabCode
)
GO

--Drop filters if were already added:
DELETE FROM DataFilters WHERE Name = 'WIP Complete by Project'
DELETE FROM DataFilters WHERE Name = 'WIP Complete by Lab'
GO

--Insert WIP filters to use the WIP_Complete views.
INSERT INTO DataFilters(FilterGroup, Name, SQL, Locked, SQLEdit, Grouping, Properties, GroupSQL, SearchCriteria)
VALUES(90000, 'WIP Complete by Project', 'SELECT qestfilterProject_WIP_Complete.ProjectCode, qestfilterProject_WIP_Complete.ProjectName, qestfilterProject_WIP_Complete.ClientName, qestfilterProject_WIP_Complete.WIP FROM qestfilterProject_WIP_Complete', 0, 0, '', '', 'SELECT Count(qestfilterProject_WIP_Complete.WIP) AS WIP_Count, Avg(qestfilterProject_WIP_Complete.WIP) AS WIP_Avg, Sum(qestfilterProject_WIP_Complete.WIP) AS WIP_Sum, Min(qestfilterProject_WIP_Complete.WIP) AS WIP_Min, Max(qestfilterProject_WIP_Complete.WIP) AS WIP_Max, StDev(qestfilterProject_WIP_Complete.WIP) AS WIP_StDevP, Var(qestfilterProject_WIP_Complete.WIP) AS WIP_VarP FROM qestfilterProject_WIP_Complete', '')
GO
INSERT INTO DataFilters(FilterGroup, Name, SQL, Locked, SQLEdit, Grouping, Properties, GroupSQL, SearchCriteria)
VALUES(90000, 'WIP Complete by Lab', 'SELECT qestfilterLab_WIP_Complete.LabNo, qestfilterLab_WIP_Complete.WIP, qestfilterLab_WIP_Complete.LabCode FROM qestfilterLab_WIP_Complete', 0, 0, '', '', 'SELECT Count(qestfilterLab_WIP_Complete.LabNo) AS LabNo_Count, Avg(qestfilterLab_WIP_Complete.LabNo) AS LabNo_Avg, Sum(qestfilterLab_WIP_Complete.LabNo) AS LabNo_Sum, Min(qestfilterLab_WIP_Complete.LabNo) AS LabNo_Min, Max(qestfilterLab_WIP_Complete.LabNo) AS LabNo_Max, StDev(qestfilterLab_WIP_Complete.LabNo) AS LabNo_StDevP, Var(qestfilterLab_WIP_Complete.LabNo) AS LabNo_VarP, Count(qestfilterLab_WIP_Complete.WIP) AS WIP_Count, Avg(qestfilterLab_WIP_Complete.WIP) AS WIP_Avg, Sum(qestfilterLab_WIP_Complete.WIP) AS WIP_Sum, Min(qestfilterLab_WIP_Complete.WIP) AS WIP_Min, Max(qestfilterLab_WIP_Complete.WIP) AS WIP_Max, StDev(qestfilterLab_WIP_Complete.WIP) AS WIP_StDevP, Var(qestfilterLab_WIP_Complete.WIP) AS WIP_VarP FROM qestfilterLab_WIP_Complete', '')
GO

--Drop reports if were already added:
DELETE FROM Reports WHERE Name = 'Project WIP Complete Report'
DELETE FROM Reports WHERE Name = 'Lab WIP Complete Report'
GO

--Insert WIP reports to use the WIP_Complete filters
INSERT INTO Reports(QestID, QestCreatedBy, QestCreatedDate, QestModifiedBy, QestModifiedDate, QestOwnerLabNo, Name, ReportGroup, FilterName, Title, SubTitle, DocumentNo, PageWidth, PageHeight, Orientation, PageMarginLeft, PageMarginRight, PageMarginTop, PageMarginBottom, ShowLimits, LimitFontBold, LimitFontItalic, LimitFontColour, LimitShadingColour, LineHeight, TitleFontName, TitleFontSize, TitleFontBold, TitleFontItalic, SubTitleFontName, SubTitleFontSize, SubTitleFontBold, SubTitleFontItalic, HeaderFontName, HeaderFontSize, HeaderFontBold, HeaderFontItalic, DetailFontName, DetailFontSize, DetailFontBold, DetailFontItalic, Fields, Locked, StatsOnly, StatsLine, LineEachRow, GroupNewPage, GroupForLastN, LastN, LastNDateField, Chart, ChartSize1, ChartSize2, ChartSize3, ChartSize4, ChartSize5, Properties, CustomReportObject, Columns, AlternateSpecification, AlternateSpecificationName, SuppressSearchCriteria, DifferenceValues) VALUES(90001, 3, CONVERT(DATETIME, '2006-03-01 8:45:50', 102), 0, CONVERT(DATETIME, '2006-03-01 8:45:50', 102), 0, 'Project WIP Complete Report', 'Billing', 'WIP Complete by Project', '', '', '', 21, 29.7, 1, 1, 1, 1, 1, 0, 0, 0, -1, -1, 210, 'Arial', 8.25, 0, 0, 'Arial', 8.25, 0, 0, 'Arial', 8.25, -1, 0, 'Arial', 8.25, 0, 0, 'LabNo,ProjectCode,ProjectName,ClientName,WIP,', 0, 0, 0, 0, 0, 0, '', '', 0, Null, Null, Null, Null, Null, '', Null, 'START[COLUMN1]
Header=Project Code
DataFormat=
RunningType=0
PrevValues_MaxSize=3
StatNumber=False
Statminimum=False
Statmaximum=False
Stataverage=False
StatStandardDeviation=False
StatSlope=False
StatSum=False
StatVarCoef=False
FieldName=ProjectCode
Alignment=0
Divider=2
Width=2.47619047619048
DefaultFont=True
FontBold=False
FontItalic=False
FontColour=-1
ShadingColour=-1
HighlightMarginal=0,0,0,
HighlightMarginalColour=0
HighlightCritical=0,0,0,
HighlightCriticalColour=0
FootnoteMargPass=
FootnoteMargFail=
FootnoteCritPass=
FootnoteCritFail=
END[COLUMN1]
START[COLUMN2]
Header=Project Name
DataFormat=
RunningType=0
PrevValues_MaxSize=3
StatNumber=False
Statminimum=False
Statmaximum=False
Stataverage=False
StatStandardDeviation=False
StatSlope=False
StatSum=False
StatVarCoef=False
FieldName=ProjectName
Alignment=0
Divider=2
Width=6.52380952380952
DefaultFont=True
FontBold=False
FontItalic=False
FontColour=-1
ShadingColour=-1
HighlightMarginal=0,0,0,
HighlightMarginalColour=0
HighlightCritical=0,0,0,
HighlightCriticalColour=0
FootnoteMargPass=
FootnoteMargFail=
FootnoteCritPass=
FootnoteCritFail=
END[COLUMN2]
START[COLUMN3]
Header=Client Name
DataFormat=
RunningType=0
PrevValues_MaxSize=3
StatNumber=False
Statminimum=False
Statmaximum=False
Stataverage=False
StatStandardDeviation=False
StatSlope=False
StatSum=False
StatVarCoef=False
FieldName=ClientName
Alignment=0
Divider=2
Width=6.47089947089947
DefaultFont=True
FontBold=False
FontItalic=False
FontColour=-1
ShadingColour=-1
HighlightMarginal=0,0,0,
HighlightMarginalColour=0
HighlightCritical=0,0,0,
HighlightCriticalColour=0
FootnoteMargPass=
FootnoteMargFail=
FootnoteCritPass=
FootnoteCritFail=
END[COLUMN3]
START[COLUMN4]
Header=WIP ($)
DataFormat=0.00
RunningType=0
PrevValues_MaxSize=3
StatNumber=False
Statminimum=False
Statmaximum=False
Stataverage=False
StatStandardDeviation=False
StatSlope=False
StatSum=True
StatVarCoef=False
FieldName=WIP
Alignment=1
Divider=1
Width=2.63492063492063
DefaultFont=True
FontBold=False
FontItalic=False
FontColour=-1
ShadingColour=-1
HighlightMarginal=0,0,0,
HighlightMarginalColour=0
HighlightCritical=0,0,0,
HighlightCriticalColour=0
FootnoteMargPass=
FootnoteMargFail=
FootnoteCritPass=
FootnoteCritFail=
END[COLUMN4]
', 0, '', 0, 0)
GO

INSERT INTO Reports(QestID, QestCreatedBy, QestCreatedDate, QestModifiedBy, QestModifiedDate, QestOwnerLabNo, Name, ReportGroup, FilterName, Title, SubTitle, DocumentNo, PageWidth, PageHeight, Orientation, PageMarginLeft, PageMarginRight, PageMarginTop, PageMarginBottom, ShowLimits, LimitFontBold, LimitFontItalic, LimitFontColour, LimitShadingColour, LineHeight, TitleFontName, TitleFontSize, TitleFontBold, TitleFontItalic, SubTitleFontName, SubTitleFontSize, SubTitleFontBold, SubTitleFontItalic, HeaderFontName, HeaderFontSize, HeaderFontBold, HeaderFontItalic, DetailFontName, DetailFontSize, DetailFontBold, DetailFontItalic, Fields, Locked, StatsOnly, StatsLine, LineEachRow, GroupNewPage, GroupForLastN, LastN, LastNDateField, Chart, ChartSize1, ChartSize2, ChartSize3, ChartSize4, ChartSize5, Properties, CustomReportObject, Columns, AlternateSpecification, AlternateSpecificationName, SuppressSearchCriteria, DifferenceValues) VALUES(90001, 3, CONVERT(DATETIME, '2006-03-01 8:46:14', 102), 0, CONVERT(DATETIME, '2006-03-01 14:31:38', 102), 0, 'Lab WIP Complete Report', 'Billing', 'WIP Complete by Lab', '', '', '', 21, 29.7, 1, 1, 1, 1, 1, 0, 0, 0, -1, -1, 210, 'Arial', 8.25, 0, 0, 'Arial', 8.25, 0, 0, 'Arial', 8.25, -1, 0, 'Arial', 8.25, 0, 0, 'LabNo,WIP,LabCode,', 0, 0, 0, 0, 0, 0, '', '', 0, Null, Null, Null, Null, Null, '', Null, 'START[COLUMN1]
Header=Lab No
DataFormat=
RunningType=0
PrevValues_MaxSize=3
StatNumber=False
Statminimum=False
Statmaximum=False
Stataverage=False
StatStandardDeviation=False
StatSlope=False
StatSum=False
StatVarCoef=False
FieldName=LabNo
Alignment=0
Divider=2
Width=1.94708994708995
DefaultFont=True
FontBold=False
FontItalic=False
FontColour=-1
ShadingColour=-1
HighlightMarginal=0,0,0,
HighlightMarginalColour=0
HighlightCritical=0,0,0,
HighlightCriticalColour=0
FootnoteMargPass=
FootnoteMargFail=
FootnoteCritPass=
FootnoteCritFail=
END[COLUMN1]
START[COLUMN2]
Header=Lab Code
DataFormat=
RunningType=0
PrevValues_MaxSize=3
StatNumber=False
Statminimum=False
Statmaximum=False
Stataverage=False
StatStandardDeviation=False
StatSlope=False
StatSum=True
StatVarCoef=False
FieldName=LabCode
Alignment=0
Divider=1
Width=2
DefaultFont=True
FontBold=False
FontItalic=False
FontColour=-1
ShadingColour=-1
HighlightMarginal=
HighlightMarginalColour=0
HighlightCritical=
HighlightCriticalColour=0
FootnoteMargPass=
FootnoteMargFail=
FootnoteCritPass=
FootnoteCritFail=
END[COLUMN2]
START[COLUMN3]
Header=WIP ($)
DataFormat=0.00
RunningType=0
PrevValues_MaxSize=3
StatNumber=False
Statminimum=False
Statmaximum=False
Stataverage=False
StatStandardDeviation=False
StatSlope=False
StatSum=True
StatVarCoef=False
FieldName=WIP
Alignment=1
Divider=1
Width=2.55555555555556
DefaultFont=True
FontBold=False
FontItalic=False
FontColour=-1
ShadingColour=-1
HighlightMarginal=0,0,0,
HighlightMarginalColour=0
HighlightCritical=0,0,0,
HighlightCriticalColour=0
FootnoteMargPass=
FootnoteMargFail=
FootnoteCritPass=
FootnoteCritFail=
END[COLUMN3]
', 0, '', 0, 0)
GO
</nowiki>