RowNumber Scope

What is the syntax for getting the inner-most scope for RowNumber ie within
a given group so that it behaves as follows:
Group1
rec1
Group2
rec1.1 expect to see Rowcount=1
rec1.2 expect to see Rowcount=2
rec2
Group2
rec2.1 expect to see Rowcount=1
rec2.2 expect to see Rowcount=2After posting this I realized that RowNumber won't give me the specific
rownumber just a total|||Mike
Rownumber SHOULD give you what you want... just add the name of the group as
the parameter ie
=rownumber(group1)
This gives the running count ( which would be the row number), and resets on
each new Group1 group...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and
it''''s
community of SQL Professionals.
"Mike Harbinger" wrote:
> After posting this I realized that RowNumber won't give me the specific
> rownumber just a total
>
>|||That's what I thought but it does not seem to be resetting even though I am
setting the group scope. The first row in the group is an addtional column
heading that I only want to see once per group instance. I was trying to use
RowNumber to test for row1 so I can toggle the visibiilty of the column
headers. Maybe there is a better way to do this?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:60CED50A-C9CB-4F65-B9A8-ECA1F529A69F@.microsoft.com...
> Mike
> Rownumber SHOULD give you what you want... just add the name of the group
> as
> the parameter ie
> =rownumber(group1)
> This gives the running count ( which would be the row number), and resets
> on
> each new Group1 group...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and
> it''''s
> community of SQL Professionals.
>
> "Mike Harbinger" wrote:
>> After posting this I realized that RowNumber won't give me the specific
>> rownumber just a total
>>

RowNumber Group

I have a report the group is a people name and the detail is their personal
informations. I wuold like insert a row numer in the group, try but in the
list sometime jump a numeber. For example:
...
...
10 John Smith
11 Luis Abrams
13 Mike Logan
...
...
25 Sam Carter
27 Tom Grey
...
how have a solution for my problem?
Thank's
MauroHi,
you can try use running value
=3DRunningValue(Fields!keyfieldinthegroup.Value, CountDistinct,
"groupname")
On Apr 3, 10:22=A0pm, Mauro <Ma...@.discussions.microsoft.com> wrote:
> I have a report the group is a people name and the detail is their persona=l
> informations. I wuold like insert a row numer in the group, try but in the=
> list sometime jump a numeber. For example:
> ...
> ...
> 10 John Smith
> 11 Luis Abrams
> 13 Mike Logan
> ...
> ...
> 25 Sam Carter
> 27 Tom Grey
> ...
> how have a solution for my problem?
> Thank's
> Mauro

RowNumber count within a group- please help!

I am new to reporting services, so forgive me if this is a simple question.
I have a simple report with 2 levels of grouping such as this:
Group 1
-Group 2
-item 1
-Item 2
I want to add a RowNumber to show the number (1, 2, 3, etc) before each item
within Group 2. I read the help and it said to use RowNumber(Scope) where
scope is the name of the grouping. However, any time I put the name of the
group in, I get an error. How do I refer to the group? I'm using this:
=RowNumber(First(Fields!Objective.Value, "DonorTrac_v3"))
RowNumber (Nothing) gives me the number for the outer group, but i want the
numbers in the inner group.
Any help you can provide is appreciated. Thanks!=RowNumber("Group 2")
Put the group name in double quotes!
Charles Kangai, MCDBA, MCT
"giggleraz" wrote:
> I am new to reporting services, so forgive me if this is a simple question.
> I have a simple report with 2 levels of grouping such as this:
> Group 1
> -Group 2
> -item 1
> -Item 2
> I want to add a RowNumber to show the number (1, 2, 3, etc) before each item
> within Group 2. I read the help and it said to use RowNumber(Scope) where
> scope is the name of the grouping. However, any time I put the name of the
> group in, I get an error. How do I refer to the group? I'm using this:
> =RowNumber(First(Fields!Objective.Value, "DonorTrac_v3"))
> RowNumber (Nothing) gives me the number for the outer group, but i want the
> numbers in the inner group.
> Any help you can provide is appreciated. Thanks!|||ah-hah! Thank you :)
"giggleraz" wrote:
> I am new to reporting services, so forgive me if this is a simple question.
> I have a simple report with 2 levels of grouping such as this:
> Group 1
> -Group 2
> -item 1
> -Item 2
> I want to add a RowNumber to show the number (1, 2, 3, etc) before each item
> within Group 2. I read the help and it said to use RowNumber(Scope) where
> scope is the name of the grouping. However, any time I put the name of the
> group in, I get an error. How do I refer to the group? I'm using this:
> =RowNumber(First(Fields!Objective.Value, "DonorTrac_v3"))
> RowNumber (Nothing) gives me the number for the outer group, but i want the
> numbers in the inner group.
> Any help you can provide is appreciated. Thanks!

Row-Limit per Group

I need to sum() the 3 biggest values from a specific field of each group.
Using
SELECT sum(numbers) FROM table GROUP BY field
operates on every row and LIMIT only restricts the final result. What I need is a way to limit the rows per group.select sum(numbers)
from daTable as X
where ( select count(*)
from daTable
where numbers > X.numbers) < 3|||This only gives a single amount, i.e., the sum of the biggest three from the whole table.
To obtain the sums of the three biggest values from each group:SELECT field, sum(numbers)
FROM daTable as X
WHERE (SELECT count(*)
FROM daTable
WHERE field = X.field AND numbers > X.numbers) < 3
GROUP BY field|||well spotted, peter, you are quite right, i misunderstood the question

:)

RowCount using Group BY and Having

Hi,
I have a query that uses Group By and Having to retrieve records.
The query is working fine and I want to get the Row Count of that Query.
How will I do that.
Thanks
Kiran
"Kiran" <Kiran@.nospam.net> wrote in message
news:eIhHM6EAFHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a query that uses Group By and Having to retrieve records.
> The query is working fine and I want to get the Row Count of that Query.
> How will I do that.
> Thanks
> Kiran
>
SELECT ... Group By Query
SELECT @.@.ROWCOUNT
You could also save the rowcount into a variable like this:
DECLARE @.MyCount int
SELECT ... Group By Query
SELECT @.MyCount = @.@.RowCount
HTH
Rick Sawtell
MCT, MCSD, MCDBA

RowCount using Group BY and Having

Hi,
I have a query that uses Group By and Having to retrieve records.
The query is working fine and I want to get the Row Count of that Query.
How will I do that.
Thanks
Kiran"Kiran" <Kiran@.nospam.net> wrote in message
news:eIhHM6EAFHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a query that uses Group By and Having to retrieve records.
> The query is working fine and I want to get the Row Count of that Query.
> How will I do that.
> Thanks
> Kiran
>
SELECT ... Group By Query
SELECT @.@.ROWCOUNT
You could also save the rowcount into a variable like this:
DECLARE @.MyCount int
SELECT ... Group By Query
SELECT @.MyCount = @.@.RowCount
HTH
Rick Sawtell
MCT, MCSD, MCDBA

RowCount using Group BY and Having

Hi,
I have a query that uses Group By and Having to retrieve records.
The query is working fine and I want to get the Row Count of that Query.
How will I do that.
Thanks
Kiran"Kiran" <Kiran@.nospam.net> wrote in message
news:eIhHM6EAFHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a query that uses Group By and Having to retrieve records.
> The query is working fine and I want to get the Row Count of that Query.
> How will I do that.
> Thanks
> Kiran
>
SELECT ... Group By Query
SELECT @.@.ROWCOUNT
You could also save the rowcount into a variable like this:
DECLARE @.MyCount int
SELECT ... Group By Query
SELECT @.MyCount = @.@.RowCount
HTH
Rick Sawtell
MCT, MCSD, MCDBA

Row Numbers for Groups

I am trying to number a group and I am using the following to do so:
=RunningValue(Fields!DBPROJECTID.Value, CountDistinct, Nothing)
However, I have one little problem. How do I clear out the value and start
over? This is what I want my report to look like:
Group 1 Header
1. Group 2 Header
Detail
2. Group 2 Header
Detail
Group 1 Header
1. Group 2 Header
Detail
Instead I get:
Group 1 Header
1. Group 2 Header
Detail
2. Group 2 Header
Detail
Group 1 Header
3. Group 2 Header
Detail
Any suggestions?Assuming your Group 1 is called "Group1", you can use a RunningValue with an
explicit scope specified:
=RunningValue(Fields!DBPROJECTID.Value, CountDistinct, "Group1")
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"wbarron" <wbarron@.discussions.microsoft.com> wrote in message
news:6018836A-2B4B-4A3B-B637-62805B443285@.microsoft.com...
>I am trying to number a group and I am using the following to do so:
> =RunningValue(Fields!DBPROJECTID.Value, CountDistinct, Nothing)
> However, I have one little problem. How do I clear out the value and
> start
> over? This is what I want my report to look like:
> Group 1 Header
> 1. Group 2 Header
> Detail
> 2. Group 2 Header
> Detail
> Group 1 Header
> 1. Group 2 Header
> Detail
> Instead I get:
> Group 1 Header
> 1. Group 2 Header
> Detail
> 2. Group 2 Header
> Detail
> Group 1 Header
> 3. Group 2 Header
> Detail
> Any suggestions?
>|||Thanks! After I posted, it dawned on me that I needed to specify the scope.
Thanks for the response.
Wendy
"Robert Bruckner [MSFT]" wrote:
> Assuming your Group 1 is called "Group1", you can use a RunningValue with an
> explicit scope specified:
> =RunningValue(Fields!DBPROJECTID.Value, CountDistinct, "Group1")
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "wbarron" <wbarron@.discussions.microsoft.com> wrote in message
> news:6018836A-2B4B-4A3B-B637-62805B443285@.microsoft.com...
> >I am trying to number a group and I am using the following to do so:
> >
> > =RunningValue(Fields!DBPROJECTID.Value, CountDistinct, Nothing)
> >
> > However, I have one little problem. How do I clear out the value and
> > start
> > over? This is what I want my report to look like:
> >
> > Group 1 Header
> > 1. Group 2 Header
> > Detail
> > 2. Group 2 Header
> > Detail
> > Group 1 Header
> > 1. Group 2 Header
> > Detail
> >
> > Instead I get:
> >
> > Group 1 Header
> > 1. Group 2 Header
> > Detail
> > 2. Group 2 Header
> > Detail
> > Group 1 Header
> > 3. Group 2 Header
> > Detail
> >
> > Any suggestions?
> >
> >
>
>

Row Numbers for Group

I have a table with 2 Groups and I want to number only Group2 with row
numbers. I put in a RowNumber(Nothing) function, and i'm getting something
like this:
Group 1
11 Group 2
14 Group 2
28 Group 2
Group 1
35 Group 2
etc...
What can I do to get the numbers to show up correctly?
Thanks!problem solved!
=RunningValue(Fields!fieldname.Value, CountDistinct, Nothing)
"jmann" wrote:
> I have a table with 2 Groups and I want to number only Group2 with row
> numbers. I put in a RowNumber(Nothing) function, and i'm getting something
> like this:
> Group 1
> 11 Group 2
> 14 Group 2
> 28 Group 2
> Group 1
> 35 Group 2
> etc...
> What can I do to get the numbers to show up correctly?
> Thanks!sql

Row Num in matrix

I am using Matrix in my report.
I have One Row Group (ProductID) and 7 Static rows.
Expression for my ProductID Group is =Fields!ProductID.Value
How can I set the Page break so I can accomodate 4 Row Groups (i.e) 28 lines
on one page and then goto next page.
Any Suggestions.Assuming you have one row of data in your query per product, you can put the
matrix in a list which groups on =Ceiling(RowNumber(Nothing)/4) and put
PageBreakAtEnd on either the list's grouping or on the matrix.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"battula" <battula_28@.hotmail.com> wrote in message
news:ODossYidEHA.1152@.TK2MSFTNGP09.phx.gbl...
> I am using Matrix in my report.
> I have One Row Group (ProductID) and 7 Static rows.
> Expression for my ProductID Group is =Fields!ProductID.Value
> How can I set the Page break so I can accomodate 4 Row Groups (i.e) 28
lines
> on one page and then goto next page.
> Any Suggestions.
>sql

Row Heigh

In my report I am using a group footer as a "line" to separate groups. I have
coloured the background of the group footer row. In the VS designer I have
reduced the row height to 0.03125in.
When previewing my report in VS the report generates as expected. When
running the report in the report manager the group footer row height reverts
back to its original height.
Is this a known bug, and is there a work around?Is the browser caching the report? Try Ctrl+F5 in IE.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:D931FCF4-AAF2-4C35-B5B2-2DE88697A502@.microsoft.com...
> In my report I am using a group footer as a "line" to separate groups. I
> have
> coloured the background of the group footer row. In the VS designer I have
> reduced the row height to 0.03125in.
> When previewing my report in VS the report generates as expected. When
> running the report in the report manager the group footer row height
> reverts
> back to its original height.
> Is this a known bug, and is there a work around?|||No the browser is not caching any reports.
"Jeff A. Stucker" wrote:
> Is the browser caching the report? Try Ctrl+F5 in IE.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> news:D931FCF4-AAF2-4C35-B5B2-2DE88697A502@.microsoft.com...
> > In my report I am using a group footer as a "line" to separate groups. I
> > have
> > coloured the background of the group footer row. In the VS designer I have
> > reduced the row height to 0.03125in.
> >
> > When previewing my report in VS the report generates as expected. When
> > running the report in the report manager the group footer row height
> > reverts
> > back to its original height.
> >
> > Is this a known bug, and is there a work around?
>
>

Row Handle Invalid

Just asking the same question in another group, apologies if you've seen
this elsewhere.
What exactly does the above (subject) error mean? I'm getting it from an
adp file when used by a few people at the same time (each user has the
file in their own filespace though). Access is through windows
authentication and it only seemed to occur during an update of a
specific table.. The problem is it didn't happen to everyone and I
can't recreate it at all on my own, so am wondering if it was something
to do with the level of traffic to/from the server at that specific time.
Any ideas?
Cheers!
Chris
Could be caused by a problem with the MDAC components on this machine.
Otherwise, check this article.
276375 Error Message with Distributed Queries When Using ADO
http://support.microsoft.com/?id=276375
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Row Handle Invalid

Just asking the same question in another group, apologies if you've seen
this elsewhere.
What exactly does the above (subject) error mean? I'm getting it from an
adp file when used by a few people at the same time (each user has the
file in their own filespace though). Access is through windows
authentication and it only seemed to occur during an update of a
specific table.. The problem is it didn't happen to everyone and I
can't recreate it at all on my own, so am wondering if it was something
to do with the level of traffic to/from the server at that specific time.
Any ideas?
Cheers!
ChrisCould be caused by a problem with the MDAC components on this machine.
Otherwise, check this article.
276375 Error Message with Distributed Queries When Using ADO
http://support.microsoft.com/?id=276375
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Row group 'footers' in matrices

I am trying to create a report that will append a calculated row of
percentages to the end of a group of test results. The results are
grouped by 'Category A' and have different subcategories. But
overall, I want to generate percentages for each column in 'Category
A'. The columns of Category A consist of Pass, Fail, and In
Progress. I want to be able to have a percentage of all the passes in
category A, but still have that distinction between a subcategory in
category A.
I have designed the matrix to look like such:
Static
Column
Category A | Sub Category
and I want it to produce a result like such:
Pass Fail In Progress
Category A Sub Category 1
1 3 0
Sub Category 2
3 0 1
Percentage
50% 37.5% 12.5%
Category B Sub Category 1
1 3 0
Sub Category 2
3 0 1
Percentage
50% 37.5% 12.5%
It is easy to format within Crystal Reports, but I have been mashing
my brain all day and I haven't found how to do it within Reporting
Services. HELP.Here are the layouts again. I didn't realize that it was going to get
ruined when it posts.
.Static Column
Category A | SubCategory
.................................P .F .IP
Category A Sub1 3 1 0
..................Sub2 1 2 1
..................Percentage 50% 37.5% 12.5%
Category B Sub1 3 1 0
..................Sub2 1 2 1
..................Percentage 50% 37.5% 12.5%

Row Count using Group By and Having

Hi,
I have a query that uses Group By and Having to retrieve records.
The query is working fine and I want to get the Row Count of that Query.
How will I do that.
Thanks
Kiran
select count(*) from
(<Your sql querey > ) dr
Here dr is name for derived table
Hth
"Kiran" <Kiran@.nospam.net> wrote in message
news:uGd396EAFHA.1296@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a query that uses Group By and Having to retrieve records.
> The query is working fine and I want to get the Row Count of that Query.
> How will I do that.
> Thanks
> Kiran
>
>|||Thanks a lot
That's exactly what I wanted
Kiran
"AM" <shahdharti@.gmail.com> wrote in message
news:ud6otJFAFHA.2552@.TK2MSFTNGP09.phx.gbl...
>
> select count(*) from
> (<Your sql querey > ) dr
> Here dr is name for derived table
> Hth
> "Kiran" <Kiran@.nospam.net> wrote in message
> news:uGd396EAFHA.1296@.TK2MSFTNGP10.phx.gbl...
>|||If you are already executing the base query in the same batch, there are a
couple of extra considerations:
1) you may be double running a potentially expensive query putting extra
load on the server.
2) unless you encapsulate the two queries in a REPEATABLE READ transaction,
you could get inconsistent results.
in this case you can do:
<your select>
SELECT @.@.ROWCOUNT
Mr Tea
http://mr-tea.blogspot.com
"Kiran" <Kiran@.nospam.net> wrote in message
news:uGd396EAFHA.1296@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a query that uses Group By and Having to retrieve records.
> The query is working fine and I want to get the Row Count of that Query.
> How will I do that.
> Thanks
> Kiran
>
>

Rounding and Grouping

Perhaps someone can settle an arguement for me ?

I have a set of data that I need to group together. SQL Script below.

CREATE TABLE [dbo].[CommTransactions] (
[ID] [id_type] NOT NULL ,
[TransactionID] [id_type] NULL ,
[ClientID] [id_type] NULL ,
[AccountCode] [varchar] (10) NULL ,
[Amount] [float] NULL ,
[CreateDateTime] [datetime] NULL

For the records I want to group the following applies.

The ID is unique and distinct.
The TransactionId is the same.
The ClientId is the same.
The AccountCode is different.
The Amount will be the same.
The CreateDateTime field is different by a few milliseconds.

I want to create a single line showing two account codes in different
fields. i.e. Staff and Manager (where their ID is the account code).

These can be entered in any order in the table mentioned.

The problem I have is I need to link two records together (that's the
problem in it's most simplistic terms). However, there may be
additional records with the same TransactionId, ClientId, AccountCode
and Amount, but happened at a slightly different time. It could be
done on the same day.

Now, the arguement is that we can group using the CreateDateTime
field. I argue that we can't as it will show down to the millisecond
and any rounding will not always allow for a match. If we added the
matching records once per day, then I can extract the date and group
on it, but if more than one group is added per day, then this would
cause the logic to fail.

So, are there any reliable methods for grouping date/time fields
reliably if there is a small difference (I suspect not)?

Is there anything I have missed ?

Any help or suggestions would be appreciated.

Thanks

RyanRyan,
Forgive me if I am not understanding the question correctly.
But I think the answer is that you don't have to group on a field; you
can group on an expression in most cases.
In this case, you can probably group by
convert(varchar,CreateDateTime,101), which is the date portion of
CreateDateTime.
I hate to suggest this because the performance will probably be terrible
unless your WHERE clause if very specific, but it may be the quick fix you
are looking for.

Best regards,
Chuck Conover
www.TechnicalVideos.net

"Ryan" <ryanofford@.hotmail.com> wrote in message
news:7802b79d.0402020100.41141655@.posting.google.c om...
> Perhaps someone can settle an arguement for me ?
> I have a set of data that I need to group together. SQL Script below.
> CREATE TABLE [dbo].[CommTransactions] (
> [ID] [id_type] NOT NULL ,
> [TransactionID] [id_type] NULL ,
> [ClientID] [id_type] NULL ,
> [AccountCode] [varchar] (10) NULL ,
> [Amount] [float] NULL ,
> [CreateDateTime] [datetime] NULL
> For the records I want to group the following applies.
> The ID is unique and distinct.
> The TransactionId is the same.
> The ClientId is the same.
> The AccountCode is different.
> The Amount will be the same.
> The CreateDateTime field is different by a few milliseconds.
> I want to create a single line showing two account codes in different
> fields. i.e. Staff and Manager (where their ID is the account code).
> These can be entered in any order in the table mentioned.
> The problem I have is I need to link two records together (that's the
> problem in it's most simplistic terms). However, there may be
> additional records with the same TransactionId, ClientId, AccountCode
> and Amount, but happened at a slightly different time. It could be
> done on the same day.
> Now, the arguement is that we can group using the CreateDateTime
> field. I argue that we can't as it will show down to the millisecond
> and any rounding will not always allow for a match. If we added the
> matching records once per day, then I can extract the date and group
> on it, but if more than one group is added per day, then this would
> cause the logic to fail.
> So, are there any reliable methods for grouping date/time fields
> reliably if there is a small difference (I suspect not)?
> Is there anything I have missed ?
> Any help or suggestions would be appreciated.
> Thanks
> Ryan|||Ryan (ryanofford@.hotmail.com) writes:
> Now, the arguement is that we can group using the CreateDateTime
> field. I argue that we can't as it will show down to the millisecond
> and any rounding will not always allow for a match. If we added the
> matching records once per day, then I can extract the date and group
> on it, but if more than one group is added per day, then this would
> cause the logic to fail.
> So, are there any reliable methods for grouping date/time fields
> reliably if there is a small difference (I suspect not)?

I'm not sure that I follow, but it sounds to me more like a business
problem.

You can group by the hour for instance:

SELECT yadadada, d, COUNT(*)
FROM (SELECT yadayada,
d = convert(char(8), CreateDateTime, 112) +
convert(char(5), CreateDateTime, 108)
FROM ...) AS a
GROUP BY yadayada, d

Of course, is a group is inserted so that some rows are inserted before
one o'clock, and others after you lose. Likewise, if two groups are
inserted the same hour.

A more complicated scheme may be devised where you compute the time
between two inserted rows, and if the difference is > some value,
those are two groups.

But you probably get a lot more robust application, by introducing a
marker which is unique for every batch you insert. This could still
be a datetime value, you just need to make sure that all rows in the
same batch gets the the same value.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yep, pretty much as I suspected. Unfortunately this table is one
supplied by another company so I can't change it as easily as I want
without affecting their app. Our users expectation differs from what
this package does hence the problem.

I want the other company to change this slightly and there will be a
cost (fair enough), only problem is our company doesn't want to pay
for it. So, I'm trying to provide them with everything to prove they
either pay for the change or accept it won't work. They would rather
my team spend several days (at God knows what cost) examining
something I know won't work instead of paying for a days worth of
development.

Daft.

As you have guessed, I'm trying to steer them down the route of a
marker that I can group on.

Thanks for the help.

Ryan

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94843E9F3EA3Yazorman@.127.0.0.1>...
> Ryan (ryanofford@.hotmail.com) writes:
> > Now, the arguement is that we can group using the CreateDateTime
> > field. I argue that we can't as it will show down to the millisecond
> > and any rounding will not always allow for a match. If we added the
> > matching records once per day, then I can extract the date and group
> > on it, but if more than one group is added per day, then this would
> > cause the logic to fail.
> > So, are there any reliable methods for grouping date/time fields
> > reliably if there is a small difference (I suspect not)?
> I'm not sure that I follow, but it sounds to me more like a business
> problem.
> You can group by the hour for instance:
> SELECT yadadada, d, COUNT(*)
> FROM (SELECT yadayada,
> d = convert(char(8), CreateDateTime, 112) +
> convert(char(5), CreateDateTime, 108)
> FROM ...) AS a
> GROUP BY yadayada, d
> Of course, is a group is inserted so that some rows are inserted before
> one o'clock, and others after you lose. Likewise, if two groups are
> inserted the same hour.
> A more complicated scheme may be devised where you compute the time
> between two inserted rows, and if the difference is > some value,
> those are two groups.
> But you probably get a lot more robust application, by introducing a
> marker which is unique for every batch you insert. This could still
> be a datetime value, you just need to make sure that all rows in the
> same batch gets the the same value.|||I have another thought that is worth a go. A slightly unusual approach
I must admit, but I think it may work.

I can establish the initial line that I want and take the
CreateDateTime from that. If I then add 1 minute to give me a start
time. Then subtract 1 minute to give me an end time, I can create a
table which holds the various ID fields, the accountcode I need and
the start and end times of a group.

I then use another query to pull out the second accountcode I want and
use a left join to the table I created previously, joining where the
createdatetime is between the start and end date. I add the
accountcode from the first table as a new field on the end of the
results of this query.

It means that the system will have a 2 minute window to commit the
transactions. Normally this is a few seconds, but I can adjust my
window.

I'll have to do some work checking where this can fail though, but
it's worth a little time doing this.

Feel free to pull this apart so I can check how well it will work.