Calculation on specific records in query

[sgjjw]

This is my code but it is not returning proper results.

CalcServicePeriod query calculates each customer's SPStartDate and SPEndDate
NSLS table has DailyCons and DailyCost by Date
NSLS.Date is related to CalcServicePeriod.SPStartDate

I need the query to look at each customers SPStartDate and SPEndDate and pull the DailyCons and DialyCost for that date range and do the calculation for CustomerWAP.

SELECT CalcServicePeriod.ID, CalcServicePeriod.SPStartDate, CalcServicePeriod.SPEndDate, DSum(([DailyCons]/[DailyCost]),'NSLS',[Date] Between [SPStartDate] And [SPEndDate]) AS CustomerWAP
FROM NSLS INNER JOIN CalcServicePeriod ON NSLS.Date = CalcServicePeriod.SPStartDate;

I must be missing something, please help.

 

[Louie]

I think you want the date test in a where clause.

SELECT CalcServicePeriod.ID,
CalcServicePeriod.SPStartDate,
CalcServicePeriod.SPEndDate,
DSum(([DailyCons]/[DailyCost]),'NSLS',[Date]
FROM NSLS INNER JOIN CalcServicePeriod
ON NSLS.Date = CalcServicePeriod.SPStartDate
Where [Date] Between [SPStartDate] And [SPEndDate]);

 

[sgjjw]

Thanks Louie.

I tried that code but it is giving me a syntax error (missing operator) in the DSum expression.

I can't figure out what has been missed.

 

[Louie]

Sorry about that. I gave you bad syntax, wrong number (). Anyway it wouldn't work. Just didn't understand what you are trying to do.
Maybe this is what you want.

SELECT CalcServicePeriod.ID, CalcServicePeriod.SPStartDate, CalcServicePeriod.SPEndDate,
Sum([DailyCons]/[DailyCost])
FROM NSLS INNER JOIN CalcServicePeriod ON NSLS.Date = CalcServicePeriod.SPStartDate
Group by CalcServicePeriod.ID, CalcServicePeriod.SPStartDate, CalcServicePeriod.SPEndDate
Having ( [Date] Between [SPStartDate] And [SPEndDate]);

 

[sgjjw]

Thanks for the suggestion. I tried it but got an error message: you tried to execute a query that does not include the specific expression '[Date] Between [SPStartDate] And [SPEndDate]' as part of an aggregate function.

I tried a few things to fix it but had no luck...I have not done a tremendous amount of work with code yet.

 

[Louie]

Could you post the two table structures?

 

[sgjjw]

What is the best way to post that?

 

[sgjjw]

Thanks for the help, I got it! You got me pointed in the right direction. Here is the code for future reference.

I had to break the calculation down into 3 steps (TotalCost, TotalCons, then TotalCost/TotalCons)

SELECT CalcServicePeriod.ID, CalcServicePeriod.SPStartDate, CalcServicePeriod.SPEndDate, DSum(("[DailyCost]"),"NSLS","[Date] Between # " & [SPStartDate] & " # And # " & [SPEndDate] & " # ") AS CustCost, DSum(("[DailyCons]"),"NSLS","[Date] Between # " & [SPStartDate] & " # And # " & [SPEndDate] & " # ") AS CustCons, [CustCost]/[CustCons] AS CustWAP
FROM NSLS INNER JOIN CalcServicePeriod ON NSLS.Date = CalcServicePeriod.SPStartDate;