While setting up windows cluster in 2003, my external array which is
logically partiotioned into P and Q appear as one disk group.
If i wanted to setup an active/active SQL cluster, how can i seperate into 2
groups, so that one node owns P and SQL instance 1 has data files on P and
the other node owns Q with another SQL instance owning Q. I didnt see a
managed disk dialog during the 2003 windows cluster instance.
Consult with your extenal array vendor documentation/support. It may be that your array cannot present virtual disks (like many high-end SANs can). This may mean that you need disk sets made up of different physical disks to present 2 "disks" to your cl
uster. eg physical disks 1, 2+3 are drive P, while physical disks 4 and 5 are drive Q. Don't forget that you'll also need a separate disk for the Quorum. So, in your configuration you'll need to have AT LEAST 3 separate disks to present to your cluster
. What sort of external array are you using?
|||MSCS looks at the physical layer, not the partition level. So you can have 4
partitions on a single disk, but MSCS will display this as one physical disk
resource. The only way to achieve an active/active configuration would be to
add another physical disk...can't be done with your current config.
Regards,
John.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e$W6OIPFEHA.2768@.tk2msftngp13.phx.gbl...
> While setting up windows cluster in 2003, my external array which is
> logically partiotioned into P and Q appear as one disk group.
> If i wanted to setup an active/active SQL cluster, how can i seperate into
2
> groups, so that one node owns P and SQL instance 1 has data files on P and
> the other node owns Q with another SQL instance owning Q. I didnt see a
> managed disk dialog during the 2003 windows cluster instance.
>
>
Showing posts with label group. Show all posts
Showing posts with label group. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
Making groups available
I'd like to get the sum of a row in a reporting services table. Unlike
summing columns which are easy, I'm told I need to create a group.
I create the group with the fields I want to sum. Then I right click on the
table cell I want to hold the sum value, and select expression. My table
column names are there, but not my group (which I need to sum my row).
When I create the group, I give it a name and add the fields I want to sum.
What else do I have to do to make the group available when I edit expressions?
Of course, if there's some aggregate functions that let's me sum a row
without a group, all the better.
Thanks
--
RandyHi Randy,
Welcome to the MSDN newsgroup.
Regarding on the "sum of a row in a reporting services table", do you mean
perform sum on all the columns in a single data row or still sum a certain
column , but restricted in a certain group?
If you're wantting to perform the sum on all the columns in a single row,
I'm afraid, the group or other filter can not help on this. You may need to
manually write the sum code in the expression of the textbox or certain
table cell. Also, since you'll perform sum on the columns, we need to make
sure all the columns are of number type.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Many thanks for the reply.
Yes, I would like to sum all the columns in the row, and they are all type
int.
I have found the code window, and written a VB function to sum all the
columns. When I say Code.MySum(...) in the Edit Expression dialog box, MySum
is underlined in red and "not defined".
I've tried to just sum the columns in the edit expression dialog box. The
sum function provided only takes one parameter. I'd need an example or BOM
reference to see how to do this. At one point, someone suggested the Subtotal
function, but I couldn't find that in the available aggregate functions list.
If you could point me toward an article on writing functions for reporting
services under the Code tab or in the Edit Expression dialog box, that would
help a lot.
Thanks again,
--
Randy
"Steven Cheng[MSFT]" wrote:
> Hi Randy,
> Welcome to the MSDN newsgroup.
> Regarding on the "sum of a row in a reporting services table", do you mean
> perform sum on all the columns in a single data row or still sum a certain
> column , but restricted in a certain group?
> If you're wantting to perform the sum on all the columns in a single row,
> I'm afraid, the group or other filter can not help on this. You may need to
> manually write the sum code in the expression of the textbox or certain
> table cell. Also, since you'll perform sum on the columns, we need to make
> sure all the columns are of number type.
> Regards,
> Steven Cheng
> Microsoft Online Community Support
>
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
>
>
>|||Thanks for your followup Randy,
IMO, if you just want to do sum on all the columns in a single row, you can
just put the sum code in the expression of the textbox( use + operator).
For example:
=Fields("col1").Value + Fields("col2").Value + Fields("col3").Value
For custom code in SSRS, you can have a look at the following web reference:
#Using Custom Code References in Expressions [Reporting Services]
http://msdn2.microsoft.com/en-US/library/ms155798(SQL.90).aspx
#Chapter 5: Using Custom .Net Code with Reports
http://www.yukonxml.com/chapters/apress/reportingservices/dotnet/
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
summing columns which are easy, I'm told I need to create a group.
I create the group with the fields I want to sum. Then I right click on the
table cell I want to hold the sum value, and select expression. My table
column names are there, but not my group (which I need to sum my row).
When I create the group, I give it a name and add the fields I want to sum.
What else do I have to do to make the group available when I edit expressions?
Of course, if there's some aggregate functions that let's me sum a row
without a group, all the better.
Thanks
--
RandyHi Randy,
Welcome to the MSDN newsgroup.
Regarding on the "sum of a row in a reporting services table", do you mean
perform sum on all the columns in a single data row or still sum a certain
column , but restricted in a certain group?
If you're wantting to perform the sum on all the columns in a single row,
I'm afraid, the group or other filter can not help on this. You may need to
manually write the sum code in the expression of the textbox or certain
table cell. Also, since you'll perform sum on the columns, we need to make
sure all the columns are of number type.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Many thanks for the reply.
Yes, I would like to sum all the columns in the row, and they are all type
int.
I have found the code window, and written a VB function to sum all the
columns. When I say Code.MySum(...) in the Edit Expression dialog box, MySum
is underlined in red and "not defined".
I've tried to just sum the columns in the edit expression dialog box. The
sum function provided only takes one parameter. I'd need an example or BOM
reference to see how to do this. At one point, someone suggested the Subtotal
function, but I couldn't find that in the available aggregate functions list.
If you could point me toward an article on writing functions for reporting
services under the Code tab or in the Edit Expression dialog box, that would
help a lot.
Thanks again,
--
Randy
"Steven Cheng[MSFT]" wrote:
> Hi Randy,
> Welcome to the MSDN newsgroup.
> Regarding on the "sum of a row in a reporting services table", do you mean
> perform sum on all the columns in a single data row or still sum a certain
> column , but restricted in a certain group?
> If you're wantting to perform the sum on all the columns in a single row,
> I'm afraid, the group or other filter can not help on this. You may need to
> manually write the sum code in the expression of the textbox or certain
> table cell. Also, since you'll perform sum on the columns, we need to make
> sure all the columns are of number type.
> Regards,
> Steven Cheng
> Microsoft Online Community Support
>
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
>
>
>|||Thanks for your followup Randy,
IMO, if you just want to do sum on all the columns in a single row, you can
just put the sum code in the expression of the textbox( use + operator).
For example:
=Fields("col1").Value + Fields("col2").Value + Fields("col3").Value
For custom code in SSRS, you can have a look at the following web reference:
#Using Custom Code References in Expressions [Reporting Services]
http://msdn2.microsoft.com/en-US/library/ms155798(SQL.90).aspx
#Chapter 5: Using Custom .Net Code with Reports
http://www.yukonxml.com/chapters/apress/reportingservices/dotnet/
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
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
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
Wednesday, March 21, 2012
Make the series stand by themselves in stacked column graph
I'm making some reports that are made for getting printed. I currently have three groups of reports, each group having four charts (four A4 papers/group). To cut down on the sheer number of papers on the boards I've been asked to merge these charts so that we'll have all three groups in one chart. This works fine for three of the charts (two line charts and one column chart), where the results gets neatly organized in group order. Where I ran into problems was the last chart..
The last chart is a stacked column chart. With one group, it works quite well; the x-axis is by year/week, and for each week there's a bar where the lower part is "done" and the upper part is "failed".
Now when doing this graph with multiple groups (groups as series), it doesn't put the different groups beside each other as I would've wished, instead it piles them on top of each other as if it was a 100% stacked column.
So the question is; is there some way to make a stacked column graph with multiple series that are one stacked bar per group instead of having all of them stacked on top of each other? Am I just missing some small detail here?What you are observing is the nature of stacked charts--the values for each category are stacked on top of each other. Unfortunately, what you are looking for, having multiple stacked columns for the same category, is not possible.|||Can you do a column graph and have some bars as a single bar and other bars be stacked?
Monday, March 12, 2012
Make a Group Section Visible when there is no data for this group
I have a report that groups on departments. The requestor would like all
sections to show whether they have information in them or not - also all
sections need to show if there are is no information, totals, etc.
Thank you,
NormaOn Nov 20, 1:48 pm, NormaD <Nor...@.discussions.microsoft.com> wrote:
> I have a report that groups on departments. The requestor would like all
> sections to show whether they have information in them or not - also all
> sections need to show if there are is no information, totals, etc.
> Thank you,
> Norma
Two ways of doing it from what I see.
1) Do a left join on the Department information with your fact data,
so that a department name is included even if there is no data for it.
Data:
SELECT D.NAME AS DEPARTMENT_NAME, F.AMOUNT
FROM DEPARTMENTS D LEFT JOIN FACT_TABLE F
ON D.DEPARTMENT_KEY = F.DEPARTMENT_KEY
Layout: Drop a Table object, add a Group that groups on the
department name. Put your department header information on that group
row, and all of the facts in the detail row.
The table will still render, but your row counts will be different
because you will be adding a row for each department that has no fact
data.
2) Implement a Subreport that passes the Department info
Data: I would have an outer Report that does a SELECT on all of your
departments by name/id.
Layout: Drop a list box on the report that groups by the Department
name/id. Then in the list, put a subreport, and pass the Department
as a Parameter to the subreport. Put your department header
information here.
Data: In the Subreport, use the @.Department in your WHERE clause to
filter to a single department.
Layout: Drop your fact headers & totals here
Now your row counts in the Subreport will properly show zero rows when
there are no facts for a department.
-- Scott
sections to show whether they have information in them or not - also all
sections need to show if there are is no information, totals, etc.
Thank you,
NormaOn Nov 20, 1:48 pm, NormaD <Nor...@.discussions.microsoft.com> wrote:
> I have a report that groups on departments. The requestor would like all
> sections to show whether they have information in them or not - also all
> sections need to show if there are is no information, totals, etc.
> Thank you,
> Norma
Two ways of doing it from what I see.
1) Do a left join on the Department information with your fact data,
so that a department name is included even if there is no data for it.
Data:
SELECT D.NAME AS DEPARTMENT_NAME, F.AMOUNT
FROM DEPARTMENTS D LEFT JOIN FACT_TABLE F
ON D.DEPARTMENT_KEY = F.DEPARTMENT_KEY
Layout: Drop a Table object, add a Group that groups on the
department name. Put your department header information on that group
row, and all of the facts in the detail row.
The table will still render, but your row counts will be different
because you will be adding a row for each department that has no fact
data.
2) Implement a Subreport that passes the Department info
Data: I would have an outer Report that does a SELECT on all of your
departments by name/id.
Layout: Drop a list box on the report that groups by the Department
name/id. Then in the list, put a subreport, and pass the Department
as a Parameter to the subreport. Put your department header
information here.
Data: In the Subreport, use the @.Department in your WHERE clause to
filter to a single department.
Layout: Drop your fact headers & totals here
Now your row counts in the Subreport will properly show zero rows when
there are no facts for a department.
-- Scott
Subscribe to:
Posts (Atom)