Viewing 15 posts - 1,051 through 1,065 (of 1,491 total)
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
This may help you.
-- *** Test Data **
DECLARE @t TABLE
(
    Col1 int NOT NULL
    ,Image1 varchar(10) NULL
    ,Image2 varchar(10) NULL
    ,Image3 varchar(10) NULL
    ,Image4 varchar(10) NULL
    ,Image5 varchar(10) NULL
    ,Image6 varchar(10) NULL
    ,Image7 varchar(10) NULL
    ,Image8 varchar(10) NULL
    ,Image9 varchar(10)...
October 1, 2008 at 5:51 am
I am not sure what you are trying to do, but the following may help.
SELECT C.Customer_ID, C.[Name], C.Country, R.Region
FROM Countries C
    JOIN
    (
        SELECT 1, 'East' UNION ALL
        SELECT 2, 'West' UNION ALL
        SELECT 3,...
September 30, 2008 at 6:01 am
Viewing 15 posts - 1,051 through 1,065 (of 1,491 total)