[Resolved] Adding to the WHERE clause using LIKE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

[Resolved] Adding to the WHERE clause using LIKE

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-11-25 : 13:09:25

I have a working proc where I need to modify and add to the "Where" clause. I have added @EmployeeName as parameter and also added the same to the where clause. Unless @EmplyeeName is blank the query does not produce result. Is there something wrong with my "WHERE' statement?

CREATE PROCEDURE sp_Vecellio_LaborDetailInquiry1 

@JobNumber int,
@ItemNumber char(20),
@DateFrom datetime = Null,
@DateTo datetime = Null,
@EmployeeName char(15) = Null
as

CREATE TABLE	#LaborListTable
		(
			job_date datetime,
			cost_code char(10),
			pay_type char(5),
			pay_text char(50),
			equipment_id char(10),
			equipment_name char(100),
			employee_name char(15),
			job_craft char(15),
			labor_hours decimal(9,2),
			equipment_hours decimal(9,2),
			labor_start datetime,
			labor_stop datetime,
			labor_lunch datetime,
			labor_rest datetime,
                                        event_name char(1)
			                  		)

INSERT INTO   #LaborListTable (job_date, 
                                                      cost_code, 
                                                      pay_type, 
                                                      pay_text, 
			             --equipment_id, 
                                                      --equipment_name, 
                                                      employee_name,
			              job_craft,
                                                      labor_hours,
                                                      --equipment_hours,
                                                      labor_start,
                                                      labor_stop,
                                                      labor_lunch,
                                                      labor_rest,
                                                      event_name) 
                          
SELECT	dbo.Batch.ReportDate as job_date, 
	  	dbo.Item.CompanyItemId as cost_code,
		pay_type = '40',
		pay_text = 'Regular Time',
		substring(dbo.Employee.Name, 1, 14) as employee_name,
	    	substring(dbo.JobCraft.Name, 1, 15) as job_craft,
	    	dbo.EmployeeLaborEvent.Hours labor_hours,
	    	e.Start,
		e.[Stop],
		e.Lunch,
		e.Rest,
                           substring(dbo.EventStatusType.Name, 1, 1) as event_name  	
FROM		Job
inner join	[Event] on dbo.Event.Jobguid = dbo.Job.Jobguid
inner join            dbo.EventStatusType on dbo.EventStatusType.EventStatusTypeGuid = dbo.Event.EventStatus
inner join	Item on dbo.Item.Itemguid = dbo.Event.Itemguid
inner join	dbo.EmployeeLaborEvent on dbo.EmployeeLaborEvent.EventGuid = dbo.Event.EventGuid 
inner join	dbo.Employee on dbo.Employee.EmployeeGuid = dbo.EmployeeLaborEvent.EmployeeGuid
inner join	dbo.JobCraft on dbo.JobCraft.JobCraftGuid = dbo.EmployeeLaborEvent.JobCraftGuid
inner join	dbo.Batch on dbo.Batch.Batchguid = dbo.Event.Batchguid

LEFT JOIN	(
			SELECT	e.BatchGuid,
					ete.EmployeeGuid,
					MAX(CASE etet.Name WHEN 'Start' THEN ete.Time ELSE NULL END) AS Start,
					MAX(CASE etet.Name WHEN 'Stop' THEN ete.Time ELSE NULL END) AS [Stop],
					MAX(CASE etet.Name WHEN 'Lunch' THEN ete.Time ELSE NULL END) AS Lunch,
					MAX(CASE etet.Name WHEN 'Break' THEN ete.Time ELSE NULL END) AS Rest
			FROM		dbo.Event AS e
			INNER JOIN	EmployeeTimeEvent AS ete ON ete.EventGuid = e.EventGuid --and ete.EmployeeGuid = emp.EmployeeGuid
			INNER JOIN      EmployeeLaborEvent as emp on emp.EmployeeGuid = ete.EmployeeGuid
			INNER JOIN	EmployeeTimeEventType AS etet ON etet.EmployeeTimeEventTypeGuid =  ete.EmployeeTimeEventTypeGuid
			GROUP BY	e.BatchGuid,
					ete.EmployeeGuid
		) AS e ON e.BatchGuid = dbo.Batch.BatchGuid
		  AND e.EmployeeGuid = dbo.EmployeeLaborEvent.EmployeeGuid


WHERE               dbo.Job.CompanyJobId = @JobNumber 
                            and dbo.Item.CompanyItemId = @ItemNumber 
                            and ( @DateFrom IS  Null OR dbo.Batch.Reportdate >=  @DateFrom)
                            and (@DateTo IS  Null OR dbo.Batch.ReportDate <= @DateTo)
                            and dbo.EmployeeLaborEvent.Hours <> 0
                           and (dbo.Employee.Name LIKE @EmployeeName + '%' OR @EmployeeName IS NULL or @EmployeeName = ' ')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 13:12:10

quote:

Originally posted by snufse

I have a working proc where I need to modify and add to the "Where" clause. I have added @EmployeeName as parameter and also added the same to the where clause. Unless @EmplyeeName is blank the query does not produce result. Is there something wrong with my "WHERE' statement?

CREATE PROCEDURE sp_Vecellio_LaborDetailInquiry1 

@JobNumber int,
@ItemNumber char(20),
@DateFrom datetime = Null,
@DateTo datetime = Null,
@EmployeeName varchar(15) = Null
as

CREATE TABLE	#LaborListTable
		(
			job_date datetime,
			cost_code char(10),
			pay_type char(5),
			pay_text char(50),
			equipment_id char(10),
			equipment_name char(100),
			employee_name char(15),
			job_craft char(15),
			labor_hours decimal(9,2),
			equipment_hours decimal(9,2),
			labor_start datetime,
			labor_stop datetime,
			labor_lunch datetime,
			labor_rest datetime,
                                        event_name char(1)
			                  		)

INSERT INTO   #LaborListTable (job_date, 
                                                      cost_code, 
                                                      pay_type, 
                                                      pay_text, 
			             --equipment_id, 
                                                      --equipment_name, 
                                                      employee_name,
			              job_craft,
                                                      labor_hours,
                                                      --equipment_hours,
                                                      labor_start,
                                                      labor_stop,
                                                      labor_lunch,
                                                      labor_rest,
                                                      event_name) 
                          
SELECT	dbo.Batch.ReportDate as job_date, 
	  	dbo.Item.CompanyItemId as cost_code,
		pay_type = '40',
		pay_text = 'Regular Time',
		substring(dbo.Employee.Name, 1, 14) as employee_name,
	    	substring(dbo.JobCraft.Name, 1, 15) as job_craft,
	    	dbo.EmployeeLaborEvent.Hours labor_hours,
	    	e.Start,
		e.[Stop],
		e.Lunch,
		e.Rest,
                           substring(dbo.EventStatusType.Name, 1, 1) as event_name  	
FROM		Job
inner join	[Event] on dbo.Event.Jobguid = dbo.Job.Jobguid
inner join            dbo.EventStatusType on dbo.EventStatusType.EventStatusTypeGuid = dbo.Event.EventStatus
inner join	Item on dbo.Item.Itemguid = dbo.Event.Itemguid
inner join	dbo.EmployeeLaborEvent on dbo.EmployeeLaborEvent.EventGuid = dbo.Event.EventGuid 
inner join	dbo.Employee on dbo.Employee.EmployeeGuid = dbo.EmployeeLaborEvent.EmployeeGuid
inner join	dbo.JobCraft on dbo.JobCraft.JobCraftGuid = dbo.EmployeeLaborEvent.JobCraftGuid
inner join	dbo.Batch on dbo.Batch.Batchguid = dbo.Event.Batchguid

LEFT JOIN	(
			SELECT	e.BatchGuid,
					ete.EmployeeGuid,
					MAX(CASE etet.Name WHEN 'Start' THEN ete.Time ELSE NULL END) AS Start,
					MAX(CASE etet.Name WHEN 'Stop' THEN ete.Time ELSE NULL END) AS [Stop],
					MAX(CASE etet.Name WHEN 'Lunch' THEN ete.Time ELSE NULL END) AS Lunch,
					MAX(CASE etet.Name WHEN 'Break' THEN ete.Time ELSE NULL END) AS Rest
			FROM		dbo.Event AS e
			INNER JOIN	EmployeeTimeEvent AS ete ON ete.EventGuid = e.EventGuid --and ete.EmployeeGuid = emp.EmployeeGuid
			INNER JOIN      EmployeeLaborEvent as emp on emp.EmployeeGuid = ete.EmployeeGuid
			INNER JOIN	EmployeeTimeEventType AS etet ON etet.EmployeeTimeEventTypeGuid =  ete.EmployeeTimeEventTypeGuid
			GROUP BY	e.BatchGuid,
					ete.EmployeeGuid
		) AS e ON e.BatchGuid = dbo.Batch.BatchGuid
		  AND e.EmployeeGuid = dbo.EmployeeLaborEvent.EmployeeGuid


WHERE               dbo.Job.CompanyJobId = @JobNumber 
                            and dbo.Item.CompanyItemId = @ItemNumber 
                            and ( @DateFrom IS  Null OR dbo.Batch.Reportdate >=  @DateFrom)
                            and (@DateTo IS  Null OR dbo.Batch.ReportDate <= @DateTo)
                            and dbo.EmployeeLaborEvent.Hours <> 0
                           and (dbo.Employee.Name LIKE @EmployeeName + '%' OR NULLIF(@EmployeeName,'') IS NULL)

make it varchar and see

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-11-25 : 14:17:33

Visakh16, worked great, thank you .........

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 16:34:23

This will be enough

and (dbo.Employee.Name LIKE ltrim(@EmployeeName + '%') OR @EmployeeName IS NULL)

Because if @EmployeeName is empty space or only spaces first condition is true.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources