0

I have a field titled DTUpdated and it is a timestamp field.

I am trying to select a count of the dates in the rows that are xx old. i.e. 0-3 months, 3-6 months etc.

I am not at the writing date selection criteria for the selection yet as i am stuck getting the first part of the query to simply count the occurrences of each day in a results pane.

The query i have so far is as follows but it is not grouping the data by the days as returned.

SELECT CONVERT(VARCHAR(10),[SCAData].[dbo].[Camera].[DTUpdated],105) AS Date_Updated,
COUNT(DTUpdated) AS No_of_Rows 
FROM [SCAData].[dbo].[Camera]
GROUP BY DTUpdated  
ORDER BY DTUpdated; 

Result:

Date_Updated    No_of_Rows
03-10-2006      1
03-10-2006      1
05-10-2006      1
05-10-2006      1
05-10-2006      1
05-10-2006      1
05-10-2006      1
05-10-2006      1
05-10-2006      1
06-10-2006      1
06-10-2006      1
06-10-2006      1
06-10-2006      1
06-10-2006      1

Can someone please help or also suggest how to then get these back based on date selections of last 3 months, 3-6 months?

Many thanks in advance.

10
  • what database are you using? SQL Server? postgres? Oracle? mysql? other? and what version – Danielle Paquette-Harvey Jan 7 at 16:12
  • I am using SQL Management Studio. – Brynster Jan 7 at 16:24
  • I have since managed to get this far to select count by day and country (constabulary) – Brynster Jan 7 at 16:25
  • 1
    Hi. This is on MS SQL Server and i have just checked the properties of the column and it is in fact a datetime field. – Brynster Jan 8 at 17:20
  • 1
    The version is as follows: Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) Apr 29 2016 23:23:58 Copyright (c) Microsoft Corporation Web Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor) – Brynster Jan 8 at 17:20
0

Since the column type is a datetime and this is SQL Server 2016, converting it to a date is easy :

SELECT CONVERT(Date,[SCAData].[dbo].[Camera].[DTUpdated]) AS Date_Updated,
COUNT(DTUpdated) AS No_of_Rows 
FROM [SCAData].[dbo].[Camera]
GROUP BY CONVERT(Date,[SCAData].[dbo].[Camera].[DTUpdated])  
ORDER BY 1; 

If you really want to do it via an VARCHAR field, just do it like that :

SELECT CONVERT(VARCHAR(10),[SCAData].[dbo].[Camera].[DTUpdated],105) AS Date_Updated,
COUNT(DTUpdated) AS No_of_Rows 
FROM [SCAData].[dbo].[Camera]
GROUP BY CONVERT(VARCHAR(10),[SCAData].[dbo].[Camera].[DTUpdated],105)  
ORDER BY 1; 
4
  • 1
    Hi Danielle. This is great thank you. I have added some amendments to lock in xx days as a parameter in the results and a country field and this all works great. – Brynster Jan 13 at 10:09
  • 1
    I kind of need to see this detail as well as then group by just country without seeing the individual days but i think i will split this over two tables of results by writing this to one and having a new query to run the report from. This has been really helpful, thank you again. – Brynster Jan 13 at 10:10
  • Happy to be of help. If you would, could you mark my answer as the answer? Thanks – Danielle Paquette-Harvey Jan 13 at 12:08
  • 1
    Thanks you again. Now marked as the answer. – Brynster Jan 14 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.