We want to add a column to the ad-hoc report for minimum (first) check date by clientcode where auto_create_payroll = YES
from [IM_STAGE].[IM].[vw_BETI_EE_Check_Approval]The query that I have.
This query gives me an error of: Invalid column name 'first_check_date'
SELECT
fin.client_bid,
CASE
WHEN family_bid Is Null
THEN fin.Client_bid
ELSE family_bid
END AS 'client family code',
Mastercode,
Client_Status,
CRR_fullname,
CRR_Mgr_fullName,
CRR_Rg_Mgr_fullname,
Temp_CRR_fullname,
PSD_Specialist_FullName,
PSD_TeamLead_FullName,
PSD_Supervisor_FullName,
TSR_PE,
TSR_PR_Manager,
TSR_Regional_Manager_PE,
firstenabled_dt,
check_date_maximum,
rolleddate,
annualrevenue,
CASE WHEN has_client_auto_created > 0 THEN 'Yes' ELSE 'No' END AS has_client_auto_created_ever,
CASE WHEN Beti_approvals + Beti_waived = 0 THEN 0 ELSE Beti_approvals / (Beti_approvals + Beti_waived) END AS 'AMC_pct',
first_check_date
from
(
SELECT
c.Client_bid,
ISNULL(vf.clientcode_fam,'') AS family_bid,
c.mastercode as Mastercode,
c.Client_Status,
c.CRR_Rg_Mgr_fullname,
c.CRR_Mgr_fullName,
c.CRR_fullname,
c.Temp_CRR_fullname,
c.PSD_Supervisor_FullName,
c.PSD_TeamLead_FullName,
c.PSD_Specialist_FullName,
pe.[157] AS TSR_PE,
pe.[158] AS TSR_PR_Manager,
pe.[159] AS TSR_Regional_Manager_PE,
SUM(CASE WHEN auto_create_payroll = 'Yes' THEN 1 ELSE 0 END) AS has_client_auto_created,
prod.firstenabled_dt,
c.Check_Date_Maximum,
bt.bookeddate as rolleddate,
c.annualrevenue,
ISNULL(CAST(SUM(b.approved_check_approvals) AS decimal),0) AS Beti_approvals,
ISNULL(CAST(SUM(b.admin_waived_check_approvals) AS decimal),0) AS Beti_waived,
CASE WHEN auto_create_payroll = 'Yes' THEN MIN(b.checkdate) ELSE ' ' END AS first_check_date
FROM
(
SELECT
isb.client_bid,
MAX(isb.proposalid) AS proposalid,
isb.procstatus,
isb.prodcodes AS products_sold,
isb.channel,
CAST(MAX(isb.bookeddate) AS DATE) AS bookeddate,
MAX(isb.initcheckdate) AS initcheckdate,
isb.salewhom_ofid
FROM
IM_STAGE.crm.stg_pci_sale_booked isb
LEFT JOIN
IM_STAGE.crm.pci_sale_whom isw
ON
isb.saleid = isw.saleid AND
isb.staffid = isw.staffid
WHERE
isb.client_bid <> '' AND
isb.procstatus IN ('FIRSTPAY','LINK','NOSTART','PASTPAY','STARTED') AND
isw.roleid = 9 AND
prodcodes = 'BET' AND
channel = 'CRR'
GROUP BY
isb.client_bid,
isb.procstatus,
isb.prodcodes,
isb.channel,
isb.salewhom_ofid
) bt
INNER JOIN
IM_STAGE.im.vw_client c
on bt.Client_bid = c.Client_bid
LEFT JOIN
IM_STAGE.IM.vw_family vf
ON
c.Family_Id = vf.Family_Id
LEFT OUTER JOIN
IM_STAGE.im.vw_BETI_EE_Check_Approval b
ON c.Client_bid = b.Client_bid
left outer join(
SELECT crmid, [157], [158], [159]
FROM
(
SELECT
ccs.crmid,
ccs.roleid,
ss.FullName
FROM IM_STAGE.crm.crm_client_staff ccs
LEFT JOIN IM_STAGE.crm.stg_staff ss
ON ccs.staffid = ss.staffid
WHERE ccs.roleid IN (157,158,159)
) AS src
PIVOT
(
MAX(FullName)
FOR roleid IN ([157], [158], [159])
) AS pvt
) pe
ON
pe.crmid = c.crmid
LEFT JOIN (SELECT firstenabled_dt,
client_bid,
prodid
FROM [IM_STAGE].[crm].[stg_client_product]
WHERE prodid = 101) AS prod
ON prod.client_bid = IIF(c.multi_client = 1, CONCAT(c.proc_city,' ',c.mastercode), c.client_bid)
WHERE bt.bookeddate between '2021-05-01' and CURRENT_TIMESTAMP and auto_create_payroll = 'Yes'
GROUP BY
c.Client_bid,
CASE
WHEN vf.clientcode_fam Is Null
THEN c.Client_bid
ELSE vf.clientcode_fam
END,
vf.clientcode_fam,
c.mastercode,
c.Client_Status,
c.CRR_Rg_Mgr_fullname,
c.CRR_Mgr_fullName,
c.CRR_fullname,
c.Temp_CRR_fullname,
c.PSD_Supervisor_FullName,
c.PSD_TeamLead_FullName,
c.PSD_Specialist_FullName,
pe.[157],
pe.[158],
pe.[159],
prod.firstenabled_dt,
c.Check_Date_Maximum,
bt.bookeddate,
c.annualrevenue,
first_check_date
) fin
ORDER BY rolleddate desc
