Aggregate Bitwise OR
Dec 4, 2003I was doing a SUM on my returned rows and i found that what i really want is an aggregate bitwise OR on all the returned rows. Do you know what's the function for that?
Thank you
ADVERTISEMENT
Aggregate Bitwise OR On A Column
Oct 22, 2014I am trying to get a culmulative Bitwise OR operation on a column by query - rather than using a CLR function.
The problem is shown below:
CREATE TABLE #Operations (
PK INT NOT NULL IDENTITY(1,1),
UserName VARCHAR(50) NOT NULL,
UserProcess VARCHAR(50) NOT NULL,
ServerOperation VARCHAR(50) NOT NULL,
Permission INT NOT NULL );
[Code] ....
So Far I've tried SUM - wrong results, and STUFF - which doesn't seem appropriate for bitwise operation - neither give useful results.
-- SUM Operation - fails
SELECT DISTINCT
UserName,
ServerOperation,
SUM(Permission) AS Permission
FROM #Operations
[Code] ....
It may be possible to materialise the permissions each time one is changed (e.g. by to use a cursor ), however I would prefer a query or function or combination to generate the result.
SQL 2012 :: Custom Bitwise OR Aggregate Function
Mar 18, 2014I want to create a custom bitwise OR aggregate function.
I want to use it like the built in aggregate functions (MIN, MAX etc.)
SELECT dbo.bitwise_or(bit_string) FROM table
where bit_string is a nvarchar(3)
so for example if the table contains two rows ('100', '001') the above query should return '101'
I would like to implement this as a CLR function/assembly with the aggregate below:
CREATE AGGREGATE dbo.bitwise_or (bit_string nvarchar(3))
RETURNS [nvarchar(3)]
EXTERNAL NAME [Aggregate].[bitwise_or]
I have followed this post to implement amedian aggregate function [URL] ..... but there is a lot of code (not sure what is really needed in my case).
Bitwise Help!
Nov 27, 2007Hi,
This is a crosspost, I also asked this question in the learnvisualstudio.net forum. I'm trying to engineer a bit of code in SQL that "decodes" a value stored in a table that represents an array of boolean values, stored using bitwise operations. I have the number, a reference of bit positions for each item, and the actual items checked per the program that normally processes this value. I'm trying to write a sproc that will perform actions based on the booleans in my list, but I can't get the same results the interface gives me.
Here's an example:
Value is 66756
Bit positions 2, 6, 7, and 10 should all return true. I was trying to deduce this using the following:
Code BlockSELECT CONVERT (BIT, <value> & (2 ^ <position>))
but that doesn't work at all. It's my (broken) translation of the code normally used to display this value as a checkbox, which comes from VB6 and looks something like this:
Code Block.ListItems(CStr() & "_").Checked = CBool <value> And (2 ^ <position>))
Can anyone tell me what I'm doing wrong? Thanks in advance.
Bitwise NOT Operation
Sep 6, 2006I was looking into some of the System stored procedures like sp_denylogin, sp_grantLogin etc. I found bit wise operations like
update master.dbo.sysxlogins set xstatus = (xstatus & ~1) | 2, xdate2 = getdate()
where name = @loginame and srvid IS NULL
How does the bitwise NOT(~) works with different datatypes like INT, SMALLINT and TINYINT?
Look at the following sample code and result. I just wanted to understand how it works with int and smallint
declare @a int
declare @b smallint
declare @c tinyint
select @a = 1, @b = 1, @c = 1
select @a=~@a, @b= ~@b, @c= ~@c
select @a, @b, @c
Result
~~~~~
-2-2254
Thanks in advance
Gnana
Non-bitwise Exclusive Or?
Jan 30, 2004Is there a way to do a logical exclusive OR (XOR) in sql server?
I'm trying to do this in where clause, something like:
WHERE
(not exists (select 1 from table a where a.date > '01/30/03') XOR
exists (select 1 from table a where a.date < '01/30/03'))
Thanks!
Bitwise Question -
Jul 23, 2005I am new to bitwise thing in MSSQL.Let's suppose there's a table of favorite foodsinsert int fav_foods(food_name,bitwiseVal)values('Pasta',1)insert int fav_foods(food_name,bitwiseVal)values('Chicken',2)insert int fav_foods(food_name,bitwiseVal)values('Beef',4)insert int fav_foods(food_name,bitwiseVal)values('Fish',8)insert int fav_foods(food_name,bitwiseVal)values('Pork',16)How do I write query to find people who selected more than one item andselected items from "Pasta, Chicken, Beef, Pork"(but not fish)?I hope my question is not confusing.....
View 6 Replies View RelatedDecoding Days Bitwise AND
Nov 11, 2004Hello All
I'm working on a recurring multi-day appointment program. Basically the user can choose a meeting on multiple days of the week over a span of time. For example: Tuesday and Thursday from 10:00 to 10:30 from December 1st 2004 to February 27th 2005.
So I've decided the best way to handle this is to assign a value to each day of the week like so:
MON = 1
TUE = 2
WED = 4
THU = 8
FRI = 16
SAT = 32
SUN = 64
So if the user picks TUE and THU that would be 2 + 8 = 10. The value is unique and seems to work.
So the values would be:
@begin_date = '12/01/2004'
@begin_time = '10:00 AM'
@end_date = '02/27/2005'
@end_time = '10:30 AM'
@recur_days = 10
Now I want to pass the values to stored procedure that will decode the recur_days variable and create entries in a table for each date. I'm struggling to figure out 2 things
1. How do I decode the 10 back into 2(TUE) + 8(THU) ( I think it has something to do with the bitwise AND "&" operator but I'm not sure how to use it.)
2. What is the best way to loop through the date range and create a record for each day?
Regards
Russ
Bitwise && Operator Does Not Work
Jan 15, 2007I would like to do something like a SELECT * FROM Files WHERE (Attributes & ?) but the & operator isn't recognized. I looked at the documentation sample for it and in the sample the & operator isn't used in the WHERE clause so I thought that might be the reason but I still can't get the operator to work. So even the doc sample code for it doesn't work.
View 3 Replies View RelatedCannot Perform An Aggregate Function On An Expression Containing An Aggregate Or A Subquery.
Oct 19, 2007Can any1 tell me why i am getting an error
SELECT DISTINCT
--p.voucher,
--p.amount,
p.siteID,
b.siteID,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS OutStandingBalance,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS CashCheque,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS Vouchers
FROM
BillingTotal b,
Payment p
--WHERE
-- s.sitename=@cmb1
--AND p.siteid = s.siteid
-- p.voucher = 0
-- p.voucher = 1
GROUP BY p.siteID,b.siteID
Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Analysis :: Calculated Member From Aggregate And Non-aggregate Factors
Sep 22, 2015I have 2 Dimensions in SSAS (see below end), I want to create a calculated member, named
This_Year_Billable_Objective_Count, with its formula = BillableLastYear*(100+ BillableObjective)/100.
The first factor, Â BillableLastYear is a number, aggregated (sum) from child units.
The second factor, Â BillableObjective is a percent number (for example 28 means 28%), it's not aggregate. It's an dependent value for each unit.
How can I calculate This_Year_Billable_Objective_Count for each unit?
\ able 1
SELECT [UnitKey]
   ,[UnitID]
   ,[UnitName]
   ,[Unit2Name]
   ,[Unit3Name]
   ,[Unit4Name]
[Code] .....
Query Trouble Regarding Bitwise Exclusive
Feb 25, 2001HI, i am trying to make query that has computations with it. but when there's a point computing between int and float i had to use cast() function to convert certain data types. somehow it only works on converting float to integer because when i'm converting an integer into float inorder to be computed with a float it bombs. my query is like this ....
SELECT cast(((cast(((lat - (SELECT LAT FROM TPS_ZIPUSA WHERE ZIP_CODE = 00210)) * 69.1) AS int) ^ 2) + (cast((69.1 * (lng - (SELECT Lng FROM TPS_ZIPUSA WHERE ZIP_CODE = 00210)) * (COS((SELECT LAT FROM TPS_ZIPUSA WHERE ZIP_CODE = 00210) / 57.3))) AS int) ^ 2)) AS float) ^ .5
FROM TPS_ZIPUSA
.5 is where the query bombs. any idea why is this happenning?
by the way, i'm using sql server 7.0.
francis,
Escaping Bitwise In Full Text Search 2005
Feb 6, 2007Hi,
We are facing a problem while passing a string containing the "&" character into Full Text search CONTAINS predicate. The records that do have this character are not being returned by the search.
I'd like to raise two questions:
1) Is there any special way to escape this character?
2) Does FTS index it at all?
We have tried all known (to us) ways of escaping like doubling the character, using char(38), using ESCAPE etc..Nothing seem to work. Any help would be appreciated.
Thanks,
Alex
How To Aggregate A Column That Was Itself An Aggregate
Jul 15, 2005I have a column that has an expression with a runningvalue in it, a "Carrying Cost" for each month. I need to create another column that aggregates the monthly Cost. I can't to do a Runningvalue on the Runingvalue. I can't even do a Sum on the Runningvalue.
View 9 Replies View RelatedUsing Aggregate
Mar 24, 2008I have a table that has 4 colums (id,projectno,date,price)
i want to make a select that returns the sum per project no
i used this query
select projectno,sum(pice) as sum
from supplier
group by projectno
but i want to include additional columns like id and date for the result
but its giving this message:
Column 'supplier.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
is there a better way to do so without joining the main table with the upper select query?
Best Regards
Aggregate On An Aggregate
Sep 25, 2007
I need to get the sum of a field that already has an aggregate function (MAX) performed on it. I am using the following query
Code Snippet
SELECT "tI"."ItemID", MAX("vSS"."ShortDesc") "Short Description",
MAX("tPCT"."FreezeQty") "Freeze Qty", SUM("vSS"."QtyOnHand") "Current Qty",
"tPCT"."BatchKey"
FROM ("vSS" "vSS"
INNER JOIN "tI" "tI"
ON "vSS"."ItemKey"="tI"."ItemKey")
LEFT OUTER JOIN "tPCT" "tPCT"
ON "vSS"."ItemKey"="tPCT"."ItemKey"
WHERE "vSS"."ItemID" = '3002954'
GROUP BY "tI"."ItemID", "tPCT"."BatchKey"
It yields the following results
ItemID
Short Description
Freeze Qty
Current Qty
BatchKey
3002954
SET, WRENCH HEX METRIC
-33
129
42221
3002954
SET, WRENCH HEX METRIC
51
129
42244
3002954
SET, WRENCH HEX METRIC
-31
129
42250
I need to SUM the maximum freeze quantity values per item ID. Therefore for this record, I need the following results:
3002954 SET, WRENCH HEX METRIC -13 129
Can this be done via a subquery? Any assistnance would be greatly appreciated?
Thanks,
DLee
Help With Aggregate Query - Can't Get It Right!!
Jun 17, 2007Hi,
I have we have a client who gives their invoices in a flat file format, we import it into a SQL Server table.
Nothing is normalized – everything is repeated in every record. The fields are:
customerNumberInvoice_numberPO_numberQtyDescriptionLine_numberLine_totalFreightTaxInvoice_date
So an if an order has 10 line items, the header information (invoice number, PO number, ivoice date) are repeated on each of the lines
I am writing a query to show the following
Order number, Invoice total, Date
select invoice_no, sum(line_total + freight + tax) as invoiceTotal, customerNumber, Invoice_date from invoices group by invoice_no, Invoice, customerNumber
This works great - for each invoice I get the invoice number, InvoiceTotal, and Date
Then I was asked to add the PO Number – this is where I can’t get it right.
When I added “PO_number� to the query, I got two lines for each invoice
select invoice_no, sum(line_total + freight + tax) as invoiceTotal, customerNumber, Invoice, PO_number from invoices group by invoice_no, Invoice, Sold_To_Cust_No, PO_number
Please help - I need to end up with: invoice_no, invoiceTotal, customerNumber, Invoice_date and PO_number (sequence does not matter)
Thanks
Aggregate Query Help
Dec 21, 2007I am attempting to wrie a query that will return aggregate totals from two different tables. The problem is that the TotalForecast totals are way to high. How do I write a query to obtain the correct totals?Table 1 - dbo.QM_ResultsColumns - dbo.QM_Results.Special8, dbo.QM_Results.SessionName, dbo.QM_Results.PersonNumberTable 2 - dbo.PM_ForecastViewColumns - dbo.PM_ForecastView.Hierarchy, dbo.PM_ForecastView.ForecastSelect substring(dbo.QM_Results.Special8,0,6) AS Hierarchy, substring(dbo.QM_Results.SessionName,0,11) As CourseCode,count(dbo.QM_Results.PersonNumber) TotalAssociates,sum(dbo.PM_ForecastView.Forecast) TotalForecastFrom dbo.QM_Results INNER JOIN dbo.PM_ForecastView ON dbo.PM_ForecastView.Hierarchy = substring(dbo.QM_Results.Special8,0,6)where SessionMid in ('96882139', '23620891', '45077427', '29721437')AND substring(dbo.QM_Results.Special8,0,6) in ('EZHBA')Group By substring(dbo.QM_Results.Special8,0,6),substring(dbo.QM_Results.SessionName,0,11)Sample of data returned with my current query.Hierarchy CourseCode TotalAssociates TotalForecastEZHBA CARD167200 1179 141480EZHBA CARD167201 1416 169920EZHBA CARD167202 1119 134280EZHBA CARD167204 99 11880Results when I run aggregate query separatelyActual Total takenHierarchy CourseCode TotalTakenEZHBA CARD167200 393EZHBA CARD167201 472EZHBA CARD167202 373EZHBA CARD167204 33Forecasted Total takenHierarchy CourseCode ForecastEZHBA CARD167200 999EZHBA CARD167201 900EZHBA CARD167202 800EZHBA CARD167204 800
View 4 Replies View RelatedAggregate Function
Feb 21, 2008Does anyone know how to make a query and use an aggregate function? This is my current code...any help would be great.
"SELECT tblTopic.Topic_ID, tblTopic.Subject, MAX(tblThread.Message_date) AS MessageDate, tblThread.Message FROM (tblThread INNER JOIN tblTopic ON tblThread.Topic_ID = tblTopic.Topic_ID) WHERE (tblThread.Message_Date LIKE '%' + @fldGenus + '%' GROUP BY tblTopic.Topic_ID, tblTopic.Subject, tblThread.Message">
Also, How can i limit the query to only bringing up 5 records?
I'm trying to get a datagrid to show the 5 most recent forum posts for a particular category.
Thanks.
Aggregate Functions
May 14, 2008I have a table that is used for employee evaluations. There are six questions that are scored either 1, 2, 3, 4, or 5. I want to tally the responses on a page, but I wonder if I can do it without 35 separate calls to the database (I also want to get the average response for each question). I know I can do "SELECT COUNT(intWorkQuality) AS Qual1 FROM dbo.Summer_Project_Req WHERE intWorkQuality = '1' " and then "SELECT COUNT(intWorkQuality) AS Qual2 FROM dbo.Summer_Project_Req WHERE intWorkQuality = '2' " and so on. But can I somehow do the aggregating at the page level, and just refer back to a datasource that uses a generic statement like "SELECT intWorkQuality, intDepend, intAnalyze, intWrite, intOral, intCompatibility FROM dbo.Summer_Project_Req"? If I can, I am not sure what type of control would be best to use or what syntax to use to write the code-behind. I would like the results to be displayed in a grid format. Thanks in advance for your help.
View 3 Replies View RelatedAggregate Sum Query, Need Help
Jul 11, 2005I have two tables tb1 with item and qtyOnHand and a second table tb2 with item and qtyOrdered I am trying without success to make this happen;select sum (onHand-Ordered) from (select sum (qtyOnHand) from tb1 where item = RD35 group by item) as onHand, (select sum (qtyOrdered) from tb2 where item = RD35 group by item) as OrderedI kind of gathered it would work based on this http://weblogs.asp.net/jgalloway/archive/2004/05/19/135358.aspxI have also tried this;select tb1.item from (select sum (qtyOnHand) from tb1 where item = RD35 group by item) as onHand, (select sum (qtyOrdered) from tb2 where item = RD35 group by item) as Ordered, sum (onHand-Ordered) as available from tb1 where tb1.item = RD35Any ides, there are multiple rows of each item in each table tb1 is inventory with several different locations and tb2 is an orders table.
View 3 Replies View RelatedStream Aggregate
Jul 24, 2001What I'm trying to solve:
I have an application that generates SQL queries, and sometimes uses
DISTINCT where the result set has no dupe rows. In terms of database
resources, I'm trying to figure out if it's worth it to change to app to be
smart enough to not use DISTINCT where it won't serve any purpose, or
whether to let it do the DISTINCT and save added complexity to the query
building application. I.e. what is the cost of DISTINCT where there are no
dupe rows?
What I want to know:
Can someone explain how the stream aggregate operator actually goes about
doing its work?
Does this always create a temp table for sorting and discarding duplicates
(for DISTICNT)? If the answer is "no or sometimes", how does it do so in
the case where a temp table is not involved? I noticed the the estimated
I/O for this operator was zero for some queries I wrote agains pubs. Does
this mean that the optimizer believes the temp table needed will fit
in-memory and creates it in-memory? Or does the estimated I/O figure not
included disk writes for work tables?
tia for any info
Bill
Aggregate Navigator
Jan 20, 2000Hello,
I was told that on Oracle there's something called an Aggregate Navigator which should be capable of changing the table you're addressing in a query to another table (with aggregate data) and in this way optimize performance in a data warehousing environment.
Is there anything similar in MS SQL Server?
Use Aggregate Values
Jun 6, 2003Hi all,
I have a table with the following fields:
carrier,calc_date,ind_id,rcf
I need to run a query to get the following result(by carrier and for each calc_date, calculate the percentage of all individuals who have rcf greater than 0.73):
carrier,calc_date,count of ind with rcf > 0.73, count of all individual, percentage of individuals with rcf's greater than 0.73.
does anyone have an idea of how to achieve that result?
Thanx
Aggregate Problem
Jun 26, 2002MS Access provides FIRST and LAST aggregates to select the first and last values from a group.
These aggregates apparently do not exist in TSQL.
I am after a workaround for the lack of these aggregates.
Any advice would be appreciated.
Aggregate Issue
Jan 26, 2008Hi, I am wanting to query the identifier field via the MAX function.
eg.
need to retrieve the BookID of the greatest Book Price
Code:
SELECT BookID,MAX(BookPrice)
FROM Book
this errors, with:
Column 'BookID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I realise there are other ways such as referencing back on the same table eg.
Code:
SELECT BookID FROM Book WHERE BookPrice = (SELECT MAX(BookPrice) FROM Book)
but was hoping would be a more direct way, is there a specific direct use of MAX for this purpose or another SQL command?
Qualifying What Goes In An Aggregate
Jun 15, 2006Greetings!
I'm hoping for a little insight for a novice.
Here's what I have:
1 table that looks like this...
Code:
rep_id answer
1 2
1 1
2 1
3 0
3 1
3 1
4 2
What I want is a recordset returned as ... (columns that tally the 1s and 2s respectively with the rep's id#)
Code:
rep_id total1 total2
1 1 1
2 1 0
3 2 0
4 0 1
What I can do so far is get individual recordsets returned for either total1 or total2 but not together.
Here's what I'm using for these...
Code:
SELECT rep_id, COUNT(answer) as total1
FROM myTable
WHERE answer = '1'
GROUP BY rep_id
and
Code:
SELECT rep_id, COUNT(answer) as total2
FROM myTable
WHERE answer = '2'
GROUP BY rep_id
So how do I form a SQL statement that will combine the 2 columns I have created into 1 joined by the rep_id?
To make it a little stickier there may be some nulls in the existing answers but hopefully that is not going to bung me up.
Any help will be thankfully received!!!
Aggregate Counting
Aug 17, 2006Hi,
How can I aggregate a top 5 count across two satellite tables?
e.g. Orders and downloads table each have multiple entries for the same customer ID I would like to count the orders and add them to the downloads count too e.g. 5 orders added to 10 downloads giving 15 as the total for this customer and get a total 'site activity' result which I would like to select the top 5 for.
Any help or pointers would be a great help!
Thanks.
Last Aggregate Function
Mar 9, 2007Hello, I need to use last() aggregate function in MS SQL Server 2005 but it is not built in.How to replace this functionality?Thanks.
View 4 Replies View RelatedAggregate Function
Apr 3, 2007I have three tables, tblschedule, tblresource and tblemployeename.
in tblschedule table there are scheduleID, resourceID and employeeID. In tblResource there are ResourceID and ResourceName. In tblemployeename there are EmployeeID, EmployeeFName and EmployeeLame. I want to have a report that show how many times the resource has been reserved by employee. i would like to have a report. Look like the following:
ResourceName EmployeeFName EmployeeLName (Or use EmployeeName) Number of record.
How to write a query? I use asp.net as front end.
Thank for your help.
The Aggregate For Several Fields In Same Row?
Jun 11, 2007I need to find an aggregate for several fields in a row
e.g. Max(date1, date2, ..., dateN)
I can pass this to a delimited string,
pass the string to an UDF that returns a table
and run Max(tablefield) on that UDF
Unfortunately I can only get this working for 1 delimited string at a time
Ideally I would want to include the function in a SELECT statement, e.g. something like
SELECT t1.a, dbo.MaxOfFieldValues(t1.d1+','+t1.d2+...+','+t2.dN )
FROM t1
I got it working with the following two udfs, but I am sure visitors here have solved this a bit smarter:
ALTER Function [dbo].[MaxOfFieldValues]
(
@ListOfValues varchar(8000)
, @delimiter varchar(10) = ','
)
RETURNS VARCHAR(8000)
AS
BEGIN
--Need to get the maximum changedate first
--pass the fields as one value (a delimited string)
--and calc the max
declare @result varchar(8000)
declare @remainder varchar(8000)
set @remainder = @ListOfValues
declare @NoOfItems int --items = delimiters +1
SET @NoOfItems = (len(@ListOfValues) - Len(Replace(@ListOfValues,@delimiter,''))/Len(@ListOfValues))+1
declare @counter int
set @counter =1
set @result = dbo.TakePart(@remainder,@delimiter,@counter)
WHILE @counter <= @NoOfItems
BEGIN
set @counter = @counter + 1
IF @result < dbo.TakePart(@remainder,@delimiter,@counter)
BEGIN
SET @result = dbo.TakePart(@remainder,@delimiter,@counter)
END
END
RETURN (@result)
END
ALTER FUNCTION [dbo].[TakePart]
(
@param varchar(8000)
, @delimiter varchar(10)
, @NumPart int
)
RETURNS varchar(8000)
AS
BEGIN
--Note: maybe smarter to whack the delimiter to the end of the string to avoid the IF statement
declare @result varchar(8000)
declare @remainder varchar(8000)
declare @counter int
set @result = ''
set @remainder = @param
set @counter = 1
WHILE @counter < @Numpart
BEGIN
SET @remainder = SUBSTRING(@remainder,CHARINDEX(@delimiter,@remaind er,1)+Len(@delimiter),8000)
SET @counter = @counter +1
END
IF @counter > (len(@param) - Len(Replace(@param,@delimiter,''))/Len(@delimiter))
BEGIN
SET @result = @remainder
END
ELSE
BEGIN
SET @result = LEFT(@remainder,CHARINDEX(@delimiter,@remainder,1) -1)
END
RETURN @result
END
Cheers
Drio
Regarding Aggregate Conditions ..
Nov 29, 2007Hai frns small help needed.
I have a table called sample and i have the following requirement. i.e i need sum(credit) group by ssn no.
One special condition is as follows:
For each distinct ssn if "flag" has the same CX value,then out of all the records with the same CX value, the highest "credit" value is added to the sum for that "ssn" and the rest are ignored.
If while adding "credit" to the sum and if "credit" value is equal to zero then "sum" value is used for summing else "credit" value is used.
Can any one help me out in trying this logic. I have tried but i could'nt able embed the conditions inbetween the Sql statetment.
Here is the query is used
select * from sample
idssncreditflagsem
11010C90
21014C93
31014.5C92
41013.5C11
51024.2C33
61030C12
select ssn,flag,sum(case credit when 0 then sem else credit end) as sum from sam2
group by ssn,flag
ssn flag sum_val
101C13.5
103C12.0
102C34.2
101C98.5
The above output is wrong one.
Expected output
101 4.5+3.5=8.0
102 4.2
103 2.0
Any help would be appreciated
Regards,
Aggregate Comparisons??
Dec 12, 2007I have implemented a login audit on a particular system which catches the users login details, including their application logon name and NT username.
What I want to do is report on users who have logged on to the software using someone else's workstation (i.e. logged on to more than one workstation).
Here's some sample stuff to play with
DECLARE @logins table (
loginName char(20)
, ntUsername char(25)
, loginDate datetime
)
--Insert test data. Please note that loginName and ntUsername are rarely the same
INSERT INTO @logins (loginName, ntUsername, loginDate)
SELECT 'Amy', 'Amy', '20070101' UNION
SELECT 'Amy', 'Amy', '20070102' UNION
SELECT 'Amy', 'Amy', '20070103' UNION
SELECT 'Bob', 'Bob', '20070101' UNION
SELECT 'Bob', 'Bob', '20070102' UNION
SELECT 'Bob', 'Amy', '20070103' UNION --Bob has logged on using 2 different NT accounts
SELECT 'Cal', 'Cal', '20070102' UNION
SELECT 'Cal', 'Amy', '20070102' UNION --So has cal
SELECT 'Dom', 'Dom', '20070102' UNION
SELECT 'Dom', 'Dom', '20070102'
Any ideas? I just can't think of the logic needed to get what I want.
Any extra info needed - just ask!
Cheers