Forum Replies Created

Viewing 14 posts - 1,396 through 1,409 (of 1,409 total)

  • RE: Set-based Aggregates Problem

    select row_number() over(partition by Value order by Value) as ID,

    Value

    from TableA

  • RE: Grouping question

    Afraid so, 'WITH' is SQL Server 2005 only and is used for CTEs (common table expressions).

  • RE: Grouping question

    with CTE1 as

    (select EmpID, LocationID,

    count(*) over(partition by EmpID,LocationID) as cn,

    sum(Amount) over(partition by EmpID) as...

  • RE: CLR to integrate with Outlook Calendar

    If you register your 32 bit VB6 COM object as a COM+ component you should be able to instantiate it in SQL 2005 x64

  • RE: Clarity vs Speed

    To be honest I wouldn't use any, instead I'd use a range table such as below, avoids hardwiring values

    create table Ranges(GrossPayMin decimal(10,3) not null,

    ...

  • RE: select last record customer record

    Maybe this

    WITH CTE AS

    (SELECT ID, CUSTPHONENO, EVENTTIME, DUEDATE, ASSIGNEDFORDATE,

    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY EVENTTIME DESC) as rn

    FROM tbl_table1)

    SELECT ID, CUSTPHONENO, EVENTTIME,...

  • RE: How to transform a string to mixed case

    Try searching for "InitCap"

    Here's a simple C# implementation

    using System;

    using System.Data;

    using System.Data.SqlClient;

    using System.Data.SqlTypes;

    using Microsoft.SqlServer.Server;

    using System.Globalization;

    public partial class UserDefinedFunctions

    {

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlString InitCap(SqlString...

  • RE: String Function Help

    You can avoid looping by using a numbers table

    http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

    declare @S varchar(100)

    set @S='aa,bb,cc,dd,ee,ff,gg,hh,ii,jj';

    with cte as

    (select Number,row_number() over(order by Number) as occurance

    from Numbers

    ...

  • RE: "function"

    Using a table of numbers as in

    http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html

    DECLARE @STR VARCHAR(100)

    SET @STR='1, 2, 3, 4, 5, 50, 99 ,100'

    SELECT CAST(SUBSTRING(@str,

    ...

  • RE: Could anyone help me on this query

    Here's another way

    SELECT R_id,ProductCode,FilterDesc,RowsCount,FilterCount,

    SUM(RowsCount) OVER(PARTITION BY FilterDesc) AS SUMROWCOUNT

    FROM @SAMPLE

  • RE: MAX() problem

    Untested, but this should work

    WITH CTE AS

    (SELECT MCID, Date, SepCode,

    ROW_NUMBER() OVER(PARTITION BY MCID ORDER BY Date DESC) as rn

    FROM Employment)

    SELECT MCID, Date,...

  • RE: Find sub-tree nodes

    Try this

    DECLARE @RootID INT

    SET @RootID=12;

    WITH CTE AS(

    SELECT GroupID,ParentGroupID

    FROM AframeGroup

    WHERE ParentGroupID=@RootID

    UNION ALL

    SELECT a.GroupID,a.ParentGroupID

    FROM AframeGroup a

    INNER JOIN CTE c ON c.GroupID=a.ParentGroupID)

    SELECT GroupID

    FROM CTE

    ORDER BY GroupID

  • RE: How to do

    Here's another way

    DECLARE @MyTable TABLE

    (

    ID INT,

    RowNumber INT,

    RowText NVARCHAR(20)

    )

    INSERT INTO @MyTable

    SELECT 12, 1, 'ABC-' UNION

    SELECT 12, 2, 'XYZ' UNION

    SELECT 12, 3, 'DEF' UNION

    SELECT 13, 1, 'ABC' UNION

    SELECT 13, 2, 'DEF' UNION

    SELECT...

  • RE: delete duplicate

    Try this

    with cte

    as (select row_number() over(partition by name order by Eff_date desc) as rn

    from mytable)

    delete from cte

    where rn>1

Viewing 14 posts - 1,396 through 1,409 (of 1,409 total)