Getdate to sum the total of time frame

[KL_Hutchmfg]

Hello,

I have a Sql query that currently shows me the last 6 weeks of rolling information. I need to change that report to show me the sum of the last 6 months of data.
I am extremely new at SQL and have been trying to find some things online but not sure where or how to get the "sum" of all the data and not just the return date of what happened 6 months ago. Hoping someone help. Here is what the current part has for each week:

(select lmlTimecardID from TimecardLines where datepart(ww,lmlActualEndTime) = datepart(ww,Getdate()) -5 and datepart(yy,lmlActualEndTime) = datepart(yy,Getdate()) and lmlJobID = jmoJobID and lmlJobAssemblyID = jmoJobAssemblyID and lmlJobOperationID = jmoJobOperationID and
lmlTimecardID = (select top 1 lmlTimecardID from TimecardLines where lmlJobID = jmoJobID and lmlJobAssemblyID = jmoJobAssemblyID and lmlJobOperationID = jmoJobOperationID Order By lmlActualEndTime desc))
group by jmoWorkCenterID) as WK6 on WK6WC = xawWorkCenterID


How can I rewrite this to pull the entire 6 month period without specific date ranges.

 

[ChrisHirst]

https://www.w3schools.com/sql/func_mysql_sum.asp

SELECT SUM(column) AS SummedValue FROM TableName WHERE [search criteria];

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.

Never mind this jesus character, stars had to die for me to live.

 

[KL_Hutchmfg]

I already have my tables where it should be pulling from to sum the correct data. How do I put in the date range for it to sum up six months and not just a certain month?

WorkCenters left outer join
(Select jmoWorkCenterID as WK1WC, sum(jmoSetupHours) as WK1_Setup_Hrs, sum(jmoActualSetupHours) as WK1_Act_Setup, sum(jmoEstimatedProductionHours) as WK1_Prod_Hrs, sum(jmoActualProductionHours) as WK1_Act_Prod,
case when sum(jmoActualSetupHours) <> 0 then 100.0 * sum(jmoSetupHours) / sum(jmoActualSetupHours) else 0 end as WK1_Setup_Eff,
case when sum(jmoActualProductionHours) <> 0 then 100.0 * sum(jmoEstimatedProductionHours) / sum(jmoActualProductionHours) else 0 end as WK1_Prod_Eff
from JobOperations where jmoProductionComplete <> 0 and exists
(select lmlTimecardID from TimecardLines where datepart(mm,lmlActualEndTime) = datepart(mm,Getdate()) -6 and datepart(yy,lmlActualEndTime) = datepart(yy,Getdate()) and lmlJobID = jmoJobID and lmlJobAssemblyID = jmoJobAssemblyID and lmlJobOperationID = jmoJobOperationID and
lmlTimecardID = (select top 1 lmlTimecardID from TimecardLines where lmlJobID = jmoJobID and lmlJobAssemblyID = jmoJobAssemblyID and lmlJobOperationID = jmoJobOperationID Order By lmlActualEndTime desc))
group by jmoWorkCenterID) as WK1 on WK1WC = xawWorkCenterID


This is the whole query, I already have the queries but I need to tell it to pull 6 months worth of data and not just 6 months in the past.

 

[ChrisHirst]

This is the whole query, I already have the queries but I need to tell it to pull 6 months worth of data and not just 6 months in the past.

Same as you do now only specify a start date instead of GetDate() {which is SQL not MySQL} in the WHERE clause along with startDate + 3 AND startDate -3.


AND please use the code markup delimiters in your posts, click on the ? button to the left of the Preview to see the markup code list

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.

Never mind this jesus character, stars had to die for me to live.