Grouping issue

I have a matrix that displays data for tags per day on each page. The matrix
has a grouped column that does the grouping by day (this column is returned
from a stored procedure) and page breaks at the end of the day.
This is what I need - before I page break, I need to also display the
statistics per day like avg, min and max values for that day for all the
tags.
Date Tag1 Tag2 Tag3 Tag4
======================================== 10/01/2003 00:00 2 4 6 7
10/01/2003 01:00 2 4 6 7
10/01/2003 02:00 2 4 6 7
======> this is the matrix (grouped by day and page breaks after each day)
10/01/2003 03:00 2 4 6 7
......
10/01/2003 21:00 2 4 6 7
10/01/2003 22:00 2 4 6 7
10/01/2003 23:00 2 4 6 7
========================================= Daily Statistics
--
Sum: 48 96 144 168
======> The Daily Statistics part I am not able to do. Since the matrix has
a page break per
Average: 2 4 6
7 day, I am not able to get the statistics also
on the same page for that day.
Min: 2 4 6
7
Max: 2 4 6
7
==========================================
Any help will be highly appreciated. Thanks.I have a matrix that displays data for tags per day on each page. The
matrix
has a grouped column that does the grouping by day (this column is returned
from a stored procedure) and page breaks at the end of the day.
This is what I need - before I page break, I need to also display the
statistics per day like avg, min and max values for that day for all the
tags.
Date Tag1 Tag2 Tag3 Tag4
======================================== 10/01/2003 00:00 2 4 6 7
10/01/2003 01:00 2 4 6 7
10/01/2003 02:00 2 4 6 7
======> this is the matrix (grouped by day and page breaks after each day)
10/01/2003 03:00 2 4 6 7
......
10/01/2003 21:00 2 4 6 7
10/01/2003 22:00 2 4 6 7
10/01/2003 23:00 2 4 6 7
========================================= Daily Statistics
--
Sum: 48 96 144
168 ======> The Daily Statistics part I am not able to do. Since the matrix
has
a page break per
Average: 2 4 6
7 day, I am not able to get the statistics also
on the same page for that day.
Min: 2 4 6
7
Max: 2 4 6
7
==========================================
Any help will be highly appreciated. Thanks.|||Hello,
I do not have an answer but there is some idea, that could be used.
Could you e-mail me more info about your data and matrix, so I can prepare
working sample?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"KMP" <KMP@.msn.com> wrote in message
news:OaQofbcFFHA.2180@.TK2MSFTNGP10.phx.gbl...
> I have a matrix that displays data for tags per day on each page. The
> matrix
> has a grouped column that does the grouping by day (this column is
> returned
> from a stored procedure) and page breaks at the end of the day.
> This is what I need - before I page break, I need to also display the
> statistics per day like avg, min and max values for that day for all the
> tags.
> Date Tag1 Tag2 Tag3 Tag4
> ========================================> 10/01/2003 00:00 2 4 6 7
> 10/01/2003 01:00 2 4 6 7
> 10/01/2003 02:00 2 4 6 7
> ======> this is the matrix (grouped by day and page breaks after each day)
> 10/01/2003 03:00 2 4 6 7
> ......
> 10/01/2003 21:00 2 4 6 7
> 10/01/2003 22:00 2 4 6 7
> 10/01/2003 23:00 2 4 6 7
> =========================================> Daily Statistics
> --
> Sum: 48 96 144
> 168 ======> The Daily Statistics part I am not able to do. Since the
> matrix
> has
> a page break per
> Average: 2 4 6
> 7 day, I am not able to get the statistics also
> on the same page for that day.
> Min: 2 4 6
> 7
> Max: 2 4 6
> 7
> ==========================================> Any help will be highly appreciated. Thanks.
>
>|||I have been able to figure it out. Thank you very much.
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:OJYwI8gFFHA.3504@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I do not have an answer but there is some idea, that could be used.
> Could you e-mail me more info about your data and matrix, so I can prepare
> working sample?
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "KMP" <KMP@.msn.com> wrote in message
> news:OaQofbcFFHA.2180@.TK2MSFTNGP10.phx.gbl...
> > I have a matrix that displays data for tags per day on each page. The
> > matrix
> > has a grouped column that does the grouping by day (this column is
> > returned
> > from a stored procedure) and page breaks at the end of the day.
> >
> > This is what I need - before I page break, I need to also display the
> > statistics per day like avg, min and max values for that day for all the
> > tags.
> >
> > Date Tag1 Tag2 Tag3 Tag4
> > ========================================> > 10/01/2003 00:00 2 4 6 7
> > 10/01/2003 01:00 2 4 6 7
> > 10/01/2003 02:00 2 4 6 7
> > ======> this is the matrix (grouped by day and page breaks after each
day)
> > 10/01/2003 03:00 2 4 6 7
> > ......
> > 10/01/2003 21:00 2 4 6 7
> > 10/01/2003 22:00 2 4 6 7
> > 10/01/2003 23:00 2 4 6 7
> > =========================================> > Daily Statistics
> > --
> > Sum: 48 96 144
> > 168 ======> The Daily Statistics part I am not able to do. Since the
> > matrix
> > has
> >
> > a page break per
> > Average: 2 4 6
> > 7 day, I am not able to get the statistics
also
> >
> > on the same page for that day.
> > Min: 2 4 6
> > 7
> > Max: 2 4 6
> > 7
> > ==========================================> >
> > Any help will be highly appreciated. Thanks.
> >
> >
> >
> >
>

Grouping in crystal reports

Hi

I m using crystal reports ver 8.0.

I have a report which is grouped on a field called "states".

My requirement is that the data for each state has to begin from a fresh page. i.e, each group item has to start from the next page.

I m not able to do this in crystal reports. Can anyone tell me if this is possible in crystal reports. and if possible, how it can be done ?

Thanking in advancethis works for 8.5 I don't know about version 8.0

Set "New Page Before" in the Group Header

Grouping databases with a folder

Does anyone know if there is a way to group user databases in Management
Studio the same way system databases are grouped within a folder? We have
several databases on one server and it is becoming cumbersome to locate the
database a user wants.
Thanks,
CB
No, this functionality is not currently supported. You can vote for similar
requests though:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=209340
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125921
I created one explicitly for custom grouping of databases in Object
Explorer, since it is not quite the same as the others, and your thoughts
prodded me to realize that I could benefit from this specific change as
well:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=290825
Aaron Bertrand
SQL Server MVP
"chrisb" <chrisb@.discussions.microsoft.com> wrote in message
news:26DBDF02-F34A-4A24-918A-F5F785FEEC24@.microsoft.com...
> Does anyone know if there is a way to group user databases in Management
> Studio the same way system databases are grouped within a folder? We have
> several databases on one server and it is becoming cumbersome to locate
> the
> database a user wants.
> Thanks,
> CB

Grouping by week in stored procedure

Hi all,

I am using the below statement to get some dates grouped by date, in my SP.

SELECT TOP 100 PERCENT COUNT(dbo.test.CallDate) AS CallCount, year(dbo.test.CallDate) AS CallYear, datepart(wk, dbo.test.CallDate) AS [Week]
FROM dbo.test

LEFT OUTER JOIN dbo.view1 ON dbo.test.CallID = dbo.view1.CallID
LEFT OUTER JOIN dbo.view2 ON dbo.test.CallID = dbo.view2.CallID

WHERE (dbo.view1.[ACCOUNT ID] = @.accountid

OR (dbo.view2.[ACCOUNT ID] = @.accountid

AND (convert(varchar(10),dbo.test.CallDate,121) BETWEEN CONVERT(DATETIME, @.StartDate, 102)AND CONVERT(DATETIME, @.EndDate, 102))
GROUP BY year(dbo.test.CallDate), datepart(wk, dbo.test.CallDate)
ORDER BY year(dbo.test.CallDate), datepart(wk, dbo.test.CallDate)

i gave startdate as 1/1/2007 and endDate as 2/18/2007

i am getting the reuslt as
count year week
42 2001 32
2 2001 39
1 2001 51
1 2002 17
1 2002 19
106 2002 21
183 2002 22
226 2002 23
.........................
...........................
1208 2007 1
1292 2007 2

actually i should get only the last 2 rows.
Can anyone please point out, why i am getting the 2001, 2002 data? and how to fix that?

Thanks

Looks to me like you've got your bracketing wrong round your OR clause...

It's currently WHERE ( A OR (B AND C))

Shouldn't it be WHERE (A OR B) AND C?

|||

Thanks

Great help

It worked

Grouping by N minute intervals

I have a query that returns full date/time, hour, and minute, and other
stuff. The report needs to be grouped by various minute intervals, but I am
having difficulty getting it grouped by any interval period.
How do I group my data in 15 Minute Intervals. Is it similar to group by 30
Minute Intervals? I am making the presumption that a variable can be used to
allow the user to change the interval from the values of 15, 20, and 30
Minutes.
I am using SQL Server to fetch the data, and have access to the query code,
so if any suggestions involve something on the Query end instead of the
report end, I can do that too.Rob,
Either you have to use analysis services or create intervals using SQL in
the dataset. I had a smiliar problem with sales reports. In some of the
months we didn't have any sales for a particular product. The reports
instead of showing zero sales, they were not showing up at all. So, I
created zero sales for every product, for a certain period of time - on the
fly and sum group them with actual sales.
Hope this helps.
Regards,
Cem
"Rob 'Spike' Stevens" <RobSpikeStevens@.discussions.microsoft.com> wrote in
message news:D54A54E9-EE31-48A2-8318-2C891F1B533C@.microsoft.com...
> I have a query that returns full date/time, hour, and minute, and other
> stuff. The report needs to be grouped by various minute intervals, but I
am
> having difficulty getting it grouped by any interval period.
> How do I group my data in 15 Minute Intervals. Is it similar to group by
30
> Minute Intervals? I am making the presumption that a variable can be used
to
> allow the user to change the interval from the values of 15, 20, and 30
> Minutes.
> I am using SQL Server to fetch the data, and have access to the query
code,
> so if any suggestions involve something on the Query end instead of the
> report end, I can do that too.

Grouped Stored Procedures

I'm doing some reading up and came across Grouped Stored Procedures.
What are the benefits, of Grouped Stored Procedures?
TIA JTC ^..^
If you refer to having several stored procedures with same name, differentiated by a number, such
as:
EXEC proc
EXEC proc;2
then you'll find that very few are using it, MS are not pushing this feature (just look at the lack
of support in their tools). I see it mostly as a backwards compatibility feature.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns960987A473AA7daveJTC@.217.32.252.50...
> I'm doing some reading up and came across Grouped Stored Procedures.
> What are the benefits, of Grouped Stored Procedures?
> TIA JTC ^..^
|||These work kind of like overloaded functions in traditional programming
languages. Depending on how the procedure is called (e.g. which suffix is
appended to the procedure name), you can dictate externally which version of
the procedure is actually called.
CREATE PROCEDURE dbo.myProc
AS
SELECT 1
GO
CREATE PROCEDURE dbo.myProc;2
AS
SELECT 2
GO
EXEC myProc
EXEC myProc;2
GO
The only benefit I know of is that you can drop them all with one fell
swoop.
-- drops all instances of myProc:
DROP PROCEDURE dbo.myProc
Note that there are a lot of negative side effects to using this feature.
Different management tools, and even different GUIs within SQL Server's own
management tools, will handle these procedures with varying degrees of
success and accuracy. Same goes for external tools such as source code and
project management software packages.
Note also that it is being deprecated (some future version of SQL Server
will no longer support them).
On 2/26/05 8:19 AM, in article Xns960987A473AA7daveJTC@.217.32.252.50, "JTC
^..^" <dave@.nospamJazzTheCat.co.uk> wrote:

> TIA JTC ^..^

Grouped Stored Procedures

I'm doing some reading up and came across Grouped Stored Procedures.
What are the benefits, of Grouped Stored Procedures?
TIA JTC ^..^If you refer to having several stored procedures with same name, differentia
ted by a number, such
as:
EXEC proc
EXEC proc;2
then you'll find that very few are using it, MS are not pushing this feature
(just look at the lack
of support in their tools). I see it mostly as a backwards compatibility fea
ture.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns960987A473AA7daveJTC@.217.32.252.50...
> I'm doing some reading up and came across Grouped Stored Procedures.
> What are the benefits, of Grouped Stored Procedures?
> TIA JTC ^..^|||These work kind of like overloaded functions in traditional programming
languages. Depending on how the procedure is called (e.g. which suffix is
appended to the procedure name), you can dictate externally which version of
the procedure is actually called.
CREATE PROCEDURE dbo.myProc
AS
SELECT 1
GO
CREATE PROCEDURE dbo.myProc;2
AS
SELECT 2
GO
EXEC myProc
EXEC myProc;2
GO
The only benefit I know of is that you can drop them all with one fell
swoop.
-- drops all instances of myProc:
DROP PROCEDURE dbo.myProc
Note that there are a lot of negative side effects to using this feature.
Different management tools, and even different GUIs within SQL Server's own
management tools, will handle these procedures with varying degrees of
success and accuracy. Same goes for external tools such as source code and
project management software packages.
Note also that it is being deprecated (some future version of SQL Server
will no longer support them).
On 2/26/05 8:19 AM, in article Xns960987A473AA7daveJTC@.217.32.252.50, "JTC
^..^" <dave@.nospamJazzTheCat.co.uk> wrote:

> TIA JTC ^..^

Grouped Stored Procedures

I'm doing some reading up and came across Grouped Stored Procedures.
What are the benefits, of Grouped Stored Procedures?
TIA JTC ^..^If you refer to having several stored procedures with same name, differentiated by a number, such
as:
EXEC proc
EXEC proc;2
then you'll find that very few are using it, MS are not pushing this feature (just look at the lack
of support in their tools). I see it mostly as a backwards compatibility feature.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> wrote in message
news:Xns960987A473AA7daveJTC@.217.32.252.50...
> I'm doing some reading up and came across Grouped Stored Procedures.
> What are the benefits, of Grouped Stored Procedures?
> TIA JTC ^..^|||These work kind of like overloaded functions in traditional programming
languages. Depending on how the procedure is called (e.g. which suffix is
appended to the procedure name), you can dictate externally which version of
the procedure is actually called.
CREATE PROCEDURE dbo.myProc
AS
SELECT 1
GO
CREATE PROCEDURE dbo.myProc;2
AS
SELECT 2
GO
EXEC myProc
EXEC myProc;2
GO
The only benefit I know of is that you can drop them all with one fell
swoop.
-- drops all instances of myProc:
DROP PROCEDURE dbo.myProc
Note that there are a lot of negative side effects to using this feature.
Different management tools, and even different GUIs within SQL Server's own
management tools, will handle these procedures with varying degrees of
success and accuracy. Same goes for external tools such as source code and
project management software packages.
Note also that it is being deprecated (some future version of SQL Server
will no longer support them).
On 2/26/05 8:19 AM, in article Xns960987A473AA7daveJTC@.217.32.252.50, "JTC
^..^" <dave@.nospamJazzTheCat.co.uk> wrote:
> TIA JTC ^..^

Grouped information from two tables

Hi,

I have two statements which when I join by a union statement give the folowing:

2005 11 0.000000
2005 12 0.000000
2006 1 0.000000
2006 1 50813.058500
2006 10 0.000000
2006 11 0.000000
2006 12 0.000000
2006 12 63224.511250
2006 2 0.000000
2006 2 59164.234500
2006 3 0.000000
2006 4 0.000000
2006 5 0.000000
2006 6 0.000000
2006 6 82442.570750
2006 7 0.000000
2006 7 61809.497750
2006 8 0.000000
2006 9 0.000000
2007 1 0.000000
2007 2 0.000000
2007 3 0.000000
2007 4 0.000000
2007 5 0.000000
2007 6 0.000000
2007 7 0.000000
2007 8 0.000000

What I want is to merge the values

2006 1 0.000000
2006 1 50813.058500

into one row, the months with zero figures are required.

Thanks

Not the best of descriptions, but I have now solved this problem.

Answer = Ist table joins to second table (Select statement) via a right outer join.

sql

Grouped by Using aliased names

Hi,

I have a select statement that gives me an output as follows:

Date Store Num Location
4-5-2007 0001 NY
4-5-2007 0002 NY
4-5-2007 0002 NY
4-4-2007 0003 PA
4-4-2007 0002 PA

The store num and location columns are derived like so:
LEFT(Table.WholeField, 4) AS 'Store Num',
RIGHT(LEFT(Table.WholeField, 6), 2) AS 'Location'

The problem I am running into is that I have been tasked to write a select statement that sums up distinct values for Store Num and Location. The output should look something like this :

Date Store Num Location Num
4-5-2007 0002 NY 2

However, 'Store Num' and 'Location' comes from one field by design. I have written a select statement that uses the GROUP BY function to get the correct output. However, I am receiving an invalid column name error because I am using an aliased name.

Does anyone have any insight into the error or a possible workaround.

Thanks,
V.Don't use the alias in the Group By, use the expression instead.

GROUP BY LEFT(Table.WholeField, 4), RIGHT(LEFT(Table.WholeField, 6), 2)

or just bury the original query as a subquery, and then sum and group by.

The first version seems "cleaner" to me though|||Thanks for the quick response. The Grouping by for the expressions works.

However, I am still getting:

Date Store Num Location Num
4-5-2007 0002 NY 1
4-5-2007 0002 NY 1

However, I am looking for :

Date Store Num Location Num
4-5-2007 0002 NY 2

Shouldn't the group by statement work with a correct count(*) or do I have to issue counts for the two separate columns?|||Can you post the SQL statement?|||Actually, I figured it out. Thanks for your help! Not thinking straight for some reason today!|||I don't see why this wouldn't work

SELECT [DATE],
LEFT(#TMP.WholeField, 4) AS 'Store Num',
RIGHT(LEFT(#TMP.WholeField, 6), 2) AS 'Location',
COUNT(*)
FROM #TMP
GROUP BY [DATE],
LEFT(#TMP.WholeField, 4),
RIGHT(LEFT(#TMP.WholeField, 6), 2)

That yields

Date Store Num Location Num
4-4-2007 0002 PA 1
4-4-2007 0003 PA 1
4-5-2007 0001 NY 1
4-5-2007 0002 NY 2|||I was using a convert function to take the timestamp field Date and convert it to a MM-DD-YYYY format. However, in the group by statement i was using just the Fieldname Date. When I added the convert function to the group by, it worked.

grouped by month

Dear All,

I'd like to write a query that lists items from a single table but groups the listed items by a date (data of item entered into the table)

So all items matching a criteria and were entered during March should be listed underneath one-another
Then all items matching the same criteria but entered during April should be grouped again.

Not sure what would be the right approach here.

I'm thinkning, creating a temp table putting data in there but altering the data enterd field into just year and month, and then group the result by that field?

Will this work?group by month(Date)

More over
group by year(date), month(date)

Group Query to include 0 values

I have to sum(NoWidgets) produced GROUP'ed By Station. The Widgets Produced
are in a subtable of the Widget Stations, so it works for the most part by
doing this:
Select Sum(NoWidgets) From WidgetData Inner Join StationData GROUP BY Station
Where I run into problems is when I want to sum the number of blue widgets
by Station. Because I want to know even if a station produced 0 blue widgets.
If I say:
Select Sum(NoWidgets) From WidgetData INNER JOIN StationData Where Color =
'Blue' GROUP BY Station
Then the stations that had no blue widgets are not included in the output.
I want ALL stations in the output, even if they have a value of 0. I'm also
wanting to do an average, and I want that based upon all stations, whether or
not any blue widgets were produced.
Seems like it should be easy to do, and I'm sure I'm showing myself to be a
novice for not know how that is done.
Thanks!
On Thu, 9 Mar 2006 13:50:28 -0800, PolarBears wrote:
(snip)
>Where I run into problems is when I want to sum the number of blue widgets
>by Station. Because I want to know even if a station produced 0 blue widgets.
>If I say:
>Select Sum(NoWidgets) From WidgetData INNER JOIN StationData Where Color =
>'Blue' GROUP BY Station
>Then the stations that had no blue widgets are not included in the output.
>I want ALL stations in the output, even if they have a value of 0. I'm also
>wanting to do an average, and I want that based upon all stations, whether or
>not any blue widgets were produced.
Hi PolarBears,
Yes, this is simple. You can use the GROUP BY ALL version of the GROUP
BY clause:
SELECT SUM(NoWidgets)
FROM WidgetDate
INNER JOIN StationData
ON somethin you forgot to include in your post
WHERE Color = 'Blue'
GROUP BY ALL Station
Note that this works in SQL Server 2000 and SQL Server 2005, but the
GROUP BY ALL clause is marked as deprecated in SQL Server 2005 (meaning
it will be removed in a future version).
If you prefer a portable, ANSI-standard version, you can use
SELECT SUM(CASE WHEN Color = 'Blue' THEN NoWidgets ELSE 0 END)
FROM WidgetDate
INNER JOIN StationData
ON somethin you forgot to include in your post
GROUP BY Station
(Note: both queries above are untested. See www.aspfaq.com/5006 ff you
prefer a tested query.)
Hugo Kornelis, SQL Server MVP
|||Thanks a ton!
"Hugo Kornelis" wrote:

> On Thu, 9 Mar 2006 13:50:28 -0800, PolarBears wrote:
> (snip)
> Hi PolarBears,
> Yes, this is simple. You can use the GROUP BY ALL version of the GROUP
> BY clause:
> SELECT SUM(NoWidgets)
> FROM WidgetDate
> INNER JOIN StationData
> ON somethin you forgot to include in your post
> WHERE Color = 'Blue'
> GROUP BY ALL Station
> Note that this works in SQL Server 2000 and SQL Server 2005, but the
> GROUP BY ALL clause is marked as deprecated in SQL Server 2005 (meaning
> it will be removed in a future version).
> If you prefer a portable, ANSI-standard version, you can use
> SELECT SUM(CASE WHEN Color = 'Blue' THEN NoWidgets ELSE 0 END)
> FROM WidgetDate
> INNER JOIN StationData
> ON somethin you forgot to include in your post
> GROUP BY Station
> (Note: both queries above are untested. See www.aspfaq.com/5006 ff you
> prefer a tested query.)
> --
> Hugo Kornelis, SQL Server MVP
>

Group Name in Page Header

When using Grouping, I need to display the value of the grouped field
in the page header (I'm also inserting a page break at the end of each
group). Is this possible?Yes this is possible in your case. Assuming you have a textbox called
GroupName in the grouping header which shows the current value of the
grouping, you can just add another textbox in the page header which
references the value of the GroupName textbox:
=ReportItems!GroupName.Value
Note: only the ReportItems collection is accessible in the page
headers/footers, but not the Fields collection.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"J Chang" <julia.chang@.maritz.com> wrote in message
news:946c3e64.0410221341.3018ec18@.posting.google.com...
> When using Grouping, I need to display the value of the grouped field
> in the page header (I'm also inserting a page break at the end of each
> group). Is this possible?

Group for each half hour

I am trying to make a report that lists phone calls that are grouped by time (using group export). I can do it by hour using the option "section will be printed: for each hour" but i need it to do it for each half hour and there is no option for that. It is possible do to it? How can i do it (In Crystal Reports 10 or 11)?

Thank you.Create a formula @.half_an_hour:

if minute({table.call_date_time})<30 or
(minute({table.call_date_time})=30 and second({table.call_date_time})=0)
then // returns h:00 - h:30
totext(hour({table.call_date_time}),0)&':00 - '&totext(hour({table.call_date_time}),0)&':30'
else // returns h:31 - h:59
totext(hour({table.call_date_time}),0)&':31 - '&totext(hour({table.call_date_time}),0)&':59'

The formula checks to see if the minute<30
or
is equal to 30 and the second=0 then it returns 'h:00 - h:30' otherwise
'h:31 - h:59'

Group your records on {table.cal_date_time} for each hour, suppress the GH and GF of that group;
then group your records on the {@.half_an_hour} formula.

As the 'Grop2 Name' field returns only time info, you will need to insert the {table.cal_date_time} and to format it as 'Date'.|||Thank you for the reply!! I can now group the calls by half hour.

Group By question

Hello!
I would like to get my result grouped by 15 min or 30 min instead of
1 min like I have now, see below.
Can anyone help me ? Any ideas?
select count(*)AS Count_Case, MIN(CAS_DT_OPE) AS TIME_DATE
from s_case (nolock)
where CAS_USG_PK_OPEN=255543
AND day(CAS_DT_OPE)=day(getdate())
AND month(CAS_DT_OPE)=month(getdate())
AND year(CAS_DT_OPE)=year(getdate())
GROUP BY datepart(mi,CAS_DT_OPE)
order by 2
****Result*******************
2 2006-04-27 06:52:54.783
1 2006-04-27 07:22:54.973
1 2006-04-27 07:23:56.493
2 2006-04-27 08:05:04.217
1 2006-04-27 08:07:41.723
1 2006-04-27 08:27:04.600
1 2006-04-27 08:29:37.840
1 2006-04-27 08:33:13.347
1 2006-04-27 08:39:23.620
1 2006-04-27 08:41:35.240
1 2006-04-27 09:12:00.930
2 2006-04-27 09:13:06.540
1 2006-04-27 09:16:34.030
1 2006-04-27 09:18:00.717
2 2006-04-27 09:26:51.957
1 2006-04-27 09:31:31.507
1 2006-04-27 09:53:32.873
regards
MikeFor group by 15 minutes use
GROUP BY datepart(mi,CAS_DT_OPE) /15
For group by 30 minutes use
GROUP BY datepart(mi,CAS_DT_OPE) /30|||Below is an example using an interval table variable. This technique has
the benefit of reporting intervals even when no related data exists. If
this is something you do often, consider creating a permanent table for this
purpose:
SET NOCOUNT ON
DECLARE @.IntervalInSeconds int
DECLARE @.StartTime datetime
DECLARE @.EndTime datetime
---
-- **** specify interval size and range below ***
---
SET @.IntervalInSeconds = 900 -- 15 minutes
SET @.StartTime = '20060427 00:00:00'
SET @.EndTime = '20060428 00:00:00'
---
-- create and load Intervals table
DECLARE @.Intervals TABLE
(
StartTime datetime NOT NULL
PRIMARY KEY,
EndTime datetime NOT NULL
)
WHILE @.StartTime < @.EndTime
BEGIN
INSERT INTO @.Intervals VALUES(@.StartTime, DATEADD(ss,
@.IntervalInSeconds, @.StartTime))
SET @.StartTime = DATEADD(ss, @.IntervalInSeconds, @.StartTime)
END
SELECT
COUNT(*) AS Count_Case,
i.StartTime AS IntervalStart,
MIN(CAS_DT_OPE) AS TIME_DATE
FROM s_case (NOLOCK)
JOIN @.Intervals i ON
s_case.CAS_DT_OPE >= i.StartTime
AND s_case.CAS_DT_OPE < i.EndTime
WHERE
CAS_USG_PK_OPEN=255543
AND CAS_DT_OPE >= CAST(CONVERT(char(8), GETDATE(), 112) AS datetime)
AND CAS_DT_OPE < CAST(CONVERT(char(8), GETDATE(), 112) AS datetime) + 1
GROUP BY
i.StartTime
ORDER BY 3
Hope this helps.
Dan Guzman
SQL Server MVP
"McA" <mikael.ahlberg@.gmail.com> wrote in message
news:1146139098.374595.30350@.v46g2000cwv.googlegroups.com...
> Hello!
> I would like to get my result grouped by 15 min or 30 min instead of
> 1 min like I have now, see below.
> Can anyone help me ? Any ideas?
> select count(*)AS Count_Case, MIN(CAS_DT_OPE) AS TIME_DATE
> from s_case (nolock)
> where CAS_USG_PK_OPEN=255543
> AND day(CAS_DT_OPE)=day(getdate())
> AND month(CAS_DT_OPE)=month(getdate())
> AND year(CAS_DT_OPE)=year(getdate())
> GROUP BY datepart(mi,CAS_DT_OPE)
> order by 2
> ****Result*******************
> 2 2006-04-27 06:52:54.783
> 1 2006-04-27 07:22:54.973
> 1 2006-04-27 07:23:56.493
> 2 2006-04-27 08:05:04.217
> 1 2006-04-27 08:07:41.723
> 1 2006-04-27 08:27:04.600
> 1 2006-04-27 08:29:37.840
> 1 2006-04-27 08:33:13.347
> 1 2006-04-27 08:39:23.620
> 1 2006-04-27 08:41:35.240
> 1 2006-04-27 09:12:00.930
> 2 2006-04-27 09:13:06.540
> 1 2006-04-27 09:16:34.030
> 1 2006-04-27 09:18:00.717
> 2 2006-04-27 09:26:51.957
> 1 2006-04-27 09:31:31.507
> 1 2006-04-27 09:53:32.873
> regards
> Mike
>|||Didn't do the trick...
/Mike|||Thanks Dan that did the trick!!
/Mike|||On 27 Apr 2006 04:58:18 -0700, McA wrote:

>Hello!
>I would like to get my result grouped by 15 min or 30 min instead of
>1 min like I have now, see below.
>Can anyone help me ? Any ideas?
GROUP BY DATEDIFF(minute, '20060101', CAS_DT_OPE) / 15
Hugo Kornelis, SQL Server MVP|||Thanks Hugo that worked 2!!
And very easy to!!
/McA

GROUP BY highest score per user

Hi there!

I've got a SPROC that generates a recordset of user vote tallies (they're calculated in a separated SPROC). The user submissions are grouped by a GUID value so as to remain unique for a user's submission (each user can have multiple submissions.

The problem is that the recordset returned displays ALL the users, and I'd like to only select the highest score for each user. So, if I have 500 submissions from 3 users (User1 and User2 submit once each and User3 submits 497 times), the total recordset will have 3 rows - being the highest score per user, discounting the others.

Here's my base query:

SELECT a.UserID,a.Name AS [Name],SUM(b.TotalTally) AS [TotalPoints]
FROM Users a
INNER JOIN Ballots b ON a.UserID = b.UserID
GROUP BY a.UserID, a.Name,b.SubmissionGUID
ORDER BY [TotalPoints] DESC,[Name] ASC

...and I've been able to get the highest vote per user, discounting duplicate entries, by using this:

SELECT a.UserID,MAX(b.TotalTally) AS [TotalPoints]
FROM Users a
INNER JOIN Ballots b ON a.UserID = b.UserID
GROUP BY a.UserID

How can I write combine the two in a nested subquery to display only the top score per user?further, here are the table schema:

USERS
- UserID (INT)
- Name (VARCHAR)

BALLOTS
- UserID (INT)
- SubmissionGUID (VARCHAR)
- TotalTally (INT) DEFAULT '0' -- this is incremented by varying values as a user makes correct selections
- WinningTeam (VARCHAR)

also, you can just assume that there are multiple submission, with each submission consisting of 63 records in the BALLOTS table, each with the same SubmissionGUID.

so, a user's total point would be the SUM'med value of all of their records grouped by a SubmissionGUID. Thus, a user "JOHN", could have the following:

USER SUBMISSIONGUID NAME TOTALTALLY
-- -- -- ----
1 kugiuvbiu JOHN 45
2 olhilugiu STEVE 32
3 oih98y897 MARK 31
1 89769gibi JOHN 29
1 0980jpo90 JOHN 13

I'd like just to select each unique USER's highest TOTALTALLY and display that, and forget about the others.

Clear as mud? :)

Group by Day

Hi,
This is what I am trying to achieve, but in vain.
I have a report that has to be grouped by "Day" - I have hourly data for a
day which can span for several days based on the report start and end time.
For example if the report duration is 10/01/2004 to 10/05/2004, then I must
see 10/01/2004 day's worth of data (hour 0 thro 23) on a single page with
summary statistics for that day towards the bottom of the page. And then
10/02/2004 on the next page and so on and so forth.
I have 2 matrices - one for detailed data for the day and another one for
the summary (sum, avg, min, max) for the same day. I am trying to group by
"Year", "Month" and "Day" but this is not working. Can anyone please let me
know how to make this work? Any help will be highly appreciated.
ThanksCan anyone please help, I am stuck.
"kmp" <kmp@.discussions.microsoft.com> wrote in message
news:3356ECAC-AB1E-4FB9-A8A2-7A424C687B10@.microsoft.com...
> Hi,
> This is what I am trying to achieve, but in vain.
> I have a report that has to be grouped by "Day" - I have hourly data for a
> day which can span for several days based on the report start and end
time.
> For example if the report duration is 10/01/2004 to 10/05/2004, then I
must
> see 10/01/2004 day's worth of data (hour 0 thro 23) on a single page with
> summary statistics for that day towards the bottom of the page. And then
> 10/02/2004 on the next page and so on and so forth.
> I have 2 matrices - one for detailed data for the day and another one for
> the summary (sum, avg, min, max) for the same day. I am trying to group by
> "Year", "Month" and "Day" but this is not working. Can anyone please let
me
> know how to make this work? Any help will be highly appreciated.
> Thanks|||Create a function in SQL server to strip off the time from the Datetime
field. Do this by coverting to to char(8) then back to datetime. Use this
function to give you the date only. Group by this function and put a page
break after that grouping level.
"kmp" wrote:
> Hi,
> This is what I am trying to achieve, but in vain.
> I have a report that has to be grouped by "Day" - I have hourly data for a
> day which can span for several days based on the report start and end time.
> For example if the report duration is 10/01/2004 to 10/05/2004, then I must
> see 10/01/2004 day's worth of data (hour 0 thro 23) on a single page with
> summary statistics for that day towards the bottom of the page. And then
> 10/02/2004 on the next page and so on and so forth.
> I have 2 matrices - one for detailed data for the day and another one for
> the summary (sum, avg, min, max) for the same day. I am trying to group by
> "Year", "Month" and "Day" but this is not working. Can anyone please let me
> know how to make this work? Any help will be highly appreciated.
> Thanks|||Thanks for the reply - this worked. But I have another problem. When I page
break per day, I would also like to see the summary statistics towards the
bottom of the page per day.
Since I have 2 matrices - one for detailed data (where I page break per day
basis) and another one for the summary statistics (sum, min, max etc.), when
I do a page break on the first matrix, I am not able to get the second matrix
on the same page (because of the page break on the first one). Is there
anyway I can see both the matrices on one page? Any help will be appreciated.
"johnE" wrote:
> Create a function in SQL server to strip off the time from the Datetime
> field. Do this by coverting to to char(8) then back to datetime. Use this
> function to give you the date only. Group by this function and put a page
> break after that grouping level.
> "kmp" wrote:
> > Hi,
> > This is what I am trying to achieve, but in vain.
> >
> > I have a report that has to be grouped by "Day" - I have hourly data for a
> > day which can span for several days based on the report start and end time.
> > For example if the report duration is 10/01/2004 to 10/05/2004, then I must
> > see 10/01/2004 day's worth of data (hour 0 thro 23) on a single page with
> > summary statistics for that day towards the bottom of the page. And then
> > 10/02/2004 on the next page and so on and so forth.
> >
> > I have 2 matrices - one for detailed data for the day and another one for
> > the summary (sum, avg, min, max) for the same day. I am trying to group by
> > "Year", "Month" and "Day" but this is not working. Can anyone please let me
> > know how to make this work? Any help will be highly appreciated.
> >
> > Thanks|||Can someone help me with my problem as posted below? I really appreciate the
help. Thanks.
"kmp" wrote:
> Thanks for the reply - this worked. But I have another problem. When I page
> break per day, I would also like to see the summary statistics towards the
> bottom of the page per day.
> Since I have 2 matrices - one for detailed data (where I page break per day
> basis) and another one for the summary statistics (sum, min, max etc.), when
> I do a page break on the first matrix, I am not able to get the second matrix
> on the same page (because of the page break on the first one). Is there
> anyway I can see both the matrices on one page? Any help will be appreciated.
> "johnE" wrote:
> > Create a function in SQL server to strip off the time from the Datetime
> > field. Do this by coverting to to char(8) then back to datetime. Use this
> > function to give you the date only. Group by this function and put a page
> > break after that grouping level.
> >
> > "kmp" wrote:
> >
> > > Hi,
> > > This is what I am trying to achieve, but in vain.
> > >
> > > I have a report that has to be grouped by "Day" - I have hourly data for a
> > > day which can span for several days based on the report start and end time.
> > > For example if the report duration is 10/01/2004 to 10/05/2004, then I must
> > > see 10/01/2004 day's worth of data (hour 0 thro 23) on a single page with
> > > summary statistics for that day towards the bottom of the page. And then
> > > 10/02/2004 on the next page and so on and so forth.
> > >
> > > I have 2 matrices - one for detailed data for the day and another one for
> > > the summary (sum, avg, min, max) for the same day. I am trying to group by
> > > "Year", "Month" and "Day" but this is not working. Can anyone please let me
> > > know how to make this work? Any help will be highly appreciated.
> > >
> > > Thanks|||Let me see if I understand this.
You have a report with two grouping levels let say by Product then by day so
you get the daily sales for the product but then you get a total for that
product. You want the total for the product to be on the same page as the
last days totals for that product
like this
Product A
Day 1
Order
Order
Total
Page Break
Day2
Order
Order
Total
Product A Total
Page Break
You may be able to achieve the results you are looking for by inserting a
group header for the Group by day level. set that to have a page break
before and set the visibility for the group header to hidden for the first
record of dataset.
"kmp" wrote:
> Thanks for the reply - this worked. But I have another problem. When I page
> break per day, I would also like to see the summary statistics towards the
> bottom of the page per day.
> Since I have 2 matrices - one for detailed data (where I page break per day
> basis) and another one for the summary statistics (sum, min, max etc.), when
> I do a page break on the first matrix, I am not able to get the second matrix
> on the same page (because of the page break on the first one). Is there
> anyway I can see both the matrices on one page? Any help will be appreciated.
> "johnE" wrote:
> > Create a function in SQL server to strip off the time from the Datetime
> > field. Do this by coverting to to char(8) then back to datetime. Use this
> > function to give you the date only. Group by this function and put a page
> > break after that grouping level.
> >
> > "kmp" wrote:
> >
> > > Hi,
> > > This is what I am trying to achieve, but in vain.
> > >
> > > I have a report that has to be grouped by "Day" - I have hourly data for a
> > > day which can span for several days based on the report start and end time.
> > > For example if the report duration is 10/01/2004 to 10/05/2004, then I must
> > > see 10/01/2004 day's worth of data (hour 0 thro 23) on a single page with
> > > summary statistics for that day towards the bottom of the page. And then
> > > 10/02/2004 on the next page and so on and so forth.
> > >
> > > I have 2 matrices - one for detailed data for the day and another one for
> > > the summary (sum, avg, min, max) for the same day. I am trying to group by
> > > "Year", "Month" and "Day" but this is not working. Can anyone please let me
> > > know how to make this work? Any help will be highly appreciated.
> > >
> > > Thanks

Group breaking prematurely

I have a report that simply shows counts, amount and an extended amount
grouped by 1) telephone exchange (exchange) then 2) tariff's being billed
(st_s_usoc). The problem I am having is the tariff group is prematurely
breaking within the group as shown by the example below. I also included the
sql code behind the report further below. Does anyone have any suggestions
for me as to what to look at that could be possibly causing this break. I
created the same exact report in Crystal Reports and the report is grouping
correctly.
Exch USOC Description Quantity Amount Ext
Amount
258
400R Call Forwarding 2 2.65
5.30
401R Busy Call Forward 2 2.65
5.30
404 Call Waiting 1
6.65 6.65
404 Call Waiting 4
6.65 26.60
(there should be one line for the 404 USOC)
SELECT st_s_usoc, st_i_quantity, tm_s_desc_1, tm_m_amount, exchange,
Extended_Amount
FROM vw_VTC_Sub_Tariff_count
WHERE st_dt_start_date < @.StartDateParm AND st_dt_stop_date IS NULL
ORDER BY exchange, st_s_usoc
Any help would be greatly appreciated.Your post is not very clear . So grouping is done first by exchange
( which is column 1 ? ) and then "extended amount" ( is that column
2 ?) .You have written "there should be one line for the 404 USOC" ,
do you mean that this row has a different exchange and should form a
new group ?Explain the abbreveations a bit, what is USOC ?
Cheers
Shai
On Nov 24, 5:02 am, Wishing I was skiing mom
<WishingIwasskiing...@.discussions.microsoft.com> wrote:
> I have a report that simply shows counts, amount and an extended amount
> grouped by 1) telephone exchange (exchange) then 2) tariff's being billed
> (st_s_usoc). The problem I am having is the tariff group is prematurely
> breaking within the group as shown by the example below. I also included the
> sql code behind the report further below. Does anyone have any suggestions
> for me as to what to look at that could be possibly causing this break. I
> created the same exact report in Crystal Reports and the report is grouping
> correctly.
> Exch USOC Description Quantity Amount Ext
> Amount
> 258
> 400R Call Forwarding 2 2.65
> 5.30
> 401R Busy Call Forward 2 2.65
> 5.30
> 404 Call Waiting 1
> 6.65 6.65
> 404 Call Waiting 4
> 6.65 26.60
> (there should be one line for the 404 USOC)
> SELECT st_s_usoc, st_i_quantity, tm_s_desc_1, tm_m_amount, exchange,
> Extended_Amount
> FROM vw_VTC_Sub_Tariff_count
> WHERE st_dt_start_date < @.StartDateParm AND st_dt_stop_date IS NULL
> ORDER BY exchange, st_s_usoc
> Any help would be greatly appreciated.|||Sorry about that, but yes unfortunately what you see on the post doesn't look
exactly like what I had typed. It wrapped the lines a bit. There should be
four lines one for USOC 400R, 401R and two for 404. The problem is the two
404 lines, this should be combined into one line, I can not figure out what
is causing the report to break into two lines. I'm stuck. USOC(stands for
Universal Service ', basically it's a telephone service tariff)
Thank you for attention regarding this.
Jackie
"shaikat.das@.gmail.com" wrote:
> Your post is not very clear . So grouping is done first by exchange
> ( which is column 1 ? ) and then "extended amount" ( is that column
> 2 ?) .You have written "there should be one line for the 404 USOC" ,
> do you mean that this row has a different exchange and should form a
> new group ?Explain the abbreveations a bit, what is USOC ?
> Cheers
> Shai
>
> On Nov 24, 5:02 am, Wishing I was skiing mom
> <WishingIwasskiing...@.discussions.microsoft.com> wrote:
> > I have a report that simply shows counts, amount and an extended amount
> > grouped by 1) telephone exchange (exchange) then 2) tariff's being billed
> > (st_s_usoc). The problem I am having is the tariff group is prematurely
> > breaking within the group as shown by the example below. I also included the
> > sql code behind the report further below. Does anyone have any suggestions
> > for me as to what to look at that could be possibly causing this break. I
> > created the same exact report in Crystal Reports and the report is grouping
> > correctly.
> >
> > Exch USOC Description Quantity Amount Ext
> > Amount
> > 258
> > 400R Call Forwarding 2 2.65
> > 5.30
> > 401R Busy Call Forward 2 2.65
> > 5.30
> > 404 Call Waiting 1
> > 6.65 6.65
> > 404 Call Waiting 4
> > 6.65 26.60
> >
> > (there should be one line for the 404 USOC)
> >
> > SELECT st_s_usoc, st_i_quantity, tm_s_desc_1, tm_m_amount, exchange,
> > Extended_Amount
> > FROM vw_VTC_Sub_Tariff_count
> > WHERE st_dt_start_date < @.StartDateParm AND st_dt_stop_date IS NULL
> > ORDER BY exchange, st_s_usoc
> >
> > Any help would be greatly appreciated.
>|||On Nov 23, 3:02 pm, Wishing I was skiing mom
<WishingIwasskiing...@.discussions.microsoft.com> wrote:
> I have a report that simply shows counts, amount and an extended amount
> grouped by 1) telephone exchange (exchange) then 2) tariff's being billed
> (st_s_usoc). The problem I am having is the tariff group is prematurely
> breaking within the group as shown by the example below. I also included the
> sql code behind the report further below. Does anyone have any suggestions
> for me as to what to look at that could be possibly causing this break. I
> created the same exact report in Crystal Reports and the report is grouping
> correctly.
> Exch USOC Description Quantity Amount Ext Amount
> 258
> 400R Call Forwarding 2 2.65 5.30
> 401R Busy Call Forward 2 2.65 5.30
> 404 Call Waiting 1 6.65 6.65
> 404 Call Waiting 4 6.65 26.60
> (there should be one line for the 404 USOC)
>
> Any help would be greatly appreciated.
I would check your data in preview filtering on EXCHANGE=404... The
query should return only the 5 rows, then change your SQL to do a
GROUP BY so the server returns the data in the form that you expect.
If you still see two groups with the 404 data, then it's a data
issue.
Make sure that your data fields are not right-padded with spaces, and
that your database converts zero-length strings to NULLs (perhaps the
USOC field, though appearing empty, really isn't). Try playing with
the TRIM command to truncate trailing spaces.
In the Expressions, try an Expression of
= "(" & Fields!ABCXYZ.Value & ")"
to make sure that there aren't any weird characters appended to your
strings.
One thing we ran into recently was Char(191) in a Memo field -- a
"hard space" that in HTML rendered as a space but to a String Compare
(which is all a Matrix grouping is) they are different.
-- Scott|||Thanks Scott for your suggestions,
At least I was able to eliminate the possibility of it being a data issue,
after using your group by suggestion. I am going to forward this issue on to
a R.S. instructor I had and hopefully he knows what might be causing this.
Then perhaps a MSDN incident, who knows may it's a bug.
Thanks again,
Jackie
"Orne" wrote:
> On Nov 23, 3:02 pm, Wishing I was skiing mom
> <WishingIwasskiing...@.discussions.microsoft.com> wrote:
> > I have a report that simply shows counts, amount and an extended amount
> > grouped by 1) telephone exchange (exchange) then 2) tariff's being billed
> > (st_s_usoc). The problem I am having is the tariff group is prematurely
> > breaking within the group as shown by the example below. I also included the
> > sql code behind the report further below. Does anyone have any suggestions
> > for me as to what to look at that could be possibly causing this break. I
> > created the same exact report in Crystal Reports and the report is grouping
> > correctly.
> >
> > Exch USOC Description Quantity Amount Ext Amount
> > 258
> > 400R Call Forwarding 2 2.65 5.30
> > 401R Busy Call Forward 2 2.65 5.30
> > 404 Call Waiting 1 6.65 6.65
> > 404 Call Waiting 4 6.65 26.60
> >
> > (there should be one line for the 404 USOC)
> >
> >
> > Any help would be greatly appreciated.
> I would check your data in preview filtering on EXCHANGE=404... The
> query should return only the 5 rows, then change your SQL to do a
> GROUP BY so the server returns the data in the form that you expect.
> If you still see two groups with the 404 data, then it's a data
> issue.
> Make sure that your data fields are not right-padded with spaces, and
> that your database converts zero-length strings to NULLs (perhaps the
> USOC field, though appearing empty, really isn't). Try playing with
> the TRIM command to truncate trailing spaces.
> In the Expressions, try an Expression of
> = "(" & Fields!ABCXYZ.Value & ")"
> to make sure that there aren't any weird characters appended to your
> strings.
> One thing we ran into recently was Char(191) in a Memo field -- a
> "hard space" that in HTML rendered as a space but to a String Compare
> (which is all a Matrix grouping is) they are different.
> -- Scott
>