SQL Server Performance Forum – Threads Archive

Querying a local Dataset

OK Jasper,
This Oracle DB issue is giving me fits, so here comes my next question.
I can query the database and load a dataset with , let’s say, this query SELECT event, details, time FROM events
which is no problem, it returns all events.(the dates are formatted like 9/15/2003 12:00 AM) But now I want to create a report which has a column for each month, and which counts the number of events within each month and displays it. SO… can I now query this local dataset (call it ‘EventsTable’) with some sort of BETWEEN statement, so that in my report table, I have a field with an expression that asks "count the number of events within ‘EventsTable’ that occur between 1/1/2003 and 1/31/2003" and display in the January column.
Does this make sense ?
Thanks so much for all your help! D

The best approach would be to do this in Oracle, you just need to find what its equivalent to the DATENAME and DATEPART functions are and then do a GROUP BY. However, you can certainly do this in RS although it seems inefficent to bring all the detail back unless you are do a master/detail type report. You can create two calculated fields (right click the fields list and choose Add. Call the first one Month and make it’s expression =MonthName(DatePart("m", Fields!time.Value)), call the second one MonthNum and make it’s expression =DatePart("m", Fields!OrderDate.Value). Then add a table to layout, set one detail column to Month field. Right click the detail row for the whole table and choose Edit Group. Add a grouping expression =Fields!Month.Value and set the Name of the group to MonthGroup. Now in the second detail column you can add the expression =CountRows("MonthGroup"). Finally select the whole tables properties and set the Sorting expression to =Fields!MonthNum.Value so the results are in the right order.This is obviously a lot more work than <pre>select datename(mm,OrderDate),count(*) from Orders group by datepart(mm,OrderDate),datename(mm,OrderDate) order by datepart(mm,OrderDate)</pre> however I don’t know what the equivalent Oracle functions are [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] HTH Jasper Smith

Don’t know if this is right but this might be the equivalent to datepart for month in oracle
to_number(to_char(date, â€˜MM#%92)) HTH Jasper Smith

jasper,
Although I haven’t tried the Oracle option yet, I tried the lengthier alternative and it worked great, exactly what I’m looking for. It will work fine until I take on the Oracle issue.
You have saved me hours of banging my head against the wall !
Thanks again
d

]]>