July 12, 2012 at 1:47 am
jerome.morris (7/11/2012)
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
Post what you have. The date filter would be in the CTE OrderedData.
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 17, 2012 at 8:07 am
In VS when I have a query like this it ask me for the dates, but running this is VS complains about parsing variables.
This is what I have tried so far
; WITH OrderedData AS (
SELECT
rn = ROW_NUMBER() OVER (ORDER BY Dockets Desc),
Docket_Date
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,
Docket_Date,
Dockets = COUNT(*)
FROM SLADB.dbo.DocketTB a1
Where Docket_Category IS NOT NULL
AND (Docket_Date BETWEEN @date1 AND @date2)
GROUP BY a1.Docket_Category,Docket_Date
) 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
July 19, 2012 at 11:19 am
Anybody help me find my error?
July 19, 2012 at 11:58 am
jerome.morris (7/19/2012)
Anybody help me find my error?
What error(s). All you gave us was this: In VS when I have a query like this it ask me for the dates, but running this is VS complains about parsing variables.
It would help if we could see the actual error message(s) you are getting.
July 19, 2012 at 2:10 pm
Sorry
OK normally I drag my tables onto my Dataset designer and run queries against it like so
SELECT Con1, Con10, Con2, Con3, Con4, Con5, Con6, Con7,Weekend, Contract, Docket_Category, Docket_Date, Docket_DateRaised, Docket_EngFinish, Docket_EngStart, Docket_EngineerName, Docket_Id, Docket_Machine, Docket_Number, Docket_Status, Docket_SubCategory, Duration, Module, Monitor_Time, Operator_Name, Section, Waittime, Weekend, spare8 FROM DocketTB WHERE (Docket_Status = 'CL') AND (Docket_Date BETWEEN @date1 AND @date2) ORDER BY Docket_Date
then from my c# code past Date1 and Date2 like so
DocketTBTableAdapter.FillByAllDowntimeRange(SLADBDataSet.DocketTB, dateTimePicker1.Value.Date,
dateTimePicker2.Value.Date);
So now I have drag my stored procedure onto my DataSet designer but need to past a query similar to above.
Hope this helps
Jay
August 1, 2012 at 4:01 am
Chris M do I need to give more information ?
I can see the finish line almost 🙂
August 1, 2012 at 4:15 am
jerome.morris (8/1/2012)
Chris M do I need to give more information ?I can see the finish line almost 🙂
I'm sorry Jerome, looks like you need a c# developer to help with your final sprint. I'm sure someone will chip in.
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
August 1, 2012 at 4:26 am
No problems Chris. Thanks for your help on this matter.
J
August 2, 2012 at 6:34 am
Hi Chris, I have to set what my parameter is before I call it like so
ALTER PROCEDURE [dbo].[GetCategory]
@mc1 nchar
AS
; 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 SLADB.dbo.DocketTB a1
Where Docket_Category IS NOT NULL AND (Docket_Status = 'CL') AND (Contract = '1')
AND (Docket_Date between '2012/07/23' and '2012/07/29') AND (Docket_Machine = @mc1)--(Docket_Machine ='APS_01')
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
But in VS when I try preiview the data it returns no result! I have noticed that on queris that work the Machine is a string parameter but with the above its a fixedlengthstring.
Any advice ?
I think rather that be a fixed lengh it needs to be a string?
make sense
Jay
August 2, 2012 at 6:38 am
@mc1 varchar(100)
worked
🙂
August 2, 2012 at 6:42 am
Hi Jay
We use VS2010Ultimate/AnkhSVN/SSDT for development here so I'm reasonably familiar with it - yet I'm not sure what you are trying to do or why. If you want to see if the stored procedure works or verify the result set, use SSMS. Declare the variable, assign a value, exec the proc with the variable as a parameter.
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
December 11, 2012 at 7:21 am
Hi Chris, I am using the solution you gave me a few weeks ago as base for my next query, can you look over please and see if there is a better way to do this, more so the query between 2 dates excluding time. (Logged Off and on are both datetime columns)
Thanks in advance
USE [SLADB]
GO
/****** Object: StoredProcedure [dbo].[GetMachine] Script Date: 12/11/2012 14:08:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter PROCEDURE [dbo].[GetMachine1]
@startd datetime,
@endd datetime
AS
; WITH OrderedData AS (
SELECT
rn = ROW_NUMBER() OVER (ORDER BY Dockets Desc),
Reason,
Dockets,
Pct_To_Total = CAST((Dockets*100.0)/SUM(Dockets) OVER(PARTITION BY 1) AS NUMERIC(5,2))
FROM ( SELECT a1.Reason,
Dockets = COUNT(*)
FROM SLADB.dbo.NPT a1
Where Reason IS NOT NULL
AND (LoggedOff >= @startd) and (LoggedOn <=@endd)
GROUP BY a1.Reason
) d
), Calculator AS (
SELECT
rn, Reason, Dockets, Pct_To_Total,
RunningTotal = Pct_To_Total
FROM OrderedData
WHERE rn = 1
UNION ALL
SELECT
tr.rn, tr.Reason, 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
December 11, 2012 at 8:21 am
Hi all, this works but its not really the result I want to see. This is fact counting the number of times a category appears in the list, what I really want to do is sum the duration of a table. How do I implement this. I am finding it hard to step through this and make sense.
sorry
December 11, 2012 at 8:26 am
Dockets = Sum(Duration)
looks better
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply