By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,266 Members | 1,312 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,266 IT Pros & Developers. It's quick & easy.

Simple Query Question

P: n/a
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
Jun 27 '08 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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.introducerName
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_TIMESTAMP) -
30, 0);

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Jun 27 '08 #2

P: n/a
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
Jun 27 '08 #3

P: n/a
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.enquiryDateTime,
I.networkName,
I.introducerName
FROM dbo.Enquiries AS E
JOIN dbo.Introducers AS I
ON E.introducerID = I.introducerID
WHERE I.networkName = 'Network Name'
AND E.enquiryDateTime >= DATEADD(DAY, DATEDIFF(DAY, 0,
CURRENT_TIMESTAMP) -
30, 0);

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Jun 27 '08 #4

P: n/a
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_IDENTIFIER ON
GO
CREATE TABLE [dbo].[enquiries](
[enquiryID] [int] IDENTITY(1014,1) NOT NULL,
[introducerID] [int] NULL,
[status] [nvarchar](50) NULL CONSTRAINT [DF_enquiries_status]
DEFAULT ('Received'),
[firstName] [nvarchar](50) NULL,
[lastName] [nvarchar](50) NULL,
[telephone] [nvarchar](50) NULL,
[mobile] [nvarchar](50) NULL,
[enquiryDateTime] [smalldatetime] NULL CONSTRAINT
[DF_enquiries_enquiryDateTime] DEFAULT (getdate()),
[requirements] [text] NULL,
CONSTRAINT [PK_enquiries] PRIMARY KEY CLUSTERED
(
[enquiryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[enquiries] WITH CHECK ADD CONSTRAINT
[FK_enquiries_introducers] FOREIGN KEY([introducerID])
REFERENCES [dbo].[introducers] ([introducerID])
GO
ALTER TABLE [dbo].[enquiries] CHECK CONSTRAINT
[FK_enquiries_introducers]
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_IDENTIFIER 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,
[introducerCreateDate] [smalldatetime] NULL CONSTRAINT
[DF_introducers_introducerCreateDate] DEFAULT (getdate()),
CONSTRAINT [PK_introducers] PRIMARY KEY CLUSTERED
(
[introducerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
For some reason I have to enter any reference to specific tables
within SSMS as databaseName.dbo.tableName (in my case
cls.dbo.enquiries)

Thanks for your help and patience!!

Lee
Jun 27 '08 #5

P: n/a
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.firstName + ' ', '') + COALESCE(E.lastName, '') AS
enquiry,
E.enquiryDateTime,
I.networkName,
COALESCE(I.firstName + ' ', '') + COALESCE(I.lastName, '') 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.enquiryDateTime >= DATEADD(DAY, DATEDIFF(DAY, 0,
CURRENT_TIMESTAMP) - 30, 0);

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Jun 27 '08 #6

P: n/a
Actually this version is better (noted the column networkName is NVARCHAR):

SELECT E.enquiryID,
E.introducerID,
COALESCE(E.firstName + ' ', '') + COALESCE(E.lastName, '') AS
enquiry,
E.enquiryDateTime,
I.networkName,
COALESCE(I.firstName + ' ', '') + COALESCE(I.lastName, '') 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.enquiryDateTime >= DATEADD(DAY, DATEDIFF(DAY, 0,
CURRENT_TIMESTAMP) - 30, 0);

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Jun 27 '08 #7

P: n/a
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.Enquiries AS E
JOIN cls.dbo.Introducers AS I

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

Once again, many many thanks for your help.

Lee
Jun 27 '08 #8

P: n/a
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

Jun 27 '08 #9

P: n/a
Perfect, thanks.

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

Your a real asset to this group.

All the best,

Lee
Jun 27 '08 #10

This discussion thread is closed

Replies have been disabled for this discussion.