May 9, 2014 at 8:45 pm
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!
May 9, 2014 at 9:18 pm
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;
May 9, 2014 at 10:23 pm
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
May 10, 2014 at 4:14 pm
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!
May 10, 2014 at 6:23 pm
Now to implement in a much more complex way. 🙂
Umm... Okay! I hope you're being facetious!
May 10, 2014 at 8:13 pm
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.
May 10, 2014 at 8:41 pm
So this was a simplified example?
If you're having trouble implementing a more complex solution, post the table info and the expected result.
May 11, 2014 at 7:30 am
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.
May 11, 2014 at 1:19 pm
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.)
May 12, 2014 at 5:13 am
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?
May 14, 2014 at 8:29 am
If you just want the total hours, you don't need to return all the rows at all, simply total the time differences. This is very easy if there are always matching check-in and check-out records.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply