Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Coding Counts and Subtotals by day, location and grand totals in T-SQL Expand / Collapse
Author
Message
Posted Tuesday, December 31, 2013 8:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 29, 2014 9:13 PM
Points: 14, Visits: 63
I am trying to create a SQL script using MS SQL 2005 to generate a report that shows railcars, product and tonnage shipped to stockpile/customer locations. Report is broken down (sorted/grouped) by stockpile/customer location description; Date Rail Car Shipment was released (Date Released); and by Dispatch Ticket Number.

The output from the T-SQL script should be similar to the Crystal Report below. Unfortunately Crystal 2013 does not generate the entire SQL script with the groupings, counts or subtotaling.

The problem I am having is creating the counts and summary subtotals using T-SQL.

We want to count the number of rail cars loaded per day [Count SOSB_Car_Detail.SOSB_CarDetail_CarNumber) as Daily Loads] and then have a total of the count of the number of loads per stockpile/customer location. Then have a final grand total of the counts of the number of loads for all locations. Same thing for the Net Tons: subtotal by day; total by location; grand total of all tons shipped.

I know how to code the basic count or sum but how to total by date, then by location, then grand totals?

See example below for Claremont, NH Location - report shows total count of loads of 3 for 12/18 and 2 loads for 12/20
with total loads for location of 5. Similar for subtotals of Net Tons.

Crystal Report example:

Ticket Number SP_Description CarNumber Product_Num Product_Description DateReleased NetTons

Claremont, NH

159026 Claremont, NH AEX12609 BIC Bulk Ice Control Salt 12/18/2013 99.65
159027 Claremont, NH GNWR5050 BIC Bulk Ice Control Salt 12/18/2013 104.00
159028 Claremont, NH AEX19091 BIC Bulk Ice Control Salt 12/18/2013 100.03

Daily Loads: 3 Daily Tons: 303.68


159220 Claremont, NH AEX16390 BIC Bulk Ice Control Salt 12/20/2013 99.97
159222 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


My SQL script without the counts/subtotals is:

Source script

------CSS Rail Activity Report - test sql script

Select

SOSB_Car_Detail.SOSB_CarDetail_Key ---- Dispatch Ticket Number
, SOSB_Stockpile_Master.sosb_sp_location ------ Number of Stockpile/Customer Location
, SOSB_Stockpile_Master.sosb_sp_description -----Description of Stockpile/Customer Location
, SOSB_Car_Detail.SOSB_CarDetail_CarNumber ---- Railroad Car Number/Identifier
, SOSB_Product_Master.sosb_product_number As 'Product' ---- Product Shipped in Railcar
, SOSB_Product_Master.sosb_product_description As 'ProdDesc' ----- Product Description
, SOSB_Car_Detail.SOSB_CarDetail_DateReleased ---- Date Railcar was loaded
, SOSB_Car_Detail.SOSB_CarDetail_NetTons ---- Weight of product in railcar in tons


FROM SOSB_Car_Detail --- primary table with railcar detail records

Left Outer Join SOSB_Stockpile_Master
ON SOSB_Car_Detail.SOSB_CarDetail_CoToSP = SOSB_Stockpile_Master.SOSB_SP_Key ---- join to link car detail table to stockpile master table

LEFT OUTER JOIN SOSB_SPLocation_Master
ON SOSB_Stockpile_Master.SOSB_SP_CoLocation = SOSB_SPLocation_Master.SOSB_SPLocation_Key

Left Outer Join SOSB_Product_Master
on SOSB_Stockpile_Master.sosb_sp_coproduct = SOSB_Product_Master.sosb_product_key ---- join to link stockpile master table to product table to get product number/description
and SOSB_Stockpile_Master.SOSB_SP_Company = SOSB_Product_Master.SOSB_Product_Company ---- link to associate correct product company with correct stockpile company record


WHERE (ISNULL(SOSB_Car_Detail.SOSB_CarDetail_Void, 'No') = 'No') --- filter to eliminate voided care detail records
and SOSB_CarDetail_DateReleased between '12/16/13' and '12/22/13' ---- date range for transactions

GROUP BY

SOSB_SP_Description
, SOSB_CarDetail_DateReleased
, SOSB_CarDetail_CarNumber
, SOSB_Product_Master.sosb_product_number
, SOSB_Product_Master.sosb_product_description
, SOSB_SP_Location
, SOSB_CarDetail_NetTons
, SOSB_CarDetail_Key


ORDER BY SOSB_SP_Description, SOSB_CarDetail_DateReleased, SOSB_Car_Detail.SOSB_CarDetail_Key ---- order by Stockpile/Customer location name/descr;Date Railcar was loaded; dispatch ticket number

************* TEST DATA: **********************

---- =====If the test table already exists, then drop it

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable

-----====== Create the test table with

CREATE TABLE #mytable

(
[sosb_CarDetail_Key] [int] IDENTITY(1,1) NOT NULL,
[sosb_SP_Location] [nvarchar](50) NULL,
[sosb_SP_Description] [nvarchar](50) NULL,
[sosb_CarDetail_CarNumber] [nvarchar](50) NULL,
[sosb_Product_Number] [nvarchar](50) NULL,
[sosb_Product_Description] [nvarchar](50) NULL
[sosb_CarDetail_DateReleased] [smalldatetime] NULL,
[sosb_CarDetail_NetTons] [real] NULL
)

------===== setup any special required conditions especially where dates are concerned

set dateformat yyyymmdd:hh:mm:ss ---- smalldatetime data type

----- ======== All Inserts into the IDENTITY column

set IDENTITY_INSERT #mytable ON

------ ==== Insert the test data into the test table

INSERT INTO #mytable

(
SOSB_CarDetail_Key
, sosb_sp_location
, sosb_sp_description
, SOSB_CarDetail_CarNumber
, sosb_product_number
, sosb_product_description
, SOSB_CarDetail_DateReleased
, SOSB_CarDetail_NetTons
)


select'159287','120','Belle Vernon, PA','GNWR5240','BIC','Bulk Ice Control Salt','Dec 19 2013 12:00AM','116' UNION ALL
select'159288','120','Belle Vernon, PA','GNWR5002','BIC','Bulk Ice Control Salt','Dec 19 2013 12:00AM','116' UNION ALL
select'159289','120','Belle Vernon, PA','GNWR5488','BIC','Bulk Ice Control Salt','Dec 19 2013 12:00AM','116' UNION ALL
select'159290','120','Belle Vernon, PA','GNWR5190','BIC','Bulk Ice Control Salt','Dec 19 2013 12:00AM','116' UNION ALL
select'159291','120','Belle Vernon, PA','AEX12646','BIC','Bulk Ice Control Salt','Dec 19 2013 12:00AM','100' UNION ALL
select'159331','120','Belle Vernon, PA','GNWR5475','BIC','Bulk Ice Control Salt','Dec 19 2013 12:00AM','116.15' UNION ALL
select'159332','120','Belle Vernon, PA','GNWR5159','BIC','Bulk Ice Control Salt','Dec 19 2013 12:00AM','116' UNION ALL
select'159333','120','Belle Vernon, PA','AEX11622','BIC','Bulk Ice Control Salt','Dec 19 2013 12:00AM','99.5' UNION ALL
select'159334','120','Belle Vernon, PA','GNWR5016','BIC','Bulk Ice Control Salt','Dec 19 2013 12:00AM','115.9' UNION ALL
select'159335','120','Belle Vernon, PA','GNWR5470','BIC','Bulk Ice Control Salt','Dec 19 2013 12:00AM','116.1' UNION ALL
select'159336','120','Belle Vernon, PA','GNWR5359','BIC','Bulk Ice Control Salt','Dec 19 2013 12:00AM','116' UNION ALL
select'159337','120','Belle Vernon, PA','GNWR5245','BIC','Bulk Ice Control Salt','Dec 19 2013 12:00AM','115.975' UNION ALL
select'159338','120','Belle Vernon, PA','AEX13809','BIC','Bulk Ice Control Salt','Dec 19 2013 12:00AM','100' UNION ALL
select'159308','120','Belle Vernon, PA','GNWR5151','BIC','Bulk Ice Control Salt','Dec 20 2013 12:00AM','116' UNION ALL
select'159309','120','Belle Vernon, PA','GNWR5522','BIC','Bulk Ice Control Salt','Dec 20 2013 12:00AM','116' UNION ALL
select'159314','120','Belle Vernon, PA','GNWR5055','BIC','Bulk Ice Control Salt','Dec 20 2013 12:00AM','115.975' UNION ALL
select'159315','120','Belle Vernon, PA','GNWR5230','BIC','Bulk Ice Control Salt','Dec 20 2013 12:00AM','116' UNION ALL
select'159316','120','Belle Vernon, PA','GNWR5237','BIC','Bulk Ice Control Salt','Dec 20 2013 12:00AM','116.025' UNION ALL
select'159317','120','Belle Vernon, PA','GNWR5512','BIC','Bulk Ice Control Salt','Dec 20 2013 12:00AM','116' UNION ALL
select'159318','120','Belle Vernon, PA','ANGX4131','BIC','Bulk Ice Control Salt','Dec 20 2013 12:00AM','99.05' UNION ALL
select'159319','120','Belle Vernon, PA','GNWR5203','BIC','Bulk Ice Control Salt','Dec 20 2013 12:00AM','116.05' UNION ALL
select'159320','120','Belle Vernon, PA','GNWR5365','BIC','Bulk Ice Control Salt','Dec 20 2013 12:00AM','115.85' UNION ALL
select'159322','120','Belle Vernon, PA','GNWR5514','BIC','Bulk Ice Control Salt','Dec 20 2013 12:00AM','115.975' UNION ALL
select'159323','120','Belle Vernon, PA','GNWR5198','BIC','Bulk Ice Control Salt','Dec 20 2013 12:00AM','115.975' UNION ALL
select'159324','120','Belle Vernon, PA','DMM810246','BIC','Bulk Ice Control Salt','Dec 20 2013 12:00AM','100.025' UNION ALL
select'159026','150','Claremont, NH','AEX12609','BIC','Bulk Ice Control Salt','Dec 18 2013 12:00AM','99.65' UNION ALL
select'159027','150','Claremont, NH','GNWR5050','BIC','Bulk Ice Control Salt','Dec 18 2013 12:00AM','104' UNION ALL
select'159028','150','Claremont, NH','AEX19091','BIC','Bulk Ice Control Salt','Dec 18 2013 12:00AM','100.025' UNION ALL
select'159220','150','Claremont, NH','AEX16390','BIC','Bulk Ice Control Salt','Dec 20 2013 12:00AM','99.975' UNION ALL
select'159222','150','Claremont, NH','AEX11529','BIC','Bulk Ice Control Salt','Dec 20 2013 12:00AM','100' UNION ALL
select'158461','170','Du Bois, PA','GNWR5314','BIC','Bulk Ice Control Salt','Dec 16 2013 12:00AM','115.95' UNION ALL
select'158462','170','Du Bois, PA','GNWR5053','BIC','Bulk Ice Control Salt','Dec 16 2013 12:00AM','115.975' UNION ALL
select'158608','170','Du Bois, PA','GNWR5083','BIC','Bulk Ice Control Salt','Dec 16 2013 12:00AM','103.95' UNION ALL
select'158609','170','Du Bois, PA','GNWR5288','BIC','Bulk Ice Control Salt','Dec 16 2013 12:00AM','104.05' UNION ALL
select'158611','170','Du Bois, PA','AEX12552','BIC','Bulk Ice Control Salt','Dec 16 2013 12:00AM','100.05' UNION ALL
select'158612','170','Du Bois, PA','DMM810011','BIC','Bulk Ice Control Salt','Dec 16 2013 12:00AM','100' UNION ALL
select'158613','170','Du Bois, PA','DMM810140','BIC','Bulk Ice Control Salt','Dec 16 2013 12:00AM','100.1' UNION ALL
select'158772','170','Du Bois, PA','DMM810300','BIC','Bulk Ice Control Salt','Dec 16 2013 12:00AM','100' UNION ALL
select'158773','170','Du Bois, PA','GNWR5292','BIC','Bulk Ice Control Salt','Dec 16 2013 12:00AM','103.975' UNION ALL
select'158774','170','Du Bois, PA','AEX12628','BIC','Bulk Ice Control Salt','Dec 16 2013 12:00AM','100' UNION ALL
select'158775','170','Du Bois, PA','GNWR5390','BIC','Bulk Ice Control Salt','Dec 16 2013 12:00AM','104' UNION ALL
select'158776','170','Du Bois, PA','GNWR5510','BIC','Bulk Ice Control Salt','Dec 16 2013 12:00AM','103.975' UNION ALL
select'158777','170','Du Bois, PA','GNWR5577','BIC','Bulk Ice Control Salt','Dec 16 2013 12:00AM','104.05' UNION ALL
select'158778','170','Du Bois, PA','DMM810301','BIC','Bulk Ice Control Salt','Dec 16 2013 12:00AM','100' UNION ALL
select'158779','170','Du Bois, PA','GNWR5257','BIC','Bulk Ice Control Salt','Dec 16 2013 12:00AM','104.05' UNION ALL
select'158780','170','Du Bois, PA','GNWR5118','BIC','Bulk Ice Control Salt','Dec 16 2013 12:00AM','103.8' UNION ALL
select'158781','170','Du Bois, PA','GNWR5574','BIC','Bulk Ice Control Salt','Dec 16 2013 12:00AM','104' UNION ALL
select'158782','170','Du Bois, PA','AEX13360','BIC','Bulk Ice Control Salt','Dec 16 2013 12:00AM','100' UNION ALL
select'158783','170','Du Bois, PA','GNWR5404','BIC','Bulk Ice Control Salt','Dec 16 2013 12:00AM','104.075' UNION ALL
select'158784','170','Du Bois, PA','GNWR5327','BIC','Bulk Ice Control Salt','Dec 16 2013 12:00AM','104.05' UNION ALL
select'158785','170','Du Bois, PA','GNWR5469','BIC','Bulk Ice Control Salt','Dec 16 2013 12:00AM','104.025' UNION ALL
select'158786','170','Du Bois, PA','GNWR5290','BIC','Bulk Ice Control Salt','Dec 16 2013 12:00AM','104.025' UNION ALL
select'158939','185','Fort Ann, NY','GNWR5322','BIC','Bulk Ice Control Salt','Dec 20 2013 12:00AM','108.525' UNION ALL
select'158940','185','Fort Ann, NY','GNWR5293','BIC','Bulk Ice Control Salt','Dec 20 2013 12:00AM','108.475' UNION ALL
select'158942','185','Fort Ann, NY','RMGX171072','BIC','Bulk Ice Control Salt','Dec 20 2013 12:00AM','99.75' UNION ALL
select'158943','185','Fort Ann, NY','AEX937','BIC','Bulk Ice Control Salt','Dec 20 2013 12:00AM','99.75' UNION ALL
select'158944','185','Fort Ann, NY','AEX15417','BIC','Bulk Ice Control Salt','Dec 20 2013 12:00AM','100.03' UNION ALL
select'158945','185','Fort Ann, NY','DMM810255','BIC','Bulk Ice Control Salt','Dec 20 2013 12:00AM','100' UNION ALL
select'159069','185','Fort Ann, NY','GNWR5281','BIC','Bulk Ice Control Salt','Dec 20 2013 12:00AM','108.5' UNION ALL
select'159070','185','Fort Ann, NY','GNWR5460','BIC','Bulk Ice Control Salt','Dec 20 2013 12:00AM','108.5' UNION ALL
select'159071','185','Fort Ann, NY','AEX12689','BIC','Bulk Ice Control Salt','Dec 20 2013 12:00AM','100'


----- ==== Set the Identity insert back to normal

Set IDENTITY_INSERT #mytable ON




Post #1526755
Posted Tuesday, December 31, 2013 4:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:39 AM
Points: 7, Visits: 68
Are you still looking to solve this?


Post #1526862
Posted Wednesday, January 1, 2014 8:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 29, 2014 9:13 PM
Points: 14, Visits: 63
Yes, I am still interested in solving this problem.
Post #1526908
Posted Wednesday, January 1, 2014 10:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:39 AM
Points: 7, Visits: 68
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 1, 2014 12:06 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 29, 2014 9:13 PM
Points: 14, Visits: 63
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 NetTons

Claremont, NH

159026 Claremont, NH AEX12609 BIC Bulk Ice Control Salt 12/18/2013 99.65
159027 Claremont, NH GNWR5050 BIC Bulk Ice Control Salt 12/18/2013 104.00
159028 Claremont, NH AEX19091 BIC Bulk Ice Control Salt 12/18/2013 100.03

Daily Loads: 3 Daily Tons: 303.68


159220 Claremont, NH AEX16390 BIC Bulk Ice Control Salt 12/20/2013 99.97
159222 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 1, 2014 12:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 29, 2014 9:13 PM
Points: 14, Visits: 63
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 1, 2014 12:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:39 AM
Points: 7, Visits: 68
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 TotalCarsByDate
FROM #mytable
GROUP 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 1, 2014 6:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 29, 2014 9:13 PM
Points: 14, Visits: 63
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 Tons
Claremont 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 1, 2014 7:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:39 AM
Points: 7, Visits: 68
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_NetTons
FROM #mytable
GROUP BY CONVERT( CHAR(10), sosb_CarDetail_DateReleased, 101 )
, sosb_SP_Description
ORDER BY CONVERT( CHAR(10), sosb_CarDetail_DateReleased, 101 ) DESC

SELECT Datepart( Month, a.DateReleased ) AS MonthReleased
, Sum( a.TotalCarsByDate ) AS GrandTotalCars
, sosb_SP_Description
, Sum( Total_NetTons ) AS TotalNetTonsByMonth
FROM ( 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 ) a
GROUP BY Datepart( Month, a.DateReleased )
, sosb_SP_Description




Post #1526968
Posted Wednesday, January 1, 2014 7:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:39 AM
Points: 7, Visits: 68
You can keep breaking it down more as needed.


Post #1526969
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse