Sql server query grouping problem

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

hi all,

i have issue with sql data processing ,my actual table DayWiseCount data is given below..

DepotCode	RouteCode	RouteName	TotalSale	DayNames	DayNumber
113	113007	JEDS 01 SNACKS	55.9944	Saturday	1
113	113007	JEDS 01 SNACKS	139.986	Saturday	1
113	113007	JEDS 01 SNACKS	120	Saturday	1
113	113007	JEDS 01 SNACKS	150	Saturday	1
113	113007	JEDS 01 SNACKS	150	Saturday	1
113	113007	JEDS 01 SNACKS	60	Saturday	1
113	113007	JEDS 01 SNACKS	60	Saturday	1
113	113007	JEDS 01 SNACKS	30	Saturday	1
113	113007	JEDS 01 SNACKS	200	Saturday	1
113	113007	JEDS 01 SNACKS	900	Saturday	1
113	113007	JEDS 01 SNACKS	600	Sunday	2
113	113007	JEDS 01 SNACKS	450	Sunday	2
113	113007	JEDS 01 SNACKS	990	Sunday	2
113	113007	JEDS 01 SNACKS	60	Sunday	2
113	113007	JEDS 01 SNACKS	60	Sunday	2
113	113007	JEDS 01 SNACKS	405	Sunday	2
113	113007	JEDS 01 SNACKS	498	Sunday	2
113	113007	JEDS 01 SNACKS	855	Sunday	2
113	113007	JEDS 01 SNACKS	225	Sunday	2
113	113007	JEDS 01 SNACKS	990	Sunday	2
113	113007	JEDS 01 SNACKS	810	Sunday	2
113	113007	JEDS 01 SNACKS	990	Sunday	2
113	113007	JEDS 01 SNACKS	990	Sunday	2
113	113007	JEDS 01 SNACKS	60	Sunday	2
113	113007	JEDS 01 SNACKS	45	Sunday	2
113	113007	JEDS 01 SNACKS	60	Sunday	2
113	113007	JEDS 01 SNACKS	210	Sunday	2
113	113007	JEDS 01 SNACKS	150	Sunday	2
113	113007	JEDS 01 SNACKS	60	Sunday	2
113	113007	JEDS 01 SNACKS	60	Sunday	2
113	113007	JEDS 01 SNACKS	900	Sunday	2
113	113007	JEDS 01 SNACKS	90	Sunday	2
113	113007	JEDS 01 SNACKS	630	Sunday	2
113	113007	JEDS 01 SNACKS	90	Sunday	2
113	113007	JEDS 01 SNACKS	100	Sunday	2
113	113007	JEDS 01 SNACKS	315	Sunday	2
113	113007	JEDS 01 SNACKS	45	Sunday	2
113	113007	JEDS 01 SNACKS	900	Sunday	2
113	113007	JEDS 01 SNACKS	45	Sunday	2
113	113007	JEDS 01 SNACKS	90	Sunday	2
113	113007	JEDS 01 SNACKS	135	Sunday	2
113	113007	JEDS 01 SNACKS	90	Sunday	2
113	113007	JEDS 01 SNACKS	90	Sunday	2
113	113007	JEDS 01 SNACKS	180	Sunday	2
113	113007	JEDS 01 SNACKS	90	Sunday	2
113	113007	JEDS 01 SNACKS	45	Sunday	2
113	113007	JEDS 01 SNACKS	90	Sunday	2
113	113008	JEDS 02 SNACKS	13.9986	Saturday1
113	113008	JEDS 02 SNACKS	60	Saturday1
113	113008	JEDS 02 SNACKS	60	Saturday1
113	113008	JEDS 02 SNACKS	60	Saturday1
113	113008	JEDS 02 SNACKS	60	Saturday1
113	113008	JEDS 02 SNACKS	60	Saturday1
113	113008	JEDS 02 SNACKS	135	Sunday	2
113	113008	JEDS 02 SNACKS	180	Sunday	2
113	113008	JEDS 02 SNACKS	45	Sunday	2
113	113008	JEDS 02 SNACKS	30	Sunday	2
113	113008	JEDS 02 SNACKS	1095	Sunday	2
113	113008	JEDS 02 SNACKS	2190	Sunday	2
113	113008	JEDS 02 SNACKS	1980	Sunday	2
113	113008	JEDS 02 SNACKS	300	Sunday	2
113	113008	JEDS 02 SNACKS	210	Sunday	2
113	113008	JEDS 02 SNACKS	360	Sunday	2
113	113008	JEDS 02 SNACKS	975	Sunday	2
113	113008	JEDS 02 SNACKS	420	Sunday	2

but i need to process this table and result will be like that

DepotCode	RouteCode	RouteName	TotalSale	DayNames	DayNumber	record
113	113007	JEDS 01 SNACKS	55.9944	Saturday 1	1
113	113007	JEDS 01 SNACKS	139.986	Saturday 1	2
113	113007	JEDS 01 SNACKS	120	Saturday 1	3
113	113007	JEDS 01 SNACKS	150	Saturday 1	4
113	113007	JEDS 01 SNACKS	150	Saturday 1	5
113	113007	JEDS 01 SNACKS	60	Saturday 1	6
113	113007	JEDS 01 SNACKS	60	Saturday 1	7
113	113007	JEDS 01 SNACKS	30	Saturday 1	8
113	113007	JEDS 01 SNACKS	200	Saturday 1	9
113	113007	JEDS 01 SNACKS	900	Saturday 1	10
113	113007	JEDS 01 SNACKS	600	Sunday	2	1
113	113007	JEDS 01 SNACKS	450	Sunday	2	2
113	113007	JEDS 01 SNACKS	990	Sunday	2	3
113	113007	JEDS 01 SNACKS	60	Sunday	2	4
113	113007	JEDS 01 SNACKS	60	Sunday	2	5
113	113007	JEDS 01 SNACKS	405	Sunday	2	6
113	113007	JEDS 01 SNACKS	498	Sunday	2	7
113	113007	JEDS 01 SNACKS	855	Sunday	2	8
113	113007	JEDS 01 SNACKS	225	Sunday	2	9
113	113007	JEDS 01 SNACKS	990	Sunday	2	10
113	113007	JEDS 01 SNACKS	810	Sunday	2	11
113	113007	JEDS 01 SNACKS	990	Sunday	2	12
113	113007	JEDS 01 SNACKS	990	Sunday	2	13
113	113007	JEDS 01 SNACKS	60	Sunday	2	14
113	113007	JEDS 01 SNACKS	45	Sunday	2	15
113	113007	JEDS 01 SNACKS	60	Sunday	2	16
113	113007	JEDS 01 SNACKS	210	Sunday	2	17
113	113007	JEDS 01 SNACKS	150	Sunday	2	18
113	113007	JEDS 01 SNACKS	60	Sunday	2	19
113	113007	JEDS 01 SNACKS	60	Sunday	2	20
113	113007	JEDS 01 SNACKS	900	Sunday	2	21
113	113007	JEDS 01 SNACKS	90	Sunday	2	22
113	113007	JEDS 01 SNACKS	630	Sunday	2	23
113	113007	JEDS 01 SNACKS	90	Sunday	2	24
113	113007	JEDS 01 SNACKS	100	Sunday	2	25
113	113007	JEDS 01 SNACKS	315	Sunday	2	26
113	113007	JEDS 01 SNACKS	45	Sunday	2	27
113	113007	JEDS 01 SNACKS	900	Sunday	2	28
113	113007	JEDS 01 SNACKS	45	Sunday	2	29
113	113007	JEDS 01 SNACKS	90	Sunday	2	30
113	113007	JEDS 01 SNACKS	135	Sunday	2	31
113	113007	JEDS 01 SNACKS	90	Sunday	2	32
113	113007	JEDS 01 SNACKS	90	Sunday	2	33
113	113007	JEDS 01 SNACKS	180	Sunday	2	34
113	113007	JEDS 01 SNACKS	90	Sunday	2	35
113	113007	JEDS 01 SNACKS	45	Sunday	2	36
113	113007	JEDS 01 SNACKS	90	Sunday	2	37
113	113008	JEDS 02 SNACKS	13.9986	Saturday 1	1
113	113008	JEDS 02 SNACKS	60	Saturday 1	2
113	113008	JEDS 02 SNACKS	60	Saturday 1	3
113	113008	JEDS 02 SNACKS	60	Saturday 1	4
113	113008	JEDS 02 SNACKS	60	Saturday 1	5
113	113008	JEDS 02 SNACKS	60	Saturday 1	6
113	113008	JEDS 02 SNACKS	135	Sunday	2	1
113	113008	JEDS 02 SNACKS	180	Sunday	2	2
113	113008	JEDS 02 SNACKS	45	Sunday	2	3
113	113008	JEDS 02 SNACKS	30	Sunday	2	4
113	113008	JEDS 02 SNACKS	1095	Sunday	2	5
113	113008	JEDS 02 SNACKS	2190	Sunday	2	6
113	113008	JEDS 02 SNACKS	1980	Sunday	2	7
113	113008	JEDS 02 SNACKS	300	Sunday	2	8
113	113008	JEDS 02 SNACKS	210	Sunday	2	9
113	113008	JEDS 02 SNACKS	360	Sunday	2	10
113	113008	JEDS 02 SNACKS	975	Sunday	2	11
113	113008	JEDS 02 SNACKS	420	Sunday	2	12

please help me how can i do that ....i spend 2-4 hours but i am still at start point

thanks

Posted 30-Oct-11 1:26am

mohd vaquas

Add a Solution

Comments

Mehdi Gholam 30-Oct-11 7:36am

Can you explain what you want?

Bala Selvanayagam 30-Oct-11 7:38am

Its hard to figure out what exactly you need by looking at the lengthy datasets, explain in simple terms and clearly what you need

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Amir Mahfoozi · Accepted Answer · 2011-10-30T03:30:00

Solution 1

SQL

SELECT DepotCode, RouteCode, RouteName , TotalSale, DayNames, DayNumber ,ROW_NUMBER() OVER(PARTITION BY DepotCode, DayNumber  ORDER BY DepotCode, DayNumber) AS 'record'
FROM DayWiseCount

If it solved you problem please accept it as an answer or vote it up. ;)

Posted 30-Oct-11 3:30am

Amir Mahfoozi

Updated 30-Oct-11 3:33am

v3

Comments

Espen Harlinn 30-Oct-11 15:44pm

5'ed :)

Amir Mahfoozi 31-Oct-11 1:07am

Thank you