Simple Query Question

[leebridgewater]

Hello Group,

I'm new to SQL sever but I have managed to create a SQL based online
application and I just have a question that I'm sure someone here will
be able to help me with.

I have a database setup and from within this database I would like to
run a query to produce a simple report showing me the enquiry details
along with the network the enquiry member is from.

Tables:

1 - dbo.enquiries

enquiryID
introducerID
enquiry
2 - dbo.introducers
introducerID
networkName
introducerName

What I would like is a report where I can view new enquiries from
introducers from a particular network, best described as follows:

View all enquiries from introducers who belong to 'Network Name' which
were created within the last 30 days.

I think that I have to create an inner join but Im not 100% so any
help including the correct sql statement to use would be perfect.

Many thanks in advance

Lee

[Plamen Ratchev]

What column in your tables has the date value when the enquiry was created?
You need that date to be able to report those that were created in the last
30 days.

Assuming that column is named enquiry_date, your query may look like:

SELECT E.enquiryID,
E.introducerID,
E.enquiry,
E.enquiry_date,
I.networkName,
I.introducerNam e
FROM dbo.Enquiries AS E
JOIN dbo.Introducers AS I
ON E.introducerID = I.introducerID
WHERE I.networkName = 'Network Name'
AND E.enquiry_date >= DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTA MP) -
30, 0);

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[leebridgewater]

Hi, many thanks for your detailed example - it is appreciated.

The column containing the date the enquiry was made is named
enquiryDateTime (within the enquiries table) and contains a small date
time value which is stored as follows:

23/01/2008 12:13:00

(By the way Im based in the UK so the date above translates to 23rd
January 2008)

I tried running the SQL statement within the QUERY tool in SQL
Management studio and I receive the following error:

Msg 207, Level 16, State 1, Line 3
Invalid column name 'enquiry'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'enquiry_date'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'introducerName '.

Not sure what I'm doing wrong!!

Thanks

Lee

[Plamen Ratchev]

You would have to change the query to have the correct column name for the
enquiry date. Why the other two columns are reported as invalid I am not
sure. Did you post the column names correctly as they appear in your tables?
You can script and post the create table statements (just right click the
table name in SSMS, and select Script Tables As -Create Table -New Query
Window).

SELECT E.enquiryID,
E.introducerID,
E.enquiry,
E.enquiryDateTi me,
I.networkName,
I.introducerNam e
FROM dbo.Enquiries AS E
JOIN dbo.Introducers AS I
ON E.introducerID = I.introducerID
WHERE I.networkName = 'Network Name'
AND E.enquiryDateTi me >= DATEADD(DAY, DATEDIFF(DAY, 0,
CURRENT_TIMESTA MP) -
30, 0);

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[leebridgewater]

Hi,

First Table:

USE [CLS]
GO
/****** Object: Table [dbo].[enquiries] Script Date: 05/26/2008
22:26:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFI ER ON
GO
CREATE TABLE [dbo].[enquiries](
[enquiryID] [int] IDENTITY(1014,1 ) NOT NULL,
[introducerID] [int] NULL,
[status] [nvarchar](50) NULL CONSTRAINT [DF_enquiries_st atus]
DEFAULT ('Received'),
[firstName] [nvarchar](50) NULL,
[lastName] [nvarchar](50) NULL,
[telephone] [nvarchar](50) NULL,
[mobile] [nvarchar](50) NULL,
[enquiryDateTime] [smalldatetime] NULL CONSTRAINT
[DF_enquiries_en quiryDateTime] DEFAULT (getdate()),
[requirements] [text] NULL,
CONSTRAINT [PK_enquiries] PRIMARY KEY CLUSTERED
(
[enquiryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORE COMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCK S = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[enquiries] WITH CHECK ADD CONSTRAINT
[FK_enquiries_in troducers] FOREIGN KEY([introducerID])
REFERENCES [dbo].[introducers] ([introducerID])
GO
ALTER TABLE [dbo].[enquiries] CHECK CONSTRAINT
[FK_enquiries_in troducers]
and the second table:

USE [CLS]
GO
/****** Object: Table [dbo].[introducers] Script Date: 05/26/2008
22:28:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFI ER ON
GO
CREATE TABLE [dbo].[introducers](
[introducerID] [int] IDENTITY(34,1) NOT NULL,
[networkName] [nvarchar](50) NULL,
[firstName] [nvarchar](50) NULL,
[lastName] [nvarchar](50) NULL,
[companyName] [nvarchar](50) NULL,
[telephone] [nvarchar](50) NULL,
[fax] [nvarchar](50) NULL,
[mobile] [nvarchar](50) NULL,
[email] [nvarchar](100) NULL,
[password] [nvarchar](50) NULL,
[addressLine1] [nvarchar](50) NULL,
[addressLine2] [nvarchar](50) NULL,
[addressLine3] [nvarchar](50) NULL,
[city] [nvarchar](50) NULL,
[county] [nvarchar](50) NULL,
[postcode] [nvarchar](50) NULL,
[introducerCreat eDate] [smalldatetime] NULL CONSTRAINT
[DF_introducers_ introducerCreat eDate] DEFAULT (getdate()),
CONSTRAINT [PK_introducers] PRIMARY KEY CLUSTERED
(
[introducerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORE COMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCK S = ON) ON [PRIMARY]
) ON [PRIMARY]
For some reason I have to enter any reference to specific tables
within SSMS as databaseName.db o.tableName (in my case
cls.dbo.enquiri es)

Thanks for your help and patience!!

Lee

[Plamen Ratchev]

Yes, you do not have the column names that you posted in your original post.
Here is adjusted query (just replace 'Network Name' in the WHERE filter with
the network name you are looking for):

SELECT E.enquiryID,
E.introducerID,
COALESCE(E.firs tName + ' ', '') + COALESCE(E.last Name, '') AS
enquiry,
E.enquiryDateTi me,
I.networkName,
COALESCE(I.firs tName + ' ', '') + COALESCE(I.last Name, '') AS
introducerName,
I.companyName
FROM dbo.Enquiries AS E
JOIN dbo.Introducers AS I
ON E.introducerID = I.introducerID
WHERE I.networkName = 'Network Name'
AND E.enquiryDateTi me >= DATEADD(DAY, DATEDIFF(DAY, 0,
CURRENT_TIMESTA MP) - 30, 0);

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Plamen Ratchev]

Actually this version is better (noted the column networkName is NVARCHAR):

SELECT E.enquiryID,
E.introducerID,
COALESCE(E.firs tName + ' ', '') + COALESCE(E.last Name, '') AS
enquiry,
E.enquiryDateTi me,
I.networkName,
COALESCE(I.firs tName + ' ', '') + COALESCE(I.last Name, '') AS
introducerName,
I.companyName
FROM dbo.Enquiries AS E
JOIN dbo.Introducers AS I
ON E.introducerID = I.introducerID
WHERE I.networkName = N'Network Name'
AND E.enquiryDateTi me >= DATEADD(DAY, DATEDIFF(DAY, 0,
CURRENT_TIMESTA MP) - 30, 0);

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[leebridgewater]

Hi, many thanks that worked perfectly!

I'll study the statement in more detail so that I can learn from it
and understand how it functions.

I just had to enter the following modification to allow it to work on
my setup:

FROM cls.dbo.Enquiri es AS E
JOIN cls.dbo.Introdu cers AS I

Just for clarification - I guess I have to enter the cls.dbo.enquiri es
instead of dbo.enquiries because I have multiple databases within SQL
Server?

Once again, many many thanks for your help.

Lee

[Plamen Ratchev]

If you run the query when connected to the CLS database, then you do not
have to specify the database name in the query. Most likely you connect with
user account that has a different default database.

You can add on top of the query:

USE CLS
GO

.... query follows here

That will change the current database and run the query with no need to
prefix with database name.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[leebridgewater]

Perfect, thanks.

Looks like I've got some learning to do!!

Your a real asset to this group.

All the best,

Lee