Running Total

Question

Post reply

Running Total

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

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

jerome.morris SSCrazy Points: 2829 More actions · Answer 1

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;

jerome.morris SSCrazy Points: 2829 More actions · Answer 2

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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

jerome.morris SSCrazy Points: 2829 More actions · Answer 4

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

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

jerome.morris SSCrazy Points: 2829 More actions · Answer 6

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

jerome.morris SSCrazy Points: 2829 More actions · Answer 7

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

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

jerome.morris SSCrazy Points: 2829 More actions · Answer 9

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 ?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

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

jerome.morris SSCrazy Points: 2829 More actions · Answer 11

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

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

jerome.morris SSCrazy Points: 2829 More actions · Answer 13

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

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

jerome.morris SSCrazy Points: 2829 More actions · Answer 15

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