SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Coding Counts and Subtotals by day, location and grand totals in T-SQL


Coding Counts and Subtotals by day, location and grand totals in T-SQL

Author
Message
rsiem
rsiem
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 125
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
RandyOM
RandyOM
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 310
Sorry I had some (back at work fires) to put out. Smile

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



RandyOM
RandyOM
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 310
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search