March 19, 2013 at 10:56 am
I have written a stored procedure. as of now it inserts 10 percent for every user as below.
orderid processid uid ordervalue perwet(percent wieghtage)
1 1 1 10000 10
1 1 2 10000 10
1 1 3 10000 10
I want that if more than 1 users are involved in 1 process in same order it should divide the percent equally
that means it must insert like this
orderid processid uid ordervalue perwet(percent weightage)
1 1 1 10000 3.33
1 1 2 10000 3.33
1 1 3 10000 3.33
structure of tables
CREATE TABLE [dbo].[temp_calpoints1](
[orderid] [int] NULL,
[processid] [int] NULL,
[uid] [int] NULL,
[ordervalue] [bigint] NULL,
[perwet] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[process](
[processid] [int] NULL,
[processdesc] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[perwet] [int] NULL
) ON [PRIMARY]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[CalculatePointsAllorder]
@fromDate varchar(10), @toDate varchar(10)
AS
BEGIN
print @fromdate;
print @todate;
delete from temp_calpoints1;
delete from temp_users;
delete from temp_OrderMaster;
insert into temp_users
SELECT uid FROM UserInfo where exists (select * from
OrderMaster where UserInfo.uid = OrderMaster.uid
);
insert into temp_OrderMaster
select * from OrderMaster where orderlogindate between
@fromDate and @toDate
DECLARE @t_orderid int
DECLARE @t_processid int
DECLARE @t_uid int
DECLARE @t_ordervalue bigint
DECLARE db_cursor CURSOR FOR SELECT orderid FROM temp_OrderMaster;
--select orderid from OrderMaster where CONVERT(VARCHAR(10),orderlogindate,110) between
--@fromDate and @toDate;
--where orderlogindate
--between @fromDate and @toDate;
DECLARE db_cursor1 CURSOR FOR SELECT processid FROM process;
--DECLARE db_cursor2 CURSOR FOR select uid from temp_users;
DECLARE db_cursor2 CURSOR FOR select uid from userinfo;
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @t_orderid
WHILE @@FETCH_STATUS = 0
BEGIN
--print 'order '
-- PRINT @t_orderid;
OPEN db_cursor1
FETCH NEXT FROM db_cursor1 INTO @t_processid
WHILE @@FETCH_STATUS = 0
BEGIN
--print 'process*******'
--PRINT @t_processid;
OPEN db_cursor2
FETCH NEXT FROM db_cursor2 INTO @t_uid
WHILE @@FETCH_STATUS = 0
BEGIN
--print '***'
--print 'user'
--print @t_uid
--print @t_processid
--print '***'
if @t_processid = 1
begin
--print 'in processid '
--print @t_uid
--print @t_processid
--print '***'
set @t_ordervalue = 0;
--insert into temp_calpoints1 (ordervalue) values(
select @t_ordervalue = ordervalue
-- @t_uid = b.uid
from temp_OrderMaster a,EnquiryMaster b where
a.EnquiryId = b.enquiryid and b.uid = @t_uid and a.orderid = @t_orderid
--)
if @t_ordervalue <> 0
insert into temp_calpoints1 (orderid,processid,uid,ordervalue)
values(@t_orderid,@t_processid,@t_uid,@t_ordervalue)
end
if @t_processid = 2
begin
set @t_ordervalue = 0;
--insert into temp_calpoints1 (ordervalue) values(
select @t_ordervalue = ordervalue --@t_uid = b.uid
from temp_OrderMaster a,Requirement b where
a.requirementid = b.RequirementID and b.uid = @t_uid and a.orderid = @t_orderid
--)
if @t_ordervalue <> 0
insert into temp_calpoints1 (orderid,processid,uid,ordervalue)
values(@t_orderid,@t_processid,@t_uid,@t_ordervalue)
end
if @t_processid = 3
begin
set @t_ordervalue = 0;
--insert into temp_calpoints1 (ordervalue) values(
select @t_ordervalue = ordervalue
--,@t_uid = b.uid
from temp_OrderMaster a,Proposal b where
a.proposalid = b.proposalid and b.uid = @t_uid and a.orderid = @t_orderid
--)
if @t_ordervalue <> 0
insert into temp_calpoints1 (orderid,processid,uid,ordervalue)
values(@t_orderid,@t_processid,@t_uid,@t_ordervalue)
end
if @t_processid = 4
begin
set @t_ordervalue = 0;
--insert into temp_calpoints1 (ordervalue) values(
select @t_ordervalue = ordervalue
--,@t_uid = uid
from temp_OrderMaster where
orderid = @t_orderid and uid = @t_uid
--)
if @t_ordervalue <> 0
insert into temp_calpoints1 (orderid,processid,uid,ordervalue)
values(@t_orderid,@t_processid,@t_uid,@t_ordervalue)
end
if @t_processid = 5
begin
set @t_ordervalue = 0;
--insert into temp_calpoints1 (ordervalue) values(
select @t_ordervalue = ordervalue
--,@t_uid = b.uid
from temp_OrderMaster a,OrderVendor b where
b.orderid = @t_orderid and b.uid = @t_uid
--)
if @t_ordervalue <> 0
insert into temp_calpoints1 (orderid,processid,uid,ordervalue)
values(@t_orderid,@t_processid,@t_uid,@t_ordervalue)
end
if @t_processid = 6
begin
set @t_ordervalue = 0;
--set @t_uid = 0;
print 'in processid 6 '
print @t_uid;
PRINT @t_orderid;
--insert into temp_calpoints1 (ordervalue) values(
select @t_ordervalue = ordervalue
-- ,@t_uid = b.uid
from temp_OrderMaster a,CollectionFollowUp b where
b.orderid = @t_orderid and b.uid = @t_uid
--)
print @t_ordervalue ;
if @t_ordervalue <> 0
insert into temp_calpoints1 (orderid,processid,uid,ordervalue)
values(@t_orderid,@t_processid,@t_uid,@t_ordervalue)
end
FETCH NEXT FROM db_cursor2 INTO @t_uid
END
CLOSE db_cursor2
FETCH NEXT FROM db_cursor1 INTO @t_processid
END
CLOSE db_cursor1
FETCH NEXT FROM db_cursor INTO @t_orderid
END
CLOSE db_cursor
update temp_calpoints1 set perwet = (select perwet
from process
where
processid=temp_calpoints1.processid)
END
--update query set on count of group by claue from another table in sql
--select count(*) from temp_calpoints1 group by processid,orderid
--EXEC [CalculatePointsAllorder] @fromDate = '2012-09-10' ,@toDate = '2013-11-13'
--select * from temp_calpoints1 order by orderid,processid,uid
--select * from temp_OrderMaster
March 19, 2013 at 12:07 pm
Hi and welcome to SSC. It seems that you didn't really post everything needed for somebody to help here. There are a number of tables missing in your ddl that are present in your stored proc.
It is entirely unclear what this proc is trying to do but nesting cursors is not going to be the best approach here. Cursors and loops are notoriously slow in sql server.
I took the liberty of running your code through a formatter so it is a little easier to read. Unfortunately your code appears to be a work in progress and I don't know what you are trying to accomplish.
Here are the create table scripts.
CREATE TABLE [dbo].[temp_calpoints1] (
[orderid] [int] NULL
,[processid] [int] NULL
,[uid] [int] NULL
,[ordervalue] [bigint] NULL
,[perwet] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[process] (
[processid] [int] NULL
,[processdesc] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,[perwet] [int] NULL
) ON [PRIMARY]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
And here is the proc.
IF object_id('CalculatePointsAllorder') IS NOT NULL
DROP PROCEDURE CalculatePointsAllorder
GO
CREATE PROCEDURE [dbo].[CalculatePointsAllorder] @fromDate VARCHAR(10)
,@toDate VARCHAR(10)
AS
BEGIN
PRINT @fromdate;
PRINT @todate;
DELETE
FROM temp_calpoints1;
DELETE
FROM temp_users;
DELETE
FROM temp_OrderMaster;
INSERT INTO temp_users
SELECT uid
FROM UserInfo
WHERE EXISTS (
SELECT *
FROM OrderMaster
WHERE UserInfo.uid = OrderMaster.uid
);
INSERT INTO temp_OrderMaster
SELECT *
FROM OrderMaster
WHERE orderlogindate BETWEEN @fromDate
AND @toDate
DECLARE @t_orderid INT
DECLARE @t_processid INT
DECLARE @t_uid INT
DECLARE @t_ordervalue BIGINT
DECLARE db_cursor CURSOR
FOR
SELECT orderid
FROM temp_OrderMaster;
--select orderid from OrderMaster where CONVERT(VARCHAR(10),orderlogindate,110) between
--@fromDate and @toDate;
--where orderlogindate
--between @fromDate and @toDate;
DECLARE db_cursor1 CURSOR
FOR
SELECT processid
FROM process;
--DECLARE db_cursor2 CURSOR FOR select uid from temp_users;
DECLARE db_cursor2 CURSOR
FOR
SELECT uid
FROM userinfo;
OPEN db_cursor
FETCH NEXT
FROM db_cursor
INTO @t_orderid
WHILE @@FETCH_STATUS = 0
BEGIN
--print 'order '
-- PRINT @t_orderid;
OPEN db_cursor1
FETCH NEXT
FROM db_cursor1
INTO @t_processid
WHILE @@FETCH_STATUS = 0
BEGIN
--print 'process*******'
--PRINT @t_processid;
OPEN db_cursor2
FETCH NEXT
FROM db_cursor2
INTO @t_uid
WHILE @@FETCH_STATUS = 0
BEGIN
--print '***'
--print 'user'
--print @t_uid
--print @t_processid
--print '***'
IF @t_processid = 1
BEGIN
--print 'in processid '
--print @t_uid
--print @t_processid
--print '***'
SET @t_ordervalue = 0;
--insert into temp_calpoints1 (ordervalue) values(
SELECT @t_ordervalue = ordervalue
-- @t_uid = b.uid
FROM temp_OrderMaster a
,EnquiryMaster b
WHERE a.EnquiryId = b.enquiryid
AND b.uid = @t_uid
AND a.orderid = @t_orderid
--)
IF @t_ordervalue <> 0
INSERT INTO temp_calpoints1 (
orderid
,processid
,uid
,ordervalue
)
VALUES (
@t_orderid
,@t_processid
,@t_uid
,@t_ordervalue
)
END
IF @t_processid = 2
BEGIN
SET @t_ordervalue = 0;
--insert into temp_calpoints1 (ordervalue) values(
SELECT @t_ordervalue = ordervalue --@t_uid = b.uid
FROM temp_OrderMaster a
,Requirement b
WHERE a.requirementid = b.RequirementID
AND b.uid = @t_uid
AND a.orderid = @t_orderid
--)
IF @t_ordervalue <> 0
INSERT INTO temp_calpoints1 (
orderid
,processid
,uid
,ordervalue
)
VALUES (
@t_orderid
,@t_processid
,@t_uid
,@t_ordervalue
)
END
IF @t_processid = 3
BEGIN
SET @t_ordervalue = 0;
--insert into temp_calpoints1 (ordervalue) values(
SELECT @t_ordervalue = ordervalue
--,@t_uid = b.uid
FROM temp_OrderMaster a
,Proposal b
WHERE a.proposalid = b.proposalid
AND b.uid = @t_uid
AND a.orderid = @t_orderid
--)
IF @t_ordervalue <> 0
INSERT INTO temp_calpoints1 (
orderid
,processid
,uid
,ordervalue
)
VALUES (
@t_orderid
,@t_processid
,@t_uid
,@t_ordervalue
)
END
IF @t_processid = 4
BEGIN
SET @t_ordervalue = 0;
--insert into temp_calpoints1 (ordervalue) values(
SELECT @t_ordervalue = ordervalue
--,@t_uid = uid
FROM temp_OrderMaster
WHERE orderid = @t_orderid
AND uid = @t_uid
--)
IF @t_ordervalue <> 0
INSERT INTO temp_calpoints1 (
orderid
,processid
,uid
,ordervalue
)
VALUES (
@t_orderid
,@t_processid
,@t_uid
,@t_ordervalue
)
END
IF @t_processid = 5
BEGIN
SET @t_ordervalue = 0;
--insert into temp_calpoints1 (ordervalue) values(
SELECT @t_ordervalue = ordervalue
--,@t_uid = b.uid
FROM temp_OrderMaster a
,OrderVendor b
WHERE b.orderid = @t_orderid
AND b.uid = @t_uid
--)
IF @t_ordervalue <> 0
INSERT INTO temp_calpoints1 (
orderid
,processid
,uid
,ordervalue
)
VALUES (
@t_orderid
,@t_processid
,@t_uid
,@t_ordervalue
)
END
IF @t_processid = 6
BEGIN
SET @t_ordervalue = 0;
--set @t_uid = 0;
PRINT 'in processid 6 '
PRINT @t_uid;
PRINT @t_orderid;
--insert into temp_calpoints1 (ordervalue) values(
SELECT @t_ordervalue = ordervalue
-- ,@t_uid = b.uid
FROM temp_OrderMaster a
,CollectionFollowUp b
WHERE b.orderid = @t_orderid
AND b.uid = @t_uid
--)
PRINT @t_ordervalue;
IF @t_ordervalue <> 0
INSERT INTO temp_calpoints1 (
orderid
,processid
,uid
,ordervalue
)
VALUES (
@t_orderid
,@t_processid
,@t_uid
,@t_ordervalue
)
END
FETCH NEXT
FROM db_cursor2
INTO @t_uid
END
CLOSE db_cursor2
FETCH NEXT
FROM db_cursor1
INTO @t_processid
END
CLOSE db_cursor1
FETCH NEXT
FROM db_cursor
INTO @t_orderid
END
CLOSE db_cursor
UPDATE temp_calpoints1
SET perwet = (
SELECT perwet
FROM process
WHERE processid = temp_calpoints1.processid
)
END
_______________________________________________________________
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/
March 19, 2013 at 12:39 pm
as of now it inserts 10 percent for every user as below.
orderid processid uid ordervalue perwet(percent wieghtage)
1 1 1 10000 10
1 1 2 10000 10
1 1 3 10000 10
I want that if more than 1 users are involved in 1 process in same order it should divide the percent equally
that means it must insert like this
orderid processid uid ordervalue perwet(percent weightage)
1 1 1 10000 3.33
1 1 2 10000 3.33
1 1 3 10000 3.33
please give me the updated stored procedure
March 19, 2013 at 12:46 pm
abembalkar (3/19/2013)
as of now it inserts 10 percent for every user as below.orderid processid uid ordervalue perwet(percent wieghtage)
1 1 1 10000 10
1 1 2 10000 10
1 1 3 10000 10
I want that if more than 1 users are involved in 1 process in same order it should divide the percent equally
that means it must insert like this
orderid processid uid ordervalue perwet(percent weightage)
1 1 1 10000 3.33
1 1 2 10000 3.33
1 1 3 10000 3.33
please give me the updated stored procedure
I can't help you with an updated procedure because I can't run what you have now. Also if you look at your table you have defined perwet as an int. You can't store decimals in an int field.
You need to provide all of the tables and enough sample data that we can execute what you have. I think I understand roughly what you are looking for but there is just too much detail missing for me to make an attempt at a solution.
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
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/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply