January 30, 2006 at 4:57 pm
Hi,
I am seeking some assistance to calculate correctly the count of items in a group of Invoices within my application.
My query returns 57 and 15 respecitvely for the lines:
COUNT(IP.SB_INVOICE_PRINT_INV_NO) AS REMINDERS,
COUNT(DISTINCT IP.SB_INVOICE_PRINT_INV_NO) AS REMINDERS
however there is in fact only 2 Invoices that can be seen when the Invoice Number (IP.SB_INVOICE_PRINT_INV_NO) is added to the group and the data shown.
The total of the Invoice group is correct.
I hope I am missing something obvious...
The query:
SELECT
INVOICE.SB_REMINDER_LEVEL,
INVOICE.SB_COST_TYPE_CRITERIA_ID,
FINTYPE.SB_FIN_CLASS_TYPE_CODE,
FINTYPE.SB_FIN_CLASS_TYPE_DESC,
CHART.SB_HOSPITAL_CODE,
HOSPITAL.SB_HOSPITAL_NAME,
INVOICE_TYPE.SB_INVOICE_TYPE_CODE,
INVOICE_TYPE.SB_INVOICE_TYPE_DESC,
CASE
when IsNull(DEBTOR.SB_HEALTH_FUND,'N') = 'Y' then
'Health Fund'
WHEN IsNull(DEBTOR.SB_STANDARD_DEBTOR,'N') = 'Y' THEN
'Corporate'
ELSE
'Individual'
END AS DEBTOR_TYPE,
CASE
when IsNull(DEBTOR.SB_HEALTH_FUND,'N') = 'Y' then
'H'
WHEN IsNull(DEBTOR.SB_STANDARD_DEBTOR,'N') = 'Y' THEN
'C'
ELSE
'I'
END AS SB_STANDARD_DEBTOR,
COUNT(IP.SB_INVOICE_PRINT_INV_NO) AS REMINDERS,
COUNT(DISTINCT IP.SB_INVOICE_PRINT_INV_NO) AS REMINDERS,
sum(ITEM.SB_INVOICE_COST
- IsNull(ITEM.SB_HIC_AMOUNT_PAID,0)
- IsNull(ITEM.SB_FUND_AMOUNT_PAID,0)
- IsNull(ITEM.SB_ADJUST_AMOUNT,0)
- IsNull(ITEM.SB_WRITEOFF_AMOUNT,0)) AS TOTAL
FROM
SB_INVOICE INVOICE INNER JOIN
SB_INVOICE_ITEM ITEM ON
INVOICE.SB_INVOICE_ID = ITEM.SB_INVOICE_ID INNER JOIN
SB_INVOICE_PRINT IP ON
INVOICE.SB_INVOICE_PRINT_ID = IP.SB_INVOICE_PRINT_ID INNER JOIN
SB_INVOICE_TYPE INVOICE_TYPE ON
INVOICE.SB_INVOICE_TYPE_CODE = INVOICE_TYPE.SB_INVOICE_TYPE_CODE INNER JOIN
SB_COST_TYPE_CRITERIA ON
INVOICE.SB_COST_TYPE_CRITERIA_ID = SB_COST_TYPE_CRITERIA.SB_COST_TYPE_CRITERIA_ID INNER JOIN
SB_FIN_CLASS_TYPE FINTYPE on
FINTYPE.SB_FIN_CLASS_TYPE_CODE = SB_COST_TYPE_CRITERIA.SB_FIN_CLASS_TYPE_CODE INNER JOIN
SB_CHART_OF_ACC CHART ON
INVOICE.SB_CHART_OF_ACC_ID = CHART.SB_CHART_OF_ACC_ID INNER JOIN
SB_HOSPITAL HOSPITAL ON
CHART.SB_HOSPITAL_CODE = HOSPITAL.SB_HOSPITAL_CODE INNER JOIN
SB_DEBTOR DEBTOR ON
INVOICE.SB_DEBTOR_ID = DEBTOR.SB_DEBTOR_ID INNER JOIN
SB_HOSPITAL_ACCESS HOSPITAL_ACCESS ON
HOSPITAL.SB_HOSPITAL_CODE = HOSPITAL_ACCESS.SB_HOSPITAL_CODE LEFT OUTER JOIN
SB_PATIENT_EPISODE EPISODE ON
INVOICE.SB_EPISODE_NUMBER = EPISODE.SB_EPISODE_NUMBER LEFT OUTER JOIN
SB_HELD_REMINDER_VIEW ON
INVOICE.SB_INVOICE_ID = SB_HELD_REMINDER_VIEW.SB_INVOICE_ID LEFT OUTER JOIN
SB_INVOICE_ERROR ERROR ON
INVOICE.SB_INVOICE_ID = ERROR.SB_INVOICE_ID AND
'R' + CONVERT(VARCHAR, INVOICE.SB_REMINDER_LEVEL) = ERROR.SB_INVOICE_FLAG_CODE
WHERE
INVOICE.SB_INVOICE_ID NOT IN (SELECT SB_INVOICE_ID FROM SB_INVOICE_ITEM WHERE SB_ITEM_STATUS_CODE = '48') AND
left(IP.SB_INVOICE_PRINT_INV_NO,4) <> 'MIGR' and
HOSPITAL_ACCESS.SB_LOGIN_CODE = 'sthornton' AND
SB_HELD_REMINDER_VIEW.SB_INVOICE_ID IS NULL AND
INVOICE.SB_INVOICE_STATUS_CODE = '20' AND
INVOICE.SB_REMINDER_DATE <= GETDATE() AND
ERROR.SB_INVOICE_ID IS NULL
AND CHART.SB_HOSPITAL_CODE = 'Q230'
AND INVOICE.SB_INVOICE_TYPE_CODE = 'DOC'
AND INVOICE.SB_REMINDER_LEVEL = 1
AND CASE
when IsNull(DEBTOR.SB_HEALTH_FUND,'N') = 'Y' then
'Health Fund'
WHEN IsNull(DEBTOR.SB_STANDARD_DEBTOR,'N') = 'Y' THEN
'Corporate'
ELSE
'Individual'
END in ('Corporate')
AND FINTYPE.SB_FIN_CLASS_TYPE_CODE in ('C')
GROUP BY
INVOICE.SB_REMINDER_LEVEL,
FINTYPE.SB_FIN_CLASS_TYPE_CODE,
INVOICE.SB_COST_TYPE_CRITERIA_ID,
FINTYPE.SB_FIN_CLASS_TYPE_DESC,
CHART.SB_HOSPITAL_CODE,
HOSPITAL.SB_HOSPITAL_NAME,
INVOICE_TYPE.SB_INVOICE_TYPE_CODE,
INVOICE_TYPE.SB_INVOICE_TYPE_DESC,
CASE
when IsNull(DEBTOR.SB_HEALTH_FUND,'N') = 'Y' then
'Health Fund'
WHEN IsNull(DEBTOR.SB_STANDARD_DEBTOR,'N') = 'Y' THEN
'Corporate'
ELSE
'Individual'
END,
CASE
when IsNull(DEBTOR.SB_HEALTH_FUND,'N') = 'Y' then
'H'
WHEN IsNull(DEBTOR.SB_STANDARD_DEBTOR,'N') = 'Y' THEN
'C'
ELSE
'I'
END
HAVING
sum(ITEM.SB_INVOICE_COST
- IsNull(ITEM.SB_HIC_AMOUNT_PAID,0)
- IsNull(ITEM.SB_FUND_AMOUNT_PAID,0)
- IsNull(ITEM.SB_ADJUST_AMOUNT,0)
- IsNull(ITEM.SB_WRITEOFF_AMOUNT,0)) > 0
and
sum(ITEM.SB_INVOICE_COST
- IsNull(ITEM.SB_HIC_AMOUNT_PAID,0)
- IsNull(ITEM.SB_FUND_AMOUNT_PAID,0)
- IsNull(ITEM.SB_ADJUST_AMOUNT,0)
- IsNull(ITEM.SB_WRITEOFF_AMOUNT,0))
>= 0
ORDER BY
INVOICE.SB_REMINDER_LEVEL,
FINTYPE.SB_FIN_CLASS_TYPE_CODE,
INVOICE.SB_COST_TYPE_CRITERIA_ID,
FINTYPE.SB_FIN_CLASS_TYPE_DESC,
CHART.SB_HOSPITAL_CODE,
HOSPITAL.SB_HOSPITAL_NAME,
INVOICE_TYPE.SB_INVOICE_TYPE_CODE,
INVOICE_TYPE.SB_INVOICE_TYPE_DESC,
CASE
when IsNull(DEBTOR.SB_HEALTH_FUND,'N') = 'Y' then
'Health Fund'
WHEN IsNull(DEBTOR.SB_STANDARD_DEBTOR,'N') = 'Y' THEN
'Corporate'
ELSE
'Individual'
END
January 30, 2006 at 5:24 pm
Without having your tables, Data, and Desired result it is impossible to look at your query and identify what could be wrong with it.
perhaps you could try
count(distinct INVOICE.SB_INVOICE_ID) AS REMINDERS
Good Luck
January 30, 2006 at 5:36 pm
HI,
count(INVOICE.SB_INVOICE_ID ),
count(distinct INVOICE.SB_INVOICE_ID )
return 57 and 15 records respectively.
The Invoice table sb_invoice is the child of sb_invoice_print, therefore 1 sb_invoice_print may be the parent of many sb_invoice records.
January 31, 2006 at 2:28 am
We really need scripts for your tables involved and also some scripted sample data that shows the effect you're having problems with so we can test your query.
(It doesn't have to be actual production data, though it must be 'proper' in the way it's constructed - ie behave the same as the production data)
Without it, it's like Ray said, near impossible to make anything intelligble out of it. The best you'll get is guesses and speculations.
/Kenneth
January 31, 2006 at 7:06 am
Is IP.SB_INVOICE_PRINT_INV_NO varchar? Could it have trailing or leading white space that make it non-distinct despite looking the same?
(Marvin)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply