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

Interesting CASE behavior -- bug?

(SQL Server 2000, SP3a)
Hello all!
I had a situation where I was getting some extraneous spaces in some string
building, and
it took me a while to figure it out. I think I can appreciate *why* it's do
ing this, but
I wish it weren't.
Consider the following:
declare @.CrLf nchar(2) select @.CrLf = nchar(13) + nchar(10)
declare @.String nvarchar(255) select @.String = N''
select @.String = @.String + case when (len(@.String) != 0) then @.CrLf + @.CrLf
else N'' end +
N'Test'
print N'"' + @.String + N'"'
print [master].[dbo].& #91;fn_varbintohexstr](convert(varbinary
(16),
@.String))
The output is:
" Test"
0x20002000200020005400650073007400
It feels like, for some reason, the CASE is evaluating both "sides" of the r
esultant
expression, and determining that it can be coerced to a NCHAR(2). However,
somewhere
along the lines, it gets confused and is introducing 2 bytes for every NCHAR
-- sort of
like accidentally making it a CHAR(4) that then gets promoted to a NCHAR(4)
(as per the
NULL bytes in every other character).
If I change the definition of @.CrLf to be a NVARCHAR(2), then everything wor
ks as
expected.
It's very odd, and certainly smells like a bug to me.
Thoughts?Hi John,
This behaviour isn't a bug. CASE is an expression that returns one datatype
only, and the datatype it returns is determined by looking at the datatypes
of all the possible results of the expression, and then choosing the
appropriate datatype according to the datatype precedence. So what happens
is that your CASE can either return a zero length NCHAR literal (N''), or a
NCHAR(4) (@.CrLf + @.CrLf) so the return type of your CASE expression will be
NCHAR(4), as this has a higher precedence.
--
Jacco Schalkwijk
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OM4e%23b6$DHA.3048@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I had a situation where I was getting some extraneous spaces in some
string building, and
> it took me a while to figure it out. I think I can appreciate *why* it's
doing this, but
> I wish it weren't.
> Consider the following:
>
> declare @.CrLf nchar(2) select @.CrLf = nchar(13) + nchar(10)
> declare @.String nvarchar(255) select @.String = N''
> select @.String = @.String + case when (len(@.String) != 0) then @.CrLf +
@.CrLf else N'' end +
> N'Test'
> print N'"' + @.String + N'"'
> print [master].[dbo].& #91;fn_varbintohexstr](convert(varbinary
(16)
, @.String))
>
> The output is:
>
> " Test"
> 0x20002000200020005400650073007400
>
> It feels like, for some reason, the CASE is evaluating both "sides" of the
resultant
> expression, and determining that it can be coerced to a NCHAR(2).
However, somewhere
> along the lines, it gets confused and is introducing 2 bytes for every
NCHAR -- sort of
> like accidentally making it a CHAR(4) that then gets promoted to a
NCHAR(4) (as per the
> NULL bytes in every other character).
> If I change the definition of @.CrLf to be a NVARCHAR(2), then everything
works as
> expected.
> It's very odd, and certainly smells like a bug to me.
> Thoughts?
>|||Ah, yes...I think I grok that -- I think that I forgot that I had *two* @.CrL
fs in my first
expression (hence the doubling).
Thanks for your help!
John Peterson
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uWhzb36$DHA.3352@.TK2MSFTNGP09.phx.gbl...
> Hi John,
> This behaviour isn't a bug. CASE is an expression that returns one datatyp
e
> only, and the datatype it returns is determined by looking at the datatype
s
> of all the possible results of the expression, and then choosing the
> appropriate datatype according to the datatype precedence. So what happens
> is that your CASE can either return a zero length NCHAR literal (N''), or
a
> NCHAR(4) (@.CrLf + @.CrLf) so the return type of your CASE expression will b
e
> NCHAR(4), as this has a higher precedence.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OM4e%23b6$DHA.3048@.tk2msftngp13.phx.gbl...
> string building, and
> doing this, but
> @.CrLf else N'' end +
> resultant
> However, somewhere
> NCHAR -- sort of
> NCHAR(4) (as per the
> works as
>

Interesting CASE behavior -- bug?

(SQL Server 2000, SP3a)
Hello all!
I had a situation where I was getting some extraneous spaces in some string building, and
it took me a while to figure it out. I think I can appreciate *why* it's doing this, but
I wish it weren't.
Consider the following:
declare @.CrLf nchar(2) select @.CrLf = nchar(13) + nchar(10)
declare @.String nvarchar(255) select @.String = N''
select @.String = @.String + case when (len(@.String) != 0) then @.CrLf + @.CrLf else N'' end +
N'Test'
print N'"' + @.String + N'"'
print [master].[dbo].[fn_varbintohexstr](convert(varbinary(16), @.String))
The output is:
" Test"
0x20002000200020005400650073007400
It feels like, for some reason, the CASE is evaluating both "sides" of the resultant
expression, and determining that it can be coerced to a NCHAR(2). However, somewhere
along the lines, it gets confused and is introducing 2 bytes for every NCHAR -- sort of
like accidentally making it a CHAR(4) that then gets promoted to a NCHAR(4) (as per the
NULL bytes in every other character).
If I change the definition of @.CrLf to be a NVARCHAR(2), then everything works as
expected.
It's very odd, and certainly smells like a bug to me.
Thoughts?Hi John,
This behaviour isn't a bug. CASE is an expression that returns one datatype
only, and the datatype it returns is determined by looking at the datatypes
of all the possible results of the expression, and then choosing the
appropriate datatype according to the datatype precedence. So what happens
is that your CASE can either return a zero length NCHAR literal (N''), or a
NCHAR(4) (@.CrLf + @.CrLf) so the return type of your CASE expression will be
NCHAR(4), as this has a higher precedence.
--
Jacco Schalkwijk
SQL Server MVP
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OM4e%23b6$DHA.3048@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I had a situation where I was getting some extraneous spaces in some
string building, and
> it took me a while to figure it out. I think I can appreciate *why* it's
doing this, but
> I wish it weren't.
> Consider the following:
>
> declare @.CrLf nchar(2) select @.CrLf = nchar(13) + nchar(10)
> declare @.String nvarchar(255) select @.String = N''
> select @.String = @.String + case when (len(@.String) != 0) then @.CrLf +
@.CrLf else N'' end +
> N'Test'
> print N'"' + @.String + N'"'
> print [master].[dbo].[fn_varbintohexstr](convert(varbinary(16), @.String))
>
> The output is:
>
> " Test"
> 0x20002000200020005400650073007400
>
> It feels like, for some reason, the CASE is evaluating both "sides" of the
resultant
> expression, and determining that it can be coerced to a NCHAR(2).
However, somewhere
> along the lines, it gets confused and is introducing 2 bytes for every
NCHAR -- sort of
> like accidentally making it a CHAR(4) that then gets promoted to a
NCHAR(4) (as per the
> NULL bytes in every other character).
> If I change the definition of @.CrLf to be a NVARCHAR(2), then everything
works as
> expected.
> It's very odd, and certainly smells like a bug to me.
> Thoughts?
>|||Ah, yes...I think I grok that -- I think that I forgot that I had *two* @.CrLfs in my first
expression (hence the doubling).
Thanks for your help!
John Peterson
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uWhzb36$DHA.3352@.TK2MSFTNGP09.phx.gbl...
> Hi John,
> This behaviour isn't a bug. CASE is an expression that returns one datatype
> only, and the datatype it returns is determined by looking at the datatypes
> of all the possible results of the expression, and then choosing the
> appropriate datatype according to the datatype precedence. So what happens
> is that your CASE can either return a zero length NCHAR literal (N''), or a
> NCHAR(4) (@.CrLf + @.CrLf) so the return type of your CASE expression will be
> NCHAR(4), as this has a higher precedence.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OM4e%23b6$DHA.3048@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I had a situation where I was getting some extraneous spaces in some
> string building, and
> > it took me a while to figure it out. I think I can appreciate *why* it's
> doing this, but
> > I wish it weren't.
> >
> > Consider the following:
> >
> >
> > declare @.CrLf nchar(2) select @.CrLf = nchar(13) + nchar(10)
> > declare @.String nvarchar(255) select @.String = N''
> >
> > select @.String = @.String + case when (len(@.String) != 0) then @.CrLf +
> @.CrLf else N'' end +
> > N'Test'
> > print N'"' + @.String + N'"'
> > print [master].[dbo].[fn_varbintohexstr](convert(varbinary(16), @.String))
> >
> >
> > The output is:
> >
> >
> > " Test"
> > 0x20002000200020005400650073007400
> >
> >
> > It feels like, for some reason, the CASE is evaluating both "sides" of the
> resultant
> > expression, and determining that it can be coerced to a NCHAR(2).
> However, somewhere
> > along the lines, it gets confused and is introducing 2 bytes for every
> NCHAR -- sort of
> > like accidentally making it a CHAR(4) that then gets promoted to a
> NCHAR(4) (as per the
> > NULL bytes in every other character).
> >
> > If I change the definition of @.CrLf to be a NVARCHAR(2), then everything
> works as
> > expected.
> >
> > It's very odd, and certainly smells like a bug to me.
> >
> > Thoughts?
> >
> >
>