Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
OK, another one
Message
 
 
À
18/01/2009 21:11:55
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Syntaxe SQL
Titre:
Divers
Thread ID:
01374859
Message ID:
01374903
Vues:
28
>Well, you DID say the last one was too easy

See if this is close to what we want.
;with cte_labor2 as 
(select TimeSheets.*, [HourlyRate] = (select top 1 HourlyRate from WorkerRates where WorkerRates.EmployeeID = 
TimeSheets.EmployeeID and TimeSheets.WorkDate >= (CASE WHEN WorkerRates.RateEffectiveDate > WorkerRates.RateAddedDate THEN WorkerRates.RateAddedDate ELSE WorkerRates.RateEffectiveDate END) 
ORDER BY CASE WHEN WorkerRates.RateEffectiveDate > WorkerRates.RateAddedDate THEN WorkerRates.RateAddedDate ELSE WorkerRates.RateEffectiveDate END DESC) from TimeSheets)          

select EmployeeID, MONTH(WorkDate) as mWork, YEAR(WorkDate) as YDate, SUM(HourlyRate*HoursWorked) as LaborAmount  from cte_labor2 group by EmployeeID, YEAR(WorkDate), MONTH(WorkDate)
If it's not broken, fix it until it is.


My Blog
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform