Minimum Value

Question

Post reply

Minimum Value

balu.arunkumar 758

Grasshopper

Points: 12
More actions
December 19, 2013 at 10:06 am

#280468

Hi
I have 3.19 Cr data in a Product table. A Customer can purchase no.of goods in a day. Every day new customer can come. This table contains CustomerID, Purchase date also. I need new customer's first purchase date from above table. I have used min() & row_number() fn to get the data. But it will take high CPU utilization. To reduce CPU uitilization any other ways available?? Pls help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Hi and welcome to the forums. In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements

2. Sample data in the form of INSERT INTO statements

3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

balu.arunkumar 758 Grasshopper Points: 12 More actions · Answer 2

Hi,

Pls find the details.

Base Table:

CustomerIDGoodsPurschased Date

1001Ball20131210013618

1001Bat20131210020000

1002Tennis Bat20131210091055

1002Foot Ball20131210111130

1002Glouse20131210141015

1003Bat20131210081015

1004Tennis Ball20131210211015

1004Stump20131210151015

Expected Output:

CustomerIDGoodsPurschased Date

1001Ball2013-12-10 01:36:18.000

1002Tennis Bat2013-12-10 09:10:55.000

1003Bat2013-12-10 08:10:15.000

1004Stump2013-12-10 15:10:15.000

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Can you set up the ddl for your base table please, as Sean suggested? This allows folks to work on your problem straight away. You do the easy bits, we do the tricky bits 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mark Eckeard Hall of Fame Points: 3462 More actions · Answer 4

It may also help if you post your current query and why you think it takes too much cpu utilization.

Mark

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Since you are pretty new around here and the Holiday Spirit took over I put this together for you as an example of how you should post your tables and data.

if OBJECT_ID('tempdb..#Sales') is not null

drop table #Sales

create table #Sales

(

CustomerID int,

GoodsPurchased varchar(25),

PurchaseDate datetime

)

insert #Sales

select 1001, 'Ball', '2013-12-10 01:36:18' union all

select 1001, 'Bat', '2013-12-10 02:00:00' union all

select 1002, 'Tennis Bat', '2013-12-10 09:10:55' union all

select 1002, 'Foot Ball', '2013-12-10 11:11:30' union all

select 1002, 'Glouse', '2013-12-10 14:10:15' union all

select 1003, 'Bat', '2013-12-10 08:10:15' union all

select 1004, 'Tennis Ball', '2013-12-10 21:10:15' union all

select 1004, 'Stump', '2013-12-10 15:10:15';

with SortedResults as

(

select CustomerID, GoodsPurchased, PurchaseDate, ROW_NUMBER() over (Partition by CustomerID order by PurchaseDate) as RowNum

from #Sales

)

select CustomerID, GoodsPurchased, PurchaseDate

from SortedResults

where RowNum = 1

According to your original post this should return what you are looking for. However, you also stated that you did something like this already?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/