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 record based on a condtion

TechnologyTypeSize

XYZA200

XYZ1A200

XYZ2A300

XYZ3A300

ABC1X238

ABC2X238

PQRB320

MNOC330

I have written a query on a table whose output will look like the above. I need to know if i should store this in a record set or create a temp table to get the following fuctionality.

Now I need to concatenate the Technology based on Type and size.

As you can see in Type A we have two sizes 200 and 300.

We need to group the Technology of type A with same size together.

So the output of the procedure should be

XYZ + XYZ1

XYZ2+ XYZ3

ABC1 + ABC2etc.

We need to concatenate the Technology string with the next technology if they have the same type and size.

Can somebody please help or send any sample code.

Any help is greatly appreciated

Thanks

Swapna

CTE solution for SQL Server 2005:

With MyCTE(Size, Type, col1, col2, myNum) AS

(

SELECT a.Size, a.Type, CONVERT(varchar(50), MIN(RTRIM(a.Technology))) as col1, CONVERT(varchar(50),RTRIM((a.Technology))) as col2, 1 as myNum

FROM techTable AS a GROUP BY a.Size, a.Type, CONVERT(varchar(50),RTRIM(a.Technology))

UNION ALL

SELECT b.Size, b.Type, CONVERT(varchar(50), RTRIM(b.Technology)) as col1, CONVERT(varchar(50), (c.col2 + '+' + RTRIM(b.Technology))) as col2, c.myNum+1 as myNum

FROM techTable AS b INNER JOIN MyCTE c ON b.Size=c.Size AND b.Type= c.Type

WHERE b.Technology>c.col1

)

SELECT a.col2 As Technology_combined, a.Size, a.Type FROM MyCTE a INNER JOIN (SELECT Max(a1.myNum) as myNumMax, a1.Size, a1.Type FROM MyCTE a1

GROUP BY a1.Size, a1.Type) b on b.Size=a.Size AND b.Type= a.Type AND a.myNum= b.myNumMax

|||

you I am new to stored procedures...and working with the databse...so could you please explain the above code...I could not get much from it...Will the loop through the sample table I mentioned and return a set of concatenated Technology values....Please get back.

Thanks for your reply

Swapna

|||

and more over the data in the table is just an example...we are in no way concerned with the data in Technology Column...all we need to do is group the technology column data which have the same Type and Size

TechnologyTypeSize

XYZA200

ABCA200

ABC1A300

XYZ3A300

MNO1X238

ABC2X238

PQRB320

MNOC330

so the output should be XYZ+ABC

ABC1+XYZ3

MNO1+ABC2.... I hope I am clear now.

Please reply...Can we use cursors to do this...can someone explain how to use cursors for the above functionality

Thanks

|||

Hello:

The "techTable" would be the name of your table which holds your data.

The CTE code I posted will work in a recursive fasion.

If you are using SQL Server 2005, you can give the code a try run (remember to change the "techTable" to your table name).

|||

--CREATE TABLE MyTable(Technology VARCHAR(MAX), Type char(10), Size int)

--Enter the values suggested

--Run the following code

DECLARE @.Type CHAR(1)

DECLARE @.Size INT

DECLARE @.MyNewString CHAR(11)

DECLARE @.MyNewString2 VARCHAR(MAX)

SET @.MyNewString2 = ''

--Replace MyTable with your tablename

--Replace Technology, Type, Size with your field names

CREATE TABLE #Temp(MyNewString VARCHAR(MAX))

DECLARE c1 CURSOR FOR

SELECT mt.Type, mt.Size

FROM MyTable mt

OPEN c1

FETCH NEXT FROM c1

INTO @.Type, @.Size

WHILE @.@.FETCH_STATUS = 0

BEGIN

DECLARE c2 CURSOR FOR

SELECT Technology from MyTable Where size = @.Size and type = @.Type

OPEN c2

FETCH NEXT FROM c2

INTO @.MyNewString

WHILE @.@.FETCH_STATUS = 0

BEGIN

SET @.MyNewString2 = LTRIM(RTRIM(@.MyNewString2)) + LTRIM(RTRIM(@.MyNewString))

FETCH NEXT FROM c2

INTO @.MyNewString

END

CLOSE c2

DEALLOCATE c2

INSERT INTO #Temp(MyNewString) VALUES(@.MyNewString2)

SET @.MyNewString2 = ''

FETCH NEXT FROM c1

INTO @.Type, @.Size

END

CLOSE c1

DEALLOCATE c1

SELECT * from #Temp

GROUP BY MyNewString

DROP TABLE #temp

|||

limno wrote:

CTE solution for SQL Server 2005:

With MyCTE(Size, Type, col1, col2, myNum) AS

(

SELECT a.Size, a.Type, CONVERT(varchar(50), MIN(RTRIM(a.Technology))) as col1, CONVERT(varchar(50),RTRIM((a.Technology))) as col2, 1 as myNum

FROM techTable AS a GROUP BY a.Size, a.Type, CONVERT(varchar(50),RTRIM(a.Technology))

UNION ALL

SELECT b.Size, b.Type, CONVERT(varchar(50), RTRIM(b.Technology)) as col1, CONVERT(varchar(50), (c.col2 + '+' + RTRIM(b.Technology))) as col2, c.myNum+1 as myNum

FROM techTable AS b INNER JOIN MyCTE c ON b.Size=c.Size AND b.Type= c.Type

WHERE b.Technology>c.col1

)

SELECT a.col2 As Technology_combined, a.Size, a.Type FROM MyCTE a INNER JOIN (SELECT Max(a1.myNum) as myNumMax, a1.Size, a1.Type FROM MyCTE a1

GROUP BY a1.Size, a1.Type) b on b.Size=a.Size AND b.Type= a.Type AND a.myNum= b.myNumMax

This code is equivalent to my nested cursor approach and works, but I agree is a tad bit confusing...but nice work all the same.|||

You don't need to use cursors to get the results. Using cursors is often inefficient and consumes more resources than necessary. Very few problems require cursor based solutions and if you don't know how to use cursors that is actually good. :-) You can learn the basics of SQL to begin with than cursors.

If you are using SQL Server 2005 you can use below approach which will be faster than CTE and slightly simpler.

select t2.Type

, t2.Size

, max(case t2.seq when 1 then t1.Technology end)

+ max(case t2.seq when 2 then '+' + t2.Technology else '' end) as Technology

from (

select t1.Type, t1.Technology, t1.Size

, ROW_NUMBER() OVER(partition by t1.Type, t1.Size order by t1.Technology) as seq

from tbl as t1

) as t2

group by t2.Type, t2.Size;

You can use similar logic in older versions of SQL Server also since they don't have the ROW_NUMBER() function.

Below working query uses pubs authors table and you can do the same based on your table schema.

select a2.city, a2.state
, max(case a2.seq when 1 then a2.au_id else '' end)
+ max(case a2.seq when 2 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 3 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 4 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 5 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 6 then ', ' + a2.au_id else '' end) as au_ids
from (
select a1.city, a1.state, a1.au_id, row_number() over(partition by a1.city, a1.state order by a1.au_id) as seq
from authors as a1
) as a2
group by a2.city, a2.state
order by a2.state, a2.city;

|||

Umachandar Jayachandran - MS wrote:

You don't need to use cursors to get the results. Using cursors is often inefficient and consumes more resources than necessary. Very few problems require cursor based solutions and if you don't know how to use cursors that is actually good. :-) You can learn the basics of SQL to begin with than cursors.

If you are using SQL Server 2005 you can use below approach which will be faster than CTE and slightly simpler.

select t2.Type

, t2.Size

, min(case t2.seq when 1 then t1.Technology end)

+ min(case t2.seq when 2 then '+' + t2.Technology else '' end) as Technology

from (

select t1.Type, t1.Technology, t1.Size

, ROW_NUMBER() OVER(partition by t1.Type, t1.Size order by t1.Technology) as seq

from tbl as t1

) as t2

group by t2.Type, t2.Size;

You can use similar logic in older versions of SQL Server also since they don't have the ROW_NUMBER() function.

Not knowing cursors is a good thing? Can we go a step further with your logic and say not knowing SQL is a good thing? Use ADO?

...and could you post some working code. I'm interested in this approach but getting errors.

Thanks,

Adamus

|||

Umachandar Jayachandran - MS wrote:

You don't need to use cursors to get the results. Using cursors is often inefficient and consumes more resources than necessary. Very few problems require cursor based solutions and if you don't know how to use cursors that is actually good. :-) You can learn the basics of SQL to begin with than cursors.

If you are using SQL Server 2005 you can use below approach which will be faster than CTE and slightly simpler.

select t2.Type

, t2.Size

, min(case t2.seq when 1 then t1.Technology end)

+ min(case t2.seq when 2 then '+' + t2.Technology else '' end) as Technology

from (

select t1.Type, t1.Technology, t1.Size

, ROW_NUMBER() OVER(partition by t1.Type, t1.Size order by t1.Technology) as seq

from tbl as t1

) as t2

group by t2.Type, t2.Size;

You can use similar logic in older versions of SQL Server also since they don't have the ROW_NUMBER() function.

I unmarked this as the answer because the poster requested a cursor approach.|||

Not using procedural logic when dealing with SQL is a good thing. Yes, you can use ADO/client-side code to do this but it will be very slow and inefficient. If you have a table that contains say millions of rows you will be moving those rows from client to server for each user and performing the logic on the client side. Moreover, you have to implement lot of specific logic on the client side whereas the SQL language has built-in functionality / primitives to solve complex problems easily.

Anyway, here is a query that uses pubs authors table:

select a2.city, a2.state
, max(case a2.seq when 1 then a2.au_id else '' end)
+ max(case a2.seq when 2 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 3 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 4 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 5 then ', ' + a2.au_id else '' end)
+ max(case a2.seq when 6 then ', ' + a2.au_id else '' end) as au_ids
from (
select a1.city, a1.state, a1.au_id, row_number() over(partition by a1.city, a1.state order by a1.au_id) as seq
from authors as a1
) as a2
group by a2.city, a2.state
order by a2.state, a2.city;

The query produces a comma-separated list of author ids for each state and city combination similar to the problem.

Grouping question

I have the following query:
SELECT PR_NO,
Total = CASE Items.Use_Item_Calc_Qty
WHEN 0 THEN CONVERT(money, SUM
(items.unit_price * items.qty))
ELSE CONVERT(money, SUM(items.unit_price *
items.qty * ITEM_CALC_QTY))
END
FROM Items
where pr_no = 5816
Group By PR_NO, Use_Item_Calc_Qty
The query is returning two records because a record in
the items table has a value of 0 in Use_item_calc_qty and
another record has a value of one.
What I want to return is only one record showing the
total for the Purchase Request. Can anyone help me with
this. I appreciate it.Vic,
I think this is what you wanted to do (your statement of problem is not
quite clear):
SELECT PR_NO,
Total = CONVERT(money, SUM(items.unit_price *
items.qty * (CASE Items.Use_Item_Calc_Qty when 0 then 1 else
Use_Item_Calc_Qty end)))
FROM Items
where pr_no = 5816
Group By PR_NO, Use_Item_Calc_Qty
hth
Quentin
"Vic" <vduran@.specpro-inc.com> wrote in message
news:000d01c3c0dc$56b27560$a501280a@.phx.gbl...
> I have the following query:
> SELECT PR_NO,
> Total => CASE Items.Use_Item_Calc_Qty
> WHEN 0 THEN CONVERT(money, SUM
> (items.unit_price * items.qty))
> ELSE CONVERT(money, SUM(items.unit_price *
> items.qty * ITEM_CALC_QTY))
> END
> FROM Items
> where pr_no = 5816
> Group By PR_NO, Use_Item_Calc_Qty
> The query is returning two records because a record in
> the items table has a value of 0 in Use_item_calc_qty and
> another record has a value of one.
> What I want to return is only one record showing the
> total for the Purchase Request. Can anyone help me with
> this. I appreciate it.sql

Grouping query

Bit stumped by this one, any advice would be appreciated.
I have two tables ([owner] and [cars]) which have a one-2-many relationship
(i.e. one owner can own one or multiple cars, a car can have but one owner).
The cars have several properties: number-plate, make, model, colour & fuel,
so for example: A123 456P, BMW, 850, red, petrol, the number-plate making it
unique.
Ignoring, the number plate property, the other 4 fields can be duplicated.
So, there are several owners who own a red BMW 850 petrol.
What I need to do is this.
I need to bring back a list of all the owner IDs and "group" them together
when they have IDENTICAL car COLLECTIONS.
So, imagine that there are four owners who all own only 3 cars: 1 x red BMW
850 petrol, 1 x blue Ford Escort Diesel and 1 x pink VW golf diesel then I'd
want their owner ID's all with a group ID of (say) 6.
234, 6
368, 6
573, 6
962, 6
Similarly for all owners.
Any suggestions?
Many thanks
GriffGriff
Please post DDL+ sample data + expected result
CREATE TABLE Owners
(
OwnerId INT NOT NULL PRIMARY KEY,
...
...
)
CREATE TABLE Cars
(
CarId INT NOT NULL PRIMARY KEY
Ownerid INT NOT NULL ...
)
INSERT INTO Owners VALUES ....
INSERT INTO Cars VALUES ......
I'd like to get the below output
............
"Griff" <Howling@.The.Moon> wrote in message
news:OxORVEQYFHA.3188@.TK2MSFTNGP09.phx.gbl...
> Bit stumped by this one, any advice would be appreciated.
> I have two tables ([owner] and [cars]) which have a one-2-many
relationship
> (i.e. one owner can own one or multiple cars, a car can have but one
owner).
> The cars have several properties: number-plate, make, model, colour &
fuel,
> so for example: A123 456P, BMW, 850, red, petrol, the number-plate making
it
> unique.
> Ignoring, the number plate property, the other 4 fields can be duplicated.
> So, there are several owners who own a red BMW 850 petrol.
> What I need to do is this.
> I need to bring back a list of all the owner IDs and "group" them together
> when they have IDENTICAL car COLLECTIONS.
> So, imagine that there are four owners who all own only 3 cars: 1 x red
BMW
> 850 petrol, 1 x blue Ford Escort Diesel and 1 x pink VW golf diesel then
I'd
> want their owner ID's all with a group ID of (say) 6.
> 234, 6
> 368, 6
> 573, 6
> 962, 6
> Similarly for all owners.
> Any suggestions?
> Many thanks
> Griff
>|||Here goes:
SQL for creation is as follows:
========================================
===========================
CREATE TABLE [dbo].[owners] (
[ownerID] [int] IDENTITY (1, 1) NOT NULL ,
[surname] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[owners] ADD
CONSTRAINT [PK_owners] PRIMARY KEY CLUSTERED
(
[ownerID]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[cars] (
[carID] [int] IDENTITY (1, 1) NOT NULL ,
[ownerID] [int] NOT NULL ,
[registration] [char] (8) COLLATE Latin1_General_CI_AS NOT NULL ,
[make] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[model] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[colour] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[cars] ADD
CONSTRAINT [PK_cars] PRIMARY KEY CLUSTERED
(
[carID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[cars] ADD
CONSTRAINT [FK_cars_owners] FOREIGN KEY
(
[ownerID]
) REFERENCES [dbo].[owners] (
[ownerID]
)
insert into owners (surname) values ('smith')
insert into owners (surname) values ('davey')
insert into owners (surname) values ('bird')
insert into owners (surname) values ('gates')
insert into cars (ownerid, registration, make, model, colour) values
(1,'abcdefgh','bmw','850','red')
insert into cars (ownerid, registration, make, model, colour) values
(2,'bcdefghi','fiat','panda','blue')
insert into cars (ownerid, registration, make, model, colour) values
(2,'cdefghij','bmw','850','red')
insert into cars (ownerid, registration, make, model, colour) values
(3,'defghijk','bmw','850','red')
insert into cars (ownerid, registration, make, model, colour) values
(4,'efghijkl','fiat','panda','blue')
insert into cars (ownerid, registration, make, model, colour) values
(4,'fghijklm','bmw','850','red')
========================================
===========================
Results wanted:
A car is considered identical to another car if the MAKE, MODEL and COLOUR
are identical (not ID or registration)
I want to create an arbitary grouping "letter" to group all owner IDs that
own the same collection of cars
Owner ID Group Code
1 A
2 B
3 A
4 B
Both owners 1 & 3 both own one car and that car is a red BMW 850 - they
therefore get assigned group code A (could be a group ID 1, doesn't matter)
Both owners 2 & 4 own two cars, one a red BMW 850 and a blue Fiat Panda, so
are assigned a different group code.
It's really saying "I want to bracket together all the people who have an
identical set of cars in their garage"
Hope this helps!
Griff
========================================
===========================|||Griff
SELECT O.ownerid,COUNT(c.ownerid)AS GroupId FROM Owners
o JOIN Cars c ON o.ownerid=c.ownerid
GROUP BY O.ownerid
"Griff" <Howling@.The.Moon> wrote in message
news:%23Ie7CTRYFHA.3356@.TK2MSFTNGP15.phx.gbl...
> Here goes:
> SQL for creation is as follows:
> ========================================
===========================
> CREATE TABLE [dbo].[owners] (
> [ownerID] [int] IDENTITY (1, 1) NOT NULL ,
> [surname] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[owners] ADD
> CONSTRAINT [PK_owners] PRIMARY KEY CLUSTERED
> (
> [ownerID]
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[cars] (
> [carID] [int] IDENTITY (1, 1) NOT NULL ,
> [ownerID] [int] NOT NULL ,
> [registration] [char] (8) COLLATE Latin1_General_CI_AS NOT NULL ,
> [make] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [model] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [colour] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[cars] ADD
> CONSTRAINT [PK_cars] PRIMARY KEY CLUSTERED
> (
> [carID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[cars] ADD
> CONSTRAINT [FK_cars_owners] FOREIGN KEY
> (
> [ownerID]
> ) REFERENCES [dbo].[owners] (
> [ownerID]
> )
> insert into owners (surname) values ('smith')
> insert into owners (surname) values ('davey')
> insert into owners (surname) values ('bird')
> insert into owners (surname) values ('gates')
> insert into cars (ownerid, registration, make, model, colour) values
> (1,'abcdefgh','bmw','850','red')
> insert into cars (ownerid, registration, make, model, colour) values
> (2,'bcdefghi','fiat','panda','blue')
> insert into cars (ownerid, registration, make, model, colour) values
> (2,'cdefghij','bmw','850','red')
> insert into cars (ownerid, registration, make, model, colour) values
> (3,'defghijk','bmw','850','red')
> insert into cars (ownerid, registration, make, model, colour) values
> (4,'efghijkl','fiat','panda','blue')
> insert into cars (ownerid, registration, make, model, colour) values
> (4,'fghijklm','bmw','850','red')
> ========================================
===========================
> Results wanted:
> A car is considered identical to another car if the MAKE, MODEL and COLOUR
> are identical (not ID or registration)
> I want to create an arbitary grouping "letter" to group all owner IDs that
> own the same collection of cars
> Owner ID Group Code
> 1 A
> 2 B
> 3 A
> 4 B
> Both owners 1 & 3 both own one car and that car is a red BMW 850 - they
> therefore get assigned group code A (could be a group ID 1, doesn't
matter)
> Both owners 2 & 4 own two cars, one a red BMW 850 and a blue Fiat Panda,
so
> are assigned a different group code.
> It's really saying "I want to bracket together all the people who have an
> identical set of cars in their garage"
> Hope this helps!
> Griff
> ========================================
===========================
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%236uDRRSYFHA.2128@.TK2MSFTNGP14.phx.gbl...

> SELECT O.ownerid,COUNT(c.ownerid)AS GroupId FROM Owners
> o JOIN Cars c ON o.ownerid=c.ownerid
> GROUP BY O.ownerid
Hi Uri
This does not take into account whether the rows actually have the same
values in them.
In the CARS table, change the model from "BMW" to "Bently" and owner 1 will
still end up in the same group as owner 3.
I'd need them to be different - the collection size is the same, but it's a
different collection.
Griff|||Solved it, so thanks everyone!
Griff

grouping problem...

I am migrating an Oracle database to SQL. I can't the following query to
work
insert into JDE911Z1
(vnani,vnaa,vndgm,vndgd,vndgy,vnctry,vndct,vnexa,v nlt,vnedbt)
select approp_no + '.1354', sum(isnull(tot_cost,0)),
datepart(mm, GETDATE()) mm, datepart(dd, GETDATE()) dd,
substring( cast(DATEPART(yy, GETDATE()) as varchar(4)), 3,2) yy ,
'0','JE','DASNY CHARGEBACK','AA','AA'+@.client+cast (@.pay_no as varchar(2))
from perptot
where pay_no = @.pay_no and client = @.client'
and approp_no is not null
and b_cat = 'B'
group by approp_no + '.1354', datepart(mm, getdate()),
datepart(dd, getdate()), substring( cast(DATEPART(yy, GETDATE()) as
varchar(4)), 3,2),
'O', 'JE','DASNY CHARGEBACK','AA','AA','AA'+ @.client + cast (@.pay_no as
varchar(2))
The error I get is "GROUP BY expressions must refer to column names that
appear in the select list"
Group by approp_no + '.1354', datepart(mm, getdate()), datepart(dd,
getdate()) works. As soon as I add substring( cast(DATEPART(yy, GETDATE())
as varchar(4)), 3,2) it breaks.
That value just uses getdate() so should be a constant as far as the query is concerned and so does not need to appear in the group by clause.

grouping problem...

I am migrating an Oracle database to SQL. I can't the following query to
work
insert into JDE911Z1
(vnani,vnaa,vndgm,vndgd,vndgy,vnctry,vnd
ct,vnexa,vnlt,vnedbt)
select approp_no + '.1354', sum(isnull(tot_cost,0)),
datepart(mm, GETDATE()) mm, datepart(dd, GETDATE()) dd,
substring( cast(DATEPART(yy, GETDATE()) as varchar(4)), 3,2) yy ,
'0','JE','DASNY CHARGEBACK','AA','AA'+@.client+cast (@.pay_no as varchar(2))
from perptot
where pay_no = @.pay_no and client = @.client'
and approp_no is not null
and b_cat = 'B'
group by approp_no + '.1354', datepart(mm, getdate()),
datepart(dd, getdate()), substring( cast(DATEPART(yy, GETDATE()) as
varchar(4)), 3,2),
'O', 'JE','DASNY CHARGEBACK','AA','AA','AA'+ @.client + cast (@.pay_no as
varchar(2))
The error I get is "GROUP BY expressions must refer to column names that
appear in the select list"
Group by approp_no + '.1354', datepart(mm, getdate()), datepart(dd,
getdate()) works. As soon as I add substring( cast(DATEPART(yy, GETDATE())
as varchar(4)), 3,2) it breaks.That value just uses getdate() so should be a constant as far as the query i
s concerned and so does not need to appear in the group by clause.sql

Grouping pblm

Hi,

I have a query which returns several data fields and one of the grouping criteria is the time.
The data format for the time field resembles this: "10.11.2005 15:45:37" .. which is 'date.month.year calltime'.
I want the query to group data by the hour, therefore i wrote the query like this: Left([AllCalls.CALLTIME],13) AS HourlyCallTime, the HourlyCallTime field shows the data in this format: 10.11.2005 15, however, the grouping is not done. It only groups properly when I do this: Left([AllCalls.CALLTIME],12) AS HourlyCallTime, but then the problem is that the HourlyCallTime field does not show the proper format, it only displays this much: '10.11.2005 1'

I hope some1 can help me out :)

ThksI speak Oracle SQL; this is, as far as I can tell, not a language I know, but perhaps this piece of advice will help you ...

In Oracle, date format you wrote as an example (10.11.2005 15:45:37) is only one (of many possible) representations of a date column. Dates are stored as a number, and it is up to the developer to choose format he wants to present data to the end user.

Now, in Oracle, you should do this: FIRST format date column to desired format, and THEN write string functions on it.

For example, it would look like this:
- First part of the solution:
SELECT TO_CHAR(date_column, 'dd.mm.yyyy hh:mi:ss') FROM ...

- Second part of the solution:
SELECT SUBSTR(TO_CHAR(date_column, 'dd.mm.yyyy hh:mi:ss'), 1, 13) FROM ...

I really wouldn't know is this the case in your database, but - if nothing else shows up - you could try with this.|||Hello Littlefoot,

Thks 4 the quick rep, i've been working on it but now seem to be having sum other pblm,
my query

SELECT Zones.Zone, Left([AllCalls.CALLTIME],13) AS HourlyCallTime, SCCount.Connected, ((SCCount.Connected/(UCCount.NotConnected+SCCount.Connected)*100)) AS Val
FROM UCCount INNER JOIN (SCCount INNER JOIN ((Zones INNER JOIN AllCalls ON Zones.Zone = AllCalls.PREFIX) INNER JOIN AllCallsBack ON AllCalls.CALLID = AllCallsBack.CALLID) ON SCCount.PREFIX = AllCalls.PREFIX) ON UCCount.PREFIX = AllCalls.PREFIX
WHERE (((AllCalls.PREFIX)=[Zones].[Zone] And (AllCalls.PREFIX)=[SCCount].[PREFIX] And (AllCalls.PREFIX)=[UCCount].[PREFIX]))
GROUP BY Zones.Zone, Left([AllCalls.CALLTIME],13), SCCount.Connected, UCCount.NotConnected, AllCalls.PREFIX;

is not grouping all the calls, it's separating the connected and not connected such that am having twice the same row of data.
When I remove the SCCount.Connected and UCCount.NotConnected, it doesn't run, comes up with "query does not include the specified xpression .."

:S|||Hm, it seems that you, actually, do not want to GROUP data, but BREAK output on the hour. I'd say that use of a GROUP BY is meaningless if there's no aggregate function (such as MAX or AVG or COUNT) in the SELECT statement.

I don't know the tool you use (do you run this query on command prompt or in a reporting tool); if it is some kind of a report builder, you might want to use master-detail blocks of data.

On command prompt, all you can do is use of an ORDER BY clause and, eventually, use of (as Oracle provides) some kind of a BREAK command which will visually break data on the screen. Something like this:SQL> break on hire_year
SQL> select to_char(hiredate, 'yyyy') hire_year, ename, hiredate
2 from emp
3 order by 1, 2;

HIRE ENAME HIREDATE
-- ---- ---
1980 SMITH 17.12.80
1981 ALLEN 20.02.81
BLAKE 01.05.81
CLARK 09.06.81
FORD 03.12.81
JAMES 03.12.81
JONES 02.04.81
KING 17.11.81
MARTIN 28.09.81
TURNER 08.09.81
WARD 22.02.81
1982 MILLER 23.01.82

12 rows selected.

SQL>|||But I do want to group the data by zones and by the hour, but the query wouldn't run unless i include the connected n notconnected as part of the grouping criteria as well which is messing it up|||I assume Connected and NotConnectedand are some times and you calculete percentage. So if you group data by Zones etc. why don't you summarize times?

SELECT
Zones.Zone,
Left([AllCalls.CALLTIME],13) AS HourlyCallTime,
sum(SCCount.Connected),
((sum(SCCount.Connected)/(sum(UCCount.NotConnected+SCCount.Connected))*100) ) AS Val
FROM UCCount INNER JOIN (SCCount INNER JOIN ((Zones INNER JOIN AllCalls ON Zones.Zone = AllCalls.PREFIX) INNER JOIN AllCallsBack ON AllCalls.CALLID = AllCallsBack.CALLID) ON SCCount.PREFIX = AllCalls.PREFIX) ON UCCount.PREFIX = AllCalls.PREFIX
WHERE (((AllCalls.PREFIX)=[Zones].[Zone]
And (AllCalls.PREFIX)=[SCCount].[PREFIX]
And (AllCalls.PREFIX)=[UCCount].[PREFIX]))
GROUP BY Zones.Zone,
Left([AllCalls.CALLTIME],13),
AllCalls.PREFIX;

BTW you have too many brackets there. It's MS Access generated code, isn't it? :-) Why don't you select AllCalls.PREFIX if you group by it?|||Connected and notconnected are the count result from another query, and access wont let me run the query unless I have 'SCCount.Connected' and 'UCCount.NotConnected' in the grouping criteria ..

Yes, MS Access keeps adding loads of brackets when i run the query and go bak 2 sql view :Ssql

Grouping output

I have a query that drives the generation of a table. The query is filtered
during output. If the filtering results in no rows being output for the
table, i'd like to put some verbage on the table footer indicating "No
Matching Records" or something similar.
Is there an easy way to do this that i'm missing?
Thanks!
BrianTake a look at NoRows table property.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"G" <brian.grant@.si-intl-kc.com> wrote in message
news:e8xqt8gqEHA.324@.TK2MSFTNGP11.phx.gbl...
> I have a query that drives the generation of a table. The query is
filtered
> during output. If the filtering results in no rows being output for the
> table, i'd like to put some verbage on the table footer indicating "No
> Matching Records" or something similar.
> Is there an easy way to do this that i'm missing?
> Thanks!
> Brian
>sql

Grouping Data with Weekly break out?

Hi All,
I'm trying to figure out and learn how to do the following:
/*I have the following query which works:*/
SELECT DataSource AS [Service Line], NamePrep AS [PO Created By],
COUNT(PoNumber) AS [Total Of PONumber]
FROM OPW
WHERE (ReqSubmitDate BETWEEN '03/ 01/2005' AND '03/31/2005')
GROUP BY DataSource, NamePrep
My question here is how can I get a wly breakout of the COUNT(PoNumber)?
I want a column for each w and the number of PONumbers Per Datasource and
NamePrep for that w.
Example Output:
Service Line | PO Created By | Total Of PONumber | 3/5/2005 |
3/12/2005 | ETC...
MNS JOHN 10
MNS ERIC 25
FMS CARL 8
Hope my question makes sense :)
John.If you had a calendar table, this would be easier, but if not, you need to
know the columns you want, or use dynamic SQL...
Select DataSource AS [Service Line], NamePrep AS [PO Created By],
COUNT(PoNumber) AS [Total Of PONumber],
Sum(Case When ReqSubmitDate
Between '03/ 01/2005' AND '03/8/2005' Then 1 End) Wk1Count,
Sum(Case When ReqSubmitDate
Between '03/ 09/2005' AND '03/16/2005' Then 1 End) Wk2Count,
Sum(Case When ReqSubmitDate
Between '03/ 17/2005' AND '03/24/2005' Then 1 End) Wk3Count,
Sum(Case When ReqSubmitDate
Between '03/ 25/2005' AND '03/31/2005' Then 1 End) Wk4Count
FROM OPW
WHERE (ReqSubmitDate BETWEEN '03/ 01/2005' AND '03/31/2005')
GROUP BY DataSource, NamePrep
If you want it dynamic, you have to write code to dynamic construct an SQL
statement like the one above, based on the date ranges you pass it, and then
execute that SQL Statement using EXECUTE, or sp_ExecuteSQL() functions
"John Rugo" wrote:

> Hi All,
> I'm trying to figure out and learn how to do the following:
> /*I have the following query which works:*/
> SELECT DataSource AS [Service Line], NamePrep AS [PO Created By],
> COUNT(PoNumber) AS [Total Of PONumber]
> FROM OPW
> WHERE (ReqSubmitDate BETWEEN '03/ 01/2005' AND '03/31/2005')
> GROUP BY DataSource, NamePrep
> My question here is how can I get a wly breakout of the COUNT(PoNumber)
?
> I want a column for each w and the number of PONumbers Per Datasource a
nd
> NamePrep for that w.
> Example Output:
> Service Line | PO Created By | Total Of PONumber | 3/5/2005
|
> 3/12/2005 | ETC...
> MNS JOHN 10
> MNS ERIC 25
> FMS CARL 8
> Hope my question makes sense :)
> John.
>
>sql

Grouping by Time

Hello,
I am trying to create a query where I can group by the time of day something
happens.
For example, somebody (we don't care who) does something ( 'ev' below). We
capture the date and time this thing happens.
For analysis, a doctor wants to know what times the day these things are
happening. The grouping would be by the hour, counting the number of times
a specific thing happens.
I am not sure how to represent the hourly range. Maybe by a number ? For
example, 12:00 am to 1 am would be '1'. Not sure. I need some advice here.
CREATE TABLE [dbo].[YourTable] (
[dt] [datetime] NOT NULL ,
[ev] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO YourTable VALUES ('2004-02-10T09:00:00.000', 2)
INSERT INTO YourTable VALUES ('2004-02-10T10:00:00.000', 1)
INSERT INTO YourTable VALUES ('2004-02-10T09:30:00.000', 2)
INSERT INTO YourTable VALUES ('2004-02-10T11:00:00.000', 1)
INSERT INTO YourTable VALUES ('2004-02-10T11:05:00.000', 1)
Basically, the output should be
time range ev number of ev's in the time range.
I tried this query, but did not work.
SELECT ev, MIN(dt), COUNT(*)
FROM YourTable
GROUP BY ev, DATEDIFF(HH,'20000101',dt)
Thanks for your time.What about that ?
Select ev,DATEPART(hh,dt),count(*)
From YourTable
Group by ev,DATEPART(hh,dt)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Jack" <jack@.jack.net> schrieb im Newsbeitrag
news:Zasge.6007$Ay3.501@.lakeread06...
> Hello,
> I am trying to create a query where I can group by the time of day
> something happens.
> For example, somebody (we don't care who) does something ( 'ev' below).
> We capture the date and time this thing happens.
> For analysis, a doctor wants to know what times the day these things are
> happening. The grouping would be by the hour, counting the number of
> times a specific thing happens.
> I am not sure how to represent the hourly range. Maybe by a number ? For
> example, 12:00 am to 1 am would be '1'. Not sure. I need some advice
> here.
> CREATE TABLE [dbo].[YourTable] (
> [dt] [datetime] NOT NULL ,
> [ev] [int] NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO YourTable VALUES ('2004-02-10T09:00:00.000', 2)
> INSERT INTO YourTable VALUES ('2004-02-10T10:00:00.000', 1)
> INSERT INTO YourTable VALUES ('2004-02-10T09:30:00.000', 2)
> INSERT INTO YourTable VALUES ('2004-02-10T11:00:00.000', 1)
> INSERT INTO YourTable VALUES ('2004-02-10T11:05:00.000', 1)
> Basically, the output should be
> time range ev number of ev's in the time range.
> I tried this query, but did not work.
> SELECT ev, MIN(dt), COUNT(*)
> FROM YourTable
> GROUP BY ev, DATEDIFF(HH,'20000101',dt)
> Thanks for your time.
>|||Try,
use northwind
go
CREATE TABLE [dbo].[YourTable] (
[dt] [datetime] NOT NULL ,
[ev] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO YourTable VALUES ('2004-02-10T09:00:00.000', 2)
INSERT INTO YourTable VALUES ('2004-02-10T10:00:00.000', 1)
INSERT INTO YourTable VALUES ('2004-02-10T09:30:00.000', 2)
INSERT INTO YourTable VALUES ('2004-02-10T11:00:00.000', 1)
INSERT INTO YourTable VALUES ('2004-02-10T11:05:00.000', 1)
SELECT
ev,
MIN(dt),
COUNT(*)
FROM
YourTable
GROUP BY
ev,
convert(char(13), dt, 126)
drop table YourTable
AMB
"Jack" wrote:

> Hello,
> I am trying to create a query where I can group by the time of day somethi
ng
> happens.
> For example, somebody (we don't care who) does something ( 'ev' below). W
e
> capture the date and time this thing happens.
> For analysis, a doctor wants to know what times the day these things are
> happening. The grouping would be by the hour, counting the number of time
s
> a specific thing happens.
> I am not sure how to represent the hourly range. Maybe by a number ? For
> example, 12:00 am to 1 am would be '1'. Not sure. I need some advice here
.
> CREATE TABLE [dbo].[YourTable] (
> [dt] [datetime] NOT NULL ,
> [ev] [int] NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO YourTable VALUES ('2004-02-10T09:00:00.000', 2)
> INSERT INTO YourTable VALUES ('2004-02-10T10:00:00.000', 1)
> INSERT INTO YourTable VALUES ('2004-02-10T09:30:00.000', 2)
> INSERT INTO YourTable VALUES ('2004-02-10T11:00:00.000', 1)
> INSERT INTO YourTable VALUES ('2004-02-10T11:05:00.000', 1)
> Basically, the output should be
> time range ev number of ev's in the time range.
> I tried this query, but did not work.
> SELECT ev, MIN(dt), COUNT(*)
> FROM YourTable
> GROUP BY ev, DATEDIFF(HH,'20000101',dt)
> Thanks for your time.
>
>|||That works great. Thank you.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uOyubrlVFHA.628@.TK2MSFTNGP09.phx.gbl...
> What about that ?
> Select ev,DATEPART(hh,dt),count(*)
> From YourTable
> Group by ev,DATEPART(hh,dt)
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Jack" <jack@.jack.net> schrieb im Newsbeitrag
> news:Zasge.6007$Ay3.501@.lakeread06...
>

Grouping by N minute intervals

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

Grouping by hour, day, month, etc

I have tables which record data entered by six users.
I would like to creat a query which will return the number of entries
created by each user. The UserId is recorded for each record along with a
date stamp.
I would like to be able to group these results by hour, day, etc.Please post DDL, sample data, and sample output...
http://www.aspfaq.com/etiquette.asp?id=5006
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Richard Lawson" <nospam@.nospam.com> wrote in message
news:ubfWCsoAFHA.4044@.TK2MSFTNGP10.phx.gbl...
> I have tables which record data entered by six users.
> I would like to creat a query which will return the number of entries
> created by each user. The UserId is recorded for each record along with a
> date stamp.
> I would like to be able to group these results by hour, day, etc.
>|||SELECT Count(EntryKey), UserID, DatePart(hh,DateTimeStamp) as TheHour,
DatePart(dd,DateTimeStamp) as TheDay, DatePart(mm,DateTimeStamp) as
TheMonth, (yy, DateTimeStamp) as TheYear
FROM TheEntryTable
--WHERE UserID = 1
GROUP BY UserID, DatePart(hh,DateTimeStamp), DatePart(dd,DateTimeStamp),
DatePart(mm,DateTimeStamp), (yy, DateTimeStamp)
"Richard Lawson" <nospam@.nospam.com> wrote in message
news:ubfWCsoAFHA.4044@.TK2MSFTNGP10.phx.gbl...
> I have tables which record data entered by six users.
> I would like to creat a query which will return the number of entries
> created by each user. The UserId is recorded for each record along with a
> date stamp.
> I would like to be able to group these results by hour, day, etc.
>|||CREATE TABLE [ImagePointers] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[TrackablesId] [int] NULL CONSTRAINT [DF__Temporary__Track__22751F6C]
DEFAULT (0),
[TrackablesRecordVersion] [smallint] NULL CONSTRAINT
[DF__Temporary__Track__236943A5] DEFAULT (0),
[ScanDirectoriesId] [int] NULL CONSTRAINT [DF__Temporary__ScanD__245D67DE]
DEFAULT (0),
[ScanBatchesId] [int] NULL CONSTRAINT [DF__Temporary__ScanB__25518C17]
DEFAULT (0),
[ScanSequence] [int] NULL CONSTRAINT [DF__Temporary__ScanS__2645B050]
DEFAULT (0),
[FileName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ScanDateTime] [datetime] NULL ,
[PageNumber] [int] NULL CONSTRAINT [DF__Temporary__PageN__2739D489] DEFAULT
(0),
[CRC] [int] NULL CONSTRAINT [DF__TemporaryUp__CRC__282DF8C2] DEFAULT (0),
[Orientation] [smallint] NULL CONSTRAINT [DF__Temporary__Orien__29221CFB]
DEFAULT (0),
[Skew] [float] NULL CONSTRAINT [DF__TemporaryU__Skew__2A164134] DEFAULT
(0),
[Front] [bit] NOT NULL CONSTRAINT [DF__Temporary__Front__2B0A656D] DEFAULT
(0),
[ImageHeight] [smallint] NULL CONSTRAINT [DF__Temporary__Image__2BFE89A6]
DEFAULT (0),
[ImageWidth] [smallint] NULL CONSTRAINT [DF__Temporary__Image__2CF2ADDF]
DEFAULT (0),
[ImageSize] [int] NULL CONSTRAINT [DF__Temporary__Image__2DE6D218] DEFAULT
(0),
[BarCodeCount] [smallint] NULL CONSTRAINT [DF__Temporary__BarCo__2EDAF651]
DEFAULT (0),
[BarCodes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrgDirectoriesId] [int] NULL ,
[OrgFileName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[upsize_ts] [timestamp] NULL ,
[PageCount] [int] NULL ,
[OrgFullPath] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AddedToFTS] [tinyint] NULL CONSTRAINT [DF__ImagePoin__Added__44EA3301]
DEFAULT (0),
[AddedToOCR] [tinyint] NULL CONSTRAINT [DF__ImagePoin__Added__47C69FAC]
DEFAULT (0),
CONSTRAINT [ImagePointers_PK] PRIMARY KEY NONCLUSTERED
(
[Id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [ScanBatches] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[BatchStartDateTime] [datetime] NULL ,
[PageCount] [int] NULL CONSTRAINT [DF__Temporary__PageC__45544755] DEFAULT
(0),
[DocumentCount] [int] NULL CONSTRAINT [DF__Temporary__Docum__46486B8E]
DEFAULT (0),
[BelowDeleteSizeCount] [smallint] NULL CONSTRAINT
[DF__Temporary__Below__473C8FC7] DEFAULT (0),
[RescannedCount] [int] NULL CONSTRAINT [DF__Temporary__Resca__4830B400]
DEFAULT (0),
[AutoIndexedCount] [int] NULL CONSTRAINT [DF__Temporary__AutoI__4924D839]
DEFAULT (0),
[LastScanSequence] [int] NULL CONSTRAINT [DF__Temporary__LastS__4A18FC72]
DEFAULT (0),
[ScanRulesIdUsed] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[UserName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [ScanBatches_PK] PRIMARY KEY NONCLUSTERED
(
[Id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
Select ScanBatches.UserName,
ImagePointers.Scandatetime
from ImagePointers, scanbatches
where ImagePointers.ScanBatchesId = ScanBatches.Id
and filename like 'Y%' and Scandatetime > '2005-01-23' and Scandatetime <
'2005-01-25'
and UserName like 't%'
Order by ImagePointers.Scandatetime
tjones 2005-01-24 08:48:19.000
tjones 2005-01-24 08:50:35.000
tjones 2005-01-24 08:50:47.000
tjones 2005-01-24 08:50:56.000
tjones 2005-01-24 08:51:02.000
tjones 2005-01-24 08:51:04.000
tjones 2005-01-24 08:51:28.000
tjones 2005-01-24 08:51:35.000
Of course, what I would like to produce is the number of records produced by
any user for any unit of time like records per hour by each user. There are
currently six users.
Thanks
Rich
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ucX0yOpAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Please post DDL, sample data, and sample output...
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Richard Lawson" <nospam@.nospam.com> wrote in message
> news:ubfWCsoAFHA.4044@.TK2MSFTNGP10.phx.gbl...
a
>|||"Richard Lawson" <nospam@.nospam.com> wrote in message
news:eXWzSzpAFHA.1260@.TK2MSFTNGP12.phx.gbl...
> Of course, what I would like to produce is the number of records produced
by
> any user for any unit of time like records per hour by each user. There
are
> currently six users.
For a per-hour report, you could do something similar to David Buchanan's
solution:
Select ScanBatches.UserName,
CONVERT(CHAR(14), ImagePointers.Scandatetime, 120) + '00',
COUNT(*) AS Total
from ImagePointers, scanbatches
where ImagePointers.ScanBatchesId = ScanBatches.Id
and filename like 'Y%' and Scandatetime > '2005-01-23' and Scandatetime <
'2005-01-25'
and UserName like 't%'
GROUP BY ScanBatches.UserName,
CONVERT(CHAR(14), ImagePointers.Scandatetime, 120) + '00'
Order by ImagePointers.Scandatetime
You can change the CONVERT to get different granularities.
... That will show you only hours that actually have data. To see hours
that didn't have data, you should implement a calendar table of some sort.
Here's some basic reading on the topic:
http://www.aspfaq.com/show.asp?id=2519
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

Grouping by column alias

I'm trying to run a query and group by a calcuated column using its alias.
When I do, I get an error that says:
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'WeekEnding'.
Here is the SQL code. Can someone tell me what is wrong with this.
select completionType,
(case datepart(dw,dateCompleted)
When 2 then dateAdd(dd,4,datecompleted)
When 3 then dateAdd(dd,3,datecompleted)
When 4 then dateAdd(dd,2,datecompleted)
When 5 then dateAdd(dd,1,datecompleted)
When 6 then dateAdd(dd,0,datecompleted)
end) as WeekEnding
--count(*)
From tblWorkQueue
where datecompleted is not null
group by completiontype, WeekEnding
order by weekendingThis is a multi-part message in MIME format.
--=_NextPart_000_00FE_01C396EF.A792ED00
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
You cannot use an alias in that context. However, you can use a derived =table to do the same thing:
select
completionType,
WeekEnding,
count(*)
from
(select completionType,
(case datepart(dw,dateCompleted)
When 2 then dateAdd(dd,4,datecompleted)
When 3 then dateAdd(dd,3,datecompleted)
When 4 then dateAdd(dd,2,datecompleted)
When 5 then dateAdd(dd,1,datecompleted)
When 6 then dateAdd(dd,0,datecompleted)
end) as WeekEnding
From tblWorkQueue
where datecompleted is not null
) as x
group by completiontype, WeekEnding
order by weekending
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jeff Czyzewski" <jeff@.red5poductions.com_NOSPAM> wrote in message =news:umg0xBxlDHA.3700@.TK2MSFTNGP11.phx.gbl...
I'm trying to run a query and group by a calcuated column using its =alias.
When I do, I get an error that says:
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'WeekEnding'.
Here is the SQL code. Can someone tell me what is wrong with this.
select completionType,
(case datepart(dw,dateCompleted)
When 2 then dateAdd(dd,4,datecompleted)
When 3 then dateAdd(dd,3,datecompleted)
When 4 then dateAdd(dd,2,datecompleted)
When 5 then dateAdd(dd,1,datecompleted)
When 6 then dateAdd(dd,0,datecompleted)
end) as WeekEnding
--count(*)
From tblWorkQueue
where datecompleted is not null
group by completiontype, WeekEnding
order by weekending
--=_NextPart_000_00FE_01C396EF.A792ED00
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You cannot use an alias in that =context. However, you can use a derived table to do the same thing:
select
completionType, WeekEnding,
count(*)from
(select =completionType, (case datepart(dw,dateCompleted) When 2 then dateAdd(dd,4,datecompleted) When 3 then dateAdd(dd,3,datecompleted) When 4 then dateAdd(dd,2,datecompleted) When 5 then dateAdd(dd,1,datecompleted) When 6 then dateAdd(dd,0,datecompleted) end) as WeekEndingFrom tblWorkQueuewhere datecompleted is not null) as =x
group by completiontype, WeekEndingorder by weekending
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jeff Czyzewski" wrote in message news:umg0xBxlDHA.3700=@.TK2MSFTNGP11.phx.gbl...I'm trying to run a query and group by a calcuated column using its =alias.When I do, I get an error that says:Server: Msg 207, Level 16, State 3, =Line 2Invalid column name 'WeekEnding'.Here is the SQL code. =Can someone tell me what is wrong with this.select completionType, (case datepart(dw,dateCompleted) When =2 then dateAdd(dd,4,datecompleted) When 3 then dateAdd(dd,3,datecompleted) When 4 then dateAdd(dd,2,datecompleted) When 5 then dateAdd(dd,1,datecompleted) When 6 then dateAdd(dd,0,datecompleted) end) as WeekEnding --count(*)From tblWorkQueuewhere =datecompleted is not nullgroup by completiontype, WeekEndingorder by weekending

---

--=_NextPart_000_00FE_01C396EF.A792ED00--|||Jeff
Make a derived table
select completionType,WeekEnding
from
(
select completionType,
(case datepart(dw,dateCompleted)
When 2 then dateAdd(dd,4,datecompleted)
When 3 then dateAdd(dd,3,datecompleted)
When 4 then dateAdd(dd,2,datecompleted)
When 5 then dateAdd(dd,1,datecompleted)
When 6 then dateAdd(dd,0,datecompleted)
end) as WeekEnding
From tblWorkQueue
where datecompleted is not null
) as x
group by completionType,WeekEnding
--order by weekending
"Jeff Czyzewski" <jeff@.red5poductions.com_NOSPAM> wrote in message
news:umg0xBxlDHA.3700@.TK2MSFTNGP11.phx.gbl...
> I'm trying to run a query and group by a calcuated column using its alias.
> When I do, I get an error that says:
> Server: Msg 207, Level 16, State 3, Line 2
> Invalid column name 'WeekEnding'.
>
> Here is the SQL code. Can someone tell me what is wrong with this.
> select completionType,
> (case datepart(dw,dateCompleted)
> When 2 then dateAdd(dd,4,datecompleted)
> When 3 then dateAdd(dd,3,datecompleted)
> When 4 then dateAdd(dd,2,datecompleted)
> When 5 then dateAdd(dd,1,datecompleted)
> When 6 then dateAdd(dd,0,datecompleted)
> end) as WeekEnding
> --count(*)
> From tblWorkQueue
> where datecompleted is not null
> group by completiontype, WeekEnding
> order by weekending
>

Grouping a query in 30 seconds

Hi,

How can I make a query and group the registries in a interval of 30 seconds...like

for each line I have a datetime field that have all the day, and I need it to return just like

TIME Contador_type1 Contador_type2 Total

01-01-2006 00:00:30.000 2 5 7

01-01-2006 00:01:00.000 3 7 10

It's just an example...but that's the result that I need and my table is

data_hora -- datetime field

tipo - 1 or 2 -- count

nrtelefone - that's is the number dialed.

Thanks

Hi there and welcome to the groups,

see if that one helps:

As you didn��t specified your logic in your request (if it should be rounded up or down if its perhaps 00:15 (00:00 vs. 00:30)), you possible have to tweak the case branch.

DROP TABLE SomeTable

GO

CREATE TABLE SomeTable

(

SomeColumn datetime,

SomeValue int

)

INSERT INTO SomeTable

VALUES(GETDATE(),1)

INSERT INTO SomeTable

VALUES(DATEADD(mi,30,GETDATE()),1)

SELECT SUm(SomeValue),

DATEADD(mi,Minutes,DATEADD(hh,hours,Date))

FROM

(

SELECT

CONVERT(VARCHAR(10),SOmeColumn,112) AS Date ,

SomeValue,

DATEPART(hh,SomeColumn) Hours,

(CASE WHEN DATEPART(mi,SomeColumn) <30 THEN 30 ELSE 0 END) Minutes

From SomeTable

) SUbQUery

GROUP BY DATEADD(mi,Minutes,DATEADD(hh,hours,Date))

Drop Table SomeTable

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Use a similar solution to that suggested by sussemeyer above, but use integer division of 30 (rounds result to nearest whole number) with the seconds, as this would be more efficient, and would do away with the CASE statement, which is computationally costly.

HTH For more SQL tips, check out my blog below:

|||

Hi,

I tried that and didn't worked, I need to count how many rows are, in a period of 30 seconds.

Thanks for helping almost there...

|||

As I the code above didn't work can you give me an example ?

Thanks

|||

Hi,

OK you didn��t mention that you just wanted to have the count, then just replace the SUM() with a COUNT(*) and you��ll be fine.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

OK,

But in the case of 30 seconds, it don't work ?

I tried to change...but it increase the minutes wrongly..

Thanks

|||Coudl you please post some sample data ?

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Does this help?

Select Floor(Convert(Float, data_hora) * 24 * 60 *2) As TimeChunk, count(*)

From myTable

Group By Floor(Convert(Float, data_hora) * 24 * 60 *2);

|||

Here it is..with the columns

COD_CLIENTE,DATA_HORA,NRTELEFONE,RAMAL,TEMPO_SEGUNDOS,TIPO,TEMPO_ATENDIMENTO,JAPROCESSADO VALOR,VALOR_CONC,VALOR_TARIFA,VALOR_TARIFA_CONC,CLASSIFICA,LOCALIDADE,VALOR_TEMPO,NUMERO_E1, BLOQUEADO,TABELA_TELEFONICA
149,2006-03-01 09:48:26.000,0800784403,6935,1.0,2,0,NULL,NULL,NULL,NULL,NULL,ESP,0800 NACIONAL,.5000,01132816900,1,1
149,2006-03-01 09:44:16.000,01144145754,6922,324.0,2,0,NULL,.6600,.7536,.1100,.1256,CONUR,ATIBAIA - SP,6.0000,01132816900,1,1
149,2006-03-01 09:49:25.000,01137417505,6935,108.0,2,0,NULL,.1700,.2400,.0850,.1200,LOCAL,SAO PAULO - SP,2.0000,01132816900,1,1
149,2006-03-01 09:53:59.000,01159258359,6941,103.0,2,0,NULL,.1700,.2400,.0850,.1200,LOCAL,SAO PAULO - SP,2.0000,01132816900,1,1
149,2006-03-01 09:57:58.000,01332995733,6922,228.0,2,0,NULL,1.0440,1.9800,.2900,.5500,DDD,SANTOS - SP,3.6000,01132816900,1,1
149,2006-03-01 10:01:20.000,01184262728,6923,76.0,2,0,NULL,.5500,.6710,.5000,.6100,VC1,AREA 011 - CELULAR - SP,1.1000,01132816900,1,1
149,2006-03-01 09:55:32.000,01121874200,6932,457.0,2,0,NULL,.6800,.9600,.0850,.1200,LOCAL,SAO PAULO - SP,8.0000,01132816900,1,1
149,2006-03-01 10:02:24.000,05534125161,6937,104.0,2,0,NULL,.6720,.8800,.4200,.5500,DDD,SANTA MARIA - RS,1.6000,01132816900,1,1
149,2006-03-01 10:05:05.000,01121617500,6935,67.0,2,0,NULL,.0850,.1200,.0850,.1200,LOCAL,SAO PAULO - SP,1.0000,01132816900,1,1
149,2006-03-01 10:04:41.000,01934627164,6937,136.0,2,0,NULL,.8820,1.1550,.4200,.5500,DDD,AMERICANA - SP,2.1000,01132816900,1,1
149,2006-03-01 10:11:05.000,01934582333,6936,46.0,2,0,NULL,.4200,.5500,.4200,.5500,DDD,SANTA BARBARA D OESTE - SP,1.0000,01132816900,1,1
149,2006-03-01 10:12:14.000,01121617525,6935,9.0,2,0,NULL,.0000,.0000,.0850,.1200,LOCAL,SAO PAULO - SP,.0000,01132816900,1,1
149,2006-03-01 10:08:37.000,01161035061,6913,255.0,2,0,NULL,.4250,.6000,.0850,.1200,LOCAL,SAO PAULO - SP,5.0000,01132816900,1,1
149,2006-03-01 10:11:35.000,01434546110,6941,263.0,2,0,NULL,1.7640,2.3100,.4200,.5500,DDD,BAURU - SP,4.2000,01132816900,1,1
149,2006-03-01 10:18:37.000,01934063393,6936,87.0,2,0,NULL,.5460,.7150,.4200,.5500,DDD,AMERICANA - SP,1.3000,01132816900,1,1
149,2006-03-01 10:26:12.000,01121617525,6935,11.0,2,0,NULL,.0000,.0000,.0850,.1200,LOCAL,SAO PAULO - SP,.0000,01132816900,1,1
149,2006-03-01 10:25:25.000,01132536576,6920,78.0,2,0,NULL,.1700,.2400,.0850,.1200,LOCAL,SAO PAULO - SP,2.0000,01132816900,1,1
149,2006-03-01 10:23:20.000,01162222734,6904,244.0,2,0,NULL,.3400,.4800,.0850,.1200,LOCAL,SAO PAULO - SP,4.0000,01132816900,1,1
149,2006-03-01 10:29:40.000,01991638344,6924,87.0,2,0,NULL,1.4820,1.5600,1.1400,1.2000,VC2,AREA 019 - CELULAR - SP,1.3000,01132816900,1,1
149,2006-03-01 10:33:54.000,011102,6926,70.0,2,0,NULL,.0850,.1200,.0850,.1200,LOCAL,SAO PAULO - SP,1.0000,01132816900,1,1
149,2006-03-01 10:35:22.000,01934692606,6926,110.0,2,0,NULL,.7140,.9350,.4200,.5500,DDD,AMERICANA - SP,1.7000,01132816900,1,1
149,2006-03-01 10:41:11.000,01161280656,6924,28.0,2,0,NULL,.0850,.1200,.0850,.1200,LOCAL,SAO PAULO - SP,1.0000,01132816900,1,1
149,2006-03-01 10:38:32.000,01381340603,6921,257.0,2,0,NULL,4.6740,4.9200,1.1400,1.2000,VC2,AREA 013 - CELULAR - SP,4.1000,01132816900,1,1
149,2006-03-01 10:50:16.000,01121617500,6935,73.0,2,0,NULL,.1700,.2400,.0850,.1200,LOCAL,SAO PAULO - SP,2.0000,01132816900,1,1
149,2006-03-01 10:50:46.000,08007728486,6923,186.0,2,0,NULL,NULL,NULL,NULL,NULL,ESP,0800 NACIONAL,2.9000,01132816900,1,1
149,2006-03-01 10:54:16.000,01169574019,6923,124.0,2,0,NULL,.1700,.2400,.0850,.1200,LOCAL,SAO PAULO - SP,2.0000,01132816900,1,1
149,2006-03-01 10:57:38.000,01155102193,6915,87.0,2,0,NULL,.1700,.2400,.0850,.1200,LOCAL,SAO PAULO - SP,2.0000,01132816900,1,1
149,2006-03-01 10:58:53.000,01171484387,6947,47.0,2,0,NULL,.3000,.3660,.5000,.6100,VC1,AREA 011 - CELULAR - SP,.6000,01132816900,1,1
149,2006-03-01 11:00:36.000,01155922414,6935,11.0,2,0,NULL,.0000,.0000,.0850,.1200,LOCAL,SAO PAULO - SP,.0000,01132816900,1,1
149,2006-03-01 10:59:43.000,01171484387,6925,104.0,2,0,NULL,.8000,.9760,.5000,.6100,VC1,AREA 011 - CELULAR - SP,1.6000,01132816900,1,1

And what I need to return is something like(ex not using the above)

DATA_HORA TOTAL

01/03/2006 00:00:30.000 5

01/03/2006 00:01:00.000 2

01/03/2006 00:01:30.000 6

Thanks

|||It's almost that...but I need the role day time from 01/03/2006 00:00:00.000 to 01/03/2006 23:59:59.997 for example|||

How about this then:

Select Convert(date, Floor(Convert(Float, data_hora) * 24 * 60 *2)/2880.0) As TimeChunk, count(*)

From myTable

Group By Convert(date, Floor(Convert(Float, data_hora) * 24 * 60 *2)/2880.0);

|||

OK, this works, but it does not group by an interval of 30 seconds, it's just put together all registries that has the same time....

Did you get the idea or not ? Like I did something(based on your idea) like a while the increase the time from 01/01/2006 00:00:00.000 to 02/01/2006 23:59:30.000 just using dateadd(ss,30,@.date) but the problem is....to fill the total field I need to do a select that counts and it takes a long time to complete...

I'm asking if there is a faster way...

|||

I am not sure what you are after if my last SQL statement does not produce the result you are looking for. The statement I gave you will group all records together that fall withing the 30 intervals. If it does not then I did something wrong.

|||

Hum...ok..I'll check again...but do you Know webchart ? I'm using this query to build a smoothlinechart, but it's getting an strange format..

Do you think that I need to pass the whole interval, or you think that the values that is missing the graph will automatically put 0 ?

Thanks anyway, it worked with Select Floor(Convert(Float, data_hora) * 24 * 60 *2) As TEMPO, count(*) AS TOTAL From pabx WHERE cod_cliente = 221 AND data_hora BETWEEN '20060201' AND '20060203' Group By Floor(Convert(Float, data_hora) * 24 * 60 *2) ORDER BY Floor(Convert(Float, data_hora) * 24 * 60 *2)

sql

grouping a few columns

/*
Hi
I need to query this table to get results where ids are found with every
searchNum, i.e. the results of this would be:
id
1
2
because both id 1 and 2 are found with searchNum 1,2,3. The table could be
any size with any variation of ids and searchNum so I need some sort of
general grouping query. Hope this makes sence. I've been bashing my head
against the wall all day.
thanks Andrew
*/
declare @.table table (searchNum int, word varchar(50), id int)
insert into @.table values (1, 'cambridge', 1)
insert into @.table values (1, 'northampton', 2)
insert into @.table values (1, 'hull', 4)
insert into @.table values (2, 'laboratory', 1)
insert into @.table values (2, 'chemistry', 2)
insert into @.table values (2, 'chemistry', 5)
insert into @.table values (2, 'laboratory', 2)
insert into @.table values (2, 'laboratory', 4)
insert into @.table values (3, 'scientist', 1)
insert into @.table values (3, 'scientist', 2)
select * from @.tableJ055 wrote:
> /*
> Hi
> I need to query this table to get results where ids are found with every
> searchNum, i.e. the results of this would be:
> id
> --
> 1
> 2
> because both id 1 and 2 are found with searchNum 1,2,3. The table could be
> any size with any variation of ids and searchNum so I need some sort of
> general grouping query. Hope this makes sence. I've been bashing my head
> against the wall all day.
> thanks Andrew
>
Thanks for posting the DDL and sample data. Please do also include keys
and constraints with your DDL. It can make a big difference to the
solution. Here's one suggestion:
SELECT id
FROM @.table
GROUP BY id
HAVING COUNT(DISTINCT searchnum)=
(SELECT COUNT(DISTINCT searchnum)
FROM @.table);
If searchnum is a foreign key you could also reference the other table:
SELECT id
FROM @.table
GROUP BY id
HAVING COUNT(DISTINCT searchnum)=
(SELECT COUNT(*)
FROM search);
David Portas
SQL Server MVP
--|||Try this:
SELECT [id] FROM
(
SELECT id, COUNT(*) AS NofRecs
FROM (SELECT DISTINCT searchNum, [id] FROM @.table) AS inn
GROUP BY [ID]
HAVING COUNT(*) IN
(
SELECT COUNT( DISTINCT searchNum ) FROM @.table
)
) AS cnt
"J055" wrote:
> /*
> Hi
> I need to query this table to get results where ids are found with every
> searchNum, i.e. the results of this would be:
> id
> --
> 1
> 2
> because both id 1 and 2 are found with searchNum 1,2,3. The table could be
> any size with any variation of ids and searchNum so I need some sort of
> general grouping query. Hope this makes sence. I've been bashing my head
> against the wall all day.
> thanks Andrew
> */
> declare @.table table (searchNum int, word varchar(50), id int)
> insert into @.table values (1, 'cambridge', 1)
> insert into @.table values (1, 'northampton', 2)
> insert into @.table values (1, 'hull', 4)
> insert into @.table values (2, 'laboratory', 1)
> insert into @.table values (2, 'chemistry', 2)
> insert into @.table values (2, 'chemistry', 5)
> insert into @.table values (2, 'laboratory', 2)
> insert into @.table values (2, 'laboratory', 4)
> insert into @.table values (3, 'scientist', 1)
> insert into @.table values (3, 'scientist', 2)
> select * from @.table
>
>
>|||This is division, the usual approach is:
SELECT id
FROM ( SELECT id, COUNT( DISTINCT searchnum)
FROM tbl
GROUP BY id ) D ( id, num )
WHERE ( SELECT COUNT(DISTINCT searchnum)
FROM tbl ) = num ;
Anith

grouping / count question

I am trying to obtain a sum of the various sequences from the following
table. I was thinking I could do some sort of select sum( <union query
here>) but that's not the case.
so for the following sample I would like to count the number of times the
values 1 & 2 occur in the same testsets.
Thanks
create table test (testset int, testnumber int, value int )
insert into test values(1,1,1)
insert into test values(1,2,2)
insert into test values(1,3,3)
insert into test values(1,4,4)
insert into test values(1,5,5)
insert into test values(2,1,1)
insert into test values(2,2,2)
insert into test values(2,3,7)
insert into test values(2,4,8)
insert into test values(2,5,9)
insert into test values(3,1,2)
insert into test values(3,2,3)
insert into test values(3,3,6)
insert into test values(3,4,7)
insert into test values(3,5,8)
--select testset from test where value = 1 and value = 2 group by testset
select testset as t from test where testnumber = 1 and value = 1
union
select testset as t from test where testnumber = 2 and value = 2
drop table testSELECT testset
FROM test
WHERE testnumber = value
AND testnumber IN (1,2)
--
Jacco Schalkwijk
SQL Server MVP
"D" <Dave@.nothing.net> wrote in message
news:%23KJBL3bRFHA.244@.TK2MSFTNGP12.phx.gbl...
>I am trying to obtain a sum of the various sequences from the following
>table. I was thinking I could do some sort of select sum( <union query
>here>) but that's not the case.
> so for the following sample I would like to count the number of times the
> values 1 & 2 occur in the same testsets.
> Thanks
> create table test (testset int, testnumber int, value int )
> insert into test values(1,1,1)
> insert into test values(1,2,2)
> insert into test values(1,3,3)
> insert into test values(1,4,4)
> insert into test values(1,5,5)
> insert into test values(2,1,1)
> insert into test values(2,2,2)
> insert into test values(2,3,7)
> insert into test values(2,4,8)
> insert into test values(2,5,9)
> insert into test values(3,1,2)
> insert into test values(3,2,3)
> insert into test values(3,3,6)
> insert into test values(3,4,7)
> insert into test values(3,5,8)
> --select testset from test where value = 1 and value = 2 group by testset
> select testset as t from test where testnumber = 1 and value = 1
> union
> select testset as t from test where testnumber = 2 and value = 2
> drop table test
>|||Thank you but I am trying to sum the total number of times that 1 and 2 have
come out. Optimally I'd like to be able to sum any number of combinations
from within the same testset. For example how many times has the sequence
1,2 and 7 come out? The ultimate would be something that told me the top
sequence is 1,2 & 7 with 55 occurrences.
Thanks
Regards
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:OPNWKFcRFHA.1176@.TK2MSFTNGP12.phx.gbl...
> SELECT testset
> FROM test
> WHERE testnumber = value
> AND testnumber IN (1,2)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "D" <Dave@.nothing.net> wrote in message
> news:%23KJBL3bRFHA.244@.TK2MSFTNGP12.phx.gbl...
>>I am trying to obtain a sum of the various sequences from the following
>>table. I was thinking I could do some sort of select sum( <union query
>>here>) but that's not the case.
>> so for the following sample I would like to count the number of times the
>> values 1 & 2 occur in the same testsets.
>> Thanks
>> create table test (testset int, testnumber int, value int )
>> insert into test values(1,1,1)
>> insert into test values(1,2,2)
>> insert into test values(1,3,3)
>> insert into test values(1,4,4)
>> insert into test values(1,5,5)
>> insert into test values(2,1,1)
>> insert into test values(2,2,2)
>> insert into test values(2,3,7)
>> insert into test values(2,4,8)
>> insert into test values(2,5,9)
>> insert into test values(3,1,2)
>> insert into test values(3,2,3)
>> insert into test values(3,3,6)
>> insert into test values(3,4,7)
>> insert into test values(3,5,8)
>> --select testset from test where value = 1 and value = 2 group by testset
>> select testset as t from test where testnumber = 1 and value = 1
>> union
>> select testset as t from test where testnumber = 2 and value = 2
>> drop table test
>|||Can you give the expected results for your questions with the sample data
you have given (Add more data if necessary)? Also, what is the Primary Key
on the table 'test'?
--
Jacco Schalkwijk
SQL Server MVP
"D" <Dave@.nothing.net> wrote in message
news:uuMUNjcRFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Thank you but I am trying to sum the total number of times that 1 and 2
> have come out. Optimally I'd like to be able to sum any number of
> combinations from within the same testset. For example how many times has
> the sequence 1,2 and 7 come out? The ultimate would be something that told
> me the top sequence is 1,2 & 7 with 55 occurrences.
> Thanks
> Regards
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:OPNWKFcRFHA.1176@.TK2MSFTNGP12.phx.gbl...
>> SELECT testset
>> FROM test
>> WHERE testnumber = value
>> AND testnumber IN (1,2)
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "D" <Dave@.nothing.net> wrote in message
>> news:%23KJBL3bRFHA.244@.TK2MSFTNGP12.phx.gbl...
>>I am trying to obtain a sum of the various sequences from the following
>>table. I was thinking I could do some sort of select sum( <union query
>>here>) but that's not the case.
>> so for the following sample I would like to count the number of times
>> the values 1 & 2 occur in the same testsets.
>> Thanks
>> create table test (testset int, testnumber int, value int )
>> insert into test values(1,1,1)
>> insert into test values(1,2,2)
>> insert into test values(1,3,3)
>> insert into test values(1,4,4)
>> insert into test values(1,5,5)
>> insert into test values(2,1,1)
>> insert into test values(2,2,2)
>> insert into test values(2,3,7)
>> insert into test values(2,4,8)
>> insert into test values(2,5,9)
>> insert into test values(3,1,2)
>> insert into test values(3,2,3)
>> insert into test values(3,3,6)
>> insert into test values(3,4,7)
>> insert into test values(3,5,8)
>> --select testset from test where value = 1 and value = 2 group by
>> testset
>> select testset as t from test where testnumber = 1 and value = 1
>> union
>> select testset as t from test where testnumber = 2 and value = 2
>> drop table test
>>
>|||for the sample results if I were searching for the total of the number of
occurences of 1,2 it would be 2, one in testset 1 and one in test set 2.
For the primary key I have testset and testnumber
Thanks for your help today
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:edDfhucRFHA.648@.TK2MSFTNGP14.phx.gbl...
> Can you give the expected results for your questions with the sample data
> you have given (Add more data if necessary)? Also, what is the Primary Key
> on the table 'test'?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "D" <Dave@.nothing.net> wrote in message
> news:uuMUNjcRFHA.2964@.TK2MSFTNGP15.phx.gbl...
>> Thank you but I am trying to sum the total number of times that 1 and 2
>> have come out. Optimally I'd like to be able to sum any number of
>> combinations from within the same testset. For example how many times has
>> the sequence 1,2 and 7 come out? The ultimate would be something that
>> told me the top sequence is 1,2 & 7 with 55 occurrences.
>> Thanks
>> Regards
>>
>> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
>> wrote in message news:OPNWKFcRFHA.1176@.TK2MSFTNGP12.phx.gbl...
>> SELECT testset
>> FROM test
>> WHERE testnumber = value
>> AND testnumber IN (1,2)
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "D" <Dave@.nothing.net> wrote in message
>> news:%23KJBL3bRFHA.244@.TK2MSFTNGP12.phx.gbl...
>>I am trying to obtain a sum of the various sequences from the following
>>table. I was thinking I could do some sort of select sum( <union query
>>here>) but that's not the case.
>> so for the following sample I would like to count the number of times
>> the values 1 & 2 occur in the same testsets.
>> Thanks
>> create table test (testset int, testnumber int, value int )
>> insert into test values(1,1,1)
>> insert into test values(1,2,2)
>> insert into test values(1,3,3)
>> insert into test values(1,4,4)
>> insert into test values(1,5,5)
>> insert into test values(2,1,1)
>> insert into test values(2,2,2)
>> insert into test values(2,3,7)
>> insert into test values(2,4,8)
>> insert into test values(2,5,9)
>> insert into test values(3,1,2)
>> insert into test values(3,2,3)
>> insert into test values(3,3,6)
>> insert into test values(3,4,7)
>> insert into test values(3,5,8)
>> --select testset from test where value = 1 and value = 2 group by
>> testset
>> select testset as t from test where testnumber = 1 and value = 1
>> union
>> select testset as t from test where testnumber = 2 and value = 2
>> drop table test
>>
>>
>|||Please provide the complete expected resultset, with column names and
values. That will make it a lot clearer for me than a textual description.
Also, what will the resultset be if you add the row insert into test
values(3,1,1) to the test data?
--
Jacco Schalkwijk
SQL Server MVP
"D" <Dave@.nothing.net> wrote in message
news:%23cNBW1cRFHA.3140@.tk2msftngp13.phx.gbl...
> for the sample results if I were searching for the total of the number of
> occurences of 1,2 it would be 2, one in testset 1 and one in test set 2.
> For the primary key I have testset and testnumber
> Thanks for your help today
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:edDfhucRFHA.648@.TK2MSFTNGP14.phx.gbl...
>> Can you give the expected results for your questions with the sample data
>> you have given (Add more data if necessary)? Also, what is the Primary
>> Key on the table 'test'?
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "D" <Dave@.nothing.net> wrote in message
>> news:uuMUNjcRFHA.2964@.TK2MSFTNGP15.phx.gbl...
>> Thank you but I am trying to sum the total number of times that 1 and 2
>> have come out. Optimally I'd like to be able to sum any number of
>> combinations from within the same testset. For example how many times
>> has the sequence 1,2 and 7 come out? The ultimate would be something
>> that told me the top sequence is 1,2 & 7 with 55 occurrences.
>> Thanks
>> Regards
>>
>> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
>> wrote in message news:OPNWKFcRFHA.1176@.TK2MSFTNGP12.phx.gbl...
>> SELECT testset
>> FROM test
>> WHERE testnumber = value
>> AND testnumber IN (1,2)
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "D" <Dave@.nothing.net> wrote in message
>> news:%23KJBL3bRFHA.244@.TK2MSFTNGP12.phx.gbl...
>>I am trying to obtain a sum of the various sequences from the following
>>table. I was thinking I could do some sort of select sum( <union query
>>here>) but that's not the case.
>> so for the following sample I would like to count the number of times
>> the values 1 & 2 occur in the same testsets.
>> Thanks
>> create table test (testset int, testnumber int, value int )
>> insert into test values(1,1,1)
>> insert into test values(1,2,2)
>> insert into test values(1,3,3)
>> insert into test values(1,4,4)
>> insert into test values(1,5,5)
>> insert into test values(2,1,1)
>> insert into test values(2,2,2)
>> insert into test values(2,3,7)
>> insert into test values(2,4,8)
>> insert into test values(2,5,9)
>> insert into test values(3,1,2)
>> insert into test values(3,2,3)
>> insert into test values(3,3,6)
>> insert into test values(3,4,7)
>> insert into test values(3,5,8)
>> --select testset from test where value = 1 and value = 2 group by
>> testset
>> select testset as t from test where testnumber = 1 and value = 1
>> union
>> select testset as t from test where testnumber = 2 and value = 2
>> drop table test
>>
>>
>>
>|||after adding the 3,1,1 set the data looks like '
>> insert into test values(1,1,1)
>> insert into test values(1,2,2)
>> insert into test values(1,3,3)
>> insert into test values(1,4,4)
>> insert into test values(1,5,5)
>> insert into test values(2,1,1)
>> insert into test values(2,2,2)
>> insert into test values(2,3,4)
>> insert into test values(2,4,5)
>> insert into test values(2,5,9)
>> insert into test values(3,1,1)
>> insert into test values(3,1,2)
>> insert into test values(3,2,3)
>> insert into test values(3,3,4)
>> insert into test values(3,4,7)
if I could get a result set that looked like
x y total
1 2 3
3 4 2
4 5 2
I started with sets of 2 and figured once I have that I can expand it to 3
or 4 if needed to so that the results would look like
x y z total
1 2 3 2
2 3 4 2
If I could get that, that would be most excellent. Basically I'm trying to
determine what patterns (grouped by testsets) occur the most. And the value
never repeats within a testset.
Thanks alot.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:eUCV39cRFHA.252@.TK2MSFTNGP12.phx.gbl...
> Please provide the complete expected resultset, with column names and
> values. That will make it a lot clearer for me than a textual description.
> Also, what will the resultset be if you add the row insert into test
> values(3,1,1) to the test data?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "D" <Dave@.nothing.net> wrote in message
> news:%23cNBW1cRFHA.3140@.tk2msftngp13.phx.gbl...
>> for the sample results if I were searching for the total of the number of
>> occurences of 1,2 it would be 2, one in testset 1 and one in test set 2.
>> For the primary key I have testset and testnumber
>> Thanks for your help today
>>
>> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
>> wrote in message news:edDfhucRFHA.648@.TK2MSFTNGP14.phx.gbl...
>> Can you give the expected results for your questions with the sample
>> data you have given (Add more data if necessary)? Also, what is the
>> Primary Key on the table 'test'?
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "D" <Dave@.nothing.net> wrote in message
>> news:uuMUNjcRFHA.2964@.TK2MSFTNGP15.phx.gbl...
>> Thank you but I am trying to sum the total number of times that 1 and 2
>> have come out. Optimally I'd like to be able to sum any number of
>> combinations from within the same testset. For example how many times
>> has the sequence 1,2 and 7 come out? The ultimate would be something
>> that told me the top sequence is 1,2 & 7 with 55 occurrences.
>> Thanks
>> Regards
>>
>> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
>> wrote in message news:OPNWKFcRFHA.1176@.TK2MSFTNGP12.phx.gbl...
>> SELECT testset
>> FROM test
>> WHERE testnumber = value
>> AND testnumber IN (1,2)
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "D" <Dave@.nothing.net> wrote in message
>> news:%23KJBL3bRFHA.244@.TK2MSFTNGP12.phx.gbl...
>>I am trying to obtain a sum of the various sequences from the
>>following table. I was thinking I could do some sort of select sum(
>><union query here>) but that's not the case.
>> so for the following sample I would like to count the number of times
>> the values 1 & 2 occur in the same testsets.
>> Thanks
>> create table test (testset int, testnumber int, value int )
>> insert into test values(1,1,1)
>> insert into test values(1,2,2)
>> insert into test values(1,3,3)
>> insert into test values(1,4,4)
>> insert into test values(1,5,5)
>> insert into test values(2,1,1)
>> insert into test values(2,2,2)
>> insert into test values(2,3,7)
>> insert into test values(2,4,8)
>> insert into test values(2,5,9)
>> insert into test values(3,1,2)
>> insert into test values(3,2,3)
>> insert into test values(3,3,6)
>> insert into test values(3,4,7)
>> insert into test values(3,5,8)
>> --select testset from test where value = 1 and value = 2 group by
>> testset
>> select testset as t from test where testnumber = 1 and value = 1
>> union
>> select testset as t from test where testnumber = 2 and value = 2
>> drop table test
>>
>>
>>
>>
>|||On Wed, 20 Apr 2005 23:50:35 -0400, D wrote:
>after adding the 3,1,1 set the data looks like '
>>> insert into test values(1,1,1)
>>> insert into test values(1,2,2)
>>> insert into test values(1,3,3)
>>> insert into test values(1,4,4)
>>> insert into test values(1,5,5)
>>>
>>> insert into test values(2,1,1)
>>> insert into test values(2,2,2)
>>> insert into test values(2,3,4)
>>> insert into test values(2,4,5)
>>> insert into test values(2,5,9)
>>> insert into test values(3,1,1)
>>> insert into test values(3,1,2)
>>> insert into test values(3,2,3)
>>> insert into test values(3,3,4)
>>> insert into test values(3,4,7)
>if I could get a result set that looked like
>x y total
>1 2 3
>3 4 2
>4 5 2
>I started with sets of 2 and figured once I have that I can expand it to 3
>or 4 if needed to so that the results would look like
>x y z total
>1 2 3 2
>2 3 4 2
>If I could get that, that would be most excellent. Basically I'm trying to
>determine what patterns (grouped by testsets) occur the most. And the value
>never repeats within a testset.
Hi D,
If I understand you correctly, you need a technique known as relational
division. Try if the following code helps:
create table test (testset int, testnumber int, value int,
primary key(testset, testnumber),
unique(testset, value))
insert into test values(1,1,1)
insert into test values(1,2,2)
insert into test values(1,3,3)
insert into test values(1,4,4)
insert into test values(1,5,5)
insert into test values(2,1,1)
insert into test values(2,2,2)
insert into test values(2,3,7)
insert into test values(2,4,8)
insert into test values(2,5,9)
insert into test values(3,1,2)
insert into test values(3,2,3)
insert into test values(3,3,6)
insert into test values(3,4,7)
insert into test values(3,5,8)
go
create table wanted (value int not null primary key)
insert into wanted values (1)
insert into wanted values (2)
go
SELECT test.testset
FROM test
INNER JOIN wanted
ON test.value = wanted.value
GROUP BY test.testset
HAVING COUNT(*) = (SELECT COUNT(*) FROM wanted)
go
drop table wanted
drop table test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks. It does work but I'm wondering how I can make it more flexible in
terms of the wanted values. Let's say I have a couple hundred different
combinations of 3 numbers, I wonder how would I cycle through that.
Another way I just figured out is to use a self join
select distinct a.testset from test a, test b where a.testset = b.testset
and a.value = 1 and b.value = 2
Thanks for your help
> Hi D,
> If I understand you correctly, you need a technique known as relational
> division. Try if the following code helps:
> create table test (testset int, testnumber int, value int,
> primary key(testset, testnumber),
> unique(testset, value))
> insert into test values(1,1,1)
> insert into test values(1,2,2)
> insert into test values(1,3,3)
> insert into test values(1,4,4)
> insert into test values(1,5,5)
> insert into test values(2,1,1)
> insert into test values(2,2,2)
> insert into test values(2,3,7)
> insert into test values(2,4,8)
> insert into test values(2,5,9)
> insert into test values(3,1,2)
> insert into test values(3,2,3)
> insert into test values(3,3,6)
> insert into test values(3,4,7)
> insert into test values(3,5,8)
> go
> create table wanted (value int not null primary key)
> insert into wanted values (1)
> insert into wanted values (2)
> go
> SELECT test.testset
> FROM test
> INNER JOIN wanted
> ON test.value = wanted.value
> GROUP BY test.testset
> HAVING COUNT(*) = (SELECT COUNT(*) FROM wanted)
> go
> drop table wanted
> drop table test
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sun, 24 Apr 2005 07:29:09 -0400, D wrote:
>Thanks. It does work but I'm wondering how I can make it more flexible in
>terms of the wanted values. Let's say I have a couple hundred different
>combinations of 3 numbers, I wonder how would I cycle through that.
Hi D,
All the more reason to store the values you want to find in a table. Only,
you'll have to add another column, so you can store different combinations
at once. And you'll have to adapt the query, of course. See if the code
below helps.
create table test (testset int, testnumber int, value int,
primary key(testset, testnumber),
unique(testset, value))
insert into test values(1,1,1)
insert into test values(1,2,2)
insert into test values(1,3,3)
insert into test values(1,4,4)
insert into test values(1,5,5)
insert into test values(2,1,1)
insert into test values(2,2,2)
insert into test values(2,3,7)
insert into test values(2,4,8)
insert into test values(2,5,9)
insert into test values(3,1,2)
insert into test values(3,2,3)
insert into test values(3,3,6)
insert into test values(3,4,7)
insert into test values(3,5,8)
go
create table wanted (combination int not null,
value int not null,
primary key(combination, value))
insert into wanted (combination, value)
-- Testset 1: values 1 and 2
select 1, 1 union all
select 1, 2 union all
-- Testset 2: values 1 and 3
select 2, 1 union all
select 2, 3 union all
-- Testset 3: values 1, 2, and 3
select 3, 1 union all
select 3, 2 union all
select 3, 3
go
SELECT w.combination, t.testset
FROM test AS t
INNER JOIN wanted AS w
ON t.value = w.value
GROUP BY w.combination, t.testset
HAVING COUNT(*) = (SELECT COUNT(*)
FROM wanted AS w2
WHERE w2.combination = w.combination)
go
drop table wanted
drop table test
go
>Another way I just figured out is to use a self join
>select distinct a.testset from test a, test b where a.testset = b.testset
>and a.value = 1 and b.value = 2
Yeah, but the problem with that approach is that you have to increase the
number of joins as the number of values you want to find goes up. Imagine
what the query would look like if you have to find all testsets where all
of the values 1 through 10 are present - you'd have to join the same table
tenfold!!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||thanks ALOT!! This works really good.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:vv1n61pnl9tr20phih2mg4n0ps4ml16226@.4ax.com...
> On Sun, 24 Apr 2005 07:29:09 -0400, D wrote:
>>Thanks. It does work but I'm wondering how I can make it more flexible in
>>terms of the wanted values. Let's say I have a couple hundred different
>>combinations of 3 numbers, I wonder how would I cycle through that.
> Hi D,
> All the more reason to store the values you want to find in a table. Only,
> you'll have to add another column, so you can store different combinations
> at once. And you'll have to adapt the query, of course. See if the code
> below helps.
> create table test (testset int, testnumber int, value int,
> primary key(testset, testnumber),
> unique(testset, value))
> insert into test values(1,1,1)
> insert into test values(1,2,2)
> insert into test values(1,3,3)
> insert into test values(1,4,4)
> insert into test values(1,5,5)
> insert into test values(2,1,1)
> insert into test values(2,2,2)
> insert into test values(2,3,7)
> insert into test values(2,4,8)
> insert into test values(2,5,9)
> insert into test values(3,1,2)
> insert into test values(3,2,3)
> insert into test values(3,3,6)
> insert into test values(3,4,7)
> insert into test values(3,5,8)
> go
> create table wanted (combination int not null,
> value int not null,
> primary key(combination, value))
> insert into wanted (combination, value)
> -- Testset 1: values 1 and 2
> select 1, 1 union all
> select 1, 2 union all
> -- Testset 2: values 1 and 3
> select 2, 1 union all
> select 2, 3 union all
> -- Testset 3: values 1, 2, and 3
> select 3, 1 union all
> select 3, 2 union all
> select 3, 3
> go
> SELECT w.combination, t.testset
> FROM test AS t
> INNER JOIN wanted AS w
> ON t.value = w.value
> GROUP BY w.combination, t.testset
> HAVING COUNT(*) = (SELECT COUNT(*)
> FROM wanted AS w2
> WHERE w2.combination = w.combination)
> go
> drop table wanted
> drop table test
> go
>
>>Another way I just figured out is to use a self join
>>select distinct a.testset from test a, test b where a.testset = b.testset
>>and a.value = 1 and b.value = 2
> Yeah, but the problem with that approach is that you have to increase the
> number of joins as the number of values you want to find goes up. Imagine
> what the query would look like if you have to find all testsets where all
> of the values 1 through 10 are present - you'd have to join the same table
> tenfold!!
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)sql