Possible with one query?

[UltraSmooth]

I have a table that contains a set of ranges. Is it possible in one query to create a summary of a column from another table by range? So I guess something like this

Select SUM(detail_table.f1)
FROM detail_table
GROUP BY detail_table.f2

This would be similar to a group by date range, but the column I'm grouping on is just an int column, I can't extract a common value from it.

Hope this makes sense to someone!

 

[r937]

please show both tables

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[flubbard]

I think we would need to see how the data is stored that you wish to select. If two tables are involved, both need to be included in your select statement. Is the "range" actually a field in the second table?

- flub

 

[UltraSmooth]

Thanks for taking the time to reply and help but I believe I have solved (or at least come up with a workaround) for my problem.

The range I was referring to were fiscal period ends but my dates are stored as 7-digit int values as opposed to actual dates. I wrote a function that takes a date value and returns it's period ending int value from the range table (list of fiscal period date ends).

I then do something like this,

SELECT tableC.sdh_date
, 'D'
, tableC.sdh_loctn
, COUNT(tableC.sdh_bill)
FROM (
SELECT tableB.sdh_date,GET_FISCALWEEK_END(tableB.sdh_date) as week_end
FROM tableB
) as weekly_summary
JOIN tableC
ON weekly_summary.sdh_date=tableC.sdh_date
GROUP BY tableC.sdh_date
, tableC.sdh_loctn;

The select syntax might not be exact (don't have the code in front of me) but it seems to get the job done, and quickly as the the period range table(tableB) only has about 1000 rows in it and will only grow by about 100 records per year.

Of course if anyone sees anything wrong with my logic here please let me know.

Thanks again guys for replying.