Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Need to return values with multiple entries only Expand / Collapse
Author
Message
Posted Friday, May 9, 2014 8:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 18, 2014 10:37 AM
Points: 7, Visits: 37
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!

Post #1569483
Posted Friday, May 9, 2014 9:18 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:53 PM
Points: 848, Visits: 5,467
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;

Post #1569485
Posted Friday, May 9, 2014 10:23 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:46 PM
Points: 2,253, Visits: 6,178
Same in slightly different flavor

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

Post #1569488
Posted Saturday, May 10, 2014 4:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 18, 2014 10:37 AM
Points: 7, Visits: 37
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. :)

Thanks again!
Post #1569557
Posted Saturday, May 10, 2014 6:23 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:53 PM
Points: 848, Visits: 5,467
Now to implement in a much more complex way. :)


Umm... Okay! I hope you're being facetious!
Post #1569564
Posted Saturday, May 10, 2014 8:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 18, 2014 10:37 AM
Points: 7, Visits: 37
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.
Post #1569567
Posted Saturday, May 10, 2014 8:41 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:53 PM
Points: 848, Visits: 5,467
So this was a simplified example?

If you're having trouble implementing a more complex solution, post the table info and the expected result.
Post #1569570
Posted Sunday, May 11, 2014 7:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 18, 2014 10:37 AM
Points: 7, Visits: 37
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.


  Post Attachments 
Punch.GIF (7 views, 19.87 KB)
Post #1569615
Posted Sunday, May 11, 2014 1:19 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:53 PM
Points: 848, Visits: 5,467
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.)
Post #1569649
Posted Monday, May 12, 2014 5:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 18, 2014 10:37 AM
Points: 7, Visits: 37
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?
Post #1569776
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse