duplicates in resultset

  •  

    I was just wondering, does anyone know what usually causes duplicate data to appear in a resultset?  I am getting rows of data that are duplicates even though there are no duplicates in the database.

     

    Thank you!

     

     

  • The same still applies.

    Post :

    - Table definitions

    - sample data

    - wrong resultset

    - expected resultset

  • Agreed.  My 'guess' would be it is in the Joining of tables, though... 

     

    I wasn't born stupid - I had to study.

  • Mine too since she didn't use the correct join syntaxe everytime she posted but that's another story.

  • Thanx RGR, I thought posting all the stuff all the time would get annoying...but if you like it, I'll give it to ya.

     

     

  • It's like asking a mechanic to fix your flat tire without bringing the car in. That's just not gonna work.

  • well, this is a big azz car...like a 77 caddy.

    Seriously it's huge, check out this rediculous query:

    SELECT DISTINCT TOP 100 PERCENT UdeptManager, Uname, IOofficeName AS Office,

                              (SELECT     SUM(Workable)

                                FROM          studentcounts F1

                                WHERE      F1.WRdeptWorker = Uname) AS Workable,

                              (SELECT     SUM(Unworkable)

                                FROM          studentcounts F2

                                WHERE      F2.WRdeptWorker = Uname) AS Unworkable,

                              (SELECT     COUNT(BMid)

                                FROM          billmaster, currentbillingperiod

                                WHERE      BMdeletedDate IS NULL AND BMtransactionDate BETWEEN BPstartDate AND BPendDate AND BMdeptWorker = Uname) AS Bills,

                              (SELECT     AVG(BMtransactionAmount)

                                FROM          billmaster, currentbillingperiod

                                WHERE      BMdeletedDate IS NULL AND BMtransactionDate BETWEEN BPstartDate AND BPendDate AND BMdeptWorker = Uname) AS AvgBill,

                              (SELECT     SUM(BMtransactionAmount)

                                FROM          billmaster, currentbillingperiod

                                WHERE      BMdeletedDate IS NULL AND BMtransactionDate BETWEEN BPstartDate AND BPendDate AND BMdeptWorker = Uname) AS Dollars,

                              (SELECT     TOP 1 GOdayGoalAmount

                                FROM          goals, currentbillingperiod

                                WHERE      GOuserName = Uname AND GOyear = YEAR(GetDate()) AND GOmonth = BPperiod

                                ORDER BY GOenteredDate DESC) AS Goal,

                              (SELECT     CONVERT(float, COUNT(BMid)) / CONVERT(float, BPbusDaysToDate)

                                FROM          billmaster, currentbillingperiod

                                WHERE      BMdeletedDate IS NULL AND BMtransactionDate BETWEEN BPstartDate AND BPendDate AND BMdeptWorker = Uname

                                GROUP BY BPbusDaysToDate) AS AvgPerDay,

                              (SELECT     COUNT(PLid)

                                FROM          policylink, webrequest WR4, currentbillingperiod

                                WHERE      PLenteredDate BETWEEN BPstartDate AND BPendDate AND PLcurrentStatus >= 0 AND PLwebrequestID = WR4.WRid AND

                                                       WR4.WRdeptWorker = Uname) AS NewRecMonth,

                              (SELECT     COUNT(PLid)

                                FROM          policylink, webrequest WR5, currentbillingperiod

                                WHERE      DATEPART(month, PLenteredDate) = DATEPART(month, GetDate()) AND DATEPART(year, PLenteredDate) = DATEPART(year, GetDate()) AND

                                                        DATEPART(day, PLenteredDate) = DATEPART(day, GetDate()) AND PLcurrentStatus >= 0 AND PLwebrequestID = WR5.WRid AND

                                                       WR5.WRdeptWorker = Uname) AS NewRecDay

    FROM         dbo.users

    LEFT JOIN webrequest ON WRdeptWorker = Uname

    RIGHT JOIN internaloffice ON WRinternalOfficeId=IOid

    WHERE Uname IS NOT NULL

    ORDER BY UdeptManager, Uname

     

     

  • This is still all missing.

    - Table definitions

    - sample data

    - wrong resultset

    - expected resultset

    Help us help you

    Generate insert statements

    Also expect to have to normalize the table design after you posted it.

  • Please follow Remi's links. 

    Here is another way to code this, plus some comments and questions that should be resolved within this select: 

    SELECT UdeptManager, Uname, IOofficeName AS Office,

           SUM( Workable) AS Workable,

           SUM( Unworkable) AS Unworkable,

           (SELECT COUNT( BMid)

            FROM billmaster, currentbillingperiod -- tables are not JOINED

            WHERE BMdeletedDate IS NULL

              AND BMtransactionDate BETWEEN BPstartDate AND BPendDate

              AND BMdeptWorker = Uname) AS Bills,

            (SELECT AVG(BMtransactionAmount)

             FROM  billmaster, currentbillingperiod -- tables are not JOINED

             WHERE  BMdeletedDate IS NULL

               AND BMtransactionDate BETWEEN BPstartDate AND BPendDate

               AND BMdeptWorker = Uname) AS AvgBill,

            (SELECT SUM(BMtransactionAmount)

             FROM billmaster, currentbillingperiod -- tables are not JOINED

             WHERE BMdeletedDate IS NULL

               AND BMtransactionDate BETWEEN BPstartDate

               AND BPendDate AND BMdeptWorker = Uname) AS Dollars,

            (SELECT TOP 1 GOdayGoalAmount -- why only TOP 1 ?

             FROM  goals, currentbillingperiod -- tables are not JOINED

             WHERE GOuserName = Uname

               AND GOyear = year( GETDATE())

               AND GOmonth = BPperiod -- maybe this is the JOIN ?

             ORDER BY GOenteredDate DESC) AS Goal,

            (SELECT CONVERT(float, COUNT(BMid)) / CONVERT( float, BPbusDaysToDate) -- float is a poor datatype choice

             FROM billmaster, currentbillingperiod -- tables are not JOINED

             WHERE BMdeletedDate IS NULL

               AND BMtransactionDate BETWEEN BPstartDate AND BPendDate

               AND BMdeptWorker = Uname

             GROUP BY BPbusDaysToDate) AS AvgPerDay,

            (SELECT COUNT( PLid)

             FROM policylink, currentbillingperiod -- tables are not JOINED

                INNER JOIN webrequest WR4 ON( PLwebrequestID = WR4.WRid) -- JOIN your tables

             WHERE PLenteredDate BETWEEN BPstartDate AND BPendDate

               AND PLcurrentStatus >= 0

               AND WR4.WRdeptWorker = Uname) AS NewRecMonth,

            (SELECT COUNT( PLid)

             FROM policylink, currentbillingperiod  -- tables are not JOINED

                INNER JOIN webrequest WR5 ON( PLwebrequestID = WR5.WRid) -- JOIN your tables

             WHERE DATEPART( month, PLenteredDate) = DATEPART( month, GETDATE())

               AND DATEPART( year, PLenteredDate) = DATEPART( year, GATEDATE())

               AND DATEPART( day, PLenteredDate) = DATEPART( day, GETDATE())

               AND PLcurrentStatus >= 0

               AND WR5.WRdeptWorker = Uname) AS NewRecDay

    FROM dbo.users

       LEFT JOIN webrequest ON( WRdeptWorker = Uname)

       RIGHT JOIN internaloffice ON( WRinternalOfficeId = IOid) -- by choosing a RIGHT JOIN, this is now your main table

                                                                -- to draw your data; is that what you want?  In other

                                                                -- words, everything from this table whether it is in the

                                                                -- the users table or not...

    WHERE Uname IS NOT NULL

    GROUP BY UdeptManager, Uname, IOofficeName

    ORDER BY UdeptManager, Uname

    I wasn't born stupid - I had to study.

  • This still can be optimized a lot... the first 3 subqueries could be turned in a single derived table, then I should probabely read the rest of this monster .

  • Well, I can't believe you guys want to read all this, but here's my tables/views.

    I'm still working out how to generate sample data without screwing up the data that's already in the DB.

    Please note that I did not create any of these tables and/or views.

    QUERY:

    SELECT DISTINCT TOP 100 PERCENT UdeptManager, Uname, IOofficeName AS Office,

                              (SELECT     SUM(Workable)

                                FROM          studentcounts F1

                                WHERE      F1.WRdeptWorker = Uname) AS Workable,

                              (SELECT     SUM(Unworkable)

                                FROM          studentcounts F2

                                WHERE      F2.WRdeptWorker = Uname) AS Unworkable,

                              (SELECT     COUNT(BMid)

                                FROM          billmaster, currentbillingperiod

                                WHERE      BMdeletedDate IS NULL AND BMtransactionDate BETWEEN BPstartDate AND BPendDate AND BMdeptWorker = Uname) AS Bills,

                              (SELECT     AVG(BMtransactionAmount)

                                FROM          billmaster, currentbillingperiod

                                WHERE      BMdeletedDate IS NULL AND BMtransactionDate BETWEEN BPstartDate AND BPendDate AND BMdeptWorker = Uname) AS AvgBill,

                              (SELECT     SUM(BMtransactionAmount)

                                FROM          billmaster, currentbillingperiod

                                WHERE      BMdeletedDate IS NULL AND BMtransactionDate BETWEEN BPstartDate AND BPendDate AND BMdeptWorker = Uname) AS Dollars,

                              (SELECT     TOP 1 GOdayGoalAmount

                                FROM          goals, currentbillingperiod

                                WHERE      GOuserName = Uname AND GOyear = YEAR(GetDate()) AND GOmonth = BPperiod

                                ORDER BY GOenteredDate DESC) AS Goal,

                              (SELECT     CONVERT(float, COUNT(BMid)) / CONVERT(float, BPbusDaysToDate)

                                FROM          billmaster, currentbillingperiod

                                WHERE      BMdeletedDate IS NULL AND BMtransactionDate BETWEEN BPstartDate AND BPendDate AND BMdeptWorker = Uname

                                GROUP BY BPbusDaysToDate) AS AvgPerDay,

                              (SELECT     COUNT(PLid)

                                FROM          policylink, webrequest WR4, currentbillingperiod

                                WHERE      PLenteredDate BETWEEN BPstartDate AND BPendDate AND PLcurrentStatus >= 0 AND PLwebrequestID = WR4.WRid AND

                                                       WR4.WRdeptWorker = Uname) AS NewRecMonth,

                              (SELECT     COUNT(PLid)

                                FROM          policylink, webrequest WR5, currentbillingperiod

                                WHERE      DATEPART(month, PLenteredDate) = DATEPART(month, GetDate()) AND DATEPART(year, PLenteredDate) = DATEPART(year, GetDate()) AND

                                                        DATEPART(day, PLenteredDate) = DATEPART(day, GetDate()) AND PLcurrentStatus >= 0 AND PLwebrequestID = WR5.WRid AND

                                                       WR5.WRdeptWorker = Uname) AS NewRecDay

    FROM         dbo.users

    LEFT JOIN internaloffice ON UinternalOfficeId=IOid

    ORDER BY UdeptManager, Uname

     

    studentcounts(view):

    SELECT     dbo.webrequest.WRdeptWorker,

                              (SELECT     COUNT(DISTINCT PLwebrequestID)

                                FROM          policylink

                                WHERE      PLcurrentStatus BETWEEN 0 AND 89 AND PLwebrequestID = WRid) AS FileCount,

                              (SELECT     COUNT(PLwebrequestID)

                                FROM          policylink

                                WHERE      PLcurrentStatus BETWEEN 0 AND 89 AND PLwebrequestID = WRid) AS policyCount,

                              (SELECT     COUNT(PLwebrequestID)

                                FROM          policylink

                                WHERE      PLcurrentStatus BETWEEN 0 AND 89 AND PLwebrequestID = WRid AND PLcurrentStatus IN (11, 30, 31, 32, 35, 41, 51, 53, 55, 75, 80, 82,

                                                       86)) AS Workable,

                              (SELECT     COUNT(PLwebrequestID)

                                FROM          policylink

                                WHERE      PLcurrentStatus BETWEEN 0 AND 89 AND PLwebrequestID = WRid AND PLcurrentStatus IN (0, 1, 2, 3, 5, 8, 21, 23, 70, 71, 72, 73, 74, 84,

                                                       88, 89)) AS Unworkable

    FROM         dbo.webrequest INNER JOIN

                          dbo.users ON dbo.webrequest.WRdeptWorker = dbo.users.Uname

    WHERE     (dbo.users.UworkgroupTypeID <> 'TRUTXXX') AND (dbo.webrequest.WRid NOT IN

                              (SELECT     WRid

                                FROM          samplerequests)) AND (dbo.webrequest.WRdeptWorker <> '%')

    currentbillingperiod(view):

    SELECT     *

    FROM         dbo.billingperiods

    WHERE     (GETDATE() BETWEEN BPstartDate AND BPendDate)

    POLICYLINK(table):

    CREATE TABLE [policylink] (

     [PLid] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

     [PLworkRequestID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [PLmedicalpolicyID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [PLrecordTypeID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [PLrushTypeID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [PLrecordInstructions] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [PLcomments] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [PLcaseWorker] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [PLattentionLine] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [PLcurrentStatus] [int] NULL ,

     [PLstatusUpdated] [datetime] NULL ,

     [PLenteredBy] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [PLenteredDate] [datetime] NULL ,

     [PLmodifiedBy] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [PLmodifiedDate] [datetime] NULL ,

     [PLdueDate] [datetime] NULL ,

     [PLtat] [int] NULL CONSTRAINT [DF_policylink_PLtat] DEFAULT (1),

     [PLunworkable] [int] NULL CONSTRAINT [DF_policylink_PLunworkable] DEFAULT (0),

     [PLbillingExceptions] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_policylink_PLbillingExceptions] DEFAULT (''),

     CONSTRAINT [PK_policylink] PRIMARY KEY  CLUSTERED

     (

      [PLid]

    &nbsp  ON [PRIMARY]

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

     

    USERS(table):

    CREATE TABLE [users] (

     [Uid] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

     [UinternalOfficeID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [UworkgroupTypeID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [Uname] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

     [UpID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [UnameFirst] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [UnameLast] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [Utitle] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [Uphone] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [Ufax] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [Uemail] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [UdeptManager] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [UenteredBy] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [UenteredDate] [datetime] NULL ,

     [UmodifiedBy] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [UmodifiedDate] [datetime] NULL ,

     [Upassword] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [Upermissions] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [Uhash] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [ULocation] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     CONSTRAINT [PK_users] PRIMARY KEY  CLUSTERED

     (

      [Uname]

    &nbsp  ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    WEBREQUEST(table):

    CREATE TABLE [webrequest] (

     [WRid] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

     [WRreceivedDate] [datetime] NULL ,

     [WRattorneyFileID] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [WRdateOfAccident] [datetime] NULL ,

     [WRrequestSubjectID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [WRinsuredName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [WRrequestGeneratorID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [WRfileContactID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [WRbillToID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [WRbillToFileID] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [WRlegaldeptID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [WRcorrespTo] [int] NULL ,

     [WRenteredBy] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [WRenteredDate] [datetime] NULL ,

     [WRmodifiedBy] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [WRmodifiedDate] [datetime] NULL ,

     [WRdeptWorker] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [WRdeptManager] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [WRarchivedBy] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [WRarchivedDate] [datetime] NULL ,

     [WRinternalOfficeID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     CONSTRAINT [PK_webrequest] PRIMARY KEY  CLUSTERED

     (

      [WRid]

    &nbsp  ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    INTERNALOFFICE(table):

    CREATE TABLE [internaloffice] (

     [IOid] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

     [IOofficeName] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [IOofficeAbbrev] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [IOcontactFirstName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [IOcontactLastName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [IOaddress1] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [IOaddress2] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [IOcity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [IOstate] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [IOzip] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [IOzip4] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [IOphone] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [IOfax] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [IOmas90locationID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [IOdivision] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [IOlocation] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     CONSTRAINT [PK_internaloffice] PRIMARY KEY  CLUSTERED

     (

      [IOid]

    &nbsp  ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    BILLMASTER(table):

    CREATE TABLE [billmaster] (

     [BMid] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

     [BMtransactionDate] [datetime] NULL ,

     [BMwebrequestID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [BMbillableEntityID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [BMtransactionType] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [BMtransactionAmount] [float] NULL ,

     [BMadditionalInfo] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [BMtransactionStatus] [int] NULL ,

     [BMdeletedDate] [datetime] NULL ,

     [BMinvoiceNumber] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [BMinvoiceDate] [datetime] NULL ,

     [BMdeptWorker] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [BMenteredBy] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [BMenteredDate] [datetime] NULL ,

     [BMmodifiedBy] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

     [BMmodifiedDate] [datetime] NULL ,

     CONSTRAINT [PK_billmaster] PRIMARY KEY  CLUSTERED

     (

      [BMid]

    &nbsp  ON [PRIMARY]

    ) ON [PRIMARY]

    GO

     

     

     

  • We still can't do squat without the sample data and required output. Do you want help or not???

    Generate insert statements

  • my guess

    DECLARE @today datetime

    SET @today = DATEADD(day,DATEDIFF(day,0,GETDATE()),0)

    SELECT u.UdeptManager, u.Uname, o.IOofficeName AS [Office],

    ISNULL(sc.Workable,0) AS [Workable], ISNULL(sc.Unworkable,0) AS [Unworkable]

    ISNULL(b.Bills,0) AS [Bills], ISNULL(b.AvgBill,0) AS [AvgBill], ISNULL(b.Dollars,0) AS [Dollars]

    (CAST(b.Bills as decimal(18,4) / CAST(b.BPbusDaysToDate as decimal(18,4)) AS [AvgPerDay],

    ISNULL(r.NewRecMonth,0) AS [NewRecMonth], ISNULL(r.NewRecDay,0) AS [NewRecDay]

    (SELECT TOP 1 g.GOdayGoalAmount

        FROM goals g

        INNER JOIN currentbillingperiod bp

        WHERE g.GOuserName = u.Uname AND g.GOyear = YEAR(GetDate()) AND g.GOmonth = bp.BPperiod

        ORDER BY g.GOenteredDate DESC) AS Goal,

    LEFT OUTER JOIN

        (SELECT F1.WRdeptWorker,

        SUM(F1.Workable) AS [Workable],

        SUM(F1.Unworkable) AS [Unworkable]

        FROM studentcounts F1

        GROUP BY F1.WRdeptWorker) sc ON sc.WRdeptWorker = u.Uname

    LEFT OUTER JOIN

        (SELECT bm.BMdeptWorker, p.BPbusDaysToDate,

        COUNT(BMid) AS [Bills],

        AVG(BMtransactionAmount) AS [AvgBill],

        SUM(BMtransactionAmount) AS [Dollars]

        FROM billmaster bm

            INNER JOIN currentbillingperiod p

            ON bm.BMtransactionDate BETWEEN p.BPstartDate AND p.BPendDate

        WHERE bm.BMdeletedDate IS NULL

        GROUP BY bm.BMdeptWorker, p.BPbusDaysToDate) b ON b.BMdeptWorker = u.Uname

    LEFT OUTER JOIN

        (SELECT wr.WRdeptWorker,

        COUNT(PLid) AS [NewRecMonth],

        SUM(CASE WHEN pl.PLenteredDate = @today THEN 1 ELSE 0 END) AS [NewRecDay]

        FROM policylink pl

        INNER JOIN webrequest wr ON wr.WRid = pl.PLwebrequestID

        INNER JOIN currentbillingperiod cbp ON pl.PLenteredDate BETWEEN cbp.BPstartDate AND cbp.BPendDate

        WHERE pl.PLcurrentStatus >= 0

        GROUP BY wr.WRdeptWorker) r ON r.WRdeptWorker = u.Uname

    FROM dbo.users u

    INNER JOIN webrequest wr ON wr.WRdeptWorker = u.Uname

    INNER JOIN internaloffice o ON wr.WRinternalOfficeId=o.IOid

    GROUP BY u.UdeptManager, u.Uname, o.IOofficeName,

    sc.Workable, sc.Unworkable,

    b.Bills, b.AvgBill b.Dollars, b.BPbusDaysToDate

    r.NewRecMonth, r.NewRecDay

    ORDER BY u.UdeptManager, u.Uname

    oops, sorry had to edit

    edit again, still not right, will not work forgot about internaloffice problem

    mmm, added GROUP BY, ran out of time

    Far away is close at hand in the images of elsewhere.
    Anon.

  • "edit again, still not right, will not work forgot about internaloffice problem.."

    far as I can tell the only thing wrong with "internal office" is "unworkable deptworker"..







    **ASCII stupid question, get a stupid ANSI !!!**

  • I think the duplicate problem stems from the users,webrequest,internaloffice link.

    quoteunworkable deptworker

     

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 15 posts - 1 through 15 (of 32 total)

You must be logged in to reply to this topic. Login to reply