Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

t-sql counting date records

Posted on 2016-08-26
2
Medium Priority
?
76 Views
Last Modified: 2016-08-26
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?
0
Comment
Question by:maqskywalker
2 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 41772443
>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
 
LVL 1

Author Closing Comment

by:maqskywalker
ID: 41772478
Thanks.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Loops Section Overview
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

782 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question