[ Resolved! ] Doing an Insert with a Top 1 Count?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

[ Resolved! ] Doing an Insert with a Top 1 Count??

Author

Topic

DTFan
Yak Posting Veteran

52 Posts

Posted - 2007-01-05 : 10:05:08

OK. First off I apologize for that stupid sounding Subject line but I wasn't sure how to phrase what I'm talking about. I'm working on a new test project and I am hoping that what I'm trying to do isn't too complicated. But I can't figure out how to do it.

I've got 5 tables. tblCompany, tblEmployee, tblNewsLetter, tblEmpLet, and tblNLFormat. tblEmpLet is a kind of joining table that says these employees receive these NewsLetters in this type of format.

What I want to do is when adding a new employee, I want a stored procedure to get a count of all other employees for the same company (as the new employee), find out which of the existing companies has the most number of NewsLetters, and then subscribe those same NewsLetters to the new employee.

OK. Here's the data schemes for the tables and some sample data:


CREATE TABLE [tblCompany] (
  [compID] int IDENTITY(1, 1) NOT NULL,
  [compName] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL
)
ON [PRIMARY]
GO

-- Structure for table tblEmpLet :

CREATE TABLE [tblEmpLet] (
  [elID] bigint IDENTITY(1, 1) NOT NULL,
  [empID] int NOT NULL,
  [nletID] int NOT NULL,
  [nlfID] int DEFAULT (1)
)
ON [PRIMARY]
GO

-- Structure for table tblEmployee :

CREATE TABLE [tblEmployee] (
  [empID] int IDENTITY(1, 1) NOT NULL,
  [compID] int NOT NULL,
  [empFName] varchar(35) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL,
  [empLName] varchar(35) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL,
  [empEmail] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL
)
ON [PRIMARY]
GO

-- Structure for table tblNewsLetter :

CREATE TABLE [tblNewsLetter] (
  [nletID] int IDENTITY(1, 1) NOT NULL,
  [nletTitle] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL,
  [nletDesc] varchar(200) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('') NOT NULL
)
ON [PRIMARY]
GO

-- Structure for table tblNLFormat :

CREATE TABLE [tblNLFormat] (
  [nlfID] int IDENTITY(1, 1) NOT NULL,
  [nlfType] varchar(4) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ('TEXT') NOT NULL
)
ON [PRIMARY]
GO

-- Data for table tblCompany  (LIMIT 0,500)

INSERT INTO [tblCompany] ([compID], [compName])
VALUES 
  (1, 'Acme Widgets')
GO

INSERT INTO [tblCompany] ([compID], [compName])
VALUES 
  (2, 'Ajax Plumbing')
GO

INSERT INTO [tblCompany] ([compID], [compName])
VALUES 
  (3, 'Johnson Lawn Care')
GO

INSERT INTO [tblCompany] ([compID], [compName])
VALUES 
  (4, 'Wilsons Tire World')
GO

COMMIT
GO

-- Data for table tblEmpLet  (LIMIT 0,500)

SET IDENTITY_INSERT [tblEmpLet] ON
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (1, 1, 4, 1)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (2, 8, 6, 3)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (3, 1, 5, 1)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (4, 12, 2, 2)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (5, 7, 7, 2)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (6, 1, 5, 1)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (7, 7, 1, 2)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (8, 10, 10, 3)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (9, 10, 6, 3)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (10, 10, 2, 3)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (11, 2, 5, 3)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (12, 2, 8, 3)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (13, 2, 9, 3)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (14, 1, 3, 3)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (15, 1, 9, 3)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (16, 1, 12, 2)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (17, 6, 5, 1)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (18, 6, 10, 2)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (19, 7, 3, 2)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (20, 11, 1, 1)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (21, 11, 3, 1)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (22, 11, 8, 3)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (23, 11, 12, 3)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (24, 1, 11, 2)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (25, 5, 5, 2)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (26, 5, 12, 2)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (27, 5, 3, 2)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (28, 9, 5, 2)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (29, 9, 9, 2)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (30, 3, 3, 3)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (31, 3, 8, 3)
GO

INSERT INTO [tblEmpLet] ([elID], [empID], [nletID], [nlfID])
VALUES 
  (32, 3, 1, 3)
GO

SET IDENTITY_INSERT [tblEmpLet] OFF
GO

COMMIT
GO

-- Data for table tblEmployee  (LIMIT 0,500)

SET IDENTITY_INSERT [tblEmployee] ON
GO

INSERT INTO [tblEmployee] ([empID], [compID], [empFName], [empLName], [empEmail])
VALUES 
  (1, 3, 'Ali', 'Larter', 'ali@heroes.com')
GO

INSERT INTO [tblEmployee] ([empID], [compID], [empFName], [empLName], [empEmail])
VALUES 
  (2, 2, 'Jennifer', 'Morrison', 'jenm@house.com')
GO

INSERT INTO [tblEmployee] ([empID], [compID], [empFName], [empLName], [empEmail])
VALUES 
  (3, 2, 'Rhona', 'Mitra', 'rmitra@niptuck.com')
GO

INSERT INTO [tblEmployee] ([empID], [compID], [empFName], [empLName], [empEmail])
VALUES 
  (4, 1, 'Jordana', 'Brewster', 'jbrew@fastandfurious.net')
GO

INSERT INTO [tblEmployee] ([empID], [compID], [empFName], [empLName], [empEmail])
VALUES 
  (5, 3, 'Amy', 'Smart', 'amys@butterflyeffect.com')
GO

INSERT INTO [tblEmployee] ([empID], [compID], [empFName], [empLName], [empEmail])
VALUES 
  (6, 2, 'Keira', 'Knightley', 'keirak@potc3.com')
GO

INSERT INTO [tblEmployee] ([empID], [compID], [empFName], [empLName], [empEmail])
VALUES 
  (7, 1, 'Anna', 'Kournikova', 'annak@tennis.com')
GO

INSERT INTO [tblEmployee] ([empID], [compID], [empFName], [empLName], [empEmail])
VALUES 
  (8, 1, 'Mila', 'Kunis', 'milak@that70sshow.com')
GO

INSERT INTO [tblEmployee] ([empID], [compID], [empFName], [empLName], [empEmail])
VALUES 
  (9, 4, 'Sarah', 'Paulson', 'sarahp@studio60.net')
GO

INSERT INTO [tblEmployee] ([empID], [compID], [empFName], [empLName], [empEmail])
VALUES 
  (10, 2, 'Kelsey', 'Oldershaw', 'kelsey.oldershaw@ering.org')
GO

INSERT INTO [tblEmployee] ([empID], [compID], [empFName], [empLName], [empEmail])
VALUES 
  (11, 4, 'Sarah', 'Lancaster', 'sarahl@everwood.com')
GO

INSERT INTO [tblEmployee] ([empID], [compID], [empFName], [empLName], [empEmail])
VALUES 
  (12, 3, 'Evangeline', 'Lilly', 'elilly@lost.com')
GO

SET IDENTITY_INSERT [tblEmployee] OFF
GO

COMMIT
GO

-- Data for table tblNewsLetter  (LIMIT 0,500)

SET IDENTITY_INSERT [tblNewsLetter] ON
GO

INSERT INTO [tblNewsLetter] ([nletID], [nletTitle], [nletDesc])
VALUES 
  (1, 'Hot Stock Tips', '')
GO

INSERT INTO [tblNewsLetter] ([nletID], [nletTitle], [nletDesc])
VALUES 
  (2, 'Lawn Care Basics', '')
GO

INSERT INTO [tblNewsLetter] ([nletID], [nletTitle], [nletDesc])
VALUES 
  (3, 'How To Change A Tire', '')
GO

INSERT INTO [tblNewsLetter] ([nletID], [nletTitle], [nletDesc])
VALUES 
  (4, '10 Ways To Lose Money', '')
GO

INSERT INTO [tblNewsLetter] ([nletID], [nletTitle], [nletDesc])
VALUES 
  (5, 'Every Which Way With Widgets', '')
GO

INSERT INTO [tblNewsLetter] ([nletID], [nletTitle], [nletDesc])
VALUES 
  (6, '10 Uses For Your Vanilla Ice CD', '')
GO

INSERT INTO [tblNewsLetter] ([nletID], [nletTitle], [nletDesc])
VALUES 
  (7, 'Hospital Rules For Doctors 101', '')
GO

INSERT INTO [tblNewsLetter] ([nletID], [nletTitle], [nletDesc])
VALUES 
  (8, 'Crab Grass And You - Its Not So Bad', '')
GO

INSERT INTO [tblNewsLetter] ([nletID], [nletTitle], [nletDesc])
VALUES 
  (9, 'Piping Hot News for Plumbers', '')
GO

INSERT INTO [tblNewsLetter] ([nletID], [nletTitle], [nletDesc])
VALUES 
  (10, 'Auto Tips From The Trade Show', '')
GO

INSERT INTO [tblNewsLetter] ([nletID], [nletTitle], [nletDesc])
VALUES 
  (11, 'Houseplant Tips For Everyone', '')
GO

INSERT INTO [tblNewsLetter] ([nletID], [nletTitle], [nletDesc])
VALUES 
  (12, 'Flowers versus Plants: Which Do You Want', '')
GO

INSERT INTO [tblNewsLetter] ([nletID], [nletTitle], [nletDesc])
VALUES 
  (13, 'Monthly Patterns For Quilts', '')
GO

SET IDENTITY_INSERT [tblNewsLetter] OFF
GO

COMMIT
GO

-- Data for table tblNLFormat  (LIMIT 0,500)

SET IDENTITY_INSERT [tblNLFormat] ON
GO

INSERT INTO [tblNLFormat] ([nlfID], [nlfType])
VALUES 
  (1, 'TEXT')
GO

INSERT INTO [tblNLFormat] ([nlfID], [nlfType])
VALUES 
  (2, 'HTML')
GO

INSERT INTO [tblNLFormat] ([nlfID], [nlfType])
VALUES 
  (3, 'BOTH')
GO

SET IDENTITY_INSERT [tblNLFormat] OFF
GO

COMMIT
GO

-- Definition for indices :

ALTER TABLE [dbo].[tblCompany]
ADD CONSTRAINT [PK_tblCompany] PRIMARY KEY CLUSTERED ([compID])
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblEmpLet]
ADD CONSTRAINT [PK_tblEmpLet] PRIMARY KEY CLUSTERED ([elID])
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblEmployee]
ADD CONSTRAINT [PK_tblEmployee] PRIMARY KEY CLUSTERED ([empID])
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblNewsLetter]
ADD CONSTRAINT [PK_tblNewsLetter] PRIMARY KEY CLUSTERED ([nletID])
ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblNLFormat]
ADD CONSTRAINT [PK_tblNLFormat] PRIMARY KEY CLUSTERED ([nlfID])
ON [PRIMARY]
GO

-- Definition for foreign keys %s :

ALTER TABLE [dbo].[tblEmpLet]
ADD CONSTRAINT [tblEmpLet_fk1] FOREIGN KEY ([empID]) 
  REFERENCES [dbo].[tblEmployee] ([empID])
  ON UPDATE CASCADE
  ON DELETE CASCADE
GO

ALTER TABLE [dbo].[tblEmpLet]
ADD CONSTRAINT [tblEmpLet_fk2] FOREIGN KEY ([nletID]) 
  REFERENCES [dbo].[tblNewsLetter] ([nletID])
  ON UPDATE CASCADE
  ON DELETE CASCADE
GO

ALTER TABLE [dbo].[tblEmpLet]
ADD CONSTRAINT [tblEmpLet_fk3] FOREIGN KEY ([nlfID]) 
  REFERENCES [dbo].[tblNLFormat] ([nlfID])
  ON UPDATE CASCADE
  ON DELETE CASCADE
GO

ALTER TABLE [dbo].[tblEmployee]
ADD CONSTRAINT [tblEmployee_fk] FOREIGN KEY ([compID]) 
  REFERENCES [dbo].[tblCompany] ([compID])
  ON UPDATE CASCADE
  ON DELETE CASCADE
GO

-- stored procedure to get employee newsletter count
CREATE PROCEDURE testproj_sp_GetEmployeeNLCount
(
     @in_intCompID INTEGER,
     @in_strEmpFName VARCHAR(35),
     @in_strEmpLName VARCHAR(35),
     @in_strEmpEmail VARCHAR(100)
)

AS

SET NOCOUNT ON

BEGIN

    INSERT INTO tblEmployee 
           (compID, empFName, empLName, empEmail)
    VALUES (@in_intCompID, @in_strEmpFName, 
            @in_strEmpLName, @in_strEmpEmail)

END

BEGIN

    SELECT    DISTINCT COUNT(empID)
    FROM      tblEmpLet
    WHERE     empID IN
              (
                  SELECT    empID
                  FROM      tblEmployee
                  WHERE     compID = @in_intCompID
              )

END

So basically, you've got 4 Companies:

1 - Acme Widgets
2 - Ajax Plumbing
3 - Johnson Lawn Care
4 - Wilsons Tire World

Company #3 (Johnson Lawn Care) has 3 employees currently receiving newsletters:
Ali Larter (7 newsletters)
Amy Smart (3 newsletters)
Evangeline Lilly (1 newsletter)

So, if I was going to add a new employee for Johnson Lawn Care, say Jessica Alba, I would be sending the CompanyID, her first name, last name and email address. After adding her to tblEmployee, I would want to get her employee ID, then search the tblEmpLet table to get a count of all other employees for that company (and the number of letters they subscribe to), then, take the one that has the greatest number of newsletters (in this case, Ali Larter), then subscribe Jessica Alba to each of those newsletters (and the same "format" (TEXT, HTML, BOTH)).

I know there are some issues (for example, what happens if there are 2 or 3 employees who each receive the same number of newsletters (but maybe different newsletters) ... which to pick. Right now I'm not concerned with that ... I just want to pick any one that has the greatest number of newsletters and subscribe the new Employee to those (along with using the same "format" for the newsletter).

I was thinking of declaring 3 variables (@CurCount, @PrevCount, @TmpEmpID), setting all equal to zero, then on each loop set @CurCount to the number of newsletters for that particular employee, then on the next compare the next employees count to @PrevCount(previous count), and if greater update @PrevCount and @TmpEmpID else loop. But there has to be a better way than that to achieve what I'm trying to do.

Sorry for the long, rambling post. I wanted to try and describe as accurately as possible what I'm trying to accomplish. Thank you in advance for any and all help that you can give. It is very much appreciated. I'll keep plugging away and if I find anything I'll be sure to update this post.

DTFan
Ever-hopeful programmer-in-training

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-05 : 13:30:45

Your proc should work something like this

-- stored procedure to get add a new employee with newsletters
CREATE PROCEDURE testproj_sp_GetEmployeeNLCount
(
     @in_intCompID INTEGER,
     @in_strEmpFName VARCHAR(35),
     @in_strEmpLName VARCHAR(35),
     @in_strEmpEmail VARCHAR(100)
)

AS

SET NOCOUNT ON

BEGIN
    BEGIN TRAN
    DECLARE @CopyEmpID int, @NewEmpID int

    INSERT INTO tblEmployee 
        (compID, empFName, empLName, empEmail)
    VALUES (@in_intCompID, @in_strEmpFName, 
        @in_strEmpLName, @in_strEmpEmail)

    SET @NewEmpID = SCOPE_IDENTITY()

    SELECT @CopyEmpID = (SELECT TOP 1 e.empID
    FROM      tblEmpLet el
    INNER JOIN tblEmployee e ON e.empID = el.empID 
    WHERE     compID = @in_intCompID
    GROUP BY e.empID
    ORDER BY count(*) DESC)

    IF @CopyEmpID IS NOT NULL
        INSERT INTO tblEmpLet
        SELECT @NewEmpID, [nletID], [nlfID] 
        FROM tblEmpLet
        WHERE empID = @CopyEmpID
    COMMIT
END

DTFan
Yak Posting Veteran

52 Posts

Posted - 2007-01-05 : 15:27:50

snSQL - That rocks!!! That did exactly what I wanted and very fast. Thank you very much for that. I can definitely say it isn't something I would have figured out on my own.

Thank you again.

DTFan
Ever-hopeful programmer-in-training

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources