Need Help With Aging T-Script

Jun 28, 2007

I am tring to create a 30 60 90 day aging in my script I am getting errors  at the bold code below can some on look at this and tell me what I am doing wrong or what I am missing.

SELECT  ' L Detail by Company' as 'qtr',
        COUNT(JOB.JOBID) as 'transcount',
       COUNT(DISTINCT JOB.PATIENTID) as 'patient count',
       SUM(JOB.LANGUAGE_TCOST) as 'lcost',
       SUM(JOB.LANGUAGE_DISC_COST) as 'dlcost',
       AVG(JOB.LANGUAGE_DISC) as 'avgLDisc',
       SUM(JOB.LANGUAGE_TCOST) + SUM(JOB.LANGUAGE_DISC_COST) as 'LGrossAmtBilled',
       SUM(JOB.LANGUAGE_TCOST) / COUNT(DISTINCT JOB.PATIENTID) as 'PatAvgL',
       SUM(JOB.LANGUAGE_TCOST) / COUNT(JOB.JOBID) as 'RefAvgL',
       SUM(JOB.LANGUAGE_DISC) as 'avgPercentDiscL',
       JOB.JURISDICTION,PAYER.PAY_GROUPNAME,PAYER.PAY_COMPANY,PAYER.PAY_CITY,PAYER.PAY_STATE,PAYER.PAY_SALES_STAFF_ID,
       JOB.INVOICE_DATE,JOB.PATIENTID,JOB.JOBOUTCOMEID,JOB.SERVICEOUTCOME,INVOICE_AR.INVOICE_NO,INVOICE_AR.INVOICE_DATE AS EXPR1,
       INVOICE_AR.AMOUNT_DUE,INVOICE_AR.CLAIMNUMBER,PATIENT.LASTNAME,PATIENT.FIRSTNAME,PATIENT.EMPLOYERNAME,JOB_OUTCOME.DESCRIPTION,
       SERVICE_TYPE.DESCRIPTION,PAT_SERVICES_HISTORY.LANG_TYPE,MASRECEIVL.MASRVDATE,MASRECEIVL.MASRCVAMOUNT,REFERRAL_SOURCE.REF_LASTNAME,
       REFERRAL_SOURCE.REF_FIRSTNAME,REFERRAL_SOURCE.REF_PHONE,REFERRAL_SOURCE.REF_PHONE_EXT,REFERRAL_SOURCE.REF_FAX,REFERRAL_SOURCE.REF_EMAIL,JOB.INJURYDATE,JOB.APPT_DATE,
 
 SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with complaint') THEN 1 ELSE 0 END) AS 'CompletedWithcomplaintItems',
 SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed Successfully') THEN 1 ELSE 0 END) AS 'CompletedSuccessfullyItems',
 SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with No Charge') THEN 1 ELSE 0 END) AS 'CompletedwithNoChargeItems',
 SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with No Show') THEN 1 ELSE 0 END) AS 'CompletedwithNoShowItems',
 SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Completed with Situation') THEN 1 ELSE 0 END) AS 'CompletedWithSituationItems',
 SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Not Completed') THEN 1 ELSE 0 END) AS 'NotCompletedItems',
 SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Cancelled Prior to service') THEN 1 ELSE 0 END) AS 'CancelledPriortoserviceItems',
 SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Cancelled During Service') THEN 1 ELSE 0 END) AS 'CancelledDuringServiceItems',
 SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Awaiting for completion') THEN 1 ELSE 0 END) AS 'AwaitingforcpmpletionItems',
 SUM(CASE WHEN (JOB_OUTCOME.DESCRIPTION = 'Pending for review') THEN 1 ELSE 0 END) AS 'PendingforreviewItems',

 SUM(CASE WHEN (INVOICE_AR.INVOICE_DATE AS EXPR1  BETWEEN @dAgingDate-30 and @dAgingDate THEN MASRECEIVL.MASRCVAMOUNT Else 0 END) AS Age30,
 SUM(CASE WHEN (INVOICE_AR.INVOICE_DATE AS EXPR1  BETWEEN @dAgingDate-60 and @dAgingDate-31 THEN MASRECEIVL.MASRCVAMOUNT Else 0 END) AS Age60,
 SUM(CASE WHEN (INVOICE_AR.INVOICE_DATE AS EXPR1  BETWEEN @dAgingDate-90 and @dAgingDate-61 THEN MASRECEIVL.MASRCVAMOUNT Else 0 END) AS Age90


FROM JOB
       INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID
       LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYERID
       LEFT OUTER JOIN MASRECEIVL ON MASRECEIVL.INVOICE_NO = INVOICE_AR.INVOICE_NO
       LEFT OUTER JOIN REFERRAL_SOURCE ON REFERRAL_SOURCE.REFERRAL_ID = JOB.ADJUSTERID
       LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS
       LEFT OUTER JOIN PATIENT ON PATIENT.PATIENTID = JOB.PATIENTID
       LEFT OUTER JOIN JOB_OUTCOME ON JOB_OUTCOME.JOB_OUTCOME_ID = JOB.JOBOUTCOMEID
       LEFT OUTER JOIN SERVICE_TYPE ON SERVICE_TYPE.DESCRIPTION = JOB.SERVICEOUTCOME
       LEFT OUTER JOIN PAT_SERVICES_HISTORY ON PAT_SERVICES_HISTORY.PATIENTID = JOB.PATIENTID
WHERE
      (INVOICE_AR.AMOUNT_DUE > 0) AND (INVOICE_AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND  
      (MONTH(INVOICE_AR.INVOICE_DATE) IN (1,2,3,4,5,6,7,8,9,10,11,12)) AND (PAYER.PAY_Company like '%' + @Company + '%') AND
       (INVOICE_AR.INVOICE_NO like '%L')  AND (MASRECEIVL.MASRCVAMOUNT > 0)         
Group By
        JOB.JURISDICTION,
        PAYER.PAY_GROUPNAME,
        PAYER.PAY_COMPANY,
        PAYER.PAY_CITY,
        PAYER.PAY_STATE,
        PAYER.PAY_SALES_STAFF_ID,
        JOB.PATIENTID,
        JOB.INVOICE_DATE,
        JOB.JOBOUTCOMEID,
        JOB.SERVICEOUTCOME,
        INVOICE_AR.INVOICE_NO,
        INVOICE_AR.INVOICE_DATE,
        INVOICE_AR.AMOUNT_DUE,
        INVOICE_AR.CLAIMNUMBER,
        PATIENT.LASTNAME,
        PATIENT.FIRSTNAME,
        PATIENT.EMPLOYERNAME,
        JOB_OUTCOME.DESCRIPTION,
        SERVICE_TYPE.DESCRIPTION,
        PAT_SERVICES_HISTORY.LANG_TYPE,
        MASRECEIVL.MASRVDATE,
        MASRECEIVL.MASRCVAMOUNT,
        REFERRAL_SOURCE.REF_LASTNAME,
        REFERRAL_SOURCE.REF_FIRSTNAME,
        REFERRAL_SOURCE.REF_PHONE,
        REFERRAL_SOURCE.REF_PHONE_EXT,
        REFERRAL_SOURCE.REF_FAX,
        REFERRAL_SOURCE.REF_EMAIL,
        JOB.INJURYDATE,
        JOB.APPT_DATE

Order By 'QTR' asc

View 3 Replies


ADVERTISEMENT

SQL Aging Report

Dec 21, 2005

Hello,
I have a table of current customers that im trying to get an aging report for but is not working correctly.




Current Day
Month To Date
Last Month Same Day

Last Month Total
Last Year to Date
 
here is a sample of what im doing
SELECT
 COUNT(CASE WHEN create_date > dateadd(yy, datediff(year, 1, getdate()), - 365) AND create_date <= DateAdd(year, - 1, DateAdd(day, 0, GetDate())) THEN create_date ELSE 0 END) AS LastYTD,  COUNT(CASE WHEN create_date > dateadd(yy, datediff(yy, 0, getdate()), 0) AND create_date <= DateAdd(day, + 1, GetDate()) THEN customer_ID ELSE 0 END)  as CurrentYTD
when i add the Current Day sql it gives me the same number as the rest of it which is wrong, it should be 0 based on my date.
 

View 4 Replies View Related

'aging' Tables

Jan 10, 2007

I'm about to create some mechanism for control both: size of given table andage of it's entries. I wish it to delete entries if older then X , or last nentries when table is bigger then Y .I need opinion if it will be good to use ddl trigger mechanism (SS2005), ormaybe someone would share another solution. It is supposed to be used onlyfor some tables like log table, not for each table in database, I don't wantto the mechanism decrease performance too much though.

View 3 Replies View Related

Query For A/R Aging Report

Aug 20, 2001

How can I write a query to output the balances of unpaid invoices into aging columns? I have invoices, lineitems, and payments tables. I have a query that will join the 3 tables to determine which invoices are unpaid. I need to write an aging report in Access 2000 with the unpaid invoices listed with the balance under the proper heading (ie:<30, 30-60, 60+). Any ideas on how to do that? I can write a query to pull one date segment at a time but I can't get them all into one query with different headings to base a report on. My customer had it working in Access 2000 but he upgraded to SQL Server 7 with an Access 2000 Project as a front end and we can't get it to work. It's driving me crazy! TIA!!!

View 2 Replies View Related

SQL 2012 :: Calculate Stock Aging Based On Hand Quantity

Jan 18, 2015

I want to calculate stock aging (qty, cost) based on the on hand quantity.

Currently I am recording only in/out transaction only.

For ex: Item A i have 115 pieces (Balance stock) as on to day.

Transaction History
---------------------
Lot 1 - 01/01/2015 - 50
Lot 2 - 10/02/2015 - 50
Lot 3 - 11/03/2015 - 50
Lot 4 - 15/04/2014 - 50

I want to calculate cost of balance qty as shown below.

Jan -
Feb - 15 @ 1.1
Mar - 50 @ 0.90
Apr - 50 @ 1.2

Database schema
--------------------
CREATE TABLE [dbo].[StockManagement](
[Uniid] [int] IDENTITY(1,1) NOT NULL,
[StockCode] [int] NULL,
[TransactionDate] [datetime] NULL,
[TransactionTime] [time](0) NULL,

[Code] .....

View 0 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved