Reports: Determine what reports are missing and create

[esteban-gs]

On ActivitiesESLAttendance report, ADD: Ls.[key] = 'Computer Class'

Reason: Casa's Education coordinator wants computer class to be included in this.

[esteban-gs]

New report used for employer follow-up.

`--completed work orders and employer emails from time range- --Used for follow-up

declare @sign_in_date_begin date declare @sign_in_date_end date

set @sign_in_date_begin= '2018-8-1' set @sign_in_date_end='2018-9-1';

SELECT distinct(a.name )as [Employer Name] , a.email as [Email] --, b.paperordernum as [Order] --, b.statusEN as [WO status] --, b.datetimeofwork as [Date-time] , count(d.id) as [workers Hired]

FROM employers a

inner join workorders b on a.id=b.employerid inner join workassignments c on b.paperordernum=c.workorderid inner join persons d on c.workerassignedid=d.id

WHERE b.datetimeofwork >= @sign_in_date_begin AND b.datetimeofwork < @sign_in_date_end AND b.status = 44 AND a.email IS NOT NULL

group by a.name , a.email

order by count(d.id) desc

`

[esteban-gs]

New report used to pull a list of employers who hire workers in a certain program.

`--cEmployer contact information for completed orders by program

declare @program_employer_contact_date_begin date declare @program_employer_contact_date_end date declare @program_employer_contact_program varchar(60)

set @program_employer_contact_date_begin= '2018-8-1' set @program_employer_contact_date_end='2018-9-1' set @program_employer_contact_program = 'dwc';

SELECT distinct(a.name) as [Employer Name] ,a.phone as [Primary Phone] ,a.[cellphone] as [Secondary Phone] , a.email as [Email] , a.zipcode as [ZipCode] , e.typeOfWork as [At Least 1 worker in] --, d.[fullName] --, b.paperordernum as [Order] --, b.statusEN as [WO status] --, b.datetimeofwork as [Date-time] --, count(d.id) as [workers Hired]

FROM employers a inner join workorders b on a.id=b.employerid inner join workassignments c on b.paperordernum=c.workorderid inner join persons d on c.workerassignedid=d.id inner join workers e on e.id=d.id

WHERE b.datetimeofwork >= @program_employer_contact_date_begin AND b.datetimeofwork < @program_employer_contact_date_end AND b.status = 44 --AND a.email IS NOT NULL AND e.typeOfWork = @program_employer_contact_program

order by a.name asc`

[esteban-gs]

New report: Online Work Orders by status with corresponding fees and dispatches generated (after hire online V2)

`--WO from online source numbers post V2

USE [machete_casa] GO

declare @online_source_date_begin date declare @online_source__date_end date declare @online_source_woStatus int

set @online_source_date_begin = '2017-7-1' set @online_source__date_end ='2018-9-1'; set @online_source_woStatus = '44' --44 is completed, 45 is cancelled

SELECT w.[paperOrderNum] as [Order#] , count(wa.id) as [Hired] , w.[onlineSource] as [Online?] , CONVERT(varchar, w.[dateTimeofWork], 7) as [Date] , w.[transportMethodEN] as [Trans. Method] , format(w.[transportFee], '$#,###.##') as [Trans. Fee] , format(w.[ppFee], '$#,###.##') as [ppFee] , w.[ppState] , (select format(sum(w.[ppFee]), '$#,###.##') FROM [WorkOrders] w where w.OnlineSource=1 And w.[dateTimeofWork] between @online_source_date_begin and @online_source__date_end AND w.[status] = @online_source_woStatus ) as [ppFeeTotal] , (select count(wa.[ID]) FROM [WorkAssignments] wa join [WorkOrders] w on wa.workorderid=w.id where w.OnlineSource=1 And w.[dateTimeofWork] between @online_source_date_begin and @online_source__date_end AND w.[status] = @online_source_woStatus ) as [Total Hired Workers]

FROM [WorkOrders] w join [WorkAssignments] wa on wa.workorderid=w.id

where w.OnlineSource=1 And w.[dateTimeofWork] between @online_source_date_begin and @online_source__date_end AND w.[status] = @online_source_woStatus --order by w.[dateTimeofWork] desc

group by w.[paperOrderNum] , w.[onlineSource] , w.[dateTimeofWork] , w.[transportMethodEN] , w.[transportFee] , w.[ppFee] , w.[ppState] --, (select sum([ppFee]) FROM [WorkOrders]) as [ppFeeTotal]

order by w.[dateTimeofWork] `

[esteban-gs]

New report: Online Work Orders by status with corresponding fees and dispatches generated (Before hire online V2)

` --WO from online source numbers pre V2

USE [machete_casa] GO

declare @online_source_date_begin date declare @online_source__date_end date declare @online_source_woStatus int

set @online_source_date_begin = '2011-1-1' set @online_source__date_end ='2017-7-1'; set @online_source_woStatus = '44' --44 is completed, 45 is cancelled

SELECT w.[paperOrderNum] as [Order#] , count(wa.id) as [Hired] , w.[onlineSource] as [Online?] , CONVERT(varchar, w.[dateTimeofWork], 7) as [Date] , w.[transportMethodEN] as [Trans. Method] , format(w.[transportFee], '$#,###.##') as [Trans. Fee] , format(w.[transportFeeExtra], '$#,###.##') as [Extra Fee] , w.[ppState] , (select format(sum(w.[transportFee])+sum(w.[transportFeeExtra]), '$#,###.##') FROM [WorkOrders] w where w.OnlineSource=1 And w.[dateTimeofWork] between @online_source_date_begin and @online_source__date_end AND w.[status] = @online_source_woStatus ) as [Old FeeTotal] , (select count(wa.[ID]) FROM [WorkAssignments] wa join [WorkOrders] w on wa.workorderid=w.id where w.OnlineSource=1 And w.[dateTimeofWork] between @online_source_date_begin and @online_source__date_end AND w.[status] = @online_source_woStatus ) as [Total Hired Workers] , w.[Createdby] as [Source]

FROM [WorkOrders] w join [WorkAssignments] wa on wa.workorderid=w.id

where w.OnlineSource=1 And w.[dateTimeofWork] between @online_source_date_begin and @online_source__date_end AND w.[status] = @online_source_woStatus --order by w.[dateTimeofWork] desc

group by w.[paperOrderNum] , w.[onlineSource] , w.[dateTimeofWork] , w.[transportMethodEN] , w.[transportFee] , w.[transportFeeExtra] , w.[ppState] , w.[Createdby] --, (select sum([ppFee]) FROM [WorkOrders]) as [ppFeeTotal]

order by w.[dateTimeofWork]

--GO --select * from workorders

`

[esteban-gs]

New Report: which workers have a specific skills, search by skill name

`--which workers have a specific skills, search by skill name

USE machete_casa GO

declare @input_skill varchar(60) declare @like varchar(60)

set @input_skill = 'paint' set @like = '%'+ @input_skill +'%';

SELECT w.dwccardnum, fullNameAndId, skill1, l1.text_en as [Skill1], skill2, l2.text_en as [Skill2], skill3, l3.text_en as [Skill3] from workers w join lookups l1 on l1.id = w.skill1 join lookups l2 on l2.id = w.skill2 join lookups l3 on l3.id = w.skill3 where (l1.text_en like @like OR l2.text_en like @like OR l3.text_en like @like)

GO`

[esteban-gs]

Add to existing report: MemberAttendanceMetrics same as original, but adds the sign in count.

`-- same as original, but adds the sign in count.

USE machete_casa GO

declare @sign_in_date_begin date declare @sign_in_date_end date

set @sign_in_date_begin= '2018-7-1' set @sign_in_date_end='2018-8-1';

with jobs (dwccardnum, Jobcount) as ( SELECT dwccardnum, count() as [Jobcount] from dbo.WorkAssignments WAs JOIN dbo.WorkOrders WOs ON WAs.workOrderID = WOs.ID JOIN dbo.Workers Ws on WAs.workerAssignedID = Ws.ID join dbo.lookups l on l.id = wos.status WHERE dateTimeofWork >= @sign_in_date_begin and dateTimeofWork < @sign_in_date_end and l.text_EN = 'Completed' group by dwccardnum ), act (dwccardnum, actcount) as ( select dwccardnum, count() as [actcount] from activitysignins asi where dateforsignin >= @sign_in_date_begin and dateforsignin < @sign_in_date_end group by dwccardnum ), esl (dwccardnum, eslcount) as ( select asi1.dwccardnum, count(*) as [eslcount] from activitysignins asi1 join activities aa on aa.ID = asi1.activityid

where aa.nameen in ('English Class 1', 'English Class 2', 'Somos Vecinos')
and asi1.dateforsignin >= @sign_in_date_begin
and asi1.dateforsignin < @sign_in_date_end
group by asi1.dwccardnum

),

signins (dwccardnum, signincount) as (

select wsi.dwccardnum, count(*) as [signincount]
from WorkerSignins wsi

Where wsi.dateforsignin >= @sign_in_date_begin
and wsi.dateforsignin < @sign_in_date_end
group by wsi.dwccardnum

),

cardnums (dwccardnum) as ( select dwccardnum from jobs union select dwccardnum from act union select dwccardnum from esl union select dwccardnum from signins

) select distinct(cn.dwccardnum) [Member number] , p.fullname [Member name] , case when w.homeless = 0 then 'no' when w.homeless is null then 'unknown' else 'yes' end as [Homeless] , cast(isnull([signincount],0) as int) as [Sign Ins] , cast(isnull([jobcount],0) as int) as [Dispatches] , cast(isnull([actcount],0) as int) as [Activities] , cast(isnull([eslcount],0) as int) as [ESL]

from cardnums cn join workers w on cn.dwccardnum = w.dwccardnum join persons p on w.id = p.id left join jobs on cn.dwccardnum = jobs.dwccardnum left join act on jobs.dwccardnum = act.dwccardnum left join esl on esl.dwccardnum = jobs.dwccardnum left join signins on signins.dwccardnum = jobs.dwccardnum

where jobcount is not null or actcount is not null or eslcount is not null `

[esteban-gs]

@chaim1221 ☝️