Recent content by mwake

I am trying to summarize multiple bill amounts and I am getting the right numbers but the decimal is in the wrong spot. When I use this statement to get bill amounts for a case, it returns the correct amounts: BILL_TOTAL= sum(CASE [TRAN_TYPE] WHEN 'BL'...

Thanks John. Please add the example of the cursor to populate the table. I'm handle reporting but I'm not a SQL wizard. I usually work with DBA's to handle things like this. I appreciate the suggestion...

I have an order table and lab results table. The field I am joining the tables on is OrderID, but it's not unique because if the patient is given multiple tests, each test has the same OrderID. I need to update some tests but not all of them, so I need to get to the test level to evaluate each...

I do have 2 triggers on the lab_nor table. The isupdate column and isafter column of your query return 1, the rest of the columns 0

I'm trying to update my lab_nor table based on the status of both the lab_nor and order tables. I want to change the status and cancel description in the lab_nor table if the ngn_lab_status = 'Opened' and then order_.actStatus field = 'ordered'. However, all records with lab_nor.ngn_lab_status...

I've got a commission report that has start and end date parameters in the command line. I created a subreport to track stocking fees for for each customer, since reps aren't paid commission on these fees. I added the subreport to the main report but I am unable to link the invoice date field...

I'm using CR13 with an Oracle db as my data source. I've got a quota attainment report and the SQL is currently only returning quota attainment % if there are sales in the period. The client wants an attainment % each month and if there are no sales in the current month, the % attainment...

I'm using CR13 with an Oracle 11 db as my data source. A command has been written to pull sales data by period. A quota amount exists for each period, but quota attainment% is only calculated on months where a rep has sales. So if I have no sales in January, sales in February, and no sales in...

I'm using Crystal For Enterprise and I have a SQL Server db as my data source. I've created a loss analysis report that's grouped by year and goes back 6 years. It only shows complete years. so it ranges from 2009 to 2014. Each year has total claim loss dollars and a count of total loss...

Thanks Olaf. That was just what I needed...

I have a TRANSACT table with a transact_id field and a related_transact field. If a payment is voided, the original transaction will have the same entry in both the transact_id and related_transact fields. The void transaction has a different transact_id than the payment record and a different...

Thanks Olaf. Your solution worked...

If an examiner has an insurance claim transferred to him, a record is created in the Claim Event table. An event_date is recorded along with an event_code='COO3'. The new_value field contains the name of the examiner the claim has been transferred to. In the current SQL, I am selecting max...

That worked Betty. THANKS!

I'm using Crystal for Enterprise, so I have a universe as a data source. I've created a report that shows a statement of benefits for each check produced in a batch. The report is grouped by check number and claim id, with check line items in the detail section. There is one claim id per...