Hi All,

Just wondering the best way to write an SQL statement to group data by
over due periods (30 days, 60 days, 90 days, 120 days)

I want to produce a result set that looks like this...

company, days0, days30, days60, days90, days120
-----------------------------------------------
ABC      $45    $200    $345    $0      $0

For example, as separate SQL statements it would look like the
following, but I wonder if I can compile it all in the ONE data result...

SELECT companyID, dep_time, SUM(bal_owe) AS BalOwe FROM history WHERE
companyID = 1 AND CAST('2012-05-30' AS DATE) - CAST(dep_time AS DATE) <
30 AND bal_owe >= 0.01 GROUP BY companyID;

SELECT companyID, dep_time, SUM(bal_owe) AS BalOwe FROM history WHERE
companyID = 1 AND CAST('2012-05-30' AS DATE) - CAST(dep_time AS DATE) >=
30 AND CAST('2012-05-30' AS DATE) - CAST(dep_time AS DATE) < 60 AND
bal_owe >= 0.01 GROUP BY companyID;

SELECT companyID, dep_time, SUM(bal_owe) AS BalOwe FROM history WHERE
companyID = 1 AND CAST('2012-05-30' AS DATE) - CAST(dep_time AS DATE) >=
60 AND CAST('2012-05-30' AS DATE) - CAST(dep_time AS DATE) < 90 AND
bal_owe >= 0.01 GROUP BY companyID;

SELECT companyID, dep_time, SUM(bal_owe) AS BalOwe FROM history WHERE
companyID = 1 AND CAST('2012-05-30' AS DATE) - CAST(dep_time AS DATE) >=
90 AND CAST('2012-05-30' AS DATE) - CAST(dep_time AS DATE) < 120 AND
bal_owe >= 0.01 GROUP BY companyID;

Cheers,

Phil

Phi

> Just wondering the best way to write an SQL statement to group data by
> over due periods (30 days, 60 days, 90 days, 120 days)
>
> I want to produce a result set that looks like this...
>
> company, days0, days30, days60, days90, days120
> -----------------------------------------------
> ABC      $45    $200    $345    $0      $0
>
> For example, as separate SQL statements it would look like the
> following, but I wonder if I can compile it all in the ONE data result...
>

Presuming dep_time is the transaction date you can use IF statements eg

SELECT companyID,
SUM(IF(CAST('2012-05-30' AS DATE) - CAST(dep_time AS DATE) <  30 THEN bal_owe ELSE 0)) AS days0,
SUM(IF(CAST('2012-05-30' AS DATE) - CAST(dep_time AS DATE) >=  30 AND CAST('2012-05-30' AS DATE) - CAST(dep_time AS
DATE) < 60 THEN bal_owe ELSE 0)) AS days30,
SUM(IF(CAST('2012-05-30' AS DATE) - CAST(dep_time AS DATE) >=  60 AND CAST('2012-05-30' AS DATE) - CAST(dep_time AS
DATE) < 90 THEN bal_owe ELSE 0)) AS days60,
SUM(IF(CAST('2012-05-30' AS DATE) - CAST(dep_time AS DATE) >=  60 AND CAST('2012-05-30' AS DATE) - CAST(dep_time AS
DATE) < 90 THEN bal_owe ELSE 0)) AS days90,
SUM(IF(CAST('2012-05-30' AS DATE) - CAST(dep_time AS DATE) >=  90 THEN bal_owe ELSE 0)) AS days120
FROM history
GROUP BY companyID;

John

Thanks John, that worked perfectly well and very fast.