October 26, 2005 at 7:44 am
Carl, when I put your query in the original query, I am still getting dupes....
Here is my resultset:
cgrtji cgrtji Dept1 IO0 0 0 7 70.847142857142856 495.92999999999995 NULL 0.34999999999999998 0 0
cgrtji cgrtji Dept2 IO3 0 0 7 70.847142857142856 495.92999999999995 NULL 0.34999999999999998 0 0
dbabic sptyo Dept1 IO0 0 0 0 NULL NULL NULL NULL 2 0
rmon aoier NULL NULL NULL NULL 0 NULL NULL NULL NULL 0 0
rmon cl Dept2 IO3 0 0 0 NULL NULL NULL NULL 0 0
rmon rmon Dept1 IO0 103 106 91 72.909890109890114 6634.8000000000002 3.0 4.5499999999999998 42 0
rmon rmon Dept2 IO3 103 106 91 72.909890109890114 6634.8000000000002 3.0 4.5499999999999998 42 0
rmon smay NULL NULL NULL NULL 0 NULL NULL NULL NULL 0 0
rmon tboks NULL NULL NULL NULL 0 NULL NULL NULL NULL 0 0
rmon wseifried NULL NULL NULL NULL 0 NULL NULL NULL NULL 0 0
smay eramsey NULL NULL NULL NULL 0 NULL NULL NULL NULL 0 0
smay rmith Dept1 IO0 280 62 262 84.577519083969477 22159.310000000001 17.0 13.1 253 0
smay rmith Dept2 IO3 280 62 262 84.577519083969477 22159.310000000001 17.0 13.1 253 0
smay jjohnson Dept1 IO0 315 192 316 94.971272151898717 30010.921999999995 21.0 15.800000000000001 405 7
smay jjohnson Dept2 IO3 315 192 316 94.971272151898717 30010.921999999995 21.0 15.800000000000001 405 7
smay kauben Dept1 IO0 300 90 248 90.412943548387048 22422.409999999989 14.0 12.4 288 5
smay kauben Dept2 IO3 300 90 248 90.412943548387048 22422.409999999989 14.0 12.4 288 5
smay jjackson Dept1 IO0 244 103 227 112.52594713656386 25543.389999999996 15.0 11.35 236 0
smay jjackson Dept2 IO3 244 103 227 112.52594713656386 25543.389999999996 15.0 11.35 236 0
smay mldako Dept1 IO0 262 149 204 73.003137254901972 14892.640000000003 11.0 10.199999999999999 297 46
smay mldako Dept2 IO3 262 149 204 73.003137254901972 14892.640000000003 11.0 10.199999999999999 297 46
smay netvich Dept1 IO0 288 194 295 102.20257627118646 30149.760000000006 19.0 14.75 331 0
smay netvich Dept2 IO3 288 194 295 102.20257627118646 30149.760000000006 19.0 14.75 331 0
smay nloer Dept1 IO0 309 167 285 74.296070175438658 21174.380000000016 19.0 14.25 394 0
smay nloer Dept2 IO3 309 167 285 74.296070175438658 21174.380000000016 19.0 14.25 394 0
smay slupin Dept1 IO0 361 85 341 107.1750146627566 36546.68 21.0 17.050000000000001 284 4
smay slupin Dept2 IO3 361 85 341 107.1750146627566 36546.68 21.0 17.050000000000001 284 4
smay smay Dept1 IO0 54 74 64 116.69890625000004 7468.7300000000023 3.0 3.2000000000000002 16 0
smay smay Dept2 IO3 54 74 64 116.69890625000004 7468.7300000000023 3.0 3.2000000000000002 16 0
As you can see, all the data after the Dept(1 or 2) is the same for each user. But it should be different.
Maybe I put together your query with mine wrong.
SELECT users.UdeptManager, users.Uname , webItems.IOofficeName AS Office, webItems.IOid,
(SELECT SUM(webable)
FROM studentcounts F1
WHERE F1.WRdeptweber = users.Uname) AS webable,
(SELECT SUM(Unwebable)
FROM studentcounts F2
WHERE F2.WRdeptweber = users.Uname) AS Unwebable,
(SELECT COUNT(BMid)
FROM billmaster, currentbillingperiod
WHERE BMdeletedDate IS NULL AND BMtransactionDate BETWEEN BPstartDate AND BPendDate AND BMdeptweber = users.Uname) AS Bills,
(SELECT AVG(BMtransactionAmount)
FROM billmaster, currentbillingperiod
WHERE BMdeletedDate IS NULL AND BMtransactionDate BETWEEN BPstartDate AND BPendDate AND BMdeptweber = users.Uname) AS AvgBill,
(SELECT SUM(BMtransactionAmount)
FROM billmaster, currentbillingperiod
WHERE BMdeletedDate IS NULL AND BMtransactionDate BETWEEN BPstartDate AND BPendDate AND BMdeptweber = users.Uname) AS Dollars,
(SELECT TOP 1 GOdayGoalAmount
FROM goals, currentbillingperiod
WHERE GOuserName = users.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 BMdeptweber = users.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.WRdeptweber = users.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.WRdeptweber = users.Uname) AS NewRecDay
FROM users
LEFT JOIN (select distinct webrequest.WRdeptweber , internaloffice.IOid , internaloffice.IOofficeName
From webrequest
RIGHT JOIN internaloffice
ON internaloffice.IOid = webrequest.WRinternalOfficeId
) as webItems (Uname, IOid , IOofficeName)
on webItems.Uname = Users.Uname
ORDER BY UdeptManager, users.uname
October 26, 2005 at 8:01 am
BTW,
Here is a sample of the resultset that I am trying to get...
smay jjohnson Dept1 IO0 315 192 316 94.971272151898717 30010.921999999995 21.0 15.800000000000001 405 7
smay jjohnson Dept2 IO3 454 342 221 45.453454334344334 45433.345345454333 54.0 33.500000000000005 488 5
smay kauben Dept1 IO0 300 90 248 90.412943548387048 22422.409999999989 14.0 12.4 288 5
smay kauben Dept2 IO3 321 67 456 53.564455345345553 56432.565644545454 15.0 12.8 433 4
smay jjackson Dept1 IO0 244 103 227 112.52594713656386 25543.389999999996 15.0 11.35 236 0
smay jjackson Dept2 IO3 342 653 453 341.45457656345345 43576.563435345522 33.0 56.33 452 0
smay mldako Dept1 IO0 262 149 204 73.003137254901972 14892.640000000003 11.0 10.199999999999999 297 46
smay mldako Dept2 IO3 231 453 231 34.004534223676756 34266.320000000005 16.0 11.453323445656345 452 34
smay netvich Dept1 IO0 288 194 295 102.20257627118646 30149.760000000006 19.0 14.75 331 0
smay netvich Dept2 IO3 324 564 344 234.56456454345437 45452.660000000005 14.0 15.56 234 0
smay nloer Dept1 IO0 309 167 285 74.296070175438658 21174.380000000016 19.0 14.25 394 0
smay nloer Dept2 IO3 342 122 232 55.565643453333452 34222.330000000015 19.0 22.55 432 0
smay slupin Dept1 IO0 361 85 341 107.1750146627566 36546.68 21.0 17.050000000000001 284 4
smay slupin Dept2 IO3 124 11 231 116.3434664545343 23422.23 54.2 33.040000000000005 343 2
smay smay Dept1 IO0 54 74 64 116.69890625000004 7468.7300000000023 3.0 3.2000000000000002 16 0
smay smay Dept2 IO3 12 22 11 233.32323232311322 4534.2342200000006 4.0 1.1000000000000005 12 0
October 26, 2005 at 9:18 pm
Numerous errors...
Invalid column name 'UcaseManager'. -- MISSING FROM users TABLE
Invalid column name 'BMworkRequestID'. -- MISSING FROM billmaster TABLE
Invalid object name 'samplerequests'. -- NEED THIS TABLE
Invalid object name 'dbo.billingperiods'. -- NEED THIS TABLE
Invalid object name 'currentbillingperiod'. -- NO dbo.billingperiods, HENCE THIS VIEW CANNOT BE CREATED
Invalid object name 'goals'. -- NEED THIS TABLE
Also, we will need more than one record per table if Duplication is the problem...
I wasn't born stupid - I had to study.
Viewing 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply