Need to count and group in units of ten

ok
well this seems easy enough so here it is.
I have a list of some 131500 records with 98 distinct employees thus there are several thousand records per employee. I am using reporting services and I need to set up the graph in units of ten. Having 98 employees on one graph will not work so I need to break them up in units of ten. I can't seem to remember the sql I need to write to accomplish this

any thoughts?
km

Assuming you are using sql server 2005, Can you try using

row_number() over (order by employee_id) as EmpIndex

for getting unique IDs and then split them based on EmpIndex/10=1, ...=9 etc.

|||

I 'think' that it would be far easier to help you if you could provide the table DDL, some sample data in the form of INSERT statements, and a display of your desired results.

For help with that, check:http://www.aspfaq.com/5006

|||thanks for the quick response-- here is some of the sql I have been playing with

SELECT
PH_ado_dac_no as DacNumber,
PH_ado_bimon as BillMonth,
PH_ado_biyr as BillYear,
[GH2_GroupName_(_ado_Bill_seq_no_)]as BillSeqNumber,
[PH_GroupName_(_ado_subscriber_no_)],
GH3_ado_unt as Unt_Employee,
DE_ado_CHANNEL_SEIZURE_DT as ChannelSeizureDate,
DE_ado_tim as ChannelSeizureTime,
DE_ado_CALL_TO_CITY_DESC as Call_To_City,
DE_ado_CALL_TO_STATE_CODE as Call_To_State,
DE_ado_tn as Number_To_From,
DE_checkserv AS CallDurationMinSec,
ROW_NUMBER() OVER(ORDER BY GH3_ado_unt ASC)/10 AS 'groupID',
DE_ado_at_charge_amt as Usage_At_Charge_Amt,
DE_ado_toll_charge_amt AS LongDistance_Toll_Charge_Amt,
DE_ado_tot as Total,
GF3_checkservtot as TotalCheckServe,
GF3_Total as TotalMins,
GF3_ac as TotalLongDistanceCharge
FROM
[cdtable1]

GROUP BY
PH_ado_dac_no,
'groupID'

this looks like something I have been trying to use but does not give the desired results since there may be hundreds of entries within the dataset with the same emp_id. when I use row_number() over (order by employee_id) as EmpIndex /10, the same person has multiple EmpIndex's-- I need each employee to have one and only one EmpIndex and group those in units of ten.
thanks again
km

|||thanks for the response-- I posted some additional information to the post previous to yours

km
|||

I should have mentioned this, sorry, can you add partition by inside the Over() clause.

row_number() over ( partition by employee_id order by employee_id)

|||Great this is looking much better
appreciate your help :-)
km

Need some practice

Please point me to a web resource from where I can study:

1) writing complex queries such as those involving HAVING, mult-level
nested queries, GROUP BY, T-SQL functions

2) Joins - a lot of practice

3) Stored Procedures, transactions, cursors and triggers - I need some
heavy-duty practice

Where can I get some good practice of the above? Also, please recommend
a good SQL Server/T-SQL book in the light of the above requirement.Here's a good one for joins. I like the colors for the join tables:

http://www.tek-tips.com/faqs.cfm?fid=4785|||Have you gotten a copy of SQL FOR SMARTIES yet? It is required for
working SQL programmers. And my house payment :)|||> Have you gotten a copy of SQL FOR SMARTIES yet? It is required for
> working SQL programmers. And my house payment :)

The book is of little use to working SQL programmers, a lot of the examples
aren't directly useable in SQL Server nor do they scale into the real world.

People are better off spending a couple of minutes searching GOOGLE for the
answers.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1144801857.130733.158410@.i40g2000cwc.googlegr oups.com...
> Have you gotten a copy of SQL FOR SMARTIES yet? It is required for
> working SQL programmers. And my house payment :)

Need some practice

Please point me to a web resource from where I can study:
1) writing complex queries such as those involving HAVING, mult-level
nested queries, GROUP BY, T-SQL functions
2) Joins - a lot of practice
3) Stored Procedures, transactions, cursors and triggers - I need some
heavy-duty practice
Where can I get some good practice of the above? Also, please recommend
a good SQL Server/T-SQL book in the light of the above requirement.Some basic sites that have a couple examples...
http://www.w3schools.com/sql/sql_intro.asp
http://sqlzoo.net/
http://www.geocities.com/SiliconVal.../2207/sql1.html
If you want more comlicated examples, this forum may be the most useful.
You will have to sort through a lot of different posts that do not have what
you are looking for, but you will find dozens of solutions to various
problems that you can learn from.
"Water Cooler v2" <wtr_clr@.yahoo.com> wrote in message
news:1144682834.749751.324310@.i39g2000cwa.googlegroups.com...
> Please point me to a web resource from where I can study:
> 1) writing complex queries such as those involving HAVING, mult-level
> nested queries, GROUP BY, T-SQL functions
> 2) Joins - a lot of practice
> 3) Stored Procedures, transactions, cursors and triggers - I need some
> heavy-duty practice
>
> Where can I get some good practice of the above? Also, please recommend
> a good SQL Server/T-SQL book in the light of the above requirement.
>|||Here's a good one for joins. I like the colors for the join tables:
http://www.tek-tips.com/faqs.cfm?fid=4785|||Have you gotten a copy of SQL FOR SMARTIES yet? It is required for
working SQL programmers. And my house payment :)|||> Have you gotten a copy of SQL FOR SMARTIES yet? It is required for
> working SQL programmers. And my house payment :)
The book is of little use to working SQL programmers, a lot of the examples
aren't directly useable in SQL Server nor do they scale into the real world.
People are better off spending a couple of minutes searching GOOGLE for the
answers.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1144801857.130733.158410@.i40g2000cwc.googlegroups.com...
> Have you gotten a copy of SQL FOR SMARTIES yet? It is required for
> working SQL programmers. And my house payment :)
>

Need replacement for sybase list aggregate

Sybase has an aggregate function called list that concatenates the
results into a scalar.
sybase eg:
select dept,list(description) from parts group by dept
This query would return one row for each department. Each row would
contain all the description text for that department.
Is there any way to do the same in TSQL without using a cursor? I'd
like to do this in a subquery.
TIAThere is none. There are certain workarounds though; see:
http://groups.google.com/group/micr...3e?dmode=source
In SQL 2005 you can use some complex methods using XML with CROSS APPLY, but
the most reliable way is to use the server to return the tabular resultset
and use a client side programming language to get concatenate/ format the
way you need. In certain situations, you might be able to use the PIVOT
operator as well.
Anith|||Consider 'type' is your department and 'name' is the descriptions then this
should do what you want...
SQL Server 2005 only...
select type,
(
select name + ',' as [text()]
from sys.objects soi
where soi.type = t.type
order by name
for xml path( '' ), root( 'sysobjects' ), type
)
from ( select distinct type from sys.objects ) as t
Will give output like this...
D <sysobjects>DF__spt_value__statu__436BFEE3,</sysobjects>
IT
<sysobjects> queue_messages_1003150619,queue_messages
_1035150733,queue_messag
es_1067150847,</sysobjects>
P <sysobjects> sp_MSrepl_startup,sp_MScleanupmergepubli
sher,</sysobjects>
S
<sysobjects> sysrowsetcolumns,sysrowsets,sysallocunit
s,sysfiles1,syshobtcolum
ns,</sysobjects>
SQ
<sysobjects> QueryNotificationErrorsQueue,EventNotifi
cationErrorsQueue,Servic
eBrokerQueue,</sysobjects>
U
<sysobjects> spt_fallback_db,spt_fallback_dev,spt_fal
lback_usg,spt_monitor,sp
t_values,</sysobjects>
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Darren" <darren@.nospam.com> wrote in message
news:uGD%23BWc%23FHA.2608@.tk2msftngp13.phx.gbl...
> Sybase has an aggregate function called list that concatenates the results
> into a scalar.
> sybase eg:
> select dept,list(description) from parts group by dept
> This query would return one row for each department. Each row would
> contain all the description text for that department.
> Is there any way to do the same in TSQL without using a cursor? I'd like
> to do this in a subquery.
> TIA|||Have you seen the FOR XML extension that will allow you to do this in the
engine without the need of sending more data to the client.
Its one of the examples I now use of when, for scalability and complexity
reduction this should be done in engine rather than client side.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:O8YqVbc%23FHA.3608@.TK2MSFTNGP09.phx.gbl...
> There is none. There are certain workarounds though; see:
> http://groups.google.com/group/micr...3e?dmode=source
> In SQL 2005 you can use some complex methods using XML with CROSS APPLY,
> but the most reliable way is to use the server to return the tabular
> resultset and use a client side programming language to get concatenate/
> format the way you need. In certain situations, you might be able to use
> the PIVOT operator as well.
> --
> Anith
>|||I know, but I am not sure if the OP is using SQL 2005 or not.
Anith

Need Page break for Every Odd Page

Hi,
I have a table with group which populated on multiple pages. If the group
ended with odd page then I need an extra page break (1 blank page).
pls get me the details on that.
Thanks & Regards
Gopi RHi,
I expecting some Suggestion but no one answer to this question. pls give
the suggestion.
Thanks & Regards
Gopi R
"Gopala Krishnan" <Gopal@.photoninfotech.com> wrote in message
news:uhB83VCYFHA.3356@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have a table with group which populated on multiple pages. If the group
> ended with odd page then I need an extra page break (1 blank page).
> pls get me the details on that.
> Thanks & Regards
> Gopi R
>
>
>

Need MAX from Each Group

Hello,

I have a table called 'tblCLTestScores' that holds student test scores
for one particular test. Students take this same test three times on
different dates, so the table contains three or fewer records for each
student. The records for any given student are made distinct by the ID
number of the test booklet, and the date on which they took the test.

I need a query that will, for every student, extract the record with
the MAX 'TestDate'.

The fields that I need to query from 'tblCLTestScores' are as
follows:

TS. StudentID, TS.LSScale, TS.RScale, TS.WScale, TS.LSLevel,
TS.RLevel, TS.WLevel, TS.OLevel, TS.SLSLevel, TS.SRLevel, TS.SWLevel,
TS.BookID,
TS.TestDate,
TS.SOLevel

I've tried a couple of things using MAX(TS.TestDate), but I always get
the maximum TestDate for all of the records, and not the maximum
TestDate for each student.

How do I set up the query to ge the MAX TestDate for each student?

Thank you for your help!

CSDunnHi

It is not clear how you differentiate testA from testB Assuming you have a
TestId try something like:

SELECT
TS.StudentID, TS.LSScale, TS.RScale, TS.WScale, TS.LSLevel,
TS.RLevel, TS.WLevel, TS.OLevel, TS.SLSLevel, TS.SRLevel, TS.SWLevel,
TS.BookID,
TS.TestDate,
TS.SOLevel
FROM tblCLTestscores TS JOIN
( SELECT StudentID, TestId, MAX(TestDate) AS TestDate
FROM tblCLTestscores
GROUP BY StudentID, TestId ) DT ON TS.StudentID = DT.StudentID AND TS.TestId
= DT.TestId
AND TS.TestDate =DT.TestDate

John

"CSDunn" <cdunn@.valverde.edu> wrote in message
news:807dbff7.0406071022.28823376@.posting.google.c om...
> Hello,
> I have a table called 'tblCLTestScores' that holds student test scores
> for one particular test. Students take this same test three times on
> different dates, so the table contains three or fewer records for each
> student. The records for any given student are made distinct by the ID
> number of the test booklet, and the date on which they took the test.
> I need a query that will, for every student, extract the record with
> the MAX 'TestDate'.
> The fields that I need to query from 'tblCLTestScores' are as
> follows:
> TS. StudentID, TS.LSScale, TS.RScale, TS.WScale, TS.LSLevel,
> TS.RLevel, TS.WLevel, TS.OLevel, TS.SLSLevel, TS.SRLevel, TS.SWLevel,
> TS.BookID,
> TS.TestDate,
> TS.SOLevel
> I've tried a couple of things using MAX(TS.TestDate), but I always get
> the maximum TestDate for all of the records, and not the maximum
> TestDate for each student.
> How do I set up the query to ge the MAX TestDate for each student?
> Thank you for your help!
> CSDunn|||>> I have a table called 'tblCLTestScores' ... <<

Take off that silly, redundant "tbl-" prefix that you added in
violation of ISO-11179 Standards.

>> .. that holds student test scores for one particular test. Students
take this same test three times on different dates, so the table
contains three or fewer records [sic] for each student. <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. They are totally different
concepts.

>> The records [sic] for any given student are made distinct by the ID
number of the test booklet, and the date on which they took the test.
<<

A great natural key!

>> I need a query that will, for every student, extract the record
with the MAX 'TestDate'. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. You ave us a bunch of qualified names without so much
as a table.

>> How do I set up the query to ge the MAX TestDate for each student?
<<

SELECT S1.student_id, ...
FROM CLTestScores AS S1
WHERE test_date
= (SELECT MAX(S2.test_date)
FROM CLTestScores AS S2
WHERE S1.student_id = S2.student_id);|||>> I have a table called 'tblCLTestScores' ... <<

Take off that silly, redundant "tbl-" prefix that you added in
violation of ISO-11179 Standards.

>> .. that holds student test scores for one particular test. Students
take this same test three times on different dates, so the table
contains three or fewer records [sic] for each student. <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. They are totally different
concepts.

>> The records [sic] for any given student are made distinct by the ID
number of the test booklet, and the date on which they took the test.
<<

A great natural key! Verifiable, clean, etc. But please post DDL, so
that people do not have to guess what the keys, constraints,
Declarative Referential Integrity, datatypes, etc. in your schema are.
Sample data is also a good idea, along with clear specifications.
You ave us a bunch of qualified names without so much as a table.

>> How do I set up the query to get the MAX(test_date) for each
student? <<

SELECT S1.student_id, ...
FROM CLTestScores AS S1
WHERE test_date
= (SELECT MAX(S2.test_date)
FROM CLTestScores AS S2
WHERE S1.student_id = S2.student_id);

Need immediate help with drill down on deployed reports

When using a group by in the query rendering a report, the drill down menus
donâ't work. When the collapsed icon is clicked, nothing happens. Exporting
to an Excel file shows the additional rows and they also show up in preview
mode. When exporting to a PDF they are still hidden. Has anyone else had
this problem? Any fixes?Amanda,
Are you running your query directly in the dataset Text box, or have you
saved it as a stored procedure and run it as such? If running directly, try
creating a user SP and running that. See if that makes a difference.
The only other thing I can think of is if you have your data hidden based on
a parameter and, for some reason, the Expression in your hidden property
isn't set right.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Amanda Adcock" wrote:
> When using a group by in the query rendering a report, the drill down menus
> donâ't work. When the collapsed icon is clicked, nothing happens. Exporting
> to an Excel file shows the additional rows and they also show up in preview
> mode. When exporting to a PDF they are still hidden. Has anyone else had
> this problem? Any fixes?
>|||Thank you for the suggestion. For anyone else having the same problem here
is the solution (at least in my case):
I found that the problem was related to my machine name. Our machine name
contained an underscore (server_name). After correcting this and making
necessary updates to my reports, the drill downs work.

Need Help! New to reporting services

I need to populate a detail cell in a table report with 0 if the value of a
group cell in the report is > 500000. I've created on iif statement and it
works great when pulling data into the cell from the query. However, I need
to know how to call the value of a specific table cell into the iif statement.
=iif(Value of group cell in table report > 500000, 0,
iif(Fields!AssetCost.Value < 5000, "0",
iif(Fields!AssetCost.value <= 100000, fields!AssetCost.Value*.01, "0")))
SamyraIIf(ReportItems!YourItem.Value>500000,0,False)
Michael
"Samyra" wrote:
> I need to populate a detail cell in a table report with 0 if the value of a
> group cell in the report is > 500000. I've created on iif statement and it
> works great when pulling data into the cell from the query. However, I need
> to know how to call the value of a specific table cell into the iif statement.
> =iif(Value of group cell in table report > 500000, 0,
> iif(Fields!AssetCost.Value < 5000, "0",
> iif(Fields!AssetCost.value <= 100000, fields!AssetCost.Value*.01, "0")))
> Samyra

Need help writing query

I have a table with items bought by the sec. I would like to group them by 5
min intervals round the clock for a day and compare them with the same value
for other days of the w around the same time to get a percentage trend.
So input in a table A for eg: would be
TableA
Date1 Count1
2/1/2006 00:01 1
2/1/2006 00:03 1
2/1/2006 00:05 1
2/1/2006 00:07 1
2/1/2006 00:09 1
2/1/2006 00:11 1
2/1/2006 00:16 1
2/1/2006 01:03 1
2/1/2006 01:05 1
2/1/2006 01:13 1
2/2/2006 00:01 1
2/2/2006 00:03 1
2/2/2006 00:05 1
2/2/2006 00:07 1
2/2/2006 00:08 1
2/2/2006 00:09 1
2/2/2006 00:11 1
2/2/2006 00:16 1
2/2/2006 01:03 1
2/2/2006 01:05 1
2/2/2006 01:13 1
Create table tableA
(Date1 datetime,
count1 int)
insert tableA values('2/1/2006 00:01 ' , 1)
insert tableA values('2/1/2006 00:03' , 1)
insert tableA values('2/1/2006 00:05' , 1)
insert tableA values('2/1/2006 00:07' , 1)
insert tableA values('2/1/2006 00:09' , 1)
insert tableA values('2/1/2006 00:11' , 1)
insert tableA values('2/1/2006 00:16' , 1)
insert tableA values('2/1/2006 01:03' , 1)
insert tableA values('2/1/2006 01:05' , 1)
insert tableA values('2/1/2006 01:13' , 1)
insert tableA values('2/2/2006 00:01' , 1)
insert tableA values('2/2/2006 00:03' , 1)
insert tableA values('2/2/2006 00:05' , 1)
insert tableA values('2/2/2006 00:07' , 1)
insert tableA values('2/2/2006 00:08' , 1)
insert tableA values('2/2/2006 00:09' , 1)
insert tableA values('2/2/2006 00:11' , 1)
insert tableA values('2/2/2006 00:16' , 1)
insert tableA values('2/2/2006 01:03' , 1)
insert tableA values('2/2/2006 01:05' , 1)
insert tableA values('2/2/2006 01:13' , 1)
Output required
Hr IntervalPeriod 2/1/2006(TotalCount) 2/2/2006(TotalCount)
00 0 0 0
00 5 2 2
00 10 3 4
00 15 1 1
00 20 1 1
00 25 0 0
00 30 0 0
00 35 0 0
00 40 0 0
00 45 0 0
00 50 0 0
00 55 0 0
01 00 0 0
01 5 2 2
01 10 3 3
01 15 1 1
01 20 1 1
01 25 0 0
01 30 0 0
01 35 0 0
01 40 0 0
01 45 0 0
01 50 0 0
01 55 0 0
........
As you can see, I would like to group within 5 minute intervals of the hour.
I would then like to pivot the dates so I can trend day over day. Ideally Id
like to group daily for 7 days .. that way its not dynamic
Can someone assist ?Hassan
Take a look at Erland's example. Perhaps it is not exactly what you wanted
but it certainly give you an idea
CREATE TABLE sessions (start datetime NOT NULL,
stop datetime NULL)
go
SET DATEFORMAT dmy
go
SELECT TOP 80000 n = identity(int, 1, 1)
INTO numbers
FROM Northwind..Orders a
CROSS JOIN Northwind..Orders b
go
INSERT sessions (start, stop)
SELECT '22/11/2004 14:02', '22/11/2004 17:30' UNION
SELECT '22/11/2004 09:00', '22/11/2004 17:12' UNION
SELECT '22/11/2004 10:25', '22/11/2004 16:30' UNION
SELECT '22/11/2004 11:02', '22/11/2004 12:30' UNION
SELECT '22/11/2004 16:00', '22/11/2004 17:30' UNION
SELECT '22/11/2004 16:00', '22/11/2004 16:05' UNION
SELECT '22/11/2004 16:06', '22/11/2004 16:10'
go
CREATE PROCEDURE get_peaks @.start datetime,
@.stop datetime,
@.len smallint AS
SELECT intstart, intstop = dateadd(mi, @.len, intstart), MAX(cnt)
FROM (SELECT intstart = dateadd(mi, @.len *
(datediff(mi, @.start, a.minute) / @.len), @.start),
a.cnt
FROM (SELECT mi.minute, cnt = COUNT(s.start)
FROM (SELECT minute = dateadd(mi, n, @.start)
FROM numbers
WHERE n <= datediff(mi, @.start, @.stop)) AS mi
LEFT JOIN sessions s
ON mi.minute BETWEEN s.start AND s.stop
GROUP BY mi.minute) AS a
) AS b
GROUP BY intstart
ORDER BY intstart
go
EXEC get_peaks '20041122 08:00', '20041122 18:00', 5
go
DROP TABLE numbers
DROP TABLE sessions
DROP PROCEDURE get_peaks
"Hassan" <Hassan@.hotmail.com> wrote in message
news:eSVEB7wOGHA.2124@.TK2MSFTNGP14.phx.gbl...
>I have a table with items bought by the sec. I would like to group them by
>5 min intervals round the clock for a day and compare them with the same
>value for other days of the w around the same time to get a percentage
>trend.
> So input in a table A for eg: would be
> TableA
> Date1 Count1
> 2/1/2006 00:01 1
> 2/1/2006 00:03 1
> 2/1/2006 00:05 1
> 2/1/2006 00:07 1
> 2/1/2006 00:09 1
> 2/1/2006 00:11 1
> 2/1/2006 00:16 1
> 2/1/2006 01:03 1
> 2/1/2006 01:05 1
> 2/1/2006 01:13 1
> 2/2/2006 00:01 1
> 2/2/2006 00:03 1
> 2/2/2006 00:05 1
> 2/2/2006 00:07 1
> 2/2/2006 00:08 1
> 2/2/2006 00:09 1
> 2/2/2006 00:11 1
> 2/2/2006 00:16 1
> 2/2/2006 01:03 1
> 2/2/2006 01:05 1
> 2/2/2006 01:13 1
>
> Create table tableA
> (Date1 datetime,
> count1 int)
> insert tableA values('2/1/2006 00:01 ' , 1)
> insert tableA values('2/1/2006 00:03' , 1)
> insert tableA values('2/1/2006 00:05' , 1)
> insert tableA values('2/1/2006 00:07' , 1)
> insert tableA values('2/1/2006 00:09' , 1)
> insert tableA values('2/1/2006 00:11' , 1)
> insert tableA values('2/1/2006 00:16' , 1)
> insert tableA values('2/1/2006 01:03' , 1)
> insert tableA values('2/1/2006 01:05' , 1)
> insert tableA values('2/1/2006 01:13' , 1)
> insert tableA values('2/2/2006 00:01' , 1)
> insert tableA values('2/2/2006 00:03' , 1)
> insert tableA values('2/2/2006 00:05' , 1)
> insert tableA values('2/2/2006 00:07' , 1)
> insert tableA values('2/2/2006 00:08' , 1)
> insert tableA values('2/2/2006 00:09' , 1)
> insert tableA values('2/2/2006 00:11' , 1)
> insert tableA values('2/2/2006 00:16' , 1)
> insert tableA values('2/2/2006 01:03' , 1)
> insert tableA values('2/2/2006 01:05' , 1)
> insert tableA values('2/2/2006 01:13' , 1)
> Output required
> Hr IntervalPeriod 2/1/2006(TotalCount) 2/2/2006(TotalCount)
> 00 0 0 0
> 00 5 2 2
> 00 10 3 4
> 00 15 1 1
> 00 20 1 1
> 00 25 0 0
> 00 30 0 0
> 00 35 0 0
> 00 40 0 0
> 00 45 0 0
> 00 50 0 0
> 00 55 0 0
> 01 00 0 0
> 01 5 2 2
> 01 10 3 3
> 01 15 1 1
> 01 20 1 1
> 01 25 0 0
> 01 30 0 0
> 01 35 0 0
> 01 40 0 0
> 01 45 0 0
> 01 50 0 0
> 01 55 0 0
> ........
>
> As you can see, I would like to group within 5 minute intervals of the
> hour. I would then like to pivot the dates so I can trend day over day.
> Ideally Id like to group daily for 7 days .. that way its not dynamic
> Can someone assist ?
>
>|||On Sun, 26 Feb 2006 11:56:36 -0800, Hassan wrote:

>I have a table with items bought by the sec. I would like to group them by
5
>min intervals round the clock for a day and compare them with the same valu
e
>for other days of the w around the same time to get a percentage trend.
(snip)
Hi Hassan,
Thanks for posting CREATE TABLE and INSERT statements and expected
output. This made it very easy to develop the query below, which will
return the expected results, BUT:
1. Rows with only 0 count are excluded. If you really need them, you'll
have to add a numbers table to the query to get the desired result (let
me know if you need assistance with that part as well)
2. Not all output matches your expected output. I think that the errors
are in your post. If not, I must have misunderstood the requirements.
Anyway, here's the query:
DECLARE @.BaseDate datetime
SET @.BaseDate = '20060201'
SELECT FiveMinIntervals / 12 AS Hours,
FiveMinIntervals % 12 * 5 AS Minutes,
SUM(CASE WHEN Days = 0 THEN count1 ELSE 0 END) AS Day1,
SUM(CASE WHEN Days = 1 THEN count1 ELSE 0 END) AS Day2,
-- repeat some more times
SUM(CASE WHEN Days = 6 THEN count1 ELSE 0 END) AS Day7
FROM (SELECT DATEDIFF(day, @.BaseDate, Date1) AS Days,
DATEDIFF(minute, @.BaseDate, Date1) / 5 % 288
AS FiveMinIntervals,
count1
FROM tableA) AS d
GROUP BY FiveMinIntervals
Hugo Kornelis, SQL Server MVP