QESTLab AUCOF WIP: Difference between revisions
No edit summary |
David.murphy (talk | contribs) m Categorised |
||
| Line 257: | Line 257: | ||
', 0, '', 0, 0) | ', 0, '', 0, 0) | ||
</pre> | </pre> | ||
[[Category:Coffey Pages|{{PAGENAME}}]] | |||
[[Category:QESTLab|{{PAGENAME}}]] | |||
Latest revision as of 01:24, 20 July 2007
--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)