Solved

t-sql query

Posted on 2014-01-16
1
359 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

726 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