grouping select query

Hi,
I have data stored as in below sample :
--+--+--
--
DateBegin | DateEnd | Rate
--+--+--
--
2005-11-13 00:00:00 2005-11-14 00:00:00 63.0000
2005-11-14 00:00:00 2005-11-15 00:00:00 63.0000
2005-11-15 00:00:00 2005-11-16 00:00:00 45.0000
2005-11-16 00:00:00 2005-11-17 00:00:00 45.0000
2005-11-17 00:00:00 2005-11-18 00:00:00 45.0000
2005-11-18 00:00:00 2005-11-19 00:00:00 45.0000
2005-11-19 00:00:00 2005-11-20 00:00:00 45.0000
2005-11-20 00:00:00 2005-11-21 00:00:00 63.0000
2005-11-21 00:00:00 2005-11-22 00:00:00 63.0000
--+--+--
--
I have to group the select query in this way :
--+--+--
--
DateBegin | DateEnd | Rate
--+--+--
--
2005-11-13 00:00:00 2005-11-15 00:00:00 63.0000
2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
2005-11-20 00:00:00 2005-11-22 00:00:00 63.0000
--+--+--
--
When I run below grouped statement, I get follewed result:
SELECT MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd,
Rate FROM X GROUP BY Rate
--+--+--
--
DateBegin | DateEnd | Rate
--+--+--
--
2005-11-13 00:00:00 2005-11-22 00:00:00 63.0000
2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
--+--+--
--
How can I do a query like in 2nd sample from top?
best regards,
rustam bogubaevThis is a periodicity problem, not a SQL syntax problem.
You have to define how the period is to be divided first. In essence,
however you decide to calculate the period, the data would logically contain
the following information.
--+--+--
--
DateBegin | DateEnd | Rate |
Period
--+--+--
--
2005-11-13 00:00:00 2005-11-14 00:00:00 63.0000 1
2005-11-14 00:00:00 2005-11-15 00:00:00 63.0000 1
2005-11-15 00:00:00 2005-11-16 00:00:00 45.0000 2
2005-11-16 00:00:00 2005-11-17 00:00:00 45.0000 2
2005-11-17 00:00:00 2005-11-18 00:00:00 45.0000 2
2005-11-18 00:00:00 2005-11-19 00:00:00 45.0000 2
2005-11-19 00:00:00 2005-11-20 00:00:00 45.0000 2
2005-11-20 00:00:00 2005-11-21 00:00:00 63.0000 3
2005-11-21 00:00:00 2005-11-22 00:00:00 63.0000 3
--+--+--
--
With the periods defined, however that is done, your problem will be easy.
Perhaps something like the following would help find the boundarys of the
periods.
SELECT b.DateBegin
FROM MyTable a JOIN MyTable b
ON a.DateEnd = b.DateBegin
WHERE a.Rate != b.Rate
RLF
<rustam.bogubaev@.gmail.com> wrote in message
news:1131461007.812709.108200@.g49g2000cwa.googlegroups.com...
> Hi,
> I have data stored as in below sample :
> --+--+--
--
> DateBegin | DateEnd | Rate
> --+--+--
--
> 2005-11-13 00:00:00 2005-11-14 00:00:00 63.0000
> 2005-11-14 00:00:00 2005-11-15 00:00:00 63.0000
> 2005-11-15 00:00:00 2005-11-16 00:00:00 45.0000
> 2005-11-16 00:00:00 2005-11-17 00:00:00 45.0000
> 2005-11-17 00:00:00 2005-11-18 00:00:00 45.0000
> 2005-11-18 00:00:00 2005-11-19 00:00:00 45.0000
> 2005-11-19 00:00:00 2005-11-20 00:00:00 45.0000
> 2005-11-20 00:00:00 2005-11-21 00:00:00 63.0000
> 2005-11-21 00:00:00 2005-11-22 00:00:00 63.0000
> --+--+--
--
>
> I have to group the select query in this way :
> --+--+--
--
> DateBegin | DateEnd | Rate
> --+--+--
--
> 2005-11-13 00:00:00 2005-11-15 00:00:00 63.0000
> 2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
> 2005-11-20 00:00:00 2005-11-22 00:00:00 63.0000
> --+--+--
--
> When I run below grouped statement, I get follewed result:
> SELECT MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd,
> Rate FROM X GROUP BY Rate
> --+--+--
--
> DateBegin | DateEnd | Rate
> --+--+--
--
> 2005-11-13 00:00:00 2005-11-22 00:00:00 63.0000
> 2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
> --+--+--
--
> How can I do a query like in 2nd sample from top?
> best regards,
> rustam bogubaev
>|||On 8 Nov 2005 06:43:27 -0800, rustam.bogubaev@.gmail.com wrote:
(snip)
>I have to group the select query in this way :
>--+--+--
--
> DateBegin | DateEnd | Rate
>--+--+--
--
>2005-11-13 00:00:00 2005-11-15 00:00:00 63.0000
>2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
>2005-11-20 00:00:00 2005-11-22 00:00:00 63.0000
>--+--+--[/c
olor]
Hi rustam,
If my assumptions about your table and the reasons for your expected
results are correct, then try:
SELECT a.DateBegin, MAX(b.DateEnd), a.Rate
FROM X AS a
INNER JOIN X as b
ON b.Rate = a.Rate
AND b.DateBegin >= a.DateStart
WHERE NOT EXISTS
(SELECT *
FROM X AS c
WHERE c.DateBegin = DATEADD(day, -1, a.DateBegin)
AND c.Rate = a.Rate)
AND NOT EXISTS
(SELECT *
FROM X AS d
WHERE d.DateBegin > a.DateEnd
AND d.DateEnd < b.DateBegin
AND d.Rate <> a.Rate)
GROUP BY a.DateBegin, a.Rate
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

grouping select query

Hi,

I have data stored as in below sample :

----------+----------+-----
DateBegin | DateEnd | Rate
----------+----------+-----
2005-11-13 00:00:002005-11-14 00:00:0063.0000
2005-11-14 00:00:002005-11-15 00:00:0063.0000
2005-11-15 00:00:002005-11-16 00:00:0045.0000
2005-11-16 00:00:002005-11-17 00:00:0045.0000
2005-11-17 00:00:002005-11-18 00:00:0045.0000
2005-11-18 00:00:002005-11-19 00:00:0045.0000
2005-11-19 00:00:002005-11-20 00:00:0045.0000
2005-11-20 00:00:002005-11-21 00:00:0063.0000
2005-11-21 00:00:002005-11-22 00:00:0063.0000
----------+----------+-----

I have to group the select query in this way :

----------+----------+-----
DateBegin | DateEnd | Rate
----------+----------+-----
2005-11-13 00:00:002005-11-15 00:00:0063.0000
2005-11-15 00:00:002005-11-20 00:00:0045.0000
2005-11-20 00:00:002005-11-22 00:00:0063.0000
----------+----------+-----

When I run below grouped statement, I get follewed result:

SELECT MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd,
Rate FROM X GROUP BY Rate

----------+----------+-----
DateBegin | DateEnd | Rate
----------+----------+-----
2005-11-13 00:00:002005-11-22 00:00:0063.0000
2005-11-15 00:00:002005-11-20 00:00:0045.0000
----------+----------+-----

How can I do a query like in 2nd sample from top?

best regards,
rustam bogubaevPYCTAM wrote:
> Hi,
> I have data stored as in below sample :
> ----------+----------+--
---
> DateBegin | DateEnd | Rate
> ----------+----------+--
---
> 2005-11-13 00:00:00 2005-11-14 00:00:00 63.0000
> 2005-11-14 00:00:00 2005-11-15 00:00:00 63.0000
> 2005-11-15 00:00:00 2005-11-16 00:00:00 45.0000
> 2005-11-16 00:00:00 2005-11-17 00:00:00 45.0000
> 2005-11-17 00:00:00 2005-11-18 00:00:00 45.0000
> 2005-11-18 00:00:00 2005-11-19 00:00:00 45.0000
> 2005-11-19 00:00:00 2005-11-20 00:00:00 45.0000
> 2005-11-20 00:00:00 2005-11-21 00:00:00 63.0000
> 2005-11-21 00:00:00 2005-11-22 00:00:00 63.0000
> ----------+----------+--
---
>
> I have to group the select query in this way :
> ----------+----------+--
---
> DateBegin | DateEnd | Rate
> ----------+----------+--
---
> 2005-11-13 00:00:00 2005-11-15 00:00:00 63.0000
> 2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
> 2005-11-20 00:00:00 2005-11-22 00:00:00 63.0000
> ----------+----------+--
---
> When I run below grouped statement, I get follewed result:
> SELECT MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd,
> Rate FROM X GROUP BY Rate
> ----------+----------+--
---
> DateBegin | DateEnd | Rate
> ----------+----------+--
---
> 2005-11-13 00:00:00 2005-11-22 00:00:00 63.0000
> 2005-11-15 00:00:00 2005-11-20 00:00:00 45.0000
> ----------+----------+--
---
> How can I do a query like in 2nd sample from top?

Care to explain by what you want to group? I cannot recognize it from
your sample output.

robert|||On 8 Nov 2005 06:42:33 -0800, PYCTAM wrote:

(snip)

Hi rustam,

You posted an exact identical copy of this question in the group
microsoft.public.sqlserver.programming, and I posted a reply there.

Please do not post the same question independently to multiple groups.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Grouping on time

Hello,
I have a table with 2 columns, time and amount. I want to be able to group
by an interval and sum the amount see below of a sample of the data.
Time Amount
2005-02-16 05:41:00.000 100
2005-02-16 05:41:01.000 100
2005-02-16 05:41:02.000 100
2005-02-16 05:41:03.000 100
2005-02-16 05:41:04.000 100
2005-02-16 05:41:05.000 100
2005-02-16 05:41:06.000 100
2005-02-16 05:41:07.000 100
2005-02-16 05:41:08.000 100
2005-02-16 05:41:09.000 100
2005-02-16 05:41:10.000 100
2005-02-16 05:41:11.000 100
2005-02-16 05:41:12.000 100
2005-02-16 05:41:13.000 100
2005-02-16 05:41:14.000 100
so the result of the above with an interval of 5 seconds would be
Time Amount
2005-02-16 05:41:04.000 500
2005-02-16 05:41:09.000 500
2005-02-16 05:41:14.000 500
any ideas?
ThanksTry,
use northwind
go
create table t (
[Time] datetime,
Amount int
)
go
insert into t values('2005-02-16 05:41:00.000', 100)
insert into t values('2005-02-16 05:41:01.000', 100)
insert into t values('2005-02-16 05:41:02.000', 100)
insert into t values('2005-02-16 05:41:03.000', 100)
insert into t values('2005-02-16 05:41:04.000', 100)
insert into t values('2005-02-16 05:41:05.000', 100)
insert into t values('2005-02-16 05:41:06.000', 100)
insert into t values('2005-02-16 05:41:07.000', 100)
insert into t values('2005-02-16 05:41:08.000', 100)
insert into t values('2005-02-16 05:41:09.000', 100)
insert into t values('2005-02-16 05:41:10.000', 100)
insert into t values('2005-02-16 05:41:11.000', 100)
insert into t values('2005-02-16 05:41:12.000', 100)
insert into t values('2005-02-16 05:41:13.000', 100)
insert into t values('2005-02-16 05:41:14.000', 100)
go
select
max([time]) as max_time,
sum(amount) as sum_amount
from
t
group by
datediff(second, convert(char(8), [time], 112), [time]) / 5
go
drop table t
go
AMB
"Fab" wrote:

> Hello,
> I have a table with 2 columns, time and amount. I want to be able to group
> by an interval and sum the amount see below of a sample of the data.
> Time Amount
> 2005-02-16 05:41:00.000 100
> 2005-02-16 05:41:01.000 100
> 2005-02-16 05:41:02.000 100
> 2005-02-16 05:41:03.000 100
> 2005-02-16 05:41:04.000 100
> 2005-02-16 05:41:05.000 100
> 2005-02-16 05:41:06.000 100
> 2005-02-16 05:41:07.000 100
> 2005-02-16 05:41:08.000 100
> 2005-02-16 05:41:09.000 100
> 2005-02-16 05:41:10.000 100
> 2005-02-16 05:41:11.000 100
> 2005-02-16 05:41:12.000 100
> 2005-02-16 05:41:13.000 100
> 2005-02-16 05:41:14.000 100
> so the result of the above with an interval of 5 seconds would be
> Time Amount
> 2005-02-16 05:41:04.000 500
> 2005-02-16 05:41:09.000 500
> 2005-02-16 05:41:14.000 500
>
> any ideas?
> Thanks
>
>|||This was responded yesterday ( assumption is that there exists one row for
every monotonically increasing second ):
[url]http://groups.google.ca/groups?selm=%238%23HOtMMFHA.3832%40TK2MSFTNGP12.phx.gbl[/u
rl]
Anith|||use something like that
select dateadd(ss,-datepart(ss,time)%5,time),sum(amount) from @.t group by
dateadd(ss,-datepart(ss,time)%5,time)
"Fab" wrote:

> Hello,
> I have a table with 2 columns, time and amount. I want to be able to group
> by an interval and sum the amount see below of a sample of the data.
> Time Amount
> 2005-02-16 05:41:00.000 100
> 2005-02-16 05:41:01.000 100
> 2005-02-16 05:41:02.000 100
> 2005-02-16 05:41:03.000 100
> 2005-02-16 05:41:04.000 100
> 2005-02-16 05:41:05.000 100
> 2005-02-16 05:41:06.000 100
> 2005-02-16 05:41:07.000 100
> 2005-02-16 05:41:08.000 100
> 2005-02-16 05:41:09.000 100
> 2005-02-16 05:41:10.000 100
> 2005-02-16 05:41:11.000 100
> 2005-02-16 05:41:12.000 100
> 2005-02-16 05:41:13.000 100
> 2005-02-16 05:41:14.000 100
> so the result of the above with an interval of 5 seconds would be
> Time Amount
> 2005-02-16 05:41:04.000 500
> 2005-02-16 05:41:09.000 500
> 2005-02-16 05:41:14.000 500
>
> any ideas?
> Thanks
>
>|||CREATE TABLE ReportPeriods
(period_id CHAR(10) NOT NULL,
start_time DATETIME NOT NULL,
end_time DATETIME NOT NULL,
CHECK (start_time < end_time),
PRIMARY KEY (start_time, end_time));
Load your times into the table then:
SELECT period_id, COUNT(*)
FROM ReportPeriods AS P1, Foobar AS F1
WHERE F1.event_time BETWEEN start_time AND end_time;|||sorry i made a mistake the script should be
select max(time),sum(amount) from @.t group by
dateadd(ss,-datepart(ss,time)%5,time)
the problem with the response of alejandro mesa is that if you have the same
time in different days the two rows will be grouped together
"Fab" wrote:

> Hello,
> I have a table with 2 columns, time and amount. I want to be able to group
> by an interval and sum the amount see below of a sample of the data.
> Time Amount
> 2005-02-16 05:41:00.000 100
> 2005-02-16 05:41:01.000 100
> 2005-02-16 05:41:02.000 100
> 2005-02-16 05:41:03.000 100
> 2005-02-16 05:41:04.000 100
> 2005-02-16 05:41:05.000 100
> 2005-02-16 05:41:06.000 100
> 2005-02-16 05:41:07.000 100
> 2005-02-16 05:41:08.000 100
> 2005-02-16 05:41:09.000 100
> 2005-02-16 05:41:10.000 100
> 2005-02-16 05:41:11.000 100
> 2005-02-16 05:41:12.000 100
> 2005-02-16 05:41:13.000 100
> 2005-02-16 05:41:14.000 100
> so the result of the above with an interval of 5 seconds would be
> Time Amount
> 2005-02-16 05:41:04.000 500
> 2005-02-16 05:41:09.000 500
> 2005-02-16 05:41:14.000 500
>
> any ideas?
> Thanks
>
>|||can you explan this part please?
-datepart(ss,time)%5
"sergiu" <sergiu@.discussions.microsoft.com> wrote in message
news:C3A9AA65-1277-4AEF-A517-60E4E03CED9B@.microsoft.com...
> sorry i made a mistake the script should be
> select max(time),sum(amount) from @.t group by
> dateadd(ss,-datepart(ss,time)%5,time)
> the problem with the response of alejandro mesa is that if you have the
> same
> time in different days the two rows will be grouped together
>
> "Fab" wrote:
>|||your assumption is wrong is my skip a second or two...
any ideas?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23uL11yVMFHA.568@.TK2MSFTNGP09.phx.gbl...
> This was responded yesterday ( assumption is that there exists one row for
> every monotonically increasing second ):
> [url]http://groups.google.ca/groups?selm=%238%23HOtMMFHA.3832%40TK2MSFTNGP12.phx.gbl[
/url]
> --
> Anith
>|||On Fri, 25 Mar 2005 14:18:16 -0500, Fab wrote:

>your assumption is wrong is my skip a second or two...
>any ideas?
Hi Fab,
So why didn't you indicate that the assumption was wrong in the original
thread? Half an hour ago, I saw the original thread with only Anith's
answer; I took the time to try a solution, write a message and send it.
And now, I find that you reposted the question in a new thread and
already got some replies.
If you had posted a follow-up to your original question instead of
starting a new thread, then I'd have seen the answers and moved on the
the next question, instead of wasting my time and cluttering the group
with yet another answer that isn't really any different from Alejandro's
suggestion.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||so now that you know your assumption was wrong are you still willing to help
me with my issue?
I need to group based on 5 seconds intervals...the result of the table will
roll up based on time not on the values in the table...so the results
should start at second 00 and end at second 04...anything that falls in
that 1st group will be rolled up...and so on for each interal all the way up
to 60.
let me know if you have any questions b4 you provide a solution.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:g45941liog7iufggqi8eqvp8mngammggir@.
4ax.com...
> On Fri, 25 Mar 2005 14:18:16 -0500, Fab wrote:
>
>
> Hi Fab,
> So why didn't you indicate that the assumption was wrong in the original
> thread? Half an hour ago, I saw the original thread with only Anith's
> answer; I took the time to try a solution, write a message and send it.
> And now, I find that you reposted the question in a new thread and
> already got some replies.
> If you had posted a follow-up to your original question instead of
> starting a new thread, then I'd have seen the answers and moved on the
> the next question, instead of wasting my time and cluttering the group
> with yet another answer that isn't really any different from Alejandro's
> suggestion.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

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 and counting

Hi,
I will try to explain:
I want the proper grouping and display counts. This query
works fine, see below some of the returned results.
SELECT TempD.state, TempD.City, TempD.Zip, count(TempE.ID)AS total
FROM
TempE INNER JOIN TempD
ON TempE.Numb = TempD.Numb
WHERE(TempE.sp in('bm','pm') or
TempE.sp2 in('bm','pm'))
GROUP BY TempD.state,TempD.City,TempD.Zip
ORDER BY TempD.state, TempD.City
Some of the results returned:
State City Zip total
Alabama ALBASTER 35007 2
Alabama BIRMINGHAM 35292 1
Arizona YUMA 85364 1
California PALMDALE 93550 1
Connecticut NEW LONDON 06320 2
Connecticut WOODBRIDGE 06525 1
Delaware CLAYMONT 19703 1
Delaware WILMINGTON 19801 3
North Carolina ASHEVILLE 28815 10
North Carolina ASHEVILLE 28816 3
South Carolina CHAPIN 29036 1
South Carolina CHARLESTON 29401 116
South Carolina CHARLESTON 29402 8
I want two more columns after total for "bm" and "pm" and with
the totals broke down like the following
State City Zip total
bm pm
Alabama ALBASTER 35007 2 1
1
Alabama BIRMINGHAM 35292 1 1 0
Arizona YUMA 85364 1 0
1
California PALMDALE 93550 1 1
0
Connecticut NEW LONDON 06320 2 2 0
Connecticut WOODBRIDGE 06525 1 0 1
Delaware CLAYMONT 19703 1 1 0
Delaware WILMINGTON 19801 3 3 0
North Carolina ASHEVILLE 28815 10 7 3
North Carolina ASHEVILLE 28816 3 0 3
South Carolina CHAPIN 29036 1 1
0
South Carolina CHARLESTON 29401 116 86 30
South Carolina CHARLESTON 29402 8 2 6
hope I was clear, Thanks
gvTry using a "case" expression.
SELECT
TempD.state,
TempD.City,
TempD.Zip,
count(TempE.ID)AS total,
sum(case when TempE.sp = 'bm' or TempE.sp2 = 'bm' then 1 else 0 end) as bm,
sum(case when TempE.sp = 'pm' or TempE.sp2 = 'pm' then 1 else 0 end) as pm
from
..
AMB
"gv" wrote:

> Hi,
> I will try to explain:
> I want the proper grouping and display counts. This query
> works fine, see below some of the returned results.
> SELECT TempD.state, TempD.City, TempD.Zip, count(TempE.ID)AS total
> FROM
> TempE INNER JOIN TempD
> ON TempE.Numb = TempD.Numb
> WHERE(TempE.sp in('bm','pm') or
> TempE.sp2 in('bm','pm'))
> GROUP BY TempD.state,TempD.City,TempD.Zip
> ORDER BY TempD.state, TempD.City
> Some of the results returned:
> State City Zip total
> Alabama ALBASTER 35007 2
> Alabama BIRMINGHAM 35292 1
> Arizona YUMA 85364 1
> California PALMDALE 93550 1
> Connecticut NEW LONDON 06320 2
> Connecticut WOODBRIDGE 06525 1
> Delaware CLAYMONT 19703 1
> Delaware WILMINGTON 19801 3
> North Carolina ASHEVILLE 28815 10
> North Carolina ASHEVILLE 28816 3
> South Carolina CHAPIN 29036 1
> South Carolina CHARLESTON 29401 116
> South Carolina CHARLESTON 29402 8
> I want two more columns after total for "bm" and "pm" and with
> the totals broke down like the following
> State City Zip total
> bm pm
> Alabama ALBASTER 35007 2 1
> 1
> Alabama BIRMINGHAM 35292 1 1 0
> Arizona YUMA 85364 1
0
> 1
> California PALMDALE 93550 1 1
> 0
> Connecticut NEW LONDON 06320 2 2 0
> Connecticut WOODBRIDGE 06525 1 0 1
> Delaware CLAYMONT 19703 1 1
0
> Delaware WILMINGTON 19801 3 3 0
> North Carolina ASHEVILLE 28815 10 7 3
> North Carolina ASHEVILLE 28816 3 0
3
> South Carolina CHAPIN 29036 1 1
> 0
> South Carolina CHARLESTON 29401 116 86 30
> South Carolina CHARLESTON 29402 8 2 6
> hope I was clear, Thanks
> gv
>
>
>|||Thanks!!
gv
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:1996E862-0C8C-4C22-A154-57D5B728A650@.microsoft.com...
> Try using a "case" expression.
> SELECT
> TempD.state,
> TempD.City,
> TempD.Zip,
> count(TempE.ID)AS total,
> sum(case when TempE.sp = 'bm' or TempE.sp2 = 'bm' then 1 else 0 end) as
> bm,
> sum(case when TempE.sp = 'pm' or TempE.sp2 = 'pm' then 1 else 0 end) as
> pm
> from
> ...
>
> AMB
> "gv" wrote:
>

Group SQL Select Statement

Hi,

I am bloody amateure and I was wondering if someone could help me edit
the statement below so it groups the field SCDMASTER.SCTY_CLASS_CODE
and creates one field called "total balance" equalling
CUSTODY_BALANCE.OPENING_BALANCE + CUSTODY_BALANCE.DEPOSIT_AMOUNT -
CUSTODY_BALANCE.WITHDRAWAL_AMOUNT

SELECT
CUSTODY_BALANCE.APPLICATIONCYCLEDATE,
CUSTODY_BALANCE.ASSET_ID,
SCDMASTER.SCTY_CLASS_CODE,
CUSTODY_BALANCE.OPENING_BALANCE,
CUSTODY_BALANCE.DEPOSIT_AMOUNT,
CUSTODY_BALANCE.WITHDRAWAL_AMOUNT
FROM
FPMCAPSHIST.CUSTODY_BALANCE CUSTODY_BALANCE,
CAPSREPORT.SCDMASTER SCDMASTER
WHERE
CUSTODY_BALANCE.ASSET_ID = SCDMASTER.CUSIP_ID AND
((CUSTODY_BALANCE.APPLICATIONCYCLEDATE={ts '2007-03-21 00:00:00'}) AND
(SCDMASTER.SCTY_CLASS_CODE Not In
('BILL','NOTE','BOND','CD','BD','CB','TINT','TPRN' ,'CA','YD')))

Thanks,

AndreasSomething like this=

Select
SCDMASTER.SCTY_CLASS_CODE,
sum(CUSTODY_BALANCE.OPENING_BALANCE +
CUSTODY_BALANCE.DEPOSIT_AMOUNT -CUSTODY_BALANCE.WITHDRAWAL_AMOUNT)

from
PMCAPSHIST.CUSTODY_BALANCE CUSTODY_BALANCE,
CAPSREPORT.SCDMASTER SCDMASTER
where
CUSTODY_BALANCE.ASSET_ID = SCDMASTER.CUSIP_ID AND
((CUSTODY_BALANCE.APPLICATIONCYCLEDATE={ts '2007-03-21 00:00:00'}) AND
(SCDMASTER.SCTY_CLASS_CODE Not In
('BILL','NOTE','BOND','CD','BD','CB','TINT','TPRN' ,'CA','YD')))
group by
SCDMASTER.SCTY_CLASS_CODE

MC

"Andreas" <andreas.strzodka@.ny.frb.orgwrote in message
news:1174673610.080198.22590@.y66g2000hsf.googlegro ups.com...

Quote:

Originally Posted by

Hi,
>
I am bloody amateure and I was wondering if someone could help me edit
the statement below so it groups the field SCDMASTER.SCTY_CLASS_CODE
and creates one field called "total balance" equalling
CUSTODY_BALANCE.OPENING_BALANCE + CUSTODY_BALANCE.DEPOSIT_AMOUNT -
CUSTODY_BALANCE.WITHDRAWAL_AMOUNT
>
SELECT
CUSTODY_BALANCE.APPLICATIONCYCLEDATE,
CUSTODY_BALANCE.ASSET_ID,
SCDMASTER.SCTY_CLASS_CODE,
CUSTODY_BALANCE.OPENING_BALANCE,
CUSTODY_BALANCE.DEPOSIT_AMOUNT,
CUSTODY_BALANCE.WITHDRAWAL_AMOUNT
FROM
FPMCAPSHIST.CUSTODY_BALANCE CUSTODY_BALANCE,
CAPSREPORT.SCDMASTER SCDMASTER
WHERE
CUSTODY_BALANCE.ASSET_ID = SCDMASTER.CUSIP_ID AND
((CUSTODY_BALANCE.APPLICATIONCYCLEDATE={ts '2007-03-21 00:00:00'}) AND
(SCDMASTER.SCTY_CLASS_CODE Not In
('BILL','NOTE','BOND','CD','BD','CB','TINT','TPRN' ,'CA','YD')))
>
Thanks,
>
Andreas
>

|||MS Query tells me ORA-00979: not a GROUP BY expression. Are there only
certain fields that I can group by?

On Mar 23, 2:22 pm, "MC" <marko.NOSPAMc...@.gmail.comwrote:

Quote:

Originally Posted by

Something like this=
>
Select
SCDMASTER.SCTY_CLASS_CODE,
sum(CUSTODY_BALANCE.OPENING_BALANCE +
CUSTODY_BALANCE.DEPOSIT_AMOUNT -CUSTODY_BALANCE.WITHDRAWAL_AMOUNT)
>
from
PMCAPSHIST.CUSTODY_BALANCE CUSTODY_BALANCE,
CAPSREPORT.SCDMASTER SCDMASTER
where
CUSTODY_BALANCE.ASSET_ID = SCDMASTER.CUSIP_ID AND
((CUSTODY_BALANCE.APPLICATIONCYCLEDATE={ts '2007-03-21 00:00:00'}) AND
(SCDMASTER.SCTY_CLASS_CODE Not In
('BILL','NOTE','BOND','CD','BD','CB','TINT','TPRN' ,'CA','YD')))
group by
SCDMASTER.SCTY_CLASS_CODE
>
MC
>
"Andreas" <andreas.strzo...@.ny.frb.orgwrote in message
>
news:1174673610.080198.22590@.y66g2000hsf.googlegro ups.com...
>
>
>

Quote:

Originally Posted by

Hi,

>

Quote:

Originally Posted by

I am bloody amateure and I was wondering if someone could help me edit
the statement below so it groups the field SCDMASTER.SCTY_CLASS_CODE
and creates one field called "total balance" equalling
CUSTODY_BALANCE.OPENING_BALANCE + CUSTODY_BALANCE.DEPOSIT_AMOUNT -
CUSTODY_BALANCE.WITHDRAWAL_AMOUNT

>

Quote:

Originally Posted by

SELECT
CUSTODY_BALANCE.APPLICATIONCYCLEDATE,
CUSTODY_BALANCE.ASSET_ID,
SCDMASTER.SCTY_CLASS_CODE,
CUSTODY_BALANCE.OPENING_BALANCE,
CUSTODY_BALANCE.DEPOSIT_AMOUNT,
CUSTODY_BALANCE.WITHDRAWAL_AMOUNT
FROM
FPMCAPSHIST.CUSTODY_BALANCE CUSTODY_BALANCE,
CAPSREPORT.SCDMASTER SCDMASTER
WHERE
CUSTODY_BALANCE.ASSET_ID = SCDMASTER.CUSIP_ID AND
((CUSTODY_BALANCE.APPLICATIONCYCLEDATE={ts '2007-03-21 00:00:00'}) AND
(SCDMASTER.SCTY_CLASS_CODE Not In
('BILL','NOTE','BOND','CD','BD','CB','TINT','TPRN' ,'CA','YD')))

>

Quote:

Originally Posted by

Thanks,

>

Quote:

Originally Posted by

Andreas- Hide quoted text -

>
- Show quoted text -

|||Andreas wrote:

Quote:

Originally Posted by

MS Query tells me ORA-00979: not a GROUP BY expression. Are there only
certain fields that I can group by?

Did you add any fields to the SELECT line? If so, then you need to
also add them to the GROUP BY line.

MC's query looks correct to me, though I would personally link the
tables using JOIN instead of WHERE, and assign a field name to the
computed total:

select
SCDMASTER.SCTY_CLASS_CODE,
sum(CUSTODY_BALANCE.OPENING_BALANCE
+ CUSTODY_BALANCE.DEPOSIT_AMOUNT
- CUSTODY_BALANCE.WITHDRAWAL_AMOUNT) as TotalBalance
from PMCAPSHIST.CUSTODY_BALANCE CUSTODY_BALANCE
join CAPSREPORT.SCDMASTER SCDMASTER
on CUSTODY_BALANCE.ASSET_ID = SCDMASTER.CUSIP_ID
where CUSTODY_BALANCE.APPLICATIONCYCLEDATE = {ts '2007-03-21 00:00:00'}
and SCDMASTER.SCTY_CLASS_CODE not in (
'BILL','NOTE','BOND','CD','BD','CB','TINT','TPRN', 'CA','YD'
)
group by SCDMASTER.SCTY_CLASS_CODE

Group Min Record

Hello Guyz,

A small problem here, I have the below table and I need to group and display the record that has the minimum value in the table (this table is derived from a query that permutates some records to give me this result).

F1 F2 F3
QQQ C 2
QQQ B 1
QQQ A 3

expected result:
QQQ B 1

my result:
when I group by F1, First(F2) and MIN(F3):
QQQ C 1

when I group by F1, MIN(F2) and MIN(F3):
QQQ A 1

when I group by F1, F2 and MIN(F3):
QQQ C 2
QQQ B 1
QQQ A 3

Any help would be very much appreciated..

CyherusYou don't GROUP BY the aggregate for one thing. What you need is something like this in this particular example:

SELECT
t1.F1,
t1.F2,
t1.F3
FROM
table t1
INNER JOIN (
SELECT MIN(F3) AS F3 FROM table) t2 ON t1.F3 = t2.F3

This is assuming you're using SQL Server, which you probably aren't.

Group Header Question

I have a report that list information for each city by state as shown below.
GA
xxxxx xxxxx xxxxx xxxx
xxxxx xxxxx xxxxx xxxx
When the page breaks and it is still on the same group, is there away to
make the group head look like this:
GA (cont'd)
xxxxx xxxxx xxxxx xxxx
xxxxx xxxxx xxxxx xxxx
Thanks in advanceThe table group header has a property for "RepeatOnNewPage". Set it to true,
and the group header will repeat.
However, you can't add the "(cont'd)" string in that case, because all
repeated group headers will be identical to the initial one.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"OriginalStealth" <OriginalStealth@.discussions.microsoft.com> wrote in
message news:67476532-6565-4426-9151-EE5107867ABD@.microsoft.com...
>I have a report that list information for each city by state as shown
>below.
> GA
> xxxxx xxxxx xxxxx xxxx
> xxxxx xxxxx xxxxx xxxx
> When the page breaks and it is still on the same group, is there away to
> make the group head look like this:
> GA (cont'd)
> xxxxx xxxxx xxxxx xxxx
> xxxxx xxxxx xxxxx xxxx
> Thanks in advance|||why can't this be done with a list as well?
"Robert Bruckner [MSFT]" wrote:
> The table group header has a property for "RepeatOnNewPage". Set it to true,
> and the group header will repeat.
> However, you can't add the "(cont'd)" string in that case, because all
> repeated group headers will be identical to the initial one.
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "OriginalStealth" <OriginalStealth@.discussions.microsoft.com> wrote in
> message news:67476532-6565-4426-9151-EE5107867ABD@.microsoft.com...
> >I have a report that list information for each city by state as shown
> >below.
> >
> > GA
> > xxxxx xxxxx xxxxx xxxx
> > xxxxx xxxxx xxxxx xxxx
> >
> > When the page breaks and it is still on the same group, is there away to
> > make the group head look like this:
> >
> > GA (cont'd)
> > xxxxx xxxxx xxxxx xxxx
> > xxxxx xxxxx xxxxx xxxx
> >
> > Thanks in advance
>
>

Group header printed unnecessarily on page because no rows below

I am using a table with grouping - but found that the group header was
printed at the bottom of the page (when exported to PDF) with no rows below
it. The following page printed the group header again with the rows.
Is there a way to stop that behaviour?
Example...
Page1.
Group Header
Row
Row
Group Footer
Group Header <<< this printed unnecessarily
Page 2
Group Header
Row
Group Footer
--
McGeeky
http://mcgeeky.blogspot.comStill no workaround for this? I hate, hate , hate telling my users they just
have to deal with it.
"McGeeky" wrote:
> I am using a table with grouping - but found that the group header was
> printed at the bottom of the page (when exported to PDF) with no rows below
> it. The following page printed the group header again with the rows.
> Is there a way to stop that behaviour?
> Example...
> Page1.
> Group Header
> Row
> Row
> Group Footer
> Group Header <<< this printed unnecessarily
> Page 2
> Group Header
> Row
> Group Footer
> --
> McGeeky
> http://mcgeeky.blogspot.com
>
>

GROUP BY syntax

I'm trying to group rows in ItemKey to produce a sum total of prices. Then
update ItemKeyprice. The select statement works, but I get the below error.
Where is my syntax wrong?
thanks
UPDATE ItemKeyPrice
SET RegPrice =
(SELECT ItemKey.KeyNumber, SUM(ItemKey.Price) AS PriceInd
FROM ItemKey INNER JOIN ItemKeyPrice ON ItemKey.KeyNumber =
ItemKeyPrice.KeyNumber
GROUP BY ItemKey.KeyNumber)
error: Only one expression can be specified in the select list when the
subquery is not introduced with EXISTS.shank
(untested)
UPDATE ItemKeyPrice
SET RegPrice =
(SELECT SUM(ItemKey.Price) AS PriceInd
FROM ItemKey INNER JOIN ItemKeyPrice ON ItemKey.KeyNumber =
ItemKeyPrice.KeyNumber
GROUP BY ItemKey.KeyNumber)
Note: Without seeing your table stucture + samle data I could only guess
,it is possible yo9u get an error like
"Subquery returned more than 1 value."
"shank" <shank@.tampabay.rr.com> wrote in message
news:eTrms$l$FHA.1288@.TK2MSFTNGP09.phx.gbl...
> I'm trying to group rows in ItemKey to produce a sum total of prices. Then
> update ItemKeyprice. The select statement works, but I get the below
> error. Where is my syntax wrong?
> thanks
> UPDATE ItemKeyPrice
> SET RegPrice =
> (SELECT ItemKey.KeyNumber, SUM(ItemKey.Price) AS PriceInd
> FROM ItemKey INNER JOIN ItemKeyPrice ON ItemKey.KeyNumber =
> ItemKeyPrice.KeyNumber
> GROUP BY ItemKey.KeyNumber)
> error: Only one expression can be specified in the select list when the
> subquery is not introduced with EXISTS.
>|||shank (shank@.tampabay.rr.com) writes:
> I'm trying to group rows in ItemKey to produce a sum total of prices.
> Then update ItemKeyprice. The select statement works, but I get the
> below error. Where is my syntax wrong?
> thanks
> UPDATE ItemKeyPrice
> SET RegPrice =
> (SELECT ItemKey.KeyNumber, SUM(ItemKey.Price) AS PriceInd
> FROM ItemKey INNER JOIN ItemKeyPrice ON ItemKey.KeyNumber =
> ItemKeyPrice.KeyNumber
> GROUP BY ItemKey.KeyNumber)
> error: Only one expression can be specified in the select list when the
> subquery is not introduced with EXISTS.
The immediate error is the inclusion of ItemKey.KeyNumber in the Select
list. You are assigning RegPrice value, but you are sending it two
values.
However, when you fix that error, you will get another error saying that
subquery returned more than one value.
Here are two ways of writing what I think you want to achieve:
UPDATE ItemKeyPrice
SET RegPrice = (SELECT SUM(ItemKey.Price)
FROM ItemKey
WHERE ItemKey.KeyNumber = ItemKeyPrice.KeyNumber)
UPDATE ItemKeyPrice
SET RegPrice = ik.totprice
FROM ItemKeyPrice ikp
JOIN (SELECT KeyNumber, totprice = SUM(ItemKey.Price)
FROM ItemKey
GROUP BY KeyNumber) AS ik ON ik.KeyNumber = ikp.KeyNumber
The first uses a correlated subquery, and this syntax is by the ANSI
standard and should run on any DBMS. The second uses a FROM clause in
the UPDATE statement. This syntax is particular to MS SQL Server and
Sybase, and thus less portable. This query also includes a derived
table (which is part of ANSI SQL) to compute the sums per key.
While the first syntax is portable, my preference is very strongly
for the second, as it is easier to write and understand, and usually
also gives better performance. Furthermore, assume that you would have
more columns to set, like an average price, min price etc. In this case,
you would have to have several correlated subqueries, but the FROM
solution is very extensible in this regard.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Group by statement problem

I am using the T-SQL code below to pull patient information. The code returns 86 rows, however, there are only 9 distinct account numbers. Why is the group by statement not grouping these together to only display the 9 distinct accounts and associated data?

select
srm.episodes.episode_type as Visit_Type,
srm.episodes.account_number as Account_Number,
srm.episodes.medrec_no as MRN,
dbo.PtMstr.PatientFullName,
left(srm.episodes.admission_date,11) as Admit_Date,
left(srm.episodes.episode_date,11) as Disch_Date,
dbo.PtMstr.Cases as Cases,
dbo.PtMstr.TotCharges,
srm.cdmab_base_info.abst_cmp_status as Abtract_Comp_Status,
srm.cdmab_base_info.adm_dx_adt as Admitting_Dx
,srm.event_types.event_type_code
from srm.cdmab_base_info inner join
srm.episodes on srm.episodes.episode_key = srm.cdmab_base_info.episode_key
inner join srm.event_history on srm.event_history.item_key = srm.episodes.episode_key
inner join srm.event_types on srm.event_types.event_type_key = srm.event_history.event_type_key
inner join dbo.PtMstr on dbo.PtMstr.AccountNumber = srm.episodes.account_number
where srm.cdmab_base_info.abst_cmp_status <> 'Y'
and srm.episodes.episode_date is not null
and srm.event_types.event_type_code <> 'ACOD'
AND srm.EPISODES.EPISODE_DATE Between @.StartDate and @.EndDate
AND srm.EPISODES.EPISODE_TYPE IN(@.VisitTypeCode)
Group By srm.episodes.account_number,
dbo.PtMstr.TotCharges,
srm.episodes.episode_type,
srm.episodes.medrec_no,
dbo.PtMstr.PatientFullName,
srm.episodes.admission_date,
srm.episodes.episode_date,
dbo.PtMstr.Cases,
srm.cdmab_base_info.abst_cmp_status,
srm.cdmab_base_info.adm_dx_adt,
srm.event_types.event_type_code

Use the following query..

select

srm.episodes.episode_type as Visit_Type,

srm.episodes.account_number as Account_Number,

srm.episodes.medrec_no as MRN,

dbo.PtMstr.PatientFullName,

left(srm.episodes.admission_date,11) as Admit_Date,

left(srm.episodes.episode_date,11) as Disch_Date,

dbo.PtMstr.Cases as Cases,

dbo.PtMstr.TotCharges,

srm.cdmab_base_info.abst_cmp_status as Abtract_Comp_Status,

srm.cdmab_base_info.adm_dx_adt as Admitting_Dx

,srm.event_types.event_type_code

from

srm.cdmab_base_info

inner join srm.episodes on srm.episodes.episode_key = srm.cdmab_base_info.episode_key

inner join srm.event_history on srm.event_history.item_key = srm.episodes.episode_key

inner join srm.event_types on srm.event_types.event_type_key = srm.event_history.event_type_key

inner join dbo.PtMstr on dbo.PtMstr.AccountNumber = srm.episodes.account_number

where

srm.cdmab_base_info.abst_cmp_status <> 'Y'

and srm.episodes.episode_date is not null

and srm.event_types.event_type_code <> 'ACOD'

AND srm.EPISODES.EPISODE_DATE Between @.StartDate and @.EndDate

AND srm.EPISODES.EPISODE_TYPE IN(@.VisitTypeCode)

Group By

srm.episodes.account_number,

dbo.PtMstr.TotCharges,

srm.episodes.episode_type,

srm.episodes.medrec_no,

dbo.PtMstr.PatientFullName,

left(srm.episodes.admission_date,11) as Admit_Date,

left(srm.episodes.episode_date,11) as Disch_Date,

dbo.PtMstr.Cases,

srm.cdmab_base_info.abst_cmp_status,

srm.cdmab_base_info.adm_dx_adt,

srm.event_types.event_type_code

|||

I had to remove the AS portion of the group by clause to get the code to work , however, it still returns 86 rows versus the expected 9 distinct rows.

|||

How you know there is only 9 distinct record. You only the get the number of rows as per the following query..& i didn't understand your requirement on your query(there is no group by funcations used).

select Distinct

srm.episodes.episode_type as Visit_Type,

srm.episodes.account_number as Account_Number,

srm.episodes.medrec_no as MRN,

dbo.PtMstr.PatientFullName,

left(srm.episodes.admission_date,11) as Admit_Date,

left(srm.episodes.episode_date,11) as Disch_Date,

dbo.PtMstr.Cases as Cases,

dbo.PtMstr.TotCharges,

srm.cdmab_base_info.abst_cmp_status as Abtract_Comp_Status,

srm.cdmab_base_info.adm_dx_adt as Admitting_Dx

,srm.event_types.event_type_code

from

srm.cdmab_base_info

inner join srm.episodes on srm.episodes.episode_key = srm.cdmab_base_info.episode_key

inner join srm.event_history on srm.event_history.item_key = srm.episodes.episode_key

inner join srm.event_types on srm.event_types.event_type_key = srm.event_history.event_type_key

inner join dbo.PtMstr on dbo.PtMstr.AccountNumber = srm.episodes.account_number

where

srm.cdmab_base_info.abst_cmp_status <> 'Y'

and srm.episodes.episode_date is not null

and srm.event_types.event_type_code <> 'ACOD'

AND srm.EPISODES.EPISODE_DATE Between @.StartDate and @.EndDate

AND srm.EPISODES.EPISODE_TYPE IN(@.VisitTypeCode)

|||

I appreciate you help. I ordered the data by account number and saw there were 9 distinct account numbers. I also noticed the srm.event_types.event_type_code field should not have been in this query; once I removed it, the code returned the expected 9 rows of data using either of the examples you provided. Thanks again for your assistance.

|||

You are grouping several additional columns after the account number.

If you just want the nine accounts listed, you'll need to just group on that column.

Then you can apply aggregates to get sums, etc. of the other data you desire.

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 Clause Help

Hello the code below shows multiple instances of targets.name "donor type" I could not correclty run the code without including contributions.program. I would like the output to only have 1 value for each donor type. How would I do this or workaround to get it done?

- thanks for your time.

SQL> SELECT targets.name "DONOR TYPE", contribution.program,
2 SUM(contribution.amount) "CONTRIBUTION QTR2"
3 FROM donor, contribution, targets
4 WHERE contribution.cdate >= TO_DATE('04/01/03', 'MM/DD/YY')
5 AND contribution.cdate <= TO_DATE('06/30/03', 'MM/DD/YY')
6 AND donor.donor = contribution.donor
7 AND targets.type = donor.type
8 GROUP BY targets.name, contributions.program;

DONOR TYPE PROGRAM CONTRIBUTION QTR2
------- -------- ------
Corporate Donors Applied Research 100
Foundations Applied Research 175
Individuals Basic Research 50
Corporate Donors International Programs 100
Corporate Donors Teaching Programs 50
Foundations Teaching Programs 50What prevents you from doing this?:

SQL> SELECT targets.name "DONOR TYPE",
2 SUM(contribution.amount) "CONTRIBUTION QTR2"
3 FROM donor, contribution, targets
4 WHERE contribution.cdate >= TO_DATE('04/01/03', 'MM/DD/YY')
5 AND contribution.cdate <= TO_DATE('06/30/03', 'MM/DD/YY')
6 AND donor.donor = contribution.donor
7 AND targets.type = donor.type
8 GROUP BY targets.name;

DONOR TYPE CONTRIBUTION QTR2
------- ------
Corporate Donors 250
Foundations 225
Individuals 50|||How can I get tthe output to look like this?

DONOR TYPE PROGRAM CONTRIBUTION QTR2
------- -------- ------
Corporate Donors Applied Research 100
International Programs 100
Teaching Programs 50

Foundations Applied Research 175
Teaching Programs 50
Individuals Basic Research 50

- thanks for your help|||Oh I see, you mean suppress the output of the repeated value?

In SQL Plus, use:

SQL> BREAK ON "DONOR TYPE"

Also, add "ORDER BY targets.name, contributions.program" after the GROUP BY clause to be sure the ordering is correct (GROUP BY doesn't guarantee the order).|||Great this worked for that table--thanks a bunch. How can I group by contrubuion by member only so there is only one isntance per name and the sum of all rows info and still maintain each of the columns. this can be easily done by removing the target column but I need to display it along with the others belwo. Once put the target column in I must also gruoup by member.qtr1 which produces the multiple row output. How can I work around this to only group by member?

SQL> SELECT contribution.member, member.qtr1 "TARGET",
2 SUM(contribution.amount) "CONT. QTR1",
3 ROUND(SUM(contribution.amount)/member.qtr1,3)*10 "% OF PROJECTION"
4 FROM contribution, member
5 WHERE contribution.cdate >= TO_DATE('01/01/03', 'MM/DD/YY')
6 AND contribution.cdate <= TO_DATE('03/31/03', 'MM/DD/YY')
7 GROUP BY contribution.member, member.qtr1;

MEMBER TARGET CONT. QTR1 % OF PROJECTION
----- ---- ---- -----
Adams 50 175 35
Adams 75 175 23.33
Adams 100 175 17.5
Adams 150 175 11.67
Adams 200 175 8.75
Adams 250 175 7
Baker 50 100 20
Baker 75 100 13.33
Baker 100 100 10
Baker 150 100 6.67
Baker 200 100 5

GROUP BY and populating temp table, ideas?

Hello All,
I have the following table, and want to create a report as seen below.
CREATE TABLE [dbo].[Sales] (
[ACTIVITY_ID] [varchar] (16) NOT NULL ,
[CREATED_BY] [varchar] (10) NULL,
[YEAR] [varchar] (9) NULL ,
[PERIOD] [varchar] (2) NULL ,
[WEEK] [char] (1) NULL ,
[AMOUNT] [varchar] (3) NULL
) ON [PRIMARY]
GO
I need to see count(*) of each rep for each w and current preriod.
Something like this:
REP_NAME W1 W2 W3 W4 W5 Period(Month)
======== ===== ===== ===== ===== ===== =============
DAVID 5 10 5 20
WILLIAM 2 8 5 15
JANE 10 2 10 22
Do I need to run seperate group by's for each w and populate a temp table
?
Or there can be a simpler way to do that?
Thanks,
Ada
--
SQL Server DBAFirst of all, pivoting data for presentation purposes does not belong on the
data layer.
But if you really, really, really need to do it in T-SQL read this:
http://www.windowsitpro.com/Article...15608.html?Ad=1
ML

group by and count

I want to know how to merge the following data. I am using 4 queries below. I was hoping to do it with 1 query.
Table1
Dist Fund
VAE AO
VAW AO
MD Court
MD Judge
CAC AO
CAC Court
VAE Judge
VAE Judge
I want to join the following 3 queries:
DcountAll DcountAO
Select Dist, Count(Dist)as Count from Table1 GROUP BY Table1.Dist Select Dist, Count(Dist) as Count from Table1 Where Dist='AO' GROUP BY Table1.Dist

DcountCourt
Select Dist, Count(Dist) as Count from Table1 Where Dist='Court' GROUP BY Table1.Dist

SELECT DCountAll.Dist, DCountAll.Count, DcountAO.Count AS AO, DcountCourt.Count
FROM DcountCourt RIGHT JOIN (DcountAO RIGHT JOIN DCountAll ON DcountAO.Dist = DCountAll.Dist) ON DcountCourt.Dist = DCountAll.Dist;

I'm not quite sure what you are wanting to see in your result set. The first query will return all unique Dist with a count for each. The second query returns the name and count only for "AO", and the third returns the count only for "Court".

The first query would already contain the results from queries 2 and 3. I don't quite understand the 4th query.

|||

Benefit from the fact that NULLs are excluded from aggregates

SELECT COUNT(*) as AllCount,COUNT(CASE WHEN Dist='AO' THEN 1 ELSE NULL) AS AOCount,COUNT(CASE WHEN Dist='Court' THEN 1 ELSE NULL) AS CourtCount FROM Table1

GROUP BY - are JOINS the problem or is it Me?

Below is an SQL statement where I am trying to use a GROUP BY option. If I remove the GROUP BY it works fine but with the GROUP BY included it generates the following error detail: Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'GROUP'. I have tried removing the ORDER BY and it still fails. Is the GROUP BY operation limited by table JOINS or am I missing something really simple?

I am working with three tables: 1) [Stores] has the franchise type (S_Type - kiosk, mall store, free standing, etc) 2) [StoreInfo] has zip code and location info and 3) ZipLatLong has ZipCodes and their respective latitude and longitude. I want to creat a Distinct List of Stores.S_Type but in the result set I have several Stores.S_Types with different Zips thus the rows are not unique. I was expecting the GROUP BY statement to allow me to return a collection of unique Stores.S_Type. The Lat/Long WHERE segment is providing a geographical frame for the result set. Everything is working except for the GROUP BY.

<

asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:localStores1ConnectionString %>"SelectCommand="SELECT Stores.S_Type, StoreInfo.S_Zip, ZipLatLong.ZIPCode, ZipLatLong.Lat, ZipLatLong.Long FROM

StoreInfo INNER JOIN ZipLatLong ON StoreInfo.S_Zip = ZipLatLong.ZIPCode INNER JOIN Stores

ON StoreInfo.S_ID = Stores.S_ID WHERE (((ZipLatLong.Lat)< @.NLat AND (ZipLatLong.Lat)> @.SLat )) AND(((ZipLatLong.Long)< @.ELong AND

(ZipLatLong.Long)> @.WLong )) ORDER BY Stores.S_Type GROUP BY Stores.S_Type;">

The Group BY without an aggregate function is just a standard Distinct so SQL Server is telling you code is not correct, check the docs below and see adjustments you need and the second link download the code samples it will help you. One more thing I see you are doing calculations of Longitude and Latitude without a lot of Math functions that could be a problem, I would look for existing code for such complex calculations. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms177673.aspx
http://mhprofessional.com/product.php?isbn=0072260939&cat=&pro

|||

I am using SQL 2000, is there any difference in the GROUP BY statements requirments for SQL 2000 vs. SQL 2005? I obviously do not yet understand the aggregate aspect of the coding and will read your links to hopefully gain the concept.

( The geographical mathematical work is done in its own class and the limits are passed into the SQL statement - without the GROUP BY statement everything works fine but I get multiple Store Types because they exist in different locations.)

|||

I don't think the GROUP BY clause changed check the link below for some advice about GROUP BY. Hope this helps.

http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx

Group By

Hi

I am having problems returning the necessary data with my SQL query. The query below is returning me the correct data but aswell as getting the datemodified column, I need to get the value in the ID column that is associated with the max date modified.

SELECT DISTINCT max([item].[datemodified])
FROM [Item] INNER JOIN [ItemDetails] ON [Item].[ID] = [ItemDetails].[ItemID]
WHERE [ItemDetails].[ItemTypeID] = 1
GROUP BY [ItemDetails].[ItemValue]

The items table also has an id column, basically I need to get the IDs of the most recent itemvalues. There are multipe entries row in the itemdetail table for the same itemvalue so that why I need to do the group by. In essence I need to order the individual groups, if this is possible.

Any help would be appreciated.

Here it is...

Code Snippet

--On SQL Server 2000

Select

Item.Id

, Item.[datemodified]

From

Item Join

(

SELECT DISTINCT max([item].[datemodified]) max_datemodified

FROM [Item] INNER JOIN [ItemDetails] ON [Item].[ID] = [ItemDetails].[ItemID]

WHERE [ItemDetails].[ItemTypeID] = 1

) as Data

On Item.[datemodified] = Data.[max_datemodified]

--On SqlServer 2005

Select Id, datemodified

From

(

Select [Item].Id, [item].[datemodified], max([item].[datemodified]) Over (Partition BY 0) Max_datemodified

From [Item] INNER JOIN [ItemDetails] ON [Item].[ID] = [ItemDetails].[ItemID]

WHERE [ItemDetails].[ItemTypeID] = 1

) as Data

Where

datemodified = Max_datemodified

|||

Thanks for that, just had to change the Partition By 0 to Partition By [ItemDetails].[ItemValue] and it worked perfect.

Cheers.

gridview sql timeout

I have a simple gridview displaying data from an MSSQL server 2005. Every now and then I get a sql timeout error. Listed below. Can anyone explain why I am getting this error? The connection pool is 100 and the timeout is set to 360. I have checked to current connections in SQL and they never max over 23. There are not locks in SQL when the problem occurs. The query is a stored procedure in sql and when sent sample data it normally takes about 5 seconds.

Event code: 3005
Event message: An unhandled exception has occurred.
Event time: 12/3/2007 9:46:37 PM
Event time (UTC): 12/4/2007 3:46:37 AM
Event ID: 140501f9a7744dfea2e445ed00939e44
Event sequence: 42
Event occurrence: 1
Event detail code: 0

Application information:
Application domain: /LM/W3SVC/1/ROOT-1-128412128787656250
Trust level: Full
Application Virtual Path: /
Application Path: c:\inetpub\wwwroot\
Machine name: DD-MAIN

Process information:
Process ID: 5544
Process name: w3wp.exe
Account name: NT AUTHORITY\NETWORK SERVICE

Exception information:
Exception type: SqlException
Exception message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Request information:
Request URL:http://localhost/Search_DG.aspx?SearchWord=1212
Request path: /Search_DG.aspx
User host address: 10.10.10.1
User:
Is authenticated: False
Authentication Type:
Thread account name: NT AUTHORITY\NETWORK SERVICE

Thread information:
Thread ID: 1
Thread account name: NT AUTHORITY\NETWORK SERVICE
Is impersonating: False
Stack trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.SetMetaData(_SqlMetaDataSet metaData, Boolean moreInfo)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments)
at System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback)
at System.Web.UI.WebControls.DataBoundControl.PerformSelect()
at System.Web.UI.WebControls.BaseDataBoundControl.DataBind()
at System.Web.UI.WebControls.GridView.DataBind()
at System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound()
at System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls()
at System.Web.UI.Control.EnsureChildControls()
at System.Web.UI.WebControls.GridView.get_Rows()
at Install_DG.Page_Load(Object sender, EventArgs e)
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)


Custom event details:

For more information, see Help and Support Center athttp://go.microsoft.com/fwlink/events.asp.

Hi

I think you need to set the timeout property of Data objects, other than Connection object.

Please refer the URL :http://techielion.blogspot.com/2007/01/error-timeout-expired-timeout-period.html|||

I think this is what you were referring to.

http://www.velocityreviews.com/forums/showpost.php?s=4fa167ceba48b31fab486f5381b1455d&p=436028&postcount=4

I will try this and let you know if it worked. It will take a good day to test.

Thanks for the info.

|||

Thanks for the help. I have tested the timeout setting on the selecting event for the gridview and it worked great.