Count instances across fields by date?

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    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

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    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

  • Calibear

    Ten Centuries

    Points: 1377

    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/[/url]
    You can also follow my twitter account to get daily updates: @BLantz2455

  • Steven Willis

    SSCrazy Eights

    Points: 9893

    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]')

    */

    )

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    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

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply