September 25, 2013 at 8:41 am
Hi all based on my query below, how do I select just 50 based on a Sum of duration matching the criteria defined?
SELECT Contract, Docket_Category, Docket_Date, Docket_DateRaised, Docket_EngFinish, Docket_EngStart, Docket_EngineerName, Docket_Id, Docket_Machine, Docket_Number, Docket_Status, Docket_SubCategory, Duration, Module, Monitor_Time, Operator_Name, Reason, ReasonReq, Section, Waittime, Weekend, spare8 FROM DocketTB WHERE (Docket_Status = 'CL') AND (Operator_Name IS NOT NULL) AND (Operator_Name <> 'None') AND (Docket_Category IS NOT NULL) AND (Contract = 1) AND (Docket_Category NOT LIKE '%Out Of SLA%')
Thanks
September 25, 2013 at 8:51 am
jerome.morris (9/25/2013)
Hi all based on my query below, how do I select just 50 based on a Sum of duration matching the criteria defined?SELECT Contract, Docket_Category, Docket_Date, Docket_DateRaised, Docket_EngFinish, Docket_EngStart, Docket_EngineerName, Docket_Id, Docket_Machine, Docket_Number, Docket_Status, Docket_SubCategory, Duration, Module, Monitor_Time, Operator_Name, Reason, ReasonReq, Section, Waittime, Weekend, spare8 FROM DocketTB WHERE (Docket_Status = 'CL') AND (Operator_Name IS NOT NULL) AND (Operator_Name <> 'None') AND (Docket_Category IS NOT NULL) AND (Contract = 1) AND (Docket_Category NOT LIKE '%Out Of SLA%')
Thanks
Not much to go on here.
select top 50
...
order by ???
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 25, 2013 at 9:02 am
Order by? top 50 what. Do I not need to sum the Duration then select the top 50 that what I don't understand
thanks
September 25, 2013 at 9:14 am
jerome.morris (9/25/2013)
Order by? top 50 what. Do I not need to sum the Duration then select the top 50 that what I don't understandthanks
You have me at an extreme disadvantage here. I can't see your tables and I have no idea what you are trying to do. My guess is you want to SUM duration. You will have to group by some columns for that to have any meaning. You can order by the aggregate if that is what you want. You said top 50, I don't know what classifies as top 50.
I can help but you have to provide some level of detail for me to understand what you are trying to do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 25, 2013 at 9:31 am
Correct, I need to sum(Duration) grouped by Operator_Name and return the result desc based on the the sum for each operator.
Sorry I cant be any clearer, is there anything I can give to assist
September 25, 2013 at 9:50 am
jerome.morris (9/25/2013)
Correct, I need to sum(Duration) grouped by Operator_Name and return the result desc based on the the sum for each operator.
This help?
select top 50 sum(Duration), Operator_Name
FROM DocketTB
WHERE (Docket_Status = 'CL')
AND (Operator_Name IS NOT NULL)
AND (Operator_Name <> 'None')
AND (Docket_Category IS NOT NULL)
AND (Contract = 1)
AND (Docket_Category NOT LIKE '%Out Of SLA%')
group by Operator_Name
order by SUM(Duration)
Sorry I cant be any clearer, is there anything I can give to assist
Yes. Posting ddl and a few rows of sample data would be very helpful. Please take a few minutes and read the first article found in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 25, 2013 at 10:17 am
This is correct but I need all my other selects in there as I pass this to a datatable for reportviewer.
thanks
September 25, 2013 at 10:31 am
jerome.morris (9/25/2013)
This is correct but I need all my other selects in there as I pass this to a datatable for reportviewer.thanks
This is where you have to post some details in order to get any help. My shoot in the dark gun is all out of ammo.
You want the other columns but you have not clarified at all what you want. When you include columns like "Reason" you are going to make your sum useless. Presumably those values are not the same across all the rows. Which one do you want?
This query would probably take about 3-4 minutes to write if there were the details posted.
Please post:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 27, 2013 at 12:04 am
jerome.morris (9/25/2013)
This is correct but I need all my other selects in there as I pass this to a datatable for reportviewer.thanks
kindly first go through this: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply