My query is like below
select distinct t1.Username as Username,t1.Scope as Scope,t1.WorkArea as Workarea ,t1.ImageCount as ImageCount,t2.DocumentCount as DocCount,t3.ErrorPercentage as Errorpercentage,t4.Errofield As ErrorField,t5.FieldsCount as FieldCount
from
(
select a.scope as Scope,
a.work_area as WorkArea ,a.resources as Username ,
SUM(a.image_count)as ImageCount
from [workalloc] .[dbo].work a
where DATEPART (MM ,a.dat_e )='03' and
DATEPART(YEAR,a.dat_e )='2015' and
a.resources ='Robinson' and
a.scope ='Data' and
a.work_area ='Coding'
group by a.resources , a.scope,a.work_area
union
select a.scope as Scope,
a.work_area as WorkArea,a.resources as Username ,
SUM(a.image_count)as ImageCount
from [workalloc] .[dbo].work a
where DATEPART (MM ,a.dat_e )='03' and
DATEPART(YEAR,a.dat_e )='2015' and
a.resources ='Robinson' and
a.scope ='Data' and
a.work_area ='QC'
group by a.resources , a.scope,a.work_area
union
select a.scope as Scope,
a.work_area as WorkArea,a.resources as Username ,
SUM(a.image_count)as ImageCount
from [workalloc] .[dbo].work a
where DATEPART (MM ,a.dat_e )='03' and
DATEPART(YEAR,a.dat_e )='2015' and
a.resources ='Robinson' and
a.scope ='Data' and
a.work_area ='CC'
group by a.resources , a.scope,a.work_area
union
select a.scope as Scope,
a.work_area as WorkArea,a.resources as Username ,
SUM(a.image_count)as ImageCount
from [workalloc] .[dbo].work a
where DATEPART (MM ,a.dat_e )='03' and
DATEPART(YEAR,a.dat_e )='2015' and
a.resources ='Robinson' and
a.scope ='Data' and
a.work_area ='QA'
group by a.resources , a.scope,a.work_area
union
select a.scope as Scope,
a.work_area as WorkArea,a.resources as Username ,
SUM(a.image_count)as ImageCount
from [workalloc] .[dbo].work a
where DATEPART (MM ,a.dat_e )='03' and
DATEPART(YEAR,a.dat_e )='2015' and
a.resources ='Robinson' and
a.scope ='LDD' and
a.work_area ='Coding'
group by a.resources , a.scope,a.work_area
union
select a.scope as Scope,
a.work_area as WorkArea,a.resources as Username ,
SUM(a.image_count)as ImageCount
from [workalloc] .[dbo].work a
where DATEPART (MM ,a.dat_e )='03' and
DATEPART(YEAR,a.dat_e )='2015' and
a.resources ='Robinson' and
a.scope ='LDD' and
a.work_area ='QC'
group by a.resources , a.scope,a.work_area
Union
Select a.scope as Scope,
a.work_area as WorkArea,a.resources as Username ,
SUM(a.image_count)as ImageCount
from [workalloc] .[dbo].work a
where DATEPART (MM ,a.dat_e )='03' and
DATEPART(YEAR,a.dat_e )='2015' and
a.resources ='Robinson' and
a.scope ='LDD' and
a.work_area ='QA'
group by a.resources , a.scope,a.work_area
UNION
select c.scope as Scope ,c.work_area as WorkArea, c.resources as UserName , sum(c.image_count )as BatesCoding
from workalloc .dbo.work c
where DATEPART(MM ,c.dat_e )='03' and DATEPART(YEAR,c.dat_e )='2015'
and c.resources ='Robinson' and
c.scope ='Page Level Bates Capture' and
c.work_area ='Coding'
group by c.resources , c.scope,c.work_area
union
select c.scope as Scope ,c.work_area as WorkArea, c.resources as UserName , sum(c.image_count )as BatesCoding
from workalloc .dbo.work c
where DATEPART(MM ,c.dat_e )='03' and DATEPART(YEAR,c.dat_e )='2015'
and c.resources ='Robinson' and
c.scope ='Page Level Bates Capture' and
c.work_area ='QC'
group by c.resources , c.scope,c.work_area
union
select c.scope as Scope ,c.work_area as WorkArea, c.resources as UserName , sum(c.image_count )as BatesCoding
from workalloc .dbo.work c
where DATEPART(MM ,c.dat_e )='03' and DATEPART(YEAR,c.dat_e )='2015'
and c.resources ='Robinson' and
c.scope ='Page Level Bates Capture' and
c.work_area ='QA'
group by c.resources , c.scope,c.work_area
union
select c.scope as Scope ,c.work_area as WorkArea, c.resources as UserName , sum(c.image_count )as BatesCoding
from workalloc .dbo.work c
where DATEPART(MM ,c.dat_e )='03' and DATEPART(YEAR,c.dat_e )='2015'
and c.resources ='Robinson' and
c.scope ='Document Level Bates Capture' and
c.work_area ='Coding'
group by c.resources , c.scope,c.work_area
union
select c.scope as Scope ,c.work_area as WorkArea, c.resources as UserName , sum(c.image_count )as BatesCoding
from workalloc .dbo.work c
where DATEPART(MM ,c.dat_e )='03' and DATEPART(YEAR,c.dat_e )='2015'
and c.resources ='Robinson' and
c.scope ='Document Level Bates Capture' and
c.work_area ='QC'
group by c.resources , c.scope,c.work_area
union
select c.scope as Scope ,c.work_area as WorkArea, c.resources as UserName , sum(c.image_count )as BatesCoding
from workalloc .dbo.work c
where DATEPART(MM ,c.dat_e )='03' and DATEPART(YEAR,c.dat_e )='2015'
and c.resources ='Robinson' and
c.scope ='Document Level Bates Capture' and
c.work_area ='QA'
group by c.resources , c.scope,c.work_area
union
select c.scope as Scope ,c.work_area, c.resources,SUM(c.image_count )
from [workalloc] .[dbo] .[work] c
where DATEPART (MM ,c.dat_e )='03' and
DATEPART(YEAR,c.dat_e )='2015' and
c.resources ='Robinson' and
c.scope ='Image Conversion'
group by c.resources , c.scope,c.work_area
union
select c.scope as Scope ,c.work_area,c.resources ,SUM(c.image_count )
from [workalloc] .[dbo] .[work] c
where DATEPART (MM ,c.dat_e )='03' and
DATEPART(YEAR,c.dat_e )='2015' and
c.resources ='Robinson' and
c.scope ='OCR'
group by c.resources , c.scope,c.work_area
union
select c.scope as Scope ,c.work_area,c.resources ,SUM(c.image_count )
from [workalloc] .[dbo] .[work] c
where DATEPART (MM ,c.dat_e )='03' and
DATEPART(YEAR,c.dat_e )='2015' and
c.resources ='Robinson' and
c.scope ='Image Scrolling'
group by c.resources , c.scope,c.work_area
union
select distinct(c.scope) as Scope ,c.work_area ,c.resources ,SUM(c.image_count )
from [workalloc] .[dbo] .[work] c
where DATEPART (MM ,c.dat_e )='03' and
DATEPART(YEAR,c.dat_e )='2015' and
c.resources ='Robinson' and
c.scope ='Image Orientation'
group by c.resources , c.scope,c.work_area
union
select c.scope as Scope ,c.work_area ,c.resources ,SUM(c.image_count )
from [workalloc] .[dbo] .[work] c
where DATEPART (MM ,c.dat_e )='03' and
DATEPART(YEAR,c.dat_e )='2015' and
c.resources ='Robinson' and
c.scope ='Other'
group by c.resources , c.scope,c.work_area
)
as t1
full outer join
(
select a.scope as Scope,
a.work_area as WorkArea ,a.resources as Username ,
SUM(a.doc_count)as DocumentCount
from [workalloc] .[dbo].work a
where DATEPART (MM ,a.dat_e )='03' and
DATEPART(YEAR,a.dat_e )='2015' and
a.resources ='Robinson' and
a.scope ='Data' and
a.work_area ='Coding'
group by a.resources , a.scope,a.work_area
union
select a.scope as Scope,
a.work_area as WorkArea ,a.resources as Username ,
SUM(a.doc_count)as DocumentCount
from [workalloc] .[dbo].work a
where DATEPART (MM ,a.dat_e )='03' and
DATEPART(YEAR,a.dat_e )='2015' and
a.resources ='Robinson' and
a.scope ='Data' and
a.work_area ='QC'
group by a.resources , a.scope,a.work_area
union
select a.scope as Scope,
a.work_area as WorkArea ,a.resources as Username ,
SUM(a.doc_count)as DocumentCount
from [workalloc] .[dbo].work a
where DATEPART (MM ,a.dat_e )='03' and
DATEPART(YEAR,a.dat_e )='2015' and
a.resources ='Robinson' and
a.scope ='Data' and
a.work_area ='CC'
group by a.resources , a.scope,a.work_area
union
select a.scope as Scope,
a.work_area as WorkArea,a.resources as Username ,
SUM(a.doc_count )as DocumentCount
from [workalloc] .[dbo].work a
where DATEPART (MM ,a.dat_e )='03' and
DATEPART(YEAR,a.dat_e )='2015' and
a.resources ='Robinson' and
a.scope ='Data' and
a.work_area ='QA'
group by a.resources , a.scope,a.work_area
union
select a.scope as Scope,
a.work_area as WorkArea,a.resources as Username ,
SUM(a.doc_count )as DocumentCount
from [workalloc] .[dbo].work a
where DATEPART (MM ,a.dat_e )='03' and
DATEPART(YEAR,a.dat_e )='2015' and
a.resources ='Robinson' and
a.scope ='LDD' and
a.work_area ='Coding'
group by a.resources , a.scope,a.work_area
union
select a.scope as Scope,
a.work_area as WorkArea,a.resources as Username ,
SUM(a.doc_count )as DocumentCount
from [workalloc] .[dbo].work a
where DATEPART (MM ,a.dat_e )='03' and
DATEPART(YEAR,a.dat_e )='2015' and
a.resources ='Robinson' and
a.scope ='LDD' and
a.work_area ='QC'
group by a.resources , a.scope,a.work_area
union
Select a.scope as Scope,
a.work_area as WorkArea,a.resources as Username ,
SUM(a.doc_count )as DocumentCount
from [workalloc] .[dbo].work a
where DATEPART (MM ,a.dat_e )='03' and
DATEPART(YEAR,a.dat_e )='2015' and
a.resources ='Robinson' and
a.scope ='LDD' and
a.work_area ='QA'
group by a.resources , a.scope,a.work_area
UNION
select c.scope as Scope ,c.work_area as WorkArea, c.resources as UserName , sum(c.doc_count )as DocumentCount
from workalloc .dbo.work c
where DATEPART(MM ,c.dat_e )='03' and DATEPART(YEAR,c.dat_e )='2015'
and c.resources ='Robinson' and
c.scope ='Document Level Bates Capture' and
c.work_area ='Coding'
group by c.resources , c.scope,c.work_area
union
select c.scope as Scope ,c.work_area as WorkArea, c.resources as UserName , sum(c.doc_count )as DocumentCount
from workalloc .dbo.work c
where DATEPART(MM ,c.dat_e )='03' and DATEPART(YEAR,c.dat_e )='2015'
and c.resources ='Robinson' and
c.scope ='Document Level Bates Capture' and
c.work_area ='QC'
group by c.resources , c.scope,c.work_area
union
select c.scope as Scope ,c.work_area as WorkArea, c.resources as UserName , sum(c.doc_count )as DocumentCount
from workalloc .dbo.work c
where DATEPART(MM ,c.dat_e )='03' and DATEPART(YEAR,c.dat_e )='2015'
and c.resources ='Robinson' and
c.scope ='Document Level Bates Capture' and
c.work_area ='QA'
group by c.resources , c.scope,c.work_area
union
select c.scope as Scope ,c.work_area, c.resources,SUM(c.doc_count) as DocumentCount
from [workalloc] .[dbo] .[work] c
where DATEPART (MM ,c.dat_e )='03' and
DATEPART(YEAR,c.dat_e )='2015' and
c.resources ='Robinson' and
c.scope ='Image Conversion'
group by c.resources , c.scope,c.work_area
union
select c.scope as Scope ,c.work_area,c.resources ,SUM(c.doc_count) as DocumentCount
from [workalloc] .[dbo] .[work] c
where DATEPART (MM ,c.dat_e )='03' and
DATEPART(YEAR,c.dat_e )='2015' and
c.resources ='Robinson' and
c.scope ='OCR'
group by c.resources , c.scope,c.work_area
union
select c.scope as Scope ,c.work_area,c.resources ,SUM(c.doc_count )as DocumentCount
from [workalloc] .[dbo] .[work] c
where DATEPART (MM ,c.dat_e )='03' and
DATEPART(YEAR,c.dat_e )='2015' and
c.resources ='Robinson' and
c.scope ='Image Scrolling'
group by c.resources , c.scope,c.work_area
union
select c.scope as Scope ,c.work_area ,c.resources ,SUM(c.doc_count)as DocumentCount
from [workalloc] .[dbo] .[work] c
where DATEPART (MM ,c.dat_e )='03' and
DATEPART(YEAR,c.dat_e )='2015' and
c.resources ='Robinson' and
c.scope ='Image Orientation'
group by c.resources , c.scope,c.work_area
union
select c.scope as Scope ,c.work_area ,c.resources ,SUM(c.doc_count)as DocumentCount
from [workalloc] .[dbo] .[work] c
where DATEPART (MM ,c.dat_e )='03' and
DATEPART(YEAR,c.dat_e )='2015' and
c.resources ='Robinson' and
c.scope ='Other'
group by c.resources , c.scope,c.work_area
)as t2 on t1.Username =t2.Username and t1.Scope =t2.Scope and t1.WorkArea =t2.WorkArea
full outer join
(
select c.scope ,c.work_area ,b.UserName ,((Sum(a.rejectioncounterid)*1.0/Sum(a.fieldcount))*100) as ErrorPercentage
from [des].[dbo].FieldsCount a,[muser].[dbo].[DTUser]b,[workalloc] .[dbo].[work] c
where a.UserID=b.UserID and b.UserName =c.resources and DATEPART (MM,a.TimeStamp )='02'and DATEPART (YEAR ,a.TimeStamp)='2012'and c.scope='Data' and c.work_area ='Coding' group by b.UserName ,c.scope,c.work_area
union
select c.scope ,c.work_area ,a.UserName ,
(100*Sum(Case when (a.CodingBreak <> a.QCBreak
OR (a.CodingBreak IS NULL AND a.QCBreak IS NOT NULL)
OR (a.CodingBreak IS NOT NULL AND a.QCBreak IS NULL)) then 1 else 0
end)*1.0/NULLIF(Sum(Case when a.QCBreak='C' or a.QCBreak='D' then 1 else 0 end),0)/NULLIF(2,0)) as LDDErrorPercentage
from [MUSER].dbo.LDDImagePath a,[workalloc] .[dbo].[work] c
where a.UserName =c.resources and
DATEPART (MM,a.Date)='02'and DATEPART (YEAR ,a.Date )='2015'and c.scope='LDD' and c.work_area ='Coding' group by a.UserName ,c.scope,c.work_area
) as t3 on t1.Username =t3.UserName and t1.Scope =t3.scope and t1.WorkArea =t3.work_area
full outer join
(
select c.scope,c.work_area ,c.resources as UserName, (Sum(CAST(ISNULL(a.rejectioncounterid,0) AS BIGINT))*1.0/Sum(CAST(ISNULL(a.fieldcount,0) AS BIGINT)))*100 as Errofield from [DES].[dbo].[FieldsCount] a,[workalloc].[dbo].[work] c ,[MUSER] .[dbo].DTUser b where a.UserID =b.UserID and b.UserName =c.resources and DATEPART (MM,a.TimeStamp )='02'and DATEPART (YEAR ,a.TimeStamp )='2015'and c.scope='Data' and c.work_area ='Coding' group by c.resources ,c.scope,c.work_area
union
select c.scope,c.work_area ,c.resources as UserName, Sum(Case when (a.CodingBreak <> a.QCBreak
OR (a.CodingBreak IS NULL AND a.QCBreak IS NOT NULL)
OR (a.CodingBreak IS NOT NULL AND a.QCBreak IS NULL)) then 1 else 0 end) as LDDErrorCount from [muser]. dbo.LDDImagePath a,[workalloc].[dbo].[work] c ,[MUSER] .[dbo].DTUser b where a.UserName =c.resources and DATEPART (MM,a.Date )='02'and DATEPART (YEAR ,a.Date)='2015'and c.scope='LDD' and c.work_area ='Coding' group by c.resources ,c.scope,c.work_area
)as t4 on t1.UserName =t4.UserName and t1.scope =t4.scope and t1.WorkArea =t4.work_area
full outer join
(
select a.scope as Scope ,a.work_area as WorkArea ,a.resources as Username ,sum(a.fieldscount) as FieldsCount
from [workalloc] .[dbo] .[work] a
where DATEPART (MM,a.dat_e )='03' and DATEPART (YEAR ,a.dat_e )='2015' and
a.resources ='Robinson' and
a.scope='Data' and a.work_area ='Coding'
group by a.scope ,a.resources ,a.work_area
union
select a.scope as Scope ,a.work_area as WorkArea ,a.resources as Username ,sum(a.fieldscount) as FieldsCount
from [workalloc] .[dbo] .[work] a
where DATEPART (MM,a.dat_e )='03' and DATEPART (YEAR ,a.dat_e )='2015' and
a.resources ='Robinson' and
a.scope='Data' and a.work_area ='QC'
group by a.scope ,a.resources ,a.work_area
union
select a.scope as Scope ,a.work_area as WorkArea ,a.resources as Username ,sum(a.fieldscount) as FieldsCount
from [workalloc] .[dbo] .[work] a
where DATEPART (MM,a.dat_e )='03' and DATEPART (YEAR ,a.dat_e )='2015' and
a.resources ='Robinson' and
a.scope='Data' and a.work_area ='CC'
group by a.scope ,a.resources ,a.work_area
union
select a.scope as Scope ,a.work_area as WorkArea ,a.resources as Username ,sum(a.fieldscount) as FieldsCount
from [workalloc] .[dbo] .[work] a
where DATEPART (MM,a.dat_e )='03' and DATEPART (YEAR ,a.dat_e )='2015' and
a.resources ='Robinson' and
a.scope='LDD' and a.work_area ='Coding'
group by a.scope ,a.resources ,a.work_area
union
select a.scope as Scope ,a.work_area as WorkArea ,a.resources as Username ,sum(a.fieldscount) as FieldsCount
from [workalloc] .[dbo] .[work] a
where DATEPART (MM,a.dat_e )='03' and DATEPART (YEAR ,a.dat_e )='2015' and
a.resources ='Robinson' and
a.scope='Data' and a.work_area ='QA'
group by a.scope ,a.resources ,a.work_area
union
select a.scope as Scope ,a.work_area as WorkArea ,a.resources as Username ,sum(a.fieldscount) as FieldsCount
from [workalloc] .[dbo] .[work] a
where DATEPART (MM,a.dat_e )='03' and DATEPART (YEAR ,a.dat_e )='2015' and
a.resources ='Robinson' and
a.scope='Page Level Bates Capture' and a.work_area ='Coding'
group by a.scope ,a.resources ,a.work_area
union
select a.scope as Scope ,a.work_area as WorkArea ,a.resources as Username ,sum(a.fieldscount) as FieldsCount
from [workalloc] .[dbo] .[work] a
where DATEPART (MM,a.dat_e )='03' and DATEPART (YEAR ,a.dat_e )='2015' and
a.resources ='Robinson' and
a.scope='Page Level Bates Capture' and a.work_area ='QC'
group by a.scope ,a.resources ,a.work_area
union
select a.scope as Scope ,a.work_area as WorkArea ,a.resources as Username ,sum(a.fieldscount) as FieldsCount
from [workalloc] .[dbo] .[work] a
where DATEPART (MM,a.dat_e )='03' and DATEPART (YEAR ,a.dat_e )='2015' and
a.resources ='Robinson' and
a.scope='Page Level Bates Capture' and a.work_area ='QA'
group by a.scope ,a.resources ,a.work_area
union
select a.scope as Scope ,a.work_area as WorkArea ,a.resources as Username ,sum(a.fieldscount) as FieldsCount
from [workalloc] .[dbo] .[work] a
where DATEPART (MM,a.dat_e )='03' and DATEPART (YEAR ,a.dat_e )='2015' and
a.resources ='Robinson' and
a.scope='Document Level Bates Capture' and a.work_area ='Coding'
group by a.scope ,a.resources ,a.work_area
union
select a.scope as Scope ,a.work_area as WorkArea ,a.resources as Username ,sum(a.fieldscount) as FieldsCount
from [workalloc] .[dbo] .[work] a
where DATEPART (MM,a.dat_e )='03' and DATEPART (YEAR ,a.dat_e )='2015' and
a.resources ='Robinson' and
a.scope='Document Level Bates Capture' and a.work_area ='QC'
group by a.scope ,a.resources ,a.work_area
union
select a.scope as Scope ,a.work_area as WorkArea ,a.resources as Username ,sum(a.fieldscount) as FieldsCount
from [workalloc] .[dbo] .[work] a
where DATEPART (MM,a.dat_e )='03' and DATEPART (YEAR ,a.dat_e )='2015' and
a.resources ='Robinson' and
a.scope='Page Level Bates Capture' and a.work_area ='QA'
group by a.scope ,a.resources ,a.work_area
)as t5 on t1.UserName =t5.Username and t1.scope=t5.Scope and t1.WorkArea =t5.WorkArea
I want to add this procedure to the above query how can i do that
DECLARE @dd TABLE (Datee DateTime,Username VARCHAR(250), WorkArea VARCHAR(50), Scope VARCHAR(50), Quality INT)
insert into @dd (Datee,Username ,WorkArea ,Scope ,Quality)select a.dat_e ,a.resources,a.work_area,a.scope, a.Quality from [workalloc] .[dbo] .work a where scope='Data' and work_area='QA'and resources ='Robinson' or resources like '%,Robinson%' or resources like '%Robinson,%'
select * from @dd
;WITH UserTotals AS
(
SELECT Scope,WorkArea,Datee,LEFT(Username, CHARINDEX(',', Username )-1) AS SingleUser, Quality, RIGHT(Username, LEN(Username) - CHARINDEX(',', Username)) AS Remainder
FROM @dd
WHERE CHARINDEX(',', Username)>0
union all
select Scope,WorkArea,Datee, Username as SingleUser,Quality, NULL as Remainder
FROM @dd
WHERE CHARINDEX(',',Username )=0
UNION ALL
SELECT Scope,WorkArea,Datee,LEFT(Remainder, CHARINDEX(',', Remainder)-1) AS SingleUser, Quality, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
FROM UserTotals
WHERE CHARINDEX(',', Remainder)>0
UNION ALL
SELECT Scope,WorkArea,Datee,Remainder AS SingleUser, Quality, NULL AS Remainder
FROM UserTotals
WHERE CHARINDEX(',', Remainder)=0
)
select UserTotals.Datee ,singleuser, sum(Quality)/COUNT(SingleUser ) as QualityPercentage, 100-sum(Quality)/COUNT(SingleUser ) as ErrorPercentage
from UserTotals
where DATEPART(MM,UserTotals.Datee)=03 and DATEPART(YEAR,usertotals.Datee )=2015 and Scope ='Data' and WorkArea='QA' and SingleUser ='Robinson'
group by SingleUser ,Datee