January 20, 2014 at 10:40 pm
Hi Friends,
My Table looks:
===========
create table billed
(
InVoice_no varchar(20),
model_ref varchar(20),
item_no int,
item_type char(20),
bill_qty int
)
insert into billed
(
InVoice_no,
model_ref,
item_no,
item_type,
bill_qty
)
values
(
'TN/0002/13-14',
'-',
'5000320',
'I',
'250'
)
insert into billed
(
InVoice_no,
model_ref,
item_no,
item_type,
bill_qty
)
values
(
'TN/0002/13-14',
'-',
'6000350',
'K',
'10'
)
insert into billed
(
InVoice_no,
model_ref,
item_no,
item_type,
bill_qty
)
values
(
'TN/0002/13-14',
'6000350'',
'5000420',
'X',
'12'
)
insert into billed
(
InVoice_no,
model_ref,
item_no,
item_type,
bill_qty
)
values
(
'TN/0002/13-14',
'6000350'',
'5000450',
'X',
'3'
)
(like i ve 1000 records are there in my table)
here ITem_Type='I' Directly billed prodcuct,
ITem_Type='K' Group Product billed,
ITem_Type='X' item are available in that group
Now My Expecting o/p:
=================
invoice_no model_ref item_no item_type sales free
TN/0002/13-14 - 5000320 I 250 0
TN/0002/13-14 6000350 5000420 X 10 2
TN/0002/13-14 6000350 5000450 X 0 3
conditions for O/P:
-----------------
1)here example of 6000350 has billed for 10 items .but in that 5000420=12,5000450=3 already ve in that group.
the highest value in group has been show (like line no 2 of my expecting o/p.) sales =10(group of billed) remaining=2(free)
lowest value has shown (like line no 3 of my expecting o/p.)
sales =0(group of billed) Actual = 3.
How to Make code sql server 2000?
January 21, 2014 at 12:00 am
You might want to check out Jeff Moden's excellent article on Running Totals http://www.sqlservercentral.com/articles/T-SQL/68467/
After doing this, if you have any more problems, come on back
January 21, 2014 at 2:17 am
I can't tell what you are trying to do here because the numbers and the descriptions don't appear to match up. It doesn't make sense. Let's restructure your sample data and expected output to see if it clarifies the position:
CREATE TABLE billed (
InVoice_no varchar(20),
model_ref varchar(20),
item_no int,
item_type char(20),
bill_qty int)
INSERT INTO billed (InVoice_no, model_ref, item_no, item_type, bill_qty)
SELECT 'TN/0002/13-14', '-', '5000320', 'I', 250 UNION ALL -- ITem_Type='I' Directly billed prodcuct
SELECT 'TN/0002/13-14', '-', '6000350', 'K', 10 UNION ALL -- ITem_Type='K' Group Product billed
SELECT 'TN/0002/13-14', '6000350', '5000420', 'X', 12 UNION ALL -- ITem_Type='X' item are available in that group
SELECT 'TN/0002/13-14', '6000350', '5000450', 'X', 3
SELECT * FROM billed
-- Now My Expecting o/p:
;WITH ExpectedOutput (invoice_no, model_ref, item_no, item_type, sales, free) AS (
SELECT 'TN/0002/13-14', '-', '5000320', 'I', 250, 0 UNION ALL
SELECT 'TN/0002/13-14', '6000350', '5000420', 'X', 10, 2 UNION ALL
SELECT 'TN/0002/13-14', '6000350', '5000450', 'X', 0, 3
)
SELECT * FROM ExpectedOutput
Nope. Your expected output still makes no sense to me at all. You appear to have "stock availability" data mixed up with your "billed" data, and in your output you are attempting to balance up stock sold against availability - but only for some rows. Where did 250 units come from?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 21, 2014 at 5:25 am
Hi friends,
as the same table how to separate lowest value only.
January 21, 2014 at 7:43 am
raghuldrag (1/21/2014)
Hi friends,as the same table how to separate lowest value only.
What does that mean? You seem to be very rich in needing help but very poor in providing any details. We are really good at t-sql but unless you give us enough information we can't help you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 21, 2014 at 10:11 pm
its all on assumptions really ... i hope i get close
Item No : 6000350
Item Type : 'K' means its a Group of items, which means
if this item no shows in model_ref column , its shows item which are in this group ... if this is right
then following will be true as well ...
i-e
6000350 have available Qty 10.
demanded/ordered qty is 12, 3 respectfully, so 10 qty consumes by item no : 5000420 its remaning qty is 2 which is called 'FreeQty' in sample, and because there is nothing left for item no : 5000450 so it remains 3.
am i close enough ?
January 21, 2014 at 10:43 pm
its correct twin .devil
January 21, 2014 at 10:55 pm
twin.devil (1/21/2014)
You might want to check out Jeff Moden's excellent article on Running Totals http://www.sqlservercentral.com/articles/T-SQL/68467/After doing this, if you have any more problems, come on back
As i mentioned about the running total solution, this solution is based on that. furthermore, this is only related to the sample data you shared earlier. so here is goes.
IF exists ( select * from sys.objects where name = 'billed' and type = 'U')
DROP TABLE billed
GO
create table billed
(
InVoice_no varchar(20)
, model_ref varchar(20)
, item_no int
, item_type char(20)
, bill_qty int
)
GO
-- ITem_Type='I' Directly billed prodcuct
-- ITem_Type='K' Group Product billed
-- ITem_Type='X' item are available in that group
INSERT INTO BILLED
SELECT 'TN/0002/13-14', '-', '5000320', 'I', 250UNION ALL
SELECT 'TN/0002/13-14', '-', '6000350', 'K', 10UNION ALL
SELECT 'TN/0002/13-14', '6000350', '5000420', 'X', 12UNION ALL
SELECT 'TN/0002/13-14', '6000350', '5000450', 'X', 3
GO
-------------------
CREATE TABLE #TMP
(ID INT NOT NULL IDENTITY(1,1)
, INVOICE_NO VARCHAR(20)
, MODEL_REF VARCHAR(20)
, ITEM_NO VARCHAR(20)
, ITEM_TYPE CHAR(1)
, GRP_BILL_QTYINT
, BILL_QTYINT
, REDUCETOTAL INT
, PRIMARY KEY CLUSTERED ([ID] ASC, MODEL_REF ASC)
)
INSERT INTO #TMP
SELECT B.INVOICE_NO
, B.MODEL_REF, B.ITEM_NO, B.ITEM_TYPE
, BB.BILL_QTY
, B.BILL_QTY
, NULL
FROM BILLED B
LEFT JOIN BILLED BB ON B.INVOICE_NO = BB.INVOICE_NO
AND B.MODEL_REF = BB.ITEM_NO
AND BB.ITEM_TYPE = 'K'
WHERE B.ITEM_TYPE = 'X'
DECLARE @PREVMODELREFVARCHAR(20) = 0
DECLARE @BALANCERUNNINGTOTALINT = 0
UPDATE #TMP
SET @BALANCERUNNINGTOTAL = REDUCETOTAL = CASE WHEN MODEL_REF = @PREVMODELREF
THEN CASE WHEN @BALANCERUNNINGTOTAL > 0
THEN @BALANCERUNNINGTOTAL - BILL_QTY
ELSE 0 END
ELSE (GRP_BILL_QTY - BILL_QTY) END
, @PREVMODELREF = MODEL_REF
FROM #TMP WITH (TABLOCKX)
OPTION (MAXDOP 1);
SELECT INVOICE_NO, MODEL_REF, ITEM_NO, ITEM_TYPE, BILL_QTY, 0 AS Remaing_Qty
FROM BILLED
WHERE (ITEM_TYPE = 'I')
UNION ALL
SELECT INVOICE_NO, MODEL_REF, ITEM_NO, ITEM_TYPE
, CASE WHEN REDUCETOTAL <> 0 THEN BILL_QTY + REDUCETOTAL ELSE 0 END
, CASE WHEN REDUCETOTAL = 0 THEN BILL_QTY ELSE ABS(REDUCETOTAL) END
FROM #TMP
DROP TABLE #TMP
hope it help you get on the track ... 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply