The Heap™ – Consultancy ©®

« first day (2567 days earlier) ← previous day next day → last day (2514 days later) »

McNets

10:32 AM

@ypercubeᵀᴹ are you there?

ypercubeᵀᴹ

@McNets yes

McNets

@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

ypercubeᵀᴹ

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.

McNets

@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

ypercubeᵀᴹ

Yes, I was talking about that one

or use OVER (... ORDER BY status_Id)

McNets

Using GROUP BY only 8

ypercubeᵀᴹ

You don't have a GROUP bY. ???

McNets

It's commented

Shouldn't COUNT() OVER (PARTITION BY Emp_ID, Status) return the same result as GROUP BY Emp_ID, Status?

ypercubeᵀᴹ

10:53 AM

Similar yes, not the same.

McNets

OK, thanks

ypercubeᵀᴹ

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

McNets

@ypercubeᵀᴹ ok, thank you

ypercubeᵀᴹ

So, what is the wanted from the query?

Return 10 rows or 8?

McNets

8 I suppose

COUNT() of Status by Emp_ID

Q: How to calculate ranking based on two different attributes from two tables?

I 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 ...