Sql-server – Select count of timestamp formatted field in short date format to give number of rows

dateselectsql servertimestamp

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.

Best Answer

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;

Related Solutions

Postgresql – Select consecutive number of rows starting from some number

You can try something like

SELECT field1, field2 
FROM table1 
ORDER BY ctid
OFFSET x
LIMIT 100
;

As I commented, without ordering you can never be sure that you get the same 100 rows. This way you will get the same rows in a certain order if they don't get updated or deleted.

Sql-server – How to speed up query on table with millions of rows

On reason this can happen is that you're using local variables.

The problem is that this query takes so much time to go, despite all of the indexes i've made on different columns.

Here's an example using a similar setup. In the Stack Overflow schema there's a narrow-ish table called Votes that looks like this.

With no index on CreationDate, our only option would be to scan the Clustered Index. But if we create one only on CreationDate, the optimizer can choose to use that index if it thinks doing a Key Lookup for the rest of the columns is cheaper than scanning the Clustered Index and applying a predicate.

CREATE INDEX ix_yourmom ON dbo.Votes(CreationDate)

For this query:

DECLARE @StartDate DATETIME = '2010-07-01';
DECLARE @EndDate DATETIME = '2010-07-02';

SELECT *
FROM   dbo.Votes AS v
WHERE  v.CreationDate BETWEEN @StartDate AND @EndDate;
GO

The cardinality estimate for unknown variables using between is 16.4317%. That leads to a clustered index scan and a missing index request for an index that covers the entire query.

If you run the query with RECOMPILE, you allow for the parameter embedding optimization.

DECLARE @StartDate DATETIME = '2010-07-01';
DECLARE @EndDate DATETIME = '2010-07-02';

SELECT *
FROM   dbo.Votes AS v
WHERE  v.CreationDate BETWEEN @StartDate AND @EndDate
OPTION ( RECOMPILE );

Which gives us a different query plan, and a more accurate estimate.

Hope this helps!

Best Answer

Related Solutions

Postgresql – Select consecutive number of rows starting from some number

Sql-server – How to speed up query on table with millions of rows

Related Question