Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Count instances across fields by date? Expand / Collapse
Author
Message
Posted Tuesday, April 23, 2013 5:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:54 AM
Points: 287, Visits: 403
I need to search a table and count instances of a particular string across multiple fields by date, I've used the following Dynamic SP in the past to look through fields and was hoping I could adapt it. I've tried different ways to change it to Count with no success, or am I barking up the wrong tree and this isn't the way to do it? I would like to keep it Dynamic so I don't have to specify field names as I may use this on other tables.

declare @TmpName as varchar (2000)
declare @TmpName1 as varchar (2000)
declare @sdate as varchar (20)
declare @svar as varchar (2000)
declare @CMD Nvarchar(100)
declare @Status1 as varchar(2000)
declare @sSearchterm as varchar(50)

set @sSearchterm = '%something%'

set @sDate = '29/Apr/2013'
DECLARE CUR1 CURSOR FAST_FORWARD FOR
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'resource'

OPEN CUR1
FETCH NEXT FROM CUR1 INTO @TmpName

WHILE (@@FETCH_STATUS = 0)
BEGIN



SET @CMD = 'SELECT @Rtn = [' + @TmpName + '] from [resource]' + ' where [date] = ''' + @sDate + ''''
exec sp_executesql @CMD,N'@Rtn varchar (200) out',@Status1 out
print @Status1


FETCH NEXT FROM CUR1 INTO @TmpName
END
CLOSE CUR1
DEALLOCATE CUR1

Post #1445359
Posted Tuesday, April 23, 2013 5:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:54 AM
Points: 287, Visits: 403
I've sort of cobbled something that works, my next question is how do I return the value @alcount rather than print it?

declare @TmpName as varchar (2000)
declare @TmpName1 as varchar (2000)
declare @sdate as varchar (20)
declare @svar as varchar (2000)
declare @CMD Nvarchar(100)
declare @Status1 as varchar(2000)
declare @alcount as int
set @alcount = 0
set @sDate = '29/Apr/2013'

DECLARE CUR1 CURSOR FAST_FORWARD FOR
select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'resource'

OPEN CUR1
FETCH NEXT FROM CUR1 INTO @TmpName

WHILE (@@FETCH_STATUS = 0)
BEGIN

if @tmpname <> 'Date' and @tmpname <> 'temp'
begin
SET @CMD = 'SELECT @Rtn = [' + @TmpName + '] from [resource]' + ' where [date] = ''' + @sDate + ''''
exec sp_executesql @CMD,N'@Rtn varchar (200) out',@Status1 out

if @Status1 like 'A/L%'
begin
set @alcount = @alcount + 1
end
end
FETCH NEXT FROM CUR1 INTO @TmpName
END
CLOSE CUR1
DEALLOCATE CUR1
print @alcount

Post #1445369
Posted Thursday, April 25, 2013 6:46 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 8, 2013 7:16 PM
Points: 221, Visits: 132
Mick,

Can you give us some kind of idea what your data looks like within the table? This will help us with a query. I've used CHARINDEX on a few instances to find certain text within fields.


--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/
You can also follow my twitter account to get daily updates: @BLantz2455
Post #1446761
Posted Thursday, April 25, 2013 9:55 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
Here's what I came up with using dynamic SQL. To retrieve the column names dynamically it uses a function which I've included below.


DECLARE
@strSearch NVARCHAR(4000)
,@strSQL NVARCHAR(MAX)
,@strPath NVARCHAR(250)
,@pDate DATETIME
,@pDateSearchCol NVARCHAR(20)

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable

CREATE TABLE #TempTable (
[ID] INT IDENTITY(1,1) NOT NULL,
[ColVal] NVARCHAR(50) NULL,
PRIMARY KEY (ID))


SET @strSearch = N'%contentpane%' --ex 1
SET @strPath = N'LocalTestDB.dbo.Tabs' --ex1

--SET @strSearch = N'22%' --ex 2
--SET @strPath = N'LocalTestDB.dbo.TabModules' --ex2

SET @pDate = '2013-01-01'
SET @pDateSearchCol = 'LastModifiedOnDate'

SET @strSQL = N''

;WITH cteCols
AS
(
SELECT ORDINAL_POSITION, COLUMN_NAME
FROM dbo.itvfGetColumnNames(''+@strPath+'')
WHERE ORDINAL_POSITION > 0
)
SELECT
@strSQL = @strSQL +
N'SELECT '+ c.COLUMN_NAME + ' ' +
N'FROM '+@strPath+'' + ' ' +
N'WHERE DATEDIFF(day,'+@strPath+'.'+@pDateSearchCol+','''+CONVERT(VARCHAR(30),@pDate,121)+''') > 0 '+
N'AND ' + c.COLUMN_NAME + ' LIKE '''+@strSearch+'''; '+CHAR(10)+CHAR(13)
FROM
cteCols c


INSERT INTO #TempTable
EXEC sp_executeSQL @strSQL

SELECT COUNT(*) AS NumRows FROM #TempTable



CREATE FUNCTION [dbo].[itvfGetColumnNames]
(
@SourceFullPath SYSNAME
)
RETURNS TABLE
AS
RETURN
(
WITH Keys
AS (
SELECT
iso.COLUMN_NAME
,iso.COLUMN_DEFAULT
,iso.ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.COLUMNS AS iso
WHERE
iso.TABLE_CATALOG = PARSENAME(@SourceFullPath,3)
AND iso.TABLE_SCHEMA = PARSENAME(@SourceFullPath,2)
AND iso.TABLE_NAME = PARSENAME(@SourceFullPath,1)
)
SELECT TOP 100 PERCENT
ROW_NUMBER() OVER (ORDER BY ORDINAL_POSITION) AS rownum
,COLUMN_NAME
,COLUMN_DEFAULT
,ORDINAL_POSITION
FROM
Keys
ORDER BY
ORDINAL_POSITION


/*
SELECT * FROM dbo.itvfGetColumnNames('[FullyQualifiedThreePartDBName]')
*/

)


Post #1446786
Posted Tuesday, April 30, 2013 7:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:54 AM
Points: 287, Visits: 403
Sorry everyone that I haven't got back to you, I've been on holiday. I will be trying your solutions when my work settles down and get back to you all. The code looks great.. many thanks
Post #1448013
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse