Viewing 15 posts - 54,586 through 54,600 (of 59,067 total)
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...
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...
October 15, 2007 at 7:50 am
jezemine (10/13/2007)
any DBA that doesn't allow use of xp_cmdshell is likely not going to allow the import of...
October 14, 2007 at 9:32 am
What do you mean by "client side data table", Lowell?
October 13, 2007 at 8:26 pm
When you get what you want, please post your solution. Thanks. 😉
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]
October 13, 2007 at 8:22 pm
You really think filtering on an OPENROWSET would be faster than just importing the table?
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...
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
+...
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]
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 😉
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...
October 13, 2007 at 4:00 pm
Viewing 15 posts - 54,586 through 54,600 (of 59,067 total)