Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need to return values with multiple entries only


Need to return values with multiple entries only

Author
Message
dnbr2002
dnbr2002
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 40
Very basically, I need to return a result set based on another value and only if there are multiples of that other value.

Example. select * from mytable

Returns (--column separater)

John---1---1
John---1---1
John---2---2
John---3---3
John---3---3
John---4---4

So I want a query that would return only

John---1---1
John---1---1
John---3---3
John---3---3

The query cannot be something as simple as this:

Select * from mytable where John=1 or John=3

I have to many possible results to plan like that. Im thinking some kind of aggregate with a group by but my brain is just not working.

I really appreciate any advice.

Thank you!
pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2181 Visits: 12535
This is close:

WITH cteList(col1, col2, freq) AS
( SELECT col1, col2, count(*) OVER (PARTITION BY col1, col2) AS Freq
FROM (
SELECT 'John' AS col1,1 as col2,1 as col3
UNION ALL
SELECT 'John',1,1
UNION ALL
SELECT 'John',2,2
UNION ALL
SELECT 'John',3,3
UNION ALL
SELECT 'John',3,3
UNION ALL
SELECT 'John',4,4) x
)

SELECT col1, col2, freq
FROM cteList
WHERE freq>1;


Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6762 Visits: 17733
Same in slightly different flavor
Cool
USE tempdb;
GO

DECLARE @SAMPLE TABLE
(
PERSON VARCHAR(25) NOT NULL
,VAL_1 INT NOT NULL
,VAL_2 INT NOT NULL
);
INSERT INTO @SAMPLE (PERSON,VAL_1,VAL_2)
VALUES
('John',1,1)
,('John',1,1)
,('John',2,2)
,('John',3,3)
,('John',3,3)
,('John',4,4);
/* Nested query */
SELECT
X.PERSON
,X.VAL_1
,X.VAL_2
FROM
(
SELECT
S.PERSON
,S.VAL_1
,S.VAL_2
,COUNT(PERSON) OVER
(
PARTITION BY
S.PERSON
,S.VAL_1
) AS PCOUNT
FROM @SAMPLE S
) AS X
WHERE X.PCOUNT > 1

/* CTE version */
;WITH PBASE AS
(
SELECT
S.PERSON
,S.VAL_1
,S.VAL_2
,COUNT(PERSON) OVER
(
PARTITION BY
S.PERSON
,S.VAL_1
) AS PCOUNT
FROM @SAMPLE S
)
SELECT
PB.PERSON
,PB.VAL_1
,PB.VAL_2
FROM PBASE PB
WHERE PB.PCOUNT > 1


dnbr2002
dnbr2002
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 40
Thank you both very much. Im using a CTE pretty simply now.

with cte as
(select name, col2, col3, COUNT(*) over (PARTITION By Name, col2, col3) as freq
from mytable)
select * from cte
where freq>1




Logic works. Now to implement in a much more complex way. Smile

Thanks again!
pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2181 Visits: 12535
Now to implement in a much more complex way. Smile


Umm... Okay! I hope you're being facetious!
dnbr2002
dnbr2002
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 40
Facetious about what?? Adding to a much more complex query, or making the logic more complex? I'd like to keep the logic as simple as possible, but the query is much more complex then the example I provided.
pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2181 Visits: 12535
So this was a simplified example?

If you're having trouble implementing a more complex solution, post the table info and the expected result.
dnbr2002
dnbr2002
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 40
Basically im looking at a query for someome. Someone (not me) wrote this query to grab the punchin time for employees and punch out time. The problem is if an employed punches in more than once in a day, lets say they punch in at 9am, punch out at 12pm, then they punch in at 1pm and punch out 5pm their query only grabs the first punch in and the last punch out, so it looks like the person worked from 9am to 5pm. Based on what I understand at this point, the query isn't designed for multiple Punchin's because of the select top 1 min and max??



SELECT DISTINCT SUBSTRING(CAST(ISNULL(tbldepartment.DepartmentNumber,0) AS VARCHAR(10)),1,3) AS FacilityID
, empMain.employeeId AS EmployeeId,convert(varchar(10), [timecard_dt], 20) AS PunchDate,
convert(VARCHAR(19),(
SELECT Top 1 MIN(tw.workingpunch_ts)
FROM timeWorkingPunch tw
WHERE tw.employee_id = timeWorkingPunch.employee_id
AND tw.inpunch_dt = timeWorkingPunch.inpunch_dt
AND tw.active_yn = '1'
AND tw.breaktype_id = 3
AND tw.inout_id = 1),20) AS PunchIn,
convert(VARCHAR(19),(
SELECT TOP 1 MAX(tw.workingpunch_ts)
FROM timeWorkingPunch tw
WHERE tw.employee_id = timeWorkingPunch.employee_id
AND tw.inpunch_dt = timeWorkingPunch.inpunch_dt
AND tw.breaktype_id = 3
AND tw.active_yn = '1'
AND tw.inout_id = 2),20) AS PunchOut,
(SELECT SUM(tc.worked_hr) FROM timeCard tc WHERE tc.employee_id = timeCard.employee_id AND tc.timecard_dt = timeCard.timecard_dt) AS PaidTime,
'' AS JobCode,'' AS PayCode
FROM timeWorkingPunch
INNER JOIN timeCard ON timeWorkingPunch.employee_id = timeCard.employee_id
LEFT OUTER JOIN empMain ON empMain.employee_id = timeWorkingPunch.employee_id
LEFT OUTER JOIN tblDepartment ON tblDepartment.department_id = timeCard.department_id
WHERE inpunch_dt = timecard_dt AND inpunch_dt > dateadd(d,datediff(d,0, dateadd(d,-15,getdate())),0) AND empmain.active_yn = '1'
AND CAST(SUBSTRING(CAST(ISNULL(DepartmentNumber,0) AS VARCHAR(10)),1,3) AS INT) >= 409
AND CAST(SUBSTRING(CAST(ISNULL(DepartmentNumber,0) AS VARCHAR(10)),1,3) AS INT) <= 414
AND (
SELECT TOP 1 MIN(tw.workingpunch_ts)
FROM timeWorkingPunch tw
WHERE tw.employee_id = timeWorkingPunch.employee_id
AND tw.inpunch_dt = timeWorkingPunch.inpunch_dt
AND tw.active_yn = '1'
AND tw.breaktype_id = 3
AND tw.inout_id = 1) IS NOT NULL




Results look like (see attached).



So I dumped the results from the query into a table and used a cte to check the freq of PUnchdate and EmployeeID:

with cte as
(
select EmployeeID, PunchDate, PunchIn, PunchOut, COUNT(*) Over (PARTITION By EmployeeID, PunchDate) as Freq
from domintest
)
select * from cte
where Freq>1
order by employeeID, PunchDate


No results where Freq>1 so it appears the query is performing as designed?? Let me know if my logic is correct.
Attachments
Punch.GIF (7 views, 19.00 KB)
pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2181 Visits: 12535
Maybe my brain is not working, but if you can return a set of punch in/outs for an employee, shouldn't you be able to use ROWNUMBER() to get odd (clock in) and even (clock out) records? Then you join odds to evens ( ON rn=rn-1), and then just subtract? Or is there a possibility that someone may not have clocked in/out on a given day? If you did that, you may not even need RowNumber, but you could use LAG instead... (just LAG by one to get the previous record.)
dnbr2002
dnbr2002
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 40
It's not a matter of getting the PunchIn and PunchOut. Whoever wrote this query is using a another field to grab what is considered PunchIn and PunchOut, a field called 'inout_id'. I think I found another table that has the records as well so I was just curious why this person used a subquery in the selects with Top 1 Min and Max. I was trying to confirm if using this Min and Max is why they are only getting the first PunchIn and the last PunchOut. I know the subquery can only return one result im just not sure why it's written that way if there is a table that holds all punchins and punchouts for days with multiple entries. Any thoughts on that?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search