Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

t-sql query

Posted on 2014-01-16
1
Medium Priority
?
388 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 41

Accepted Solution

by:
Kyle Abrahams earned 2000 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

618 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