Grouping by date

Transact-SQL
1

Hi

I have this code that sorts a list of orders by date

USE [DB1.Test]
Select CreatedAt,Identifier,RoundedPriceSum from StarOrder
where SiteID = 1
and OrderType <>2
and CreatedAt between '2015/01/01' and '2015/08/20'

Is it possible to group so that each date between the date ranges provides the number of orders and a combined total of RoundedPriceSum

2

Even though you did not provide sample data and sample output, I would think that you could use

USE [DB1.Test]
SELECT COUNT(*), SUM(RoundedPriceSum) 
FROM StarOrder
WHERE SiteID = 1
    AND OrderType <>2
    AND CreatedAt BETWEEN '2015/01/01' AND '2015/08/20';
1 Like
3

If you want combined total and sum for each date. Try this!

USE [DB1.Test]
SELECT COUNT(*), SUM(RoundedPriceSum)
FROM StarOrder
WHERE SiteID = 1
AND OrderType <>2
AND CreatedAt BETWEEN '2015/01/01' AND '2015/08/20'
GROUP BY CreatedAt

4 
Select Dateadd(Day, Datediff(Day, 0, CreatedAt), 0) As CreatedDate,
    Count(*) As OrderCount, Sum(RoundedPriceSum) As SumRoundedPriceSum
From StarOrder
Where SiteID = 1
and OrderType <>2
and CreatedAt >= '20150101'
and CreatedAt < '20150821'
Group by Dateadd(Day, Datediff(Day, 0, CreatedAt), 0)
--Order by Dateadd(Day, Datediff(Day, 0, CreatedAt), 0)
5

As 20-August is TODAY I wonder whether you mean to include today, or not ... either way I think that BETWEEN is a bit risky in this sort of situation, there is an Edge Condition that other people (even you in a couple of years time!!) might misinterpret, so I would recommend Scotts approach:

    MyColumn >= @SomeStartPoint
AND MyColumn < @SomeEndPoint_PlusOne

Note Scott's string dates as 'yyyymmdd' with no punctuation. Pretty much anything else is ambiguous and SQL will parse it differently depending on various settings - which can include the Language of the currently connected user, which might not be something you are expecting!

Scotts is the only solution, so far, that handles you request for "each date between the date ranges provides ..."

6

P.S. If CreatedAt is DATE datatype, rathre than DATETIME datatype, then you can replace the (three ocurrences of)

Dateadd(Day, Datediff(Day, 0, CreatedAt), 0) As CreatedDate

with just plain

CreatedDate
7

Hi

Please find ur solution

;
WITH abc_cte
AS (
	SELECT CreatedAt
		,count(orderType) AS countOrders
		,sum(PriceSum) AS sumPriceSum
	FROM #SampleData
	WHERE OrderType <> 2
		AND CreatedAt BETWEEN '2015/01/01'
			AND '2015/08/20'
	GROUP BY CreatedAt
	)
SELECT CreatedAt
	,countOrders
	,sum(sumPriceSum) OVER () AS RoundedPriceSum
FROM abc_cte

Please find sample data below
/*
IF OBJECT_ID('tempdb.dbo.#SampleData', 'U') IS NOT NULL
drop table #SampleData;
GO

create table #SampleData
(
CreatedAt datetime null,
orderType int null,
PriceSum int null
)
GO

insert into #SampleData select '2015-01-01',4,10
insert into #SampleData select '2015-01-01',3,6
insert into #SampleData select '2015-01-01',1,2

insert into #SampleData select '2015-03-11',5,1
insert into #SampleData select '2015-03-11',3,3
insert into #SampleData select '2015-03-11',5,2
insert into #SampleData select '2015-03-11',3,6

insert into #SampleData select '2015-06-03',4,1
insert into #SampleData select '2015-06-03',4,3
insert into #SampleData select '2015-06-03',1,2
insert into #SampleData select '2015-06-03',3,6
GO

select * from #SampleData
GO
*/

8

Typically order times will not always be exactly midnight :grin: Try it with this sample data:

insert into #SampleData select '20150101 03:16:24',4,10
insert into #SampleData select '20150101 09:52:21',3,6
insert into #SampleData select '20150101 16:33;08',1,2

9

You should only use 'yyyymmdd' for string dates, anything else is ambiguous and open to being parsed differently to how you expect.

Try this:

SET LANGUAGE 'French'
GO
SELECT CONVERT(datetime, '2015/08/20')