Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2007
    Posts
    16

    Unanswered: Making an leauge series tabel.

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

    Want following resutlt:
    Code:
    Tabell 
    Lag                        S     V     O     F     TOTAL   P 
    Grupp A 
    BK Örnen                2     2     0      0     30-10    4 
    Wåxnäs BC             3     2     0      1     35-25    4 
    BK Kaskad              2     1     0      1     20-20    2 
    BK Bågen              3      1     0      2     29-31    2 
    BK Glam                3      1     0     2     24-36     2 
    IFK Norrköping 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 
    Djurgårdens IF 2        1      0       1    15-25   2 
    BK Brio           3        0      0       3     21-39  0
    Tabel Match:
    Code:
    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:
    Code:
    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:
    Code:
    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:
    Code:
    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
    Code:
    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:
    Code:
    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:
    Code:
    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:
    Code:
    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 Bågen             1
    33768       Wåxnäs BC            1
    33874       BK Kaskad            1
    33894       BK Örnen             1
    42031       LBK Hudik            2
    43635       Djurgårdens IF       3
    159120      IFK Norrköping       1
    
    (18 row(s) affected)
    TeamInGroup:
    Code:
    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:
    Code:
    GroupId     SeriesId    Namn
    ----------- ----------- --------------------------------------------------
    1           48967       Grupp A
    2           48967       Grupp B
    3           48967       Grupp C
    
    (3 row(s) affected)
    And Serier:
    Code:
    SeriesId
    -----------
    48967
    
    (1 row(s) affected)
    Best regards

    Gerten

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Your 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.
    Last edited by pdreyer; 09-20-07 at 04:35.

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    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.

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

  4. #4
    Join Date
    Sep 2007
    Posts
    16

    Thanks for your response!

    TeamInGroup is an suggestion from an erlier test. Now i&#180;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&#180;t quite well:

    Code:
    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
    Last edited by Gerten; 09-20-07 at 17:58.

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    You need to explain more, how do you know who the winner is?
    Lets take these to for example
    Code:
    Lag                          S     V     O     F     TOTAL   P 
    Wåxnäs BC (33768)            3     2     0     1     35-25   4 
    IFK Norrköping 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

  6. #6
    Join Date
    Sep 2007
    Posts
    16

    Some changes in the table:

    Sorry for my misstake:
    I did't get all values in tabel match:
    Here is the new updated tabel:
    Code:
    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:
    Code:
    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&#246;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&#229;xn&#228;s BC and IFK Norrk&#246;ping F following result:
    Code:
    V&#229;xn&#228;s BC            4  3  0  1  47-33  6
    IFK Norrk&#246;ping BF    4  2  0  2  33-47  4
    Hope you can get over that i cause you trouble.
    Gerten
    Last edited by Gerten; 09-27-07 at 02:32.

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    I take it that the query now works for you

  8. #8
    Join Date
    Sep 2007
    Posts
    16

    Not yet!

    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

  9. #9
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    But your match data is not the same as on
    http://www.swebowl.se/Table.aspx?WCI...namentId=48967
    Code:
    11:00 0700201035  BK Brio - Tureberg IF 8 - 12 Nyköpings Bowlinghall
    but you have
    Code:
    700201035	2007-09-22 11:00	33705	33684	8	12
                                          BK Brio - Stureby BK
    ___________________________________

    Edit1: Added sample data and query below
    You’ll need a team table that hold the team’s 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.
    Code:
    create table #match (
    matchdate datetime, matchid int, team varchar(20),  vsteam varchar(20), score int, vsscore int) insert into #match select
    ------------------ ------------ -----------------   ------------------  ---------- -------------
    --Omgång 2 
    '2007-09-08 10:00', 0700201001 ,'Wåxnäs BC        ','IFK Norrköping BF',16        ,  4 union all select -- Nöjesfabriken 
    '2007-09-08 11:00', 0700201009 ,'BK Amiki         ','BK Stallis       ', 7        , 13 union all select -- Högdalens Bowlinghall 
    '2007-09-08 11:00', 0700201003 ,'BK Örnen         ','BK Glam          ',16        ,  4 union all select -- Vilbergen Bowlinghall 
    '2007-09-08 11:00', 0700201011 ,'Djurgårdens IF   ','BK Brio          ',11        ,  9 union all select -- Vårby 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 Bågen         ','IFK Norrköping 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/Mälarhallen 
    '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 
    --Omgång 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 Bågen         ',11        ,  9 union all select -- Vilbergen Bowlinghall 
    '2007-09-15 11:00', 0700201021 ,'BK Stallis       ','BK Amiki         ',10        ,  9 union all select -- Strängnäs Bowlinghall 
    '2007-09-15 11:00', 0700201013 ,'IFK Norrköping BF','Wåxnäs 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 Bågen         ',14        ,  6 union all select -- Vilbergen Bowlinghall 
    '2007-09-15 15:00', 0700201024 ,'Djurgårdens IF   ','Stureby BK       ', 4        , 16 union all select -- Vårby 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 -- Nyköpings Bowlinghall 
    '2007-09-15 16:00', 0700201014 ,'BK Glam          ','Wåxnäs 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 
    --Omgång 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 -- Nyköpings Bowlinghall 
    '2007-09-22 11:00', 0700201025 ,'BK Bågen         ','BK Örnen         ',10        ,  9 union all select -- WB Bowling 
    '2007-09-22 11:00', 0700201033 ,'Stureby BK       ','Djurgårdens IF   ',12        ,  8 union all select -- Bowl-o-Rama/Mälarhallen 
    '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         ','Djurgårdens IF   ',11        ,  9 union all select -- Högdalens Bowlinghall 
    '2007-09-22 16:00', 0700201036 ,'BK Stallis       ','Tureberg IF      ',13        ,  7 union all select -- Strängnäs Bowlinghall 
    '2007-09-22 16:00', 0700201028 ,'IFK Norrköping 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 ,'Wåxnäs BC        ','BK Örnen         ',12        ,  8 union all select -- Nöjesfabriken 
    '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'            ,'Wåxnäs BC        ' union all select
    'A'            ,'BK Örnen         ' union all select
    'A'            ,'BK Bågen         ' union all select
    'A'            ,'IFK Norrköping 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'            ,'Djurgårdens 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
    Attached Thumbnails Attached Thumbnails t1.gif  
    Last edited by pdreyer; 09-28-07 at 03:26. Reason: Added t1.gif

  10. #10
    Join Date
    Sep 2007
    Posts
    16

    Thumbs up Thanks for all your help

    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.
    Code:
    WHERE (score > 0) AND (vsscore > 0)
    Now i'll have to get an better program to convert from Swebowl bowlingsite.

    Once again thanks,

  11. #11
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by Gerten
    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
    Quote Originally Posted by Gerten
    So i can sort out played matches.
    Code:
    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???

  12. #12
    Join Date
    Sep 2007
    Posts
    16

    About draw in bowlingmatch

    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

  13. #13
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    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.
    Last edited by pdreyer; 10-01-07 at 09:42. Reason: PS

  14. #14
    Join Date
    Sep 2007
    Posts
    16

    My datatable

    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&#229;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.

    Code:
    SELECT     Omg&#229;ngar.Omg&#229;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&#229;ngar ON Match.matchdate = Omg&#229;ngar.DateStart
    WHERE Omg&#229;ngId = 4
    The result i got is:
    Code:
    Omg&#229;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

  15. #15
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    My crystal ball indicate that Omgångar.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.
    Code:
    match.matchdate between 
       OmgångarDate and dateadd(ms,86399996,OmgångarDate)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •