CODEWITHSUNDEEP
sqlsql-server
Tanmay
Tanmay

Reputation: 590

how to do a query to select some selected datas on group by

I have a table like below structure:

ID|PrcessorID|BatchNO|NoOfTransaction|BatchCreatedDate
1 |20        |2      |3              |2017-03-28
2 |21        |3      |3              |2017-04-01
3 |21        |4      |7              |2017-04-01
4 |20        |5      |3              |2017-04-01
5 |21        |6      |2              |2017-04-02
6 |20        |7      |4              |2017-04-02

and another table is like 

ProcessorID|ProcessorName
20         |Payme
21         |Payany

I have to get the data total no of transaction and count of the batch on each specific date by each specific processor of 3 days ago group by the processor so that I can get data like:

PrcessorName|Batchcount|TotNoOfTransaction|BatchCreatedDate

     Payany |2         |10                |2017-04-01
     Payme  |1         |3                 |2017-04-01
     Payany |1         |2                 |2017-04-02
      Payme |1         |4                 |2017-04-02

What i am doin right now is:

Select a.ProcessorId As ProcessorID,b.ProcessorName As ProcessorName,BatchCreatedDate,Sum(NoofTRansaction) As TotNoofTransaction,Count(BatchNo) As BatchCountfrom TableA a innerjoin TableB b on a.ProcessorID=b.ProcessorID where BatchcreatedDate<GetDate() and BatchCreatedDate>GetDate()-4 groupby ProcessorName,BatchCreatedDate

but this query is giving me result like

PrcessorName|Batchcount|TotNoOfTransaction|BatchCreatedDate

     Payany |1         |3                 |2017-04-01
     Payany |1         |7                 |2017-04-01 
     Payme  |1         |3                 |2017-04-01
     Payany |1         |2                 |2017-04-02
      Payme |1         |4                 |2017-04-02

Upvotes: 0

Views: 43

Answers (2)

user7715598
user7715598

Reputation: 

;WITH Cte1 (ID,ProcessorID,BatchNO,NoOfTransaction,BatchCreatedDate)
As
(
SELECT 1 ,20 ,2 ,3  ,'2017-03-28' UNION ALL
SELECT 2 ,21 ,3 ,3  ,'2017-04-01' UNION ALL
SELECT 3 ,21 ,4 ,7  ,'2017-04-01' UNION ALL
SELECT 4 ,20 ,5 ,3  ,'2017-04-01' UNION ALL
SELECT 5 ,21 ,6 ,2  ,'2017-04-02' UNION ALL
SELECT 6 ,20 ,7 ,4  ,'2017-04-02' 
)
,cte2(ProcessorID,ProcessorName) AS (
        SELECT 20,'Payme'UNION ALL
        SELECT 21,'Payany'
        )

SELECT DISTINCT cte2.ProcessorName
    ,COUNT(BatchNO) OVER (
        PARTITION BY cte1.ProcessorID
        ,Cte1.BatchCreatedDate ORDER BY Cte1.BatchCreatedDate
        )As Batchcount
    ,SUM(NoOfTransaction) OVER (
        PARTITION BY Cte1.ProcessorID
        ,Cte1.BatchCreatedDate ORDER BY Cte1.BatchCreatedDate
        ) As TotNoOfTransaction
    ,cte1.BatchCreatedDate
FROM Cte1
INNER JOIN cte2 ON cte2.ProcessorID = Cte1.ProcessorID
ORDER BY cte1.BatchCreatedDate

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

My best guess is that BatchCreatedDate has a time component. Does this do what you want?

Select a.ProcessorId As ProcessorID, b.ProcessorName As ProcessorName,
       cast(BatchCreatedDate as date) as bcd,
       Sum(NoofTRansaction) As TotNoofTransaction,
       Count(BatchNo) As BatchCount
from TableA a inner join
     TableB b
     on a.ProcessorID = b.ProcessorID
where BatchcreatedDate < GetDate() and BatchCreatedDate > GetDate()-4
group by a.ProcessorId, b.ProcessorName, cast(BatchCreatedDate as date);

Upvotes: 1

Related Questions