Viewing 15 posts - 136 through 150 (of 2,171 total)
Start with the simple things to see if the algorithm calculates the correct usernames.
Then, and only then, streamline things to suit your presentation needs.
The cross apply seeks the table for...
July 22, 2011 at 5:52 am
Here is a solution for you
CREATE TABLE#Sample
(
ComputerName VARCHAR(100),
UserName VARCHAR(100),
LogonTime DATETIME
)
DECLARE@a INT = 100000
INSERT#Sample
SELECT TOP(@a)ABS(CHECKSUM(NEWID())) % 20 AS ComputerName,
ABS(CHECKSUM(NEWID())) % 20 AS UserName,
DATEADD(SECOND, ABS(CHECKSUM(NEWID())) % 86400, '20110722') AS LogonTime
FROMmaster..spt_values AS v1
CROSS...
July 22, 2011 at 4:01 am
bjorn.gabrielsson (7/22/2011)
easy? 🙂
Well... Not me, but the solution is.
July 22, 2011 at 3:50 am
Use IMEX=1 in your extended properties for the driver.
July 22, 2011 at 3:48 am
Isn't this problem of finding a streak? If so, there is a linear solution like this
;WITH cteSource(ID, Value, GrpID)
AS (
SELECTID,
Value,
ROW_NUMBER() OVER (ORDER BY ID) - ROW_NUMBER() OVER (ORDER BY ABS(Value),...
July 21, 2011 at 6:26 am
We cannot always alter the existing schema. However, we can mimic 1NF with code!
This is a simplified query
SELECTs.StaffID,
MAX(CASE WHEN f.Priority = 1 THEN f.Number ELSE '' END) AS Phone1,
MAX(CASE WHEN...
July 21, 2011 at 6:14 am
Check out the article by Itzik Ben-Gan on how to calculate "Concurrent Session".
When doing so, it's very easy to include the sum of percentages.
July 19, 2011 at 1:06 am
SQLkiwi (7/15/2011)
No, of course not. Neither SUM nor COUNT can use OVER ... ORDER BY before Denali.
I think Peter meant if existing code was ported to Denali and the...
July 15, 2011 at 7:37 am
Only if you add the ORDER BY clause, then the default framing will be "between unbounded preceding and current row".
If the ORDER BY clause is not there, the default framing...
July 15, 2011 at 4:27 am
There is an additional clause for the order by part, which defaults to "first row" to "current row" in the partition window.
These three options are avaiable with the RANGE predicate:
RANGE...
July 15, 2011 at 3:34 am
You can download it for free to install on your own computer.
July 15, 2011 at 2:58 am
Simplify!
;WITH cteSource(CustomerID, StartDate, EndDate)
AS (
SELECTCustomerID,
MIN(theDate) AS StartDate,
MAX(theDate) AS EndDate
FROM(
SELECT t.CustomerID,
f.theDate,
DENSE_RANK() OVER (PARTITION BY t.CustomerID ORDER BY f.theDate) / 2 AS SeqID
FROM#Test AS t
CROSS APPLY(
VALUES(t.StartDate),
(DATEADD(DAY, -1, t.StartDate)),
(t.EndDate),
(DATEADD(DAY, 1, t.EndDate))
) AS f(theDate)
)...
July 1, 2011 at 5:01 am
SQL Server created an execution plan based on certain costs.
So there is no guarantee your multi-use of ColumnValue column is always the same.
If SQL Server thinks casting all values to...
June 23, 2011 at 3:28 pm
Are there other users working in the database?
A SELECT INTO puts a table lock on each involved tables in your query to prevent schema alterations during the duration of query.
If...
June 16, 2011 at 12:14 am
Viewing 15 posts - 136 through 150 (of 2,171 total)