December 31, 2013 at 8:10 am
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
December 31, 2013 at 4:49 pm
Are you still looking to solve this?
January 1, 2014 at 8:17 am
Yes, I am still interested in solving this problem.
January 1, 2014 at 10:15 am
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?
January 1, 2014 at 12:06 pm
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
January 1, 2014 at 12:12 pm
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.
January 1, 2014 at 12:36 pm
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.
January 1, 2014 at 6:35 pm
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.
January 1, 2014 at 7:14 pm
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
January 1, 2014 at 7:20 pm
You can keep breaking it down more as needed.
January 2, 2014 at 8:41 am
Randy:
This is great! This is about 90% of what I need.
I have 2 questions.
Your code generated the following:
DateReleasedTotalCarsByDatesosb_SP_DescriptionTotal_NetTons
12/20/2013 12 Belle Vernon, PA 1358.93
12/20/2013 2 Claremont, NH 199.97
12/20/2013 9 Fort Ann, NY 933.53
12/19/2013 13 Belle Vernon, PA 1459.63
12/18/2013 3 Claremont, NH 303.68
12/16/2013 22 Du Bois, PA 2284.1
MonthReleasedGrandTotalCarssosb_SP_DescriptionTotalNetTonsByMonth
12 25 Belle Vernon, PA 2818.56
12 5 Claremont, NH 503.65
12 22 Du Bois, PA 2284.1
12 9 Fort Ann, NY 933.53
What I need is the following:
Date Loads Location Net Tons
12/18/2013 Detail Record 1
12/18/2013 Detail Record 2
Daily Totals: 12/18/2013 3 Claremont, NH303.68
12/20/2013 Detail Record 1
12/20/2013 Detail Record 2
Daily Totals: 12/20/2013 2 Claremont, NH199.97
Location Totals: 12 5 Claremont, NH503.65
So how do I order your script results to keep the detail records and put the Daily Totals for each Location/Day as a footer for that day and same for the Location Totals?
My 2nd question is how to I incorporate your script with my main script (i.e. how do I nest the select subqueries in the correct order/syntax within my script)?
------CSS Rail Activity Report - Released sql script
Select
SOSB_Car_Detail.SOSB_CarDetail_Key as Ticket
, SOSB_Stockpile_Master.sosb_sp_location AS Location ------SOSB_SPLocation_Location
, SOSB_Stockpile_Master.sosb_sp_description AS Location_Descr -----SOSB_SPLocation_Description
, SOSB_Car_Detail.SOSB_CarDetail_CarNumber AS 'Car #'
, SOSB_Product_Master.sosb_product_number As 'Product'
, SOSB_Product_Master.sosb_product_description As 'ProdDesc'
, SOSB_Car_Detail.SOSB_CarDetail_DateReleased AS 'Date Released'
, SOSB_Car_Detail.SOSB_CarDetail_NetTons AS 'Net Tons'
FROM SOSB_Car_Detail
Left Outer Join SOSB_Stockpile_Master
ON SOSB_Car_Detail.SOSB_CarDetail_CoToSP = SOSB_Stockpile_Master.SOSB_SP_Key
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
and SOSB_Stockpile_Master.SOSB_SP_Company = SOSB_Product_Master.SOSB_Product_Company
WHERE (ISNULL(SOSB_Car_Detail.SOSB_CarDetail_Void, 'No') = 'No')
and SOSB_CarDetail_DateReleased between '12/16/13' and '12/22/13' -- added
group by
SOSB_SP_Description
, SOSB_CarDetail_DateReleased
, SOSB_Car_Detail.SOSB_CarDetail_Key
, SOSB_Stockpile_Master.sosb_sp_location
, SOSB_Car_Detail.SOSB_CarDetail_CarNumber
, SOSB_Product_Master.sosb_product_number
, SOSB_Product_Master.sosb_product_description
, SOSB_Car_Detail.SOSB_CarDetail_NetTons
ORDER BY SOSB_SP_Description, SOSB_CarDetail_DateReleased, SOSB_Car_Detail.SOSB_CarDetail_Key
January 2, 2014 at 4:07 pm
Sorry I had some (back at work fires) to put out. 🙂
Here's an approach that'll help you out a bit. Remember you can assign values to a variable in a select statement.
Example:
Declare @MyValue int
Select @MyValue = Sum('fieldname')
from #xxxxxxx
Where bla bla bla.....
Print @MyValue
So I've done that here for you to tweak to fit your needs. This method allows you to create a table with the dates you are interested in and generate individual values + plus + you can increment a value like @totals for this daterange. anyway let me know if it helps.
SET NOCOUNT ON
DECLARE
@RowCnt INT
, @MaxRows INT
, @sosb_CarDetail_DateReleased DATETIME
, @TotalCarsByDate INT
, @Total_NetTons INT
, @GrandTotalCars INT
, @TotalNetTonsByMonth INT
SELECT @RowCnt = 1
---- =====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
)
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 IDENTITY_INSERT #mytable ON
DECLARE @datesreleased TABLE
(
dateid INT IDENTITY (1, 1) PRIMARY KEY NOT NULL
, sosb_CarDetail_DateReleased DATETIME
)
INSERT INTO @datesreleased
(sosb_CarDetail_DateReleased)
SELECT DISTINCT sosb_CarDetail_DateReleased
FROM #mytable
ORDER BY sosb_CarDetail_DateReleased
SELECT @MaxRows = Count( * )
FROM @datesreleased
WHILE @RowCnt <= @MaxRows
BEGIN
PRINT '@sosb_CarDetail_DateReleased: '
+ CONVERT(CHAR(10), @sosb_CarDetail_DateReleased, 101)
-- Selecting Date Released to use below
SELECT @sosb_CarDetail_DateReleased = [sosb_CarDetail_DateReleased]
FROM @datesreleased
WHERE dateid = @RowCnt
-- Details for Date Released below
SELECT *
FROM #mytable
WHERE CONVERT( CHAR(10), [sosb_CarDetail_DateReleased], 101 ) = @sosb_CarDetail_DateReleased
-- Totals for the Day (Total Car Details / Sum (Net Tons))
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
WHERE CONVERT( CHAR(10), [sosb_CarDetail_DateReleased], 101 ) = @sosb_CarDetail_DateReleased
GROUP BY CONVERT( CHAR(10), sosb_CarDetail_DateReleased, 101 )
, sosb_SP_Description
ORDER BY CONVERT( CHAR(10), sosb_CarDetail_DateReleased, 101 ) DESC
SELECT @RowCnt = @RowCnt + 1
END
January 3, 2014 at 12:21 pm
SET NOCOUNT ON
DECLARE
@RowCnt INT
, @MaxRows INT
, @sosb_CarDetail_DateReleased DATETIME
, @TotalCarsByDate INT
, @Total_NetTons INT
, @GrandTotalCars INT
, @TotalNetTonsByMonth INT
SELECT @RowCnt = 1
---- =====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
)
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 IDENTITY_INSERT #mytable ON
DECLARE @datesreleased TABLE
(
dateid INT IDENTITY (1, 1) PRIMARY KEY NOT NULL
, sosb_CarDetail_DateReleased DATETIME
)
INSERT INTO @datesreleased
(sosb_CarDetail_DateReleased)
SELECT DISTINCT sosb_CarDetail_DateReleased
FROM #mytable
ORDER BY sosb_CarDetail_DateReleased
SELECT @MaxRows = Count( * )
FROM @datesreleased
WHILE @RowCnt <= @MaxRows
BEGIN
PRINT '@sosb_CarDetail_DateReleased: '
+ CONVERT(CHAR(10), @sosb_CarDetail_DateReleased, 101)
-- Selecting Date Released to use below
SELECT @sosb_CarDetail_DateReleased = [sosb_CarDetail_DateReleased]
FROM @datesreleased
WHERE dateid = @RowCnt
-- Details for Date Released below
SELECT *
FROM #mytable
WHERE CONVERT( CHAR(10), [sosb_CarDetail_DateReleased], 101 ) = @sosb_CarDetail_DateReleased
-- Totals for the Day (Total Car Details / Sum (Net Tons))
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
WHERE CONVERT( CHAR(10), [sosb_CarDetail_DateReleased], 101 ) = @sosb_CarDetail_DateReleased
GROUP BY CONVERT( CHAR(10), sosb_CarDetail_DateReleased, 101 )
, sosb_SP_Description
ORDER BY CONVERT( CHAR(10), sosb_CarDetail_DateReleased, 101 ) DESC
SELECT @RowCnt = @RowCnt + 1
END
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply