Viewing 15 posts - 1,081 through 1,095 (of 1,492 total)
I think Jack meant something like:
UPDATE T1
SET Col1 = T2.Col1
    ,Col2 = T2.Col2
FROM Tab1 T1
    JOIN Tab2 T2
        ON T1.Col3 = T2.Col3
            AND T1.Col4 = T2.Col4
    JOIN
    (
        SELECT C.Col3, C.Col4, MAX(C.Col1) AS Col1
        FROM Tab2 C
        GROUP BY...
August 21, 2008 at 7:51 am
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
Viewing 15 posts - 1,081 through 1,095 (of 1,492 total)