Step by Step Sequence Number set in SQl Server Query

Question

5.00/5 (1 vote)

See more:

SQL-Server-2005

, +

Hi....
Hi..Frnd Good Morning

My Question Is:

ID Code Code Qty
1 A1 A1 1
2 A2 A2 1
3 A3 A3 1
4 A5 A5 1
5 A6 A6 1
6 A7 A7 1
7 A11 A11 1
8 A12 A12 1
9 A13 A13 1

Out Out This Range
ID From To Qty
1 A1 A3 3
2 A5 A7 3
3 A11 A13 3

Please Fast Replay me..
How to Possible in Query or Vb Code..????

Posted 22-Jan-13 0:23am

Abhai Oza

Updated 22-Jan-13 18:05pm

v2

Add a Solution

Comments

Tejas Vaishnav 22-Jan-13 6:36am

Not clear what you want to do. please be more specified and give more detail

Abhai Oza 22-Jan-13 23:57pm

Hi..Frnd Good Morning

My Question Is:

ID Code Code Qty
1 A1 A1 1
2 A2 A2 1
3 A3 A3 1
4 A5 A5 1
5 A6 A6 1
6 A7 A7 1
7 A11 A11 1
8 A12 A12 1
9 A13 A13 1

Out Out This Range
ID From To Qty
1 A1 A3 3
2 A5 A7 3
3 A11 A13 3

Please Fast Replay me..
How to Possible in Query or Vb Code..????

Tharaka MTR 22-Jan-13 7:07am

Yes, Please specify more details.

Abhai Oza 22-Jan-13 23:56pm

Hi..Frnd Good Morning

My Question Is:

ID Code Code Qty
1 A1 A1 1
2 A2 A2 1
3 A3 A3 1
4 A5 A5 1
5 A6 A6 1
6 A7 A7 1
7 A11 A11 1
8 A12 A12 1
9 A13 A13 1

Out Out This Range
ID From To Qty
1 A1 A3 3
2 A5 A7 3
3 A11 A13 3

Please Fast Replay me..
How to Possible in Query or Vb Code..????

Sandeep Mewara 22-Jan-13 9:35am

This is not a well framed question! We cannot work out what you are trying to do/ask from the post. Please elaborate and be specific.
Use the "Improve question" link to edit your question and provide better information.

Abhai Oza 22-Jan-13 23:56pm

Hi..Frnd Good Morning

My Question Is:

ID Code Code Qty
1 A1 A1 1
2 A2 A2 1
3 A3 A3 1
4 A5 A5 1
5 A6 A6 1
6 A7 A7 1
7 A11 A11 1
8 A12 A12 1
9 A13 A13 1

Out Out This Range
ID From To Qty
1 A1 A3 3
2 A5 A7 3
3 A11 A13 3

Please Fast Replay me..
How to Possible in Query or Vb Code..????

Sandeep Mewara 23-Jan-13 2:34am

And what have you tried so far? Where are you stuck?

URVISH_SUTHAR1 23-Jan-13 5:38am

I think you wanted to calc summary for 3-3 codes?

Is it correct ?

2 solutions

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)

RedDk · Answer 1 · 2013-01-22T08:16:00

Solution 1

Sure, that's easy:

Select SClrMemberCd,SMemberCd,SClientCd,SGroupId,BClrMemberCd,BMemberCd,BClientCd,BGroupId,
WRNOFrom, WRNOTo,W.MastName Warehouse,Rate,Qty,ExpiryDate,P.ProductName
From AllocationCallSub A,WarehouseMast W,ProductMast P
Where A.WarehouseId = W.MastId
AND A.ProductId = P.ProductId
ORDER BY [WRNOForm] ASC

Not including the ORDER BY statement results in seemingly random return of SELECT hits. This is a great example, imho, of why the ORDER BY isn't intuitively automatic or based upon a previous SELECT ... so many field members.

Posted 22-Jan-13 8:16am

RedDk

Comments

Abhai Oza 22-Jan-13 23:56pm

Hi..Frnd Good Morning

My Question Is:

ID Code Code Qty
1 A1 A1 1
2 A2 A2 1
3 A3 A3 1
4 A5 A5 1
5 A6 A6 1
6 A7 A7 1
7 A11 A11 1
8 A12 A12 1
9 A13 A13 1

Out Out This Range
ID From To Qty
1 A1 A3 3
2 A5 A7 3
3 A11 A13 3

Please Fast Replay me..
How to Possible in Query or Vb Code..????

RedDk 2-Feb-13 18:33pm

Wow,
That was some move my friend ... but the downvote tells me this question IS out of my league, so sorry can't help you.

Tharaka MTR · Answer 2 · 2013-01-22T20:07:00

TRY THIS

Suppose your table name "ABCD"

SQL

SELECT ROW_NUMBER() OVER(ORDER BY [FROM] ASC) , [FROM]= 'A' + CAST([FROM] AS VARCHAR) , [TO] = 'A' + CAST([TO] AS VARCHAR), SUM(QTY) AS QTY
FROM (
SELECT 
(CASE
	WHEN Code ='A1' OR Code ='A2' OR Code='A3' THEN 1
	WHEN Code ='A5' OR Code ='A6' OR Code='A7' THEN 5
	WHEN Code ='A11' OR Code ='A12' OR Code='A13' THEN 11
END) AS 'FROM',
(CASE
	WHEN Code ='A1' OR Code ='A2' OR Code='A3' THEN 5
	WHEN Code ='A5' OR Code ='A6' OR Code='A7' THEN 7
	WHEN Code ='A11' OR Code ='A12' OR Code='A13' THEN 13
END) AS 'TO'
,Qty
FROM ABCD) t
GROUP BY t.[FROM],t.[To]

OR

SQL

SELECT ROW_NUMBER() OVER(ORDER BY [FROM] ASC) AS ID , [FROM]= 'A' + CAST([FROM] AS VARCHAR) , [TO] = 'A' + CAST([TO] AS VARCHAR), SUM(QTY) AS QTY
FROM (
SELECT 
(CASE
	WHEN SubString(Code, PatIndex('%[0-9.-]%', Code),LEN(Code)) BETWEEN 1 AND 3 THEN 1
	WHEN SubString(Code, PatIndex('%[0-9.-]%', Code),LEN(Code)) BETWEEN 5 AND 7 THEN 5
	WHEN SubString(Code, PatIndex('%[0-9.-]%', Code),LEN(Code)) BETWEEN 11 AND 13 THEN 11	
END) AS 'FROM',
(CASE
	WHEN SubString(Code, PatIndex('%[0-9.-]%', Code),LEN(Code)) BETWEEN 1 AND 3 THEN 5
	WHEN SubString(Code, PatIndex('%[0-9.-]%', Code),LEN(Code)) BETWEEN 5 AND 7 THEN 7
	WHEN SubString(Code, PatIndex('%[0-9.-]%', Code),LEN(Code)) BETWEEN 11 AND 13 THEN 13
END) AS 'TO'
,Qty
FROM ABCD) t
GROUP BY t.[FROM],t.[To]

FROM To   QTY
---- ---- -----------
A11  A13  3
A1   A5   3
A5   A7   3

Step by Step Sequence Number set in SQl Server Query

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us