CODEWITHSUNDEEP
sqlsql-serversql-server-2008
techV
techV

Reputation: 935

Sql query result using Group By Clause without including timestamp in date field

I have a table as below which has records like, there are many records for a same client but their EffectiveDate and RunDate may be same or different. EffectiveDate and RunDate may also includes TimeStamp like 2014-02-09 14:15:30.000 and timestamp may be different for the same date.

ID  Client        EffectiveDate                 RunDate         
1   abc      01-01-2014 14:15:30.000    09-02-2014 14:15:30.000        
2   abc      02-01-2014 14:15:30.000    10-01-2014 14:15:30.000       
3   abc      02-01-2014 15:19:30.000    09-01-2014 15:19:30.000          
4   abc      03-01-2014 14:15:30.000    10-01-2014 14:15:30.000            
5   abc      03-01-2014 15:20:30.000    09-01-2014 15:20:30.000           
6   abc      02-01-2014 14:15:30.000    10-01-2014 14:15:30.000          
7   abc      04-01-2014 14:30:30.000    10-01-2014 14:30:30.000           
8   abc      04-01-2014 17:15:30.000    10-01-2014 17:15:30.000

I need a sql query which results like for same EffectiveDate it will count the number of records for that client regardless of EffectiveDate's Timestamp and RunDate (including its Timestamp). I need results like

 ID  Client      EffectiveDate   Number Of Records    
 1   abc          01-01-2014            1
 2   abc          02-01-2014            3
 4   abc          03-01-2014            2
 7   abc          04-01-2014            2

I know it may be possible using Group By in sql query But if there is timestamp in date then i am not able to get the desired result.

Upvotes: 0

Views: 232

Answers (5)

Rajesh
Rajesh

Reputation: 7876

Try the below query:

SELECT Min(ID) AS ID, 
    Client, 
    CONVERT(DATE,EffectiveDate) AS EffectiveDate, 
    MIN(RunDate) AS RunDate, 
    COUNT(*) AS [Number Of Records]
FROM ClientTableName
GROUP BY Client, CONVERT(Date,EffectiveDate)

NOTE: The only difference is the date format returned. which can be easily changed to required format

Upvotes: 0

Dharmesh Porwal
Dharmesh Porwal

Reputation: 1406

select min(id) id,client,trunc(effectivedate) effectivedate,
min(trunc(rundate)) rundate,
count(client) no_of_records 
from tempd
group by client,trunc(effectivedate)
order by 1

You can try this one its working fine and fast in oracle sql, for rundate, depends on you which one you want to keep minimum or maximum one;in this query i kept it minimum if you want maximum then put max instead of min and you will get your desired result.

Upvotes: 0

Veera
Veera

Reputation: 3492

Use the below query to obtain the result: 

    SELECT ID, Client, EffectiveDate, RunDate
    FROM 
        (SELECT ROW_NUMBER() OVER (PARTITION BY A.Client, B.EffectiveDate, 
                B.NumberOfRecords ORDER BY A.ID) AS RowNum, 
                 A.ID, A.Client, B.EffectiveDate, CONVERT(VARCHAR(10), A.RunDate ,105) 
                AS RunDate, B.NumberOfRecords
         FROM #Temp A 
         INNER JOIN (SELECT Client, CONVERT(VARCHAR(10),EffectiveDate ,105) AS 
                          EffectiveDate, COUNT(*) AS NumberOfRecords
                     FROM #Temp
                      GROUP BY Client, CONVERT(VARCHAR(10),EffectiveDate ,105)) B 
         ON A.Client = B.Client AND CONVERT(VARCHAR(10), A.EffectiveDate ,105) = 
            B.EffectiveDate) AS C
    WHERE RowNum = 1

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93724

You need to convert datetime to date to remove the timestamp in group by

select  Client,convert(date,EffectiveDate), count(1) [Number Of Records]
from tablename 
group by Client,convert(date,EffectiveDate)

If you want result with Id then try this(untested).

;WITH cte
     AS (SELECT Row_number() OVER(partition BY CONVERT(DATE, EffectiveDate) ORDER BY id) rn,*
         FROM   tablename)
SELECT a.ID,
       a.Client,
       a.EffectiveDate,
       b.[Number Of Records]
FROM   cte a
       JOIN(SELECT Client,
                   CONVERT(DATE, EffectiveDate) EffectiveDate,
                   Count(1)                     [Number Of Records]
            FROM   tablename
            GROUP  BY Client,
                      CONVERT(DATE, EffectiveDate)) b
         ON CONVERT(DATE, a.EffectiveDate) = b.EffectiveDate 
where rn =1

Upvotes: 0

Krish KvR
Krish KvR

Reputation: 1054

select date part like this 

select convert(varchar,getdate(),105)

also group by with the same

Upvotes: 0

Related Questions