Code Snippet
ALTER PROCEDURE [dbo].[Read_Current_User]
-- Add the parameters for the stored procedure here
@LoginUserName varchar(50) OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- get current user name
SET @LoginUserName=(SELECT [NomosConfig].[dbo].[Users].[UserName]
,[NomosConfig].[dbo].[Users].[Id]
,[NomosConfig].[dbo].[Users].[FirstName]
,[NomosConfig].[dbo].[Users].[LastName]
,[NomosConfig].[dbo].[Users].[Email]
FROM [NomosConfig].[dbo].[Users]
WHERE [NomosConfig].[dbo].[Users].[LoggedIn]=1
END
How can I set my @LoginUserName output parameter to be a combination of fields [NomosConfig].[dbo].[Users].[FirstName] + [NomosConfig].[dbo].[Users].[LastName] ?
As a result I should get @LoginUserName = FirstName + ' ' + Lastname
My query would have the form: select BetId where GameId in(108,109) from Bets then it has to get me BetId : 500 and 502. Not 501,since this is different combination(108,109,110) ;)
I want BetId 500 and 502 to be returned as result if i give select criteria where game id = 108,109. Pls.Note: It should not return BetId 501 in the result, since it belongs to different combination(108,109,110). Similarly if i give, select criteria where game id =(108,109,110) it should return BetId 501.not the 500 and 502..which is different combination..
Hope i clarified my problem..pls help me in this regard.Thanks a lot...
I have one table and I am trying to accomplish two tasks, generate all possible combinations and the probability of these combinations occuring. My sample data is below:
Time of Incident 7AM 8AM 9AM 10AM 11AM 12PM 1PM 2PM 3PM 4PM 5PM 6PM 7PM
Location Off-Site OnSite PC Off-Site Virtual
IssueStatic CR CR Unable to Hear caller Caller Unable to Hear CR Garbled Speech Echo
Call disposition Able to Handle Call Caller Initiated Disconnect Transfered to 5508 Transfered to CCV
Provider AT&T U-verse Cox Cable Mercury Time Warner VA Network
I want a list of rows in one table where a combination of two columns aren't in another table. One column is character and the second column is date/time. This happens to be SQL 2005. My first attempt using only one column worked OK:
SELECT * FROM TABLEA WHERE CUSTID NOT IN (SELECT CUSTNO FROM TABLEB)
But when I added the combination of customer number and sales date, it made the query "run away" - I had to cancel it and it didn't return anything:
SELECT * FROM TABLEA WHERE CUSTID + CONVERT(VARCHAR, SLS_DATE, 101) NOT IN (SELECT CUSTNO + CONVERT(VARCHAR, SALES_DATE, 101) FROM TABLEB)
I wonder what might be wrong, but also whether this is the correct approach to begin with.
Hi there, my situation is I have a table x with 3 filed a nvarchar(100), b smalldatetime, c text(16) . I want to create a view like this: select a + ' ' + b + ' ' + c as all_field from x where all_field like %my_str%
So, I always get a message error said wrong datatype, how can i do, please help me.
1.For MonthIDs 309 & 307, I would need to add same combinations to 4 prior months (for 309, I would need the 309 row as 308, 307, 306, 305 and for 307, I would need to add 306, 305, 304, 303).
2.From the snapshot, Green rows are added for 309 MonthID and Orange rows are added for 307.
3.While we are adding 309 row to prior to 4 months, if any prior month has different combination than 309, that same combination need to be repeated for prior months.
I was using the below code to achieve the same:
declare @SC_j as int = 0; declare @SC_k as int = 4; while @SC_j <= 4 begin insert into #Test select distinct dt.CustomerID
[code]....
But my code starts from 1st MonthID however I would want this to be starting from last MonthID which I am not able to achieve.
For a class, I need to write SQL statements for an assortment of problems and I'm stuck on the last few:
What is the most popular combination of 2 products that are ordered simultaneously? The most popular combination is defined as follows: Consider any combination of two products such as (LS200A, ATM50A). Looking at all orders, count the number of times these two products were present in an order. An order could of course have other products in it than just these two, so you are not limited to examining only orders with exactly two products. If you repeat this process for all possible combinations of two products, then that combination which was present the highest number of times in all orders is the most popular combination of two products that were ordered simultaneously. The quantities ordered are not relevant in this context.
The result of the query should look something like the following:
First Product ----- Second Product ------ Max Number of Times --- LS200A --------- ATM50A ------------------- 7 -----------
I know little to no SQL, just the basics of it and my professor is pretty terrible at teaching it. I'm thinking I'll need to make a virtual table with a CREATE VIEW but I'm not sure where to go after that. I also need to write statements for the most popular combination of three products. If anyone could give me a hand, I would really appreciate it. Thanks a lot.
Having a brainfart....I need a query that returns a record count, based on two distinct fields.For example:Order Revision Customer001 1 Bob001 2 Bob002 1 John003 1 John004 1 John005 1 Bob006 1 Bob006 2 BobThe query on the above data should return a count of orders, regardless ofthe revision numbers (each order number should only be counted once).So WHERE Customer = 'Bob', it should return OrderCount = 3TIA!Calan
I have a table with 3 columns and 20 million records. first 2 columns have VARCHAR(4) data type and third column is VARCHAR(5000). I put 3rd column under FULLTEXT and implement a normal INDEX on 1st column. Now when i try to search
SELECT
TOP 20
col1, col3 FROM
tbl WHERE
col1 = '1234' AND
CONTAINS(col3,'"market*"')
I am facing following problems 1- It hang for like 1 minute and give 2 records, whereas if i remove col1='1234' from where clause it take less than 1 second. 2- Some time it show criteria is too complex, although i am only requesting a single word in col3.
I am noob in FULL-TEXT but i have done all research in books, microsoft forum and Google and not getting any information.
I am trying to calculate the work experiance of an employee. I am using the function DATEDIFF for that. For eg:
Code SnippetDATEDIFF(yy,JoiningDate,GETDATE())
But this will display the date difference in years, if I use mm, instead of yy then I will get the result in months.
But I want to dislay the result as a combination of year and month, like if an employee has 13 months of experiance then i want to get the result as 1.1 years. Is that possible using SQL? Please help. All your helps will be appriciated.
I have this data as below. I need to find out the combination from the data and take a count of them
CREATE TABLE A ( nRef INT, nOrd INT, Token INT, nML INT, nNode INT, sSymbol VARCHAR(50), nMessageCode INT ) INSERT INTO A ( nReferenceNumber,nOrderNumber,nTokenNumber,nML,nNode,sSymbol,nMessageCode ) VALUES (1, 101, 1001,0,2,'SILVER',13073),
[code]....
if you can see, the rows with column nRefNo 1 and 3 are same i.e. with same combination of Symbol viz. Silver and Castorseed. How to get this combination together and then take count of them. Please note i will be dealing with more than 5 million rows.
how this can be achieved by writing a procedure in SQL Server DB.Remember that if there is a non-null value in the benchmark that is not equal to the equivalent criteria in the Hosting input then there is no match. Therefore those with region = IN would not match any of the given benchmarks. It is best fit as long as the non-matching values are to null value benchmark criteria.
Hosting_Site (Source File) YEAR MONTH HOSTING CLASS REGION HOSTING COST 2015 1 A UK 75 2015 1 A IN 80 2015 1 B IN 60 2015 1 C US 100
BENCHMARK (Lookup Table) Row Year Month Hosting class Region BENCHMARK COST 1 2015 1 A US 100 2 2015 1 B US 200 3 2015 1 A UK 50 4 2015 1 A null 70
STANDARD CLASS COST (If the criteria between source file and the lookup file doesn't match then have to load this cost)
Row Year Hosting class BENCHMARK COST 1 2015 A 22 2 2015 B 33
Then result should be:
FACT LOAD:
YEAR MONTH HOSTING CLASS REGION HOSTING COST BENCHMARK COST 2015 1 A UK 75 50 2015 1 A IN 80 70 2015 1 B IN 60 33 (Since for B class there is no region = IN or = null the Standard Class Cost must be used) 2015 1 C US 100 ??? (Reject record since there is no default value in Standard Class Cost or in Benchmark)
I have a case where a table has two candidate primary keys,but either (but not both) may be NULL. I don't want to storea copy of the concatenated ISNULL'ed fields as an additionalcolumn, though that would work if necessary. Instead, I triedthe following (this is a related simplified example, not myreal one):CREATE FUNCTION ApplyActionPK(@IP int = NULL,@DNS varchar(64) = NULL)RETURNS varchar(74) -- NOT NULLASBEGINdeclare @val varchar(74)set @val = str(ISNULL(@IP, 0), 10)set @val = @val + ISNULL(@DNS, '')return @val-- Also tried "return str(ISNULL(@IP, 0), 10)+ISNULL(@DNS, '')"-- Also tried "return ISNULL(STR(@IP, 10), ISNULL(@DNS, ''))"-- ... and other things...ENDGOcreate table ApplyAction(-- An action applies to a computerAct varchar(16) NOT NULL,-- The action to applyIP int NULL,-- The computer IP address, orDNS varchar(64) NULL,-- The DNS name of the computerTarget as dbo.ApplyActionPK(ComputerID, DNS), -- PK value-- Also tried "Target as ISNULL(STR(@IP, 10), ISNULL(@DNS, ''))"CONSTRAINT PK_ApplyAction PRIMARY KEY(Act, Target))SQL Server always complains that the primary key constraint cannot becreated over a nullable field - even though in no case will the 'Target'field be NULL.Please don't explain that I should store an IP address as a string.Though that would suffice for this example, it doesn't solve myactual problem (where there are four nullable fields, two of whichare FKs into other tables).What's the reason for SQL Server deciding that the value is NULLable?What's the usual way of handling such alternate PKs?Clifford Heath.
I have a perfectly working application, accessing a SQL Express database, displaying data and images.
Now I am trying to add a Crystal Report. I add a new connection (OLE DB (ADO)) The connection points to my .MDF database. I drag the fields I want to print to the report, including an image. Now I click the Report Preview. I get a message saying "The database table "Table-Name" can not be found, Proceed to remove this table from the report?" Yes/No. I select NO, and the preview shows up correctly.
Happily I continue to execute the program in debug mode. At startup I get errors saying that login failed. To get around this error, I have to close down Visual Studio and then open it up again, running the program without touching the Crystal Report Design. Now the program starts up normaly without problems.
Now comes the real strange behavior. I click on my link to request the Crystal report and I get a request to login to the database. I try to login but nothing is accepted. I again close down Visual Studio and open up SQL Server Management Studio Express. I connect to SQL Express and expand "Databases" MY .MDF file is listed but the + sign in front of it is missing and I have to delete the database and attach it again to get it back.
Where is this problem occuring????? I have no idea. Please help.
I have images on which users may comment. Now I want to sort those images on the amount of comments each image has.I use custom paging in combination with ROW_NUMBERSo I want to sort items in one table based on the number of occurences of that item in another table...I have the following query (which doesnt work):SELECT *FROM(select ROW_NUMBER() OVER (ORDER BY mediaComments DESC) as RowNum,m.title,m.usercode,m.mediaid,mediaComments=(select count(*) from tblMediaComments where mediaid=m.mediaid)FROM tblAlbums a inner join tblMedia m on am.mediaid=m.mediaidleft join tblMediaComments mc on mc.mediaid=m.mediaidWHERE a.showTo=1group by m.title,m.usercode,m.mediaid) as InfoWHERE RowNum between @startRowIndex AND (@startRowIndex + @maximumRows-1)
Hi, I need to concatenate a string with an int variable on a stored procedure; however, i looks like i am lost in single and double quotes. Does any one know the right comination of quotes for this please? My Code is below: 1 @Price int 2 3 DECLARE @SqlPrice varchar(50) 4 5 if (@Price is not null) 6 7 set @sqlPrice = 'AND price' + '' > '' + '' + @Price + '' 8
I would like to build a report with nice functionalities like filter, sorting, drill-down, something like a PowerPivot Table, but with some layout/design/format capabilities. I would also want to publish the report, refresh it let´s say once a week, notify users when a new version is available, etc.
If I use PowerPivot, then I am not able to customized the report or to mix data from different sources in one table.
If I convert the cells of the PowerPivot table to workbook formulas I lose the filter, sorting, etc functionalities.
I still have to try using Reporting Services, but I think that always something is missing.
create table #temp1 (name varchar(5), id int) insert into #temp1 (name, id) ( select 'a', 5 union select 'a', 8
[code]....
As a result I would need every name from #temp2, where both searchred id's (5 & 8) from #temp1 are included.In this example i would like to get 'e' as a result, because in #temp2 'e' has the id's 5, 8 and 25.I've tried using cross apply, but cross apply returns every Name that have one of the ids... in this case it would also return 'c'...
Selectdistinct b.name from( Selectdistinct name , id from#temp1 wherename = 'a' ) as a cross join( Selectdistinct name , id from#temp2 ) as b wherea.id = b.id
I need a query to produce permutation combination.
declare @t2 as table (tab varchar(100)) insert into @t2 values ('V') insert into @t2 values ('VL') insert into @t2 values ('1099') insert into @t2 values ('VOI')
declare @t1 as table (tab varchar(100)) insert into @t1 values ('I') insert into @t1 values ('U') from the above I need following output (attached output),
Id StdId TeacherName Day subject 1 1 Archana Monday English 2 1 Archana Tue Marathi 3 1 Shama Wed Hindi 4 1 shama Thus Hindi 5 1 Kavita Fri Hindi 6 2 Archana Mon english 7 2 Dipti Tues Hindi
Second table : Student
Id Sname Cid 1 Shalini 1 2 Monika 1 3 Rohan 3
I want to fetch uniq combination of stuid and subject.Result should show all subject of student whether may be teachername and day. If I choose shalini whose stuid is 1,all subject for shalini(hindi,english,marathi) should come. Record from either of three should come
Id StdId TeacherName Day subject 3 1 Shama Wed Hindi 4 1 shama Thus Hindi 5 1 Kavita Fri Hindi
I want fetch studentname along with teachername,day and subject whose cid = 1 here is my query
select Student.Sname,TeacherName, Day,subject from StudentTeacherRelation inner join Student Student.id = StudentTeacherRelation.StuId where cid = 1
I want place result of it in temp,Want fetch max(id) from temp table by doing group by on Sname and Subject.find all id from temp table where that id present in max id.
show Id StdId TeacherName Day subject where (1,2,3,4,5,6,7-- all id from temp) in (1,2,5,6,7 -- max id from temp by doing group by on Sname and subject)
So it will show record Id StdId TeacherName Day subject where id is 1,2,5,6,7.Only five record should come.How to do that?
please excuse my ignorance but if someone could point me in the right direction i would be grateful.
i have produce a chart with some data against date points on x axis. i have another date field which i want to combine with the orignal date axis is this possible .
ie
table
results planned date Acutual date 10 02/03/07 01/03/07 46 03/03/07 03/03/07 60 06/03/07 07/03/07
i want to combine the planned date and actual date as an x axis
I have a scenario where I need to develop a stored proc to identify invalid input provided.
Following is a sample scenario
Create table product (ProductId varchar(10),SizeId int,ProductColor varchar(10)); insert into Product select 'Prod1',10,'Black' union ALL select 'Prod1',10,'BLue' union ALL select 'Prod2',20,'Green' union ALL select 'Prod2',10,'Black' ;
[Code] ....
In following TSql Code , Color and Size are optional. Both are provided as comma separated input. I have provided "bbc" as wrong color and "MM" as wrong size. I want to identify if color is invalid or size (MM is in valid for Black and Blue) and to set flag accordingly.
On our datawarehouse server we are regularly having a 1203 error, causing the sql-server to hang. We get this message in the errorlog: Failed Assertion = 'm_activeSdesList.Head () == NULL'. In the knowledgebase I found a bug description that is very lookalike to our problem.
Article: Q240853 FIX: Lock Escalation With Parallel Query May Cause 1203 Error And Server Shutdown
*** part of the article *** SYMPTOMS If a lock escalation occurs while running a parallel query, it is possible to encounter error message 1203 as follows:
spid7 Process 7 unlocking unowned resource: KEY: 13:117575457:2 (35010560ebcd) spid7 Process 7 unlocking unowned resource: KEY: 13:117575457:2 (35010560ebcd) spid7 Error: 1203, Severity: 20, State: 1 spid7 Process ID 7 attempting to unlock unowned resource KEY: 13:117575457:2 (35010560ebcd). spid7 Error: 1203, Severity: 20, State: 1 spid7 Process ID 7 attempting to unlock unowned resource KEY: 13:117575457:2 (35010560ebcd).
The error message included in the error log probably mentions the same lock resource in several of the error messages.
Once the error is printed, an assertion message similar to the following is also printed: 1999-08-09 13:15:26.79 kernel SQL Server Assertion: File: <proc.c>, line=1866 Failed Assertion = 'm_activeSdesList.Head () == NULL'. After a dump of the stack for all threads, the server initiates a shutdown of the SQL Server process. ... *** end ***
You can find the complete article on: http://support.microsoft.com/support/kb/articles/q240/8/53.asp?LN=EN-US&SD=gn&FR=0
We can't use the workaround, because that would shut out parallelism, which is necessary for the project.
There is a fix, but in the article Microsoft says: "A supported fix that corrects this problem is now available from Microsoft, but it has not been fully regression tested and should be applied only to systems experiencing this specific problem.". You understan,d this is not one of my favorite type of fixes...
Does anyone have already installed the fix mentioned? Had any problems with it, or did it cause some other troubles?
Thx, Kurt De Cauwsemaecker Database Administrator Telepolis Antwerpen
DOC_NO // REV_NO // FILE_NAME ABC123 // A // abc123.pdf ABC123 // B // abc123_2.docx ABC124 // A // abc124.xlsx ABC124 // A // - ABC125 // A // abc125.docx ABC125 // C // abc125.jpg ABC125 // C // abc125.docx ABC125 // C // - ABC126 // 0 // - ABC127 // A1 // abc127.xlsx ABC127 // A1 // abc127.pdf
I'm looking to select all rows where the DOC_NO and REV_NO appear only once.(i.e. the combination of the two values together, not any distinct value in a column)
I have written the sub query to filter the correct results;
SELECT DOC_NO, REV_NO FROM [MYTABLE] GROUP BY DOC_NO, REV_NO HAVING COUNT(*) =1
I now need to strip out the records which have no file (represented as "-" in the FILE_NAME field) and select the other fields (same table - for example, lets just say "ADD1", "ADD2" and "ADD3")
I was looking to put together a query like;
SELECT DOC_NO, REV_NO, FILE_NAME, ADD1, ADD2, ADD3 FROM [MYTABLE] WHERE FILE_NAME NOT LIKE '-' AND DOC_NO IN (SELECT DOC_NO, REV_NO FROM [MYTABLE] GROUP BY DOC_NO, REV_NO HAVING COUNT(*) =1)
But of course, DOC_NO alone being in the subquery select is not sufficient, as (ABC125 /A) is a unique combination, but (ABC125 /C) is not, but these results would be pulled in.
I also cannot simply add an additional "AND" clause on its own to make sure the REV_NO value appears in the subquery, because it is highly repetitive and would have to specifically match the DOC_NO)
What is the easiest way of ensuring that I only pull in the records where both the DOC_NO and REV_NO (combination) are unique, or is there a better way of putting this select together altogether?
I have a problem where I have 2 compare 2 records from the same table. This part looks easy but the problem is for a User there can be multiple records and I have 2 compare each record with its previous instance based on the timestamp. Not only I have to compare I have to perform some analysis. Below is the Table script and sample output.
Givens: All SQL Server 2008 or 2012 tools at your disposal.
Production database contains the following tables (simplified for example: constraints ignored, etc.) associated with a racing video game’s server.
-- A player of our game
-- Table greater than 10 million rows
CREATE TABLE [dbo].[User] ( [UserId] [bigint] NOT NULL ,[country] [int] NULL -- User’s home country ,[name] [nvarchar](15) NULL -- User’s displayable name (‘John’, ‘Bill’) ,[subscriptionTier] [int] NULL ) -- 0 == free, 1 == paid, for instance
Assume that rows get written into the event tables at a rate of 1,000 a minute,are never updated once written and currently are only read on a replica/reporting server.
Question Background: Write up a single query that would return the following: List of users and whose “TotalMoneyEarned” value ever grew (between logon events) at a rate of more than 1,000 per minute (we’d consider these suspicious and flag them for later investigation).
For instance, if the sample data were:
-- example of [Events.UserLogon] data -- not the query output we want
Event 1 is okay because there’s nothing to compare it against
Event 2 is okay because the TotalMoneyEarned only grew 500 in a minute
Event 3 should be flagged, as the value grew 1500 in a minute
Event 4 is okay, as it grew 7,000 in 8 minutes (< 1000 per minute)
Query Output (your query should return data in a format like this):
User Flagged Logon Time Rate Since Last Logon (money/minute) John 2010-10-16 00:21:56 1500 Dave 2010-10-16 00:30:50 3200 Bill 2010-10-16 00:35:23 1000
It is likely that you will need to create sample data for both the User and [Events.Logon] tables. We are looking for a single query that returns data like what is represented in Query Output.
I have recently upgraded from SQL 2000 to SQL 2005 and I'm getting the following problem, can you suggest me if this is a issue with SQL 2005 or suggest me an asnwer for this.
Below is the exception from my log file
The cursor type/concurrency combination is not supported. com.microsoft.sqlserver.jdbc.SQLServerException: The cursor type/concurrency combination is not supported. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerStatement.<init>(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.<init>(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(Unknown Source)
The following is the piece of code where the problem I'm assuming is happening, how can I correct it.
I have created a matrix report having "Probability" in Row and "Impact" in Column and "Risk ID" in Data Part .
Now the problem which I am facing is I need to fill matrix with different color value based on combination of Probability and Impact.
I am able to write the expression in "Fill" area of "Risk_ID" text box but the problem is only those text box gets colored which are having Risk-Id value in it like shown below.I want all text box to be colored even if no Risk_Id is there in any text box.
I am trying writing the correct logic so that all text box gets colored even if there is no Risk _Id in it.
