Fine Tuning - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Fine Tuning

Author

Topic

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2004-04-05 : 14:33:04

Attached is a query I've been working on. The query works like a dream and now I just need a little fine tuning to take it up another notch.

In this query there are two work Groups that I am focusing on at station 752. 'PSVC' and 'RSVC'. At the end of the query the "Having SUM" is a filter that is providing data of where the "points" value is >= 2.5.

This "points" value is perfect for the 'PSVC' group, but is incorrect for the 'RSVC' group as they are not on a "points" system and I need to see everyone. I tried to seperate the two groups in the WHERE area and move the "HAVING SUM" function into a mini query with the 'PSVC' group but keep getting an error.
Here is the original query:

SELECT BranchCode as 'Branch',
count(*)'Emp NN C23',
SUM([C23No]) as '# Completed',
SUM([C23Yes]) as '# Needed',
SUM([AIC]) as '# On AIC',
SUM([1STADV]) as '# On 1STADV',
SUM([2NDADV]) as '# On 2NDADV',
SUM([TRMWRN]) as '# On TRMWRN',
SUM([DRSLIP]) as '# On Dr Slip'
From(
SELECT losttime.CTwentyOne.EmployeeName,
losttime.CTwentyOne.EmployeeNumber,
losttime.Profit_Centers_Table.AirportCode,
losttime.CTwentyOne.BranchCode,
losttime.CTwentyOne.Shopcode,
losttime.CTwentyOne.AbsenceCode,
losttime.CTwentyOne.AbsenceStartDate,
losttime.CTwentyOne.AbsenceStopDate,
CASE
WHEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate) = DATENAME (MONTH,GETDATE()) and DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)= DATENAME (YEAR,GETDATE())THEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate)
WHEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate) = DATENAME (MONTH,(DATEADD(MONTH, -1, GETDATE()))) and DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)= DATENAME (YEAR,GETDATE())THEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate)
WHEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate) = DATENAME (MONTH,(DATEADD(MONTH, -2, GETDATE()))) and DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)= DATENAME (YEAR,GETDATE())THEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate)
WHEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate) = DATENAME (MONTH,(DATEADD(MONTH, -3, GETDATE()))) and DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)= DATENAME (YEAR,(DATEADD(Year,-1,GETDATE())))THEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate)
WHEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate) = DATENAME (MONTH,(DATEADD(MONTH, -4, GETDATE()))) and DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)= DATENAME (YEAR,(DATEADD(Year,-1,GETDATE())))THEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate)
ELSE DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)
END 'AbsenceMonth / Year',
losttime.CTwentyOne.AbsenceHours,
losttime.CTwentyOne.C23DiscussionDate,
CASE WHEN (losttime.CTwentyOne.C23DiscussionDate IS NULL or losttime.CTwentyOne.C23DiscussionDate < CTwentyOne.AbsenceStopDate)THEN 1 ELSE 0 END 'C23YES',
CASE WHEN (losttime.CTwentyOne.C23DiscussionDate >= CTwentyOne.AbsenceStopDate)THEN 1 ELSE 0 END 'C23NO',
CASE
WHEN losttime.CTwentyOne.C23DiscussionDate >= CTwentyOne.AbsenceStopDate THEN 'Completed'
ELSE CONVERT(varchar(20),DATEDIFF(dd,CTwentyOne.AbsenceStopDate, getdate()))
END AS DaysOverDue,
CASE
WHEN losttime.CTwentyOne.C23DiscussionDate IS NULL THEN 'Needed'
WHEN DATEDIFF(dd,CTwentyOne.AbsenceStopDate, losttime.CTwentyOne.C23DiscussionDate) < 0 THEN 'Needed'
ELSE CONVERT(varchar(20), DATEDIFF(dd,CTwentyOne.AbsenceStopDate, losttime.CTwentyOne.C23DiscussionDate))
END AS DaysC23CompletedIn,
losttime.CTwentyOne.SelfManaged,
losttime.CTwentyOne.CorrectiveAction,
CASE WHEN (losttime.CTwentyOne.CorrectiveAction = 'AIC')THEN 1 ELSE 0 END 'AIC',
CASE WHEN (losttime.CTwentyOne.CorrectiveAction = '1STADV')THEN 1 ELSE 0 END '1STADV',
CASE WHEN (losttime.CTwentyOne.CorrectiveAction = '2NDADV')THEN 1 ELSE 0 END '2NDADV',
CASE WHEN (losttime.CTwentyOne.CorrectiveAction = 'TRMWRN')THEN 1 ELSE 0 END 'TRMWRN',
losttime.CTwentyOne.CorrectiveAction_DateOf,
losttime.CTwentyOne.DrSlipStartDate,
losttime.CTwentyOne.DrSlipStopDate,
CASE WHEN losttime.CTwentyOne.DrSlipStartDate <= GETDATE() and losttime.CTwentyOne.DrSlipStartDate >= GETDATE()- 365 THEN 1 ELSE 0 END 'DRSLIP',
CASE
WHEN losttime.CTwentyOne.DrSlipStartDate IS NULL THEN 'NO'
WHEN losttime.CTwentyOne.DrSlipStartDate <= GETDATE() and losttime.CTwentyOne.DrSlipStartDate >= GETDATE()- 365 THEN 'YES'
ELSE 'NO'
END 'Currently on Dr Slip',
SUM(losttime.CTwentyOne.SKL_Count
+ losttime.CTwentyOne.ID_Count
+ losttime.CTwentyOne.IU_Count
+ losttime.CTwentyOne.NC_Count
+ losttime.CTwentyOne.PO_Count
+ losttime.CTwentyOne.RL_Count
+ losttime.CTwentyOne.SK_Count
+ losttime.CTwentyOne.SKI_Count
+ losttime.CTwentyOne.SKP_Count
+ losttime.CTwentyOne.SKQ_Count
+ losttime.CTwentyOne.SKU_Count
+ losttime.CTwentyOne.UA_Count) AS TotalCount,
SUM(losttime.CTwentyOne.SKL_Hours
+ losttime.CTwentyOne.ID_Hours
+ losttime.CTwentyOne.IU_Hours
+ losttime.CTwentyOne.NC_Hours
+ losttime.CTwentyOne.PO_Hours
+ losttime.CTwentyOne.RL_Hours
+ losttime.CTwentyOne.SK_Hours
+ losttime.CTwentyOne.SKI_Hours
+ losttime.CTwentyOne.SKP_Hours
+ losttime.CTwentyOne.SKQ_Hours
+ losttime.CTwentyOne.SKU_Hours
+ losttime.CTwentyOne.UA_Hours) AS TotalHours,
SUM(losttime.CTwentyOne.SKL_Points
+ losttime.CTwentyOne.ID_Points
+ losttime.CTwentyOne.IU_Points
+ losttime.CTwentyOne.NC_Points
+ losttime.CTwentyOne.PO_Points
+ losttime.CTwentyOne.RL_Points
+ losttime.CTwentyOne.SK_Points
+ losttime.CTwentyOne.SKI_Points
+ losttime.CTwentyOne.SKP_Points
+ losttime.CTwentyOne.SKQ_Points
+ losttime.CTwentyOne.SKU_Points
+ losttime.CTwentyOne.UA_Points) AS TotalPoints
FROM losttime.CTwentyOne
JOIN losttime.Profit_Centers_Table ON losttime.CTwentyOne.StationCode = losttime.Profit_Centers_Table.StationCode
and losttime.CTwentyOne.CompanyCode = losttime.Profit_Centers_Table.CompanyCode
Join losttime.Branches_Table ON losttime.CTwentyOne.BranchCode = losttime.Branches_Table.BranchCode
Join losttime.PayCodes_Table ON losttime.CTwentyOne.AbsenceCode = losttime.PayCodes_Table.PayCode
WHERE losttime.CTwentyOne.CompanyCode = 'AA'
and losttime.CTwentyOne.EmployeeStatus = 'Active'
and losttime.CTwentyOne.StationCode = '752'
and losttime.CTwentyOne.AbsenceCode IN ('NC', 'PO', 'RL', 'SK', 'SKP', 'SKQ', 'SKU', 'UA')
and Branches_Table.BRANCHCATEGORYSMALL IN ('PSVC', 'RSVC')
and (losttime.Profit_Centers_Table.StationsReportingToCARGO <> 'CARGO'
or losttime.Branches_Table.CARGOCATEGORIES <> 'CGO - Field')
and (losttime.CTwentyOne.AbsenceStopDate <= GETDATE()-8)
GROUP BY losttime.CTwentyOne.EmployeeName,
losttime.CTwentyOne.EmployeeNumber,
losttime.Profit_Centers_Table.AirportCode,
losttime.CTwentyOne.BranchCode,
losttime.CTwentyOne.Shopcode,
losttime.CTwentyOne.AbsenceCode,
losttime.CTwentyOne.AbsenceStartDate,
losttime.CTwentyOne.AbsenceStopDate,
losttime.CTwentyOne.AbsenceHours,
losttime.CTwentyOne.C23DiscussionDate,
losttime.CTwentyOne.SelfManaged,
losttime.CTwentyOne.CorrectiveAction,
losttime.CTwentyOne.CorrectiveAction_DateOf,
losttime.CTwentyOne.DrSlipStartDate,
losttime.CTwentyOne.DrSlipStopDate
HAVING SUM(losttime.CTwentyOne.SKL_Points
+ losttime.CTwentyOne.ID_Points
+ losttime.CTwentyOne.IU_Points
+ losttime.CTwentyOne.NC_Points
+ losttime.CTwentyOne.PO_Points
+ losttime.CTwentyOne.RL_Points
+ losttime.CTwentyOne.SK_Points
+ losttime.CTwentyOne.SKI_Points
+ losttime.CTwentyOne.SKP_Points
+ losttime.CTwentyOne.SKQ_Points
+ losttime.CTwentyOne.SKU_Points
+ losttime.CTwentyOne.UA_Points) >= 2.5
) X
GROUP BY BranchCode
ORDER BY BranchCode

Here is the adjustment I tried :

and(Branches_Table.BRANCHCATEGORYSMALL = 'PSVC'
and (SELECT SUM(losttime.CTwentyOne.SKL_Points
+ losttime.CTwentyOne.ID_Points
+ losttime.CTwentyOne.IU_Points
+ losttime.CTwentyOne.NC_Points
+ losttime.CTwentyOne.PO_Points
+ losttime.CTwentyOne.RL_Points
+ losttime.CTwentyOne.SK_Points
+ losttime.CTwentyOne.SKI_Points
+ losttime.CTwentyOne.SKP_Points
+ losttime.CTwentyOne.SKQ_Points
+ losttime.CTwentyOne.SKU_Points
+ losttime.CTwentyOne.UA_Points) >= 2.5)FROM losttime.CTwentyOne)

And here is the error msg:

Server: Msg 170, Level 15, State 1, Line 112
Line 112: Incorrect syntax near '>'.

Am I close??? Thanks again for all the assistance in the past and with this query.

GC

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-05 : 15:13:56

and(Branches_Table.BRANCHCATEGORYSMALL = 'PSVC'
and (SELECT SUM(losttime.CTwentyOne.SKL_Points
+ losttime.CTwentyOne.ID_Points
+ losttime.CTwentyOne.IU_Points
+ losttime.CTwentyOne.NC_Points
+ losttime.CTwentyOne.PO_Points
+ losttime.CTwentyOne.RL_Points
+ losttime.CTwentyOne.SK_Points
+ losttime.CTwentyOne.SKI_Points
+ losttime.CTwentyOne.SKP_Points
+ losttime.CTwentyOne.SKQ_Points
+ losttime.CTwentyOne.SKU_Points
+ losttime.CTwentyOne.UA_Points) >= 2.5))
FROM losttime.CTwentyOne

??

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2004-04-05 : 15:30:12

Derrick,

I gave it a try and am still getting an error. Here is the query with the adjustment I tried.

SELECT BranchCode as 'Branch',
count(*)'Emp NN C23',
SUM([C23No]) as '# Completed',
SUM([C23Yes]) as '# Needed',
SUM([AIC]) as '# On AIC',
SUM([1STADV]) as '# On 1STADV',
SUM([2NDADV]) as '# On 2NDADV',
SUM([TRMWRN]) as '# On TRMWRN',
SUM([DRSLIP]) as '# On Dr Slip'
From(
SELECT losttime.CTwentyOne.EmployeeName,
losttime.CTwentyOne.EmployeeNumber,
losttime.Profit_Centers_Table.AirportCode,
losttime.CTwentyOne.BranchCode,
losttime.CTwentyOne.Shopcode,
losttime.CTwentyOne.AbsenceCode,
losttime.CTwentyOne.AbsenceStartDate,
losttime.CTwentyOne.AbsenceStopDate,
CASE
WHEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate) = DATENAME (MONTH,GETDATE()) and DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)= DATENAME (YEAR,GETDATE())THEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate)
WHEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate) = DATENAME (MONTH,(DATEADD(MONTH, -1, GETDATE()))) and DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)= DATENAME (YEAR,GETDATE())THEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate)
WHEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate) = DATENAME (MONTH,(DATEADD(MONTH, -2, GETDATE()))) and DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)= DATENAME (YEAR,GETDATE())THEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate)
WHEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate) = DATENAME (MONTH,(DATEADD(MONTH, -3, GETDATE()))) and DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)= DATENAME (YEAR,(DATEADD(Year,-1,GETDATE())))THEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate)
WHEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate) = DATENAME (MONTH,(DATEADD(MONTH, -4, GETDATE()))) and DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)= DATENAME (YEAR,(DATEADD(Year,-1,GETDATE())))THEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate)
ELSE DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)
END 'AbsenceMonth / Year',
losttime.CTwentyOne.AbsenceHours,
losttime.CTwentyOne.C23DiscussionDate,
CASE WHEN (losttime.CTwentyOne.C23DiscussionDate IS NULL or losttime.CTwentyOne.C23DiscussionDate < CTwentyOne.AbsenceStopDate)THEN 1 ELSE 0 END 'C23YES',
CASE WHEN (losttime.CTwentyOne.C23DiscussionDate >= CTwentyOne.AbsenceStopDate)THEN 1 ELSE 0 END 'C23NO',
CASE
WHEN losttime.CTwentyOne.C23DiscussionDate >= CTwentyOne.AbsenceStopDate THEN 'Completed'
ELSE CONVERT(varchar(20),DATEDIFF(dd,CTwentyOne.AbsenceStopDate, getdate()))
END AS DaysOverDue,
CASE
WHEN losttime.CTwentyOne.C23DiscussionDate IS NULL THEN 'Needed'
WHEN DATEDIFF(dd,CTwentyOne.AbsenceStopDate, losttime.CTwentyOne.C23DiscussionDate) < 0 THEN 'Needed'
ELSE CONVERT(varchar(20), DATEDIFF(dd,CTwentyOne.AbsenceStopDate, losttime.CTwentyOne.C23DiscussionDate))
END AS DaysC23CompletedIn,
losttime.CTwentyOne.SelfManaged,
losttime.CTwentyOne.CorrectiveAction,
CASE WHEN (losttime.CTwentyOne.CorrectiveAction = 'AIC')THEN 1 ELSE 0 END 'AIC',
CASE WHEN (losttime.CTwentyOne.CorrectiveAction = '1STADV')THEN 1 ELSE 0 END '1STADV',
CASE WHEN (losttime.CTwentyOne.CorrectiveAction = '2NDADV')THEN 1 ELSE 0 END '2NDADV',
CASE WHEN (losttime.CTwentyOne.CorrectiveAction = 'TRMWRN')THEN 1 ELSE 0 END 'TRMWRN',
losttime.CTwentyOne.CorrectiveAction_DateOf,
losttime.CTwentyOne.DrSlipStartDate,
losttime.CTwentyOne.DrSlipStopDate,
CASE WHEN losttime.CTwentyOne.DrSlipStartDate <= GETDATE() and losttime.CTwentyOne.DrSlipStartDate >= GETDATE()- 365 THEN 1 ELSE 0 END 'DRSLIP',
CASE
WHEN losttime.CTwentyOne.DrSlipStartDate IS NULL THEN 'NO'
WHEN losttime.CTwentyOne.DrSlipStartDate <= GETDATE() and losttime.CTwentyOne.DrSlipStartDate >= GETDATE()- 365 THEN 'YES'
ELSE 'NO'
END 'Currently on Dr Slip',
SUM(losttime.CTwentyOne.SKL_Count
+ losttime.CTwentyOne.ID_Count
+ losttime.CTwentyOne.IU_Count
+ losttime.CTwentyOne.NC_Count
+ losttime.CTwentyOne.PO_Count
+ losttime.CTwentyOne.RL_Count
+ losttime.CTwentyOne.SK_Count
+ losttime.CTwentyOne.SKI_Count
+ losttime.CTwentyOne.SKP_Count
+ losttime.CTwentyOne.SKQ_Count
+ losttime.CTwentyOne.SKU_Count
+ losttime.CTwentyOne.UA_Count) AS TotalCount,
SUM(losttime.CTwentyOne.SKL_Hours
+ losttime.CTwentyOne.ID_Hours
+ losttime.CTwentyOne.IU_Hours
+ losttime.CTwentyOne.NC_Hours
+ losttime.CTwentyOne.PO_Hours
+ losttime.CTwentyOne.RL_Hours
+ losttime.CTwentyOne.SK_Hours
+ losttime.CTwentyOne.SKI_Hours
+ losttime.CTwentyOne.SKP_Hours
+ losttime.CTwentyOne.SKQ_Hours
+ losttime.CTwentyOne.SKU_Hours
+ losttime.CTwentyOne.UA_Hours) AS TotalHours,
SUM(losttime.CTwentyOne.SKL_Points
+ losttime.CTwentyOne.ID_Points
+ losttime.CTwentyOne.IU_Points
+ losttime.CTwentyOne.NC_Points
+ losttime.CTwentyOne.PO_Points
+ losttime.CTwentyOne.RL_Points
+ losttime.CTwentyOne.SK_Points
+ losttime.CTwentyOne.SKI_Points
+ losttime.CTwentyOne.SKP_Points
+ losttime.CTwentyOne.SKQ_Points
+ losttime.CTwentyOne.SKU_Points
+ losttime.CTwentyOne.UA_Points) AS TotalPoints
FROM losttime.CTwentyOne
JOIN losttime.Profit_Centers_Table ON losttime.CTwentyOne.StationCode = losttime.Profit_Centers_Table.StationCode
and losttime.CTwentyOne.CompanyCode = losttime.Profit_Centers_Table.CompanyCode
Join losttime.Branches_Table ON losttime.CTwentyOne.BranchCode = losttime.Branches_Table.BranchCode
Join losttime.PayCodes_Table ON losttime.CTwentyOne.AbsenceCode = losttime.PayCodes_Table.PayCode
WHERE losttime.CTwentyOne.CompanyCode = 'AA'
and losttime.CTwentyOne.EmployeeStatus = 'Active'
and losttime.CTwentyOne.StationCode = '752'
and losttime.CTwentyOne.AbsenceCode IN ('NC', 'PO', 'RL', 'SK', 'SKP', 'SKQ', 'SKU', 'UA')
and(Branches_Table.BRANCHCATEGORYSMALL = 'PSVC'
and (SELECT SUM(losttime.CTwentyOne.SKL_Points
+ losttime.CTwentyOne.ID_Points
+ losttime.CTwentyOne.IU_Points
+ losttime.CTwentyOne.NC_Points
+ losttime.CTwentyOne.PO_Points
+ losttime.CTwentyOne.RL_Points
+ losttime.CTwentyOne.SK_Points
+ losttime.CTwentyOne.SKI_Points
+ losttime.CTwentyOne.SKP_Points
+ losttime.CTwentyOne.SKQ_Points
+ losttime.CTwentyOne.SKU_Points
+ losttime.CTwentyOne.UA_Points) >= 2.5))
FROM losttime.CTwentyOne
and Branches_Table.BRANCHCATEGORYSMALL = 'RSVC'
and (losttime.Profit_Centers_Table.StationsReportingToCARGO <> 'CARGO'
or losttime.Branches_Table.CARGOCATEGORIES <> 'CGO - Field')
and (losttime.CTwentyOne.AbsenceStopDate <= GETDATE()-8)
GROUP BY losttime.CTwentyOne.EmployeeName,
losttime.CTwentyOne.EmployeeNumber,
losttime.Profit_Centers_Table.AirportCode,
losttime.CTwentyOne.BranchCode,
losttime.CTwentyOne.Shopcode,
losttime.CTwentyOne.AbsenceCode,
losttime.CTwentyOne.AbsenceStartDate,
losttime.CTwentyOne.AbsenceStopDate,
losttime.CTwentyOne.AbsenceHours,
losttime.CTwentyOne.C23DiscussionDate,
losttime.CTwentyOne.SelfManaged,
losttime.CTwentyOne.CorrectiveAction,
losttime.CTwentyOne.CorrectiveAction_DateOf,
losttime.CTwentyOne.DrSlipStartDate,
losttime.CTwentyOne.DrSlipStopDate
) X
GROUP BY BranchCode
ORDER BY BranchCode

I tried moving the last ) around in several places and didn't have any luck.

Here is the error msg.

Server: Msg 170, Level 15, State 1, Line 112
Line 112: Incorrect syntax near '>'.

Thanks again.

GC

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-05 : 17:06:03

You had a select in the middle of here you didn't need. It was also throwing your paranthesis off. Try this out. I wish I had your ddl so I could test this. :)

SELECT
BranchCode as 'Branch',
count(*)'Emp NN C23',
SUM([C23No]) as '# Completed',
SUM([C23Yes]) as '# Needed',
SUM([AIC]) as '# On AIC',
SUM([1STADV]) as '# On 1STADV',
SUM([2NDADV]) as '# On 2NDADV',
SUM([TRMWRN]) as '# On TRMWRN',
SUM([DRSLIP]) as '# On Dr Slip'
From(
SELECT
losttime.CTwentyOne.EmployeeName,
losttime.CTwentyOne.EmployeeNumber,
losttime.Profit_Centers_Table.AirportCode,
losttime.CTwentyOne.BranchCode,
losttime.CTwentyOne.Shopcode,
losttime.CTwentyOne.AbsenceCode,
losttime.CTwentyOne.AbsenceStartDate,
losttime.CTwentyOne.AbsenceStopDate,
CASE
WHEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate) = DATENAME (MONTH,GETDATE()) and DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)= DATENAME (YEAR,GETDATE())THEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate)
WHEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate) = DATENAME (MONTH,(DATEADD(MONTH, -1, GETDATE()))) and DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)= DATENAME (YEAR,GETDATE())THEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate)
WHEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate) = DATENAME (MONTH,(DATEADD(MONTH, -2, GETDATE()))) and DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)= DATENAME (YEAR,GETDATE())THEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate)
WHEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate) = DATENAME (MONTH,(DATEADD(MONTH, -3, GETDATE()))) and DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)= DATENAME (YEAR,(DATEADD(Year,-1,GETDATE())))THEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate)
WHEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate) = DATENAME (MONTH,(DATEADD(MONTH, -4, GETDATE()))) and DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)= DATENAME (YEAR,(DATEADD(Year,-1,GETDATE())))THEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate)
ELSE DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)
END 'AbsenceMonth / Year',
losttime.CTwentyOne.AbsenceHours,
losttime.CTwentyOne.C23DiscussionDate,
CASE WHEN (losttime.CTwentyOne.C23DiscussionDate IS NULL or losttime.CTwentyOne.C23DiscussionDate < CTwentyOne.AbsenceStopDate)THEN 1 ELSE 0 END 'C23YES',
CASE WHEN (losttime.CTwentyOne.C23DiscussionDate >= CTwentyOne.AbsenceStopDate)THEN 1 ELSE 0 END 'C23NO',
CASE
WHEN losttime.CTwentyOne.C23DiscussionDate >= CTwentyOne.AbsenceStopDate THEN 'Completed'
ELSE CONVERT(varchar(20),DATEDIFF(dd,CTwentyOne.AbsenceStopDate, getdate()))
END AS DaysOverDue,
CASE
WHEN losttime.CTwentyOne.C23DiscussionDate IS NULL THEN 'Needed'
WHEN DATEDIFF(dd,CTwentyOne.AbsenceStopDate, losttime.CTwentyOne.C23DiscussionDate) < 0 THEN 'Needed'
ELSE CONVERT(varchar(20), DATEDIFF(dd,CTwentyOne.AbsenceStopDate, losttime.CTwentyOne.C23DiscussionDate))
END AS DaysC23CompletedIn,
losttime.CTwentyOne.SelfManaged,
losttime.CTwentyOne.CorrectiveAction,
CASE WHEN (losttime.CTwentyOne.CorrectiveAction = 'AIC')THEN 1 ELSE 0 END 'AIC',
CASE WHEN (losttime.CTwentyOne.CorrectiveAction = '1STADV')THEN 1 ELSE 0 END '1STADV',
CASE WHEN (losttime.CTwentyOne.CorrectiveAction = '2NDADV')THEN 1 ELSE 0 END '2NDADV',
CASE WHEN (losttime.CTwentyOne.CorrectiveAction = 'TRMWRN')THEN 1 ELSE 0 END 'TRMWRN',
losttime.CTwentyOne.CorrectiveAction_DateOf,
losttime.CTwentyOne.DrSlipStartDate,
losttime.CTwentyOne.DrSlipStopDate,
CASE WHEN losttime.CTwentyOne.DrSlipStartDate <= GETDATE() and losttime.CTwentyOne.DrSlipStartDate >= GETDATE()- 365 THEN 1 ELSE 0 END 'DRSLIP',
CASE
WHEN losttime.CTwentyOne.DrSlipStartDate IS NULL THEN 'NO'
WHEN losttime.CTwentyOne.DrSlipStartDate <= GETDATE() and losttime.CTwentyOne.DrSlipStartDate >= GETDATE()- 365 THEN 'YES'
ELSE 'NO'
END 'Currently on Dr Slip',
SUM(losttime.CTwentyOne.SKL_Count
+ losttime.CTwentyOne.ID_Count
+ losttime.CTwentyOne.IU_Count
+ losttime.CTwentyOne.NC_Count
+ losttime.CTwentyOne.PO_Count
+ losttime.CTwentyOne.RL_Count
+ losttime.CTwentyOne.SK_Count
+ losttime.CTwentyOne.SKI_Count
+ losttime.CTwentyOne.SKP_Count
+ losttime.CTwentyOne.SKQ_Count
+ losttime.CTwentyOne.SKU_Count
+ losttime.CTwentyOne.UA_Count) AS TotalCount,
SUM(losttime.CTwentyOne.SKL_Hours
+ losttime.CTwentyOne.ID_Hours
+ losttime.CTwentyOne.IU_Hours
+ losttime.CTwentyOne.NC_Hours
+ losttime.CTwentyOne.PO_Hours
+ losttime.CTwentyOne.RL_Hours
+ losttime.CTwentyOne.SK_Hours
+ losttime.CTwentyOne.SKI_Hours
+ losttime.CTwentyOne.SKP_Hours
+ losttime.CTwentyOne.SKQ_Hours
+ losttime.CTwentyOne.SKU_Hours
+ losttime.CTwentyOne.UA_Hours) AS TotalHours,
SUM(losttime.CTwentyOne.SKL_Points
+ losttime.CTwentyOne.ID_Points
+ losttime.CTwentyOne.IU_Points
+ losttime.CTwentyOne.NC_Points
+ losttime.CTwentyOne.PO_Points
+ losttime.CTwentyOne.RL_Points
+ losttime.CTwentyOne.SK_Points
+ losttime.CTwentyOne.SKI_Points
+ losttime.CTwentyOne.SKP_Points
+ losttime.CTwentyOne.SKQ_Points
+ losttime.CTwentyOne.SKU_Points
+ losttime.CTwentyOne.UA_Points) AS TotalPoints
FROM
losttime.CTwentyOne
JOIN losttime.Profit_Centers_Table ON losttime.CTwentyOne.StationCode = losttime.Profit_Centers_Table.StationCode
and losttime.CTwentyOne.CompanyCode = losttime.Profit_Centers_Table.CompanyCode
Join losttime.Branches_Table ON losttime.CTwentyOne.BranchCode = losttime.Branches_Table.BranchCode
Join losttime.PayCodes_Table ON losttime.CTwentyOne.AbsenceCode = losttime.PayCodes_Table.PayCode
WHERE
losttime.CTwentyOne.CompanyCode = 'AA'
and losttime.CTwentyOne.EmployeeStatus = 'Active'
and losttime.CTwentyOne.StationCode = '752'
and losttime.CTwentyOne.AbsenceCode IN ('NC', 'PO', 'RL', 'SK', 'SKP', 'SKQ', 'SKU', 'UA')
and(Branches_Table.BRANCHCATEGORYSMALL = 'PSVC'
and (SUM(losttime.CTwentyOne.SKL_Points
+ losttime.CTwentyOne.ID_Points
+ losttime.CTwentyOne.IU_Points
+ losttime.CTwentyOne.NC_Points
+ losttime.CTwentyOne.PO_Points
+ losttime.CTwentyOne.RL_Points
+ losttime.CTwentyOne.SK_Points
+ losttime.CTwentyOne.SKI_Points
+ losttime.CTwentyOne.SKP_Points
+ losttime.CTwentyOne.SKQ_Points
+ losttime.CTwentyOne.SKU_Points
+ losttime.CTwentyOne.UA_Points) >= 2.5
and Branches_Table.BRANCHCATEGORYSMALL = 'RSVC'
and (losttime.Profit_Centers_Table.StationsReportingToCARGO <> 'CARGO'
or losttime.Branches_Table.CARGOCATEGORIES <> 'CGO - Field')
and (losttime.CTwentyOne.AbsenceStopDate <= GETDATE()-8)
GROUP BY
losttime.CTwentyOne.EmployeeName,
losttime.CTwentyOne.EmployeeNumber,
losttime.Profit_Centers_Table.AirportCode,
losttime.CTwentyOne.BranchCode,
losttime.CTwentyOne.Shopcode,
losttime.CTwentyOne.AbsenceCode,
losttime.CTwentyOne.AbsenceStartDate,
losttime.CTwentyOne.AbsenceStopDate,
losttime.CTwentyOne.AbsenceHours,
losttime.CTwentyOne.C23DiscussionDate,
losttime.CTwentyOne.SelfManaged,
losttime.CTwentyOne.CorrectiveAction,
losttime.CTwentyOne.CorrectiveAction_DateOf,
losttime.CTwentyOne.DrSlipStartDate,
losttime.CTwentyOne.DrSlipStopDate) X
GROUP BY BranchCode
ORDER BY BranchCode

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2004-04-05 : 17:52:54

Derrick,

Close, here is the error msg.

Server: Msg 156, Level 15, State 1, Line 121
Incorrect syntax near the keyword 'GROUP'.

I tried seeing if I could give you access to our test server but there is only one table on there and I don't have CREATE authority to copy the three other tables over. So even if you had access the query wouldn't work without the tables. Sorry.

Thanks again for your help!!

GC

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-04-05 : 17:54:59

Post us the CREATE TABLE and INSERT INTO statements and we can build it on our ends.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2004-04-05 : 18:52:18

Here is the Create Table, or how the tables are formatted.

if exists (select * from dbo.sysobjects where id = object_id(N'[losttime].[CTwentyOne]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [losttime].[CTwentyOne]
GO

CREATE TABLE [losttime].[CTwentyOne] (
[CompanyCode] [char] (2) NULL ,
[EmployeeName] [char] (50) NULL ,
[EmployeeNumber] [int] NULL ,
[StationCode] [smallint] NULL ,
[BranchCode] [smallint] NULL ,
[ShopCode] [varchar] (50) NULL ,
[EmployeeStatus] [char] (25) NULL ,
[CompanySeniortyDate] [smalldatetime] NULL ,
[PerfectAttendanceStartDate] [smalldatetime] NULL ,
[AbsenceCode] [char] (3) NULL ,
[AbsenceStartDate] [smalldatetime] NULL ,
[AbsenceStopDate] [smalldatetime] NULL ,
[AbsenceHours] [decimal](12, 1) NULL ,
[C23DiscussionDate] [smalldatetime] NULL ,
[SelfManaged] [varchar] (2) NULL ,
[CorrectiveAction] [char] (6) NULL ,
[CorrectiveAction_DateOf] [smalldatetime] NULL ,
[LOAType] [char] (2) NULL ,
[LOAStartDate] [smalldatetime] NULL ,
[LOAStopDate] [smalldatetime] NULL ,
[SickHours_Jan_01] [decimal](6, 1) NULL ,
[SickHours_Current] [decimal](12, 1) NULL ,
[LetterOfConcernIssueDate] [smalldatetime] NULL ,
[DrSlipStartDate] [smalldatetime] NULL ,
[DrSlipStopDate] [smalldatetime] NULL ,
[SKL_Count] [smallint] NULL ,
[SKL_Hours] [decimal](5, 1) NULL ,
[SKL_Points] [decimal](3, 1) NULL ,
[ID_Count] [smallint] NULL ,
[ID_Hours] [decimal](5, 1) NULL ,
[ID_Points] [decimal](5, 1) NULL ,
[IU_Count] [smallint] NULL ,
[IU_Hours] [decimal](5, 1) NULL ,
[IU_Points] [decimal](5, 1) NULL ,
[NC_Count] [smallint] NULL ,
[NC_Hours] [decimal](5, 1) NULL ,
[NC_Points] [decimal](5, 1) NULL ,
[PO_Count] [smallint] NULL ,
[PO_Hours] [decimal](5, 1) NULL ,
[PO_Points] [decimal](5, 1) NULL ,
[RD_Count] [smallint] NULL ,
[RD_Hours] [decimal](5, 1) NULL ,
[RD_Points] [decimal](5, 1) NULL ,
[RL_Count] [smallint] NULL ,
[RL_Hours] [decimal](5, 1) NULL ,
[RL_Points] [decimal](5, 1) NULL ,
[SK_Count] [smallint] NULL ,
[SK_Hours] [decimal](5, 1) NULL ,
[SK_Points] [decimal](5, 1) NULL ,
[SKI_Count] [smallint] NULL ,
[SKI_Hours] [decimal](5, 1) NULL ,
[SKI_Points] [decimal](5, 1) NULL ,
[SKP_Count] [smallint] NULL ,
[SKP_Hours] [decimal](5, 1) NULL ,
[SKP_Points] [decimal](5, 1) NULL ,
[SKQ_Count] [smallint] NULL ,
[SKQ_Hours] [decimal](5, 1) NULL ,
[SKQ_Points] [decimal](5, 1) NULL ,
[SKU_Count] [smallint] NULL ,
[SKU_Hours] [decimal](5, 1) NULL ,
[SKU_Points] [decimal](5, 1) NULL ,
[TD_Count] [smallint] NULL ,
[TD_Hours] [decimal](5, 1) NULL ,
[TD_Points] [decimal](5, 1) NULL ,
[UA_Count] [smallint] NULL ,
[UA_Hours] [decimal](5, 1) NULL ,
[UA_Points] [decimal](5, 1) NULL
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[losttime].[Branches_Table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [losttime].[Branches_Table]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[losttime].[PayCodes_Table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [losttime].[PayCodes_Table]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[losttime].[Profit_Centers_Table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [losttime].[Profit_Centers_Table]
GO

CREATE TABLE [losttime].[Branches_Table] (
[COMPANYCODE] [varchar] (4) NULL ,
[BRANCHCODE] [int] NULL ,
[BRANCHDESCRIPTION] [varchar] (255) NULL ,
[BRANCHCATEGORYSMALL] [varchar] (100) NULL ,
[BRANCHCATEGORYLARGE] [varchar] (100) NULL ,
[MAINTENANCECATEGORIES] [varchar] (100) NULL ,
[CARGOCATEGORIES] [varchar] (100) NULL ,
[BRANCHMAINGROUPING] [varchar] (100) NULL ,
[TULSABASEGROUPINGS] [varchar] (100) NULL
) ON [PRIMARY]
GO

CREATE TABLE [losttime].[PayCodes_Table] (
[PAYCODE] [varchar] (5) NULL ,
[PAYCODEDESCRIPTION] [varchar] (255) NULL ,
[PAYCODETYPE] [varchar] (100) NULL ,
[LOSTTIMEREPORT] [varchar] (25) NULL ,
[PAID/UNPAID] [varchar] (25) NULL ,
[PDMISC] [varchar] (25) NULL ,
[PRODUCTIVE/NONPRODUCTIVE] [varchar] (255) NULL ,
[TOTALMANHOURS] [varchar] (100) NULL
) ON [PRIMARY]
GO

CREATE TABLE [losttime].[Profit_Centers_Table] (
[COMPANYCODE] [varchar] (10) NULL ,
[STATIONCODE] [smallint] NULL ,
[STATIONCODEDESCRIPTION] [varchar] (255) NULL ,
[AIRPORTCODE] [varchar] (20) NULL ,
[CTO] [varchar] (5) NULL ,
[SATO] [varchar] (5) NULL ,
[USSTATECODE] [varchar] (50) NULL ,
[USSTATENAME] [varchar] (100) NULL ,
[COUNTRYCODE] [varchar] (5) NULL ,
[COUNTRYNAME] [varchar] (100) NULL ,
[STATIONCATEGORYSMALL] [varchar] (10) NULL ,
[STATIONCATEGORYLARGE] [varchar] (100) NULL ,
[STATIONTYPESMALL] [varchar] (10) NULL ,
[STATIONTYPELARGE] [varchar] (100) NULL ,
[STATIONSREPORTINGTOPSVC] [varchar] (100) NULL ,
[STATIONSREPORTINGTORSVC] [varchar] (100) NULL ,
[STATIONSREPORTINGTOREGION] [varchar] (100) NULL ,
[STATIONSREPORTINGTORES] [varchar] (100) NULL ,
[STATIONSREPORTINGTOCARGO] [varchar] (100) NULL
) ON [PRIMARY]
GO

We don't have any INSERT INTO statements as the Tables are already set up and the CTwentyOne data is automatically refreshed in the early morning. All I am doing is running queries off the database. Hope this helps, and Thanks.

GC

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-04-05 : 19:15:32

I've got to say, and please don't take this personally (I'm sure this is not your database design), but I don't think I've EVER seen a more unnormalized database than this .... I think literally every rule in the book is broken ....

sorry -- I know this doesn't help at all but I just had to say SOMETHING ....

(I think this schema should win some kind of award or something)

- Jeff

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-04-05 : 19:18:26

I'm going to have to agree with Jeff on this, but I'll try to help some more.

Gary,
Can you give us some INSERT statements so that we can mock up this data and see if we can make this work? We don't need ALL of the data, just some sample data to try out your query. Make something up.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-05 : 19:23:58

Don't bother guys. :)

Gary, the problem is in your inner select. You have several columns where you haven't put losttime. in front of the column name. You need to just work on getting this inner select to work first.

After you fix the owner thing (there's like six places where you need to fix it), you will find out that you have the whole inner query wrong. You have a SUM, which is an aggregate function, on the query. When you do this, you have to put every column not in that SUM in the GROUP BY clause, which you have not done.

After you get that piece working, the rest should be a breeze.

I don't suppose there's any chance you could change the data struture and make the query simpler to write.

Also, you said the query was working like a dream. I'm not sure how that could be the case. Could you post the query you have working and tell us what you are trying to accomplish with it?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

The Enigma
Posting Yak Master

179 Posts

Posted - 2004-04-06 : 12:36:24

quote:
Originally posted by jsmith8858

I've got to say, and please don't take this personally (I'm sure this is not your database design), but I don't think I've EVER seen a more unnormalized database than this .... I think literally every rule in the book is broken ....

sorry -- I know this doesn't help at all but I just had to say SOMETHING ....

(I think this schema should win some kind of award or something)

- Jeff

I think you might not have seen this .[url]http://sqlteam.com/forums/topic.asp?TOPIC_ID=30628[/url]

Enigma

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2004-04-06 : 12:49:35

All,

Thank you for your help with this and sorry for my lack of SQL knowledge. My only training has been teaching myself by reading several books on the subject. Would love to get some formal training in this, but lets just say that things haven’t been the greatest in the airline industry these days. When I mention a training budget my manager just laughs at me. At the same time they still expect to see results though.

The first query I posted in my original post does work. The first column provides a list of the different airport codes and then the other columns provide counts of the different data items I need. What would take me a full day to do I can now do the entire system in seconds. The response time is very quick as the CTwentyOne table is only 73,000 rows, and the query feeds an Excel spreadsheet report.

My only problem is that we have two different attendance policies for our two work groups, Agents (PSVC) and Clerks (RSVC). Attendance for the Agent group is based on a "Self Managed" points system where a Supervisor does not need to speak to an Agent until they have been absent several times to where they are at 2.5 points or higher and are no longer "Self Managed". The Clerks group is not on a points system and a Supervisor has to have a discussion with the employee after each occurrence.

The problem with my original query is that after the Group By I am performing a HAVING SUM function that filters out everyone who has less than 2.5 points. This is fine for the Agent group, but incorrect for the Clerks.

I thought that if I could move the HAVING SUM function or something similar up into the WHERE area and have the filter work on just the Agent group. I could then see all of the Clerks.

I don’t think I can post a sample file of the data to this website, so please let me know if you would like me to send the file as an attachment to your posted e-mail address.

Thanks again for all your help, it is appreciated.

GC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-06 : 12:53:41

Gary,

The data does not have to be real data. You can post sample data that represents your problem. The data should be posted in the form of INSERT INTO statements so that we can copy and paste them into Query Analyzer on our machines.

Tara

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2004-04-06 : 13:18:45

See if this works. I've tried to provide several employee records.

INSERT INTO losttime.CTwentyOne (
CompanyCode,
EmployeeName,
EmployeeNumber,
StationCode,
BranchCode,
ShopCode,
EmployeeStatus,
CompanySeniortyDate,
PerfectAttendanceStartDate,
AbsenceCode,
AbsenceStartDate,
AbsenceStopDate,
AbsenceHours,
C23DiscussionDate,
SelfManaged,
CorrectiveAction,
CorrectiveAction_DateOf,
LOAType,
LOAStartDate,
LOAStopDate,
SickHours_Jan_01,
SickHours_Current,
LetterOfConcernIssueDate,
DrSlipStartDate,
DrSlipStopDate,
SKL_Count,
SKL_Hours,
SKL_Points,
ID_Count ,
ID_Hours ,
ID_Points ,
IU_Count ,
IU_Hours ,
IU_Points ,
NC_Count ,
NC_Hours ,
NC_Points ,
PO_Count ,
PO_Hours ,
PO_Points ,
RD_Count ,
RD_Hours ,
RD_Points ,
RL_Count ,
RL_Hours ,
RL_Points,
SK_Count ,
SK_Hours ,
SK_Points ,
SKI_Count ,
SKI_Hours ,
SKI_Points ,
SKP_Count ,
SKP_Hours ,
SKP_Points ,
SKQ_Count ,
SKQ_Hours ,
SKQ_Points ,
SKU_Count ,
SKU_Hours ,
SKU_Points ,
TD_Count ,
TD_Hours ,
TD_Points ,
UA_Count ,
UA_Hours ,
UA_Points )

VALUES

‘AA’,’SMITH,AB ‘,’000009’,’0691’,’4810’,’9000 ‘,’ACTIVE ‘,’1985/03/01’,’1985/03/01’,’ ‘,’1900/01/01’,’1900/01/01’,’ 0000.0’,’1900/01/01’,’Y’,’ ‘,’1900/01/01’,’ ‘,’1900/01/01’,’1985/02/28’,’ 0440.0’,’ 0440.0’,’1900/01/01’,’1900/01/01’,’1900/01/01’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’

‘AA’,’JONES JR,HA ‘,’000154’,’1020’,’8618’,’0000 ‘,’OFF PAYROLL ‘,’2001/07/02’,’2003/04/30’,’SK ‘,’2003/04/28’,’2003/04/29’,’ 0016.0’,’2003/05/01’,’Y’,’ ‘,’1900/01/01’,’13’,’1992/06/10’,’1992/06/29’,’ 0104.0’,’ 0104.0’,’1900/01/01’,’1900/01/01’,’1900/01/01’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0001’,’ 0016.0’,’01.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’

‘AA’,’BARNES,F ‘,’000189’,’0697’,’7720’,’2000 ‘,’ACTIVE ‘,’1986/08/18’,’2003/06/28’,’SKL’,’2003/06/09’,’2003/06/27’,’ 0120.0’,’2003/06/30’,’Y’,’ ‘,’1900/01/01’,’ ‘,’1900/01/01’,’1985/03/31’,’ 0430.5’,’ 0430.5’,’1900/01/01’,’1900/01/01’,’1900/01/01’,’ 0001’,’ 0120.0’,’01.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’

‘AA’,’NOBLE,KL ‘,’000211’,’0801’,’4403’,’4103 ‘,’ACTIVE ‘,’1986/07/28’,’2002/12/05’,’ ‘,’1900/01/01’,’1900/01/01’,’ 0000.0’,’1900/01/01’,’Y’,’ ‘,’1900/01/01’,’ ‘,’1900/01/01’,’1986/07/27’,’ 1200.0’,’ 1200.0’,’1900/01/01’,’1900/01/01’,’1900/01/01’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’

‘AA’,’SANDS,SL ‘,’000221’,’0691’,’4510’,’9000 ‘,’ACTIVE ‘,’1985/04/01’,’2002/02/16’,’ ‘,’1900/01/01’,’1900/01/01’,’ 0000.0’,’2002/02/19’,’Y’,’ ‘,’1900/01/01’,’ ‘,’1900/01/01’,’1985/03/31’,’ 0483.0’,’ 0483.0’,’1900/01/01’,’1900/01/01’,’1900/01/01’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’

‘AA’,’ALTON,SE ‘,’000252’,’0346’,’4300’,’1000 ‘,’ACTIVE ‘,’1993/02/20’,’2003/08/30’,’SK ‘,’2003/08/21’,’2003/08/29’,’ 0056.0’,’1900/01/01’,’Y’,’ ‘,’1900/01/01’,’19’,’1987/12/19’,’1988/02/03’,’ 0592.0’,’ 0592.0’,’1900/01/01’,’1900/01/01’,’1900/01/01’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0001’,’ 0056.0’,’01.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’

‘AA’,’ENGLISH,AM ‘,’000260’,’0801’,’4450’,’1000 ‘,’ACTIVE ‘,’1986/07/28’,’2004/01/29’,’SKL’,’2004/01/06’,’2004/01/28’,’ 0136.0’,’2004/03/14’,’Y’,’ ‘,’1900/01/01’,’ ‘,’1900/01/01’,’1986/07/27’,’ 0854.0’,’ 0718.0’,’1900/01/01’,’1900/01/01’,’1900/01/01’,’ 0001’,’ 0136.0’,’01.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’

‘AA’,’GIBBS,AC ‘,’000297’,’0960’,’6014’,’4500 ‘,’ACTIVE ‘,’1984/11/05’,’2004/03/19’,’SK ‘,’2004/03/10’,’2004/03/18’,’ 0028.0’,’2004/03/19’,’Y’,’ ‘,’1900/01/01’,’18’,’1993/03/28’,’1993/09/03’,’ 0372.9’,’ 0344.9’,’1900/01/01’,’1900/01/01’,’1900/01/01’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0001’,’ 0028.0’,’01.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’,’ 0000’,’ 0000.0’,’00.00’)

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2004-04-06 : 14:24:00

Here is some data for the Profit_Centers_Table

INSERT INTO losttime.Profit_Centers_Table
(COMPANYCODE,
STATIONCODE,
STATIONCODEDESCRIPTION,
AIRPORTCODE,
CTO,
SATO,
USSTATECODE,
USSTATENAME,
COUNTRYCODE,
COUNTRYNAME,
STATIONCATEGORYSMALL,
STATIONCATEGORYLARGE,
STATIONTYPESMALL,
STATIONTYPELARGE,
STATIONSREPORTINGTOPSVC,
STATIONSREPORTINGTORSVC,
STATIONSREPORTINGTOREGION,
STATIONSREPORTINGTORES,
STATIONSREPORTINGTOCARGO)

VALUES

(‘AA’,
‘0691’,
‘MIAMI FL ARPT/MIA’,
‘MIA’,
‘No’,
‘No’,
‘FL’,
‘FLORIDA’,
‘US’,
‘United States’,
‘APT’,
‘AIRPORT’,
‘APT HUB’,
‘HUB AIRPORTS’,
‘DOMESTIC’,
‘DOMESTIC’,
‘HUB’,
‘NON – RES’,
‘CARGO’,

‘AA’,
‘1020’,
‘AFW ALLIANCE MNTC BASE’,
‘AFW MB’,
‘No’,
‘No’,
‘TX’,
‘TEXAS’,
‘US’,
‘United States’,
‘MNTC’,
‘MAINTENANCE’,
‘MNTC’,
‘MAINTENANCE’,
‘NON - FIELD’,
‘NON - FIELD’,
‘NON - FIELD’,
‘NON – RES’,
‘NON - CGO’,

‘AA’,
‘0697’,
‘TAMPA FL ARPT/TPA’,
‘TPA’,
‘No’,
‘No’,
‘FL’,
‘FLORIDA’,
‘US’,
‘United States’,
‘APT’,
‘AIRPORT’,
‘APT MED’,
‘MEDIUM AIRPORTS’,
‘DOMESTIC’,
‘DOMESTIC’,
‘SOUTHEAST’,
‘NON – RES’,
‘NON - CGO’,

‘AA’,
‘0801’,
‘LOS ANGELES CA INTL ARPT/LAX’,
‘LAX’,
‘No’,
‘No’,
‘CA’,
‘CALIFORNIA’,
‘US’,
‘United States’,
‘APT’,
‘AIRPORT’,
‘APT LGE’,
‘LARGE AIRPORTS’,
‘DOMESTIC’,
‘DOMESTIC’,
‘LARGE STATION’,
‘NON – RES’,
‘CARGO’,

‘AA’,
‘0346’,
‘WASHINGTON BALTIMORE ARPT/BWI’,
‘BWI’,
‘No’,
‘No’,
‘MD’,
‘MARYLAND’,
‘US’,
‘United States’,
‘APT’,
‘AIRPORT’,
‘APT MED’,
‘MEDIUM AIRPORTS’,
‘DOMESTIC’,
‘DOMESTIC’,
‘SOUTHEAST’,
‘NON – RES’,
‘NON - CGO’,

‘AA’,
‘0960’,
‘HARTFORD CITY CT RES OFF-ERO’,
‘ERO’,
‘No’,
‘No’,
‘CT’,
‘CONNECTICUT’,
‘US’,
‘United States’,
‘RES’,
‘RESERVATIONS’,
‘RES’,
‘RESERVATIONS’,
‘NON - FIELD’,
‘NON - FIELD’,
‘NON - FIELD’,
‘RES’,
‘NON - CGO’)

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2004-04-06 : 14:39:48

Here is sample data for the Branches Table

INSERT INTO losttime.Branches_Table
(COMPANYCODE,
BRANCHCODE,
BRANCHDESCRIPTION,
BRANCHCATEGORYSMALL,
BRANCHCATEGORYLARGE,
MAINTENANCECATEGORIES,
CARGOCATEGORIES,
BRANCHMAINGROUPING,
TULSABASEGROUPINGS )

VALUES

(‘AA’,
‘4810’,
‘FIELD MNTC FACILITIES MNTC’,
‘RSVC’,
‘RAMP SERVICE’
‘NON – MNTC’,
‘NON – CGO’,
‘RSVC’,
‘NON – TULE’,

‘AA’,
‘8618’,
‘AFW HOSE & TUBE’,
‘MNTC’,
‘MAINTENANCE’
‘MNTC - BASE’,
‘NON – CGO’,
‘MNTC’,
‘NON – TULE’,

‘AA’,
‘7720’,
‘M&E MATERIAL MANAGEMENT’,
‘MNTC’,
‘MAINTENANCE - STORES MANAGEMENT’
‘MNTC - STORES’,
‘NON – CGO’,
‘MNTC’,
‘NON – TULE’,

‘AA’,
‘4403’,
‘FIELD PSGR SVCS AGENT’,
‘PSVC’,
‘PASSENGER SERVICE’
‘NON – MNTC’,
‘NON – CGO’,
‘PSVC’,
‘NON – TULE’,

‘AA’,
‘4510’,
‘FIELD RAMP LINE CARGO’,
‘RSVC’,
‘RAMP SERVICE’
‘NON – MNTC’,
‘NON – CGO’,
‘RSVC’,
‘NON – TULE’,

‘AA’,
‘4300’,
‘FIELD SR VP/STA MGR’,
‘APT MGT’,
‘AIRPORT MANAGEMENT’
‘NON – MNTC’,
‘NON – CGO’,
‘MGT AND SUP’,
‘NON – TULE’,

‘AA’,
‘4450’,
‘FIELD PSGR LOAD & CLEARANCE’,
‘PSVC’,
‘PASSENGER SERVICE’
‘NON – MNTC’,
‘NON – CGO’,
‘PSVC’,
‘NON – TULE’,

‘AA’,
‘6014’,
‘EXECUTIVE PLATINUM DESK’,
‘RES’,
‘RESERVATIONS’
‘NON – MNTC’,
‘NON – CGO’,
‘RES’,
‘NON – TULE’)

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2004-04-06 : 14:52:15

Here is sample data for the PayCode Table.

INSERT INTO losttime.PayCodes_Table
(PAYCODE,
PAYCODEDESCRIPTION,
PAYCODETYPE,
LOSTTIMEREPORT,
PAID/UNPAID,
PDMISC,
PRODUCTIVE/NONPRODUCTIVE,
TOTALMANHOURS)

VALUES

(‘NC’,
‘NO CALL IN’,
‘N.P.N.W.’,
‘OTHER’,
‘UNPAID’,
‘UNPAID NOT INCLUDED’,
‘NOT INCLUDED’,
‘TOTALMANHOURS’,

‘PO’,
‘PERSONAL OTHER’,
‘N.P.N.W.’,
‘OTHER’,
‘UNPAID’,
‘UNPAID NOT INCLUDED’,
‘NOT INCLUDED’,
‘TOTALMANHOURS’,

‘RL’,
‘REPORTED LATE’,
‘N.P.N.W.’,
‘OTHER’,
‘UNPAID’,
‘UNPAID NOT INCLUDED’,
‘NOT INCLUDED’,
‘TOTALMANHOURS’,

‘SK’,
‘SICK’,
‘L.T.P.N.W.’,
‘SK’,
‘PAID’,
‘PAID SK’,
‘NON-PRODUCTIVE’,
‘TOTALMANHOURS’,

‘SKP’,
‘SICK DUE TO PREGNANCY’,
‘L.T.P.N.W.’,
‘SK’,
‘PAID’,
‘PAID SK’,
‘NON-PRODUCTIVE’,
‘TOTALMANHOURS’,
‘SKQ’,
‘SICK QUALIFIED PAID’,
‘L.T.P.N.W.’,
‘SK’,
‘PAID’,
‘PAID SK’,
‘NON-PRODUCTIVE’,
‘TOTALMANHOURS’,

‘SKU’,
‘SICK UNPAID’,
‘N.P.N.W.’,
‘OTHER’,
‘UNPAID’,
‘UNPAID SK’,
‘NOT INCLUDED’,
‘TOTALMANHOURS’,

‘UA’,
‘UNPAID ABSENCE’,
‘N.P.N.W.’,
‘OTHER’,
‘UNPAID’,
‘UNPAID NOT INCLUDED’,
‘NOT INCLUDED’,
‘TOTALMANHOURS’)

Let me know if I need to add anything else.

GC

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2004-04-12 : 17:17:50

Was just wondering if anyone had a chance to look at this since I added the CREATE and INSERT data to test with.

Thanks again.

GC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-12 : 17:19:41

The code that you posted does not work when copied into Query Analyzer.

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-12 : 19:02:19

I have no idea if this is what you are looking for, but it will compile. :)

SELECT
BranchCode as 'Branch',
count(*)'Emp NN C23',
SUM([C23No]) as '# Completed',
SUM([C23Yes]) as '# Needed',
SUM([AIC]) as '# On AIC',
SUM([1STADV]) as '# On 1STADV',
SUM([2NDADV]) as '# On 2NDADV',
SUM([TRMWRN]) as '# On TRMWRN',
SUM([DRSLIP]) as '# On Dr Slip'
From(
SELECT
losttime.CTwentyOne.EmployeeName,
losttime.CTwentyOne.EmployeeNumber,
losttime.Profit_Centers_Table.AirportCode,
losttime.CTwentyOne.BranchCode,
losttime.CTwentyOne.Shopcode,
losttime.CTwentyOne.AbsenceCode,
losttime.CTwentyOne.AbsenceStartDate,
losttime.CTwentyOne.AbsenceStopDate,
CASE
WHEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate) = DATENAME (MONTH,GETDATE()) and DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)= DATENAME (YEAR,GETDATE())THEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate)
WHEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate) = DATENAME (MONTH,(DATEADD(MONTH, -1, GETDATE()))) and DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)= DATENAME (YEAR,GETDATE())THEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate)
WHEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate) = DATENAME (MONTH,(DATEADD(MONTH, -2, GETDATE()))) and DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)= DATENAME (YEAR,GETDATE())THEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate)
WHEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate) = DATENAME (MONTH,(DATEADD(MONTH, -3, GETDATE()))) and DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)= DATENAME (YEAR,(DATEADD(Year,-1,GETDATE())))THEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate)
WHEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate) = DATENAME (MONTH,(DATEADD(MONTH, -4, GETDATE()))) and DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)= DATENAME (YEAR,(DATEADD(Year,-1,GETDATE())))THEN DATENAME (MONTH,losttime.CTwentyOne.AbsenceStartDate)
ELSE DATENAME (YEAR,losttime.CTwentyOne.AbsenceStartDate)
END 'AbsenceMonth / Year',
losttime.CTwentyOne.AbsenceHours,
losttime.CTwentyOne.C23DiscussionDate,
CASE
WHEN (losttime.CTwentyOne.C23DiscussionDate IS NULL
or losttime.CTwentyOne.C23DiscussionDate <losttime.CTwentyOne.AbsenceStopDate)THEN 1
ELSE 0
END 'C23YES',
CASE WHEN (losttime.CTwentyOne.C23DiscussionDate >=losttime.CTwentyOne.AbsenceStopDate)THEN 1 ELSE 0 END 'C23NO',
CASE
WHEN losttime.CTwentyOne.C23DiscussionDate >=losttime.CTwentyOne.AbsenceStopDate THEN 'Completed'
ELSE CONVERT(varchar(20),DATEDIFF(dd,losttime.CTwentyOne.AbsenceStopDate, getdate()))
END AS DaysOverDue,
CASE
WHEN losttime.CTwentyOne.C23DiscussionDate IS NULL THEN 'Needed'
WHEN DATEDIFF(dd,losttime.CTwentyOne.AbsenceStopDate, losttime.CTwentyOne.C23DiscussionDate) < 0 THEN 'Needed'
ELSE CONVERT(varchar(20), DATEDIFF(dd,losttime.CTwentyOne.AbsenceStopDate, losttime.CTwentyOne.C23DiscussionDate))
END AS DaysC23CompletedIn,
losttime.CTwentyOne.SelfManaged,
losttime.CTwentyOne.CorrectiveAction,
CASE WHEN (losttime.CTwentyOne.CorrectiveAction = 'AIC')THEN 1 ELSE 0 END 'AIC',
CASE WHEN (losttime.CTwentyOne.CorrectiveAction = '1STADV')THEN 1 ELSE 0 END '1STADV',
CASE WHEN (losttime.CTwentyOne.CorrectiveAction = '2NDADV')THEN 1 ELSE 0 END '2NDADV',
CASE WHEN (losttime.CTwentyOne.CorrectiveAction = 'TRMWRN')THEN 1 ELSE 0 END 'TRMWRN',
losttime.CTwentyOne.CorrectiveAction_DateOf,
losttime.CTwentyOne.DrSlipStartDate,
losttime.CTwentyOne.DrSlipStopDate,
CASE
WHEN losttime.CTwentyOne.DrSlipStartDate <= GETDATE() and losttime.CTwentyOne.DrSlipStartDate >= GETDATE()- 365
THEN 1
ELSE 0
END 'DRSLIP',
CASE
WHEN losttime.CTwentyOne.DrSlipStartDate IS NULL THEN 'NO'
WHEN losttime.CTwentyOne.DrSlipStartDate <= GETDATE() and losttime.CTwentyOne.DrSlipStartDate >= GETDATE()- 365 THEN 'YES'
ELSE 'NO'
END 'Currently on Dr Slip',
SUM(
losttime.CTwentyOne.SKL_Count
+ losttime.CTwentyOne.ID_Count
+ losttime.CTwentyOne.IU_Count
+ losttime.CTwentyOne.NC_Count
+ losttime.CTwentyOne.PO_Count
+ losttime.CTwentyOne.RL_Count
+ losttime.CTwentyOne.SK_Count
+ losttime.CTwentyOne.SKI_Count
+ losttime.CTwentyOne.SKP_Count
+ losttime.CTwentyOne.SKQ_Count
+ losttime.CTwentyOne.SKU_Count
+ losttime.CTwentyOne.UA_Count) AS TotalCount,
SUM(
losttime.CTwentyOne.SKL_Hours
+ losttime.CTwentyOne.ID_Hours
+ losttime.CTwentyOne.IU_Hours
+ losttime.CTwentyOne.NC_Hours
+ losttime.CTwentyOne.PO_Hours
+ losttime.CTwentyOne.RL_Hours
+ losttime.CTwentyOne.SK_Hours
+ losttime.CTwentyOne.SKI_Hours
+ losttime.CTwentyOne.SKP_Hours
+ losttime.CTwentyOne.SKQ_Hours
+ losttime.CTwentyOne.SKU_Hours
+ losttime.CTwentyOne.UA_Hours) AS TotalHours,
SUM(
losttime.CTwentyOne.SKL_Points
+ losttime.CTwentyOne.ID_Points
+ losttime.CTwentyOne.IU_Points
+ losttime.CTwentyOne.NC_Points
+ losttime.CTwentyOne.PO_Points
+ losttime.CTwentyOne.RL_Points
+ losttime.CTwentyOne.SK_Points
+ losttime.CTwentyOne.SKI_Points
+ losttime.CTwentyOne.SKP_Points
+ losttime.CTwentyOne.SKQ_Points
+ losttime.CTwentyOne.SKU_Points
+ losttime.CTwentyOne.UA_Points) AS TotalPoints
FROM
losttime.CTwentyOne
JOIN losttime.Profit_Centers_Table ON losttime.CTwentyOne.StationCode = losttime.Profit_Centers_Table.StationCode
and losttime.CTwentyOne.CompanyCode = losttime.Profit_Centers_Table.CompanyCode
Join losttime.Branches_Table ON losttime.CTwentyOne.BranchCode = losttime.Branches_Table.BranchCode
Join losttime.PayCodes_Table ON losttime.CTwentyOne.AbsenceCode = losttime.PayCodes_Table.PayCode
WHERE
losttime.CTwentyOne.CompanyCode = 'AA'
and losttime.CTwentyOne.EmployeeStatus = 'Active'
and losttime.CTwentyOne.StationCode = '752'
and losttime.CTwentyOne.AbsenceCode IN ('NC', 'PO', 'RL', 'SK', 'SKP', 'SKQ', 'SKU', 'UA')
and losttime.Branches_Table.BRANCHCATEGORYSMALL IN ('PSVC', 'RSVC')
and (losttime.Profit_Centers_Table.StationsReportingToCARGO <> 'CARGO'
or losttime.Branches_Table.CARGOCATEGORIES <> 'CGO - Field')
and (losttime.CTwentyOne.AbsenceStopDate <= GETDATE()-8)
-- and (losttime.Branches_Table.BRANCHCATEGORYSMALL = 'RSVC'
-- OR (
-- SUM(losttime.CTwentyOne.SKL_Points
-- + losttime.CTwentyOne.ID_Points
-- + losttime.CTwentyOne.IU_Points
-- + losttime.CTwentyOne.NC_Points
-- + losttime.CTwentyOne.PO_Points
-- + losttime.CTwentyOne.RL_Points
-- + losttime.CTwentyOne.SK_Points
-- + losttime.CTwentyOne.SKI_Points
-- + losttime.CTwentyOne.SKP_Points
-- + losttime.CTwentyOne.SKQ_Points
-- + losttime.CTwentyOne.SKU_Points
-- + losttime.CTwentyOne.UA_Points) >= 2.5))
GROUP BY
losttime.CTwentyOne.EmployeeName,
losttime.CTwentyOne.EmployeeNumber,
losttime.Profit_Centers_Table.AirportCode,
losttime.CTwentyOne.BranchCode,
losttime.CTwentyOne.Shopcode,
losttime.CTwentyOne.AbsenceCode,
losttime.CTwentyOne.AbsenceStartDate,
losttime.CTwentyOne.AbsenceStopDate,
losttime.CTwentyOne.AbsenceHours,
losttime.CTwentyOne.C23DiscussionDate,
losttime.CTwentyOne.SelfManaged,
losttime.CTwentyOne.CorrectiveAction,
losttime.CTwentyOne.CorrectiveAction_DateOf,
losttime.CTwentyOne.DrSlipStartDate,
losttime.CTwentyOne.DrSlipStopDate,
losttime.Branches_Table.BRANCHCATEGORYSMALL
HAVING
losttime.Branches_Table.BRANCHCATEGORYSMALL = 'RSVC'
OR
SUM(losttime.CTwentyOne.SKL_Points
+ losttime.CTwentyOne.ID_Points
+ losttime.CTwentyOne.IU_Points
+ losttime.CTwentyOne.NC_Points
+ losttime.CTwentyOne.PO_Points
+ losttime.CTwentyOne.RL_Points
+ losttime.CTwentyOne.SK_Points
+ losttime.CTwentyOne.SKI_Points
+ losttime.CTwentyOne.SKP_Points
+ losttime.CTwentyOne.SKQ_Points
+ losttime.CTwentyOne.SKU_Points
+ losttime.CTwentyOne.UA_Points) >= 2.5) X
GROUP BY
BranchCode
ORDER BY
BranchCode

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2004-04-13 : 16:16:01

Derrick,

It works!!! 'Thank You" to you and everyone else who had the patients to help me with this. I was sure the calculation had to be in the WHERE area and never would have thought to do what you did. Once again I learn something new.

Thanks.

GC

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources