July 2, 2010 at 7:09 am
the main task is.
scenario:
assume if a production company take material for a weekly basis..
this week they have a stock 800 ton rawmaterial.
for daily basis they going to use only 100 ton for production,
when stock get inside the inventory is automatically want to be alloted for the daily basis.
eg :mon =100 ton,tue=100ton,wed=100ton,thur=100ton,fri=100 ton,sat=100 ton,sunday=company off,monday =100ton ,tue=100ton,wed=no stock in inventory..
output must be like this..can any one do this in sql by using cursor..
pls help me..
July 2, 2010 at 10:43 am
Why do you want to use a cursor?
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 2, 2010 at 1:51 pm
yes I agree with Mr.Gail.. You may not need cursor for this..
As per my understand.. simple triggers will work for you
🙂
July 2, 2010 at 2:42 pm
Ram:) (7/2/2010)
yes I agree with Mr.Gail.. You may not need cursor for this..As per my understand.. simple triggers will work for you
Whoa! Hold the phone! Triggers? For this? You'll have to show me that one, Ram.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 2, 2010 at 2:53 pm
rajendra prasad-377679 (7/2/2010)
the main task is.scenario:
assume if a production company take material for a weekly basis..
this week they have a stock 800 ton rawmaterial.
for daily basis they going to use only 100 ton for production,
when stock get inside the inventory is automatically want to be alloted for the daily basis.
eg :mon =100 ton,tue=100ton,wed=100ton,thur=100ton,fri=100 ton,sat=100 ton,sunday=company off,monday =100ton ,tue=100ton,wed=no stock in inventory..
output must be like this..can any one do this in sql by using cursor..
pls help me..
Take a look at the following code. If you don't know what a Tally table is or how it works to replace cursors and loops, then see the following article.
http://www.sqlservercentral.com/articles/T-SQL/62867/
--===== Declare a couple of obviously name variables
DECLARE @StartDate DATETIME,
@Quantity INT,
@DailyAmount INT
--===== Present the variables to the known values
SELECT @StartDate = '20100705',
@Quantity = 800,
@DailyAmount = 100
--===== Use a Tally table to produce the data in a set-based
-- fashion without a CURSOR.
SELECT @StartDate + (t.N-1) AS Date,
@DailyAmount AS Quantity
FROM dbo.Tally t
WHERE t.N BETWEEN 1 AND @Quantity/@DailyAmount
--Jeff Moden
Change is inevitable... Change for the better is not.
July 2, 2010 at 10:07 pm
Ram:) (7/2/2010)
yes I agree with Mr.Gail.. You may not need cursor for this..As per my understand.. simple triggers will work for you
Jeff already responded to the trigger comment, so I'll leave that alone.
However, Gail is one of the brightest women you will find on SSC or anywhere else, especially when it comes to things about SQL Server.
July 2, 2010 at 11:54 pm
Please post table definitions, sample data and desired output
Dear ,
THANKS FOR UR SUPPORT.
i want to do that by using cursor program not in trigger..
U CAN CREATE OWN TABLE AS U LIKE
table definitions.
for days USE datefunctions or varchar
tons must be get from the user
eg:if 800 tons means
The output must be like this
sun mon tue wed thur fri sat-------attributes
100100100100100100 ---values that passed automatically
OFF100100100 ----inventory values that passed automatically
if they try to retrieve information for thursday
/*select inventory values from tablename
where date='thur'
*/
this will raise error like this.
FOR THURSDAY = NO STOCK IN INVENTORY or stock does not exist./...
Kindly help me as soon as possible..
Thank u..
July 2, 2010 at 11:56 pm
hi dude
hey can u send me the sample table with queries by using trigger..
Regards
prasad
July 3, 2010 at 12:41 am
rajendra prasad-377679 (7/2/2010)
Please post table definitions, sample data and desired outputDear ,
THANKS FOR UR SUPPORT.
i want to do that by using cursor program not in trigger..
U CAN CREATE OWN TABLE AS U LIKE
table definitions.
for days USE datefunctions or varchar
tons must be get from the user
eg:if 800 tons means
The output must be like this
sun mon tue wed thur fri sat-------attributes
100100100100100100 ---values that passed automatically
OFF100100100 ----inventory values that passed automatically
if they try to retrieve information for thursday
/*select inventory values from tablename
where date='thur'
*/
this will raise error like this.
FOR THURSDAY = NO STOCK IN INVENTORY or stock does not exist./...
Kindly help me as soon as possible..
Thank u..
I'm sorry, but did you bother to read the article that Gail linked to in her port (it is the same article I reference below in my signature block regarding asking for help)? Without that information there really isn't much we can do to help you.
Also, please explain why it MUST be a cursor.
July 3, 2010 at 1:21 am
rajendra prasad-377679 (7/2/2010)
THANKS FOR UR SUPPORT.i want to do that by using cursor program not in trigger..
It's highly unlikely this needs a cursor and I don't see what a trigger has to do with anything. Triggers fire when data changes, you haven't mentioned anything about that
U CAN CREATE OWN TABLE AS U LIKE
Why should I spend time creating tables to solve your problem? Post the table definitions, some sample data and what the query should output and it will make it a lot easier for the volunteers here to produce a tested and working solution
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2010 at 1:45 am
hey linn,
i didnt know that much of using cursor,
frankly through niit only i learn sql.
there didnt teach me how to use cursor and everything..
now i am working in one organisation they ask me to write the program for that scenario using cursor..
that s y i ask help to the experts..like u and gail etc.
July 3, 2010 at 1:50 am
hey gail,
i didnt have any sample data and everything its just scenario which my TL gave to me.
i dont know how to use that..
he ask me to create own table as u like..
thats y i mentioned it to u..if any thing wrong means sorry for that..
i know only simple queries whatever in niit material..
that y i ask help through the experts..
can u teach me or give some material to become experts in sql server''
Regards
Rajendra prasad
July 3, 2010 at 2:58 am
You need to provide us with the table definition(s), sample data, expected results, and what codeyou have written so far to solve your problem.
You are going to find many volunteers here that are going to take the time to design our own tables, sample data, etc to help you solve a problem you can't even properly post yourself. We are volunteers, not paid employees. We help people wherewe can on our own time. If your lead told you to make up your own scenerio, then make it up and show us what you are trying to do.
Most likely, what ever your lead is asking you to accomplish with a cursor can be done using set based code instead.
July 3, 2010 at 3:26 am
rajendra prasad-377679 (7/3/2010)
hey gail,i didnt have any sample data and everything its just scenario which my TL gave to me.
Then I think you need to go back to your TL (Technical Lead?) and ask him for more information. Frankly, from the little you gave, I have no idea where to even start.
I can't believe that there's no existing table, unless this is a completely new system that you're supposed to be writing, in which case there should be a lot more information available on what is required.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2010 at 5:55 am
hey gail,
thanks a lot..
regards
Rajendra prasad
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply