Viewing 15 posts - 54,616 through 54,630 (of 59,098 total)
You've seen simple equi-joins go bad? Any chance you have an example of one that has gone bad?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 15, 2007 at 8:07 am
Heh... too bad I don't have SQL Server 2k5, yet... would be an interesting test 'cause, you're right, Regex is very fast.
The fastest option, though, would be to have correctly...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 15, 2007 at 8:00 am
I agree... it seems like the called proc is for a GUI. If it's not too complex (or even if it is), you might want to consider rewritting it...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 15, 2007 at 7:50 am
Agreed... 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
October 15, 2007 at 7:40 am
Great! Thanks for the feedback, Bob!
--Jeff Moden
Change is inevitable... Change for the better is not.
October 14, 2007 at 10:11 am
jezemine (10/13/2007)
any DBA that doesn't allow use of xp_cmdshell is likely not going to allow the import of...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 14, 2007 at 9:32 am
What do you mean by "client side data table", Lowell?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2007 at 8:26 pm
When you get what you want, please post your solution. Thanks. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2007 at 8:24 pm
Heh... you just need to look it in the eye... 😉
[font="Courier New"]
--===== This is just to build some sample data and is NOT part of the solution
DECLARE @yourtable TABLE (FromUser VARCHAR(10), ToUser VARCHAR(10), Date DATETIME)
 INSERT INTO @yourtable (FromUser, ToUser, Date)
 SELECT 'user1','user2', '20070115' UNION ALL
 SELECT 'user2','user1', '20070116' UNION ALL
 SELECT 'user1','user2', '20070117' UNION ALL
 SELECT 'user1','user3', '20070118' UNION ALL
 SELECT 'user3','user1', '20070118' UNION ALL
 SELECT 'user1','user2', '20070201' UNION ALL
 SELECT 'user2','user1', '20070205' UNION ALL
 SELECT 'user1','user2', '20070301' UNION ALL
 SELECT 'user1','user3', '20070310' UNION ALL
 SELECT 'user3','user1', '20070311'
--===== This is the solution
 SELECT d.FromUser, d.ToUser, d.YrMon, COUNT(*) AS TimesConnected
   FROM (--==== Derived table swaps users when needed
         SELECT FromUser, 
                ToUser,
                REPLACE(CONVERT(CHAR(7),Date,102),'.','-') AS YrMon
           FROM @yourtable
          WHERE FromUser < ToUser
          UNION ALL
         SELECT ToUser AS FromUser,
                FromUser AS ToUser,
                REPLACE(CONVERT(CHAR(7),Date,102),'.','-') AS YrMon
           FROM @yourtable
          WHERE ToUser <= FromUser
        ) d
  GROUP BY d.FromUser, d.ToUser, d.YrMon
[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2007 at 8:22 pm
You really think filtering on an OPENROWSET would be faster than just importing the table?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2007 at 4:46 pm
This isn't Oracle... no need to slow down the update with correlated subqueries like that... just do a simple join like some of the other posters have.
Recommend you lookup UPDATE...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2007 at 4:36 pm
CASE WHEN a.col1 = b.col1 THEN 1 ELSE 0 END
+ CASE WHEN a.col2 = b.col2 THEN 1 ELSE 0 END
+ CASE WHEN a.col3 = b.col3 THEN 1 ELSE 0 END
+...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2007 at 4:27 pm
Sure...
[font="Courier New"]--===== This is just to build some sample data and is NOT part of the solution
DECLARE @yourtable TABLE (FromUser VARCHAR(10), ToUser VARCHAR(10))
 INSERT INTO @yourtable (FromUser, ToUser)
 SELECT 'user1','user2' UNION ALL
 SELECT 'user2','user1' UNION ALL
 SELECT 'user1','user2' UNION ALL
 SELECT 'user1','user3' UNION ALL
 SELECT 'user3','user1'
--===== This is the solution
 SELECT d.FromUser, d.ToUser, COUNT(*) AS TimeConnected
   FROM (--==== Derived table swaps users when needed
         SELECT FromUser, ToUser
           FROM @yourtable
          WHERE FromUser < ToUser
          UNION ALL
         SELECT ToUser AS FromUser,
                FromUser AS ToUser
           FROM @yourtable
          WHERE ToUser <= FromUser
        ) d
  GROUP BY d.FromUser, d.ToUser[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2007 at 4:17 pm
SQL Server has a LEFT and a RIGHT... no need to build a special function to do it as bad as Oracle 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2007 at 4:04 pm
Sure... A thing called "Books Online" should become your best friend. One way of getting to it is to open Query Analyzer, click on the [Help] button, and select...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2007 at 4:00 pm
Viewing 15 posts - 54,616 through 54,630 (of 59,098 total)