Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Coding Counts and Subtotals by day, location and grand totals in T-SQL Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, December 31, 2013 8:10 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, April 16, 2014 9:46 AM Points: 6, Visits: 30
Post #1526755
 Posted Tuesday, December 31, 2013 4:49 PM
 Forum Newbie Group: General Forum Members Last Login: Yesterday @ 5:58 PM Points: 7, Visits: 66
 Are you still looking to solve this?
Post #1526862
 Posted Wednesday, January 01, 2014 8:17 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, April 16, 2014 9:46 AM Points: 6, Visits: 30
 Yes, I am still interested in solving this problem.
Post #1526908
 Posted Wednesday, January 01, 2014 10:15 AM
 Forum Newbie Group: General Forum Members Last Login: Yesterday @ 5:58 PM Points: 7, Visits: 66
 How are you looking at presenting the data? Do you need one table showing all the details or what will be displaying the information out?
Post #1526923
 Posted Wednesday, January 01, 2014 12:06 PM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, April 16, 2014 9:46 AM Points: 6, Visits: 30
 Randy:For my purposes now I just need to generate the results in the standard MS 2005 Management Studio grid. I just need to show the detail data and then the 3 counts/sum/subtotals similar to the output below. Show total number of loads (count of rail cars) per day (group by Date Released);Sum of Net Tons per day (grouped by Date Released)Then sum of the total number of loads (rail cars) by Location (grouped by location); Sum of Net Tons by Location (grouped by location).Then Grand Totals (Sum of counts of all Loads [rail cars]) and Sum of Net Tons of all Locations [running total???]Ticket Number SP_Description CarNumber Product Product_Description DateReleased NetTonsClaremont, NH159026 Claremont, NH AEX12609 BIC Bulk Ice Control Salt 12/18/2013 99.65159027 Claremont, NH GNWR5050 BIC Bulk Ice Control Salt 12/18/2013 104.00159028 Claremont, NH AEX19091 BIC Bulk Ice Control Salt 12/18/2013 100.03 Daily Loads: 3 Daily Tons: 303.68159220 Claremont, NH AEX16390 BIC Bulk Ice Control Salt 12/20/2013 99.97159222 Claremont, NH AEX11529 BIC Bulk Ice Control Salt 12/20/2013 100.00 Daily Loads: 2 Daily Tons: 199.97 Location Loads Total: 5 Location Total Tons: 503.65
Post #1526932
 Posted Wednesday, January 01, 2014 12:12 PM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, April 16, 2014 9:46 AM Points: 6, Visits: 30
 One table showing details and the counts/sums/subtotals would be fine. We add html coding to the SQL script so the script output (report) can be displayed in a web browser. I am not worried about that piece as we have template code for that. I just want to show details for a location with the daily totals followed by the Location totals and Grand Totals at end of the report.I don't know how to get T-SQL to sum or count by day, then sum the counts or tons by location. Then sum all counts and sum Net Tons for all records in the date range specified.Hope this makes sense. Thanks.
Post #1526934
 Posted Wednesday, January 01, 2014 12:36 PM
 Forum Newbie Group: General Forum Members Last Login: Yesterday @ 5:58 PM Points: 7, Visits: 66
 So just so that I don't go in the wrong direction is this something towards what you are looking for? SELECT CONVERT( CHAR(10), sosb_CarDetail_DateReleased, 101 ) AS [DateReleased] , Count( sosb_CarDetail_CarNumber ) AS TotalCarsByDateFROM #mytableGROUP BY CONVERT( CHAR(10), sosb_CarDetail_DateReleased, 101 )ORDER BY CONVERT( CHAR(10), sosb_CarDetail_DateReleased, 101 )The grouping by the Date converted to '11/11/2013' makes it easier. I guess if you need the data in a table you can create a table variable and populated it (section by section) with update statements. Let me know if this helps ~ we can apply the same logic to the other information you're looking to capture.
Post #1526942
 Posted Wednesday, January 01, 2014 6:35 PM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, April 16, 2014 9:46 AM Points: 6, Visits: 30
 Randy: This is close but not quite. I need to show total cars/loads by day per location and then total cars/loads per location. See bold items below.Example: Location Date Car Net TonsClaremont HY 12/6 Car A 10 12/6 Car B 16 12/6 Car C 14 Daily Totals 3 loads 40 12/7 Car A 12 12/7 Car B 16 Daily Totals 2 Loads 28 Location Totals 5 Loads 68 Your code is very helpful for getting totals (car/loads) by date but I need sums/totals by location.
Post #1526965
 Posted Wednesday, January 01, 2014 7:14 PM
 Forum Newbie Group: General Forum Members Last Login: Yesterday @ 5:58 PM Points: 7, Visits: 66
 Does this get you closer?SELECT CONVERT( CHAR(10), sosb_CarDetail_DateReleased, 101 ) AS [DateReleased] , Count( sosb_CarDetail_CarNumber ) AS TotalCarsByDate , sosb_SP_Description , Round( Sum( sosb_CarDetail_NetTons ), 2 ) AS Total_NetTonsFROM #mytableGROUP BY CONVERT( CHAR(10), sosb_CarDetail_DateReleased, 101 ) , sosb_SP_DescriptionORDER BY CONVERT( CHAR(10), sosb_CarDetail_DateReleased, 101 ) DESCSELECT Datepart( Month, a.DateReleased ) AS MonthReleased , Sum( a.TotalCarsByDate ) AS GrandTotalCars , sosb_SP_Description , Sum( Total_NetTons ) AS TotalNetTonsByMonthFROM ( SELECT CONVERT( CHAR(10), sosb_CarDetail_DateReleased, 101 ) AS [DateReleased] , Count( sosb_CarDetail_CarNumber ) AS TotalCarsByDate , sosb_SP_Description , Round( Sum( sosb_CarDetail_NetTons ), 2 ) AS Total_NetTons FROM #mytable GROUP BY CONVERT( CHAR(10), sosb_CarDetail_DateReleased, 101 ) , sosb_SP_Description ) aGROUP BY Datepart( Month, a.DateReleased ) , sosb_SP_Description
Post #1526968
 Posted Wednesday, January 01, 2014 7:20 PM
 Forum Newbie Group: General Forum Members Last Login: Yesterday @ 5:58 PM Points: 7, Visits: 66
 You can keep breaking it down more as needed.
Post #1526969

 Permissions