maqskywalker
asked on
t-sql query
I'm using Sql Server 2008 R2.
I have a table called EmployeeTestTable
It looks like this:
This is the script to create this table:
I created a query that looks like this:
The logic for my where clause is this:
The two pink rows are the ones that are incorrect.
When I use this query
I get this:
But for these parameter values, I want it to only return the employee with and EmployeeID of 3.
When I use this query:
I get this:
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:
So how do I modify my where clause to abide my correct truth table?
Can anyone help?
I have a table called EmployeeTestTable
It looks like this:
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
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 = '?'))
)
The logic for my where clause is this:
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 = '?'))
)
I get this:
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 = '?'))
)
I get this:
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:
So how do I modify my where clause to abide my correct truth table?
Can anyone help?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.