Interesting Grouping problem...

Alright, I hope somebody can help me with this...

I have a database that keeps a log of people going in and out of our building. It records whether they entered or exited along with the current date/time. Now I'm trying to pull those values from the table; I want a count of the entrances, a count of the exits, and the time range, grouped by a half hour. A sample row of what I want would look like:

TimeRange Entered Exited
9:00-9:30 5 3

Does anyone know what sort of a SQL Statement I could use to do this? It's been driving me crazy! I'd like to do it all in one stored proc.

Thank ya,
Joe FioriniIs this the kind of thing you're after?

CREATE PROCEDURE [dbo].[getRecordsBetween]
(
@.startDate datetime,
@.endDate datetime = null
)
AS

if @.endDate is null
begin
set @.endDate = dateadd(minute, 30, @.startDate)
end

select *
from register
where dateIn > @.startDate and dateIn < @.endDate

RETURN

Register is the name of the table where the records are stored, and on this querey we're checking for the time signed in (rather than signed out).

Hope it helps

Dan|||you can probably do it with plain sql, no need for a stored proc

what are the columns called and what are their datatypes?

rudy
http://rudy.ca/|||It would be easier to do on an hourly basis.

You'd do something like:

select datepart(hh,TheTimestamp) as hour, Name, Type
into #t1
from SourceData

select hour,
sum (case when Type='Entry' then 1 else 0 end)as Entries,
sum (case when Type='Exit' then 1 else 0 end)as Exits,
from #t1

You could then join on a lookup table for the hour to give texts like "09:00 to 10:00"

Ask me if you need more details on this

Or

If you had Names in your input data you could group by name as follows:

select Name
sum(case when hour=1 and Type='Entry' then 1 else 0 end)as EntryHour1,
sum(case when hour=2 and Type='Entry' then 1 else 0 end)as EntryHour2,
sum(case when hour=3 and Type='Entry' then 1 else 0 end)as EntryHour3,
sum(case when hour=4 and Type='Entry' then 1 else 0 end)as EntryHour4,
...etc...
sum(case when hour=1 and Type='Exit' then 1 else 0 end)as ExitHour1,
sum(case when hour=2 and Type='Exit' then 1 else 0 end)as ExitHour2,
sum(case when hour=3 and Type='Exit' then 1 else 0 end)as ExitHour3,
sum(case when hour=4 and Type='Exit' then 1 else 0 end)as ExitHour4,
...etc...
from #t1
group by Name

Hope this helps.

- Andy Abelsql

Interactive sorting a list

Okay, I'm sure there is a way to do this. Basically what I have is a
List that contains a table. The list is grouping on a value so that
the table just shows some sub-details related to the grouping. What I
would like to do is sort the List. Here is an example of what I have
is the List grouping on the House Name (e.g. "My House"):
House Name:
My House
Rooms (table):
Bedroom 1 ...
Bedroom 2 ...
Living Room ...
House Name:
Bob's House
Rooms (table):
Bedroom 1 ...
Kitchen ...
So basically I want the user to be able to click on either "My House"
or "Bob's House" and change the list sort. Hope I'm making sense.
Thanks for any help!So I found one way to potentially accomplish this by setting up a
parameter, and then doing a "Jump to report" to reload the same
report, but changing the parameter that I use to control the sort of
the List. However, whenever I click on "My House" to cause the whole
jump to report thing to happen, I end up with the error "A data source
instance has not been supplied for the data source "House_Dataset"".
As some more info, this is all local processing, and I'm setting up
the data sources myself when the report is loaded for the first time
like so:
reportViewer1.LocalReport.ReportEmbeddedResource = "HouseInfo.rdlc";
reportViewer1.LocalReport.DataSources.Add(new
Microsoft.Reporting.WinForms.ReportDataSource("House_Dataset",
GetHouseDS()));
reportViewer1.LocalReport.DataSources.Add(new
Microsoft.Reporting.WinForms.ReportDataSource("Room_Dataset",
GetRooms()));
reportViewer1.RefreshReport();
So the initial load of the report is fine, but once the "jump to
report" executes, seems the reload loses (or never gets) the data
sources it needs.

Interactive Reporting

Software packages like Microsoft Small Business Accounting and Quickbooks offer a very powerful reporting module that lets end users change grouping, filtering, sorting, etc at run time (having it change the report dynamically infront of them). More importantly, their reporting tools let users click on details on the reports which opens the data in the form based portion of their software.

For example: If the end user pulls up a financial report, lets say "All Bills for February 07", the user get a report of all the bills that have gone out in that time frame. The end user can then click on the actual details in the report, and the Bill will come up in the Windows Forms portion of their software so they edit the bill, or create a new bill.

I have done a very limited amount of reporting in SQL Server, so I am not sure of how they were able to achieve this. If someone could give me some key words or ideas that I can bring up more information from in google, or even on here, I'd appreciate it.

Thanks in advance!

Have you taken a look at Report Builder yet?

Jarret

|||

Possibly... I will have to look that term up shortly to see what it actually is. So far I have just gone through visual studio biz intelligence projects and created new reporting projects. Ive done the wizards, created them manually, but I havent seen any kind of options to set that would actually let an end user interact with the report directly.