Forum Replies Created

Viewing 15 posts - 1 through 15 (of 21 total)

  • RE: Optimising join with a CASE statement

    While I definitely agree with Scott and Gail that the table design here does not sound optimal at all; if changing the tables is really not an option, what about...

  • RE: Compare Rows in the same Table

    Is this what you're after?

    WITH cte (PO, LINE, DUE_DATE, UPDATED_DTE, rn)

    AS

    (

    SELECT

    PO

    , LINE

    , DUE_DATE

    , UPDATED_DTE

    , ROW_NUMBER() OVER (PARTITION BY PO, LINE ORDER BY UPDATED_DTE DESC) AS [rn]

    FROM PO_LINE_CHANGE

    )

    SELECT

    cte1.PO

    , cte1.LINE

    , cte1.DUE_DATE

    ,...

  • RE: Compare Rows in the same Table

    It would be nice if you could have included more information about how your environment works. Specifically if your audit table contains only historical values or if it also...

  • RE: simple self join

    Something like this?

    DECLARE @Temp TABLE

    (

    EmailId varchar(25) NOT NULL

    , EmpName varchar(25) NOT NULL

    , Team varchar(25) NOT NULL

    , ID int NOT NULL

    , Reports int NOT NULL

    );

    DECLARE @Temp2 TABLE

    (

    EmpName varchar(25) NOT NULL

    , Reports...

  • RE: Generating Hex on Insert

    I have to agree with what autoexcrement said earlier, why not get rid of the trigger altogether and use a persisted computed column?

    USE tempdb

    GO

    CREATE TABLE [dbo].[scancodeX](

    [scancode_id] [int] IDENTITY(1,1) NOT NULL

    ,...

  • RE: Forecasting Problem

    % is the Modulo operator.

    http://msdn.microsoft.com/en-us/library/ms190279(v=sql.110).aspx

    Essentially it returns the remainder of a division operation.

    So 7 % 5 would = 2, since 7 divided by 5 is 1 remainder 2.

    5...

  • RE: Adding count to query without duplicating original select query

    I think this may do what you want?

    SELECTCOUNT(*) OVER (PARTITION BY _bvSerialMasterFull.SerialNumber) AS SerialCount,

    _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,

    ...

  • RE: Forecasting Problem

    Try this out. Updated for SQL 2012 as well.

    create table #temp(priority int,project varchar(255),estimatehrs int);

    insert #temp (priority,project,estimatehrs) values (1,'Project 1',2);

    insert #temp (priority,project,estimatehrs) values (2,'Project 2',2);

    insert #temp (priority,project,estimatehrs) values (3,'Project 3',1);

    insert...

  • RE: Forecasting Problem

    Since you're on SQL 2012, you can use some of the new partitioning functionality to do this. Take a look at this blog post: http://ole.michelsen.dk/blog/calculate-a-running-total-in-sql-server-2012/

    I only have...

  • RE: Difference between two date/times and flag each hour in between

    Can you include table structure for your data describing when cars begin and end their service period? How those tables are laid out is going to have a pretty...

  • RE: ORDER BY, Bad form?

    Sean Lange (10/1/2013)


    SQL_FS (9/30/2013)


    SELECT string, category

    FROM @SampleData

    ORDER BY

    CASE WHEN category % 7 = 0 THEN 1

    WHEN category % 3 = 0 THEN 2

    ELSE 3

    END ASC

    , string ASC

    This is NOT ordering...

  • RE: ORDER BY, Bad form?

    I think people are misunderstanding, allow me to clarify. I'm not trying to ask whether or not using ORDER BY [Column Position] is bad form or not. I...

  • RE: Date Query Issue

    What about this?

    SELECT

    SeqNo

    , coalesce(Data.startYear, src.startYear) AS [startYear]

    , coalesce(data.endYear, src.endYear) AS [endYear]

    , coalesce(data.Number, src.Number) AS [Number]

    , src.name

    FROM @tblJobHist_src src

    OUTER APPLY

    (

    SELECT

    min(startYear) AS [startYear]

    , max(endYear) AS [endYear]

    , sum(Number) AS [Number]

    FROM @tblJobHist_src src2

    WHERE

    src2.name =...

  • RE: update output into

    You may have figured this out already, but in your last example the reason they are able to use p.Name in the output clause is because the table that p...

  • RE: Summing Invoice Amounts ONCE and MULTIPLE Invoice Payments in same T-SQL Statement

    Will this do what you want? (Place directly before your rollback statement)

    SELECT

    Inv.Currency

    , SUM(Inv.Amount) AS [GrandTotal]

    , SUM(coalesce(Pay.Paid, 0)) AS [Payments]

    FROM #INV Inv

    LEFT OUTER JOIN

    (

    SELECT

    InvNo

    , SUM(Paid) AS [Paid]

    FROM #Pay

    GROUP BY

    InvNo

    ) Pay...

Viewing 15 posts - 1 through 15 (of 21 total)