quanmac
asked on
SQL Query Problem
Hi there,
I am trying to retrieve the last 100 distinct job positions that have attended an event. Right now the query returns the last 100 job positions but they are not distinct, because I have an order by event start date in my query clause. Is there anyway else I can retrieve this information.
I've posted the query below. I've tried a group by and that didn't work either because of the event start date.
Query:
SELECT DISTINCT TOP 100 WebUser.Wuser_Position, Event.Evnt_Start_Date
FROM Event INNER JOIN
Invoice ON Event.Evnt_ID = Invoice.Evnt_ID INNER JOIN
InvoiceItems ON Invoice.Inv_ID = InvoiceItems.Inv_ID INNER JOIN
WebUser ON InvoiceItems.Wuser_ID = WebUser.Wuser_ID
WHERE (WebUser.Wuser_Position IS NOT NULL) AND (WebUser.Wuser_Enabled = 1) AND
(NOT (UPPER(WebUser.Wuser_First _Name) LIKE '%TEST%')) AND
(NOT (UPPER(WebUser.Wuser_Last_ Name) LIKE '%TEST%'))
ORDER BY Event.Evnt_Start_Date DESC
I am trying to retrieve the last 100 distinct job positions that have attended an event. Right now the query returns the last 100 job positions but they are not distinct, because I have an order by event start date in my query clause. Is there anyway else I can retrieve this information.
I've posted the query below. I've tried a group by and that didn't work either because of the event start date.
Query:
SELECT DISTINCT TOP 100 WebUser.Wuser_Position, Event.Evnt_Start_Date
FROM Event INNER JOIN
Invoice ON Event.Evnt_ID = Invoice.Evnt_ID INNER JOIN
InvoiceItems ON Invoice.Inv_ID = InvoiceItems.Inv_ID INNER JOIN
WebUser ON InvoiceItems.Wuser_ID = WebUser.Wuser_ID
WHERE (WebUser.Wuser_Position IS NOT NULL) AND (WebUser.Wuser_Enabled = 1) AND
(NOT (UPPER(WebUser.Wuser_First
(NOT (UPPER(WebUser.Wuser_Last_
ORDER BY Event.Evnt_Start_Date DESC
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Whoops!
Add before RETURN:
SELECT * FROM #tempPos
Add before RETURN:
SELECT * FROM #tempPos
ASKER
Thanks Lowfatspread, it worked like a charm
Don't use a single query. Use a stored procedure.
CREATE PROCEDURE get100Positions
AS
CREATE TABLE #tempPos (
position <whatever type Wuser_position is> NOT NULL PRIMARY KEY
)
DECLARE p1 READ-ONLY CURSOR FOR
SELECT
WebUser.Wuser_Position, Event.Evnt_Start_Date
FROM
Event
INNER JOIN
Invoice
ON
Event.Evnt_ID = Invoice.Evnt_ID
INNER JOIN
InvoiceItems ON Invoice.Inv_ID = InvoiceItems.Inv_ID
INNER JOIN
WebUser
ON InvoiceItems.Wuser_ID = WebUser.Wuser_ID
WHERE
(
WebUser.Wuser_Position IS NOT NULL
)
AND
(
WebUser.Wuser_Enabled = 1
)
AND
(
NOT
(
UPPER(WebUser.Wuser_First_
)
)
AND
(
NOT
(
UPPER(WebUser.Wuser_Last_N
)
)
ORDER BY
Event.Evnt_Start_Date DESC
OPEN p1
DECLARE @position <type of Wuser_Position>
DECLARE @startDate DATETIME
FETCH NEXT FROM p1 INTO @postion, @startDate
WHILE @@FETCH_STATUS = 0 AND (SELECT COUNT COUNT(*) FROM #tempPOS) < 100)
BEGIN
IF (SELECT COUNT(*) FROM #tempPOS WHERE position = @position) = 0
BEGIN
INSERT INTO #tempPos (position) VALUES (@position)
END
END
CLOSE p1
DEALLOCATE p1
RETURN
GO