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 Thursday, January 02, 2014 8:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:46 AM
Points: 6, Visits: 30
Randy:

This is great! This is about 90% of what I need.

I have 2 questions.

Your code generated the following:

DateReleased TotalCarsByDate sosb_SP_Description Total_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


MonthReleased GrandTotalCars sosb_SP_Description TotalNetTonsByMonth
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, NH 303.68

12/20/2013 Detail Record 1
12/20/2013 Detail Record 2

Daily Totals: 12/20/2013 2 Claremont, NH 199.97

Location Totals: 12 5 Claremont, NH 503.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




Post #1527159
Posted Thursday, January 02, 2014 4:07 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 5:58 PM
Points: 7, Visits: 66
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



Post #1527315
Posted Friday, January 03, 2014 12:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 5:58 PM
Points: 7, Visits: 66
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



Post #1527687
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse