User Profile

Collapse

Profile Sidebar

Collapse
Infide
Infide
Last Activity: Mar 20 '08, 11:37 PM
Joined: Jul 10 '07
Location:
  •  
  • Time
  • Show
  • Source
Clear All
new posts

  • You might get stuck with having to use a cursor.

    This should get you started.
    Code:
    DECLARE @PassedList VARCHAR(5000)
    DECLARE @ParsedList VARCHAR(5000)
    DECLARE @ROW VARCHAR(5000)
    set @passedlist = '2,5,10,6'
    
    declare @ParsedInt int
    declare @CompareInt int
    
    
    
    DECLARE  C cursor  LOCAL FAST_FORWARD FOR
    SELECT  ColumnX
    	From TableTest
    ...
    See more | Go to post

    Leave a comment:


  • Infide
    replied to Query for making a monthly report
    What are you trying to see? Totals by quantity? Totals by unit price? Before you build a sql statement, the first thing you should ask in your mind is "what do i want to see?".

    But try this for starters.

    Code:
    declare @begindate datetime, @enddate datetime
    
    set @begindate = '1/1/2007'
    set  @enddate = '1/31/2007 23:59'
    
    
    Select SUM(quantity) as quantity, sum(unitprice)
    ...
    See more | Go to post

    Leave a comment:


  • We're not manipulating the data, we are merely pivoting it out. If SQL case is not working see what the syntax is for a case or switch statement on whatever platform you are using....
    See more | Go to post

    Leave a comment:


  • In my world, this is better practice:
    SELECT @SavedSearches = count(id), username
    from endeavor_member info
    where something = something

    Because this allows you to do this:

    SELECT @SavedSearches = count(id),
    @username = username
    from endeavor_member info
    where something = something


    "SELECT count(id) as countr, username FROM endeavor_search WHERE IN (SELECT...
    See more | Go to post

    Leave a comment:


  • Infide
    replied to Stored Proc Optimization Help Needed
    There are a couple of things I would recommend.
    One is not using views. Views are inherently slow. It's a pain, but write out the entire select instead of using the view.

    Two, use the table hint 'with (no lock,readuncomm itted)'. This is a dirty read. Use it if your reporting cycle and processing cycles are not on the same schedule.

    Couple of other things you could do...
    create a temp table with the #...
    See more | Go to post

    Leave a comment:


  • Code:
    Update antrix.Creature_proto
    set health = cp2.Health
    from antrix2.Creature_proto cp2
       inner join antrix.creature_proto cp1
            on cp1.entry = cp2.entry
    ...
    See more | Go to post

    Leave a comment:


  • Infide
    replied to Manager Hierarchy (Recursive)
    Or better yet, if you're using sql server 2005 I just found this in the documentation.
    Code:
    USE AdventureWorks;
    GO
    WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
    (
        SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
        FROM HumanResources.Employee
        WHERE ManagerID IS NULL
        UNION ALL
        SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
        FROM HumanResources.Employee
    ...
    See more | Go to post

    Leave a comment:


  • To pivot out data on different rows into seperate columns try using a case statement.

    SELECT CASE WHEN Option = 100 then value else null end as Option100,
    case when Option = 101 then value else null end as Option101
    from Table

    If you dont' want to use a case statement, let's say because you have 10 or more options you could use a cursor.
    Iterate through the rows, add a column to a temporary table...
    See more | Go to post

    Leave a comment:


  • Infide
    replied to Manager Hierarchy (Recursive)
    That's quite the task. But if you want a recursive function, here you go..

    Code:
    CREATE FUNCTION dbo.GetHierarchy(@ManagerID int)
    RETURNS @Hierarchy table (ParentID int,
    			       ChildID int)
    AS
    BEGIN
    
    	--EMPNUM, --MANAGEREMPNUM, --TblEmployee
    
    	declare @ChildID int
    	if (@ManagerID int is null)
    	begin
    		declare curEmp cursor local fast_forward for
    ...
    See more | Go to post

    Leave a comment:


  • Code:
    Update Creature_proto
    set 'health' = cp2.Health
    from antrix2.Creature_proto cp2
       inner join antrix.creature_proto cp1
            on cp1.entry = cp2.entry
    ...
    See more | Go to post

    Leave a comment:


  • I do not believe you can use stored procedures in this way, at least not in sql server. If you create functions you could call them in the way you are trying to call the stored procs....
    See more | Go to post

    Leave a comment:


  • Infide
    replied to Help!!!
    If I understand correctly, you are trying to insert data from one table into another.

    If these tables exist on different platforms you will need to write an application that does this.

    I will assume both tables are in the same database.

    INSERT INTO Table1(Column1, Column2)
    SELECT Column1,Column2
    FROM Table2...
    See more | Go to post

    Leave a comment:


  • This is a common permissions problem.
    Make sure the account you are using is added to the sql database and has the appropriate permissions. Regardless of whether you are using windows authentication or a sql account you must add the user to the database and set the permissions.

    If you are still having issues please post your connection string and tell us what kind of security you have....
    See more | Go to post

    Leave a comment:


  • Infide
    replied to Identity Column Values.
    You could turn identity off and on but I don't recommend this. It's not maintainable.

    Code:
    set identity_insert[mydatabase.dbo] mytable on
    Now you can insert whatever identity you want as long as it's unique.

    Keep in mind that sql server keeps all transactions logged. If you delete a record from a table with the ID of 50, that record actually still exists, you just don't see it. Now if you insert...
    See more | Go to post

    Leave a comment:


  • I'm not exactly sure what you're trying to accomplish. But this may get you started.

    Code:
    create proc usp_Example
    (@Value1 varchar(50),@Value2 varchar(50))
    AS
    BEGIN
    
    DECLARE @ParentTablePrimaryKey INT
    
    Insert into ParentTable(Column1)
    values(value1)
    
    set @ParentTablePrimaryKey = @@IDENTITY
    
    Insert into ChildTable(Column1,fk_ParentTableID)
    ...
    See more | Go to post

    Leave a comment:


  • Code:
    SELECT T1.*, T2.*, bootydata.*
    FROM TABLE1
    inner join table t2
        on t2.id = t1.id
    inner join
        (SELECT Max(bootycalls), t3.id,t2.id
            FROM 
            (SELECT count(*) as bootycalls, t3.id, t2.id
                     from table3 t3 inner join 
                             table t2 on t2.id = t3.id
                     GROUP BY t3.id, t2.id) as BootyData
    on t2.id = bootydata.id
    ...
    See more | Go to post

    Leave a comment:


  • try

    Code:
    SELECT T1.*, T2.*, bootydata.*
    FROM TABLE1
    inner join table t2
    	on t2.id = t1.id
    inner join
        (SELECT Max(bootycalls), t3.id,t2.id
    		FROM 
    		(SELECT count(*) as bootycalls, t3.id, t2.id
                     from table3 t3 inner join 
                             table t2 on t2.id = t3.id
                     GROUP BY t3.id, t2.id) as BootyData
    on t2.id =
    ...
    See more | Go to post

    Leave a comment:


  • Infide
    replied to Help!!!
    Can you give us any more specifics?

    What kind of program are you writing, what are the specs? What platform/programming language are you using?...
    See more | Go to post

    Leave a comment:


  • Infide
    replied to Need to find 'yesterday'
    Most likely though he will want the entire date range for yesterday. GETDATE() - 1 will exclude all times from the current time to midnight.

    For example, a result that had a date of 7-5-2007 7:35:23 would not be returned in your query.

    Another solution would be either.
    Select *
    from table
    where DATEPART(dd,cre ationdate) = datepart(dd,dat eadd(dd,-1,getdate())
    and datepart(mm,cre ationdate) =...
    See more | Go to post

    Leave a comment:


  • Infide
    replied to Database Design Question.?
    Here's how I would solve this problem.

    Three tables.
    Image table. pk_ImageID, description, path, etc..
    Orders table. pk_OrderID, fk_CustomerID, order stuff, etc....
    OrderImage table. pk_OrderImageID ,fk_ImageID,fk_ OrderID

    This will give you the many to many relationship yet keep your image and order table normalized....
    See more | Go to post

    Leave a comment:

No activity results to display
Show More
Working...