Home Forums SQL Server 2008 SQL Server Newbies Coding Counts and Subtotals by day, location and grand totals in T-SQL RE: Coding Counts and Subtotals by day, location and grand totals in T-SQL

  • 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