Viewing 15 posts - 1,096 through 1,110 (of 1,491 total)
SELECT TOP 30
    S.Prod
    ,S.Acct
    ,S.TC
    ,CASE N.N
        WHEN 1 THEN S.SubmitRep1
        WHEN 2 THEN S.SubmitRep2
        ELSE S.SubmitRep3
    END AS Rep
    ,CASE N.N
        WHEN 1 THEN S.Percent1
        WHEN 2 THEN S.Percent2
        ELSE S.Percent3
    END AS Percentage
FROM dbo.tbl_CYProcessedSales S
    CROSS JOIN -- make 3...
February 5, 2008 at 10:26 am
SELECT U.*
FROM Usr_AmtDetails U
    JOIN
    (
        SELECT X.AcctID, MAX(X.RenewDate) AS RenewDate
        FROM Usr_AmtDetails X
        GROUP BY X.AcctID
    ) D
        ON U.AcctID = D.AcctID
            AND U.RenewDate = D.RenewDate
February 5, 2008 at 7:53 am
I do not see any problem.
What logically happens for a left join is:
1. An inner join is done.
2. Any rows in the left table not in the result are added.
3....
January 30, 2008 at 10:40 am
If you check my Rolling Total query, you will see I am grouping by M2.userhistoryid
January 29, 2008 at 8:02 am
Umm.... You obviously did not try my original query as no rows would be returned.
In future, please read the following when posting:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
As you seem to want a rolling total, try...
January 29, 2008 at 2:48 am
You need to get the required userid's and then use them with a derived table or a subquery.
Here is an example of a derived table:
SELECT M.*
FROM moviesviewed M
    JOIN
    (
        SELECT M1.userid
        FROM moviesviewed...
January 28, 2008 at 10:59 am
Try getting rid of the brackets:
SELECT P.user_permissions_id
    ,U.[user_name] AS [User]
    ,UA.[user_id]
    ,A.abreviation AS Account
    ,UA.account_id
    ,P.[read] AS [Read]
    ,P.write AS [Write]
FROM q_user_account UA
    JOIN t_users U
        ON UA.[user_id] = U.[user_id]
            AND U.active = 1
    JOIN t_account A
        ON UA.account_id = A.account_id
    LEFT...
January 24, 2008 at 3:33 am
Dale,
My apologies – I did not visualize the joins correctly. (More coffee required!)
I do not think HAVING can be used easily in this case.
Ken
January 17, 2008 at 3:38 am
BusinessObjectInstanceId = @BusinessObjectInstanceId
OR
@BusinessObjectInstanceId = ''
Also, as BusinessObjectInstanceId is an integer, the implicit type casting will not help.
Try OR @BusinessObjectInstanceId = 0
January 16, 2008 at 9:29 am
To be fair, DLINQ, with it's dynamic SQL, is good for quickly creating prototypes. Also, if you decide to cache some data as XML, it saves having to know too...
January 16, 2008 at 4:44 am
I started the query with the PO_MX table as you were looking for PONumbers - I find this easier but you can start with any table you like.
You should also...
January 16, 2008 at 4:07 am
In addition, derived tables, also known as inline views, could be used instead of HAVING:
SELECT PO_MX.PONumber
FROM PO_MX
    JOIN
    (
        SELECT EJ.PONumber, SUM(EJ.Amount) AS Amount
        FROM EJ
        GROUP BY EJ.PONumber
    ) D1
    ON PO_MX.PONumber = D1.PONumber
    JOIN
    (
        SELECT PO_DX.PONumber, SUM(PO_DX.QuantityOrdered...
January 15, 2008 at 3:21 am
Without DDL and test data it is difficult to know what you want.
Maybe you need to use the ROW_NUMBER() function like:
SELECT *
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY S.sessie_id ORDER BY S.created_date)...
January 9, 2008 at 10:10 am
1. Create a #temp table with an IDENTITY column
2. Insert your data into the #temp table
3. Join the #temp table to a derived table containing the MINIMUM(ID) for each tans_code...
January 9, 2008 at 7:24 am
In SQL2000 you will need to use a #temp table:
January 9, 2008 at 7:06 am
Viewing 15 posts - 1,096 through 1,110 (of 1,491 total)