Defenseload

MYSQL Query for Aging Report

Assume the following table scheme

Table Name : billing

billNo varchar(20);

insuranceCode varchar(20);

balanceAmount decimal(12,2);

billedDate date;

Here is the query for Aging Report

SELECT insuranceCode,
 SUM(IF(DATEDIFF(CURDATE(), billedDate ) BETWEEN 1 AND 30, balanceAmount, 0)) AS age130,
 SUM(IF(DATEDIFF(CURDATE(), billedDate ) BETWEEN 31 AND 60, balanceAmount, 0)) AS age3160,
 SUM(IF(DATEDIFF(CURDATE(), billedDate ) BETWEEN 61 AND 90, balanceAmount, 0)) AS age6190,
 SUM(IF(DATEDIFF(CURDATE(), billedDate ) > 90, balanceAmount, 0)) AS agegt90,
 SUM(balanceAmount) AS totalBalance
FROM billing bill
WHERE
 bill.balanceAmount > 0

GROUP BY insuranceCode 
ORDER BY totalBalance DESC

Available link for download

MySQL query reference

To list all the databases;

show databases;

SELECT dob, STR_TO_DATE(dob, "%d-%M-%y") FROM patdemofloridalab 

Query which contains future date.

SELECT * FROM patient WHERE dob >= CURDATE()

Example for Date_Sub

UPDATE patient SET dob = DATE_SUB(dob,INTERVAL 100 YEAR) WHERE dob >= CURDATE()

This will convert 01/01/2045 to 01/01/1945

Update mysql table with data from another table

UPDATE table1 t1, table2 t2
SET t1.field_to_change = t2.field_with_data
WHERE t1.field1 = t2.field2;

Add new column with default value

ALTER TABLE insurance ADD primaryedi INTEGER DEFAULT 1;

ALTER TABLE appsettings ADD claimstatusnew BIGINT NULL;

Drop Foreign Key constraint and Column

ALTER TABLE patientreceipt DROP FOREIGN KEY FK_patientreceipt_practiceID;
ALTER TABLE patientreceipt DROP   practiceID;

Update from another select

UPDATE tmpreport AS a
JOIN
(SELECT
    this_.accountCode AS accountcode,
        SUM(this_.billedAmount) AS amount
    FROM
        viewreporttranheader this_
    WHERE
        this_.DOSYear=2014 AND this_.DOSMonth = 3
    GROUP BY
        this_.accountCode) b
ON
  a.datacode = b.accountcode
SET billedAmt1 = b.amount

Available link for download