Viewing 15 posts - 1,081 through 1,095 (of 1,491 total)
The following is a set based solution but, as it involves a triangular join, a cursor will be quicker on large data sets. What constitutes a large data set will...
August 21, 2008 at 5:44 am
SELECT *
    ,T.Depreciation +
    COALESCE
    (
        (
            SELECT SUM(T1.Depreciation)
            FROM YourTable T1
            WHERE T1.[ID] < T.[ID]
        )
        , 0
    ) AS AccummulativeDepreciation
FROM YourTable T
August 19, 2008 at 6:58 am
-- *** Test Data ***
DECLARE @t TABLE
(
    Meter varchar(20) NOT NULL
    ,MeterDate datetime NOT NULL
    ,Value int NOT NULL
)
INSERT INTO @t
SELECT 'Meter_1', '20080101', 10 UNION ALL
SELECT 'Meter_2', '20080101', 50 UNION ALL
SELECT 'Meter_3', '20080101',...
August 15, 2008 at 9:13 am
Or, if you have very complicated and validated expressions (eg QTY needs to be SalesQTY), you could try dynamic SQL. eg:
-- *** Test Data ***
CREATE TABLE #Products
(
    ProductID int NOT NULL
    ,SalesValue...
August 14, 2008 at 6:17 am
I think you should try it or try 9 separate queries if MajorArea is the same for each row in the result set.
August 13, 2008 at 8:38 am
Try using CASE
SELECT N.PersonID
    ,MAX(CASE N.nameTypeID = 7780 THEN [Name] END) AS NameFirst
    ,MAX(CASE N.nameTypeID = 7781 THEN [Name] END) AS NameMiddle
    ,MAX(CASE N.nameTypeID = 7783 THEN [Name] END) AS NamePreferred
    ,MAX(CASE N.nameTypeID =...
August 13, 2008 at 6:02 am
Maybe something like:
WHERE EXISTS
(
    SELECT *
    FROM
    (
        SELECT ManagingGroupID, 'tb_Core' FROM tb_CoreManGroup WHERE CoreID = @id
        UNION ALL
        SELECT ManagingGroupID, 'tb_Event' FROM tb_EventManGroup WHERE EventID = @id
        UNION ALL
        SELECT ManagingGroupID, 'tb_FocusGroup' FROM tb_FocusGroupManGroup WHERE FocusGroupID =...
August 13, 2008 at 5:33 am
In SQL2005, a CTE could be used.
A temp table can be avoided, in SQL2000, by doing something horrible like:
SELECT T1.[ID], T1.[NAME] AS Name1, T2.[NAME] AS Name2
FROM YourTable T1
    JOIN
    (
        SELECT T11.[ID], MIN(T11.SUB)...
August 12, 2008 at 6:13 am
I would be inclined to use a function. Something like:
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE FUNCTION dbo.GetAssetNames
(
    @policy_number varchar(20) -- or whatever the datatype of policy_number is.
)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @AssetNames varchar(8000)
    SET @AssetNames =...
August 8, 2008 at 4:10 am
Try using a transaction and UPDLOCK.
Here is an outline - you will need to add error handling etc.
DECLARE @NextInvoiceNumber int
BEGIN TRANSACTION
SET @NextInvoiceNumber =
(
    SELECT NextInvoiceNumber
    FROM InvoiceNumbers WITH (UPDLOCK)
    WHERE Company =...
April 10, 2008 at 3:02 am
Try something like:
SELECT *
FROM ord1 H
WHERE EXISTS
(
    SELECT NULL
    FROM PRCR C
    WHERE C.customer = H.customer
        AND C.division = H.division
        AND C.ack_by = 'P'
        AND C.sku_upc <> 'N'
        AND C.ovnd_key IN (H.ovnd_key, '')
)
March 17, 2008 at 11:42 am
Maybe you could try variations on:
SELECT T3.IDK, T3.C4
FROM T3
WHERE EXISTS
    (
        SELECT *
        FROM T1
        WHERE T1.C1 = T3.IDK
            AND EXISTS
            (
                SELECT *
                FROM SelField S
                WHERE S.val = T1.C1
            )
    )
    AND EXISTS
    (
        SELECT T2.IDK
        FROM T2
        WHERE T2.IDK = T3.IDK
        GROUP BY T2.IDK
        HAVING...
February 14, 2008 at 3:12 am
I think these are the ODBC functions which TSQL seems to be able to process.
February 14, 2008 at 2:57 am
DECLARE @s varchar(255)
SET @s = 'Mark_m_andrew_levey_caa12002'
SELECT REVERSE(LEFT(REVERSE(@s), CHARINDEX('a', REVERSE(@s)) - 1))
February 12, 2008 at 7:20 am
Viewing 15 posts - 1,081 through 1,095 (of 1,491 total)