Need summary rows in a query, but how?

I have ORDERS which contain a number of ITEMS. Both are stored as rows in a
single table, tblOrders.
I would like to make a query that returns a list of items, then a total for
the order as a whole. Something like...
ORDER ID PART ID NAME QUANTITY PRICE NET
1000 1 widget 10 10 100
1000 2 gazeeza 5 5 25
125 < summary row
It appears this is the idea behind CUBE or ROLLUP, but as is typical, the
documentation on this is useless. Does anyone have any examples of how to
actually use this and get the output the way you want?
It appears you have to use GROUP BY for this sort of thing, and this brings
up another question. In the examples above, I want the grouping for the
summary to work only on the ORDER ID. However, as far as I can tell, in order
to get any output at all you need to list every column in the GROUP BY. This
kind of defeats the purpose in this case.
Any pointers?
Maury
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:1AFFFE59-A8F4-4BD1-8626-C9757BE6597C@.microsoft.com...

> ORDER ID PART ID NAME QUANTITY PRICE NET
> 1000 1 widget 10 10 100
> 1000 2 gazeeza 5 5 25
> 125 < summary row
>
That is not the result of a query. It's a report. It usually makes more
sense to use tools like reporting services for this kind of thing.
To do it in SQL you won't need CUBE/ROLLUP. UNION is probably more
appropriate:
SELECT order_id, tot, part_id, name, quantity, price, net
FROM
(SELECT 0 AS tot, order_id, part_id, name, quantity, price, net
FROM tbl_orders
UNION ALL
SELECT 1, order_id, NULL, NULL, NULL, NULL, SUM(net)
FROM tbl_orders
GROUP BY order_id) AS T
ORDER BY order_id, tot, part_id ;
Apparently your Order table is very denormalized. I hope and expect that you
are aware of that.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
sql

Need summary rows in a query, but how?

I have ORDERS which contain a number of ITEMS. Both are stored as rows in a
single table, tblOrders.
I would like to make a query that returns a list of items, then a total for
the order as a whole. Something like...
ORDER ID PART ID NAME QUANTITY PRICE NET
1000 1 widget 10 10 100
1000 2 gazeeza 5 5 25
125 < summary row
It appears this is the idea behind CUBE or ROLLUP, but as is typical, the
documentation on this is useless. Does anyone have any examples of how to
actually use this and get the output the way you want?
It appears you have to use GROUP BY for this sort of thing, and this brings
up another question. In the examples above, I want the grouping for the
summary to work only on the ORDER ID. However, as far as I can tell, in orde
r
to get any output at all you need to list every column in the GROUP BY. This
kind of defeats the purpose in this case.
Any pointers?
Maury"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:1AFFFE59-A8F4-4BD1-8626-C9757BE6597C@.microsoft.com...

> ORDER ID PART ID NAME QUANTITY PRICE NET
> 1000 1 widget 10 10 100
> 1000 2 gazeeza 5 5 25
> 125 < summary row
>
That is not the result of a query. It's a report. It usually makes more
sense to use tools like reporting services for this kind of thing.
To do it in SQL you won't need CUBE/ROLLUP. UNION is probably more
appropriate:
SELECT order_id, tot, part_id, name, quantity, price, net
FROM
(SELECT 0 AS tot, order_id, part_id, name, quantity, price, net
FROM tbl_orders
UNION ALL
SELECT 1, order_id, NULL, NULL, NULL, NULL, SUM(net)
FROM tbl_orders
GROUP BY order_id) AS T
ORDER BY order_id, tot, part_id ;
Apparently your Order table is very denormalized. I hope and expect that you
are aware of that.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Need summary rows in a query, but how?

I have ORDERS which contain a number of ITEMS. Both are stored as rows in a
single table, tblOrders.
I would like to make a query that returns a list of items, then a total for
the order as a whole. Something like...
ORDER ID PART ID NAME QUANTITY PRICE NET
1000 1 widget 10 10 100
1000 2 gazeeza 5 5 25
125 < summary row
It appears this is the idea behind CUBE or ROLLUP, but as is typical, the
documentation on this is useless. Does anyone have any examples of how to
actually use this and get the output the way you want?
It appears you have to use GROUP BY for this sort of thing, and this brings
up another question. In the examples above, I want the grouping for the
summary to work only on the ORDER ID. However, as far as I can tell, in order
to get any output at all you need to list every column in the GROUP BY. This
kind of defeats the purpose in this case.
Any pointers?
Maury"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:1AFFFE59-A8F4-4BD1-8626-C9757BE6597C@.microsoft.com...
> ORDER ID PART ID NAME QUANTITY PRICE NET
> 1000 1 widget 10 10 100
> 1000 2 gazeeza 5 5 25
> 125 < summary row
>
That is not the result of a query. It's a report. It usually makes more
sense to use tools like reporting services for this kind of thing.
To do it in SQL you won't need CUBE/ROLLUP. UNION is probably more
appropriate:
SELECT order_id, tot, part_id, name, quantity, price, net
FROM
(SELECT 0 AS tot, order_id, part_id, name, quantity, price, net
FROM tbl_orders
UNION ALL
SELECT 1, order_id, NULL, NULL, NULL, NULL, SUM(net)
FROM tbl_orders
GROUP BY order_id) AS T
ORDER BY order_id, tot, part_id ;
Apparently your Order table is very denormalized. I hope and expect that you
are aware of that.
Hope this helps.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Need report to NEVER cache

Let me say up front that rs:ClearSession=true didn't work :)

I have a report on RS that displays two blocks of data. The intent is to move items from one list to the other. To do so, there's a link on the detail lines which does a drill-through to another report. That report takes parameters (IDs for the record to be moved) and uses a stored procedure as it's datasource. The stored procedure takes in the parameter, does an INSERT INTO into the table that's driving which list items appear on, then returns the ID that was affected. On the report, there's just a text box stating that the item was added to the table and a "Back" text box. This Back item is a drill through back to the original report.

When we go back to the report (not really *back* since we're actually drilling through to the report anew), I want it to show the two lists with the one item moved from the one list to the other. Problem is, since the report was already run, it's showing the data from the cache. If I hit the refresh button, it updates and the item moves as it should.

I've read a number of posting on this and the only thing anyone said that should work was to use the rs:ClearSession=true option in the URL. So I generated a URL to the report, added this option. Tested that my URL worked and then changed the "Back" text box's navigation so that rather than a simple drill-through, it goes to a URL...the one with the ClearSession.

But it still doesn't work. I click the link on one of my lines, it goes to the drill through saying that it added the line to the table (I can even query the table in QA and see that it did), then I click the Back and I can see in the address box that the ClearSession arguement is there but the results still don't refresh.

I've also tried CTRL-F5 while in this state and the data doesn't refresh. The only thing that makes it re-run the stored procedure and pull in new data is the green refresh button.

Any ideas?

Thanks, Tim

Does the original report have a query parameter? If it does, the original query should be re-executed. You might need to bind it to a complext expression (not just =Parameters!XX.Value).|||

Brian,

The report has two data sets that are based on Stored Procedures. The sproc does have parameters but the only parameters being passed is a @.MODE that allows the sproc to run different blocks of code (let me know if you'd like to see the code of the sproc.

So there's nothing dynamic about what's being passed in, it's always the same. I tried adding a parameter called @.CURR_TIME to test what I thought you were saying. I set the dataset to pass "=Now()" to that parameter. Within the code of the sproc, the parameter is then ignored. In theory, everytime it's called, it would think it needs to send the sproc a new time, right? But still, when I drill through back to the parent report, it doesn't rerun. I hit the refresh and the data changes show up.

Help!!! :(

Tim

|||

Can anyone elaborate on what Brian was saying? I really need to get this report to always rerun the sproc behind it every time it refreshes through any means and Brian sounded like he knew how to do that.

Thanks,

Tim

|||

I'd still love to hear if anyone can elaborate on this. I didn't quite understand how to do what Brian was referring to.

Thanks,

Tim

|||

Hi Tim,

I had the same issue. To get around it, we created a time parameter, similar to what you described on your stored procedure, but on the report itself. Always passing the current time will make the report completely re-execute.

-Jessica

|||

Jessica,

Thanks so much for the response. I was so focused on getting the sproc to rerun, it didn't occur to me to try forcing the report to refresh in that way! I added a parameter to the report called ENSURE_REFRESH with a prompt "Ingore this prompt:", made it a datetime type and gave it a default of =DateAdd("s", 1, Now()). It worked perfectly!!!!

Everytime I drill back to the report, it's realizes it needs to refresh because the default changes and the underlying data changes are reflected!!!!

Thanks for getting me thinking in the right direction.

Tim Graffham

Need report to NEVER cache

Let me say up front that rs:ClearSession=true didn't work :)

I have a report on RS that displays two blocks of data. The intent is to move items from one list to the other. To do so, there's a link on the detail lines which does a drill-through to another report. That report takes parameters (IDs for the record to be moved) and uses a stored procedure as it's datasource. The stored procedure takes in the parameter, does an INSERT INTO into the table that's driving which list items appear on, then returns the ID that was affected. On the report, there's just a text box stating that the item was added to the table and a "Back" text box. This Back item is a drill through back to the original report.

When we go back to the report (not really *back* since we're actually drilling through to the report anew), I want it to show the two lists with the one item moved from the one list to the other. Problem is, since the report was already run, it's showing the data from the cache. If I hit the refresh button, it updates and the item moves as it should.

I've read a number of posting on this and the only thing anyone said that should work was to use the rs:ClearSession=true option in the URL. So I generated a URL to the report, added this option. Tested that my URL worked and then changed the "Back" text box's navigation so that rather than a simple drill-through, it goes to a URL...the one with the ClearSession.

But it still doesn't work. I click the link on one of my lines, it goes to the drill through saying that it added the line to the table (I can even query the table in QA and see that it did), then I click the Back and I can see in the address box that the ClearSession arguement is there but the results still don't refresh.

I've also tried CTRL-F5 while in this state and the data doesn't refresh. The only thing that makes it re-run the stored procedure and pull in new data is the green refresh button.

Any ideas?

Thanks, Tim

Does the original report have a query parameter? If it does, the original query should be re-executed. You might need to bind it to a complext expression (not just =Parameters!XX.Value).|||

Brian,

The report has two data sets that are based on Stored Procedures. The sproc does have parameters but the only parameters being passed is a @.MODE that allows the sproc to run different blocks of code (let me know if you'd like to see the code of the sproc.

So there's nothing dynamic about what's being passed in, it's always the same. I tried adding a parameter called @.CURR_TIME to test what I thought you were saying. I set the dataset to pass "=Now()" to that parameter. Within the code of the sproc, the parameter is then ignored. In theory, everytime it's called, it would think it needs to send the sproc a new time, right? But still, when I drill through back to the parent report, it doesn't rerun. I hit the refresh and the data changes show up.

Help!!! :(

Tim

|||

Can anyone elaborate on what Brian was saying? I really need to get this report to always rerun the sproc behind it every time it refreshes through any means and Brian sounded like he knew how to do that.

Thanks,

Tim

|||

I'd still love to hear if anyone can elaborate on this. I didn't quite understand how to do what Brian was referring to.

Thanks,

Tim

|||

Hi Tim,

I had the same issue. To get around it, we created a time parameter, similar to what you described on your stored procedure, but on the report itself. Always passing the current time will make the report completely re-execute.

-Jessica

|||

Jessica,

Thanks so much for the response. I was so focused on getting the sproc to rerun, it didn't occur to me to try forcing the report to refresh in that way! I added a parameter to the report called ENSURE_REFRESH with a prompt "Ingore this prompt:", made it a datetime type and gave it a default of =DateAdd("s", 1, Now()). It worked perfectly!!!!

Everytime I drill back to the report, it's realizes it needs to refresh because the default changes and the underlying data changes are reflected!!!!

Thanks for getting me thinking in the right direction.

Tim Graffham

Need report to NEVER cache

Let me say up front that rs:ClearSession=true didn't work :)

I have a report on RS that displays two blocks of data. The intent is to move items from one list to the other. To do so, there's a link on the detail lines which does a drill-through to another report. That report takes parameters (IDs for the record to be moved) and uses a stored procedure as it's datasource. The stored procedure takes in the parameter, does an INSERT INTO into the table that's driving which list items appear on, then returns the ID that was affected. On the report, there's just a text box stating that the item was added to the table and a "Back" text box. This Back item is a drill through back to the original report.

When we go back to the report (not really *back* since we're actually drilling through to the report anew), I want it to show the two lists with the one item moved from the one list to the other. Problem is, since the report was already run, it's showing the data from the cache. If I hit the refresh button, it updates and the item moves as it should.

I've read a number of posting on this and the only thing anyone said that should work was to use the rs:ClearSession=true option in the URL. So I generated a URL to the report, added this option. Tested that my URL worked and then changed the "Back" text box's navigation so that rather than a simple drill-through, it goes to a URL...the one with the ClearSession.

But it still doesn't work. I click the link on one of my lines, it goes to the drill through saying that it added the line to the table (I can even query the table in QA and see that it did), then I click the Back and I can see in the address box that the ClearSession arguement is there but the results still don't refresh.

I've also tried CTRL-F5 while in this state and the data doesn't refresh. The only thing that makes it re-run the stored procedure and pull in new data is the green refresh button.

Any ideas?

Thanks, Tim

Does the original report have a query parameter? If it does, the original query should be re-executed. You might need to bind it to a complext expression (not just =Parameters!XX.Value).|||

Brian,

The report has two data sets that are based on Stored Procedures. The sproc does have parameters but the only parameters being passed is a @.MODE that allows the sproc to run different blocks of code (let me know if you'd like to see the code of the sproc.

So there's nothing dynamic about what's being passed in, it's always the same. I tried adding a parameter called @.CURR_TIME to test what I thought you were saying. I set the dataset to pass "=Now()" to that parameter. Within the code of the sproc, the parameter is then ignored. In theory, everytime it's called, it would think it needs to send the sproc a new time, right? But still, when I drill through back to the parent report, it doesn't rerun. I hit the refresh and the data changes show up.

Help!!! :(

Tim

|||

Can anyone elaborate on what Brian was saying? I really need to get this report to always rerun the sproc behind it every time it refreshes through any means and Brian sounded like he knew how to do that.

Thanks,

Tim

|||

I'd still love to hear if anyone can elaborate on this. I didn't quite understand how to do what Brian was referring to.

Thanks,

Tim

|||

Hi Tim,

I had the same issue. To get around it, we created a time parameter, similar to what you described on your stored procedure, but on the report itself. Always passing the current time will make the report completely re-execute.

-Jessica

|||

Jessica,

Thanks so much for the response. I was so focused on getting the sproc to rerun, it didn't occur to me to try forcing the report to refresh in that way! I added a parameter to the report called ENSURE_REFRESH with a prompt "Ingore this prompt:", made it a datetime type and gave it a default of =DateAdd("s", 1, Now()). It worked perfectly!!!!

Everytime I drill back to the report, it's realizes it needs to refresh because the default changes and the underlying data changes are reflected!!!!

Thanks for getting me thinking in the right direction.

Tim Graffham

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