Database

Re: SQL stored porcedures

13-Sep-07 6:11

Trying reading SQL Server books online (there are online at ms).

Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture)

http://www.frankkerrigan.com/[^]

Paul Conrad14-Sep-07 19:20

Paul Conrad

14-Sep-07 19:20

OS GIKEN wrote:
UPDATE,DELETE,INSERT

Like the others have said, read up on these, and they are sql keywords, not stored procedures Smile | :)

http://www.w3schools.com/sql/[^] might help you some...

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Query not returning distinct value

miniThomas12-Sep-07 19:35

miniThomas

12-Sep-07 19:35

Hi,

I am firing a query as "select distinct(trim(opp_name)) from tblOpps"

but this query returns
============
Renewal
Services
Renewal
New

I want Renewal to appear only once not twice

Thnaks,
Mini

ritu432112-Sep-07 20:13

ritu4321

12-Sep-07 20:13

there is nothing as trim in sql query. Try removing trim you will get the result.

/randz12-Sep-07 23:25

/randz

12-Sep-07 23:25

i agree with ritu4321. but if you intend to remove trailing or preceding space, you can use rtrim or ltrim. But please note that ltrim and rtrim does not remove extra spaces within the string, only those at the beginning (for ltrim) and those at the end (for rtrim). Hope this helps.

Remember, your work is not yours alone. Somewhere, there are some codes written by others amongst us that depends on your work. By failing to see that you are part of their ecosystem, you are bound to break their code.

Michael Potter13-Sep-07 4:46

Michael Potter

13-Sep-07 4:46

DISTINCT should be used as a SELECT operator, not as a function. I also do not know where you get the trim() function from.

SELECT DISTINCT
    LTRIM(RTRIM(opp_name))
FROM
    tblOpps

Frank Kerrigan13-Sep-07 6:15

Trying to extract data from a single table

13-Sep-07 6:15

if you get stuck try and use the len (length function) sometimes using or importing unicode string (nvarchar etc) can cause issues.

<br />
<br />
SELECT DISTINCT   <br />
 LTRIM(RTRIM(opp_name)),<br />
 len(opp_name),<br />
 len(LTRIM(RTRIM(opp_name)))<br />
FROM    tblOpps<br />
<br />

Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture)

http://www.frankkerrigan.com/[^]

Skanless12-Sep-07 8:18

12-Sep-07 8:18

I attempting to extract data from a single table based on the two different criteria then presenting the information as one table. In the first instance I am trying to extact the date and count() of all messages received with a given time period(the query is based on createdDate). In the second intsatnce I am attempting to extrtact data based on it current status(i.e resolved, resaerched, open) I have created two temp table that provides me with the data I need now I need to group them by date so I can display the data by months for example:

Beginning Period Received Messages Resolved Issues
1/1/2007 15 6
2/1/07 8 19
3/1/07 25 10

And so forth. I have inclused the script any assistance will be great appreciated.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_MS_IssuesReport](@PeriodName varchar(50), @BeginningDate datetime, @EndDate datetime)AS
BEGIN
DECLARE

@Period datetime,
@MessagesReceived bigint,
@MessagesClosed bigint

SET NOCOUNT ON;

CREATE TABLE #MS_MessagesReceived(
DateCreated datetime,
IssuesReceived int,

)
CREATE TABLE #MS_MessagesStatus(
DateCreated datetime,
IssuesClosed int
)
CREATE TABLE #MS_MessagesReceivedAndStatuses(
DateCreated datetime,
IssuesReceived int,
IssuesClosed int
)

Insert Into #MS_MessagesReceived(DateCreated,IssuesReceived)
Select dbo.RPT_GetPeriod(@PeriodName, CreateDate) AS 'Period', count(CurrentStatusID) from Ms_threads
where CreateDate BETWEEN @BeginningDate and @EndDate
Group by CreateDate,CurrentStatusID
Order by CreateDate

Insert Into #MS_MessagesStatus(DateCreated, IssuesClosed)
Select dbo.RPT_GetPeriod(@PeriodName, StatusDate) AS 'Period',count(CurrentStatusID)from Ms_threads
where dbo.RPT_IsInquiryCompleted(CurrentStatusID) = 1
and StatusDate BETWEEN @BeginningDate and @EndDate
Group by StatusDate, CurrentStatusID
Order by StatusDate, CurrentStatusID

SELECT @MessagesReceived = count(IssuesReceived) from #MS_MessagesReceived
SELECT @MessagesClosed = count (IssuesClosed) from #MS_MessagesStatus
SELECT @Period =dbo.RPT_GetPeriod(@PeriodName, DateCreated) from #MS_MessagesReceived

INSERT INTO #MS_MessagesReceivedAndStatuses(DateCreated, IssuesReceived, IssuesClosed)
VALUES(@Period, @MessagesReceived, @MessagesClosed)

Select DateCreated 'Time Period Beginning' , IssuesReceived 'Issue Received', IssuesClosed 'Issue Closed'
from #MS_MessagesReceivedAndStatuses

Drop Table #MS_MessagesReceived
Drop Table #MS_MessagesStatus
Drop Table #MS_MessagesReceivedAndStatuses
END

Skan

If you knew it would not compile why didn't you tell me?!?!?!

andyharman13-Sep-07 2:32

andyharman

13-Sep-07 2:32

How about:

SELECT dbo.RPT_GetPeriod(@PeriodName, CreateDate) AS Period,
    COUNT(*) AS IssuesReceived,
    SUM(dbo.RPT_IsInquiryCompleted(CurrentStatusID)) AS IssuesClosed
FROM Ms_threads
WHERE CreateDate BETWEEN @BeginningDate and @EndDate
GROUP BY CreateDate
ORDER BY CreateDate

The "SUM" expression assumes that your function returns either 0 or 1.

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Skanless13-Sep-07 5:46

13-Sep-07 5:46

Thanks for the your help. It sort does what I want however because a message that was created 1/5/2005 may not be resolved until sometime in March its current Status will not be 'Resolved' thus will not fall within this status period. Looking at the code that messages status may be counted during a January monthly report the status is not resolved only becuase the message was created in January. I guess I did not provide information but I am trying to extract and count ALL messages created during the period and all messages Resolved during the period. Agian, a messages created during that period may not be resolved until a future date hence we do not need to count their status on their status is 'Resolved' thus keying off the statusDate.

Thanks for you response. I like the way you clean up the code.

Skan

If you knew it would not compile why didn't you tell me?!?!?!

Skanless13-Sep-07 5:54

13-Sep-07 5:54

A simple 'OR' Statement should work.

SELECT dbo.RPT_GetPeriod(@PeriodName, CreateDate) AS Period, COUNT(*) AS IssuesReceived, SUM(dbo.RPT_IsInquiryCompleted(CurrentStatusID)=1) AS IssuesClosed FROM Ms_threadsWHERE CreateDate BETWEEN @BeginningDate and @EndDateGROUP
or StatusDate BETWEEN @BeginningDate and @EndDate(This would have to be union with CreateDate for it to work.)
GROUP BY CreateDate
ORDER BY CreateDate

unfortunately the OR statement as scripted is not working either.

Skan

If you knew it would not compile why didn't you tell me?!?!?!

andyharman13-Sep-07 6:45

andyharman

13-Sep-07 6:45

Hi Skan

How about:

SELECT dbo.RPT_GetPeriod(@PeriodName, A.PeriodDate) AS Period,
    SUM(A.IssueReceived) AS IssuesReceived,
    SUM(A.IssueClosed) AS IssuesClosed
FROM (
  SELECT CreateDate AS PeriodDate,
      1 AS IssueReceived,
      0 AS IssueClosed
    FROM Ms_threads
    WHERE CreateDate BETWEEN @BeginningDate and @EndDate
  UNION ALL
  SELECT StatusDate AS PeriodDate,
      0 AS IssueReceived,
      dbo.RPT_IsInquiryCompleted(CurrentStatusID) AS IssueClosed
    FROM Ms_threads
    WHERE StatusDate BETWEEN @BeginningDate and @EndDate
) A
GROUP BY A.PeriodDate
ORDER BY A.PeriodDate

The select statements within the from-clause count the issues openned and closed during the selected period. The outside select statement then groups everything together.

The technique is fairly common. If you have problems understanding what it is doing then run each of the select statements individually.

Regards
Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Skanless13-Sep-07 8:00

Getting Top 10 results per month/year

13-Sep-07 8:00

Dude you are a Genius. I modified the script a but and it worked like a baby. Thnks dude.. I owe u a beer.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_MS_IssuesReport_Num2](@PeriodName varchar(50), @BeginningDate datetime, @EndDate datetime)AS
BEGIN

SELECT dbo.RPT_GetPeriod(@PeriodName, A.PeriodDate) AS Period,
SUM(A.IssueReceived) AS IssuesReceived,
SUM(A.IssueClosed) AS IssuesClosed
FROM(
SELECT CreateDate AS PeriodDate,
1 AS IssueReceived,
0 AS IssueClosed
FROM Ms_threads
WHERE CreateDate BETWEEN @BeginningDate and @EndDate
UNION ALL
SELECT StatusDate AS PeriodDate,
0 AS IssueReceived,
dbo.RPT_IsInquiryCompleted(CurrentStatusID) AS IssueClosed
FROM Ms_threads
WHERE StatusDate BETWEEN @BeginningDate and @EndDate
) A
GROUP BY dbo.RPT_GetPeriod(@PeriodName, A.PeriodDate) -- Added the date floor function.
ORDER BY dbo.RPT_GetPeriod(@PeriodName, A.PeriodDate)
end

Thank Code Project....This deserves a 10 not a 5...hehehehe!!

Skan

If you knew it would not compile why didn't you tell me?!?!?!

_Joao_12-Sep-07 0:33

_Joao_

12-Sep-07 0:33

Hi there.

I'm having some trouble trying to do the following:

I have some data in my DB:
ID (PK)
type
creation_date

What I want to do is to get the top x results by month/year of creation_date.

I already have a query to count the number of ID's in some month/year, grouped by type, with a 'group by'. This would return the number of ID's with type T in month mm of year yy, for all months and years that appear on creation_date.

Now I need to return the top X results in month 01/2007, top X results in month 02/2007 and so on.

I already make this happen, with a stored procedure, but I'm using another stored procedure to get all the data to a XML file, and I'm not being able to call the stored procedure with the top from the stored procedure that will generate the XML...

Any ideia, anyone? Smile | :)

Joao

Blue_Boy12-Sep-07 3:19

Blue_Boy

12-Sep-07 3:19

use union

example:

SELECT * FROM Table1<br />
UNION<br />
SELECT * FROM Table2

I Love SQL

Frank Kerrigan12-Sep-07 5:36

12-Sep-07 5:36

That is total rubbish!!

Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture)

http://www.frankkerrigan.com/[^]

Colin Angus Mackay12-Sep-07 5:53

Colin Angus Mackay

12-Sep-07 5:53

I concur.

Upcoming FREE developer events:
* Glasgow: Agile in the Enterprise Vs. ISVs, db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ...

My website

Pete O'Hanlon12-Sep-07 9:18

Pete O'Hanlon

12-Sep-07 9:18

How does this solve the problem? Did you even read his post?

Deja View - the feeling that you've seen this post before.

_Joao_12-Sep-07 11:41

_Joao_

12-Sep-07 11:41

Well, that will not solve my problem, because I do not know exactly how many months/years there could be in the first place.

I do have something like this:

select count(ID), type, year(creation_date), month(creation_date)
from table1
group by year(creation_date), month(creation_date), type

which returns the count per type/month/year... After that I just want to show top 5 types per month/year...

As I said, I could do something like what I want with a stored procedure, with a loop foreach month/year, but I do not know how to run a stored procedure from inside another stored procedure...

I've tried 'exec sp_getdata' from inside the other stored procedure, but that didn't work...

Joao

yahao12-Sep-07 13:05

yahao

12-Sep-07 13:05

A non-clever method is to use temp table instead of a single sql script. :p

can i send emails in this way?

andyharman13-Sep-07 6:57

andyharman

13-Sep-07 6:57

Sql-Server 2005 introduced a bunch of new functions for ranking and windowing data. Try Using Ranking and Windowing Functions in SQL Server 2005[^]

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

prasadbuddhika11-Sep-07 17:49

prasadbuddhika

11-Sep-07 17:49

by using sql server agent we backup the database as we required.in the same way can i send an email.

what my requirement is , to send mails to relevent parties when the complaints are not handled within 24 hrs , they are lodged.

to send the email i should specify the mail server,but what if i'm not having a mail server and using a free mail server in the internet,how to configure the database mail..

-- modified at 0:15 Wednesday 12th September, 2007

Re: can i send emails in this way?

Frank Kerrigan12-Sep-07 5:39