×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Getdate to sum the total of time frame

Getdate to sum the total of time frame

Getdate to sum the total of time frame

(OP)
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.

RE: Getdate to sum the total of time frame

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

CODE --> MySQL

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.

RE: Getdate to sum the total of time frame

(OP)
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.

RE: Getdate to sum the total of time frame

Quote:

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close