September 14, 2009 at 1:58 am
Hi Friend,
I am looking for optimized solution to retrieve the data from table, please find the attached excel sheet for details.
I have one Project Table with data, I am expecting result set as count of projects according to stage , CheckFlag and UserID. mentioned result set in excel sheet is for userID 1. I have done this stuff by using cursor and IF .. Else conditions, I got the correct result but it’s lengthy solution, I was tried to optimize the code but I didn’t get any success. I was tried with Common Table Expression also.
Is it possible to get result in single SQL Statement?
If anyone knows the solution/ suggestion please let me know.
Thanks
Jayraj
September 14, 2009 at 2:04 am
Hi,
First of all your link has got some problem it seems. Its not opening the excel. Even if it opens, I dont think it will be of much help. Give the data like this,
1. Create script for table
2. Insert statements for test values (some test data)
3. result that you are expecting.
4. Anything that you tried.
This should help (to post the question)
http://www.sqlservercentral.com/articles/Best+Practices/61537/
This should too, if its a performance related query,
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
---------------------------------------------------------------------------------
September 14, 2009 at 3:13 am
Hi PP,
Thanks for your suggestion, I am very sorry for inconvenience,
I have updated the Topic.
Thanks
September 14, 2009 at 6:56 am
Thanks for the post, it was clear.
A quick and dirty way to do that just to avoid cursor is this, I cant say this is the best, performance wise, I am sure folks will come up with better queries. THis is written free hand so please test it extensively. Thanks.
DECLARE @TEmpTable TABLE(stage varchar(10), col1 int, col2 int, col3 int, col4 int)
INSERT INTO @tempTable (STAGE, col1, col2, col3, col4)
Select stage, Count(*), 0 , 0 , 0 AS col1 from tblProject
Where UserID = 1
AND CheckFlag IN (1)
Group BY stage
INSERT INTO @tempTable (STAGE, col1, col2, col3, col4)
Select stage,0, Count(*),0, 0 AS col2 from tblProject
Where UserID = 1
AND CheckFlag IN (1, 2)
Group BY stage
INSERT INTO @tempTable (STAGE, col1, col2, col3, col4)
SELECTStage, 0,0, Count(*),0
FROMtblProject
WHERECheckFlag IN (1)
Group BY stage
INSERT INTO @tempTable (STAGE, col1, col2, col3, col4)
SELECTstage, 0,0,0,Count(*)
FROMtblProject
WHERECheckFlag IN (1,2)
Group BY stage
SELECT Stage, Sum(col1), sum(col2), sum(col3), sum(col4) From @tempTable
Group BY stage
---------------------------------------------------------------------------------
September 14, 2009 at 12:42 pm
This code results in the exact same result as yours, except it uses the coalesce function instead of a cursor:
create table #tblProject (
ProjectID INT,
Stage VARCHAR(100),
CheckFlag INT,
UserID INT)
INSERT #tblProject
Select 1, 'Stage1',1,1 UNION ALL
Select 2, 'Stage1',1,1 UNION ALL
Select 3, 'Stage1',1,2 UNION ALL
Select 4, 'Stage1',2,1 UNION ALL
Select 5, 'Stage2',1,1 UNION ALL
Select 6, 'Stage2',1,3 UNION ALL
Select 7, 'Stage2',1,2 UNION ALL
Select 8, 'Stage2',1,4 UNION ALL
Select 9, 'Stage3',1,1 UNION ALL
Select 10, 'Stage3',1,1 UNION ALL
Select 11, 'Stage3',1,1 UNION ALL
Select 12, 'Stage3',1,1 UNION ALL
Select 13, 'Stage4',1,4 UNION ALL
Select 14, 'Stage4',1,5 UNION ALL
Select 15, 'Stage4',2,1 UNION ALL
Select 16, 'Stage4',1,3
declare @sqlstring varchar(max)
DECLARE @user-id INT
SET @user-id = 1
select @sqlstring = coalesce(@sqlstring, '') +
replace(replace(
'SELECT ''@Stage'' AS Stage,MeNew.MeNew, MeTotal.MeTotal,NtwNew.NtwNew,NtwTotal.NtwTotal
FROM
(SELECTCount(ProjectID) As ''MeNew''
FROM#tblProject
WHEREUserID = @user-id
AND CheckFlag IN (1)
AND Stage = ''@Stage''
) AS MeNew,
(SELECTCount(ProjectID) AS ''MeTotal''
FROM#tblProject
WHEREUserID = @user-id
AND CheckFlag IN (1,2)
AND Stage = ''@Stage''
) AS MeTotal,
(SELECTCount(ProjectID) AS ''NtwNew''
FROM#tblProject
WHERECheckFlag IN (1)
AND Stage = ''@Stage''
) AS NtwNew,
(SELECTCount(ProjectID) AS ''NtwTotal''
FROM#tblProject
WHERECheckFlag IN (1,2)
AND Stage = ''@Stage''
) AS NtwTotal'
,'@Stage',Stage), '@UserID',@UserID) + char(10) + char(10)
from #tblProject
group by stage
exec (@sqlstring)
Cheers,
Carleton
September 14, 2009 at 1:10 pm
Hi, I hope this helps, it reads the table only once...
SELECT stage,
sum(CASE
WHEN UserID = @user-id
AND CheckFlag IN (1)
THEN 1
ELSE 0
END) AS MeNew,
sum(CASE
WHEN UserID = @user-id
AND CheckFlag IN (1,2)
THEN 1
ELSE 0
END) AS MeTotal,
sum(CASE
WHEN CheckFlag IN (1)
THEN 1
ELSE 0
END) AS NtwNew,
sum(CASE
WHEN CheckFlag IN (1,2)
THEN 1
ELSE 0
END) AS NtwTotal
FROM @tblProject
GROUP BY Stage
ORDER BY Stage
Cheers,
J-F
September 15, 2009 at 8:27 am
Hi All,
Thanks a lot for help.
Best Regards,
Jayraj 😛
September 15, 2009 at 8:39 am
Hi,
Can you post the chosen solutions, and why you chose it? We would certainly appreciate feedback from you, as well as others looking for the "Best" approved solution by you.
Thanks for your time,
Cheers,
J-F
September 15, 2009 at 11:59 pm
Dear Friends,
I would like to thank again for providing me your views, all solutions provided by you all have always helped me straight way. I would like to prefer Carleton’s solution because that solutions reduce the code and I think it gives better performance in my scenario.
Right now I am working on the performance issue for the same, because actual data is huge and there are lots of conditions on different tables. I will revert back with the solution which I will be implementing after going through my scenarios.
Best Regards,
Jayraj Todkar
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply