Need to split the record based on value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need to split the record based on value

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2010-03-17 : 03:55:27

Hi, My table contains data as below.


RID	RSALES	RQUANTITY
168	 4560	198
156	 7890	200
162	 7645	250
168	 8765	560

I am looking for output as below.


RID	RSALES	RQUANTITY
168	 4560	198
156	 7890	200
162	 7645	200
162	 7645	50
168	 8765	200
168	 8765	200
168	 8765	160

if RQUANTITY > 200 for any of the records, I need to split the record. For example, the 4 records contains Rquantity as
250. so i divided in to 2 rows. And 5 record contains Rquantity as 560. so i divided each record in to 200.

How can i write a query for this reqt. Is this possible to write a query with out using programming lang?
Please help me in this regard

developer :)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-03-17 : 04:32:31

try like this


DECLARE @t TABLE (id INT IDENTITY(1,1),RID INT,rsales INT,	RQUANTITY INT)
INSERT INTO @t 
SELECT  168, 4560, 198 UNION ALL SELECT 
		156, 7890, 200 UNION ALL SELECT 
		162, 7645, 250 UNION ALL SELECT 
		168, 8765, 560

SELECT * FROM (
SELECT DISTINCT t.rid,t.rsales,
CASE WHEN  t.RQUANTITY <= number* 200 THEN 
	CASE WHEN t.RQUANTITY > 200 THEN 200 + (t.RQUANTITY-(number* 200))ELSE t.RQUANTITY END ELSE (number* 200) END
	AS RQUANTITY
FROM @t AS t 
INNER JOIN MASTER..spt_values AS m ON m.type = 'p' AND number > 0
WHERE m.number <= id ) s WHERE rquantity > 0

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-17 : 04:33:53

[code]
DECLARE @sample TABLE
(
RID int,
RSALES int,
RQUANTITY int
)
INSERT INTO @sample (RID, RSALES, RQUANTITY)
SELECT 168, 4560, 198 UNION ALL
SELECT 156, 7890, 200 UNION ALL
SELECT 162, 7645, 250 UNION ALL
SELECT 168, 8765, 560

DECLARE @MAX int

SELECT @MAX = 200

SELECT s.RID, s.RSALES, s.RQUANTITY,
QTY = CASE WHEN n.NUMBER = 1
AND s.RQUANTITY % @MAX <> 0
THEN s.RQUANTITY % @MAX
ELSE @MAX
END
FROM @sample s
CROSS APPLY dbo.F_TABLE_NUMBER_RANGE(1, (RQUANTITY - 1) / @MAX + 1) n
ORDER BY s.RID, s.RSALES, QTY DESC

/*
RID RSALES RQUANTITY QTY
----------- ----------- ----------- -----------
156 7890 200 200
162 7645 250 200
162 7645 250 50
168 4560 198 198
168 8765 560 200
168 8765 560 200
168 8765 560 160

(7 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-17 : 04:34:56

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 04:35:20

[code]

DECLARE @Tbl table
(
RID int,
RSALES int,
RQUANTITY int
)

INSERT @Tbl
SELECT 168 , 4560 ,198 UNION ALL
SELECT 156, 7890, 200 UNION ALL
SELECT 162, 7645, 250 UNION ALL
SELECT 168, 8765, 560 UNION ALL
SELECT 174, 3365, 785 UNION ALL
SELECT 192,3221,3654

SELECT RID,RSALES,RQUANTITY,CASE WHEN t1.number<> CEILING(RQUANTITY/200.0) THEN 200 ELSE RQUANTITY-(200* (t1.number-1)) END
FROM
@Tbl t
CROSS JOIN master..spt_values t1
WHERE t1.type='p'
AND t1.number BETWEEN 1 AND CEILING(RQUANTITY/200.0)

output
-------------------------------
RID RSALES RQUANTITY (No column name)
168 4560 198 198
156 7890 200 200
162 7645 250 200
162 7645 250 50
168 8765 560 200
168 8765 560 200
168 8765 560 160
174 3365 785 200
174 3365 785 200
174 3365 785 200
174 3365 785 185
192 3221 3654 200
192 3221 3654 200
192 3221 3654 200
192 3221 3654 200
192 3221 3654 200
192 3221 3654 200
192 3221 3654 200
192 3221 3654 200
192 3221 3654 200
192 3221 3654 200
192 3221 3654 200
192 3221 3654 200
192 3221 3654 200
192 3221 3654 200
192 3221 3654 200
192 3221 3654 200
192 3221 3654 200
192 3221 3654 200
192 3221 3654 54

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-17 : 04:35:43

quote:
Is this possible to write a query with out using programming lang?

Does T-SQL counts as a programming lang ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources