473,556 Members | 2,427 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Simple Query Question

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
9 1750
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

Jun 27 '08 #2
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
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

Jun 27 '08 #4
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
Jun 27 '08 #5
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

Jun 27 '08 #6
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

Jun 27 '08 #7
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
Jun 27 '08 #8
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
1928
by: Maciej Nadolski | last post by:
Hi! I`ve got a simple question but I`m puzzled:( When I create variable: for example $query for query to MySQL its obvieus that I want to use variables. Now should I do something like that: 1) $query = "blahblahblah".$variable1."blahblahblah" ."blahblahblah".$variable2."blahblahblah"; OR 2) $query = "blahblahblah."$variable1."blahblahblah"...
3
3668
by: Patchwork | last post by:
Hi Everyone, Please take a look at the following (simple and fun) program: //////////////////////////////////////////////////////////////////////////// ///////////// // Monster Munch, example program #include <list>
6
3993
by: Eddie Smit | last post by:
field- field- surname town ---- ---- john NY john Vegas john Boston eddie Boston eddie New Orleans eddie NY
15
1996
by: Richard Hollenbeck | last post by:
For example, one college course has only 24 students in it, but the following code says there are zero As, 20 Bs, 16 Cs, 4 Ds, and 8 Fs. When it prints it then says 0 As, 40 Bs, 32 Cs, 8 Ds, and 16 Fs. The actual number it should show is zero As, 10 Bs, 8 Cs, 2 Ds, and 4 Fs. I can't find anything wrong with the code. Here it is: Option...
13
1843
by: Saber | last post by:
I did a lot of searches and read something about datagrids. But I couldn't find the answer of my simple question, how can I show only my desired columns of a table? for example I wrote this sql query: OleDbDataAdapter1.SelectCommand.CommandText = & _ "Select illNameE From tblIllness" OleDbDataAdapter1.Fill(DsIllness1) But in my...
2
2381
by: Fendi Baba | last post by:
I created a person table with various fields such as Suffix, Salutation, etc, Some of these fields may not be mandatory for example suffix. In the actual table itself, I only have a field for suffix ID where 1=Phd, 2= MD. To display all of these to the user, I created a form with an underlying query. The problem I am encountering is this,...
3
1457
by: Steven Blair | last post by:
Query application made simple? I have to use ASP.NET quite often to knock up quick protype applications. Generally, these applications have some components for querying and an area of screen for displaying the results. Using GridViews and SqlDataSource, I can almost make the application with no code. My where clause is handled by the...
3
2116
by: psuwebmasters | last post by:
I am doing some work developing a OneBox for a Google Mini. All I need to do is take a $_GET values from the Mini (in this case, specifically "query"), format it into a URI to pass off to another script, get the results from that script in XML, and hand that XML back off to the Mini. So basically it acts as a go-between, and simple...
3
2469
by: deejayquai | last post by:
Hello Simple one this I guess, but I'm quite stuck at the moment. I would like to update the records displayed in my listbox (lstStudents) using criteria selected from my combo (cboForm) in a form. My basic code is:
9
1488
by: muddasirmunir | last post by:
i have a simple query and does not getting desire results which i want i am using vb6 and access i had a table with with 8 fields but just to simplyfy by question i am just supposing to four. suppose i had a table with field Name----------City----------Type----------Value
0
7622
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7547
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8060
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7904
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5452
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3575
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2037
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1159
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
865
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.