Re: Need help w/ SELECT statement within a SELECT statement

From: Sam (sam_at_globalwebcentral.com)
Date: 12/21/04

Next message: MS User: "Excel - named ranges"
Previous message: Roberto Lo Baido: "Re: Modify Local IP in VB6"
In reply to: John Bell: "Re: Need help w/ SELECT statement within a SELECT statement"
Messages sorted by: [ date ] [ thread ]

Date: Tue, 21 Dec 2004 11:11:52 -0500

Again... Thanks for your response John. Your help is much appreciated.

Sam

"John Bell" <jbellnewsposts@hotmail.com> wrote in message
news:Owa1Vox5EHA.2512@TK2MSFTNGP09.phx.gbl...
> Hi Sam
>
> You are right, this type of thing is quite common and with appropriate
> indexes it is not too costly (EmployeeID as a FK should be indexed which
> your DDL does not show!). I don't think getting two record sets would not
> produce any less work as you will need to restrict the summation record
> set. Another alternative it to de-normalise your database and maintain the
> summation through triggers.
>
> John
>
>
> "Sam" <sam@globalwebcentral.com> wrote in message
> news:ecff$pt5EHA.992@TK2MSFTNGP12.phx.gbl...
>> John,
>>
>> Thanks for the reply. What about performance though? Do you think having
>> a SELECT statement within a SELECT is a bad idea from a performance stand
>> point? I assume such statements should be fairly common in real world. Or
>> is there a better technique to handling such situations i.e. get two
>> record sets and do the sorting and figuring things out in the application
>> code.
>>
>> Sam
>> "John Bell" <jbellnewsposts@hotmail.com> wrote in message
>> news:ukuMBGt5EHA.3840@tk2msftngp13.phx.gbl...
>>> Hi
>>>
>>> You don't give the current SQL but try something like:
>>>
>>> SELECT
>>> E.[FirstName] + ' ' + E.[LastName] AS [Sales Person],
>>> S.[ActivityDate] AS [Activity Date],
>>> S.[MarketingStat1] AS [Stat 1],
>>> S.[MarketingStat2] AS [Stat 2],
>>> (SELECT COUNT(*) FROM [dbo].[tblDeals] D WHERE D.[EmployeeID] =
>>> E.[EmployeeID]) AS [DealsWritten]
>>> FROM [dbo].[tblMarketingStats] S
>>> JOIN [dbo].[tblEmployee] E ON E.[EmployeeID] = S.[EmployeeID]
>>>
>>> John
>>>
>>> "Sam" <sam@globalwebcentral.com> wrote in message
>>> news:eJo%23TCr5EHA.2568@TK2MSFTNGP10.phx.gbl...
>>>> Hi,
>>>>
>>>> I've included SQL scripts at the bottom of this message so that you can
>>>> create the database if you need to. It only has 3 tables.
>>>>
>>>> I need help writing the SQL statement that gives me marketing
>>>> performance data for sales personnel. There are 3 tables:
>>>>
>>>> 1. tblEmployee is for Employee data i.e. name, last name, etc.
>>>> 2. tblDeals table is for contracts written. EmployeeID foreign key
>>>> field identifies the sales person who wrote the deal.
>>>> 3. tblMarketingStats table is where we keep a sales person's phone
>>>> time, how many calls he/she made, etc.
>>>>
>>>> I already wrote the code for providing marketing stats for a specified
>>>> time frame. This was pretty straight forward. I provide start and end
>>>> dates for the time period and the SP spits out the data. It looks like
>>>> this:
>>>>
>>>> Sales Person -- Activity Date -- Stat 1 -- Stat 2
>>>> John Doe -- 12/15/2004 -- 2:30:00 -- 47
>>>> Jane Smith -- 12/15/2004 -- 2:14:30 -- 38
>>>> John Doe -- 12/16/2004 -- 2:47:15 -- 52
>>>> Jane Smith -- 12/16/2004 -- 2:13:00 -- 40
>>>> ... and so on
>>>>
>>>> What I want to add to this is the number of deals written by each sales
>>>> person. So I want to add another column that will give me that, which
>>>> should look like this:
>>>>
>>>> Sales Person -- Activity Date -- Stat 1 -- Stat 2 -- DealsWritten
>>>> John Doe -- 12/15/2004 -- 2:30:00 -- 47 -- 0
>>>> Jane Smith -- 12/15/2004 -- 2:14:30 -- 38 -- 1
>>>> John Doe -- 12/16/2004 -- 2:47:15 -- 52 -- 3
>>>> Jane Smith -- 12/16/2004 -- 2:13:00 -- 40 -- 0
>>>> ... and so on
>>>>
>>>> This data is in tblDeals. I just need help including it in my stored
>>>> procedure. I guess the only way to do this is to have a SELECT
>>>> statement embedded in another one. I don't know how to write the SQL
>>>> statement so that it will query tblDeals for the given sales person and
>>>> activity date as it's gathering data from tblMarketingStats.
>>>>
>>>> The second concern I have is w/ performance. In the middle of one
>>>> SELECT statement, stopping and running another SELECT statement would
>>>> hurt the performance, wouldn't it?
>>>>
>>>> Thanks for your help.
>>>>
>>>> Sam
>>>>
>>>> =============================
>>>> SQL Script
>>>> =============================
>>>> /****** Object: Database DummyDB Script Date: 12/20/2004 11:06:23
>>>> AM ******/
>>>> IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name =
>>>> N'DummyDB')
>>>> DROP DATABASE [DummyDB]
>>>> GO
>>>>
>>>> CREATE DATABASE [DummyDB] ON (NAME = N'DummyDB_Data', FILENAME =
>>>> N'D:\SQL_Data\DummyDB\DummyDB_Data.MDF' , SIZE = 1, FILEGROWTH = 10%)
>>>> LOG ON (NAME = N'DummyDB_Log', FILENAME =
>>>> N'D:\SQL_Data\DummyDB\DummyDB_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
>>>> COLLATE SQL_Latin1_General_CP1_CI_AS
>>>> GO
>>>>
>>>> exec sp_dboption N'DummyDB', N'autoclose', N'false'
>>>> GO
>>>>
>>>> exec sp_dboption N'DummyDB', N'bulkcopy', N'false'
>>>> GO
>>>>
>>>> exec sp_dboption N'DummyDB', N'trunc. log', N'false'
>>>> GO
>>>>
>>>> exec sp_dboption N'DummyDB', N'torn page detection', N'true'
>>>> GO
>>>>
>>>> exec sp_dboption N'DummyDB', N'read only', N'false'
>>>> GO
>>>>
>>>> exec sp_dboption N'DummyDB', N'dbo use', N'false'
>>>> GO
>>>>
>>>> exec sp_dboption N'DummyDB', N'single', N'false'
>>>> GO
>>>>
>>>> exec sp_dboption N'DummyDB', N'autoshrink', N'false'
>>>> GO
>>>>
>>>> exec sp_dboption N'DummyDB', N'ANSI null default', N'false'
>>>> GO
>>>>
>>>> exec sp_dboption N'DummyDB', N'recursive triggers', N'false'
>>>> GO
>>>>
>>>> exec sp_dboption N'DummyDB', N'ANSI nulls', N'false'
>>>> GO
>>>>
>>>> exec sp_dboption N'DummyDB', N'concat null yields null', N'false'
>>>> GO
>>>>
>>>> exec sp_dboption N'DummyDB', N'cursor close on commit', N'false'
>>>> GO
>>>>
>>>> exec sp_dboption N'DummyDB', N'default to local cursor', N'false'
>>>> GO
>>>>
>>>> exec sp_dboption N'DummyDB', N'quoted identifier', N'false'
>>>> GO
>>>>
>>>> exec sp_dboption N'DummyDB', N'ANSI warnings', N'false'
>>>> GO
>>>>
>>>> exec sp_dboption N'DummyDB', N'auto create statistics', N'true'
>>>> GO
>>>>
>>>> exec sp_dboption N'DummyDB', N'auto update statistics', N'true'
>>>> GO
>>>>
>>>> if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion &
>>>> 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and
>>>> (@@microsoftversion & 0xffff >= 1082) ) )
>>>> exec sp_dboption N'DummyDB', N'db chaining', N'false'
>>>> GO
>>>>
>>>> use [DummyDB]
>>>> GO
>>>>
>>>> if exists (select * from dbo.sysobjects where id =
>>>> object_id(N'[dbo].[FK_tblDeals_tblEmployee]') and OBJECTPROPERTY(id,
>>>> N'IsForeignKey') = 1)
>>>> ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblEmployee
>>>> GO
>>>>
>>>> if exists (select * from dbo.sysobjects where id =
>>>> object_id(N'[dbo].[FK_tblMarketingStats_tblEmployee]') and
>>>> OBJECTPROPERTY(id, N'IsForeignKey') = 1)
>>>> ALTER TABLE [dbo].[tblMarketingStats] DROP CONSTRAINT
>>>> FK_tblMarketingStats_tblEmployee
>>>> GO
>>>>
>>>> /****** Object: Table [dbo].[tblDeals] Script Date: 12/20/2004
>>>> 11:06:23 AM ******/
>>>> if exists (select * from dbo.sysobjects where id =
>>>> object_id(N'[dbo].[tblDeals]') and OBJECTPROPERTY(id, N'IsUserTable') =
>>>> 1)
>>>> drop table [dbo].[tblDeals]
>>>> GO
>>>>
>>>> /****** Object: Table [dbo].[tblEmployee] Script Date: 12/20/2004
>>>> 11:06:23 AM ******/
>>>> if exists (select * from dbo.sysobjects where id =
>>>> object_id(N'[dbo].[tblEmployee]') and OBJECTPROPERTY(id,
>>>> N'IsUserTable') = 1)
>>>> drop table [dbo].[tblEmployee]
>>>> GO
>>>>
>>>> /****** Object: Table [dbo].[tblMarketingStats] Script Date:
>>>> 12/20/2004 11:06:23 AM ******/
>>>> if exists (select * from dbo.sysobjects where id =
>>>> object_id(N'[dbo].[tblMarketingStats]') and OBJECTPROPERTY(id,
>>>> N'IsUserTable') = 1)
>>>> drop table [dbo].[tblMarketingStats]
>>>> GO
>>>>
>>>> /****** Object: Table [dbo].[tblDeals] Script Date: 12/20/2004
>>>> 11:06:25 AM ******/
>>>> CREATE TABLE [dbo].[tblDeals] (
>>>> [DealID] [int] IDENTITY (1, 1) NOT NULL ,
>>>> [ContractDate] [smalldatetime] NOT NULL ,
>>>> [EmployeeID] [smallint] NOT NULL
>>>> ) ON [PRIMARY]
>>>> GO
>>>>
>>>> /****** Object: Table [dbo].[tblEmployee] Script Date: 12/20/2004
>>>> 11:06:25 AM ******/
>>>> CREATE TABLE [dbo].[tblEmployee] (
>>>> [EmployeeID] [smallint] IDENTITY (1, 1) NOT NULL ,
>>>> [FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
>>>> NULL ,
>>>> [LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>>>> ) ON [PRIMARY]
>>>> GO
>>>>
>>>> /****** Object: Table [dbo].[tblMarketingStats] Script Date:
>>>> 12/20/2004 11:06:25 AM ******/
>>>> CREATE TABLE [dbo].[tblMarketingStats] (
>>>> [StatID] [int] IDENTITY (1, 1) NOT NULL ,
>>>> [EmployeeID] [smallint] NOT NULL ,
>>>> [ActivityDate] [smalldatetime] NOT NULL ,
>>>> [MarketingStat1] [smallint] NOT NULL ,
>>>> [MarketingStat2] [smallint] NOT NULL
>>>> ) ON [PRIMARY]
>>>> GO
>>>>
>>>> ALTER TABLE [dbo].[tblDeals] ADD
>>>> CONSTRAINT [PK_tblDeals] PRIMARY KEY CLUSTERED
>>>> (
>>>> [DealID]
>>>> ) ON [PRIMARY]
>>>> GO
>>>>
>>>> ALTER TABLE [dbo].[tblEmployee] ADD
>>>> CONSTRAINT [PK_tblEmployee] PRIMARY KEY CLUSTERED
>>>> (
>>>> [EmployeeID]
>>>> ) ON [PRIMARY]
>>>> GO
>>>>
>>>> ALTER TABLE [dbo].[tblMarketingStats] ADD
>>>> CONSTRAINT [PK_tblMarketingStats] PRIMARY KEY CLUSTERED
>>>> (
>>>> [StatID]
>>>> ) ON [PRIMARY]
>>>> GO
>>>>
>>>> ALTER TABLE [dbo].[tblDeals] ADD
>>>> CONSTRAINT [FK_tblDeals_tblEmployee] FOREIGN KEY
>>>> (
>>>> [EmployeeID]
>>>> ) REFERENCES [dbo].[tblEmployee] (
>>>> [EmployeeID]
>>>> )
>>>> GO
>>>>
>>>> ALTER TABLE [dbo].[tblMarketingStats] ADD
>>>> CONSTRAINT [FK_tblMarketingStats_tblEmployee] FOREIGN KEY
>>>> (
>>>> [EmployeeID]
>>>> ) REFERENCES [dbo].[tblEmployee] (
>>>> [EmployeeID]
>>>> )
>>>> GO
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

Next message: MS User: "Excel - named ranges"
Previous message: Roberto Lo Baido: "Re: Modify Local IP in VB6"
In reply to: John Bell: "Re: Need help w/ SELECT statement within a SELECT statement"
Messages sorted by: [ date ] [ thread ]

Relevant Pages

Re: Need help w/ SELECT statement within a SELECT statement
... I don't think getting two record sets would not ... produce any less work as you will need to restrict the summation record set. ... John ... I assume such statements should be fairly common in real world. ...
(microsoft.public.sqlserver.programming)
Re: Negligee at Abbey Rd.--Geoff Emerick reminisces
... What was weird 30 years ago is accepted now as common. ... Rap stars and others use the f--- word commonly. ... (when John did it, I ... self destructive path and should have ...
(rec.music.beatles)
This bag is not a toy.
... Sam and John were out cutting wood, and John cut his arm off. ... the arm in a plastic bag and took it and John to a surgeon. ... Sam put the leg in a plastic bag and took it and John back to the surgeon. ...
(rec.humor.funny.reruns)
Re: Transparent bridges (a. k. a. HUB-to-PCI bridges)?
... I'm no John Kennedy, nor even John Baldwin, but I'll give this a shot. ... A host-PCI bridge is typically part of the "southbridge" chip of modern ... careful programming in C lets you create several closely-related structs for ... The most common example of this would probably be the protocol-independent ...
(freebsd-current)
Re: intrinsic energy
... It's a wonderful and fascinating world, John! ... I don't understand why you prefer to not understand, Sam. ... Our effort with our Science is to make thing *not strange*. ... developement of better tools. ...
(sci.physics)