« first day (2567 days earlier)      last day (1185 days later) » 

10:32 AM
@ypercubeᵀᴹ are you there?
 
@McNets yes
 
@ypercubeᵀᴹ I'm a bit bind, why first query returns 2 times 101,Delivered and 102,Pending?
http://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=d7979923b310509e81c0df5d654a7680
 
10:48 AM
Try adding ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW in the wndow.
The default is RANGE ... in SQL Server and you have ties.
 
@ypercubeᵀᴹ But the first one:
SELECT Emp_ID,
Status,
COUNT(Status) OVER (PARTITION BY Emp_ID, Status ORDER BY Emp_ID) AS No_Status
FROM tbl_Status
Returns 10 rows
 
Yes, I was talking about that one
or use OVER (... ORDER BY status_Id)
 
Using GROUP BY only 8
 
You don't have a GROUP bY. ???
 
It's commented
Shouldn't COUNT() OVER (PARTITION BY Emp_ID, Status) return the same result as GROUP BY Emp_ID, Status?
 
10:53 AM
Similar yes, not the same.
 
OK, thanks
 
Anyway, when you PARTITION BY Emp_ID, Status or GROUP BY Emp_ID, Status what happens when you have 2 or more rows in the same group/partition?
The ORDER BY Emp_ID you have in the OVER makes it a rolling sum/count.
This is possible with OVER PARTITION BY - but not with GROUP BY
@McNets If you want the same number, do NOT use ORDER BY inside the OVER
 
@ypercubeᵀᴹ ok, thank you
 
So, what is the wanted from the query?
Return 10 rows or 8?
 
8 I suppose
COUNT() of Status by Emp_ID
0
Q: How to calculate ranking based on two different attributes from two tables?

rchauI have two tables tbl_Generate and tbl_Status. tbl_Generate has two columns one is TGenerate(having only boolean value 1 or 0) and and Emp_Id Table is something like this:- T_ID Tgenerate Emp_ID 1 0 101 2 1 101 3 1 101 4 0 102 5 1 102 ...

 
11:12 AM
@McNets then GROUP BY in a derived table or CTE makes more sense
I can't figure what the questions is about though
 
@ypercubeᵀᴹ ok, me neither.
Please add the desired result. — McNets 59 mins ago
 
 
5 hours later…
4:36 PM
Skeet is about 480 away from 1mm
 
 
2 hours later…
6:35 PM
I'd have more than that if it wasn't for the Microsoft's little #TeamDownvote
 

« first day (2567 days earlier)      last day (1185 days later) »