Solved

t-sql query

Posted on 2014-01-16
1
308 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

910 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now