June 23, 2011 at 10:23 pm
Hi all, this is for my local little league.
We have people spending money with suppliers that they may not have permission to buy from.
The little league has negotiated special prices with certain suppliers and we want to see which team(s) has spent money with the same supplier.
Example, the Angels have spent money with Randy’s Major League, and so has the Rangers and the Dodgers.
What I am trying to get is a report listing the suppliers and the teams that have bought from them.
The catch, pardon the pun, is I only want the suppliers that have sold to more than one team.
The report would look like this.
Randy’s Major League
Angels, $55.00
Dodgers,$19.00
Rangers, $62.00
Big-5
Angels, $50.00
Yankees, $33.00
I would appreciate the help.
Thanks
GF
-- ================== Table Create Script =======================================================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_spend_suppliers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[spend] DROP CONSTRAINT FK_spend_suppliers
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_spend_team]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[spend] DROP CONSTRAINT FK_spend_team
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spend]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[spend]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[suppliers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[suppliers]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[team]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[team]
GO
CREATE TABLE [dbo].[spend] (
[spendid] [int] IDENTITY (1, 1) NOT NULL ,
[teamid] [int] NULL ,
[supplierid] [int] NULL ,
[spend] [money] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[suppliers] (
[supplierid] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[team] (
[teamid] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[spend] WITH NOCHECK ADD
CONSTRAINT [PK_spend] PRIMARY KEY CLUSTERED
(
[spendid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[suppliers] WITH NOCHECK ADD
CONSTRAINT [PK_suppliers] PRIMARY KEY CLUSTERED
(
[supplierid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[team] WITH NOCHECK ADD
CONSTRAINT [PK_team] PRIMARY KEY CLUSTERED
(
[teamid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[spend] ADD
CONSTRAINT [FK_spend_suppliers] FOREIGN KEY
(
[supplierid]
) REFERENCES [dbo].[suppliers] (
[supplierid]
),
CONSTRAINT [FK_spend_team] FOREIGN KEY
(
[teamid]
) REFERENCES [dbo].[team] (
[teamid]
)
GO
-- ================== Insert Data Scripts =======================================================================
-- Teams
INSERT INTO team (name) VALUES ('Angels')
INSERT INTO team (name) VALUES ('Red Sox')
INSERT INTO team (name) VALUES ('White Sox')
INSERT INTO team (name) VALUES ('Cubs')
INSERT INTO team (name) VALUES ('Dodgers')
INSERT INTO team (name) VALUES ('Giants')
INSERT INTO team (name) VALUES ('Yankees')
INSERT INTO team (name) VALUES ('Mets')
INSERT INTO team (name) VALUES ('Astros')
INSERT INTO team (name) VALUES ('Rangers')
-- Suppliers
INSERT INTO suppliers (name) VALUES ('Gordons Sports')
INSERT INTO suppliers (name) VALUES ('Big-5')
INSERT INTO suppliers (name) VALUES ('Randys Major League')
INSERT INTO suppliers (name) VALUES ('Sports-R-US')
INSERT INTO suppliers (name) VALUES ('The Dugout')
INSERT INTO suppliers (name) VALUES ('Infield Supplies')
INSERT INTO suppliers (name) VALUES ('Home Plate')
INSERT INTO suppliers (name) VALUES ('Coaches Sports')
-- Spend
INSERT INTO spend (teamid, supplierid, spend) VALUES (1,1,'75.00')
INSERT INTO spend (teamid, supplierid, spend) VALUES (1,2,'50.00')
INSERT INTO spend (teamid, supplierid, spend) VALUES (1,3,'55.00')
INSERT INTO spend (teamid, supplierid, spend) VALUES (9,1,'25.00')
INSERT INTO spend (teamid, supplierid, spend) VALUES (9,6,'35.00')
INSERT INTO spend (teamid, supplierid, spend) VALUES (5,3,'19.00')
INSERT INTO spend (teamid, supplierid, spend) VALUES (7,2,'33.00')
INSERT INTO spend (teamid, supplierid, spend) VALUES (7,4,'60.00')
INSERT INTO spend (teamid, supplierid, spend) VALUES (10,8,'45.00')
INSERT INTO spend (teamid, supplierid, spend) VAL
June 24, 2011 at 9:45 am
SELECT Sup.NAME, T.NAME, SUM(S.Spend) Spent
FROM Suppliers Sup
INNER JOIN Spend S ON Sup.SupplierID = S.SupplierID
INNER JOIN Team T ON T.TeamID = S.TeamID
WHERE S.SupplierID IN (SELECT SupplierID FROM Spend GROUP BY SupplierID HAVING COUNT(DISTINCT TeamID) > 1)
GROUP BY Sup.NAME, T.NAME
ORDER BY Sup.NAME, T.Name
June 24, 2011 at 12:29 pm
Garadin,
Thank you for your help.
This got me going in the right direction.
I appreciate it.
GF
June 24, 2011 at 12:35 pm
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply