duplicates in resultset

• Jurushia

  SSC Eights!

  Points: 947

  More actions

  October 24, 2005 at 1:14 pm

  #89707

   

  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!

   

   

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  October 24, 2005 at 1:15 pm

  #599645

  The same still applies.

  Post :

  - Table definitions

  - sample data

  - wrong resultset

  - expected resultset

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  October 24, 2005 at 1:17 pm

  #599646

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

   

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

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  October 24, 2005 at 1:19 pm

  #599647

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

• Jurushia

  SSC Eights!

  Points: 947

  More actions

  October 24, 2005 at 1:30 pm

  #599651

  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.

   

   

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  October 24, 2005 at 1:34 pm

  #599653

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

• Jurushia

  SSC Eights!

  Points: 947

  More actions

  October 24, 2005 at 2:19 pm

  #599664

  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

   

   

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  October 24, 2005 at 2:24 pm

  #599666

  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.

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  October 24, 2005 at 6:42 pm

  #599698

  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.

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  October 25, 2005 at 6:40 am

  #599767

  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 .

• Jurushia

  SSC Eights!

  Points: 947

  More actions

  October 25, 2005 at 7:32 am

  #599786

  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

   

   

   

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  October 25, 2005 at 7:47 am

  #599792

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

  Generate insert statements

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  October 25, 2005 at 7:57 am

  #599798

  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.

• sushila

  SSC-Dedicated

  Points: 35293

  More actions

  October 25, 2005 at 8:14 am

  #599810

  "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 !!!**

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  October 25, 2005 at 8:18 am

  #599813

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

  unworkable deptworker

   

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