July 9, 2012 at 10:36 am
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;
July 9, 2012 at 10:44 am
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
July 10, 2012 at 3:01 am
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_Category Count Percentage
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
*/
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
July 11, 2012 at 7:17 am
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
July 11, 2012 at 7:23 am
Jay, using the sample data you posted yesterday, your query above generates the following result:
Docket_Category Dockets Pct_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
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
July 11, 2012 at 7:32 am
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
July 11, 2012 at 8:23 am
Is it possible to select Pct_To_Total and sum on this column and generate another column?
July 11, 2012 at 8:39 am
jerome.morris (7/11/2012)
That what I cant do I need to make a that column and add the totals togetherso 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
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
July 11, 2012 at 8:48 am
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 ?
July 11, 2012 at 8:54 am
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.
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
July 11, 2012 at 9:41 am
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
July 11, 2012 at 9:43 am
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?
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
July 11, 2012 at 9:47 am
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
July 11, 2012 at 9:50 am
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 wrongAgain thank you for your help
J
Anytime J, thanks for the feedback 😎
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
July 11, 2012 at 12:26 pm
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