Sub Query issues - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sub Query issues

Author

Topic

jessyx
Starting Member

5 Posts

Posted - 2008-08-11 : 10:02:35

Hello. I have 2 tables I am combining together - perlist (listing of personnel) and PhoneDaily (tracking of things they do on the phones). I am combining these two tables, and need all records to show, whether blank or not - for all personnel based on what desk they are on. Here is the current query i have tried:

SELECT PerList.LName + ', ' + PerList.FName AS FLName, PerList.Team, derivedtbl_1.Calls, derivedtbl_1.Tickets, derivedtbl_1.TTC,
derivedtbl_1.productiontime, derivedtbl_1.FCR, derivedtbl_1.quality, derivedtbl_1.ACD, derivedtbl_1.ACW, derivedtbl_1.acdp, derivedtbl_1.acwp,
derivedtbl_1.attendance, derivedtbl_1.attendp, derivedtbl_1.Aux1, derivedtbl_1.Aux2, derivedtbl_1.Staffed
FROM (PerList LEFT OUTER JOIN
(SELECT ID, SUM(Aux1) AS Aux1, SUM(Aux2) AS Aux2, SUM(StaffedTime) AS Staffed, SUM(Tickets) AS Tickets, SUM(Calls) AS Calls, '-' AS TTC,
SUM(Aux1 + Aux2 + Aux3 + Aux4 + Aux5 + Aux6 + Aux7 + Aux8) AS productiontime, '-' AS FCR, '-' AS quality, SUM(ACD) AS ACD, SUM(ACW)
AS ACW, '-' AS acdp, '-' AS acwp, '-' AS attendance, '-' AS attendp, ? AS Tm, ? AS Dsk
FROM PhoneDaily PhoneDaily_1
WHERE ([Date] BETWEEN ? AND ?) AND (PhoneDaily.Desk LIKE '%' + derivedtbl_1.Dsk + '%')
GROUP BY ID) derivedtbl_1 ON PerList.ID = derivedtbl_1.ID)
WHERE (PerList.Team LIKE '%' + derivedtbl_1.Tm + '%') AND (PerList.ClientDesk LIKE '%' + derivedtbl_1.Dsk + '%')
ORDER BY PerList.LName

Everything works fine if I take out one of the desk checks - but I need both there. Basically - the first (on the derrived table) is used to strictly sum a specific desk (based on assigned project). I cant sum thier DOE data with their NUSA data for instance. The second - should be showing me only those entries from the desk that is in selection (dropdown choices on webpage). When I run this query, I get an error.

I have tried doing both desks as their own: ...LIKE "%" + ? + "%"... and that failed too. So I doubt its from the input method, but am not completely sure how to fix this. Going crazy over this one, so any help would be greatly appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 10:07:42

you're using alias derivedtbl inside the query while definition is outside. remove it and try. also are you using this inside SSIS as i can see a lot of ?'s are they parameter place holders?

SELECT PerList.LName + ', ' + PerList.FName AS FLName, PerList.Team, derivedtbl_1.Calls, derivedtbl_1.Tickets, derivedtbl_1.TTC, 
derivedtbl_1.productiontime, derivedtbl_1.FCR, derivedtbl_1.quality, derivedtbl_1.ACD, derivedtbl_1.ACW, derivedtbl_1.acdp, derivedtbl_1.acwp, 
derivedtbl_1.attendance, derivedtbl_1.attendp, derivedtbl_1.Aux1, derivedtbl_1.Aux2, derivedtbl_1.Staffed
FROM PerList 
LEFT OUTER JOIN
(SELECT ID, 
SUM(Aux1) AS Aux1, 
SUM(Aux2) AS Aux2, 
SUM(StaffedTime) AS Staffed, 
SUM(Tickets) AS Tickets, 
SUM(Calls) AS Calls, 
'-' AS TTC, 
SUM(Aux1 + Aux2 + Aux3 + Aux4 + Aux5 + Aux6 + Aux7 + Aux8) AS productiontime, 
'-' AS FCR, '-' AS quality, 
SUM(ACD) AS ACD, 
SUM(ACW) AS ACW, 
'-' AS acdp, 
'-' AS acwp, 
'-' AS attendance, 
'-' AS attendp, 
? AS Tm, 
? AS Dsk
FROM PhoneDaily PhoneDaily_1
WHERE 
([Date] BETWEEN ? AND ?) 
AND (PhoneDaily.Desk LIKE '%' + derivedtbl_1.Dsk + '%')
GROUP BY ID) derivedtbl_1 
ON PerList.ID = derivedtbl_1.ID
WHERE 
(PerList.Team LIKE '%' + derivedtbl_1.Tm + '%')
 AND (PerList.ClientDesk LIKE '%' + derivedtbl_1.Dsk + '%')
ORDER BY PerList.LName

jessyx
Starting Member

5 Posts

Posted - 2008-08-11 : 10:11:18

Unfortunately, that didnt change the error either :(

Yes, the site passes in 4 different parameters:

Team
Desk
Start Date
End Date

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 10:15:07

quote:
Originally posted by jessyx

Unfortunately, that didnt change the error either :(

Yes, the site passes in 4 different parameters:

Team
Desk
Start Date
End Date

you're grouping on ID so needs some aggregate to applied on other columns. Apply MIN or MAX to all the values except ID in derivedtbl_1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 10:17:36

Here you have 5 parameters, of which 2 should be the same

SELECT		PerList.LName + ', ' + PerList.FName AS FLName,
		PerList.Team,
		derivedtbl.Calls,
		derivedtbl.Tickets,
		derivedtbl.TTC,
		derivedtbl.productiontime,
		derivedtbl.FCR,
		derivedtbl.quality,
		derivedtbl.ACD,
		derivedtbl.ACW,
		derivedtbl.acdp,
		derivedtbl.acwp,
		derivedtbl.attendance,
		derivedtbl.attendp,
		derivedtbl.Aux1,
		derivedtbl.Aux2,
		derivedtbl.Staffed
FROM		PerList
LEFT JOIN	(
			SELECT		ID,
					SUM(Aux1) AS Aux1,
					SUM(Aux2) AS Aux2,
					SUM(StaffedTime) AS Staffed,
					SUM(Tickets) AS Tickets,
					SUM(Calls) AS Calls,
					'-' AS TTC,
					SUM(Aux1 + Aux2 + Aux3 + Aux4 + Aux5 + Aux6 + Aux7 + Aux8) AS productiontime,
					'-' AS FCR,
					'-' AS quality,
					SUM(ACD) AS ACD,
					SUM(ACW) AS ACW,
					'-' AS acdp,
					'-' AS acwp,
					'-' AS attendance,
					'-' AS attendp
			FROM		PhoneDaily PhoneDaily_1
			WHERE		[Date] BETWEEN ? AND ?
					AND PhoneDaily.Desk LIKE '%' + ? + '%'
			GROUP BY	ID
		) AS derivedtbl ON PerList.ID = derivedtbl.ID
WHERE		PerList.Team LIKE '%' + ? + '%'
		AND PerList.ClientDesk LIKE '%' + ? + '%'
ORDER BY	PerList.LName

E 12°55'05.25"
N 56°04'39.16"

jessyx
Starting Member

5 Posts

Posted - 2008-08-11 : 10:22:22

Strangely enough - that last one did it... I tried it before with ? instead of Dsk, but it gave the same error. Oh well, that fixed it. Thanx sooo much! Perhaps i screwed it up when I tried earlier! Thanx again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources