February 24, 2010 at 7:56 am
Hello,
I am having trouble with trying to get the total count of tickets based on type based on specific query. Can anyone offer thoughts.
I think the trouble is at this location in the code where my syntax is not correct. Do I need to convert?
WHERE Type = '"&@varTypeName"'
Here is the code with some description:
USE TRACKIT80_DATAPROD2
-- Declare variables for use
DECLARE @varTypeName varchar(30) --Ticket type (top level category)
DECLARE @varTotal int -- Total ticket count
-- Declare first curser to hold each distinct task type name & Pull the distinct task type name
DECLARE cTypeName CURSOR
FOR
Select DISTINCT Typename
From TaskTypeLevelDim
WHERE TypeName IS NOT NULL
-- Fetch next curser and insert value into @varTypeName
OPEN cTypeName
FETCH NEXT FROM cTypeName
INTO @varTypeName
-- Declare cTotals curser as read only and populate with value from select statement
DECLARE cTotals CURSOR
FOR
SELECT COUNT(*) AS Totals
FROM Tasks
WHERE Type = '@varTypeName'
SELECT COUNT(*) AS Totals
FROM (SELECT *
FROM Tasks
WHERE Type = '+&@varTypeName+') as TASKTypes
-- Open the cTotals Cursor and insert into @varTypeName as it new value
OPEN cTotals
FETCH NEXT FROM cTotals
INTO @varTotal
WHILE @@FETCH_STATUS = 0
BEGIN
-- Print to screen values of variables varTypeName and varTotal
SELECT @varTotal,@varTypeName
-- PRINT convert (char(24), @varTotal)+ convert (char(24), @varTypeName)
-- Continue to fetch until you hit the end of the fetch loop
FETCH NEXT FROM cTotals
INTO @varTotal
FETCH NEXT FROM cTypeName
INTO @varTypeName
END
CLOSE cTypeName
CLOSE cTotals
DEALLOCATE cTotals
DEALLOCATE cTypeName
February 24, 2010 at 8:50 am
At first blush you are probably right, I'm curious how you came about this syntax since you did pretty well everywhere else..
Also, I may not fully understand your goal in this code so give it a try and get back to us as to whether it solved your problem.
I don't think you need a cursor to do this.
It seems that you want a distinct list of types and you want to get a count of tasks by that type list. If so, I think this query should probably do it, but since I don't have your structures it is hard to be exact..
SELECT TN.Typename, TaskCount=COUNT(t.type)
FROM dbo.Tasks AS t,
( SELECT DISTINCT Typename
FROM dbo.TaskTypeLevelDim
WHERE TypeName IS NOT NULL ) AS TN
WHERE t.type = TN.Typename
GROUP BY TN.Typename
Also this method is like 10000% more efficent than the cursor method and won't get you a chastising from Jeff Moden for RBAR..
CEWII
February 24, 2010 at 9:47 am
Wow Elliot, thank you so much!
This did the trick. I am new to programming and really like it however hate when I do not know how to approach some thing.
I guess that is mount SQL in front of me right? I am only at the approach, I have a little way to go before I hit the base....
Thanks again for reaching out and helping me with this.
Best
Paul
February 24, 2010 at 10:08 am
You are very welcome. SQL, like every other language take a while to get into.
I would say this, my guess is you are coming from a procedural language, like C#, VB.NET and such. Where you deal with things one row at a time. In SQL the paradigm is different, you need to start looking at the data as sets and not individual rows and how you want to operate on those sets. That is precisely what that query does, we operate on the entire set at one time instead of using cursors to go row-by-row, or as Jeff Moden says, Row-By-Agonizing-Row (RBAR pronounced Ree-Bar).
Have a great day.
CEWII
February 24, 2010 at 1:18 pm
Ok Elliott, this helps. I will need to perform a few more of these to really bake in what you are outlining but this certainly make sense.
I am also new to the forum sense of knowledge sharing. It is so great to be able to toss something out there and have others help out. Is there a way once I get up to speed with SQL and the accidental DBA position I seem to be in at the moment, that I can contribute? I am not sure how this works, and I have lots of question I feel funny posting and not giving back in someway?
Paul
February 24, 2010 at 1:39 pm
I think it is ok to post your questions. But I offer this.
1. Google it first, see if you can find a resolution. Not doing any research and then asking a question that could easily be answered with a single google search tends to irritate people..
2. Expect your methodology to be challenged, don't get too wrapped up in a particular implementation because you will likely find a better solution that you started with.
3. Some posters are gruffer than others don't let them wind you up.
4. You will make stupid errors, you don't know any better, learn from them. You will ask stupid questions, when you get called on it try to learn from it.
5. When you see a topic in "Active Threads" that you KNOW the answer to or have some good experience with, if you can confidently answer go ahead. If you have very little experience don't just throw out a wild guess. For example I answer a LOT of SSIS questions because I have done a LOT with it and things that I don't have a great deal of knowledge about I answer carefully.
6. And a personal favorite.. If you give attitude to a poster it will absolutely not help you get an answer.. On this particular point I often will state something along the lines of drop the attitude, I am trying to help you but if it is too much trouble then I'm out, good luck with your problem..
7. Stay professional..
8. If you are wrong, say so. There are a lot of things to know about the SQL Server product, you can't know everything, you will be wrong periodically. One of the most unprofessional things (in my view) is to hold on to an utterly discredit postion. Playing devils advocate is one thing..
Post when you feel comfortable.
CEWII
February 25, 2010 at 10:26 am
Thanks for your feed back Elliott. Sounds like the right way to approach it.
Here we go....
Paul
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply