November 19, 2013 at 6:12 am
Hi Guys,
I need to pass comma separated date values in a single parameter and do a Year on Year comparison and see the counts for those days e.g. @DateComparison = '2011-11-01,2012-11-01,2013-11-01'
At the moment I’m able to do IncidentType counts for each day using a From and To date parameters e.g. @dteFromDate = '2013-11-01', @dteToDate = '2013-11-19'.
I now need to get rid of the From and To date and just use one date parameter and be able to select multiple dates from that single parameter.
I’m not sure if this would be achieved with a split function as I’d normally use for splitting string values, please assist if you can.
Thanks
--Here’s How I’m getting the values for each day at the moment
if (@vchComparisonGroup = 'daily')
begin
set @vchComparison = substring(convert(varchar, dateadd(day, @cnt, @dteFromDate), 113), 1, 6)
set @vchYear = cast(year(dateadd(day, @cnt, @dteFromDate)) as varchar)
end
The final result set is return dynamically using the Sql below, the stored proc is too long to copy and paste here:
set @sql = 'update #final set [' + @vchComparison + ' ' + @vchYear + '] = ' + cast(@incidentTypeCount as varchar) + ' where [uidIncidentTypeId] = ''' + cast(@uidCurrIncidentTypeId as varchar(36)) + ''''
exec sp_sqlexec @sql
--Current Output
Incident Type01 Nov 201302 Nov 201303 Nov 2013
Safety Stock Reached100
SLA Expiry030
--I’d like to return the new output as:
Incident Type01 Nov 201101 Nov 201201 Nov 2013
Safety Stock Reached100
SLA Expiry030
any help would be appreaciated 🙂
November 19, 2013 at 8:15 am
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
I can tell you from what you posted that this appears to vulnerable to sql injection. Also, if you want to split values take a look at the link in my signature about splitting strings.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 20, 2013 at 6:24 am
Hi,
Thanks for your response, I've managed to come up with a solution to split the dates:
-- Get comparison dates
Create Table #TempComparison(dteDateComparison varchar(36), bitProcessed bit)
IF (@dteDateComparison <> '')
Insert Into #TempComparison (dteDateComparison, bitProcessed)
Select Distinct Convert(Varchar, dteIncidentDate, 111), 0 From dtlIncident
Where Convert(Varchar, dteIncidentDate, 111)
IN (Select Value From dbo.[fnSplitString](@dteDateComparison, ','))
There's too may tables involved in this report that's why it was a bit of a mission to put everything in here.
Regards Teee
November 20, 2013 at 7:11 am
Teee (11/20/2013)
Hi,Thanks for your response, I've managed to come up with a solution to split the dates:
-- Get comparison dates
Create Table #TempComparison(dteDateComparison varchar(36), bitProcessed bit)
IF (@dteDateComparison <> '')
Insert Into #TempComparison (dteDateComparison, bitProcessed)
Select Distinct Convert(Varchar, dteIncidentDate, 111), 0 From dtlIncident
Where Convert(Varchar, dteIncidentDate, 111)
IN (Select Value From dbo.[fnSplitString](@dteDateComparison, ','))
There's too may tables involved in this report that's why it was a bit of a mission to put everything in here.
Regards Teee
If your split function has any loops, cursors or xml I would HIGHLY recommend reading the article I reference in my signature block about splitting strings. The performance of your system will appreciate it. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 20, 2013 at 7:58 pm
I hope that the OP realizes that it is no longer necessary to pass multiple values within a comma-delimited list to a SP.
Use a Table-valued Parameter instead.
The only question of course is whether the TVP would be in the scope of the dynamic SQL. I've never tried that myself.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 20, 2013 at 8:06 pm
dwain.c (11/20/2013)
I hope that the OP realizes that it is no longer necessary to pass multiple values within a comma-delimited list to a SP.Use a Table-valued Parameter instead.
The only question of course is whether the TVP would be in the scope of the dynamic SQL. I've never tried that myself.
Answering my own question, it can be made to work:
CREATE TYPE MyTab AS TABLE
(
col1 INT
);
GO
DECLARE @T MyTab;
INSERT INTO @T
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
DECLARE @SQL NVARCHAR(MAX)
,@PARM NVARCHAR(MAX) = N'@T MyTab READONLY';
SELECT @SQL = N'SELECT * FROM @T';
EXEC sp_executesql @SQL, @PARM, @T = @T;
GO
DROP TYPE MyTab;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 20, 2013 at 10:31 pm
Thanks for your suggestions, I'll have a look and improve my script.
Regards Teee 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply