Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005 SQL-Server , +
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
Achal Oza1.4K
Edited 22-Jan-13 18:05pm
v2
Comments
Tejas Vaishnav at 22-Jan-13 6:36am
   
Not clear what you want to do. please be more specified and give more detail
Achal Oza at 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 at 22-Jan-13 7:07am
   
Yes, Please specify more details.
Achal Oza at 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 at 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.
Achal Oza at 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 at 23-Jan-13 2:34am
   
And what have you tried so far? Where are you stuck?
URVISH_SUTHAR1 at 23-Jan-13 5:38am
   
I think you wanted to calc summary for 3-3 codes?
 
Is it correct ?
Rate this: bad
good
Please Sign up or sign in to vote.

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.
  Permalink  
Comments
Achal Oza at 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 at 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.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

TRY THIS
 
Suppose your table name "ABCD"
 
 
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
 
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
  Permalink  
v3
Comments
Achal Oza at 23-Jan-13 2:25am
   
This Static not Dynamic ...???
Tharaka MTR at 23-Jan-13 2:35am
   
what do you mean static and dynamic? give me more details how your steps work,
1-3, 3-5, 5-7 etc..??
without giving proper information how do we generate the sql?

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



Advertise | Privacy | Mobile
Web02 | 2.8.141022.2 | Last Updated 23 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100