Jump to content

QESTLab AUCOF WIP

From QESTonline
Revision as of 00:26, 24 October 2006 by Mark.pritchard (talk | contribs)
(diff) ←Older revision | view current revision (diff) | Newer revision→ (diff)
--Drop WIP views if they were already added:
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'qestfilterProject_WIP')
  DROP VIEW qestfilterProject_WIP
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'qestfilterLab_WIP')
  DROP VIEW qestfilterLab_WIP
GO

CREATE VIEW qestfilterProject_WIP AS 
(
	SELECT QestOwnerLabNo, ProjectCode, ProjectName, ClientName, Sum(TotalCharge) AS WIP FROM DocumentChargeSingle WHERE Printed = 0 OR Printed IS NULL Group By ProjectCode, ProjectName, ClientName, QESTOwnerLabNo
)
GO

CREATE VIEW qestfilterLab_WIP AS 
(
	SELECT QestOwnerLabNo, Sum(TotalCharge) AS WIP, CounterPrefix AS LabCode FROM DocumentChargeSingle Inner Join Laboratory On DocumentChargeSingle.QESTOwnerLabNo = Laboratory.LabNo WHERE Printed = 0 OR Printed IS NULL Group By QESTOwnerLabNo, CounterPrefix
)
GO

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

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

--Drop reports if were already added:
DELETE FROM Reports WHERE Name = 'Project WIP Report'
DELETE FROM Reports WHERE Name = 'Lab WIP Report'
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, '2005-12-06 11:30:50', 102), 0, CONVERT(DATETIME, '2005-12-06 11:55:12', 102), 0, 'Project WIP Report', 'Billing', 'WIP 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
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)
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, '2005-12-06 11:55:14', 102), 0, CONVERT(DATETIME, '2005-12-06 14:31:38', 102), 0, 'Lab WIP Report', 'Billing', 'WIP 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
DataFormat=
RunningType=0
PrevValues_MaxSize=3
StatNumber=False
Statminimum=False
Statmaximum=False
Stataverage=False
StatStandardDeviation=False
StatSlope=False
StatSum=False
StatVarCoef=False
FieldName=QESTOwnerLabNo
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)
Retrieved from "http://online.spectraqest.com/index.php?title=QESTLab_AUCOF_WIP&oldid=12592"