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 1:23am
Achal Oza1.4K
Edited 22-Jan-13 19: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)

  Print Answers RSS
0 OriginalGriff 274
1 Sergey Alexandrovich Kryukov 255
2 Shweta N Mishra 216
3 PIEBALDconsult 200
4 BillWoodruff 174
0 OriginalGriff 7,630
1 Sergey Alexandrovich Kryukov 7,022
2 DamithSL 5,586
3 Manas Bhardwaj 4,946
4 Maciej Los 4,525


Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | 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