August 19, 2009 at 9:44 am
Sorry for not providing the details.. i have udated the excel sheet
if i am trying with the existing code ..its showing 1 for other items and the count is also not correct for the 1st item
Regards,
Ravi
August 19, 2009 at 9:47 am
What changes to the code have you tried to make it work for multiple items? That is what I want to see. Where are you having problems. That way we can help you learn instead of just doing it for you.
August 19, 2009 at 9:50 am
i added itemid=@itemID in the below part
UPDATE #temp1
SET Quantity = 0
WHERE ID = @ID
and ItemID = @ItemID
and removed the top 1 from
BEGIN
SELECT @ItemID = ItemID, @MaxTotal = Total
FROM #temp1
Pleas help ...
August 19, 2009 at 10:22 am
Ravi,
I'm not sure what you're after but if you want to aged a column you would use this statement.
datediff(day,[column name2], [column name1]) AS [Days Aged]
I don't know if this helps or not....
Michelle
August 19, 2009 at 10:53 am
well that's totally different than what you asked. the procedure i gave you isn't going to work for having different items to go with.
you've now got a triple-nested iteration there. you want to treat all the items separately, and then all the cities separately.
if you want to use the code that i gave you, you can still do it, but you'll have to nest it properly. IE, first loop through all the items, then loop through all the cities, then loop through all the quantities. for each city, update only the records for that city.
not the most efficient approach by far, but itt'l work.
Oh - and what are you going to do in this situation:
100-45 DaysANJPQR4
846-60 DaysANJPQR-5
661-90 DaysANJPQR3
261-90 DaysANJPQR2
461-90 DaysANJPQR3
Which one of the three records at 61-90 are you going to bring the -5 to? arbitrarily choose one?
PS - if anyone else wants to help this guy out further, here's some code you can use to create his dataset. you REALLY should present things like this to people. an excel spreadsheet is worthless.
DECLARE @TempTestTable TABLE
(
ID INT IDENTITY PRIMARY KEY,
[Date] SMALLDATETIME,
Item VARCHAR(10),
City VARCHAR(10),
Street VARCHAR(10),
Quantity INT
)
INSERT INTO @TempTestTable ([Date], Item, City, Street, Quantity)
SELECT '2009-07-10', 'A', 'NY', 'ABC', -4
UNION
SELECT '2009-07-05', 'A', 'NJ', 'PQR', 4
UNION
SELECT '2009-07-01', 'A', 'NY', 'XYZ', 2
UNION
SELECT '2009-06-30', 'A', 'NJ', 'PQR', -5
UNION
SELECT '2009-06-23', 'A', 'NY', 'ABC', 5
UNION
SELECT '2009-06-09', 'A', 'NJ', 'PQR', 3
UNION
SELECT '2009-06-04', 'A', 'NY', 'ABC', 2
UNION
SELECT '2009-05-31', 'A', 'NJ', 'PQR', 3
UNION
SELECT '2009-05-27', 'A', 'NJ', 'PQR', 2
UNION
SELECT '2009-05-25', 'A', 'NY', 'XYZ', 1
UNION
SELECT '2009-07-05', 'B', 'NY', 'ABC', -2
UNION
SELECT '2009-05-30', 'B', 'NY', 'ABC', 3
DECLARE @TestTable TABLE
(
ID INT IDENTITY PRIMARY KEY,
DateRange VARCHAR(100),
Item VARCHAR(10),
City VARCHAR(10),
Street VARCHAR(10),
Quantity INT
)
INSERT INTO @TestTable (DateRange, Item, City, Street, Quantity)
SELECT
(CASE
WHEN DATEDIFF(dd, GETDATE(), [Date]) BETWEEN -45 AND 0 THEN '0-45 Days'
WHEN DATEDIFF(dd, GETDATE(), [Date]) BETWEEN -60 AND -46 THEN '46-60 Days'
WHEN DATEDIFF(dd, GETDATE(), [Date]) BETWEEN -90 AND -61 THEN '61-90 Days'
ELSE '>90 Days'
END),
Item, City, Street, Quantity
FROM @TempTestTable
IDDateRangeItemCityStreetQuantity
161-90 DaysANYXYZ1
261-90 DaysANJPQR2
361-90 DaysBNYABC3
461-90 DaysANJPQR3
561-90 DaysANYABC2
661-90 DaysANJPQR3
746-60 DaysANYABC5
846-60 DaysANJPQR-5
946-60 DaysANYXYZ2
100-45 DaysANJPQR4
110-45 DaysBNYABC-2
120-45 DaysANYABC-4
August 19, 2009 at 10:54 am
Any idea how we can achive this ...
Regards,
Ravi
August 19, 2009 at 11:02 am
You do realize we are volunteers with jobs of our own. We give of our time as we have time available. If this is a critical issue, seems like you may want to look for help within your organization. You really can't hold us to getting things done for you under your time constraints.
August 19, 2009 at 11:11 am
Ravi,
Try to thoughly understand what the user requirment is and the objective and don't assume what you think the user needs. You might be making it harder then it really needs to be. Then break it down in simplier components in your temp table and grow upon that. That's what I try to do when I'm really stuck on a problem. Or bump heads with other colleague, they might give you a new perspective on the problem.
Good luck.
Michelle
August 19, 2009 at 11:13 am
Sorry sir , i didnt mean that
August 19, 2009 at 11:15 am
Michelle ,
Thanks for your suggestion
Regards,
Ravi
August 19, 2009 at 11:28 am
You shouldn't be so gumpy Lynn... Remember back in the days when you were working on a problem and you just couldn't figure it out and your boss has got you under the gun because it wanted it like, yesterday?! OK, I'll get off my soapbox now...
Ravi,
Please restate what the user requirement is and the objective and maybe we can start from there.
Michelle 🙂
August 19, 2009 at 11:39 am
here's the deal -
what you're trying to accomplish, at least from what i've understood, is really not something you should be doing from a T-SQL level.
T-SQL handles iterative things "alright", but it's defiinately not designed to do it like that.
what i'd recommend is that you take your result set, and then use something like .NET to write a program to handle the results.
if you really want to use T-SQL, then like i said, try the script which i gave you, and modify it to iterate through the items then iterate through the cities.
as simple pseudocode,
FOR EACH Item IN RawData
FOR EACH city in Item
RunScript()
NEXT
NEXT
where RunScript() is a script similar to what i gave you, although needing to be modified.
August 19, 2009 at 11:49 am
mm (8/19/2009)
You shouldn't be so gumpy Lynn... Remember back in the days when you were working on a problem and you just couldn't figure it out and your boss has got you under the gun because it wanted it like, yesterday?! OK, I'll get off my soapbox now...Ravi,
Please restate what the user requirement is and the objective and maybe we can start from there.
Michelle 🙂
Sure I do. One, I didn't have SSC to jump on and ask for help. Two, I had a wonderful group of coworkers that could go to and ask for help and were more than willing and able to do do so. That is where he should look to first as they are right there and have access to everything he does.
So, really, I'm not being so grumpy. Just trying to make sure Ravi realizes we aren't here to do his job, but to help him learn. Crunch time isn't the time to be relying on the internet to get work done. Plus, had he provided the full spec upfront, he probably would have had a working solution already.
Also, we need to guide him away from multiple nested loops, not really scalable and he hasn't stated how many records this needs to run against.
August 19, 2009 at 11:49 am
Hey Michelle 🙂
If you didn't already know, Lynn is renowned for his patience to the point that he has often been called Saint Lynn by the regulars at SSC. I was about to get up on a soapbox, but cut off the last two paragraphs because it would add nothing to this discussion.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 19, 2009 at 12:05 pm
Michelle,
The user requirement is basically to create an Aging report
I am able to calculate the age of the product , but say when i get negative value in particular age ..then that i need to get substracted from last value , like FIFO
now say for example if input is
Age= 0-30 Days, Item = A, City =NY , Street=ABC , Qty = -4
Age= 0-30 Days, Item = B, City =NY , Street=ABC , Qty =-2
Age= 31-60 Days, Item = A, City =NY, Street=Abc, Qty =5
Age= 31-60 Days, Item = B, City =NY, Street=ABC, Qty =1
Age= 61-90 Days, Item = A, City =NY, Street=ABC, Qty = 2
Age= 61-90 Days, Item = B, City =NY, Street=ABC, Qty = 2
Then the output should be
Age= 0-30 Days, Item = A, City =NY , Street=ABC , Qty = 0
Age= 0-30 Days, Item = B, City =NY , Street=ABC , Qty =0
Age= 31-60 Days, Item = A, City =NY, Street=Abc, Qty =3
Age= 31-60 Days, Item = B, City =NY, Street=ABC, Qty =1
Age= 61-90 Days, Item = A, City =NY, Street=ABC, Qty = 0
Age= 61-90 Days, Item = B, City =NY, Street=ABC, Qty = 0
Similarly i can hve same itemid in different cities , but the deduction should be done from the same city ...say City NJ and Item C should be substracted from City NJ and Item C only
I have also attached the excel sheet
Regards,
Ravi
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply