Viewing 15 posts - 1,051 through 1,065 (of 1,493 total)
You could try checking your SP for #temp tables and table variables. If you are using these then adding COLLATE DATABASE_DEFAULT to the definitions of string columns may solved your...
November 12, 2008 at 4:01 am
Maybe:
DECLARE @TempTbl TABLE
(
    Col1 char(7)
    ,Col2 char(7)
    ,Col3 char(7)
    ,Case_Col bigint
)
INSERT INTO @TempTbl
SELECT Col1, Col2, Col3, MIN(Case_Col)
FROM tbleActualDBTable
WHERE Case_Col > 0
GROUP BY Col1, Col2, Col3
HAVING COUNT(Case_Col) > 1
otherwise post some sample data with...
November 12, 2008 at 2:58 am
Use an OUTER JOIN and use ISNULL or COALESCE on the outer quantity. eg
SELECT T1.location, T1.number, T1.quantity - ISNULL(T2.quantity, 0) AS quantity
FROM
(
    SELECT T11.location, T11.number, SUM(T11.quantity) AS quantity
    FROM Table1 T11
    GROUP BY...
November 11, 2008 at 6:29 am
Use sp_executesql with an OUTPUT parameter.
DECLARE @word sysname
EXEC sp_executesql
    N'SELECT TOP 1 @DynamicWord = [name] FROM sysobjects'
    ,N'@DynamicWord sysname OUTPUT'
    ,@word OUTPUT
SELECT @word
November 11, 2008 at 5:24 am
SELECT T1.location, T1.number, T1.quantity - T2.quantity AS quantity
FROM
(
    SELECT T11.location, T11.number, SUM(T11.quantity) AS quantity
    FROM Table1 T11
    GROUP BY T11.location, T11.number
) T1
    JOIN
    (
        SELECT T21.location, T21.number, SUM(T21.quantity) AS quantity
        FROM Table2 T21
        WHERE T21.param1 > 0
            AND T21.param2...
November 11, 2008 at 4:12 am
You will have to do a COUNT. Something like:
SELECT DISTINCT
    C.CategoryID
    ,P.*
    ,F.FeatureValueID
FROM tblProduct P
    JOIN tblProductFeatureValue F
        ON P.ProductID = F.ProductID
    JOIN tblProductCategory C
        ON P.ProductID = C.ProductID
    JOIN
    (
        SELECT F1.ProductID
        FROM tblProductFeatureValue F1
        WHERE F1.FeatureValueID IN
        (
            SELECT X1.item.value('@value','int')
            FROM @XMLDoc.nodes('/root/item') X1(Item)
        )
        GROUP...
November 11, 2008 at 3:09 am
SELECT empid
    ,ISNULL(SUM(CASE WHEN activitytype = 'Billable' THEN noOfHrs END), 0) AS Billable
    ,ISNULL(SUM(CASE WHEN activitytype = 'NonBillable' THEN noOfHrs END), 0) AS NonBillable
    ,SUM(noOfHrs) AS Total
FROM testTiemsheet
GROUP BY empid
November 6, 2008 at 8:15 am
I think you would be better off doing the pivot in the front end.
If you really want to do it in the db I suspect you will have to resort...
November 5, 2008 at 6:14 am
Sure, just use the same idea. Something like:
SELECT O.[ID]
    ,O.Location
    ,SUM(CASE WHEN O.[Date] >= M.ThisMonth THEN O.Quanity END) AS TotalQuantityThisMonth
    ,SUM(CASE WHEN O.[Date] >= M.ThisMonth THEN O.Cost END) AS TotalCostThisMonth
    ,SUM(CASE WHEN O.[Date] >=...
October 31, 2008 at 5:47 am
A derived table is sometimes known as an inline view.
The derived table below has an alias of D:
SELECT O1.[ID]
    ,O1.Location
    ,SUM(O1.Quanity) AS TotalQuantity
    ,SUM(O1.Cost) AS TotalCost
    ,D.[Date] AS LatestSalesDate
FROM OrderData O1
    LEFT JOIN
    (
        SELECT O2.[ID], O2.Location,...
October 31, 2008 at 4:56 am
You may be better off forgetting about the trigger and using a computed column.
Something like:
ALTER TABLE humanresources.employee
ADD AgeWhenHired
AS YEAR(hiredate) - YEAR(birthdate)
    - CASE
        WHEN MONTH(hiredate) > MONTH(birthdate)
        THEN 0
        WHEN MONTH(hiredate) = MONTH(birthdate)
            AND DAY(hiredate)...
October 23, 2008 at 8:10 am
As you only want to count non-null values, use DATALENGTH to convert image to int.
eg WHEN 1 THEN Image1 becomes WHEN 1 THEN DATALENGTH(Image1) etc
[Edit]
or you could alter saby's CASE...
October 3, 2008 at 5:08 am
Kenneth Wilhelmsson (10/2/2008)
What you're essentially proposing here, is that the model doesn't count.
Whenever you're using 'select *' instead...
October 2, 2008 at 10:04 am
I do not have time to look at your code, but based on your latest explaination you could try something like:
DECLARE @TermYear char(4)
    ,@Term varchar(6)
SELECT @TermYear = '2008'
    ,@Term = 'Fall'
UPDATE academic
SET...
October 2, 2008 at 9:28 am
What you are attempting is far from clear. Maybe something like this:
DECLARE @YearStart char(4)
    ,@TermStart varchar(6)
    ,@YearEnd char(4)
    ,@TermEnd varchar(6)
SELECT @YearStart = '2006'
    ,@TermStart = 'Fall'
    ,@YearEnd = '2008'
    ,@TermEnd = 'Fall'
UPDATE academic
SET status = 'N'
WHERE...
October 2, 2008 at 2:55 am
Viewing 15 posts - 1,051 through 1,065 (of 1,493 total)