split up data by days - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

split up data by days

Author

Topic

uri dimant
Starting Member

16 Posts

Posted - 2001-08-14 : 03:33:25

CREATE TABLE dbo.PURCHASES
(
SaleAmount money NOT NULL,
SaleTime datetime NOT NULL
) ON [PRIMARY]
GO

So, we have the following data:

Insert into PURCHASES (SaleAmount, SaleTime) values (6.24, 'Jan 1, 2001 8:22
am')
Insert into PURCHASES (SaleAmount, SaleTime) values (22.29, 'Jan 1, 2001
10:42 pm')
Insert into PURCHASES (SaleAmount, SaleTime) values (128.40, 'Jan 2, 2001
11:12 am')
Insert into PURCHASES (SaleAmount, SaleTime) values (.45, 'Jan 3, 2001 6:28
pm')
Insert into PURCHASES (SaleAmount, SaleTime) values (16.32, 'Jan 4, 2001
11:41 am')
Insert into PURCHASES (SaleAmount, SaleTime) values (9.11, 'Jan 6, 2001 6:30
pm')
Insert into PURCHASES (SaleAmount, SaleTime) values (69.96, 'Jan 6, 2001
6:55 pm')
Insert into PURCHASES (SaleAmount, SaleTime) values (6.99, 'Jan 6, 2001 8:23
pm')
Insert into PURCHASES (SaleAmount, SaleTime) values (18.43, 'Jan 7, 2001
11:42 am')

I have a statement basically doing this:

select sum(saleamount) as DaySaleTotal, cast(datepart(dd, saletime) as
nvarchar) + ', ' + cast(datepart(m, saletime) as nvarchar) as Day from
purchases
group by cast(datepart(dd, saletime) as nvarchar) + ', ' +
cast(datepart(m, saletime) as nvarchar)

returns

DaySaleTotal Day
--------------- -----------
28.5300 1, 1
128.4000 2, 1
.4500 3, 1
16.3200 4, 1
86.0600 6, 1
18.4300 7, 1

But I want to get a summary of the sales in the course of a period of time
INCLUDING days that don't have sales in them, returning results like this:

DaySaleTotal Day
--------------- -----------
28.5300 1, 1
128.4000 2, 1
.4500 3, 1
16.3200 4, 1
0 5, 1 <<
86.0600 6, 1
18.4300 7, 1

I'll use the results from this new resultset for doing things such as
averaging over all days, etc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources