|
Sorry I was able to do it.
I made some silly mistakes.
Regards
Anil
|
|
|
|
|
Harini311 wrote: Better delete this forum
What about other people that want to use this forum?  (I suspect you meant "message" rather than "forum")
|
|
|
|
|
Hi G to U.R.U.S!
I want to return results based on entries on date range (Date From, Date To), the condition is that when the users doesn't fill up (leave blank) the date range (Date From and Date To), the SP should make use of oldest date and earliest date as date range.
Below is the SP I made which doesn't seem to work, please advice
<br />
(<br />
@DateFrom datetime =null,<br />
@DateTo datetime =null<br />
)<br />
as<br />
begin <br />
<br />
select <br />
PageName,<br />
count(PageName) as [Page Name]<br />
from<br />
PageViews<br />
where <br />
DateEntered > <br />
case when @DateFrom is null then (select convert(char(12),min(DateEntered)) from pageviews)<br />
<br />
else<br />
dateadd(day,-1,@DateFrom) <br />
end <br />
and <br />
DateEntered <<br />
case when @DateTo is null then (select convert(char(12),max(DateEntered)) from pageviews)<br />
else<br />
dateadd(day,1,@DateTo)<br />
end<br />
group by<br />
PageName<br />
order by <br />
[Page Name]<br />
<br />
end<br />
<br />
Thanks
Dom
|
|
|
|
|
I normally use:
select PageName, count(PageName)
from PageViews
where DateEntered between IsNull(@StartDate, DateEntered)
and IsNull(@EndDate, DateEntered)
group by PageName
order by PageName If either of the date variables is null then the IsNull function uses the value of the DateEntered column (and so the expression evaluates to true).
Regards
Andy
|
|
|
|
|
Hi Andy,
You nailed it, I have some question though, when i put in a blank date how will I know from these statement that the sp will pick on the oldest and latest date?
<br />
where DateEntered between IsNull(@StartDate, DateEntered)<br />
and IsNull(@EndDate, DateEntered)<br />
Please advice
Thanks
Dom
|
|
|
|
|
There are really two approaches that you can take. The first is this:
SELECT
PageName,
COUNT(PageName) AS PageCount
FROM
PageViews
WHERE
DateEntered BETWEEN
COALESCE(@StartDate, (SELECT MIN(DateEntered) FROM PageViews)) AND
COALESCE(@EndDate, (SELECT MAX(DateEntered) FROM PageViews))
GROUP BY PageName, DateEntered
While this works, it is largely inefficient because you are asking the system to re-evaluate the MIN/MAX every time. A better approach would be to retrieve these values initially, and then use them in your procedure like this:
DECLARE @MinDate DATETIME, @MaxDate AS DateTime
SELECT @MinDate = MIN(DateEntered), @MaxDate = MAX(DateEntered) FROM PageViews
SELECT
PageName,
COUNT(PageName) AS PageCount
FROM
PageViews
WHERE
DateEntered BETWEEN
COALESCE(@StartDate, @MinDate) AND
COALESCE(@EndDate, @MaxDate)
GROUP BY PageName
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
It cheats a bit - and so doesn't require knowledge of the oldest and latest dates.
As a worked example: If the @StartDate is blank and the @EndDate is "1 Mar 2007" then the where clause is equivalent to:
where DateEntered between DateEntered and '1 mar 2007' Pete's second answer (using the Coalesce function) is exactly the same. I tend to use "IsNull" because its easier to spell
Hope that helps.
Andy
|
|
|
|
|
Hi
For date filtering, try this:
From front-end, pass default value as Min: 01/01/1900 and Max: 01/01/9999 if date values are not entered or blank
In your select statement, add this statement
IsNull(DateCol,'01/01/1900') >= Convert(nvarchar(12),@FromDate,101) AND
IsNull(DateCol,'01/01/9999') <= Convert(nvarchar(12),@ToDate,101)
Change date format from '101' to anything based on the format (whether MM/dd/yyyy or dd/MM/yyyy) you are storing in the database.
IsNull function on the left side is required as if you are allowing NULL values for the date field.
That 's it.
Rate this message. Thank you. Harini
|
|
|
|
|
My vc++ application works fine till the SQL Server 2005 is not restarted(i mean if we stop the SQL Server 2005 and then start it again while application is running).
Could anyone please tell me how to establish connection(auto connection) for the application after the SQL Server 2005 is restarted.
Thanks!!
|
|
|
|
|
Hi
How can we can make DB2 and SQL server communicate with each other. It's not a kind of datamigration, these were need to be updating each other on regular basis.
Thanking u in advance,
Regards,
Ajit
-- modified at 0:51 Thursday 8th March, 2007
|
|
|
|
|
I have a service (which I have written in C#) that I want to run on 2 different computers however they will both be accessing the same database.
Is there a way to tell when one computer is writing to the database so I can stop the other from writing to it at the same time to avoid contention issues?
|
|
|
|
|
It would help to know what kind of database... Access, SQL Server, ...
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
|
In that case, no, you don't have to worry about it. SQL Server maintains order on its own. Just keep in mind that the "last write" will win.
Dave Kreskowiak
Microsoft MVP - Visual Basic
|
|
|
|
|
sql server 2000
server is MyServer named "SALE" and is on local comp
When stored procedure is like this
UPDATE MyServer.MyDataBase.dbo.Mytable SET System='pib'
there is an error 7405
:"Heterogeneous queries require the ANSI_NULLS and Ansi_Warnings option to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query."
Coulmn System is db type varchar(50)
When stored procedure is like this
UPDATE MyDataBase.dbo.Mytable SET System='pib' it is all ok
Problem is that I need to be remote MyServer.MyDataBase.dbo.Mytable...
It is all working when is sent from QueryAnalizer
Where and how can i resolve this
|
|
|
|
|
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO CREATE PROCEDURE spStoredProdc AS ... go
SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF
|
|
|
|
|
I've been trying to consolidate/reuse some business logic in a recent project and doing so would require me (within a MS-SQL 2000 stored procedure) to get a dataset from a stored procedure and aggregate some data. Is there a way to get a dataset from a stored procedure and put it in a temporary table? This is not an output variable, just the regular output dataset. I'm using Microsoft SQL 2000.
Help please!
USE PD9
DECLARE @RC int
DECLARE @datebegin varchar(10)
DECLARE @dateend varchar(10)
SET @datebegin = '01/01/2007'
SET @dateend = '01/15/2007'
--this is a dataset of our sales reps
DECLARE sales_cursor CURSOR
FOR
SELECT DISTINCT
[ABAN8]
FROM
[vF0101_RepDetails]
ORDER BY [ABAN8]
OPEN sales_cursor
DECLARE @an8 int
FETCH NEXT FROM sales_cursor INTO @an8
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
-- i'm looping through a dataset of our sales reps
-- now i need to get a dataset from the below sproc
-- loop through that dataset and put it in a temp table
-- the problem is getting the dataset from the next line - HOW??
-- EXEC @RC = [PD9].[dbo].[usp_rs_getSalesShipments] @an8, @datebegin, @dateend
-- print @RC
END
FETCH NEXT FROM sales_cursor INTO @an8
END
CLOSE sales_cursor
DEALLOCATE sales_cursor
|
|
|
|
|
You can use:
INSERT INTO #MyTempTable (col1, col2, col3, ..., coln)
EXEC MyStoredProc where the temporary table's column list matches the columns returned in the stored procedures resultset (and their data types).
|
|
|
|
|
hi
plzz tell me, if any one use split function in sql server 2000
i want to split one column data and then get coresponding values from another table like:-
table_1
year code
2001 1,2,6
2002 2,5,6
table_2
code name
1 one
2 two
5 five
6 six
i want to split code on(,)but this query is not working.
"select name from table_2 where code=(split(select code from table_1 where year='2001'))"
plzz help me
thanks in advance
john
-- modified at 11:16 Wednesday 7th March, 2007
|
|
|
|
|
|
Hi,
I know how to retrieve data from SQL to Dataset. Can anyone help me on how to insert updated data from Dataset back to SQL.
Thanks
Raj
|
|
|
|
|
From VS 2005 documentation:
DataAdapter.Update - Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the specified DataSet from a DataTable named "Table."
|
|
|
|
|
Thanks for your reply. "DataAdapter.Update" is for updating a DataTable in DataSet. I want to know how to insert this updated data back to SQL.
|
|
|
|
|
Try this snippet and let me know if it worked for you.
SqlConnection c = new SqlConnection(@"Data Source=(local)\SQL2005;Initial Catalog=TEST_DB;Integrated Security=True");<br />
SqlDataAdapter da = new SqlDataAdapter("select * from customer where customer_id = 1", c);<br />
SqlCommandBuilder cb = new SqlCommandBuilder(da);<br />
DataTable dt = new DataTable();<br />
<br />
da.Fill(dt);<br />
dt.Rows[0]["NAME"] = dt.Rows[0]["NAME"].ToString() + "1";<br />
da.Update(dt);
|
|
|
|
|
hi U have to use commandbuilder(dataadapterobject)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
