Select Case Help - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Select Case Help

Author

Topic

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2012-05-07 : 21:04:59

Hello Guys Need Help.

I have create a query to calculate the Regular Hours, Special Holiday OT, Holiday OT, Rest Day OT, Company Holiday OT.

But it returns like this.

---|-----|----------|---------|-------|--------|--------|--------|
ID Name Department Reg Hrs SPL HOL HOL OT Rest Day Comp HOL
---|-----|----------|---------|-------|--------|--------|--------|
811 Steve HR NULL NULL NULL 20.50 NULL
811 Steve HR NULL NULL 11.50 NULL NULL
811 Steve HR NULL 11.50 NULL NULL NULL
811 Steve HR 160.00 NULL NULL NULL NULL

How can I make it to return like this.?

---|-----|----------|---------|-------|--------|--------|--------|
ID Name Department Reg Hrs SPL HOL HOL OT Rest Day Comp HOL
---|-----|----------|---------|-------|--------|--------|--------|
811 Steve HR 160.00 11.50 11.50 20.50 NULL

I think it has something to do with Group by but when I tried to remove it an error message appears.

Please see my query below.

select
distinct
t_employeelogledgerhist.ell_employeeid as ID,
T_EmployeeMaster.Emt_FirstName+ ' '+ T_EmployeeMaster.Emt_LastName as Name,

--Condition for the Department
case
when substring(Emt_CostCenterCode,1,4) = '0101' then 'Administration'
when substring(Emt_CostCenterCode,1,4) = '0102' then 'Production'
when substring(Emt_CostCenterCode,1,4) = '0103' then 'Production Engineering'
when substring(Emt_CostCenterCode,1,4) = '0104' then 'Quality Assurance'
when substring(Emt_CostCenterCode,1,4) = '0105' then 'Office of Factory GM'
end as Department,

--Condition for the Section
case
when substring(Emt_CostCenterCode,5,8) = '0102' then 'Safety & Environment'
when substring(Emt_CostCenterCode,5,8) = '02' then 'IT'
when substring(Emt_CostCenterCode,5,8) = '0304' then 'Human Resources'
when substring(Emt_CostCenterCode,5,8) = '0305' then 'General Affairs'
when substring(Emt_CostCenterCode,5,8) = '0406' then 'Purchasing'
when substring(Emt_CostCenterCode,5,8) = '0407' then 'Import & Export Control'
when substring(Emt_CostCenterCode,5,8) = '0408' then 'Warehouse Control'
when substring(Emt_CostCenterCode,5,8) = '05' then 'Finance & Accounting'
when substring(Emt_CostCenterCode,5,8) = '06' then 'Production'
when substring(Emt_CostCenterCode,5,8) = '0709' then 'Production Control'
when substring(Emt_CostCenterCode,5,8) = '1011' then 'Quality Assurance'
when substring(Emt_CostCenterCode,5,8) = '1012' then 'Quality Control'
when substring(Emt_CostCenterCode,5,8) = '08' then 'Process Engineering'
when substring(Emt_CostCenterCode,5,8) = '09' then 'Machine Maintenance'
when substring(Emt_CostCenterCode,5,8) = '11' then 'Facility Maintenance'
--Make the Null Row Blank
when substring(Emt_CostCenterCode,3,6) = '01' then ' '
when substring(Emt_CostCenterCode,3,6) = '02' then ' '
when substring(Emt_CostCenterCode,3,6) = '03' then ' '
when substring(Emt_CostCenterCode,3,6) = '0410' then ' '
when substring(Emt_CostCenterCode,3,6) = '05' then ' '
end as Section,

case
when
t_employeelogledgerhist.Ell_DayCode = 'REG'
then
sum(t_employeelogledgerhist.Ell_RegularHour) end as Reg_Hours,

case
when
t_employeelogledgerhist.Ell_DayCode = 'SPL'
then
(sum(t_employeelogledgerhist.Ell_RegularHour)
+
SUM(t_employeelogledgerhist.Ell_OvertimeHour)) end as "Special Holiday OT",

case
when
t_employeelogledgerhist.Ell_DayCode = 'HOL'
then
(sum(t_employeelogledgerhist.Ell_RegularHour)
+
SUM(t_employeelogledgerhist.Ell_OvertimeHour)) end as "Holiday OT",

case
when
t_employeelogledgerhist.Ell_DayCode = 'REST'
then
(sum(t_employeelogledgerhist.Ell_RegularHour)
+
SUM(t_employeelogledgerhist.Ell_OvertimeHour)) end as "Rest Day OT",

case
when
t_employeelogledgerhist.Ell_DayCode = 'Comp'
then
(sum(t_employeelogledgerhist.Ell_RegularHour)
+
SUM(t_employeelogledgerhist.Ell_OvertimeHour)) end as "Company Holiday OT"

from T_EmployeeLogLedgerHist
cross join T_EmployeeMaster

where Ell_PayPeriod in ('2012042','2012041')
and
Ell_EmployeeId like '80011'
and
T_EmployeeLogLedgerHist.Ell_EmployeeId = t_employeemaster.Emt_EmployeeID
group by T_EmployeeLogLedgerHist.Ell_EmployeeId, t_employeemaster.Emt_FirstName,T_EmployeeMaster.Emt_LastName,
T_EmployeeMaster.Emt_CostCenterCode,t_employeelogledgerhist.Ell_DayCode

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-07 : 21:14:52

Wrap your existing query in CTE and do a group by like this:

;WITH cte AS (
-- YOUR CURRENT QUERY HERE
)
SELECT
	ID,
	[Name],
	Department,
	MAX(Reg_Hours) AS Reg_Hours,
	MAX([Special Holiday OT]) AS [Special Holiday OT],
	MAX([Holiday OT]) AS [Holiday OT],
	MAX([Rest Day OT]) AS [Rest Day OT],
	MAX([Company Holiday OT]) AS [Company Holiday OT]
GROUP BY
	ID,
	[Name],
	Department;

Once you have the right results, there may be opportunities to make the code more compact and efficient.

One thing that stands out, for example, is the way you are generating Department and Section columns. It would be more flexible and probably more efficient if you have reference tables that have Departments and Sections and join with those tables.

Also, it looks like Emt_CostCenterCode packs different pieces of information into it - characters 1 to 4 for Department, 5 to 8 for Section etc. A more normalized table would make querying easier (assuming of course you have the permissions and opportunity to do so).

Ok, I will get off my soap box and shut up :)

Gerald30
Yak Posting Veteran

62 Posts

Posted - 2012-05-08 : 01:57:47

Thank you very much for the help.

I for the Department and Section part It was just being dump into once column and so I just have to use sub string it to put a value.

Thanks again for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-11 : 15:17:49

quote:
Originally posted by Gerald30

Thank you very much for the help.

I for the Department and Section part It was just being dump into once column and so I just have to use sub string it to put a value.

Thanks again for the help.

ideally you should have them as separate column values to avoid unnecessary splitting logic to get the values from Emt_CostCenterCode field everywhere in query where you need to use them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources