Running Total

  • I think may I have gone way of the tracks

    I found this which does what I would like but I cant to make it fit my requirments

    SELECT a1.Name, a1.Sales, SUM(a2.Sales)/(SELECT SUM(Sales) FROM Total_Sales) Pct_To_Total

    FROM Total_Sales a1, Total_Sales a2

    WHERE a1.Sales <= a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)

    GROUP BY a1.Name, a1.Sales

    ORDER BY a1.Sales DESC, a1.Name DESC;

  • Attempt 1

    SELECT a1.Docket_Category, a1.Docket_Id, count(a2.Docket_Id)/(SELECT Count(Docket_id) FROM SLADB.dbo.DocketTB) Pct_To_Total

    FROM SLADB.dbo.DocketTB a1, SLADB.dbo.DocketTB a2

    WHERE a1.Docket_Id <= a2.Docket_Id

    GROUP BY a1.Docket_Category, a1.Docket_Id

    ORDER BY a1.Docket_Category DESC

    I no this is wrong by the results returned, and its not grouping the categories together

    Jay

  • Jay

    I don't see anything cumulative yet - the result sets you've posted are simple aggregates:

    SELECT

    Docket_Category,

    [Count],

    [Percentage] = CAST(100*[Count]/(Total*1.00) AS NUMERIC(4,2))

    FROM (

    SELECT

    Docket_Category,

    [Count] = COUNT(*),

    Total = SUM(COUNT(*)) OVER(PARTITION BY (SELECT NULL))

    FROM DocketTB

    GROUP BY Docket_Category

    ) d

    -- results

    /*

    Docket_CategoryCountPercentage

    Mechanical 440 53.14

    Configuration 22 2.66

    NULL 76 9.18

    Software 27 3.26

    Electrical 81 9.78

    Operator 121 14.61

    Material 59 7.13

    Network 2 0.24

    */

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • This query is returning almost what I need,

    SELECT

    a1.Docket_Category,

    COUNT(a1.Docket_Id) as Dockets ,

    -- MAX(a1.Docket_Id) as MaxDocketID,

    (count(a1.Docket_Id) *100) /(SELECT Count(Docket_id) FROM SLADB.dbo.DocketTB) Pct_To_Total

    FROM SLADB.dbo.DocketTB a1

    GROUP BY a1.Docket_Category

    ORDER BY Dockets Desc

    I need to add running total on the Pct_To_Total how can I do this please

    Thanks

  • Jay, using the sample data you posted yesterday, your query above generates the following result:

    Docket_CategoryDocketsPct_To_Total

    Mechanical 440 53

    Operator 121 14

    Electrical 81 9

    NULL 76 9

    Material 59 7

    Software 27 3

    Configuration 22 2

    Network 2 0

    Can you add another column to this table and populate it with the values you are expecting to see?

    Cheers

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • That what I cant do I need to make a that column and add the totals together

    so the last column would look like this

    53

    70

    79

    88

    95

    98

    100

  • Is it possible to select Pct_To_Total and sum on this column and generate another column?

  • jerome.morris (7/11/2012)


    That what I cant do I need to make a that column and add the totals together

    so the last column would look like this

    53

    70

    79

    88

    95

    98

    100

    Like this?

    ; WITH OrderedData AS (

    SELECT

    rn = ROW_NUMBER() OVER (ORDER BY Dockets Desc),

    Docket_Category,

    Dockets,

    Pct_To_Total = CAST((Dockets*100.0)/SUM(Dockets) OVER(PARTITION BY 1) AS NUMERIC(5,2))

    FROM (

    SELECT

    a1.Docket_Category,

    Dockets = COUNT(*)

    FROM tempdb.dbo.DocketTB a1

    GROUP BY a1.Docket_Category

    ) d

    ), Calculator AS (

    SELECT

    rn, Docket_Category, Dockets, Pct_To_Total,

    RunningTotal = Pct_To_Total

    FROM OrderedData

    WHERE rn = 1

    UNION ALL

    SELECT

    tr.rn, tr.Docket_Category, tr.Dockets, tr.Pct_To_Total,

    RunningTotal = CAST(lr.RunningTotal+tr.Pct_To_Total AS NUMERIC(5,2))

    FROM Calculator lr

    INNER JOIN OrderedData tr ON tr.rn = lr.rn+1

    ) SELECT * FROM Calculator ORDER BY rn

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Wow just like that, that so far advance to what I was trying 🙁

    I will try make some sense of it and understand just what is happening.

    Thank you Kindly.

    I am trying to save this as a view so I can select it as my dataset for my report. Is this known as a stored procedure ?

  • Jay, don't be afraid to ask - so long as you're willing to put in some effort, which you certainly have done so far, then folks will chip in to help.

    There are two CTE's, "OrderedData" and "Calculator".

    OrderedData does the aggregation and numbers the rows.

    Calculator is a recursive CTE, and it uses the row numbers to crawl through the data a row at a time, accumulating the docket count.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • When I execute this in SQL the results are perfect and in the correct order, I use a stored procedure and then drag to my DataSet designer in VS2010 create my report . When I run the report the order is not the same even if I apply sorting. I no this is not SQL related but was wondering if you had any ideas?

    Thanks

    J

  • jerome.morris (7/11/2012)


    When I execute this in SQL the results are perfect and in the correct order, I use a stored procedure and then drag to my DataSet designer in VS2010 create my report . When I run the report the order is not the same even if I apply sorting. I no this is not SQL related but was wondering if you had any ideas?

    Thanks

    J

    Can you post the script for the stored procedure?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • I found out the Row grouping was incorrect in my report so its working now. I will eventually select Docket_Date also but will try a figure out where to put in the query, if I struggle I will post my attempts for you to have a look at and then advise me where I am going wrong

    Again thank you for your help

    J

  • jerome.morris (7/11/2012)


    I found out the Row grouping was incorrect in my report so its working now. I will eventually select Docket_Date also but will try a figure out where to put in the query, if I struggle I will post my attempts for you to have a look at and then advise me where I am going wrong

    Again thank you for your help

    J

    Anytime J, thanks for the feedback 😎

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • There is lots of selects in this so I am not 100% where to put the date part. I also want to use Variables form from C# app.

    (Docket_Date BETWEEN @date1 AND @date2)

    I find this extremely difficult with this complex query maybe not to you but to me

Viewing 15 posts - 16 through 30 (of 43 total)

You must be logged in to reply to this topic. Login to reply