February 10, 2014 at 4:22 am
Hi,
I have a table set of records. Its contains some customerID,SportsGoods,Price in different datetime.
i want to add customer spent. If crossed 1000 means i have to show purchase time when it is crossed 1000. I need query without while and looping.
Example:
Customer Name Goods Price DatePurchased
A Bat 250 1/31/2014
A Ball 22 1/31/2014
B Carrom Board 475 2/2/2014
C Tennis Ball 50 2/1/2014
A Football 150 2/2/2014
D Bat 250 1/31/2014
B Ball 22 1/31/2014
A Hockey Bat 125 2/4/2014
C Chess 55 2/4/2014
A Volley Ball 55 2/4/2014
February 10, 2014 at 4:39 am
I'm sorry but I did not understand the requirement. Could you please explain it further ?
Additionally please try and provide create, insert statements for the sample data and the output snapshot that you are looking for..
February 10, 2014 at 5:02 am
Hi,
Pls find the below details
Create table #sample(Customer_Name varchar(50),Goods varchar(50),
Price float,DatePurchased datetime)
insert into #sample
select 'A','Bat',250,'1/31/2014'
select 'A','Ball',250,'1/31/2014'
select 'B','Carrom',850,'2/2/2014'
select 'C','TennisBall',250,'2/1/2014'
select 'A','Bat',250,'2/2/2014'
select 'D','Bat',250,'1/31/2014'
select 'B','Bat',250,'1/31/2014'
select 'A','Bat',250,'2/4/2014'
select 'C','Chess',250,'2/4/2014'
select 'A','Bat',250,'2/4/2014'
select 'C','Chess',250,'2/4/2014'
Output Format:
Customer_Name Total_Price DatePurchased
A 1000 2/4/2014
B 1100 1/31/2014
February 10, 2014 at 5:19 am
Something like this..
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER( PARTITION BY Customer_Name ORDER BY DatePurchased ) AS RN
FROM (
SELECT *, SUM( Price ) OVER( PARTITION BY Customer_Name ORDER BY DatePurchased ) AS Total_Till_Date
FROM #sample
) AS S
WHERE Total_Till_Date >= 1000
) AS T
WHERE RN = 1
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 10, 2014 at 5:32 am
Would something like following do ?
SELECT Customer_Name,SUM(price) as Total_price,MAX(DatePurchased) as DatePurchased FROM #sample
GROUP BY Customer_Name HAVING SUM(price) >= 1000
February 10, 2014 at 5:38 am
Hi This part shows error.
/*------------------------
SELECT *, SUM( Price ) OVER( Partition BY Customer_Name ORDER BY DatePurchased) AS Total_Till_Date
FROM #sample where Customer_Name='A'
------------------------*/
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'order'.
February 10, 2014 at 5:49 am
Hi Chetan,
Thank u. But Total_price and DatePurchased date showing wrong.
February 10, 2014 at 6:13 am
You are welcome.
is the query correct ? I mean is it what you were looking for ? if yes, then the output that it is generating is as per the data present in the table. If you want something different like say you want to know sum(price) per customer per goods >= 1000 then you'll have to add Goods in the group by condition and that should solve the problem. But that won't tell you for what good you are seeing this result in taht case add it(goods) in the SELECT column list .
February 10, 2014 at 6:16 am
hi TRY THIS ONE:
;with cte as
(
select CUSTOMER_NAME, SUM(PRICE) AS TOTAL_PRICE , max(DATEPURCHASED)DATEPURCHASED, ROW_NUMBER() OVER (PARTITION BY CUSTOMER_NAME ORDER BY CUSTOMER_NAME) RN
FROM #SAMPLE
GROUP BY CUSTOMER_NAME
HAVING SUM(PRICE) > 1000
)
SELECT
DISTINCT S.CUSTOMER_NAME, C.TOTAL_PRICE, C.DATEPURCHASED
FROM #SAMPLE S
JOIN cte c ON S.CUSTOMER_NAME = c.CUSTOMER_nAME
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 10, 2014 at 6:37 am
balu.arunkumar (2/10/2014)
Hi This part shows error./*------------------------
SELECT *, SUM( Price ) OVER( Partition BY Customer_Name ORDER BY DatePurchased) AS Total_Till_Date
FROM #sample where Customer_Name='A'
------------------------*/
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'order'.
Which version of SQL Server are you using? Kingston's solution works in SQL Server 2012.
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
February 10, 2014 at 6:41 am
ChrisM@Work (2/10/2014)
balu.arunkumar (2/10/2014)
Hi This part shows error./*------------------------
SELECT *, SUM( Price ) OVER( Partition BY Customer_Name ORDER BY DatePurchased) AS Total_Till_Date
FROM #sample where Customer_Name='A'
------------------------*/
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'order'.
Which version of SQL Server are you using? Kingston's solution works in SQL Server 2012.
But I thought that it will give the error:
Column '#SAMPLE.DatePurchased' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 10, 2014 at 6:49 am
kapil_kk (2/10/2014)
ChrisM@Work (2/10/2014)
balu.arunkumar (2/10/2014)
Hi This part shows error./*------------------------
SELECT *, SUM( Price ) OVER( Partition BY Customer_Name ORDER BY DatePurchased) AS Total_Till_Date
FROM #sample where Customer_Name='A'
------------------------*/
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'order'.
Which version of SQL Server are you using? Kingston's solution works in SQL Server 2012.
But I thought that it will give the error:
Column '#SAMPLE.DatePurchased' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Which GROUP BY clause? Kingston's written a running total using a window function 😉
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
February 10, 2014 at 6:52 am
ChrisM@Work (2/10/2014)
kapil_kk (2/10/2014)
ChrisM@Work (2/10/2014)
balu.arunkumar (2/10/2014)
Hi This part shows error./*------------------------
SELECT *, SUM( Price ) OVER( Partition BY Customer_Name ORDER BY DatePurchased) AS Total_Till_Date
FROM #sample where Customer_Name='A'
------------------------*/
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'order'.
Which version of SQL Server are you using? Kingston's solution works in SQL Server 2012.
But I thought that it will give the error:
Column '#SAMPLE.DatePurchased' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Which GROUP BY clause? Kingston's written a running total using a window function 😉
He uses * in the SELECT statement and use aggreagate function only on Price columns so it will give the error for rest of the columns
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 10, 2014 at 7:15 am
kapil_kk (2/10/2014)
ChrisM@Work (2/10/2014)
kapil_kk (2/10/2014)
ChrisM@Work (2/10/2014)
balu.arunkumar (2/10/2014)
Hi This part shows error./*------------------------
SELECT *, SUM( Price ) OVER( Partition BY Customer_Name ORDER BY DatePurchased) AS Total_Till_Date
FROM #sample where Customer_Name='A'
------------------------*/
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'order'.
Which version of SQL Server are you using? Kingston's solution works in SQL Server 2012.
But I thought that it will give the error:
Column '#SAMPLE.DatePurchased' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Which GROUP BY clause? Kingston's written a running total using a window function 😉
He uses * in the SELECT statement and use aggreagate function only on Price columns so it will give the error for rest of the columns
The SUM function works differently when used with an OVER clause.
Please check the link below for more information
http://technet.microsoft.com/en-us/library/ms187810.aspx
This behaviour is explained under the heading Using the OVER clause
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 10, 2014 at 9:37 am
1) Balu, you were asked explicitly what version of SQL Server you were running this on. You posted to a SQL 2012 forum, and the code provided DOES WORK on that version.
2) When providing sample data, you need to make it executable. Your INSERT statement actually will just INSERT ONE row and then SELECT out the rest to the SSMS window. It should look like this (provided for others to use to actually populate the temp table):
insert into #sample
select 'A','Bat',250,'1/31/2014'
UNION ALL
select 'A','Ball',250,'1/31/2014'
UNION ALL
select 'B','Carrom',850,'2/2/2014'
UNION ALL
select 'C','TennisBall',250,'2/1/2014'
UNION ALL
select 'A','Bat',250,'2/2/2014'
UNION ALL
select 'D','Bat',250,'1/31/2014'
UNION ALL
select 'B','Bat',250,'1/31/2014'
UNION ALL
select 'A','Bat',250,'2/4/2014'
UNION ALL
select 'C','Chess',250,'2/4/2014'
UNION ALL
select 'A','Bat',250,'2/4/2014'
UNION ALL
select 'C','Chess',250,'2/4/2014'
3) I am not sure the query provided will solve your actual need or not. It does provide the DAY that the running total crosses 1000, and the running total amount for THAT day, but if there are additional inputs later it doesn't provide for that (which you may not need - I am uncertain). For example, I get this output for YOUR test data as given:
Customer_Name Goods Price DatePurchased Total_Till_Date RN
-------------- -------- ------ -------------- ---------------- ---
A Bat 250 2014-02-04 1250 1
B Carrom 850 2014-02-02 1100 1
If you add in this record:
insert into #sample
select 'A','Bat',250,'2/6/2014'
you get the exact same output, but I could certainly see the requirement being THIS output (which takes into account additional entries but KEEPS the date you crossed the 1000 threshold):
Customer_Name Goods Price DatePurchased Total_Till_Date RN
-------------- -------- ------ -------------- ---------------- ---
A Bat 250 2014-02-04 1500 1
B Carrom 850 2014-02-02 1100 1
I would think TWO date ouputs would be helpful above - the first being the date 1000 was crossed and the second being the date of the last purchase (2/6/14 in my example).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply