Hello, I have the following table with 4 columns....
firstname, lastname1, lastname2, EMAIL
Table has user names and email, I would like to generate a 5th column
called DisplayName.
The email Id is sometimes firstname.lastname1.lastname2@ and others
just firstname.lastname1@
I would like to generate the display name exactly like the email eg
firstname.lastname1.lastname2@ displayName = firstname lastname1
lastname2......so for james.smith display name = James Smith and for
james.earl.smith displayName = James Earl Smith etc etc
Is there a way that I can check/compare email Id (before the @ part)
with firstname, lastname1 and lastname2 and generate a display name
based on what was used for the email address?
I have a table that have different groups in it, All the records that belong to one Group have the same GroupID and each GroupID has same columns. I need a query to generate the output in the attachment.
When the user input one GroupID then all the records belong to that group will pull out,
When the user input two GroupIDs then the user need to select another set of parameter (duplicate records or different records or nonoverlap records) to display
Intersect of the two group or different records or except records
when user input three GroupIDs then the user need to display the overlaps among all three groups or different among three groups or dinstinct records among the three groups
Hi, I'm wanting to generate a list of all the days that fall between a certain period of time (month, year etc.), but can't find any documentation on how to do this, does anybody know the sql query to do so? TIA!
1 st column 'A1Ctest' 2 nd column 'diagnoising heart disease' and my 3 rd column is combination of both columns 'A1Ctest for diagnoising heart disease'.Here i need to comapre 'A1Ctest' from 1st column and 'diagnoising heart disease' from 2 nd column
I am trying to compare values from two columns but I don't know what I am doing, I tried select custom5,skipcount substr( custom5, 1, 4) as 'mynumber' from songs where custom5 > '0000%' where skipcount > cast( substr( custom5, 1, 4) as int ); But that is obviously wrong?
Hi,I have three columns and want to update the third column based on the comparison of first two columns in the same table.For instance:Col1 - val1, val2, val3, val4, val5......Col2 - NULL, NULL, val1, NULL, val2....Col3 - TRUE, TRUE, FALSE, FALSE, FALSE....Thanks for any help.
I have one database with several tables in it (table 1, table2, table3). In each table is two colums (colum1 = a number (201220) and colum2 = a number (0.50). Now, both tables will have rows with the same data in colum 1, but colum two will have different numbers (different prices). My goal is to run a query that will compare both colums in all three tables, take the lower of the three based on colum 2 and spit out the row. Obviously, this would output all rows (around 175k). The point is to create a least cost spreadsheet (csv) file based on evaluating all three tables.
I am fairly new to SQL and writing queries so bear with my faults. I am learning on the job, which is good and bad. Below is a query that I have written to obtain some information. The problem arises when we have a patient who goes from Patient Type '1' to Patient Type '2'. This needs to be considered a singular visit and the only way I can think that this may work is if: for any specific medical record a dsch_ts is equal to the Admit TS on the next row.
How to complete something like this and my google searches have been fruitless. I attached a spreadsheet with an example of what I am getting.
SELECT DISTINCT TPM300_PAT_VISIT.med_rec_no, TSM040_PERSON_HDR.lst_nm AS 'Last Name', TSM040_PERSON_HDR.fst_nm AS 'First Name',
I have two table with the same name that reside in different databases. The two database have the same name, but reside in two linked servers TABLE A: [ServerA].[ProdDB].dbo.[Orders] TABLE B: [ServerB].[ProdDB].dbo.[Orders]
How do i find out if the two tables have the same number of columns or if the a column that exists on TableA does not exist on TableB.
Does any one have a script that could help me with this task. Thanks
What I want to achieve is to get values from period1 till period04 and used the lasted value to code the value of accoutperiod, if value is from period1 then code it as 01, period2 as 02, period03 as 03 and period04 as 04. So the output should be like this
Hi, Can we able to auto generate columns in sql server 2000? I have a tabe with two fields No and Name. I need to auto generate no for each name i am going to enter. Can any give me a solution for this? If so i will be thankful to u.
I have a problem. I have two feilds in a table. Both of them are (int). I want to be able make sure the same number is not put in both columns for each row. Both colums have to be a different number, and also if one is null, the other cannot be. How do I go about enforcing this policy?
I am using a Data Flow task which copies data from an Excel Source to a SQL Database Table Destination. From 15 columns I require only 10 columns to be imported to the DB Table. So I have mapped those colums. In SQL DB there is a colum called say X, whose value should be the "Remedy" for all the columns which are imported. Is there any task that can achieve it.
I have 5 different tables with same structure. Each table has an account column and another column count. I am trying to generate a report based of the columns in each table. Account is same in all tables with same account numbers but count will be different.
Is there a efficient way to compare two different columns of 2 different rows in a data set as shown below.
For eg: I would like to DateDiff between Date2 of RowID 1 and Date1 of RowID 2 of IDNo 123. After this comparision , if datediff between two dates are <=14 then i want to update 1 else 0 in IsDateDiffLess14 of RowID1 . In below example its 0 because datediff of two dates >=14. So, want to compare the Date2 and Date1 in this sequence for the same IDNo. For RowID 6 there is only 1 row and no other row to compare, in this case IsDateDiffLess14 should be updated with 0.
What I need to be able to find is any records where the Discontinue_Date is greater than the Effective_Date on the next row for a given Customer ID and Part_ID. This is a customer pricing table so the Discontinue_Date of row 53 for example should never be greater than the Effective_Date of row 54130, these are the records I'm looking to find. So I'm looking for a SELECT query that would look for any records where this is true. Obviously the last Discontinue_Date row for a Customer_ID will not have a next row so I wouldn't want to return that.
create table #myfirst (id int, city varchar(20)) insert into #myfirst values (500,'Newyork') insert into #myfirst values (100,'Ediosn') insert into #myfirst values (200,'Atlanta') insert into #myfirst values (300,'Greenwoods') insert into #myfirst values (400,'Hitchcok') insert into #myfirst values (700,'Walmart') insert into #myfirst values (800,'Madida')
-- My Second Data
create table #mySecond (id int, city varchar(20),Sector varchar(2)) insert into #mySecond values (1500,'Newyork','MK') insert into #mySecond values (5500,'Ediosn','HH') insert into #mySecond values (5060,'The Atlanta','JK') insert into #mySecond values (7500,'The Greenwoods','DF') insert into #mySecond values (9500,'Metro','KK') insert into #mySecond values (3300,'Kilapr','MK') insert into #mySecond values (9500,'Metro','NH')
--Third Second Data
create table #myThird (id int, city varchar(20),Sector varchar(2)) insert into #myThird values (33,'Walmart','PP') insert into #myThird values (20,'Ediosn','DD') select f.*,s.Sector from #myfirst f join #mySecond s on f.city = s.city /* idcitySector 500NewyorkMK 100EdiosnHH */
i have doubt on two things
1) How Can i compare the City names, by eliminating 'The ' at the beginning (if there is any in second tale city) between first and second
2) after comparing first and second if there is no match found in second them want to compare with third table values for those not found
--i tried below to solve first doubt, it is working but want to know any other wasys to do it
select f.*,s.Sector from #myfirst f join #mySecond s on replace (f.city, 'THE ','')= replace (s.city, 'THE ','')
--Expected results wull be
create table #ExpectResults (id int, city varchar(20),Sector varchar(2)) insert into #ExpectResults values (200,'Atlanta','JK') insert into #ExpectResults values (100,'Ediosn','HH') insert into #ExpectResults values (300,'Greenwoods','DF') insert into #ExpectResults values (500,'Newyork','MK') insert into #ExpectResults values (700, 'Walmart','PP') insert into #ExpectResults values (800, 'Madidar','')
My first ASP.NET/SQL project. I'm creating an Asset Management DB. I wish to view the Asset number, the full name of the user it's assigned to and the Make and Model of each record in the DB. However assets are split into different categories e.g. monitors, PCs, Preinters etc. and they are all in different tables. The SQL below displays the asset number, Name of person assigned and the model of the asset. SELECT Hardware.AssetNo, [User].FullName, MonitorModel.Model, PCModel.Model AS Expr1, PrinterModel.Model AS Expr2 FROM Hardware INNER JOIN [User] ON Hardware.UserID = [User].UserID INNER JOIN Model ON Hardware.ModelID = Model.ModelID LEFT OUTER JOIN MonitorModel ON Model.MonitorModelID = MonitorModel.MonitorModelID LEFT OUTER JOIN PCModel ON Model.PCModelID = PCModel.PCModelID LEFT OUTER JOIN PrinterModel ON Model.PrinterModelID = PrinterModel.PrinterModelID This outputs:- Asset number FullName Model Expr1 Expr2 00000 User Name Model NULL NULL 00001 User Name NULL Model NULL 00002 User Name NULL NULL Model However what i hope to acheive is output Model, Expr1, Expr2 into one column like so:- Asset number FullName Model 00000 User Name Model Can i do this in the SQL or do i have to do it in my ASP.NET (VB) Page? Using VSWD 2005 Ex Edition and SQL Server 2005 Ex Edition Thank you for your replies
Does somebody know how do you get a column in Reporting Services to display the values from two different columns? I don't mean a concat, but the result of the two columns.
I have a table. I have some sensitive data that I don't want to display that sensitive data. The columns I want to abstract are
Firstname varchar(50) not null Latname varchar(50) not null dob datetime not null addressline1 varchar(50) null adressline2 varchar(50) null city varchar(50) null postalcode varchar(20) null
I want to display null values as nulls empty as empty not null as numbers.
Hi all, i have a table which has date and count as column Can you please...help me write a query for it.
date | type | count 10/07 | A | 70 11/07 | A | 50 12/07 | A | 50 11/07 | B | 60 12/07 | B | 50 13/07 | B | 40 11/07 | C | 50 12/07 | C | 60 13/07 | C |60 14/07 | A | 50 i need to write a sp such that..if user sends date and @ndays(no. of days) as parameter say 11/07 and 3
then
out put
type 11/07 12/07 13/07 A 50 50 B 60 50 40 C 50 60 60
Trying to get the PSI Outcome, Expected, and PSIIndex every month whether it has data or not. Created a CTE and left outer joined with PSI table, but it's still not pulling every month for every PSIKey.
I already read a lot about the inscope-function and how it is used to display variances over time periods. But I don't know where to start, as there is no tutorial how to setup this functionality. What I want to display within the report is the following:
Code Snippet
Months ProductGroup Article Year 1 2 3 4 5 .... Bicycles 1020 2007 1500 2000 etc. 2008 3000 3000 Var. abs. 1500 1000 Var. % 100% 50% 1025 2007 0 1000 2008 500 1200 Var. abs. 500 200 Var. % 500% 20% Motorcycles etc.
I have built an Advanced Search page which allows users to select which columns to return (via checkbox) and to enter search criteria for any of the selected columns (into textboxes). I build the SQL statement from the properties of the controls. Works great. My problem is getting my gridview control to play nicely. At first I used a SqlDataReader and bound the gridview to it, thus giving me the ability to run new SQL statements through it (with different columns each time). Worked nicely. But, per Microsoft, sorting can only be done if the gridview is bound to a datasource control like the SqlDataSource. So I wrote the code to handle sorting. No big deal; worked nicely. But I could not adjust the column widths programmatically unless bound to a datasource control like the SqlDataSource. And could not figure out a work around. So, I decided to use the SqlDataSource. Works great. Except, I cannot figure out how to run a new SELECT statement through the SQLDataSource and have the gridview respond accordingly. If I try to return anything other than the exact same columns defined declaratively in the html, it pukes. But I need to be able to return a new selection of columns each time. For example, first time through the user selects columns 1,2,3,4 – the gridview should show those 4 columns. The second time the user selects columns 2,5,7 – the gridview should those 3 columns (and ONLY those 3 columns). Plus support selection and sorting. I am desperate on this. I've burned 2.5 days researching and testing. Does anyone have any suggestions? Thanks, Brad
