CODEWITHSUNDEEP
sql-server
suresh
suresh

Reputation: 1

what is the correct date function to count the number of occurrences per weekly basis

I would like to calculate the number of records available per daily basis and consolidate report on weekly basis. I have written below sql but it is displaying the data more than a week.

select convert(varchar, CreatedDateTime, 101), COUNT(CreatedDateTime) as Count 
from dbo.table 
group by convert(varchar, CreatedDateTime, 101)

Union all

select 'Grand Total' CreatedDateTime,COUNT(CreatedDateTime) 
from dbo.table 
where CreatedDateTime >= DATEADD(WEEK,-1,getdate());

Please suggest .

Upvotes: 0

Views: 84

Answers (2)

richinsql
richinsql

Reputation: 332

I created some sample data to use 

CREATE TABLE CreatedDateTime
(
CreatedDateTime DATETIME
)

Inserted the data into the table above

INSERT INTO CreatedDateTime 
    VALUES
    (GETDATE()-8),
    (GETDATE()-8),
    (GETDATE()-8),
    (GETDATE()-8),
    (GETDATE()-8),
    (GETDATE()-8),
    (GETDATE()-8),
    (GETDATE()-6),
    (GETDATE()-6),
    (GETDATE()-1),
    (GETDATE()-1),
    (GETDATE()-1),
    (GETDATE()-1),
    (GETDATE()-1),
    (GETDATE()-1),
    (GETDATE()-2),
    (GETDATE()-2),
    (GETDATE()-2),
    (GETDATE()-2),
    (GETDATE()-2)

The query

select     
convert(varchar, CreatedDateTime, 101) as Date, 
COUNT(CreatedDateTime) as Total     
from dbo.CreatedDateTime     
where CreatedDateTime >= DATEADD(Week,-1,getdate())    
GROUP BY ROLLUP(CreatedDateTime)

Upvotes: 0

Orenger
Orenger

Reputation: 172

you were missing a where on you converted date ,TRY:

select convert(varchar, CreatedDateTime, 101), COUNT(CreatedDateTime) as Count 
FROM [Test_DB].[dbo].[table]
where convert(varchar, CreatedDateTime, 101) >= DATEADD(WEEK,-1,getdate())
group by convert(varchar, CreatedDateTime, 101)
Union all
select 'Grand Total' ,COUNT(CreatedDateTime) 
FROM [Test_DB].[dbo].[table]
where CreatedDateTime >= DATEADD(WEEK,-1,getdate());

Upvotes: 1

Related Questions