|
hi,
forget the code, what do you want to do???
listen, don´t set date, use getdate(), datediff() or dateadd() function often
Shane Leach wrote: SET @DateFrom = '01/04/2008' --MM/DD/YYYY
SET @DateTo = '01/04/2008' --MM/DD/YYYY
i never do it. tell more maybe we could help you 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
It's a report that right now I have to manually go in and set the dates. It's always run on the first day of the month and the data we are looking for is the previous month's data (i.e., October 1st, I will run it for 9/1/08-9/30/08).
|
|
|
|
|
You can calculate the From and To days in your sp.
First day of previous month:
SELECT DATEADD(MONTH, -1, DATEADD(DAY, - DAY(getdate()) + 1, cast(Floor(cast(GETDATE() AS float)) AS datetime)))
Last day of previous month:
SELECT DATEADD(s, -1 ,DATEADD(DAY, -DATEPART(DAY, getdate()) + 1, cast(Floor(cast(GETDATE() AS float)) AS datetime)))
Ryan
|
|
|
|
|
Thanks for the info. My question would be where in the original script would your statement go? Sorry, I'm a SQL idiot. . .
|
|
|
|
|
DECLARE @DateFrom VARCHAR(15)
DECLARE @DateTo VARCHAR(15)
SELECT @DateFrom = DATEADD(MONTH, -1, DATEADD(DAY, - DAY(getdate()) + 1, cast(Floor(cast(GETDATE() AS float)) AS datetime)))
SELECT @DateTo = DATEADD(s, -1 ,DATEADD(DAY, -DATEPART(DAY, getdate()) + 1, cast(Floor(cast(GETDATE() AS float)) AS datetime)))
|
|
|
|
|
|
what do you want to do???
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
I have a stored procedure as follows, which runs OK on SQL Server 2005.
CREATE PROCEDURE spGetChildAccounts
@ParentId Char(12)
AS
BEGIN
SET NOCOUNT ON;
WITH Subsidaries (ACCOUNTID,ParentID)
AS
(
SELECT ACCOUNTID, ParentID FROM Accounts WHERE ParentID = @parentID
UNION ALL
SELECT Accounts.ACCOUNTID, Accounts.ParentID FROM Accounts
INNER JOIN Subsidaries ON Accounts.ParentID = Subsidaries.ACCOUNTID
)
SELECT * FROM Accounts INNER JOIN Subsidaries ON Accounts.ACCOUNTID = Subsidaries.ACCOUNTID
END
The problem is that it has to run on SQL Server 2000 sp3 and when I try to create the procedure on the 2000 server it get an error "Incorrect syntax near the keyword WITH".
I am assuming the WITH statement is not supported in 2000 and would appreciate any help converting the above stored procedure to a vaild SQL Server 2000 format.
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
Try to generate compact-able script for your stored procedure from SQL2005 for SQL2000.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Blue_Boy wrote: Try to generate compact-able script
Thanks for the advice, unfortunately you are assuming I know what I am doing. Truth is I am currently on a very steep learning curve and I am only just getting to grips with T-SQL. That said I have no idea what you mean by a compact-able script or even how to go about generating one as you suggest.
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
I beleive the with structure you are seeing is not available in 2000 (sorry to state the obvious). What it is doing is allowing you to use a sub select. So the select in () is named Subsidaries and is then used in the join in the second select statement.
Work around would be a view ot temp table to isolate the first select so you can use it in the second select (or completely redesign the query)
OR
You could upgrade SQL Server 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The WITH indicates a CTE (Common Table Expression) which was only introduced with SQL Server 2005. Probablt a temp table to replace your Subsidaries select is the best bet.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hello Friends,
I am using SSIS and I have SQL Server Source and the same Destination. I have a column datetime created in my source and it contains data for about 15 years. I just want to retrieve the data for the current month. i.e, from September 1, 2008 to September 17, 2008. Can anyone plz help me, how can I achieve this?
Using Conditional Split Transformation or SQL Query for this? I want to do this task using SSIS.
Thank you.
notes4we
|
|
|
|
|
 see if this is what you want
declare @mydate datetime
set @mydate = '1/4/2006'
select year(@mydate)
select day(@mydate)
select mouth(@mydate)
not sure about the exact funtion name. if not search in sql help, easy to find.
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Try the following
select *
from [Table]
where datepart(m,[Date field]) = 9
and datepart(d,[Date field]) between 1 and datepart(d,getdate())
Ryan
|
|
|
|
|
Thank you so much for all your replies.
The business logic was that there are two units associated with a single machine. So, the number fields were units that I wanted together in one.
If there are two columns UnitID1 and UnitID2, both having integers in it and if we wish to get the UnitID1 and UnitID2 as “UnitID1, UnitID2” in our destination Stations, then we use the following derived column expression. UnitID1 has no null values, but UnitID2 might have null values also.
[Copy of UnitID1] + (ISNULL([Copy of UnitID2]) ? " " : " , " + [Copy of UnitID2])
'Copy' as I have used Data Conversion transformation to change the datatypes before using derived column. Thank you once again all of you.
|
|
|
|
|
hi,
I have a table with persons having a integer as a unique key.
now i've made a second table groups defined by the persons in it.
the combination of the uniek keys of the persons should be my unique key in the groups table.
for example
1. person a
2. person b
3. person c
first group is
1,2
second group is
1,3
third group is
2,3
fourth group is
1,2,3
Does anyone has any idea how i can store this data easely as unique key and still find the related data in an easy way ?
Data delivered to search a group are the combinations of the persons.
greetz and thx
Kurt
|
|
|
|
|
I'm not sure I follow you, but shouldn't you have a table of Groups
1. Group1
2. Group2
3. Group3
Then a table to represent the many-to-many relationships
Person Group
1 1
1 2
1 4
2 1
2 3
2 4
3 2
3 3
3 4
|
|
|
|
|
thx for the response,
but i'm not quite sure.
if i deliver you person 2 and 3 can you give me the group the present ?
i gues i need a select where group is the same for person 2 and 3 and where group is not used by other persons or so..
hmm this might work...
Problem is that i get a bunch of persons and update common data as group,
i'll need to check if the group exists
if not i'll add the common data
else i'll update the common data of those persons.
so i didn't think about a cross table..
good thinking...
thx
just thinking now that i need to insert multiple keys in de many-to-many table when adding one record for a group, but that should not be a problem.
|
|
|
|
|
listen,
i don´t understand what you want to do. what about the groups stuff? name then to understand, maybe anyone could help you.
do you have 5 tables? persons,group1?,group2?,group3?,group4? 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hi!
I am a newbee to SQL and I am trying to write a trigger. My aim is to realize a triger when a new record has been made in TABLE (A), and identical record will be written in TABLE (B) please see the example below. What intriques my mind is, would it be the last record in TABLE(A) which will be written in TABLE(B) with the definition below.
If you give me a correct example, I would really appreciate it.
Thanks.
<br />
CREATE TRIGGER Trigger2<br />
ON dbo.Customer<br />
FOR <br />
INSERT INTO Administration (AccountStatus, UserType, UserLoginId,UserPassword,PasswordReminder,PasswordReminderAnswer,IsUserLoggedIn)<br />
VALUES (False, <br />
Customer.Usertype,<br />
Customer.UserLoginId,<br />
Customer.UserPassword,<br />
Customer.PasswordReminder,<br />
Customer.PasswordReminderAnswer,<br />
False)<br />
AS<br />
<br />
<br />
What a curious mind needs to discover knowledge is noting else than a pin-hole.
|
|
|
|
|
|
In SQL, you shouldn't think of it as a last row. What you really want is to identify the row by either a sequence number or a timestamp. Try to break yourself from visualizing the data like a file.
Good luck.
|
|
|
|
|
try this;
declare @id_last int
set @id_last = (select max(id) from mytable)
if that is a report? ok, if not (meaning you edit,delete,add) better place some kind of "lock"(search this keyword) because people can trigger that procedure at the same time!!! 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Hi All, could you please give some tips and suggestions on how to improve performance of databases in SQL and optimize in better ways.
Be a good professional who shares programming secrets with others.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
