Help with WTD, MTD, and YTD

[fmrock]

I have a query that I am using in a crystal report to do the WTD, MTD, and YTD. I would like to get rid of doing that in Crystal and do it in the SQL instead.

SELECT 
	A.ACCTCPCODE "Site", 
	L.LOCNAME "Location_Name",	
	A.ACCTCODE "Account", 
	CASE
	  WHEN (C.TYPE='C') THEN 'Charges'
	  WHEN ((C.TYPE='P') AND (C.PRCODE IN ('90','92'))) THEN 'Patient Payments'
	  WHEN ((C.TYPE='P') AND (C.PRCODE='93')) THEN 'Credit Card Payments'
	  WHEN ((C.TYPE='P') AND (C.PRCODE NOT IN ('90','92','93','99'))) THEN 'Payments'
	  WHEN ((C.TYPE='P') AND (C.PRCODE='99')) THEN 'Refund Payments'
	  WHEN (C.TYPE='A') THEN 'Adjustments'
	  ELSE C.TYPE END	"Type",	
	C.PRAMOUNT "PR_Amount",
	to_date(C.ORIGPOSTDATE,'j') "Post_Date"
FROM   
	MEDACCOUNT A
	JOIN MEDCHARGES C ON ((A.ACCTCPCODE=C.CPCODE) AND (A.ACCTCODE=C.ACCOUNT) AND (C.SPLITFLAG IS NULL))
	LEFT JOIN MEDLOCATIONS L ON ((C.CPCODE=L.LOCCPCODE) AND (C.DOCLOC=L.LOCCODE))
WHERE   
	(A.ACCTCPCODE IN ('116001','116002','116003'))
	AND C.PRCODE not in ('4084F', '3120F', '2010F', '3028F', '2014F', '4045F')
	AND (C.ORIGPOSTDATE >= to_char(TRUNC(SYSDATE, 'YYYY'),'j'))

The columns I would like to see in the report are

Site
Location Name
Type
Amount Posted Yesterday
# of Accounts Yesterday (Distinct Count)
WTD Amount
WTD Accounts (Distinct)
MTD Amount
MTD Accounts (Distinct)
YTD Amount
YTD Accounts (Distinct)

What would best sum the PR_Amount into these buckets?

 

[Dagon]

What do you mean by WTD, MTD and YTD ? Are these Week to Date, Month to Date and Year to Date ? If so, one technique would be to use a sum(case...):

SQL> create table trans (id number, trans_date date, amount number);

Table created.

SQL> insert into trans values (1, to_date('31-dec-2009', 'dd-mon-yyyy'), 500);

1 row created.
 
SQL> insert into trans values (1, to_date('01-jan-2010', 'dd-mon-yyyy'), 600);

1 row created.

SQL> insert into trans values (1, to_date('01-feb-2010', 'dd-mon-yyyy'), 700);

1 row created.
 
SQL> insert into trans values (1, to_date('17-feb-2010', 'dd-mon-yyyy'), 800);

1 row created.
 
SQL> select sum(case when trans_date >= trunc(sysdate, 'YYYY') then amount end) as ytd,
  2         sum(case when trans_date >= trunc(sysdate, 'mm') then amount end) as mtd,
  3         sum(case when trans_date >= trunc(sysdate, 'ww') then amount end) as wtd
  4  from trans
  5  /

       YTD        MTD        WTD
---------- ---------- ----------
      2100       1500        800

I've no idea what you mean by distinct YTD etc. Do you mean distinct amounts or distinct dates or something else ?


Retired (not by choice) Oracle contractor.

 

[cmmrfrds]

I hesitate to comment where I can't test out the code, but I can give you an example of something similiar. It is the code for a materialized view that is used by some Crystal Report writers. It uses Analytical Functions to create summaries at various levels. It also has a sequence number so that the report writers can pick the summaries off the 1st row if they want.

Example.
SELECT
Post_Period,
DEPT_ID,
PERFORMING_PROV_ID,
PAYOR,
ROW_NUM,
TX_ID,
REV_LOC_ID,
MAX(POS_ID) AS POS_ID,
MAX(LOC_ID) AS LOC_ID,
MAX(ACCOUNT_ID) AS ACCOUNT_ID,
MAX(BILLING_PROV_ID) AS BILLING_PROV_ID,
MAX(TX_NUM) AS TX_NUM,
MAX(CHARGE_SLIP_NUMBER) AS CHARGE_SLIP_NUMBER,
MAX(MODIFIER_ONE) AS MODIFIER_ONE,
MAX(MODIFIER_TWO) AS MODIFIER_TWO,
MAX(ORIG_SERVICE_DATE) AS ORIG_SERVICE_DATE,
MAX(ORIG_POST_DATE) AS ORIG_POST_DATE,
MAX(INT_PAT_ID) AS INT_PAT_ID,
MAX(PAT_NAME) AS PAT_NAME,
MAX(MRN_ID) AS MRN_ID,
MAX(CPT_CODE) AS CPT_CODE,
MAX(PROC_ID) PROC_ID,
Sum(PROC_QTY) AS PROC_QTY,
Sum(Charge_Amt) AS CHARGE_AMT,
SUM(SUM(CHARGE_AMT)) OVER (PARTITION BY POST_PERIOD, PERFORMING_PROV_ID) AS PROV_AMT,
SUM(SUM(CHARGE_AMT)) OVER (PARTITION BY POST_PERIOD, DEPT_ID) AS DEPT_AMT,
SUM(SUM(CHARGE_AMT)) OVER (PARTITION BY POST_PERIOD, REV_LOC_ID) AS LOC_AMT,
SUM(SUM(CHARGE_AMT)) OVER (PARTITION BY POST_PERIOD) AS PERIOD_AMT
FROM
(
SELECT
To_Char(CLARITY_TDL_TRAN.Post_date,'YYYYMM') as Post_Period,
CLARITY_TDL_TRAN.DEPT_ID,
CLARITY_DEP.REV_LOC_ID as REV_LOC_ID,
CLARITY_TDL_TRAN.PERFORMING_PROV_ID AS PERFORMING_PROV_ID,
CLARITY_TDL_TRAN.TX_ID,
CLARITY_TDL_TRAN.POS_ID,
CLARITY_TDL_TRAN.LOC_ID,
CLARITY_TDL_TRAN.ACCOUNT_ID,
CLARITY_TDL_TRAN.REASON_CODE_ID,
CLARITY_TDL_TRAN.BILLING_PROVIDER_ID AS BILLING_PROV_ID,
CLARITY_TDL_TRAN.TX_NUM,
CLARITY_TDL_TRAN.CHARGE_SLIP_NUMBER AS CHARGE_SLIP_NUMBER,
PROCEDURE_QUANTITY AS PROC_QTY,
AMOUNT AS CHARGE_AMT,
CLARITY_TDL_TRAN.MODIFIER_ONE AS MODIFIER_ONE,
CLARITY_TDL_TRAN.MODIFIER_TWO AS MODIFIER_TWO,
CLARITY_TDL_TRAN.ORIG_SERVICE_DATE AS ORIG_SERVICE_DATE,
CLARITY_TDL_TRAN.ORIG_POST_DATE AS ORIG_POST_DATE,
CLARITY_TDL_TRAN.INT_PAT_ID AS INT_PAT_ID,
PATIENT.PAT_NAME AS PAT_NAME,
PATIENT.PAT_MRN_ID AS MRN_ID,
CLARITY_TDL_TRAN.CPT_CODE AS CPT_CODE,
CLARITY_TDL_TRAN.PROC_ID AS PROC_ID,
(CASE WHEN (DETAIL_TYPE = 1 OR DETAIL_TYPE = 10) AND INSURANCE_AMOUNT <> 0 THEN ORIGINAL_PAYOR_ID
ELSE 1 END) AS PAYOR,
ROW_NUMBER() OVER (PARTITION BY To_Char(CLARITY_TDL_TRAN.Post_date,'YYYYMM'), CLARITY_TDL_TRAN.DEPT_ID,
CLARITY_TDL_TRAN.PERFORMING_PROV_ID
ORDER BY To_Char(CLARITY_TDL_TRAN.Post_date,'YYYYMM') ASC, CLARITY_TDL_TRAN.DEPT_ID ASC,
CLARITY_TDL_TRAN.PERFORMING_PROV_ID ASC) AS ROW_NUM
FROM CLARITY.CLARITY_TDL_TRAN CLARITY_TDL_TRAN
INNER JOIN CLARITY.CLARITY_SER CLARITY_SER
ON CLARITY_TDL_TRAN.PERFORMING_PROV_ID=CLARITY_SER.PROV_ID
LEFT JOIN CLARITY.CLARITY_DEP CLARITY_DEP
ON CLARITY_TDL_TRAN.DEPT_ID = CLARITY_DEP.DEPARTMENT_ID
INNER JOIN CLARITY.PATIENT PATIENT
ON CLARITY_TDL_TRAN.INT_PAT_ID=PATIENT.PAT_ID
WHERE post_date >= trunc(last_day(add_months(sysdate, -14))+1)
AND (CLARITY_TDL_TRAN.DETAIL_TYPE IN (1, 10))
) InView
GROUP BY
POST_PERIOD,
REV_LOC_ID,
DEPT_ID,
PERFORMING_PROV_ID,
PAYOR,
TX_ID,
ROW_NUM;