Solved

t-sql query

Posted on 2014-01-16
1
321 Views
Last Modified: 2014-01-16
I'm using Sql Server 2008 R2.

I have a table called EmployeeTestTable

It looks like this:

my table
This is the script to create this table:

CREATE TABLE [dbo].[EmployeesTestTable](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[LastName] [nvarchar](20) NOT NULL,
	[FirstName] [nvarchar](10) NOT NULL,
	[BirthDate] [datetime] NULL,
	[HireDate] [datetime] NULL,
	[Address] [nvarchar](60) NULL,
	[City] [nvarchar](15) NULL,
	[PostalCode] [nvarchar](10) NULL,
	[Country] [nvarchar](15) NULL,
	[HomePhone] [nvarchar](24) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[EmployeesTestTable] ON
INSERT [dbo].[EmployeesTestTable] ([EmployeeID], [LastName], [FirstName], [BirthDate], [HireDate], [Address], [City], [PostalCode], [Country], [HomePhone]) VALUES (1, N'Davolio', N'Nancy', CAST(0x000045D100000000 AS DateTime), CAST(0x000083BB00000000 AS DateTime), N'507 - 20th Ave. E.
Apt. 2A', N'Seattle', N'98122', N'USA', N'(206) 555-9857')
INSERT [dbo].[EmployeesTestTable] ([EmployeeID], [LastName], [FirstName], [BirthDate], [HireDate], [Address], [City], [PostalCode], [Country], [HomePhone]) VALUES (2, N'Fuller', N'Andrew', CAST(0x00004A6100000000 AS DateTime), CAST(0x0000842400C5C100 AS DateTime), N'908 W. Capital Way', N'Tacoma', N'98401', N'USA', N'(206) 555-9482')
INSERT [dbo].[EmployeesTestTable] ([EmployeeID], [LastName], [FirstName], [BirthDate], [HireDate], [Address], [City], [PostalCode], [Country], [HomePhone]) VALUES (3, N'Leverling', N'Janet', CAST(0x00005AD300000000 AS DateTime), CAST(0x0000839D00000000 AS DateTime), N'722 Moss Bay Blvd.', N'Kirkland', N'98033', N'USA', N'(206) 555-3412')
INSERT [dbo].[EmployeesTestTable] ([EmployeeID], [LastName], [FirstName], [BirthDate], [HireDate], [Address], [City], [PostalCode], [Country], [HomePhone]) VALUES (4, N'Peacock', N'Margaret', CAST(0x000035CF00000000 AS DateTime), CAST(0x0000852A0083D600 AS DateTime), N'4110 Old Redmond Rd.', N'Redmond', N'98052', N'USA', N'(206) 555-8122')
INSERT [dbo].[EmployeesTestTable] ([EmployeeID], [LastName], [FirstName], [BirthDate], [HireDate], [Address], [City], [PostalCode], [Country], [HomePhone]) VALUES (5, N'Buchanan', N'Steven', CAST(0x00004EB600000000 AS DateTime), CAST(0x000085D100000000 AS DateTime), N'14 Garrett Hill', N'London', N'SW1 8JR', N'UK', N'(71) 555-4848')
INSERT [dbo].[EmployeesTestTable] ([EmployeeID], [LastName], [FirstName], [BirthDate], [HireDate], [Address], [City], [PostalCode], [Country], [HomePhone]) VALUES (6, N'Suyama', N'Michael', CAST(0x00005A9800000000 AS DateTime), CAST(0x000085D100000000 AS DateTime), N'Coventry House
Miner Rd.', N'London', N'EC2 7JR', N'UK', N'(71) 555-7773')
INSERT [dbo].[EmployeesTestTable] ([EmployeeID], [LastName], [FirstName], [BirthDate], [HireDate], [Address], [City], [PostalCode], [Country], [HomePhone]) VALUES (7, N'King', N'Robert', CAST(0x0000562F00000000 AS DateTime), CAST(0x0000861E00000000 AS DateTime), N'Edgeham Hollow
Winchester Way', N'London', N'RG1 9SP', N'UK', N'(71) 555-5598')
INSERT [dbo].[EmployeesTestTable] ([EmployeeID], [LastName], [FirstName], [BirthDate], [HireDate], [Address], [City], [PostalCode], [Country], [HomePhone]) VALUES (8, N'Callahan', N'Laura', CAST(0x000052C800000000 AS DateTime), CAST(0x0000865C00000000 AS DateTime), N'4726 - 11th Ave. N.E.', N'Seattle', N'98105', N'USA', N'(206) 555-1189')
INSERT [dbo].[EmployeesTestTable] ([EmployeeID], [LastName], [FirstName], [BirthDate], [HireDate], [Address], [City], [PostalCode], [Country], [HomePhone]) VALUES (9, N'Dodsworth', N'Anne', CAST(0x00005E4400000000 AS DateTime), CAST(0x0000875B00000000 AS DateTime), N'7 Houndstooth Rd.', N'London', N'WG2 7LT', N'UK', N'(71) 555-4444')
SET IDENTITY_INSERT [dbo].[EmployeesTestTable] OFF

Open in new window


I created a query that looks like this:

DECLARE @FromDate as varchar(50), @ToDate as varchar(50), @EmployeeID as varchar(50), @LastName as char(50)

SET @FromDate = '07/01/1937'
SET @ToDate = '10/12/1968'
SET @EmployeeID = '0'
SET @LastName = '?'

SELECT [EmployeeID]
      ,[LastName]
      ,[FirstName]
      ,[BirthDate]
FROM [Northwind].[dbo].[EmployeesTestTable]
WHERE (([BirthDate] >= @FromDate ) And ([BirthDate] <= @ToDate)) 
      AND (
	     (([EmployeeID] = @EmployeeID) Or (@EmployeeID = '0')) 
		   Or
             (([LastName] = @LastName) Or (@LastName  = '?'))
	   )

Open in new window


The logic for my where clause is this:

my logic table for my where clause
The two pink rows are the ones that are incorrect.

When I use this query

DECLARE @FromDate as varchar(50), @ToDate as varchar(50), @EmployeeID as varchar(50), @LastName as char(50)
SET @FromDate = '07/01/1937'
SET @ToDate = '10/12/1968'
SET @EmployeeID = '3'
SET @LastName = '?'

SELECT [EmployeeID]
      ,[LastName]
      ,[FirstName]
      ,[BirthDate]
FROM [Northwind].[dbo].[EmployeesTestTable]
WHERE (([BirthDate] >= @FromDate ) And ([BirthDate] <= @ToDate)) 
      AND (
	     (([EmployeeID] = @EmployeeID) Or (@EmployeeID = '0')) 
		   Or
             (([LastName] = @LastName) Or (@LastName  = '?'))
	   )

Open in new window


I get this:

one of my wrong results
But for these parameter values, I want it to only return the employee with and EmployeeID of 3.


When I use this query:

DECLARE @FromDate as varchar(50), @ToDate as varchar(50), @EmployeeID as varchar(50), @LastName as char(50)
SET @FromDate = '07/01/1937'
SET @ToDate = '10/12/1968'
SET @EmployeeID = '0'
SET @LastName = 'Davolio'

SELECT [EmployeeID]
      ,[LastName]
      ,[FirstName]
      ,[BirthDate]
FROM [Northwind].[dbo].[EmployeesTestTable]
WHERE (([BirthDate] >= @FromDate ) And ([BirthDate] <= @ToDate)) 
      AND (
	     (([EmployeeID] = @EmployeeID) Or (@EmployeeID = '0')) 
		   Or
             (([LastName] = @LastName) Or (@LastName  = '?'))
	   )

Open in new window


I get this:

one of my wrong results
But for these parameter values, I want it to only return the employee with LastName of Davolio.


So my actual  correct truth table looks like this:

my correct truth table
So how do I modify my where clause to abide my correct truth table?
Can anyone help?
0
Comment
Question by:maqskywalker
1 Comment
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 39786359
SELECT [EmployeeID]
      ,[LastName]
      ,[FirstName]
      ,[BirthDate]
FROM [Northwind].[dbo].[EmployeesTestTable]
WHERE
  ([BirthDate] >= @FromDate ) And ([BirthDate] <= @ToDate)      AND
  ([EmployeeID] = @EmployeeID Or @EmployeeID = '0')          AND
  ([LastName] = @LastName Or @LastName  = '?')
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question