SQL Server Stored Proc
I have query that I need to convert to a far more advanced parameterized stored procedure..
To keep this as simple as possible instead of posting my query I am going to make a fake query and explain from that what needs to happen.
I have a query that pulls the following data.
Name/ Assessment / Age / DateAssessed
John / Good / 34 / 7-7-2011
Robb/ Bad / 60 / 7-1-2011
From the stored procedure I need to be able to do this against this data.
I need to be able to input the assessment value ...which is one of 4 values...good, very good, bad, very bad and return a record count that is totaled by the date field over 7 day increments.
I need to be able to input the assessment value along with and age which is also one of 4 values and return a record count that is totaled by the date field over 7 day increments.
If all this could be done in one pull....or 4 procedure calls.that would be awsome... I am attaching sample output for 1 of the 4 scenarios.
To keep this as simple as possible instead of posting my query I am going to make a fake query and explain from that what needs to happen.
I have a query that pulls the following data.
Name/ Assessment / Age / DateAssessed
John / Good / 34 / 7-7-2011
Robb/ Bad / 60 / 7-1-2011
From the stored procedure I need to be able to do this against this data.
I need to be able to input the assessment value ...which is one of 4 values...good, very good, bad, very bad and return a record count that is totaled by the date field over 7 day increments.
I need to be able to input the assessment value along with and age which is also one of 4 values and return a record count that is totaled by the date field over 7 day increments.
If all this could be done in one pull....or 4 procedure calls.that would be awsome... I am attaching sample output for 1 of the 4 scenarios.
Level Date Total Good Total Good with Age Value
Good 4/25/2011 4194 2369
5/2/2011 3686 2528
5/11/2011 2021 1058
5/16/2011 2211 1384
5/19/2011 3181 1008
5/23/2011 2618 725
5/24/2011 2288 525
5/31/2011 1684 555
6/8/2011 1523 688
6/13/2011 1390 521
6/20/2011 6386 196
6/27/2011 4618 214
Last Comment
There are a few ways to do this. You could do this with a simple table, recording the 4 values, and then using views to present the data in the way you want. You can call those views from the same or seperate stored procedure, depending on how you plan on accessing the data and the flow of your program. Or you could put the query for the view right in the stored procedured.
ASKER
I am looking for syntax that would pass the params and return the data counted and trended over those dates....similar to pivoting....I will be calling the proc from code for charting
I was trying to make a sample query for you. but your data in your sample sort of confuses me. You have a date column, but no sum by date. you have a sum by age column, but you don't display the age. Not really sure what you are getting at.
ASKER
the output table I made as an example would be a count of the names...provided both params or one param is passed and then aggregated in time every seven days.. the bale age and assess soul be params
ASKER
I can post part of my query if that will make it easier..but was trying to keep the idea simple...I will blank out the inner part of the query to help make it less complicated...the whole name/asset...ect...is not the real column names in the query but it should be straight forward.
The below query outputs Name, RatingDescription,Days,Dat
I need to convert this to a stored proc that accomplishes the following and then ouputs in the format as I showed early...except the headers on the above sample output would change accordingly to this more real example.
I would need to be able to pass in the RatingDescripton and get a count of name on that criteria...along the trended 7 day periods.
I would also need to pass in the ratingdescription along with the days criteria along the same trended 7 days period.
These two values are subsets of each other...and would like to see if flipped as in a pivot so you would get output from one sp call that handled both scearios at once.
The output would be for the critical scenario: would be 3 columns like so.
Date Total Critical Total Critical with days 14
4/25/2011 4194 2369
5/2/2011 3686 2528
5/11/2011 2021 1058
5/16/2011 2211 1384
5/19/2011 3181 1008
I would then do the same thing for High, Medium, and Low...
each of those having a 2nd column based on the subset count with days.
The below query outputs Name, RatingDescription,Days,Dat
I need to convert this to a stored proc that accomplishes the following and then ouputs in the format as I showed early...except the headers on the above sample output would change accordingly to this more real example.
I would need to be able to pass in the RatingDescripton and get a count of name on that criteria...along the trended 7 day periods.
I would also need to pass in the ratingdescription along with the days criteria along the same trended 7 days period.
These two values are subsets of each other...and would like to see if flipped as in a pivot so you would get output from one sp call that handled both scearios at once.
The output would be for the critical scenario: would be 3 columns like so.
Date Total Critical Total Critical with days 14
4/25/2011 4194 2369
5/2/2011 3686 2528
5/11/2011 2021 1058
5/16/2011 2211 1384
5/19/2011 3181 1008
I would then do the same thing for High, Medium, and Low...
each of those having a 2nd column based on the subset count with days.
select
t2.Name
t2.RatingDescription,
t2.Days,
t2.Date
from
(select t1.Name,
CASE
WHEN t1.Rating >= 22 THEN 'Critical'
WHEN t1.Rating between 20 and 21 THEN 'High'
WHEN t1.Rating between 18 and 19 THEN 'Medium'
WHEN t1.Rating between 0 and 17 THEN 'Low'
ELSE 'n/a'
END as RatingDesc,
CASE
WHEN t1.Days > 14 THEN '14'
WHEN t1.Days > 30 THEN '30'
WHEN t1.Days > 60 THEN '60'
WHEN t1.Days > 90 THEN '90'
ELSE '0'
END as Days,
t1.Date
from
(SELECT Items
FROM tables
)t1
)t2
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
so in the above, replace tables with a subquery of your data source
oops, forgot to rename the procedure, call it whatever you want obviously, but I named it there byDecription, when it is really ByRatingAndDays.
ASKER
I dont understand the date piece to this but I see where your going everywhere else....
Lets say in the sql query...there are 90 days of information. I only want to count lets say over this 90 days of data.....the data that is there every 7 days.
So lets say I did a count of name from tables where rating = @Desc and Days = @Days the count would be totaled by Today for all counts with a date of today....then a count for all dates for a day of y days ago...and continuing until there is no date
So only the data on those days gets counted.
I dont know if this makes it complicated...but it would be really cool if there was a way just to pass in the sp the date increment...so if I said 7...it gave me this weekly trend...or if I put 1 it did daily....etc.
Lets say in the sql query...there are 90 days of information. I only want to count lets say over this 90 days of data.....the data that is there every 7 days.
So lets say I did a count of name from tables where rating = @Desc and Days = @Days the count would be totaled by Today for all counts with a date of today....then a count for all dates for a day of y days ago...and continuing until there is no date
So only the data on those days gets counted.
I dont know if this makes it complicated...but it would be really cool if there was a way just to pass in the sp the date increment...so if I said 7...it gave me this weekly trend...or if I put 1 it did daily....etc.
ASKER
I have two sub queires in this stored proc....and its giving me row issues...the data comes out like this. I am pasting the entire sp.
date total1 date total2
05/12/2011 1 05/12/2011 1
06/09/2011 2 05/12/2011 1
06/23/2011 17 05/12/2011 1
06/30/2011 137 05/12/2011 1
05/12/2011 1 06/09/2011 2
06/09/2011 2 06/09/2011 2
06/23/2011 17 06/09/2011 2
06/30/2011 137 06/09/2011 2
Somehow I need this to pivot on Date....so it would look like this.
date total1 total2
5/12/2011 1 1
6/9/2011 2 1
6/23/2011 17 null or 0
6/30/2011 137 null or 0
date total1 date total2
05/12/2011 1 05/12/2011 1
06/09/2011 2 05/12/2011 1
06/23/2011 17 05/12/2011 1
06/30/2011 137 05/12/2011 1
05/12/2011 1 06/09/2011 2
06/09/2011 2 06/09/2011 2
06/23/2011 17 06/09/2011 2
06/30/2011 137 06/09/2011 2
Somehow I need this to pivot on Date....so it would look like this.
date total1 total2
5/12/2011 1 1
6/9/2011 2 1
6/23/2011 17 null or 0
6/30/2011 137 null or 0
Create Proc DSWX_VulnRatingSummary (@Rating varchar(50), @Aging int)
AS
BEGIN
DECLARE @Nth int, @MaxGoBack int, @Today datetime
SET @Nth = 7
SET @MaxGoBack = 90
SET @Today = DATEADD(day, DATEDIFF(day, '1900-01-01', GETDATE()), '1900-01-01')
select TotalsForRating.DateDetected, TotalsForRating.Total as Rating, TotalsForAging.total as total
from
(
SELECT t3.DateDetected, COUNT(t3.VulnName) AS Total
from
(
select
t2.DeviceName,
t2.IPAddress,
t2.VulnName,
t2.VulnDescription,
t2.ScanSource,
convert(varchar,t2.DateDetected,101) as DateDetected,
t2.Aging,
t2.AgingDays,
t2.DSWX_VulnRating,
t2.DSWX_VulnRatingDesc,
t2.VendorSeverity,
t2.VendorSeverityValue
from
(select t1.DeviceName,
t1.IPAddress,
t1.VulnID,
t1.VulnName,
t1.VulnDescription,
t1.ScanSource,
t1.VendorSeverity,
t1.VendorSeverityValue,
CASE
WHEN t1.DSWX_VulnRating >= 22 THEN 'Critical'
WHEN t1.DSWX_VulnRating between 20 and 21 THEN 'High'
WHEN t1.DSWX_VulnRating between 18 and 19 THEN 'Medium'
WHEN t1.DSWX_VulnRating between 0 and 17 THEN 'Low'
ELSE 'n/a'
END as DSWX_VulnRatingDesc,
t1.DSWX_VulnRating,
CASE
WHEN t1.AgingDays > 14 THEN '14'
WHEN t1.AgingDays > 30 THEN '30'
WHEN t1.AgingDays > 60 THEN '60'
WHEN t1.AgingDays > 90 THEN '90'
ELSE '0'
END as Aging,
t1.AgingDays,
t1.DateDetected
from
(SELECT h.DeviceName,
h.IPAddress,
v.VulnID,
v.VulnName,
v.VulnDescription,
h.RiskRatingAsset,
v.RiskRatingVuln,
sc.Value AS VendorSeverityValue,
h.RiskRatingCounter AS RiskCounterMeasuresRating,
h.RiskRatingAsset + sc.Value + v.RiskRatingVuln - h.RiskRatingCounter AS DSWX_VulnRating,
v.Severity AS VendorSeverity,
v.DateDetected,
v.AgingDays,
v.ScanSource
FROM dbo.SWRX_SeverityConversionTable sc RIGHT OUTER JOIN dbo.SWRX_MasterVulnerabilityList v
ON sc.Severity = v.Severity
RIGHT OUTER JOIN dbo.SWRX_MasterHost h
ON v.DeviceName = h.DeviceName
WHERE (NOT (v.VulnName LIKE 'CD-%'))
)t1
)t2
where t2.ScanSource <> 'Sherlock'
)t3
WHERE t3.DSWX_VulnRatingDesc = @Rating
and t3.DateDetected < @Today AND
t3.DateDetected >= DATEADD(day, -@MaxGoBack, @Today) AND
(DATEDIFF(day, @Today, t3.DateDetected) % @Nth) = 0
group by t3.DateDetected
) as TotalsForRating,
(
SELECT t3.DateDetected, COUNT(t3.VulnName) AS Total
from
(
select
t2.DeviceName,
t2.IPAddress,
t2.VulnName,
t2.VulnDescription,
t2.ScanSource,
convert(varchar,t2.DateDetected,101) as DateDetected,
t2.Aging,
t2.AgingDays,
t2.DSWX_VulnRating,
t2.DSWX_VulnRatingDesc,
t2.VendorSeverity,
t2.VendorSeverityValue
from
(select t1.DeviceName,
t1.IPAddress,
t1.VulnID,
t1.VulnName,
t1.VulnDescription,
t1.ScanSource,
t1.VendorSeverity,
t1.VendorSeverityValue,
CASE
WHEN t1.DSWX_VulnRating >= 22 THEN 'Critical'
WHEN t1.DSWX_VulnRating between 20 and 21 THEN 'High'
WHEN t1.DSWX_VulnRating between 18 and 19 THEN 'Medium'
WHEN t1.DSWX_VulnRating between 0 and 17 THEN 'Low'
ELSE 'n/a'
END as DSWX_VulnRatingDesc,
t1.DSWX_VulnRating,
CASE
WHEN t1.AgingDays > 14 THEN '14'
WHEN t1.AgingDays > 30 THEN '30'
WHEN t1.AgingDays > 60 THEN '60'
WHEN t1.AgingDays > 90 THEN '90'
ELSE '0'
END as Aging,
t1.AgingDays,
t1.DateDetected
from
(SELECT h.DeviceName,
h.IPAddress,
v.VulnID,
v.VulnName,
v.VulnDescription,
h.RiskRatingAsset,
v.RiskRatingVuln,
sc.Value AS VendorSeverityValue,
h.RiskRatingCounter AS RiskCounterMeasuresRating,
h.RiskRatingAsset + sc.Value + v.RiskRatingVuln - h.RiskRatingCounter AS DSWX_VulnRating,
v.Severity AS VendorSeverity,
v.DateDetected,
v.AgingDays,
v.ScanSource
FROM dbo.SWRX_SeverityConversionTable sc RIGHT OUTER JOIN dbo.SWRX_MasterVulnerabilityList v
ON sc.Severity = v.Severity
RIGHT OUTER JOIN dbo.SWRX_MasterHost h
ON v.DeviceName = h.DeviceName
WHERE (NOT (v.VulnName LIKE 'CD-%'))
)t1
)t2
where t2.ScanSource <> 'Sherlock'
)t3
WHERE t3.DSWX_VulnRatingDesc = @Rating and t3.Aging = @Aging
and t3.DateDetected < @Today AND
t3.DateDetected >= DATEADD(day, -@MaxGoBack, @Today) AND
(DATEDIFF(day, @Today, t3.DateDetected) % @Nth) = 0
group by t3.DateDetected
) as TotalsForAging
end
You have quite a bit of cleanup to do in the query, but mainly, you seem to be really confused on how you are correlating your DateDetected to your totals. Please write this very descriptly, and I can help further. Otherwise edit the query below to the best of your abilty. Remember the below where clause will not work, but this is the structure you need to get the output you want. (two subqueries as columns).
Below is the best I could do with what you have provided, you really need to change that WHERE clause to correlate to the totals.
FYI, you could have put GetDate() where you put @Today in the dateadd, but I just left it.
Below is the best I could do with what you have provided, you really need to change that WHERE clause to correlate to the totals.
FYI, you could have put GetDate() where you put @Today in the dateadd, but I just left it.
Create View View_DSWX_VulnRatingSummary_SourceData as
SELECT h.DeviceName,
h.IPAddress,
v.VulnID,
v.VulnName,
v.VulnDescription,
CASE
WHEN t1.DSWX_VulnRating >= 22 THEN 'Critical'
WHEN t1.DSWX_VulnRating between 20 and 21 THEN 'High'
WHEN t1.DSWX_VulnRating between 18 and 19 THEN 'Medium'
WHEN t1.DSWX_VulnRating between 0 and 17 THEN 'Low'
ELSE 'n/a'
END as DSWX_VulnRatingDesc,
h.RiskRatingAsset,
v.RiskRatingVuln,
sc.Value AS VendorSeverityValue,
h.RiskRatingCounter AS RiskCounterMeasuresRating,
h.RiskRatingAsset + sc.Value + v.RiskRatingVuln - h.RiskRatingCounter AS DSWX_VulnRating,
v.Severity AS VendorSeverity,
v.DateDetected,
v.AgingDays,
CASE
WHEN v.AgingDays > 14 THEN '14'
WHEN v.AgingDays > 30 THEN '30'
WHEN v.AgingDays > 60 THEN '60'
WHEN v.AgingDays > 90 THEN '90'
ELSE '0'
END as Aging,
v.ScanSource
FROM dbo.SWRX_SeverityConversionTable sc RIGHT OUTER JOIN dbo.SWRX_MasterVulnerabilityList v
ON sc.Severity = v.Severity
RIGHT OUTER JOIN dbo.SWRX_MasterHost h
ON v.DeviceName = h.DeviceName
WHERE (NOT (v.VulnName LIKE 'CD-%'))
ScanSource <> 'Sherlock'
GO
Create Proc DSWX_VulnRatingSummary (@Rating varchar(50), @Aging int)
AS
BEGIN
DECLARE @Nth int, @MaxGoBack int, @Today datetime
SET @Nth = 7
SET @MaxGoBack = 90
SET @Today = DATEADD(day, DATEDIFF(day, '1900-01-01', GETDATE()), '1900-01-01')
select distinct
v1.DateDetected,
(Select count(v2.VulName) from View_DSWX_VulnRatingSummary_SourceData v2
WHERE v2.DSWX_VulnRatingDesc = @Rating
and v2.DateDetected < @Today AND
v2.DateDetected >= DATEADD(day, -@MaxGoBack, @Today) AND
(DATEDIFF(day, @Today, v2.DateDetected) % @Nth) = 0) as TotalForRating,
(Select count(v3.VulName) from View_DSWX_VulnRatingSummary_SourceData v3 WHERE v3.DSWX_VulnRatingDesc = @Rating and v3.Aging = @Aging
and v3.DateDetected < @Today AND
v3.DateDetected >= DATEADD(day, -@MaxGoBack, @Today) AND
(DATEDIFF(day, @Today, v3.DateDetected) % @Nth) = 0) as TotalForAging,
from View_DSWX_VulnRatingSummary_SourceData v1
end
I will be away for 6 days now. Sorry to leave you like this, but if you need more help, I can help you then. Otherwise make a call for attention or delete or something.
ASKER
It was fixed with one line of code at the bottom....I will give you points though as you got me off on the right foot.
Thanks for you help.
Thanks for you help.
ASKER
This part atleast had the params and the two subselects working.
It was missing the outer join and the date piece was not correct.
It was missing the outer join and the date piece was not correct.
Microsoft SQL Server 2008
Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the Always On technologies and support for unstructured data types.
50K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
