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