Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 2 of 2 total |
grouping across various date ranges |
Tue, Jun 5 2012 11:34 PM | Permanent Link |
IQA | 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 |
Tue, Jun 5 2012 11:37 PM | Permanent Link |
IQA | PLEASE IGNORE... I sent this to the DBISAM newsgroup instead of the
ElevateDB newsgroup. SORRY!!!! |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |