Making an leauge series tabel.

Hi
Running on SQL Server2005
I have an tabel Match, TeamInGroup, Team, Group and Series.

Want following resutlt:

Tabell
Lag S V O F TOTAL P
Grupp A
BK rnen 2 2 0 0 30-10 4
Wxns BC 3 2 0 1 35-25 4
BK Kaskad 2 1 0 1 20-20 2
BK Bgen 3 1 0 2 29-31 2
BK Glam 3 1 0 2 24-36 2
IFK Norrkping BF 3 1 0 2 22-38 2

Grupp B
Uppsala BC 90 3 2 1 0 35-25 5
Ludvika BK 2 2 0 0 25-14 4
Sundbybergs IK F 3 1 1 1 34-26 3
LBK Hudik 3 1 0 2 23-36 2
Domnarvets BS 2 0 1 1 19-21 1
rta IF 3 0 1 2 23-37 1

Grupp C
Stureby BK 3 2 0 1 39-21 4
Tureberg IF 2 2 0 0 28-12 4
BK Stallis 3 2 0 1 28-31 4
BK Amiki 3 1 0 2 28-31 2
Djurgrdens IF 2 1 0 1 15-25 2
BK Brio 3 0 0 3 21-39 0

Tabel Match:

USE [Bowlingserier]
GO
/****** Object: Table [dbo].[Match] Script Date: 09/19/2007 17:25:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Match](
[MatchId] [int] NOT NULL,
[Matchstart] [datetime] NULL,
[LagIdHemma] [int] NOT NULL,
[LagIdBorta] [int] NOT NULL,
[Resultathemma] [int] NULL,
[ResultatBorta] [int] NULL,
CONSTRAINT [PK_Match_1] PRIMARY KEY CLUSTERED
(
[MatchId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Match] WITH CHECK ADD CONSTRAINT [FK_Match_Team] FOREIGN KEY([LagIdHemma])
REFERENCES [dbo].[Team] ([TeamId])
GO
ALTER TABLE [dbo].[Match] CHECK CONSTRAINT [FK_Match_Team]
GO
ALTER TABLE [dbo].[Match] WITH CHECK ADD CONSTRAINT [FK_Match_TeamInGroup] FOREIGN KEY([LagIdHemma])
REFERENCES [dbo].[TeamInGroup] ([TeamId])
GO
ALTER TABLE [dbo].[Match] CHECK CONSTRAINT [FK_Match_TeamInGroup]

Tabel Team:

USE [Bowlingserier]
GO
/****** Object: Table [dbo].[Team] Script Date: 09/19/2007 17:28:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Team](
[TeamId] [int] NOT NULL,
[Name] [varchar](20) NOT NULL,
[GroupId] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Team] PRIMARY KEY CLUSTERED
(
[TeamId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Tabel TeamInGroup:

USE [Bowlingserier]
GO
/****** Object: Table [dbo].[TeamInGroup] Script Date: 09/19/2007 17:27:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TeamInGroup](
[TeamId] [int] NOT NULL,
[GroupID] [int] NOT NULL,
CONSTRAINT [PK_TeamInGroup_1] PRIMARY KEY CLUSTERED
(
[TeamId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[TeamInGroup] WITH CHECK ADD CONSTRAINT [FK_TeamInGroup_Grupp] FOREIGN KEY([GroupID])
REFERENCES [dbo].[Grupp] ([GroupId])
GO
ALTER TABLE [dbo].[TeamInGroup] CHECK CONSTRAINT [FK_TeamInGroup_Grupp]

Tabel Group:

USE [Bowlingserier]
GO
/****** Object: Table [dbo].[Grupp] Script Date: 09/19/2007 17:29:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Grupp](
[GroupId] [int] NOT NULL,
[SeriesId] [int] NOT NULL,
[Namn] [varchar](50) NULL,
CONSTRAINT [PK_Grupp_1] PRIMARY KEY CLUSTERED
(
[GroupId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Grupp] WITH CHECK ADD CONSTRAINT [FK_Grupp_Serier] FOREIGN KEY([SeriesId])
REFERENCES [dbo].[Serier] ([SeriesId])
GO
ALTER TABLE [dbo].[Grupp] CHECK CONSTRAINT [FK_Grupp_Serier]

Tabel Series

USE [Bowlingserier]
GO
/****** Object: Table [dbo].[Serier] Script Date: 09/19/2007 17:30:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Serier](
[SeriesId] [int] NOT NULL,
CONSTRAINT [PK_Serier] PRIMARY KEY CLUSTERED
(
[SeriesId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

This is what i dot so far:

SELECT tg.GroupID, t.Name, COUNT(m.ResultatHemma) AS S,
CASE
WHEN m.ResultatHemma - m.ResultatBorta > 0 THEN
COUNT(m.ResultatHemma)
ELSE 0
END AS V,
CASE
WHEN m.ResultatHemma - m.ResultatBorta = 0 THEN
COUNT(m.ResultatHemma)
ELSE 0
END AS O,
CASE
WHEN m.ResultatHemma - m.ResultatBorta < 0 THEN
COUNT(m.ResultatHemma)
ELSE 0
END AS F,
SUM(m.ResultatHemma) AS Hemma, SUM(m.ResultatBorta) AS Borta,
SUM(m.ResultatHemma - m.ResultatBorta) AS Diff,

CASE
WHEN m.ResultatHemma - m.ResultatBorta = 0 THEN 1
ELSE
CASE
WHEN m.ResultatHemma - m.ResultatBorta > 0 THEN 2
ELSE 0
END END AS P
FROM Match AS m INNER JOIN
Team AS t ON m.LagIdHemma = t.TeamId INNER JOIN
TeamInGroup AS tg ON m.LagIdHemma = tg.TeamId
WHERE (m.MatchId = m.MatchId) AND (tg.GroupID = 2 OR tg.GroupID = 1 OR tg.GroupID = 3)
GROUP BY t.Name, tg.GroupID, m.Resultathemma, m.Resultatborta
ORDER BY P DESC, Diff DESC, S DESC

I can't figure out howto group by t.Name and team in Group A,B, and C

Here are some data From Match:

MatchId Matchstart LagIdHemma LagIdBorta Resultathemma ResultatBorta
---- -------- ---- ---- ---- ----
700201001 2007-09-08 10:00:00.000 33768 159120 16 4
700201002 2007-09-08 15:00:00.000 33744 159120 16 6
700201003 2007-09-08 11:00:00.000 33894 33407 16 4
700201004 2007-09-08 14:00:00.000 33874 33407 11 9
700201005 2007-09-08 11:00:00.000 33737 32186 10 10
700201006 2007-09-08 16:20:00.000 30896 32186 15 5
700201007 2007-09-08 11:00:00.000 33286 42031 11 9
700201008 2007-09-08 15:00:00.000 33290 42031 14 5
700201009 2007-09-08 11:00:00.000 33628 33722 13 7
700201010 2007-09-08 15:00:00.000 33684 33722 15 5
700201011 2007-09-08 11:00:00.000 43635 33705 9 11
700201012 2007-09-08 15:00:00.000 31346 33705 16 4
700201013 2007-09-15 11:00:00.000 159120 33768 8 12
700201014 2007-09-15 16:00:00.000 33407 33768 11 9
700201015 2007-09-15 11:00:00.000 33874 33744 9 11
700201016 2007-09-15 13:00:00.000 33894 33744 14 6
700201017 2007-09-15 10:00:00.000 32186 33737 12 8
700201018 2007-09-15 16:00:00.000 42031 33737 13 7
700201019 2007-09-15 11:00:00.000 33290 30896 9 11
700201020 2007-09-15 15:40:00.000 33286 30896 10 10
700201021 2007-09-15 11:00:00.000 33722 33628 9 10
700201022 2007-09-15 16:00:00.000 33705 33628 12 8
700201023 2007-09-15 11:00:00.000 31346 33684 8 12
700201024 2007-09-15 15:00:00.000 43635 33684 16 4

Team:
TeamId Name GroupId
---- ------- ----
30896 Sundbybergs IK F 2
31346 Turebergs IF 3
32186 rta IF 2
33286 Domnarvets BS 2
33290 Ludvika BK 2
33407 BK Glam 1
33628 BK Amiki 3
33684 Stureby BK 3
33705 BK Brio 3
33722 BK Stallis 3
33737 Uppsala BC90 2
33744 BK Bgen 1
33768 Wxns BC 1
33874 BK Kaskad 1
33894 BK rnen 1
42031 LBK Hudik 2
43635 Djurgrdens IF 3
159120 IFK Norrkping 1

(18 row(s) affected)

TeamInGroup:
TeamId GroupID
---- ----
30896 2
31346 3
32186 2
33286 2
33290 2
33407 1
33628 3
33684 3
33705 3
33722 3
33737 2
33744 1
33768 1
33874 1
33894 1
42031 2
43635 3
159120 1

(18 row(s) affected)

Group:
GroupId SeriesId Namn
---- ---- ----------------
1 48967 Grupp A
2 48967 Grupp B
3 48967 Grupp C

(3 row(s) affected)
And Serier:
SeriesId
----
48967

(1 row(s) affected)

Best regards

GertenYour create table and data do not agree. You have
CREATE TABLE [dbo].[Team](
[TeamId] [int] NOT NULL,
[Name] [varchar](20) NOT NULL,
[GroupId] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_Team] PRIMARY KEY CLUSTERED
([TeamId] ASC

But you have duplicate GroupId in Team

Why do you have a TeamInGroup table? The relationship between TeamId and GroupId is already defined in the Team table.|||I leave joining to the other tables up to you as you still need to make a decision on the design of the other tables in your assignment.

select TeamId ,count(*) S
, sum(case when PointsFor>PointsAgainst then 1 else 0 end) V
, sum(case when PointsFor=PointsAgainst then 1 else 0 end) O
, sum(case when PointsFor<PointsAgainst then 1 else 0 end) F
, str(sum(PointsFor),3,0)+'-'+convert(varchar(3),sum(PointsAgainst)) TOTAL
, sum(case when PointsFor>PointsAgainst then 2
else case when PointsFor=PointsAgainst then 1 else 0 end
end) P
from (
select MatchId ,LagIdHemma as TeamId , Resultathemma as PointsFor, ResultatBorta as PointsAgainst
from Match
union all
select MatchId ,LagIdBorta as TeamId , ResultatBorta as PointsFor, Resultathemma as PointsAgainst
from Match ) view1
group by TeamId|||TeamInGroup is an suggestion from an erlier test. Now i´ll use as you say in Team.
When i Script Table as CREATE TO i just clip the text and put it in here...
We are now nearly there, however the result aren´t quite well:

TeamId S V O F TOTAL P
---- ---- ---- ---- ---- --- --
30896 3 2 1 0 36-24 5
33768 3 2 0 1 37-23 4
33744 3 2 0 1 33-29 4
33684 3 2 0 1 31-29 4
33628 3 2 0 1 31-28 4
33894 2 2 0 0 30-10 4
33705 3 2 0 1 27-33 4
32186 3 1 1 1 27-33 3
33286 2 1 1 0 21-19 3
42031 3 1 0 2 27-32 2
43635 2 1 0 1 25-15 2
33407 3 1 0 2 24-36 2
31346 2 1 0 1 24-16 2
33290 2 1 0 1 23-16 2
33874 2 1 0 1 20-20 2
33737 3 0 1 2 25-35 1
33722 3 0 0 3 21-38 0
159120 3 0 0 3 18-44 0

(18 row(s) affected)

Example TeamID 33737 has played one match home All square 10 - 10
And 2 matches Away with wictory 12 - 8 And 13 - 7
That makes:
33737 3 2 1 0 35-25 5
And TeamId 30896 1 Home Victory, 1 Allsquare and one lost Away:
30896 3 1 1 1 34-26 3

Regards

Gert|||You need to explain more, how do you know who the winner is?
Lets take these to for example
Lag S V O F TOTAL P
Wxns BC (33768) 3 2 0 1 35-25 4
IFK Norrkping BF (159120) 3 1 0 2 22-38 2

MatchId LagIdHemma LagIdBorta Resultathemma ResultatBorta
---- ---- ---- ---- ----
700201001 33768 159120 16 4 win lost
700201013 159120 33768 8 12 lost win
700201014 33407 33768 11 9 win
To agree with the points 16+8+11=35 25=4+12+9

700201002 33744 159120 16 6 lost

But how do you now get 22-38 ?
4+12+6=22 but 16+8+16=40|||Sorry for my misstake:
I did't get all values in tabel match:
Here is the new updated tabel:
MatchNr Tid Hemma Borta HemmaRes BortaRes
700201001 2007-09-08 10:00 33768 159120 16 4
700201003 2007-09-08 11:00 33894 33407 16 4
700201005 2007-09-08 11:00 33737 32186 10 10
700201007 2007-09-08 11:00 33286 42031 9 11
700201009 2007-09-08 11:00 33628 33722 7 13
700201011 2007-09-08 11:00 43635 33705 11 9
700201004 2007-09-08 14:00 33874 33407 9 11
700201002 2007-09-08 15:00 33744 159120 14 6
700201008 2007-09-08 15:00 33290 42031 14 5
700201010 2007-09-08 15:00 33684 33722 15 5
700201012 2007-09-08 15:00 31346 33705 16 4
700201006 2007-09-08 16:20 30896 32186 15 5
700201017 2007-09-15 10:00 32186 33737 8 12
700201013 2007-09-15 11:00 159120 33768 12 8
700201015 2007-09-15 11:00 33874 33744 11 9
700201019 2007-09-15 11:00 33290 30896 11 9
700201021 2007-09-15 11:00 33722 33628 10 9
700201023 2007-09-15 11:00 31346 33684 12 8
700201016 2007-09-15 13:00 33894 33744 14 6
700201024 2007-09-15 15:00 43635 33684 4 16
700201020 2007-09-15 15:40 33286 30896 10 10
700201014 2007-09-15 16:00 33407 33768 9 11
700201018 2007-09-15 16:00 42031 33737 7 13
700201022 2007-09-15 16:00 33705 33628 8 12
700201027 2007-09-22 10:00 33407 33874 10 10
700201031 2007-09-22 10:00 42031 33290 5 14
700201025 2007-09-22 11:00 33744 33894 10 9
700201029 2007-09-22 11:00 30896 33286 12 8
700201033 2007-09-22 11:00 33684 43635 12 8
700201035 2007-09-22 11:00 33705 33684 8 12
700201034 2007-09-22 15:00 33628 43635 11 9
700201026 2007-09-22 16:00 33768 33894 12 8
700201030 2007-09-22 16:00 33737 33286 10 9
700201032 2007-09-22 16:00 32186 33290 14 6
700201028 2007-09-22 16:00 159120 33874 11 9
700201036 2007-09-22 16:00 33722 31346 13 7

So in the new updated tabel TeamId 33768 and TeamId 159120 have:

700201001 2007-09-08 10:00 33768 159120 16 4
700201013 2007-09-15 11:00 159120 33768 12 8
700201014 2007-09-15 16:00 33407 33768 9 11
700201026 2007-09-22 16:00 33768 33894 12 8
And for IFK Norrköping BF
700201001 2007-09-08 10:00 33768 159120 16 4
700201002 2007-09-08 15:00 33744 159120 14 6
700201013 2007-09-15 11:00 159120 33768 12 8
700201028 2007-09-22 16:00 159120 33874 11 9

33768 PointsFor 16+8+11+12=47 And PointsAgainst 4+12+9+8=33
159120 PointsFor 4+6+12+11=43 And PointsAgainst 16+14+8+9=47

That makes for Wåxnäs BC and IFK Norrköping F following result:

Våxnäs BC 4 3 0 1 47-33 6
IFK Norrköping BF 4 2 0 2 33-47 4

Hope you can get over that i cause you trouble.
Gerten|||I take it that the query now works for you|||This is how it look in the Swedish bowling federation webpage:

http://www.swebowl.se/table.aspx?TournamentId=48967

I just want the same in our own website.

Best regards
Gert:beer:|||But your match data is not the same as on
http://www.swebowl.se/Table.aspx?WCI=wiFixtureList&TournamentId=48967
11:00 0700201035 BK Brio - Tureberg IF 8 - 12 Nykpings Bowlinghall
but you have
700201035 2007-09-22 11:00 33705 33684 8 12
BK Brio - Stureby BK
___________________________________

Edit1: Added sample data and query below
Youll need a team table that hold the teams info like address, homesite, email, phone, etc. and then use a teamid in the related tables, but for easy copy and past of data I chose to use the name as the id in below code.

create table #match (
matchdate datetime, matchid int, team varchar(20), vsteam varchar(20), score int, vsscore int) insert into #match select
------ ---- ------ ------ ---- ----
--Omgng 2
'2007-09-08 10:00', 0700201001 ,'Wxns BC ','IFK Norrkping BF',16 , 4 union all select -- Njesfabriken
'2007-09-08 11:00', 0700201009 ,'BK Amiki ','BK Stallis ', 7 , 13 union all select -- Hgdalens Bowlinghall
'2007-09-08 11:00', 0700201003 ,'BK rnen ','BK Glam ',16 , 4 union all select -- Vilbergen Bowlinghall
'2007-09-08 11:00', 0700201011 ,'Djurgrdens IF ','BK Brio ',11 , 9 union all select -- Vrby Bowlinghall
'2007-09-08 11:00', 0700201007 ,'Domnarvets BS ','LBK Hudik ', 9 , 11 union all select -- Maserhallen - Bowling
'2007-09-08 11:00', 0700201005 ,'Uppsala BC 90 ','rta IF ',10 , 10 union all select -- Fyrishofs Bowling
'2007-09-08 14:00', 0700201004 ,'BK Kaskad ','BK Glam ', 9 , 11 union all select -- Vilbergen Bowlinghall
'2007-09-08 15:00', 0700201002 ,'BK Bgen ','IFK Norrkping BF',14 , 6 union all select -- WB Bowling
'2007-09-08 15:00', 0700201008 ,'Ludvika BK ','LBK Hudik ',14 , 5 union all select -- Ludvika Bowlinghall
'2007-09-08 15:00', 0700201010 ,'Stureby BK ','BK Stallis ',15 , 5 union all select -- Bowl-o-Rama/Mlarhallen
'2007-09-08 15:00', 0700201012 ,'Tureberg IF ','BK Brio ',16 , 4 union all select -- Sollentuna Bowlinghall
'2007-09-08 16:20', 0700201006 ,'Sundbybergs IK F ','rta IF ',15 , 5 union all select -- Sundbybergs Bowlinghall
--Omgng 3
'2007-09-15 10:00', 0700201017 ,'rta IF ','Uppsala BC 90 ', 8 , 12 union all select -- Sandvikens Bowlingcenter
'2007-09-15 11:00', 0700201015 ,'BK Kaskad ','BK Bgen ',11 , 9 union all select -- Vilbergen Bowlinghall
'2007-09-15 11:00', 0700201021 ,'BK Stallis ','BK Amiki ',10 , 9 union all select -- Strngns Bowlinghall
'2007-09-15 11:00', 0700201013 ,'IFK Norrkping BF','Wxns BC ',12 , 8 union all select -- Hugo Bowling
'2007-09-15 11:00', 0700201019 ,'Ludvika BK ','Sundbybergs IK F ',11 , 9 union all select -- Ludvika Bowlinghall
'2007-09-15 11:00', 0700201023 ,'Tureberg IF ','Stureby BK ',12 , 8 union all select -- Sollentuna Bowlinghall
'2007-09-15 13:00', 0700201016 ,'BK rnen ','BK Bgen ',14 , 6 union all select -- Vilbergen Bowlinghall
'2007-09-15 15:00', 0700201024 ,'Djurgrdens IF ','Stureby BK ', 4 , 16 union all select -- Vrby Bowlinghall
'2007-09-15 15:40', 0700201020 ,'Domnarvets BS ','Sundbybergs IK F ',10 , 10 union all select -- Maserhallen - Bowling
'2007-09-15 16:00', 0700201022 ,'BK Brio ','BK Amiki ', 8 , 12 union all select -- Nykpings Bowlinghall
'2007-09-15 16:00', 0700201014 ,'BK Glam ','Wxns BC ', 9 , 11 union all select -- rebro Strike & Co
'2007-09-15 16:00', 0700201018 ,'LBK Hudik ','Uppsala BC 90 ', 7 , 13 union all select -- Hudiksvalls Bowlinghall
--Omgng 4
'2007-09-22 10:00', 0700201027 ,'BK Glam ','BK Kaskad ',10 , 10 union all select -- rebro Strike & Co
'2007-09-22 10:00', 0700201031 ,'LBK Hudik ','Ludvika BK ', 5 , 14 union all select -- Hudiksvalls Bowlinghall
'2007-09-22 11:00', 0700201035 ,'BK Brio ','Tureberg IF ', 8 , 12 union all select -- Nykpings Bowlinghall
'2007-09-22 11:00', 0700201025 ,'BK Bgen ','BK rnen ',10 , 9 union all select -- WB Bowling
'2007-09-22 11:00', 0700201033 ,'Stureby BK ','Djurgrdens IF ',12 , 8 union all select -- Bowl-o-Rama/Mlarhallen
'2007-09-22 11:00', 0700201029 ,'Sundbybergs IK F ','Domnarvets BS ',12 , 8 union all select -- Sundbybergs Bowlinghall
'2007-09-22 15:00', 0700201034 ,'BK Amiki ','Djurgrdens IF ',11 , 9 union all select -- Hgdalens Bowlinghall
'2007-09-22 16:00', 0700201036 ,'BK Stallis ','Tureberg IF ',13 , 7 union all select -- Strngns Bowlinghall
'2007-09-22 16:00', 0700201028 ,'IFK Norrkping BF','BK Kaskad ',11 , 9 union all select -- Hugo Bowling
'2007-09-22 16:00', 0700201030 ,'Uppsala BC 90 ','Domnarvets BS ',10 , 9 union all select -- Fyrishofs Bowling
'2007-09-22 16:00', 0700201026 ,'Wxns BC ','BK rnen ',12 , 8 union all select -- Njesfabriken
'2007-09-22 16:00', 0700201032 ,'rta IF ','Ludvika BK ',14 , 6 -- Sandvikens Bowlingcenter

create table #teamgroup (
groupid char(1), team varchar(20)) insert into #teamgroup select
----- ------
'A' ,'Wxns BC ' union all select
'A' ,'BK rnen ' union all select
'A' ,'BK Bgen ' union all select
'A' ,'IFK Norrkping BF' union all select
'A' ,'BK Kaskad ' union all select
'A' ,'BK Glam ' union all select
--
'B' ,'Uppsala BC 90 ' union all select
'B' ,'Ludvika BK ' union all select
'B' ,'Sundbybergs IK F ' union all select
'B' ,'rta IF ' union all select
'B' ,'LBK Hudik ' union all select
'B' ,'Domnarvets BS ' union all select
--
'C' ,'Stureby BK ' union all select
'C' ,'Tureberg IF ' union all select
'C' ,'BK Stallis ' union all select
'C' ,'BK Amiki ' union all select
'C' ,'Djurgrdens IF ' union all select
'C' ,'BK Brio '

select b.groupid, a.team, count(*) played
,count(case when points=2 then points end) won
,count(case when points=1 then points end) draw
,count(case when points=0 then points end) lost
,sum(score) pointsfor
,sum(vsscore) pointsaginst
,sum(points ) points
from (
select team ,score ,vsscore,points=sign(score-vsscore)+1 from #match
union all
select vsteam,vsscore,score ,points=sign(vsscore-score)+1 from #match
) a join #teamgroup b on a.team=b.team
group by b.groupid, a.team
order by b.groupid, a.points desc, 7 desc, 8

drop table #match
drop table #teamgroup

Edit2: For those interested I've attached <t1.gif> a picture of what the league table looked like on 28 Sep 2007|||Hi pdreyer.

Thanks for all your help. But I have to write:
ORDER BY order by b.groupid, points desc, 7 desc, 8
instead of a.points, get an error.

Can't explain why the figurs are diffrent more that when i convert the table from the swebowl site to Excel the result columg get an date format and change it.

So now i got the final SP for my tabel did some changes and put:
So i can sort out played matches.
WHERE (score > 0) AND (vsscore > 0)

Now i'll have to get an better program to convert from Swebowl bowlingsite.

Once again thanks,:beer:|||But I have to write:
ORDER BY order by b.groupid, points desc, 7 desc, 8
instead of a.points, get an error.
Indeed 2005 is not as tolerant as 2000 (what I used) when it comes to a mismatch of an aggregate function

use one of these instead
order by b.groupid, 9 desc, 7 desc, 8
order by groupid, points desc, pointsfor desc, pointsaginst
So i can sort out played matches.
WHERE (score > 0) AND (vsscore > 0)
Just make sure that the match can never be declared a 0 - 0 draw when a match was not played due to ?an act nature?|||No problem, when an bowlingmatch is draw the result is 10 - 10. They played about 20 p. so the result can be from 20 - 0 to 10 - 10.
Do you knew any good program that convert from txt to INSERT INTO in db?

Gert|||http://en.wikipedia.org/wiki/AWK_(programming_language)
http://en.wikipedia.org/wiki/Sed

PS. What does your data file look like? Maybe you can use bcp with a bcp format file.|||For the moment, i just select and copy from the site Bowling federation got.

If i want an list of matches in an specific weekend.
For example Omgång 4 is played Sat 07-09-22 to Sun 07-09-23. And in Tables Match i got matchId 700201025 to 700201036 with matchdate 07-09-22.

SELECT Omgångar.OmgångId, Match.MatchId, Team.team, Team1.team AS vsstem, Match.score, Match.vsscore
FROM Team1 INNER JOIN
Match ON Team1.TeamId = Match.vsteam INNER JOIN
Team ON Match.team = Team.TeamId RIGHT OUTER JOIN
Omgångar ON Match.matchdate = Omgångar.DateStart
WHERE OmgångId = 4
The result i got is:
OmgångId MatchId team vsstem score vsscore
---- ---- ------- ------- ---- ----
4 NULL NULL NULL NULL NULL

(1 row(s) affected)
I want it like:
700201025 2007-09-22 11:00:00 90581 90584 10 9
700201026 2007-09-22 16:00:00 90647 90584 12 8
700201027 2007-09-22 10:00:00 90587 89364 10 10
700201028 2007-09-22 16:00:00 159913 89364 11 9
700201029 2007-09-22 11:00:00 107105 90572 12 8
700201030 2007-09-22 16:00:00 90659 90572 10 9
700201031 2007-09-22 10:00:00 90494 90484 5 14
700201032 2007-09-22 16:00:00 90664 90484 14 6
700201033 2007-09-22 11:00:00 90577 90579 12 8
700201034 2007-09-22 15:00:00 89371 90579 11 9
700201035 2007-09-22 11:00:00 89374 90507 8 12
700201036 2007-09-22 16:00:00 90637 90507 13 7

This is an cut from table Match but i JOIN the team and vsteam with Team AND Team1

Hope You understand my issue :rolleyes:|||My crystal ball indicate that Omgngar.DateStart doesn't have a time
If you don't specify a time it defaults to midnight.
Since no match start at midnight you get no result
select where e.g.
match.matchdate between
OmgngarDate and dateadd(ms,86399996,OmgngarDate)|||I have to put in an row Omgngar in table Match
And here are my SELECT;)

ALTER PROCEDURE UBC90GetSerie
@.SerieOmgng INT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT Match.MatchId, Match.matchdate AS MatchStart, Team.team AS Hemma, Team1.team AS Borta, Match.score, Match.vsscore
FROM Match INNER JOIN
Team ON Match.team = Team.TeamId INNER JOIN
(SELECT TeamId, team, GroupId
FROM Team AS Team_1) AS Team1 ON Match.vsteam = Team1.TeamId
WHERE (Match.Omgng = @.SerieOmgng)
ORDER BY MatchStart

MatchId MatchStart Hemma Borta score vsscore
---- -------- ------- ------- ---- ----
700201027 2007-09-22 10:00:00.000 BK Glam BK Kaskad 10 10
700201031 2007-09-22 10:00:00.000 LBK Hudik Ludvika BK 5 14
700201029 2007-09-22 11:00:00.000 Sundbybergs IK F Domnarvets BS 12 8
700201025 2007-09-22 11:00:00.000 BK Bgen BK rnen 10 9
700201033 2007-09-22 11:00:00.000 Stureby BK Djurgrdens IF 12 8
700201035 2007-09-22 11:00:00.000 BK Brio Tureberg IF 8 12
700201034 2007-09-22 15:00:00.000 BK Amiki Djurgrdens IF 11 9
700201036 2007-09-22 16:00:00.000 BK Stallis Tureberg IF 13 7
700201026 2007-09-22 16:00:00.000 Wxns BC BK rnen 12 8
700201028 2007-09-22 16:00:00.000 IFK Norrkping BF BK Kaskad 11 9
700201030 2007-09-22 16:00:00.000 Uppsala BC 90 Domnarvets BS 10 9
700201032 2007-09-22 16:00:00.000 rta IF Ludvika BK 14 6

Best regards

Gert