August 17, 2009 at 5:09 am
I have to create a report for aging and i am getting data as
Qty Age
-5 0-30Days
7 31-60Days
3 61-90 Days
but i want data as
Qty Age
0 0-30Days
5 31-60Days
0 61-90 Days
that means if i get negative value than that should be substracted from the oldest one and if oldest one becomes 0 then it should look out for the second last and so on.
if anyone can provide the SQL query that will be really helpful
Please help
Thanks in advance
Ravi
August 17, 2009 at 5:43 am
Ravi (8/17/2009)
I have to create a report for aging and i am getting data asQty Age
-5 0-30Days
7 31-60Days
3 61-90 Days
but i want data as
Qty Age
0 0-30Days
5 31-60Days
0 61-90 Days
that means if i get negative value than that should be substracted from the oldest one and if oldest one becomes 0 then it should look out for the second last and so on.
if anyone can provide the SQL query that will be really helpful
Please help
Thanks in advance
Ravi
it's not entirely clear what you are doing, but it sounds like you're running a recursive algorithm. if that's the case, then your best bet is to either write a recursive stored proc, or just have a statement that returns true until your condition is satisfied, and then within that statement, loop through each iteration of the procedure.
August 17, 2009 at 5:46 am
Please take the time to help us help you.
In order to get a solution to the problem you described some of us like to have ready to use data (including table definition, sample data and expected results based on the sample).
Please follow the link in my signature on how to post sample data.
August 17, 2009 at 7:58 pm
I have attached the sample data and expected output
let me try to explain what i need
say for a particular city ,street i get negative quantity for particular age then that should become 0 and that negative value should be substracted from the oldest value
for example if 0-30 days qty = -4 , 31-60 days qty = 4 and 61-90 days qty =2 then the expected output is 0-30days =0 , 31-60days =2 and 61-90 days =0
sum should remain same
see if this is helpful for you
Regards,
Ravi
August 17, 2009 at 8:29 pm
Ravi (8/17/2009)
I have attached the sample data and expected outputlet me try to explain what i need
It's only slightly better. You still haven't provided it in the requested format. You're missing the DDL for the tables and the insert statements for the data.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 17, 2009 at 9:08 pm
I have attached the create statment and statment to insert data.
Please let me know if anything else is required
Regards,
Ravi
August 17, 2009 at 9:22 pm
Not sure, but this is looking more like FIFO Inventory problem than an Aging problem.
From looking at the sample data in the spreadsheet, you are deduction the (-n) from the previous (n) values for each product. Is this a fair assessment of what I saw in a cursory exam of the data?
August 17, 2009 at 9:25 pm
Yes this has to be FIFO , and its similar to inventory
August 18, 2009 at 5:15 am
Ravi (8/17/2009)
for example if 0-30 days qty = -4 , 31-60 days qty = 4 and 61-90 days qty =2 then the expected output is 0-30days =0 , 31-60days =2 and 61-90 days =0
so, if i understand your request correctly, if you have this situation:
0-30 days = -4
31-60 days = 6
61-90 days = 2
then your output should be
0-30 days = 0
31-60 days = 4
61-90 days = 2
and, if it was
0-30 days = -4
31-60 days = 2
61-90 days = 6
then the output would be
0-30 days = 0
31-60 days = 2
61-90 days = 2
what about if the input was
0-30 days = -2
31-60 days = -2
61-90 days = 6
would the output be
0-30 days = 0
31-60 days = 0
61-90 days = 2
?
August 18, 2009 at 6:06 am
The 2nd and 3rd case are correct but some minor mistake in 1st ..let me correct it
if the input is
0-30 days = -4
31-60 days = 6
61-90 days = 2
then your output should be
0-30 days = 0
31-60 days = 4
61-90 days = 0
i really need the Sql query for this. please help
Regards,
Ravi
August 18, 2009 at 6:50 am
I'm sure someone will come up with a more elegant solution, but this should work for you. if the total negative numbers add up to more than the total positive numbers though, what should happen? for now, the result set is going to look a bit odd, but an easy solution is just to set them all to 0 in this case, which is just adding an extra update at the end.
Input Data:
DECLARE @TestTable TABLE
(
ID INT IDENTITY PRIMARY KEY,
DateRange VARCHAR(100),
Total INT
)
INSERT INTO @TestTable (DateRange, Total)
SELECT '0-30 days', -4
UNION
SELECT '31-60 days', -2
UNION
SELECT '61-90 days', 8
UNION
SELECT '91-120 days', 3
IDDateRangeTotal
10-30 days-4
231-60 days-2
361-90 days8
491-120 days3
Code:
DECLARE @MinTotal INT
DECLARE @MaxTotal INT
DECLARE @ID INT
SET @MinTotal = 0
SET @MaxTotal = 0
SET @ID = 0
WHILE 1 = 1
BEGIN
SELECT @ID = ID, @MinTotal = Total
FROM @TestTable
ORDER BY Total DESC
SELECT @MaxTotal = MAX(Total)
FROM @TestTable
-- Finished with all the negative numbers
IF ISNULL(@MinTotal, 0) >= 0 OR ISNULL(@MaxTotal, 0) 0
ORDER BY ID DESC
IF @@rowcount = 0 BREAK
UPDATE @TestTable
SET Total = (CASE WHEN Total + @MinTotal > 0 THEN Total + @MinTotal ELSE 0 END)
WHERE ID = @ID
SET @MinTotal = @MinTotal + @MaxTotal
IF @MinTotal > 0 BREAK
END
END
Output data:
IDDateRangeTotal
10-30 days0
231-60 days0
361-90 days5
491-120 days0
August 18, 2009 at 7:22 am
Thanks for the Query , ill surely try this
meanwhile if any one has any other solution ,please provide
Regards,
Ravi
August 19, 2009 at 9:27 am
it works when we have 1 item ,but i need for multiple items and city
Item likes A,B,C
and thr is also City
Can u please help
Regards,
Ravi
August 19, 2009 at 9:31 am
i don't understand what you mean. you're going to have to provide some sample data in a more clear fashion for me adapt the query for you.
on that note - you really should do this all in your first post, so you don't waste people's time re-working their solutions to fit your changing needs.
August 19, 2009 at 9:32 am
Ravi (8/18/2009)
Thanks for the Query , ill surely try thismeanwhile if any one has any other solution ,please provide
Regards,
Ravi
How about you give it a try first. Show us what you have done to make it work for multiple items, etc. By the way, perhaps you should have provided more realistic data to begin with and you may have gotten a better answer as well.
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply