Grouping with a full join

Hi,
I would like to know how to group the Amount of both tables while maintaing
all Ids.
-- Correct results for Table1
select
Table1.id
,sum(Table1.Amount) as AmountTable1
from Table1
group by Table1.id
order by 1
-- Correct results for Table2
select
Table2.id
,sum(Table2.Amount) as AmountTable2
from Table2
group by Table2.id
order by 1
-- How do I combine both results?
select
Table1.id
,sum(Table1.Amount) + sum(Table2.Amount) as AmountBoth
from Table1
left join Table2 on Table1.id = Table2.id
group by Table1.id
order by 1
/*
create table Table1 (Id int, Amount int)
create table Table2 (Id int, Amount int)
insert Table1 select 1, 100
insert Table1 select 2, 200
insert Table1 select 3, 300
insert Table1 select 4, 400
insert Table2 select 5, 500
insert Table2 select 2, 100
insert Table2 select 4, 400
insert Table2 select 6, 600
--drop table Table1
--drop table Table2
*/
---
select
coalesce(Table1.id,Table2.id) as id
,sum(coalesce(Table1.Amount,0)) + sum(coalesce(Table2.Amount,0)) as
AmountBoth
from Table1
full outer join Table2 on Table1.id = Table2.id
group by coalesce(Table1.id,Table2.id)
order by 1|||Great, thank you!
<markc600@.hotmail.com> wrote in message
news:1146119352.959456.161230@.t31g2000cwb.googlegroups.com...
>
> select
> coalesce(Table1.id,Table2.id) as id
> ,sum(coalesce(Table1.Amount,0)) + sum(coalesce(Table2.Amount,0)) as
> AmountBoth
> from Table1
> full outer join Table2 on Table1.id = Table2.id
> group by coalesce(Table1.id,Table2.id)
> order by 1
>|||You should be aware that this solution works when there is a one to
one relation ship between the two tables, but not if there is a one to
many (or many to many) relationship.
Here are two alternatives that avoid that problem.
SELECT id, sum(Amount) as Amount
FROM (select id, sum(Amount) as Amount
from Table1
group by id
UNION ALL
select id, sum(Amount) as Amount
from Table1
group by id) as Combo
GROUP BY id
ORDER BY 1
SELECT COALESCE(T1.id,T2.id),
T1.Amount + T2.Amount as Amount
FROM (select id, sum(Amount) as Amount
from Table1
group by id) as T1
FULL OUTER
JOIN (select id, sum(Amount) as Amount
from Table1
group by id) as T2
ON T1.id = T2.id
ORDER BY 1
Roy Harvey
Beacon Falls, CT
On Thu, 27 Apr 2006 09:53:11 +0300, "Yan" <yanive@.rediffmail.com>
wrote:

>Great, thank you!
>
><markc600@.hotmail.com> wrote in message
>news:1146119352.959456.161230@.t31g2000cwb.googlegroups.com...
>

Grouping records - HOW TO

Hi,
Is there a way that I can get the same sort of output as when you create a
relationship between two tables, but with only one table?
With a dataset containing two (or more) tables that has a data relation
added, you get a resultant output (say a datagrid) that groups by the column
detailed in the relationship. When the datagrid is displayed it shows each
parent record (row) with a '+' next to it. When selected you can then
display the related child records.
I have a single table with multiple records. Let's say one field is name.
There may be multiple records for each person who is displayed in the name
field. Instead of doing a simple sort and showing all records at the same
time, I would like to have the '+', and only show the persons name once. I
could then expand that record to show all for that person.
Is this possible?
I am using VB.Net, windows display (not IE), and sql server.
Hope there is an answer out there............even if it is NO ;-)
Rgds, PhilPhil,
use
SELECT DISTINCT name FROM table
for the first DataTable and
SELECT name, other_cols_needed FROM table
for the second DataTable.
Then add a DataRelation on the name column.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Phil" <Phil@.nospam.com> wrote in message
news:ddkugr$a1h$1@.nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
> Hi,
> Is there a way that I can get the same sort of output as when you create a
> relationship between two tables, but with only one table?
> With a dataset containing two (or more) tables that has a data relation
> added, you get a resultant output (say a datagrid) that groups by the
> column detailed in the relationship. When the datagrid is displayed it
> shows each parent record (row) with a '+' next to it. When selected you
> can then display the related child records.
> I have a single table with multiple records. Let's say one field is name.
> There may be multiple records for each person who is displayed in the name
> field. Instead of doing a simple sort and showing all records at the same
> time, I would like to have the '+', and only show the persons name once. I
> could then expand that record to show all for that person.
> Is this possible?
> I am using VB.Net, windows display (not IE), and sql server.
> Hope there is an answer out there............even if it is NO ;-)
> Rgds, Phil
>|||Dejan,
Thanks for the response but I only have ONE table, as stated. If I had two
tables then it wouldn't be a problem for me. Can it be done with one table?
Cheers, Phil
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23r89FNBoFHA.4028@.TK2MSFTNGP10.phx.gbl...
> Phil,
> use
> SELECT DISTINCT name FROM table
> for the first DataTable and
> SELECT name, other_cols_needed FROM table
> for the second DataTable.
> Then add a DataRelation on the name column.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "Phil" <Phil@.nospam.com> wrote in message
> news:ddkugr$a1h$1@.nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
>|||> Thanks for the response but I only have ONE table, as stated. If I had two
> tables then it wouldn't be a problem for me. Can it be done with one
> table?
You can use two selects to fill two DataTable objects from a single SQL
table.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Grouping records

Hi,

I have the following tables :

Town
towncode
townname

Area
areano
areaname
towncode

Locality

locno
areano

RequestType
reqid
reqdtls

Eg:
1 - Addition
2 - Removal
3 - Relocate

WebSummit

SummitId

RequestorName

DateOfRequest

reqid

Areano

I want to find out the Town/Area/Locality wise Addition/Removal/Relocation request that have come
during the last 1 month.

The query I have written so far is as follows :

SELECT WebRequest.SummitId, RequestType.reqdtls, Area.areaname, Locality.locno, Town.townname
FROM RequestType INNER JOIN
WebRequest ON RequestType.reqid = WebRequest.reqid INNER JOIN
Area ON WebRequest.areano = Area.areano INNER JOIN
TownList ON Area.towncode = TownList.towncode INNER JOIN
Locality ON Area.areano = Locality.areano

However the results are entirely incorrect with a lot of duplicates.

Kindly suggest me the right query

Regards,

Vidya.

Vidya:

You have given a good description of your problem that includes (1) table schema (2) current query and (3) the specific problem -- duplicate rows. What is missing here is (1) sample data and (2) desired output. Without this we must to some extent guess at the problem. You might be able to fix the problem by simply adding a DISTINCT clause to your select statement. This can be done by changing the word SELECT into SELECT DISTINCT.

|||

IF I understand you correctly, you may want something more like this:

Code Snippet

SELECT DISTINCT
w.SummitId,
r.ReqDtLs,
a.AreaName,
l.LocNo,
t.TownName
FROM TownList t
JOIN Area a
ON t.TownCode = a.TownCode
JOIN Locality l
ON a.AreaNo = l.AreaNo
JOIN WebRequest w
ON w.AreaNo = a.AreaNo
JOIN RequestType ar
ON r.ReqID = w.ReqID
WHERE ( w.DateOfRequest >= dateadd( month, datediff( month , 0, getdate() ) -1 , 0 )
AND w.DateOfRequest < dateadd( month, datediff( month, 0, getdate() ), 0 )

Without sample data, we can't test our suggestions.

|||

Hi,

Yes I understand that without sample data it is difficult to test the query. I am sorry.

I will try and take care of this point the next time i need some help.

Thanks for the help anyways. I will test it at my end and revert back.

Regards,

Vidya.

|||

Hi,

The results are the same as I got

Here's some sample data

Town

TownCode TownName

1 Conteck

Area

AreaNo AreaName TownCode

1 Area1 1

2 Area2 1

3 Area 3 1

4 Area4 1

5 Area5 1

6 Area6 1

7 Area7 1

Locality

LocNo AreaNo

1 1

2 1

3 1

1 2

2 2

1 3

2 3

3 3

4 3

1 4

2 4

1 5

2 5

3 5

RequestType

reqid reqdtls

1 Addition

2 Removal

3 Relocate

WebSummit

SummitId RequestorName DateOfRequest reqid AreaNo

1 John 12/6/2007 1 1

2 Jack 13/6/2007 1 1

3 Bill 12/6/2007 2 2

4 Ben 12/6/2007 2 2

5 Dale 14/6/2007 2 3

6 Evjen 15/6/2007 3 1

7 Fuller 16/6/2007 1 4

8 Jimmy 16/6/2007 3 4

9 Kart 16/6/2007 1 5

10 Fuller 16/6/2007 1 5

Regards,

Vidya.

|||

Hello,

Any clues why the query is returning duplicate records. The data gets duplicated for every locality in that area.

Regards,

Vidya

|||

Thanks for providing the table information. It is now possible to realize that it is impossible to get the information you desire. There are significant 'design problems' that will prevent you from accurately getting what you want.

You are getting duplicates because each Area has Two Localities, there is NO way to associate a Town to a specific Locality, AND there is no way to associate a specific town to WebSummit Request.

Therefore, the result shows all Localities and all Towns for an Area.

(I think you want the specific town related to a WebSummit request.)

Here are my suggestions about 'design problems' that should be corrected.

The Towns table 'should' have LocNo. Locality is an attribute of the Town. From the LocNo, we can get the Area.

The Areas table 'should NOT' have the TownCode. TownName is NOT an attribute of the Area.

The WebSummit table 'should' have the TownCode -NOT the AreaNo. Without the TownCode, you cannot break it down below the level of the Area. You have NO way to associate a Locality or Town to a WebSummit row.

Think of it this way: A Ttown belongs to a Locality, a Locality belongs to an Area. A WebSummit Request is related to a specific Town, NOT to all towns in a Locality, or NOT to all towns in an Area.

(At least that is my interpretation of your outline -and it's late, so I could be totally wrong...)

|||

Hi Arnie,

You have written :

A Ttown belongs to a Locality, a Locality belongs to an Area.

whereas

A Town has areas, Areas have locality so...

A locality belongs to an area, an area belongs to a town.

Regards,

Vidya.

|||

Thanks for clarifying that. This is why this process works so much better when we are provided table DDL, sample data, desired results, AND an explanition of the data model.

Well, I was making assumptions based upon my interpretation of the data you provided. As I said, it's late, and I made some incorrect assumptions.

In your sample data, you have a Location (LocNo 1) belonging to multiple Areas. Is that correct?

INSERT INTO @.Locality VALUES ( 1, 1 )
INSERT INTO @.Locality VALUES ( 2, 1 )
INSERT INTO @.Locality VALUES ( 3, 1 )
INSERT INTO @.Locality VALUES ( 1, 2 )
INSERT INTO @.Locality VALUES ( 2, 2 )

And the WebSummit rows are related to Areas, not Locality. So any attempt to bring Locality into the resultset will entail having all Localities for an Area.

It still seems that the WebSummit data does not capture the finest granular information and most likely should have LocNo instead of AreaNo. Otherwise, by removing Locality from the resultset, you can get one row per row in the WebSummit table.

My apologies for the misinterpretation...

|||

Hi Arnie,

Its my fault again. I shortened the structure of the websummit table to explain my problem.

The websummit contains the localityno as well as the areano.

I also forgot to mention that in table locality, both the LocNo and AreaNo together form a primary key.

You can easily change the data to avoid confusion :

Locality

LocNo AreaNo

1 1

2 1

3 1

33 2

34 2

35 2

1 3

2 3

6 3

66 4

67 4

22 5

23 5

24 5

Now even though locality 1 may be in area 1 or area 3 as shown in teh data above, the uniqueness is achieved using both the locno and areano together.

SummitId RequestorName DateOfRequest reqid AreaNo LocNo

1 John 12/6/2007 1 1 1

2 Jack 13/6/2007 1 1 2

3 Bill 12/6/2007 2 2 34

4 Ben 12/6/2007 2 2 35

5 Dale 14/6/2007 2 3 6

6 Evjen 15/6/2007 3 1 3

7 Fuller 16/6/2007 1 4 66

8 Jimmy 16/6/2007 3 4 66

9 Kart 16/6/2007 1 5 23

10 Fuller 16/6/2007 1 5 24

So in short, a locality is just a no. given to divide areas, however its important. A locality belongs to only one area since both locno and area no together form a unique row.

If i have caused much confusion, I am ready to start over again with a fresh query, explaining everything in one go.

Regards,

Vidya.

|||

Vidya,

Thanks, that was the 'missing' piece. Now it becomes possible to link all the data. (Amazing what we can do when we have all the 'facts'.)

This 'should' work for you as you have explained the problem. Note that I changed the WebSummit dates so the the previous month filter will work properly. I added a few rows of data that should be out of range in order to test exculsion.

Code Snippet

SET NOCOUNT ON

DECLARE @.Towns table
( TownCode int,
TownName varchar(20)
)

INSERT INTO @.Towns VALUES ( 1, 'Conteck' )
INSERT INTO @.Towns VALUES ( 3, 'BigConteck' )

DECLARE @.Areas table
( AreaNo int,
AreaName varchar(20),
TownCode int
)

INSERT INTO @.Areas VALUES ( 1, 'Area1', 1 )
INSERT INTO @.Areas VALUES ( 2, 'Area2', 1 )
INSERT INTO @.Areas VALUES ( 3, 'Area3', 1 )
INSERT INTO @.Areas VALUES ( 4, 'Area4', 1 )
INSERT INTO @.Areas VALUES ( 5, 'Area5', 1 )
INSERT INTO @.Areas VALUES ( 6, 'Area6', 1 )
INSERT INTO @.Areas VALUES ( 7, 'Area7', 1 )
INSERT INTO @.Areas VALUES ( 8, 'Area1', 3 )

DECLARE @.Locality table
( LocNo int,
AreaNo int
)

INSERT INTO @.Locality VALUES ( 1, 1 )
INSERT INTO @.Locality VALUES ( 2, 1 )
INSERT INTO @.Locality VALUES ( 3, 1 )
INSERT INTO @.Locality VALUES ( 33, 2 )
INSERT INTO @.Locality VALUES ( 34, 2 )
INSERT INTO @.Locality VALUES ( 35, 2 )
INSERT INTO @.Locality VALUES ( 1, 3 )
INSERT INTO @.Locality VALUES ( 2, 3 )
INSERT INTO @.Locality VALUES ( 6, 3 )
INSERT INTO @.Locality VALUES ( 10, 3 )
INSERT INTO @.Locality VALUES ( 11, 3 )
INSERT INTO @.Locality VALUES ( 66, 4 )
INSERT INTO @.Locality VALUES ( 67, 4 )
INSERT INTO @.Locality VALUES ( 22, 5 )
INSERT INTO @.Locality VALUES ( 23, 5 )
INSERT INTO @.Locality VALUES ( 24, 5 )

DECLARE @.RequestType table
( ReqID int,
ReqdTLS varchar(20)
)

INSERT INTO @.RequestType VALUES ( 1, 'Addition' )
INSERT INTO @.RequestType VALUES ( 2, 'Removal' )
INSERT INTO @.RequestType VALUES ( 3, 'Relocate' )

DECLARE @.WebSummit table
( SummitId int,
RequestorName varchar(20),
DateOfRequest datetime,
ReqID int,
AreaNo int,
LocNo int
)

INSERT INTO @.WebSummit VALUES ( 1, 'John', '2007/05/12', 1, 1, 1 )
INSERT INTO @.WebSummit VALUES ( 2, 'Jack', '2007/05/13', 1, 1, 2 )
INSERT INTO @.WebSummit VALUES ( 3, 'Bill', '2007/05/12', 2, 2, 34 )
INSERT INTO @.WebSummit VALUES ( 4, 'Ben', '2007/05/12', 2, 2, 35 )
INSERT INTO @.WebSummit VALUES ( 5, 'Dale', '2007/05/14', 2, 3, 6 )
INSERT INTO @.WebSummit VALUES ( 6, 'Evjen', '2007/05/15', 3, 1, 3 )
INSERT INTO @.WebSummit VALUES ( 7, 'Fuller', '2007/05/16', 1, 4, 66 )
INSERT INTO @.WebSummit VALUES ( 8, 'Jimmy', '2007/05/16', 3, 4, 66 )
INSERT INTO @.WebSummit VALUES ( 9, 'Kart', '2007/05/16', 1, 5, 23 )
INSERT INTO @.WebSummit VALUES ( 10, 'Fuller', '2007/05/16', 1, 5, 24 )
INSERT INTO @.WebSummit VALUES ( 11, 'Kart', '2007/06/16', 1, 5, 23 )
INSERT INTO @.WebSummit VALUES ( 12, 'Fuller', '2007/06/16', 1, 5, 24 )

SELECT DISTINCT
w.SummitId,
r.ReqDtLs,
a.AreaName,
a.AreaNo,
l.LocNo,
t.TownName
FROM @.WebSummit w
JOIN @.Locality l
ON ( w.AreaNo = l.AreaNo
AND w.LocNo = l.LocNo
)
JOIN @.Areas a
ON w.AreaNo = a.AreaNo
JOIN @.Towns t
ON t.TownCode = a.TownCode
JOIN @.RequestType r
ON w.ReqID = r.ReqID
WHERE ( w.DateOfRequest >= dateadd( month, datediff( month , 0, getdate() ) -1 , 0 )
AND w.DateOfRequest < dateadd( month, datediff( month, 0, getdate() ), 0 )
)

|||

Bingo!!

Thanks a ton Arnie. Indeed, that's the query I was trying out.

Do me another favour. Suggest me a book to study T-sql or Sql server. I would now like to dedicate a lot of time to get strong in Sql.

See arnie, what you just did!! You inspired me!!

Regards,

Vidya.

|||

Itzak Ben-Gan has two books out on Microsoft Press -they are excellent!

A VERY worthwhile investment in your skills.

Good luck!

grouping question

hey all,
i have 2 tables. i'm grouping on table1 i'd like to join this to table2
which would create 1 to 1 relationship. when add extra fields from table2 it
forces me to group by these fields as well. Can someone please explain this
concept to me?
thanks,
rodcharCould you give better specs?
http://www.aspfaq.com/5006
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:DAD412E6-9F72-42D2-8220-A8905E863944@.microsoft.com...
> hey all,
> i have 2 tables. i'm grouping on table1 i'd like to join this to table2
> which would create 1 to 1 relationship. when add extra fields from table2
> it
> forces me to group by these fields as well. Can someone please explain
> this
> concept to me?
> thanks,
> rodchar|||Can you post what you are doing to have an idea of what you are talking abou
t?
AMB
"rodchar" wrote:

> hey all,
> i have 2 tables. i'm grouping on table1 i'd like to join this to table2
> which would create 1 to 1 relationship. when add extra fields from table2
it
> forces me to group by these fields as well. Can someone please explain thi
s
> concept to me?
> thanks,
> rodchar|||rodchar wrote:
> hey all,
> i have 2 tables. i'm grouping on table1 i'd like to join this to
> table2 which would create 1 to 1 relationship. when add extra fields
> from table2 it forces me to group by these fields as well. Can
> someone please explain this concept to me?
> thanks,
> rodchar
All columns must appear in a group by clause unless you are using an
aggregate. From BOL: "When GROUP BY is specified, either each column in
any non-aggregate expression in the select list should be included in
the GROUP BY list, or the GROUP BY expression must match exactly the
select list expression."
If you were allowed to leave a column off the Group By clause, what
value would SQL Server use for the result set (assuming there were
multiple matches)?
You may be able to use a derived table to do what you want, but as Aaron
mentioned, we need some more details.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||The GROUP BY clause is explained here:
http://msdn.microsoft.com/library/d...r />
_9sfo.asp
ML|||thanks David and everyone this helped.
"David Gugick" wrote:

> rodchar wrote:
> All columns must appear in a group by clause unless you are using an
> aggregate. From BOL: "When GROUP BY is specified, either each column in
> any non-aggregate expression in the select list should be included in
> the GROUP BY list, or the GROUP BY expression must match exactly the
> select list expression."
> If you were allowed to leave a column off the Group By clause, what
> value would SQL Server use for the result set (assuming there were
> multiple matches)?
> You may be able to use a derived table to do what you want, but as Aaron
> mentioned, we need some more details.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

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

Grouped information from two tables

Hi,

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

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

What I want is to merge the values

2006 1 0.000000
2006 1 50813.058500

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

Thanks

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

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

sql

Group with Time Values - Please Help

I have two tables that are joined by a left outer join.
Table A (Hours) just has 24 records that represent each our in the day. For example:
00:00, 01:00, ...23:00. Table B (Data) has the data I need to report off of. I joined
Table A to Table B.

I created a group on Table A because I always want to display all 24 hours even if there
is no data in Table B for that hour. So I now have 24 sections in my group.

My problem is that when I put data into the details section, I'm only getting data where
the hours exactly match. For example, Group section 08:00 is only returning data where
the hour is 08:00. I actually need it to return all data where the hour is between
08:00 and 08:59. I've been working on this for a while and I'm really stuck.

I'm using an access database and the hours field in both table is a date/time field.

Any help would be greatly appreciated. Thanks so much.

- StephanieHi,

I had experienced the same problem. I had to display the data for all the days in a month regardless of the data they have.

Used the same left outer join concept. But it didn't work. Then I had created a temp table and written code to get the result.

If any body knows why the left outer join concept is not working in crystal, please share with us

sample

table1 : contains simply all the dates from 1 to lastday
table2 : contains data for the dates in table1 (not for all the days)|||Hi Stephanie
One possible solution would be to create a report based only on Table A(with hours registered) and subreport based on Table B.Don't make any links between them.In main report insert group for field that holds hours.If you view preview now you would see all records from Table B for each hour.
In main report create a formula and add shared variable and assign only first two characters from group field.Values will be 00,01,02 etc.
Now,in subreport supress all records where first two characters of your hour fiels in Table B are not equal to shared variable.
I tested this in CR 8.0 and it worked fine.|||Thanks Denan, that's a very interesting suggestion! I'm going to try that right now!

Stephanie|||Good idea!

But what about the performance?

For a single day, the report will be called 24 times?|||Hi
Performance is definetly not optimal here.Best solution would be to filter data in subreport but unfortunately CR (at least 8.0) doesn't allow shared variables in record selection.
Biggest problem here is that you can not link those two tables.Left outer join doesn't help since it also requires a match in both tables.
Another solution would be to add another field in Table B that would hold values like 08:00,09:00 etc.That means you would need to add application logic to compute that value.I think that solution is more expensive.

Đenan

GROUP BY's on 3 tables in one SELECT?

Hi there, I'd like to ask you for help with following:
having 3 tables:
T1 (Person_ID, Product_ID, Costs)
T2 (Person_ID, Product_ID, Balancies)
T3 (Product_ID, Product_Type)
I have simple GROUP BY query:
SELECT T1.Person_ID, T1.Product_ID, MAX(Costs)
FROM T1, T2
WHERE (T1.Product_ID = T3.Product_ID) AND (T3.Product_Type='AA')
GROUP BY T1.Person_ID, T1.Product_ID
at the end of each row obtained from previous statement, I'd like to add
sum of T2.Balancies for which (T1.Person_ID = T2.Person_ID) AND
(T2.Product_ID = T3.Product_ID) AND (T3.Product_Type='BB')
Is it possible to do it within one SELECT statement?
On Thu, 18 Aug 2005 12:51:03 -0700, Martin S. <Martin
S.@.discussions.microsoft.com> wrote:

>Hi there, I'd like to ask you for help with following:
>having 3 tables:
>T1 (Person_ID, Product_ID, Costs)
>T2 (Person_ID, Product_ID, Balancies)
>T3 (Product_ID, Product_Type)
>I have simple GROUP BY query:
>SELECT T1.Person_ID, T1.Product_ID, MAX(Costs)
>FROM T1, T2
>WHERE (T1.Product_ID = T3.Product_ID) AND (T3.Product_Type='AA')
>GROUP BY T1.Person_ID, T1.Product_ID
>at the end of each row obtained from previous statement, I'd like to add
>sum of T2.Balancies for which (T1.Person_ID = T2.Person_ID) AND
>(T2.Product_ID = T3.Product_ID) AND (T3.Product_Type='BB')
>Is it possible to do it within one SELECT statement?
Hi Martin,
Probably - but your question has to be clarified first. I think that
there might be some typos in your query as well (e.g. the T2 after FROM
should be T3, right? And should the product type in the WHERE clause not
read BB instead of AA?)
To make sure that I'll understand the question you are asking and not
something else, I ask you to post details about your tables AS CREATE
TABLE STATEMENTS (and please include all constraints and roperties).
Also, add some rows of sample data (as INSERT statements) and the
required output.
See www.aspfaq.com/5006 for more details.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo,

> Probably - but your question has to be clarified first. I think that
> there might be some typos in your query as well (e.g. the T2 after FROM
> should be T3, right?
Yes, You are right.

> And should the product type in the WHERE clause not
> read BB instead of AA?)
That's as I'have written. I need to find MAX(T1.Costs) for product AA and
somehow connect to it SUM(T2.Balancies) for product BB for the same person.
I see that trying to simplify my question, I've missed one field -
T1.Account_ID and T2.Account_ID. So let me write it down once again:
having 3 tables:
T1 (Person_ID, Account_ID, Product_ID, Costs)
T2 (Person_ID, Account_ID, Product_ID, Balancies)
T3 (Product_ID, Product_Type)
primary key for T1 is Person_ID together with Account_ID
primary key for T2 is Person_ID together with Account_ID
primary key for T3 is Product_ID
Account_ID's in T1 are different from Account_ID's in T2
so having GROUP BY query:
SELECT T1.Person_ID,
T1.Account_ID,
T1.Product_ID,
MAX(Costs)
FROM T1, T3
WHERE (T1.Product_ID = T3.Product_ID)
AND (T3.Product_Type='AA')
GROUP BY T1.Person_ID, T1.Account_ID, T1.Product_ID
at the end of each row obtained from previous statement, I'd like to add
sum of T2.Balancies for which (T1.Person_ID = T2.Person_ID) AND
(T2.Product_ID = T3.Product_ID) AND (T3.Product_Type='BB')
and write the whole thing using one SELECT
Unfortunately, the tables were not created by me (I just want to extract
something), so I can't include any CREATE TABLE statement.
Example:
T1
Person_ID Account_ID Product_ID Costs
-- -- -- --
2 254 1000 4
80 243 1000 3
80 232 1000 6
100 235 3000 8
T2
Person_ID Account_ID Product_ID Balancies
-- -- -- --
1 345 4000 10
2 346 2000 10
80 376 2000 10
80 389 2000 10
80 325 5000 10
T3
Product_ID Product_Type
-- --
1000 AA
2000 BB
3000 C
4000 D
5000 E
The result on previous tables should be like this:
Person_ID Account_ID Product_ID MAX(Costs) SUM(Balancies)
-- -- -- -- --
2 254 1000 4
10
80 243 1000 6
20
80 232 1000 6
20
Martin
"Hugo Kornelis" wrote:

> On Thu, 18 Aug 2005 12:51:03 -0700, Martin S. <Martin
> S.@.discussions.microsoft.com> wrote:
>
> Hi Martin,
> Probably - but your question has to be clarified first. I think that
> there might be some typos in your query as well (e.g. the T2 after FROM
> should be T3, right? And should the product type in the WHERE clause not
> read BB instead of AA?)
> To make sure that I'll understand the question you are asking and not
> something else, I ask you to post details about your tables AS CREATE
> TABLE STATEMENTS (and please include all constraints and roperties).
> Also, add some rows of sample data (as INSERT statements) and the
> required output.
> See www.aspfaq.com/5006 for more details.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 18 Aug 2005 16:12:02 -0700, Martin S. wrote:
(snip)
>Unfortunately, the tables were not created by me (I just want to extract
>something), so I can't include any CREATE TABLE statement.
Hi Martin,
The website I refered you to includes a description of how you can
easily create a script from your database. It also has a link to a
script that will generate INSERT statements from existing data.
The sample data you posted doesn't help much either, as their formatting
is off (at least in my news reader). There's a reason I asked you to
post the sample data as INSERT statements, you know...
Anyway, based on your narrative and my limited understanding of the
sample data you posted, the following might work:
SELECT T1.Person_ID, T1.Product_ID, MAX(Costs),
(SELECT SUM(T2b.Balancies)
FROM T3, T2 as T2b
WHERE T2b.PersonID = T1.PersonID
AND T2b.Product_ID = T3.Product_ID
AND T3.Product_Type = 'BB')
FROM T1, T2
WHERE (T1.Product_ID = T3.Product_ID) AND (T3.Product_Type='AA')
GROUP BY T1.Person_ID, T1.Product_ID
(untested)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks for your time (and sorry for missing DDL), the issue was finally
solved using LEFT JOIN.
Martin

Group By??

Hi,

I have a pretty simple problem for a SQL programmer regarding GROUPINGS:

I have two tables tblShoppingCarts and tblProducts.
The first table stores all shopping cart data.
I would like to retrieve all this data (cart and all related product
information) BUT need to GROUP BY tblShoppingCarts.sessionid.

Also, any suggestions on fields to include in the shopping cart would be
much appreciated.

Thanks for your help.

NOTE: To retreive all cart information run the stored procedure EXEC
spGetShoppingCarts NULL, NULL

--------------

CREATE TABLE [dbo].[tblProducts] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[category] [int] NOT NULL ,
[publishdate] [smalldatetime] NOT NULL ,
[title] [varchar] (100) NOT NULL ,
[shorttitle] [varchar] (30) NULL ,
[version] [float] NOT NULL ,
[build] [int] NOT NULL ,
[shortDescription] [varchar] (1000) NULL ,
[description] [varchar] (4000) NOT NULL ,
[retailPrice] [money] NOT NULL ,
[oemPrice] [money] NULL ,
[hasImage] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblShoppingCarts] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[sessionid] [bigint] NOT NULL ,
[publishdate] [smalldatetime] NOT NULL ,
[modifieddate] [smalldatetime] NULL ,
[productid] [int] NOT NULL ,
[quantity] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE PROCEDURE spGetShoppingCarts

@.id int = NULL,
@.sessionid bigint = NULL

AS

-- Get all shopping carts
IF (@.id IS NULL) AND (@.sessionid IS NULL)

BEGIN

SELECT
sc.[id],
sc.sessionid,
sc.publishdate,
sc.modifieddate,
sc.productid,
p.[title] AS ProductTitle,
p.[shorttitle] AS ProductShortTitle,
sc.quantity,
COUNT(sc.sessionid) AS ProductCount
--p.discount,
--p.quantity AS DiscountQuantity,
--p.code
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
GROUP BY
sc.[id],
--I WANT TO GROUP BY SESSIONID!!!!!!!!!!!!!!!!!! WHY DOESNT THIS
WORK??
sc.sessionid,
sc.publishdate,
sc.modifieddate,
sc.productid,
p.title,
p.shorttitle,
sc.quantity

ORDER BY
sc.[modifieddate] DESC

END

ELSE

BEGIN

-- Get shopping cart by session
IF (@.id IS NULL)

BEGIN

SELECT
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.[title] AS ProductTitle,
p.[shorttitle] AS ProductShortTitle,
sc.quantity,
ProductCount = ( SELECT COUNT(sc.productid) FROM tblShoppingCarts sc
WHERE sc.sessionid = @.sessionid )
--p.discount,
--p.quantity AS DiscountQuantity,
--p.code
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
AND sc.[sessionid] = @.sessionid

END

-- Get shopping cart by id
ELSE

BEGIN

SELECT
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.[title] AS ProductTitle,
p.[shorttitle] AS ProductShortTitle,
sc.quantity,
COUNT(sc.productid) AS ProductCount
--p.discount,
--p.quantity AS DiscountQuantity,
--p.code
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
AND sc.[id] = @.id

GROUP BY
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.title,
p.shorttitle,
sc.quantity
END

END
GOThanks everyone for your help!

I finally worked out after a bit of reading exactly how the GROUP BY
statement works and why I would only use it if I where aggregating
information (ie SUM, COUNT etc).
The solution for the stored procedure was:

CREATE PROCEDURE spGetShoppingCarts

@.id int = NULL,
@.sessionid bigint = NULL

AS

-- Get all shopping carts
IF (@.id IS NULL) AND (@.sessionid IS NULL)

BEGIN

SELECT
sc.sessionid,
LastUpdated = MAX( sc.publishdate ),
TotalPrice = SUM(sc.quantity * p.retailPrice)
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
GROUP BY
sc.sessionid
ORDER BY
2 DESC

END

ELSE

BEGIN

-- Get shopping cart by session
IF (@.id IS NULL)

BEGIN

SELECT
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.[title] AS ProductTitle,
p.[shorttitle] AS ProductShortTitle,
sc.quantity,
TotalPrice = (sc.quantity * p.retailPrice),
sc.discount,
sc.code
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
AND sc.[sessionid] = @.sessionid

END

-- Get shopping cart by id
ELSE

BEGIN

SELECT
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.[title] AS ProductTitle,
p.[shorttitle] AS ProductShortTitle,
sc.quantity,
TotalPrice = (sc.quantity * p.retailPrice),
sc.discount,
sc.code
FROM
tblShoppingCarts sc,
tblProducts p
WHERE
sc.productid = p.[id]
AND sc.[id] = @.id

GROUP BY
sc.[id],
sc.publishdate,
sc.modifieddate,
sc.productid,
p.title,
p.shorttitle,
sc.quantity,
sc.discount,
sc.code ,
p.retailPrice
END

END
GO

"Stephen McCormack" <stephenm@.mwebsolutions.com.au> wrote in message
news:XdOMa.235$JI4.5088@.news-server.bigpond.net.au...
> Hi,
> I have a pretty simple problem for a SQL programmer regarding GROUPINGS:
> I have two tables tblShoppingCarts and tblProducts.
> The first table stores all shopping cart data.
> I would like to retrieve all this data (cart and all related product
> information) BUT need to GROUP BY tblShoppingCarts.sessionid.
> Also, any suggestions on fields to include in the shopping cart would be
> much appreciated.
> Thanks for your help.
> NOTE: To retreive all cart information run the stored procedure EXEC
> spGetShoppingCarts NULL, NULL
> --------------
> CREATE TABLE [dbo].[tblProducts] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [category] [int] NOT NULL ,
> [publishdate] [smalldatetime] NOT NULL ,
> [title] [varchar] (100) NOT NULL ,
> [shorttitle] [varchar] (30) NULL ,
> [version] [float] NOT NULL ,
> [build] [int] NOT NULL ,
> [shortDescription] [varchar] (1000) NULL ,
> [description] [varchar] (4000) NOT NULL ,
> [retailPrice] [money] NOT NULL ,
> [oemPrice] [money] NULL ,
> [hasImage] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[tblShoppingCarts] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [sessionid] [bigint] NOT NULL ,
> [publishdate] [smalldatetime] NOT NULL ,
> [modifieddate] [smalldatetime] NULL ,
> [productid] [int] NOT NULL ,
> [quantity] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE PROCEDURE spGetShoppingCarts
> @.id int = NULL,
> @.sessionid bigint = NULL
> AS
> -- Get all shopping carts
> IF (@.id IS NULL) AND (@.sessionid IS NULL)
> BEGIN
> SELECT
> sc.[id],
> sc.sessionid,
> sc.publishdate,
> sc.modifieddate,
> sc.productid,
> p.[title] AS ProductTitle,
> p.[shorttitle] AS ProductShortTitle,
> sc.quantity,
> COUNT(sc.sessionid) AS ProductCount
> --p.discount,
> --p.quantity AS DiscountQuantity,
> --p.code
> FROM
> tblShoppingCarts sc,
> tblProducts p
> WHERE
> sc.productid = p.[id]
> GROUP BY
> sc.[id],
> --I WANT TO GROUP BY SESSIONID!!!!!!!!!!!!!!!!!! WHY DOESNT THIS
> WORK??
> sc.sessionid,
> sc.publishdate,
> sc.modifieddate,
> sc.productid,
> p.title,
> p.shorttitle,
> sc.quantity
> ORDER BY
> sc.[modifieddate] DESC
> END
> ELSE
> BEGIN
> -- Get shopping cart by session
> IF (@.id IS NULL)
>
> BEGIN
> SELECT
> sc.[id],
> sc.publishdate,
> sc.modifieddate,
> sc.productid,
> p.[title] AS ProductTitle,
> p.[shorttitle] AS ProductShortTitle,
> sc.quantity,
> ProductCount = ( SELECT COUNT(sc.productid) FROM tblShoppingCarts sc
> WHERE sc.sessionid = @.sessionid )
> --p.discount,
> --p.quantity AS DiscountQuantity,
> --p.code
> FROM
> tblShoppingCarts sc,
> tblProducts p
> WHERE
> sc.productid = p.[id]
> AND sc.[sessionid] = @.sessionid
> END
> -- Get shopping cart by id
> ELSE
> BEGIN
> SELECT
> sc.[id],
> sc.publishdate,
> sc.modifieddate,
> sc.productid,
> p.[title] AS ProductTitle,
> p.[shorttitle] AS ProductShortTitle,
> sc.quantity,
> COUNT(sc.productid) AS ProductCount
> --p.discount,
> --p.quantity AS DiscountQuantity,
> --p.code
> FROM
> tblShoppingCarts sc,
> tblProducts p
> WHERE
> sc.productid = p.[id]
> AND sc.[id] = @.id
> GROUP BY
> sc.[id],
> sc.publishdate,
> sc.modifieddate,
> sc.productid,
> p.title,
> p.shorttitle,
> sc.quantity
> END
>
> END
> GO

Group by/ Having question

Hi all
I have these two tables, account and package and i want to get all
accounts that have more than 1 package where dateremoved is null.
This is what I have so far..
select accounts.name, packages.guid, packages.datecreated,
packages.dateremoved
from accounts inner join packages on packages.account = accounts.guid
where packages.datecreated > '2007-01-12 00:00:00'
and (packages.dateremoved is null)
order by packages.datecreated
This gives me number of accounts and i can see the accounts that have
more than 1 package where the dateremoved field is null but I only want
to get those accounts..
I did try this;
select accounts.guid, packages.guid, packages.datecreated
from accounts inner join packages on packages.account = accounts.guid
where packages.dateremoved is null
and (packages.dateremoved in ( select packages.dateremoved
from packages
GROUP BY (packages.dateremoved)
HAVING count(packages.dateremoved) > 1
but this gives me now results.. not error, just empty results.
Can you guys and girls help me with this'
thanxAccording to yout definition:
"I have these two tables, account and package and i want to get all
accounts that have more than 1 package where dateremoved is null. "
SELECT * FROM Accounts A
WHERE EXISTS
(
SELECT * FROM Package P
WHERE dateremoved IS NULL
AND P.account = A.guid
AND where packages.datecreated > '2007-01-12 00:00:00'
)
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||SELECT accounts.*
FROM accounts
JOIN (select account
from packages
where datecreated > '2007-01-12 00:00:00'
and dateremoved is null
group by account
HAVING COUNT(*) > 1) as X
ON accounts.guid = X.account
If all you wanted was the list of account numbers, just run the inner
query. If you also need other account information you need the rest
too.
Roy Harvey
Beacon Falls, CT
On 16 Jan 2007 03:32:59 -0800, kollatjorva@.gmail.com wrote:
>Hi all
>I have these two tables, account and package and i want to get all
>accounts that have more than 1 package where dateremoved is null.
>This is what I have so far..
>select accounts.name, packages.guid, packages.datecreated,
>packages.dateremoved
>from accounts inner join packages on packages.account = accounts.guid
>where packages.datecreated > '2007-01-12 00:00:00'
>and (packages.dateremoved is null)
>order by packages.datecreated
>This gives me number of accounts and i can see the accounts that have
>more than 1 package where the dateremoved field is null but I only want
>to get those accounts..
>I did try this;
>select accounts.guid, packages.guid, packages.datecreated
>from accounts inner join packages on packages.account = accounts.guid
>where packages.dateremoved is null
>and (packages.dateremoved in ( select packages.dateremoved
>from packages
>GROUP BY (packages.dateremoved)
>HAVING count(packages.dateremoved) > 1
>but this gives me now results.. not error, just empty results.
>Can you guys and girls help me with this'
>thanx|||This works like a charm..
Thanks Roy, regards to you from Iceland.
Roy Harvey wrote:
> SELECT accounts.*
> FROM accounts
> JOIN (select account
> from packages
> where datecreated > '2007-01-12 00:00:00'
> and dateremoved is null
> group by account
> HAVING COUNT(*) > 1) as X
> ON accounts.guid = X.account
> If all you wanted was the list of account numbers, just run the inner
> query. If you also need other account information you need the rest
> too.
> Roy Harvey
> Beacon Falls, CT
> On 16 Jan 2007 03:32:59 -0800, kollatjorva@.gmail.com wrote:
> >Hi all
> >I have these two tables, account and package and i want to get all
> >accounts that have more than 1 package where dateremoved is null.
> >
> >This is what I have so far..
> >
> >select accounts.name, packages.guid, packages.datecreated,
> >packages.dateremoved
> >from accounts inner join packages on packages.account = accounts.guid
> >where packages.datecreated > '2007-01-12 00:00:00'
> >and (packages.dateremoved is null)
> >order by packages.datecreated
> >
> >This gives me number of accounts and i can see the accounts that have
> >more than 1 package where the dateremoved field is null but I only want
> >to get those accounts..
> >
> >I did try this;
> >
> >select accounts.guid, packages.guid, packages.datecreated
> >from accounts inner join packages on packages.account = accounts.guid
> >where packages.dateremoved is null
> >and (packages.dateremoved in ( select packages.dateremoved
> >from packages
> >GROUP BY (packages.dateremoved)
> >HAVING count(packages.dateremoved) > 1
> >
> >but this gives me now results.. not error, just empty results.
> >
> >Can you guys and girls help me with this'
> >thanx|||You should consider using the plan analyzer, I guess Roys solution will
take longer than just using the EXISTS.
-Jens|||On 16 Jan 2007 06:29:40 -0800, "Jens" <Jens@.sqlserver2005.de> wrote:
>You should consider using the plan analyzer, I guess Roys solution will
>take longer than just using the EXISTS.
>-Jens
Recall that the requirement was "to get all accounts that have more
than 1 package where dateremoved is null." I believe the EXISTS
version posted does not test for more than one, it tests for at least
one.
Roy Harvey
Beacon Falls, CT|||You are right, I missed the "more" than one.

Group by/ Having question

Hi all
I have these two tables, account and package and i want to get all
accounts that have more than 1 package where dateremoved is null.
This is what I have so far..
select accounts.name, packages.guid, packages.datecreated,
packages.dateremoved
from accounts inner join packages on packages.account = accounts.guid
where packages.datecreated > '2007-01-12 00:00:00'
and (packages.dateremoved is null)
order by packages.datecreated
This gives me number of accounts and i can see the accounts that have
more than 1 package where the dateremoved field is null but I only want
to get those accounts..
I did try this;
select accounts.guid, packages.guid, packages.datecreated
from accounts inner join packages on packages.account = accounts.guid
where packages.dateremoved is null
and (packages.dateremoved in ( select packages.dateremoved
from packages
GROUP BY (packages.dateremoved)
HAVING count(packages.dateremoved) > 1
but this gives me now results.. not error, just empty results.
Can you guys and girls help me with this?
thanx
SELECT accounts.*
FROM accounts
JOIN (select account
from packages
where datecreated > '2007-01-12 00:00:00'
and dateremoved is null
group by account
HAVING COUNT(*) > 1) as X
ON accounts.guid = X.account
If all you wanted was the list of account numbers, just run the inner
query. If you also need other account information you need the rest
too.
Roy Harvey
Beacon Falls, CT
On 16 Jan 2007 03:32:59 -0800, kollatjorva@.gmail.com wrote:

>Hi all
>I have these two tables, account and package and i want to get all
>accounts that have more than 1 package where dateremoved is null.
>This is what I have so far..
>select accounts.name, packages.guid, packages.datecreated,
>packages.dateremoved
>from accounts inner join packages on packages.account = accounts.guid
>where packages.datecreated > '2007-01-12 00:00:00'
>and (packages.dateremoved is null)
>order by packages.datecreated
>This gives me number of accounts and i can see the accounts that have
>more than 1 package where the dateremoved field is null but I only want
>to get those accounts..
>I did try this;
>select accounts.guid, packages.guid, packages.datecreated
>from accounts inner join packages on packages.account = accounts.guid
>where packages.dateremoved is null
>and (packages.dateremoved in ( select packages.dateremoved
>from packages
>GROUP BY (packages.dateremoved)
>HAVING count(packages.dateremoved) > 1
>but this gives me now results.. not error, just empty results.
>Can you guys and girls help me with this?
>thanx
|||This works like a charm..
Thanks Roy, regards to you from Iceland.
Roy Harvey wrote:[vbcol=seagreen]
> SELECT accounts.*
> FROM accounts
> JOIN (select account
> from packages
> where datecreated > '2007-01-12 00:00:00'
> and dateremoved is null
> group by account
> HAVING COUNT(*) > 1) as X
> ON accounts.guid = X.account
> If all you wanted was the list of account numbers, just run the inner
> query. If you also need other account information you need the rest
> too.
> Roy Harvey
> Beacon Falls, CT
> On 16 Jan 2007 03:32:59 -0800, kollatjorva@.gmail.com wrote:
|||You should consider using the plan analyzer, I guess Roys solution will
take longer than just using the EXISTS.
-Jens
|||On 16 Jan 2007 06:29:40 -0800, "Jens" <Jens@.sqlserver2005.de> wrote:

>You should consider using the plan analyzer, I guess Roys solution will
>take longer than just using the EXISTS.
>-Jens
Recall that the requirement was "to get all accounts that have more
than 1 package where dateremoved is null." I believe the EXISTS
version posted does not test for more than one, it tests for at least
one.
Roy Harvey
Beacon Falls, CT
|||You are right, I missed the "more" than one.

Group by/ Having question

Hi all
I have these two tables, account and package and i want to get all
accounts that have more than 1 package where dateremoved is null.
This is what I have so far..
select accounts.name, packages.guid, packages.datecreated,
packages.dateremoved
from accounts inner join packages on packages.account = accounts.guid
where packages.datecreated > '2007-01-12 00:00:00'
and (packages.dateremoved is null)
order by packages.datecreated
This gives me number of accounts and i can see the accounts that have
more than 1 package where the dateremoved field is null but I only want
to get those accounts..
I did try this;
select accounts.guid, packages.guid, packages.datecreated
from accounts inner join packages on packages.account = accounts.guid
where packages.dateremoved is null
and (packages.dateremoved in ( select packages.dateremoved
from packages
GROUP BY (packages.dateremoved)
HAVING count(packages.dateremoved) > 1
but this gives me now results.. not error, just empty results.
Can you guys and girls help me with this'
thanxAccording to yout definition:
"I have these two tables, account and package and i want to get all
accounts that have more than 1 package where dateremoved is null. "
SELECT * FROM Accounts A
WHERE EXISTS
(
SELECT * FROM Package P
WHERE dateremoved IS NULL
AND P.account = A.guid
AND where packages.datecreated > '2007-01-12 00:00:00'
)
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||SELECT accounts.*
FROM accounts
JOIN (select account
from packages
where datecreated > '2007-01-12 00:00:00'
and dateremoved is null
group by account
HAVING COUNT(*) > 1) as X
ON accounts.guid = X.account
If all you wanted was the list of account numbers, just run the inner
query. If you also need other account information you need the rest
too.
Roy Harvey
Beacon Falls, CT
On 16 Jan 2007 03:32:59 -0800, kollatjorva@.gmail.com wrote:

>Hi all
>I have these two tables, account and package and i want to get all
>accounts that have more than 1 package where dateremoved is null.
>This is what I have so far..
>select accounts.name, packages.guid, packages.datecreated,
>packages.dateremoved
>from accounts inner join packages on packages.account = accounts.guid
>where packages.datecreated > '2007-01-12 00:00:00'
>and (packages.dateremoved is null)
>order by packages.datecreated
>This gives me number of accounts and i can see the accounts that have
>more than 1 package where the dateremoved field is null but I only want
>to get those accounts..
>I did try this;
>select accounts.guid, packages.guid, packages.datecreated
>from accounts inner join packages on packages.account = accounts.guid
>where packages.dateremoved is null
>and (packages.dateremoved in ( select packages.dateremoved
>from packages
>GROUP BY (packages.dateremoved)
>HAVING count(packages.dateremoved) > 1
>but this gives me now results.. not error, just empty results.
>Can you guys and girls help me with this'
>thanx|||This works like a charm..
Thanks Roy, regards to you from Iceland.
Roy Harvey wrote:[vbcol=seagreen]
> SELECT accounts.*
> FROM accounts
> JOIN (select account
> from packages
> where datecreated > '2007-01-12 00:00:00'
> and dateremoved is null
> group by account
> HAVING COUNT(*) > 1) as X
> ON accounts.guid = X.account
> If all you wanted was the list of account numbers, just run the inner
> query. If you also need other account information you need the rest
> too.
> Roy Harvey
> Beacon Falls, CT
> On 16 Jan 2007 03:32:59 -0800, kollatjorva@.gmail.com wrote:
>|||You should consider using the plan analyzer, I guess Roys solution will
take longer than just using the EXISTS.
-Jens|||On 16 Jan 2007 06:29:40 -0800, "Jens" <Jens@.sqlserver2005.de> wrote:

>You should consider using the plan analyzer, I guess Roys solution will
>take longer than just using the EXISTS.
>-Jens
Recall that the requirement was "to get all accounts that have more
than 1 package where dateremoved is null." I believe the EXISTS
version posted does not test for more than one, it tests for at least
one.
Roy Harvey
Beacon Falls, CT|||You are right, I missed the "more" than one.

Group By, Max(date) query problem

Hello all:

I have an invoice header and detail tables and a customer table using sqlserver 2005. The Detail invoice table has price and product id. The header has the date and customerID.

I need to create a list of the most recent invoice date (and the product price) for each product for each customer. I can't use the group by because when I select both the max(date) and the price (as well as the productID and customerID which all have to be included in the group by) , I get more than one date per product per customer. I can only get the most recent date when I leave out the price and headerdetailID from the field selection.

Any help would be appreciated. Here is sample data & results.

invoiceHeadertable

invheaderID CustomerID InvoiceDate

1 40 1/1/2006

2 40 4/1/2006

3 80 3/1/2006

4 80 7/1/2006

5 80 8/12/2006

invoicedetailtable

invdetail ID invheaderID productcode price

11 1 AA 1.50

12 1 BB 1.30

13 1 CC 1.00

21 2 AA 1.40

23 2 CC 2.00

24 3 AA 2.00

25 3 CC 2.10

26 3 EE 1.10

27 4 AA 1.00

28 4 CC 2.00

29 4 EE 0.99

34 5 EE 1.55

CustomerTable

CustomerID Customername

40 johnCorp

80 maryCorp

Results

customer product most recent invoice(for this product) price

JohnCorp 40 AA 4/1/2006 1.40

JohnCorp 40 BB 1/1/2006 1.30

JohnCorp 40 CC 4/1/2006 2.00

maryCorp 80 AA 7/1/2006 1.40

maryCorp 80 CC 7 /1/2006 2.00

maryCorp 80 EE 8/12/2006 1.55

Something like this 'should' work for you. (Untested)

SELECT
dt.Customer,
dt.Product,
dt.InvoiceDate
d.Price
FROM InvoiceDetailTable d
JOIN ( SELECT
Customer,
Product,
InvoiceDate = max( InvoiceDate )
FROM InvoiceHeaderTable h
JOIN InvoiceDetailTable d
ON h.InvHeaderID = d.InvHeaderID
GROUP BY
Customer,
Product
) dt
ON ( d.Customer = dt.Customer
AND d.Product = dt.Product
AND d.InvoiceDate = dt.InvoiceDate
)

|||Crapola, didn't check your tables first. So ignore the prevoius exercise in 'egg on my face'.|||

IF there was no more than one invoice per day for a customer, you could, in the derived table (dt), include in the SELECT list:

InvHeaderID = max( InvHeaderID )

And then JOIN ON InvHeaderID instead of the three fields I indicated.

Of course, if there were two invoices for the same customer in a day, that would still not be the correct solution.

|||

Well, most of the time there would only be one invoice. But as is always the case, there can be an exception.

I had also wondered if the rank and partition function in sql server 2005 could apply, then one could just use a select query (if this is possible) to return the # 1 invoice per group but I have found no examples showing this used in a group by and where there are multiple tables involved.

Thanks

smhaig

|||

select

ct.CustomerName as Customer,

ct.CustomerID as CustomerID,

idt.ProductCode as Product,

iht.InvoiceDate as MostRecentInvoiceDate,

idt.Price as Price

from InvoiceDetailTable as idt

join InvoiceHeaderTable as iht

on idt.invheaderID = iht.invheaderID

join CustomerTable as ct

on ct.CustomerID = iht.CustomerID

where not exists

(select 1

from InvoiceDetailTable as idtx

join InvoiceHeaderTable as ihtx

on idtx.InvHeaderID = ihtx.InvHeaderID

where idtx.ProductCode = idt.ProductCode

and ihtx.CustomerID = iht.CustomerID

and ihtx.InvoiceDate > iht.InvoiceDate)

order by

ct.CustomerName,

idt.ProductCode

|||

-- Using SQL Server 2005

set nocount on
set dateformat mdy

create table invoiceHeadertable(invheaderID int,CustomerID int,InvoiceDate datetime)
insert into invoiceHeadertable(invheaderID ,CustomerID ,InvoiceDate )
select 1, 40, '1/1/2006' union all
select 2, 40, '4/1/2006' union all
select 3, 80, '3/1/2006' union all
select 4, 80, '7/1/2006' union all
select 5, 80, '8/12/2006'

create table invoicedetailtable(invdetailID int, invheaderID int, productcode char(2), price decimal(5,2))
insert into invoicedetailtable(invdetailID , invheaderID , productcode , price )
select 11, 1, 'AA', 1.50 union all
select 12, 1, 'BB', 1.30 union all
select 13, 1, 'CC', 1.00 union all
select 21, 2, 'AA', 1.40 union all
select 23, 2, 'CC', 2.00 union all
select 24, 3, 'AA', 2.00 union all
select 25, 3, 'CC', 2.10 union all
select 26, 3, 'EE', 1.10 union all
select 27, 4, 'AA', 1.00 union all
select 28, 4, 'CC', 2.00 union all
select 29, 4, 'EE', 0.99 union all
select 34, 5, 'EE', 1.55

create table CustomerTable(CustomerID int, Customername varchar(10))
insert into CustomerTable(CustomerID , Customername )
select 40, 'johnCorp' union all
select 80, 'maryCorp';

with cte(customer,product,[most recent invoice(for this product)],price,rn)
as (
select c.Customername,
d.productcode,
h.InvoiceDate,
d.price,
rank() over(partition by c.Customername,d.productcode order by h.InvoiceDate desc)
from CustomerTable c
inner join invoiceHeadertable h on h.CustomerID=c.CustomerID
inner join invoicedetailtable d on d.invheaderID=h.invheaderID
)
select customer,
product,
[most recent invoice(for this product)],
price
from CTE
where rn=1
order by customer,product

|||

I want to thank Mark and Ron for their solutions and Arnie for getting me to think about two invoices on the same day for the same product and customer (which do exist actually).

I found these solutions to be on the level of advanced lessons for me to study. I have always had trouble with group by when I needed a unique ID on a table where I was selecting a max or min or first one, etc. on another field in the same table.

I have not found any good examples other than very basic ones for rank and partition so if anyone has a good site let me know. Meanwhile I will study what I have as I now have two great ways to solve my problem.

I did not give a duplicate item with my sample data so I will see how this sql 2005 query deals with this. I seem to remember something about ties and ranking and perhaps I could also use select distinct when I select the rank = 1.

The second standard sql query (Ron's) shows me both invoices when there are 2 on same date. It may be that this is the way the data should be displayed if the prices are different, so I will need to check further on that and see if I can tweak these 2 queries to deal with that.

Thank you all again

smHaig

|||

And my thanks to Mark for demonstrating the more modern solution of the two!

Ron

GROUP BY Question

Given the following tables, I would like to figure out employee (employeeID)
leads the most assignments.
Doing it in two steps works (after a fashion) with a view (as listed below)
but I would like the Assignmentleader ID and the number of assignments that
person leads only for the leader that leads the most assignments and do it
in one query if possible.
CREATE VIEW MostNum AS
SELECT AssignmentLeader,COUNT(AssignmentID) AS NumOF
FROM Assignments
GROUP BY AssignmentLeader
CREATE TABLE Employees
(
EmployeeID INT NOT NULL PRIMARY KEY,
FirstName CHAR (20) NULL,
LastName CHAR (20) NULL,
HireDate DATETIME NULL,
TerminationDate DATETIME NULL,
EmployeeComments CHAR (300) NULL,
SupervisorsID INT NULL,
FOREIGN KEY (SupervisorsID) REFERENCES Employees (EmployeeID)
)
--Employees sample data
INSERT INTO Employees VALUES(101,'James','Hinkle','2006-01-01
00:00:00.000',NULL,'The Big Boss',NULL)
INSERT INTO Employees VALUES(102,'Dave','McCracken','2006-01-04
00:00:00.000',NULL,'VP',101)
INSERT INTO Employees VALUES(103,'Phil','Jensen','2006-01-05
00:00:00.000',NULL,'Accounting Supervisor',101)
INSERT INTO Employees VALUES(104,'Melinda','Carol','2006-01-05
00:00:00.000',NULL,'Finance Supervisor',101)
INSERT INTO Employees VALUES(105,'Daniel','Humphreys','2006-01-06
00:00:00.000',NULL,'MIS Support',101)
INSERT INTO Employees VALUES(106,'Lisa','Nugen','2006-01-06
00:00:00.000',NULL,'Shipping Supplier',101)
INSERT INTO Employees VALUES(107,'Henry','Wallace','2006-01-06
00:00:00.000',NULL,'Shipping PC',106)
INSERT INTO Employees VALUES(108,'Karen','Berrett','2006-01-07
00:00:00.000',NULL,'Secretary',103)
INSERT INTO Employees VALUES(109,'Ben','Pratt','2006-01-08
00:00:00.000','2006-01-09 00:00:00.000','Grunt',104)
INSERT INTO Employees VALUES(110,'Chump','Wegion','2006-01-08
00:00:00.000',NULL,'Warehouse PC',101)
CREATE TABLE Assignments
(
AssignmentID INT NOT NULL PRIMARY KEY,
AssignmentDescription CHAR(60) NULL ,
AssignmentLeader INT NULL
FOREIGN KEY (AssignmentLeader) REFERENCES Employees (EmployeeID)
)
--Assignments sample data
INSERT INTO Assignments VALUES(1,'Run operations of company',105)
INSERT INTO Assignments VALUES(2,'East Coast Marketing Plan',101)
INSERT INTO Assignments VALUES(3,'West Coast Marketing Plan',103)
INSERT INTO Assignments VALUES(4,'Build a new assembly line for Mark V',101)
INSERT INTO Assignments VALUES(5,'Research and develop a new pump
sprayer',102)
INSERT INTO Assignments VALUES(6,'Build new engineering facility',109)SELECT TOP 1 EmployeeID, COUNT(*) FROM Assignments GROUP BY EmployeeID ORDER
BY 2 DESC
"David Olsen" <david.olsen@.usu.edu> wrote in message
news:%23Dn2%23n7QGHA.2176@.TK2MSFTNGP10.phx.gbl...
> Given the following tables, I would like to figure out employee
> (employeeID) leads the most assignments.
>
> Doing it in two steps works (after a fashion) with a view (as listed
> below) but I would like the Assignmentleader ID and the number of
> assignments that person leads only for the leader that leads the most
> assignments and do it in one query if possible.
>
>
>
> CREATE VIEW MostNum AS
>
> SELECT AssignmentLeader,COUNT(AssignmentID) AS NumOF
> FROM Assignments
> GROUP BY AssignmentLeader
>
>
> CREATE TABLE Employees
> (
> EmployeeID INT NOT NULL PRIMARY KEY,
> FirstName CHAR (20) NULL,
> LastName CHAR (20) NULL,
> HireDate DATETIME NULL,
> TerminationDate DATETIME NULL,
> EmployeeComments CHAR (300) NULL,
> SupervisorsID INT NULL,
>
> FOREIGN KEY (SupervisorsID) REFERENCES Employees (EmployeeID)
>
> )
>
>
> --Employees sample data
> INSERT INTO Employees VALUES(101,'James','Hinkle','2006-01-01
> 00:00:00.000',NULL,'The Big Boss',NULL)
> INSERT INTO Employees VALUES(102,'Dave','McCracken','2006-01-04
> 00:00:00.000',NULL,'VP',101)
> INSERT INTO Employees VALUES(103,'Phil','Jensen','2006-01-05
> 00:00:00.000',NULL,'Accounting Supervisor',101)
> INSERT INTO Employees VALUES(104,'Melinda','Carol','2006-01-05
> 00:00:00.000',NULL,'Finance Supervisor',101)
> INSERT INTO Employees VALUES(105,'Daniel','Humphreys','2006-01-06
> 00:00:00.000',NULL,'MIS Support',101)
> INSERT INTO Employees VALUES(106,'Lisa','Nugen','2006-01-06
> 00:00:00.000',NULL,'Shipping Supplier',101)
> INSERT INTO Employees VALUES(107,'Henry','Wallace','2006-01-06
> 00:00:00.000',NULL,'Shipping PC',106)
> INSERT INTO Employees VALUES(108,'Karen','Berrett','2006-01-07
> 00:00:00.000',NULL,'Secretary',103)
> INSERT INTO Employees VALUES(109,'Ben','Pratt','2006-01-08
> 00:00:00.000','2006-01-09 00:00:00.000','Grunt',104)
> INSERT INTO Employees VALUES(110,'Chump','Wegion','2006-01-08
> 00:00:00.000',NULL,'Warehouse PC',101)
>
>
> CREATE TABLE Assignments
> (
> AssignmentID INT NOT NULL PRIMARY KEY,
> AssignmentDescription CHAR(60) NULL ,
> AssignmentLeader INT NULL
> FOREIGN KEY (AssignmentLeader) REFERENCES Employees (EmployeeID)
> )
>
> --Assignments sample data
> INSERT INTO Assignments VALUES(1,'Run operations of company',105)
> INSERT INTO Assignments VALUES(2,'East Coast Marketing Plan',101)
> INSERT INTO Assignments VALUES(3,'West Coast Marketing Plan',103)
> INSERT INTO Assignments VALUES(4,'Build a new assembly line for Mark
> V',101)
> INSERT INTO Assignments VALUES(5,'Research and develop a new pump
> sprayer',102)
> INSERT INTO Assignments VALUES(6,'Build new engineering facility',109)
>
>|||"David Olsen" <david.olsen@.usu.edu> wrote in message
news:%23Dn2%23n7QGHA.2176@.TK2MSFTNGP10.phx.gbl...
> Given the following tables, I would like to figure out employee
> (employeeID) leads the most assignments.
>
> Doing it in two steps works (after a fashion) with a view (as listed
> below) but I would like the Assignmentleader ID and the number of
> assignments that person leads only for the leader that leads the most
> assignments and do it in one query if possible.
>
>
>
> CREATE VIEW MostNum AS
>
> SELECT AssignmentLeader,COUNT(AssignmentID) AS NumOF
> FROM Assignments
> GROUP BY AssignmentLeader
>
>
> CREATE TABLE Employees
> (
> EmployeeID INT NOT NULL PRIMARY KEY,
> FirstName CHAR (20) NULL,
> LastName CHAR (20) NULL,
> HireDate DATETIME NULL,
> TerminationDate DATETIME NULL,
> EmployeeComments CHAR (300) NULL,
> SupervisorsID INT NULL,
>
> FOREIGN KEY (SupervisorsID) REFERENCES Employees (EmployeeID)
>
> )
>
>
> --Employees sample data
> INSERT INTO Employees VALUES(101,'James','Hinkle','2006-01-01
> 00:00:00.000',NULL,'The Big Boss',NULL)
> INSERT INTO Employees VALUES(102,'Dave','McCracken','2006-01-04
> 00:00:00.000',NULL,'VP',101)
> INSERT INTO Employees VALUES(103,'Phil','Jensen','2006-01-05
> 00:00:00.000',NULL,'Accounting Supervisor',101)
> INSERT INTO Employees VALUES(104,'Melinda','Carol','2006-01-05
> 00:00:00.000',NULL,'Finance Supervisor',101)
> INSERT INTO Employees VALUES(105,'Daniel','Humphreys','2006-01-06
> 00:00:00.000',NULL,'MIS Support',101)
> INSERT INTO Employees VALUES(106,'Lisa','Nugen','2006-01-06
> 00:00:00.000',NULL,'Shipping Supplier',101)
> INSERT INTO Employees VALUES(107,'Henry','Wallace','2006-01-06
> 00:00:00.000',NULL,'Shipping PC',106)
> INSERT INTO Employees VALUES(108,'Karen','Berrett','2006-01-07
> 00:00:00.000',NULL,'Secretary',103)
> INSERT INTO Employees VALUES(109,'Ben','Pratt','2006-01-08
> 00:00:00.000','2006-01-09 00:00:00.000','Grunt',104)
> INSERT INTO Employees VALUES(110,'Chump','Wegion','2006-01-08
> 00:00:00.000',NULL,'Warehouse PC',101)
>
>
> CREATE TABLE Assignments
> (
> AssignmentID INT NOT NULL PRIMARY KEY,
> AssignmentDescription CHAR(60) NULL ,
> AssignmentLeader INT NULL
> FOREIGN KEY (AssignmentLeader) REFERENCES Employees (EmployeeID)
> )
>
> --Assignments sample data
> INSERT INTO Assignments VALUES(1,'Run operations of company',105)
> INSERT INTO Assignments VALUES(2,'East Coast Marketing Plan',101)
> INSERT INTO Assignments VALUES(3,'West Coast Marketing Plan',103)
> INSERT INTO Assignments VALUES(4,'Build a new assembly line for Mark
> V',101)
> INSERT INTO Assignments VALUES(5,'Research and develop a new pump
> sprayer',102)
> INSERT INTO Assignments VALUES(6,'Build new engineering facility',109)
>
>
Transact-SQL proprietary version:
SELECT TOP 1 WITH TIES
assignmentleader, COUNT(*) AS numof
FROM Assignments
GROUP BY assignmentleader
ORDER BY COUNT(*) DESC ;
ANSI/ISO Standard SQL version:
SELECT assignmentleader, COUNT(*) AS numof
FROM Assignments
GROUP BY assignmentleader
HAVING COUNT(*)>= ALL
(SELECT COUNT(*)
FROM Assignments
GROUP BY assignmentleader);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:%23hw5rz7QGHA.1096@.TK2MSFTNGP11.phx.gbl...
> "David Olsen" <david.olsen@.usu.edu> wrote in message
> news:%23Dn2%23n7QGHA.2176@.TK2MSFTNGP10.phx.gbl...
> Transact-SQL proprietary version:
> SELECT TOP 1 WITH TIES
> assignmentleader, COUNT(*) AS numof
> FROM Assignments
> GROUP BY assignmentleader
> ORDER BY COUNT(*) DESC ;
>
> ANSI/ISO Standard SQL version:
> SELECT assignmentleader, COUNT(*) AS numof
> FROM Assignments
> GROUP BY assignmentleader
> HAVING COUNT(*)>= ALL
> (SELECT COUNT(*)
> FROM Assignments
> GROUP BY assignmentleader);
>
Thanks dude (and Aaron), you guys rock.

> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

GROUP BY or what else?

Hi All,
I have a set very complicated tables and queries. This is a significantly
simplified schema and one of the subqueries should do the following: This is
an example of the table.
Table tblResponses
---
RespID, PK, int, NOT NULL
UserID int, not null
QiestionID,
Response, varchar(255), not null
RespDate datetime, not null, def=getdate()
The schema is done so that any user can answer same question from 0 to a few
times per day. I need to get only the latest response existing on the
required date using some simple trick. The response can be given on the
@.RequestDate or earlier, it doesn't matter, it should exist on this date.
The basic query is looking like this:
SELECT *
FROM tblResponses
WHERE UserID=@.UserID AND
DATEDIFF(DAY,@.RequestDate,RespDate)<=0
It returns all responses on the date or earlier. How can I get the latest
response only? To use GROUP BY in some subquery doesn't work or I'm doing
something wrong. For example I can write:
SELECT RespDate, Count(RespDate)
FROM tblResponses
WHERE UserID=@.UserID AND
DATEDIFF(DAY,@.RequestDate,RespDate)<=0
GROUP BY RespDate DESC
and get two columns of values - datetimes and number of responses. It will
not work because datetime is not the date and includes the time as well.
Maybe it's easier to group by QuestionID like:
SELECT RespDate, Count(RespDate)
FROM tblResponses
WHERE UserID=@.UserID AND
DATEDIFF(DAY,@.RequestDate,RespDate)<=0
GROUP BY RespDate DESC
Maybe this is the wrong way to go. But what are we having to get the latest
responses from each subgroup assuming that the greater RespID belongs to the
greater response.
Thanks,
Just D."Just D." <no@.spam.please> wrote in message
news:yqOef.93$4v.10@.fed1read03...
> Hi All,
> I have a set very complicated tables and queries. This is a significantly
> simplified schema and one of the subqueries should do the following: This
> is an example of the table.
>
> Table tblResponses
> ---
> RespID, PK, int, NOT NULL
> UserID int, not null
> QiestionID,
> Response, varchar(255), not null
> RespDate datetime, not null, def=getdate()
>
> The schema is done so that any user can answer same question from 0 to a
> few times per day. I need to get only the latest response existing on the
> required date using some simple trick. The response can be given on the
> @.RequestDate or earlier, it doesn't matter, it should exist on this date.
> The basic query is looking like this:
> SELECT *
> FROM tblResponses
> WHERE UserID=@.UserID AND
> DATEDIFF(DAY,@.RequestDate,RespDate)<=0
> It returns all responses on the date or earlier. How can I get the latest
> response only? To use GROUP BY in some subquery doesn't work or I'm doing
> something wrong. For example I can write:
> SELECT RespDate, Count(RespDate)
> FROM tblResponses
> WHERE UserID=@.UserID AND
> DATEDIFF(DAY,@.RequestDate,RespDate)<=0
> GROUP BY RespDate DESC
> and get two columns of values - datetimes and number of responses. It will
> not work because datetime is not the date and includes the time as well.
> Maybe it's easier to group by QuestionID like:
> SELECT RespDate, Count(RespDate)
> FROM tblResponses
> WHERE UserID=@.UserID AND
> DATEDIFF(DAY,@.RequestDate,RespDate)<=0
> GROUP BY RespDate DESC
> Maybe this is the wrong way to go. But what are we having to get the
> latest responses from each subgroup assuming that the greater RespID
> belongs to the greater response.
> Thanks,
> Just D.
>
The best way to specify your problem is to post DDL, sample data and show
your required end result. In the absence of any of those here's an untested
guess:
SELECT respid, userid, questionid, response, respdate
FROM tblResponses AS T
WHERE respdate =
(SELECT MAX(respdate)
FROM tblResponses
WHERE respdate >= @.respdate
AND respdate < DATEADD(DAY,1,@.respdate)
AND userid = @.userid)
AND userid = @.userid ;
Notice that it's generally much more efficient to specify a range for
DATETIMEs rather than try to do date math with the column.
David Portas
SQL Server MVP
--|||Hi David,
You wrote me:

> SELECT respid, userid, questionid, response, respdate
> FROM tblResponses AS T
> WHERE respdate =
> (SELECT MAX(respdate)
> FROM tblResponses
> WHERE respdate >= @.respdate
> AND respdate < DATEADD(DAY,1,@.respdate)
> AND userid = @.userid)
> AND userid = @.userid ;
> Notice that it's generally much more efficient to specify a range for
> DATETIMEs rather than try to do date math with the column.
It returns only one response from the whole table, not the one from each
group if we group by QID that was required, sorry.
Thanks,
Just D.|||Just D,
If you only want the latest response, regardless of QuestionID, then you
are pretty close with your first query: Just add a Top 1 and an order by
clause such as the following:
SELECT top 1 *
FROM tblResponses
WHERE UserID=@.UserID AND DATEDIFF(DAY,@.RequestDate,RespDate)<=0
order by RespDate desc
Hope this helps,
Mark|||Mark,
Thank you for your help. Like the previous answer the query with this
modification returns only one response from the whole bunch of responses.
Maybe the original question wasn't clear.
If we have for example the table:
RespID, PK, int, NOT NULL
UserID int, not null
QiestionID,
Response, varchar(255), not null
RespDate datetime, not null, def=getdate()
and the data in the table is looking like this:
RespID UserID QuestionID Response RespDate
1 1032 1050 'SomeResponse'
'11/16/2005 10:15:12'
2 1032 1052 'SomeResponse'
'11/16/2005 10:15:14'
3 1032 1052 'SomeResponse'
'11/16/2005 10:15:16'
4 1032 1052 'SomeResponse'
'11/16/2005 10:15:19'
5 1032 1051 'SomeResponse'
'11/16/2005 10:15:27'
6 1032 1051 'SomeResponse'
'11/16/2005 10:16:20'
7 1032 1051 'SomeResponse'
'11/16/2005 10:18:20'
8 1032 1050 'SomeResponse'
'11/16/2005 10:28:30'
9 1032 1050 'SomeResponse'
'11/16/2005 10:34:50'
The problem is first - to group by QID, second - find the latest response
for the group (belonging to one QID), return these responses - all
responses, one for each group, in the case above we need to get 3 responses
for QID = 1050, 1051 and 1052 so that the response is the latest for this
group.
SELECT Top 1 *
returns only one response for sure.
Just D.
"mark sullivan" <marksullivan@.rcn.com> wrote in message
news:%23F%23%230hw6FHA.2036@.TK2MSFTNGP14.phx.gbl...
> Just D,
> If you only want the latest response, regardless of QuestionID, then you
> are pretty close with your first query: Just add a Top 1 and an order by
> clause such as the following:
> SELECT top 1 *
> FROM tblResponses
> WHERE UserID=@.UserID AND DATEDIFF(DAY,@.RequestDate,RespDate)<=0
> order by RespDate desc
> Hope this helps,
> Mark
>|||Btw,
I tried this:
SELECT * FROM tblResponses
WHERE RespID IN(
SELECT RespID
FROM tblResponses
WHERE UserID=@.UserID AND DATEDIFF(DAY,@.RespDate,RespDate)<=0
GROUP BY RespID
HAVING RespID=Max(RespID)
)
ORDER BY QID,RespID
and this query returns all responses from the whole table, not only the
latest from each group. That's correct because I used
GROUP BY RespID
but should use
GROUP BY QID
The language didn't allow me to group by QID, aggregate, etc.
Just D.|||Poor specs lead to poor results. Please post DDL, sample data and a
description of expected results.
ML|||The thread is closed, thanks to everybody! The final query is:
SELECT * FROM tblResponses
WHERE RespID IN(
SELECT Max(RespID)
FROM tblResponses
WHERE PtID=@.PtID AND DATEDIFF(DAY,@.RespDate,RespDate)<=0
GROUP BY QID
)
ORDER BY QID,RespID
Just D.|||FIRST, learn netiquette: Please post DDL, so that people do not have
to guess what the keys, constraints, Declarative Referential Integrity,
data types, etc. in your schema are.
In order to do your job for you, we have to guess and decode what you
meant in that personal and very rude pseudo-code you posted.
Sample data is also a good idea, along with clear specifications. It
is very hard to debug code when you do not let us see it.
SECOND: Never use "tbl-" prefixes on table names. This shows us that
you are not and SQL programmer.
THIRD: Why isn't (user_id, question_nbr) the natural key? Why did you
need to construct a "response_id" ; I need a LOGICAL reason, not some
crap about using an IDENTITY column.
FOURTH: the Standard SQL syntax is CURRENT-TIMESTAMP, not getdate().
FIFTH: the GROUP BY clause does not have an ordering option. This
reallllllly tells us you do not write SQL. That is soooo fundamental
and it would have crashed if you ran the code.
SELECT R1.resp_date, COUNT(*)
FROM Responses AS R1
WHERE user_id = @.my_user_id
AND resp_date
= (SELECT MAX(resp_date)
FROM Responses AS R2
WHERE R1.user_id = R2.user_id
AND R1.user_id = R2.user_id);
But you have more problems than this and need to get someone more
experienced to help you with your SQL.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1132197969.434508.50880@.g44g2000cwa.googlegroups.com...
> FOURTH: the Standard SQL syntax is CURRENT-TIMESTAMP, not
getdate().
Ahem . . . that's CURRENT_TIMESTAMP.
Sincerely,
Chris O.