October 24, 2005 at 1:14 pm
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!
October 24, 2005 at 1:15 pm
The same still applies.
Post :
- Table definitions
- sample data
- wrong resultset
- expected resultset
October 24, 2005 at 1:17 pm
Agreed. My 'guess' would be it is in the Joining of tables, though...
I wasn't born stupid - I had to study.
October 24, 2005 at 1:19 pm
Mine too since she didn't use the correct join syntaxe everytime she posted but that's another story.
October 24, 2005 at 1:30 pm
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.
October 24, 2005 at 1:34 pm
It's like asking a mechanic to fix your flat tire without bringing the car in. That's just not gonna work.
October 24, 2005 at 2:19 pm
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
October 24, 2005 at 2:24 pm
This is still all missing.
- Table definitions
- sample data
- wrong resultset
- expected resultset
Also expect to have to normalize the table design after you posted it.
October 24, 2005 at 6:42 pm
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.
October 25, 2005 at 6:40 am
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 .
October 25, 2005 at 7:32 am
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]
  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]
  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]
  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]
  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]
  ON [PRIMARY]
) ON [PRIMARY]
GO
October 25, 2005 at 7:47 am
We still can't do squat without the sample data and required output. Do you want help or not???
October 25, 2005 at 7:57 am
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.
October 25, 2005 at 8:14 am
"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 !!!**
October 25, 2005 at 8:18 am
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.
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply