t-sql counting date records

hi experts,

I'm using sql server 2008.

I have a table that looks like this:

my table
Here is script to create this table.

CREATE TABLE [dbo].[TestOrders](
	[CustomerNo] [int] NULL,
	[OrderNo] [int] NULL,
	[OrderDate] [datetime] NULL,
	[ItemOrdered] [nvarchar](255) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TestOrders] ([CustomerNo], [OrderNo], [OrderDate], [ItemOrdered]) VALUES (1201, 1245, CAST(0x0000A64900000000 AS DateTime), N'envelopes')
INSERT [dbo].[TestOrders] ([CustomerNo], [OrderNo], [OrderDate], [ItemOrdered]) VALUES (1403, 1456, CAST(0x0000A64B00000000 AS DateTime), N'pencils')
INSERT [dbo].[TestOrders] ([CustomerNo], [OrderNo], [OrderDate], [ItemOrdered]) VALUES (1403, 1899, CAST(0x0000A64C00000000 AS DateTime), N'pens')
INSERT [dbo].[TestOrders] ([CustomerNo], [OrderNo], [OrderDate], [ItemOrdered]) VALUES (1403, 1987, CAST(0x0000A64D00000000 AS DateTime), N'erasers')
INSERT [dbo].[TestOrders] ([CustomerNo], [OrderNo], [OrderDate], [ItemOrdered]) VALUES (1101, 2002, CAST(0x0000A64F00000000 AS DateTime), N'ink')
INSERT [dbo].[TestOrders] ([CustomerNo], [OrderNo], [OrderDate], [ItemOrdered]) VALUES (1101, 2102, CAST(0x0000A65000000000 AS DateTime), N'printer paper')
INSERT [dbo].[TestOrders] ([CustomerNo], [OrderNo], [OrderDate], [ItemOrdered]) VALUES (1101, 2102, CAST(0x0000A65100000000 AS DateTime), N'pencils')
INSERT [dbo].[TestOrders] ([CustomerNo], [OrderNo], [OrderDate], [ItemOrdered]) VALUES (1101, 2234, CAST(0x0000A65200000000 AS DateTime), N'ball point pen')
INSERT [dbo].[TestOrders] ([CustomerNo], [OrderNo], [OrderDate], [ItemOrdered]) VALUES (578, 2568, CAST(0x0000A65300000000 AS DateTime), N'pencils')
INSERT [dbo].[TestOrders] ([CustomerNo], [OrderNo], [OrderDate], [ItemOrdered]) VALUES (578, 2756, CAST(0x0000A65400000000 AS DateTime), N'pens')

Open in new window


My desired result looks like this:

desired result
Notice the column called OrderDays that is the one i need to to create.
The OrderDays column is the count of OrderDate items for each CustomerNo.

For example, if you look at CustomerNo 1403. That customer placed an order on 7/22/2016, another order on 7/23/16 and another order on 7/24/16.  So on the OrderDays column, on 7/22/2016 that was the first day he placed an order so it gets 1, on 7/23/2016 that was the second day he placed an order so it gets 2,
on 7/24/2016 that was the third day he placed an order so it gets 3,

Any idea of the syntax to create the OrderDays column?
LVL 1
maqskywalkerAsked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>The OrderDays column is the count of OrderDate items for each CustomerNo.
Using the windowing function ROW_NUMBER will handle this.

SELECT CustomerNo, OrderNo, OrderDate, ItemOrdered, 
   ROW_NUMBER() OVER (PARTITION BY CustomerNo ORDER BY OrderNo) as OrderDays
FROM TestOrders
ORDER BY OrderDate

Open in new window

0
 
maqskywalkerAuthor Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.