Calulate total prior to a given date

[Nick]

I have receive help with SQL that gives totals between a certain period of
time. I would like to know how to get the total prior to the requested time
in the query and also have have quarter to date. I am including the SQL of
the requested time frame.

PARAMETERS Weekendingdate DateTime;
TRANSFORM Max(OvertimeTracking.oTDLhOURUSED) AS MaxOfoTDLhOURUSED
SELECT Employees.SSI, Sum(OvertimeTracking.oTDLhOURUSED) AS [Total Of otdliD]
FROM Employees INNER JOIN OvertimeTracking ON Employees.SSI =
OvertimeTracking.EMPiD
WHERE (((OvertimeTracking.aUTHORIZED) Like 1 Or
(OvertimeTracking.aUTHORIZED) Like 6)) and otdldate
Between DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])
and DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])+6
GROUP BY Employees.SSI
PIVOT Format(OTDLDate,"ddd") IN
("Sat", "Sun", "Mon", "Tue","Wed","Thu","Fri");

Thanks for your help.

 

[KARL DEWEY]

This might be what you are looking for ---
PARAMETERS Weekendingdate DateTime;
TRANSFORM Sum(OvertimeTracking.oTDLhOURUSED) AS SumOfoTDLhOURUSED
SELECT Employees.SSI,
DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate]) AS Start_Date,
Sum(OvertimeTracking.oTDLhOURUSED) AS [Total Of otdliD]
FROM Employees INNER JOIN OvertimeTracking ON Employees.SSI =
OvertimeTracking.EMPiD
WHERE (((OvertimeTracking.aUTHORIZED)="1" Or
(OvertimeTracking.aUTHORIZED)="6"))
GROUP BY Employees.SSI,
DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])
PIVOT IIf([otdldate] Between
DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate]) And
DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])+6,Format([OTDLDate],"ddd"),"Prior") In ("Sat", "Sun", "Mon", "Tue","Wed","Thu","Fri","Prior");

I think to include the quarter to date you will need to change the 'Total Of
otdliD' to be the QTD.

 

[Nick]

I copied SQL and after entering a date, I received this error message. This
expression is typed incorrectly, or it is too complex to be evaluated. For
example, a numeric expression may contain too many complicated elements. Try
simplifying the expression by assigning parts of the expression to variables.
Did I miss something?

This might be what you are looking for ---
PARAMETERS Weekendingdate DateTime;
TRANSFORM Sum(OvertimeTracking.oTDLhOURUSED) AS SumOfoTDLhOURUSED
SELECT Employees.SSI,
DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate]) AS Start_Date,
Sum(OvertimeTracking.oTDLhOURUSED) AS [Total Of otdliD]
FROM Employees INNER JOIN OvertimeTracking ON Employees.SSI =
OvertimeTracking.EMPiD
WHERE (((OvertimeTracking.aUTHORIZED)="1" Or
(OvertimeTracking.aUTHORIZED)="6"))
GROUP BY Employees.SSI,
DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])
PIVOT IIf([otdldate] Between
DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate]) And
DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])+6,Format([OTDLDate],"ddd"),"Prior") In ("Sat", "Sun", "Mon", "Tue","Wed","Thu","Fri","Prior");

I think to include the quarter to date you will need to change the 'Total Of
otdliD' to be the QTD.
--
KARL DEWEY
Build a little - Test a little

I have receive help with SQL that gives totals between a certain period of
time. I would like to know how to get the total prior to the requested time
in the query and also have have quarter to date. I am including the SQL of
the requested time frame.

PARAMETERS Weekendingdate DateTime;
TRANSFORM Max(OvertimeTracking.oTDLhOURUSED) AS MaxOfoTDLhOURUSED
SELECT Employees.SSI, Sum(OvertimeTracking.oTDLhOURUSED) AS [Total Of otdliD]
FROM Employees INNER JOIN OvertimeTracking ON Employees.SSI =
OvertimeTracking.EMPiD
WHERE (((OvertimeTracking.aUTHORIZED) Like 1 Or
(OvertimeTracking.aUTHORIZED) Like 6)) and otdldate
Between DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])
and DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])+6
GROUP BY Employees.SSI
PIVOT Format(OTDLDate,"ddd") IN
("Sat", "Sun", "Mon", "Tue","Wed","Thu","Fri");

Thanks for your help.

 

[Nick]

Thank you, I did figure what I did to receive the error I told you about.
After see what it does, I wanted to know how to change the query so that I
could get data prior to a given week. The query totals all and backs up a
week no matter what week I select. It is the same week prior to total of all
week. Again thank you, It takes some time for me to unerstand query sturcture

This might be what you are looking for ---
PARAMETERS Weekendingdate DateTime;
TRANSFORM Sum(OvertimeTracking.oTDLhOURUSED) AS SumOfoTDLhOURUSED
SELECT Employees.SSI,
DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate]) AS Start_Date,
Sum(OvertimeTracking.oTDLhOURUSED) AS [Total Of otdliD]
FROM Employees INNER JOIN OvertimeTracking ON Employees.SSI =
OvertimeTracking.EMPiD
WHERE (((OvertimeTracking.aUTHORIZED)="1" Or
(OvertimeTracking.aUTHORIZED)="6"))
GROUP BY Employees.SSI,
DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])
PIVOT IIf([otdldate] Between
DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate]) And
DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])+6,Format([OTDLDate],"ddd"),"Prior") In ("Sat", "Sun", "Mon", "Tue","Wed","Thu","Fri","Prior");

I think to include the quarter to date you will need to change the 'Total Of
otdliD' to be the QTD.
--
KARL DEWEY
Build a little - Test a little

I have receive help with SQL that gives totals between a certain period of
time. I would like to know how to get the total prior to the requested time
in the query and also have have quarter to date. I am including the SQL of
the requested time frame.

PARAMETERS Weekendingdate DateTime;
TRANSFORM Max(OvertimeTracking.oTDLhOURUSED) AS MaxOfoTDLhOURUSED
SELECT Employees.SSI, Sum(OvertimeTracking.oTDLhOURUSED) AS [Total Of otdliD]
FROM Employees INNER JOIN OvertimeTracking ON Employees.SSI =
OvertimeTracking.EMPiD
WHERE (((OvertimeTracking.aUTHORIZED) Like 1 Or
(OvertimeTracking.aUTHORIZED) Like 6)) and otdldate
Between DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])
and DateAdd("d",1-Weekday([Weekendingdate],7),[Weekendingdate])+6
GROUP BY Employees.SSI
PIVOT Format(OTDLDate,"ddd") IN
("Sat", "Sun", "Mon", "Tue","Wed","Thu","Fri");

Thanks for your help.