|
The way you have it does not allow nulls so you may just want to handle the exception msg 201 as a null parameter. The other way is to add a default @RateLevel1 money = NULL and have an output parameter @IsNull of type bit and set to true if any parameters are null.
|
|
|
|
|
update @lia_table1<br />
set vou_id = replace((select voucher_id from confirm_detail where <br />
bkng_no in (select bkng_no from @lia_table1 where ids = @minsum + 1)<br />
and voucher_no in (select max(voucher_no) from confirm_detail where <br />
bkng_no in (select bkng_no from @lia_table1 where ids = @minsum + 1) group by bkng_no))<br />
where ids = @minsum + 1,'con','dom')
This is giving me error near where .
actually i want this replace the voucher_id 'con_ccg_199' with 'Dom_ccg_198'
|
|
|
|
|
soniasan wrote: This is giving me error near where .
I guess your where clause(where ids = @minsum + 1) is outside the outerquery
<br />
(select voucher_id from confirm_detail where .....)
If it needs to be along with the outerquery, you might be in need of AND clause instead of WHERE.
Also, looks to me like the query is muddled up with respect to the inner query's termination.
Probably executing the inner-queries individually might help you analyse the query in a better way.
HTH
|
|
|
|
|
If you're using SQL-Server then I think that the following may work for you:
UPDATE A SET
vou_id = REPLACE(B.voucher_id, 'con', 'dom')
FROM @lia_table1 A
INNER JOIN confirm_detail B
ON B.bkng_no = A.bkng_no
AND B.voucher_no = (
SELECT MAX(voucher_no) FROM confirm_detail C
WHERE C.bkng_no = A.bkng_no)
WHERE A.ids = @minsum + 1Regards
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
I don't - I'm not keen on the blatant attempts to get MVP status.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Pete O`Hanlon wrote: not keen on the blatant attempts to get MVP status
You got my 5 for your attitude 
|
|
|
|
|
Back at you
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi, can anyone help me understand stored procedures? UPDATE,DELETE,INSERT,GET. I need to understand it, not just copy it and do my work, I want to understand it, Im using SQL server 2005
Thanks
This is what I think, so suck it up!
|
|
|
|
|
OS GIKEN wrote: UPDATE,DELETE,INSERT,GET
Those aren't stored procedures. They relate to the way that you manipulate data in the database. They are typically referred to as CRUD operations (or Create/Retrieve/Update/Delete) - and it's not GET, it's SELECT.
A stored procedure is a mechanism by which code is stored in a database server, which can then be executed at a later stage. The code is written in the particular dialect that the database engine can "compile". For instance, in SQL Server, the stored procedure is written in T/SQL (Transact SQL). I hope that this helps to give you a start.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Trying reading SQL Server books online (there are online at ms).
|
|
|
|
|
OS GIKEN wrote: UPDATE,DELETE,INSERT
Like the others have said, read up on these, and they are sql keywords, not stored procedures 
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
|
|
|
|
|
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
|
|
|
|
|
there is nothing as trim in sql query. Try removing trim you will get the result.
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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 />
|
|
|
|
|
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?!?!?!
|
|
|
|
|
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 CreateDateThe "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".
|
|
|
|
|
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?!?!?!
|
|
|
|
|
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?!?!?!
|
|
|
|
|
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.PeriodDateThe 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".
|
|
|
|
|
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?!?!?!
|
|
|
|
|
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?
Joao
|
|
|
|
|
use union
example:
SELECT * FROM Table1<br />
UNION<br />
SELECT * FROM Table2
I Love SQL
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
