How to identify different fields with in a group of records?
Example:
create table #test
(ID int, Text varchar(10))
insert into #test
select 1, 'ab'
union all
select 1, 'ab'
[Code] ...
I want to show additional field as Matched as ID 1 has same Text field on both the records, and for the ID 2 I want to show Unmatched as the Text fields are different but with the same ID.
I have a problem. In my database I have the following numbers available:
101
104
105
110
111
112
113
114
What I need is to get a select query with records and sequentials numbers after it like:
101 0
104 1 (the number 105)
105 0
110 4 (the numbers 111,112,113,114)
111 3 (the numbers 112,113,114)
112 2 (the numbers 113,114)
113 1 (the numbers 114)
114 0
How can I do It?
Hi!
I'am new to this forum and would apreciate any feedback on my problem.
I have a quarry that returns the count of former customers with average cell-phone usage between 200 and 299.
The ressult is grouped in year and week with group by. The dates are represented by the closingdate of the customers subscription.
The ressult is used for reporting purposses, but I need my selection to return '0' on weeks where there are "no reccords found".
CODE:
SELECT '200-299' AS ARPU, year AS YEAR, week AS WEEK, COUNT(nummer) AS Antall
FROM
(SELECT SERGEL_PREPAID.SP_Mobilenumber AS nummer, DATEPART(yyyy, TRANSLOG.TRL_TIMESTAMP) AS year, DATEPART(ww, TRANSLOG.TRL_TIMESTAMP) AS week,
ROUND(AVG(SERGEL_PREPAID.SP_Sum), 0) AS average
FROM SERGEL_PREPAID INNER JOIN
TRANSLOG ON SERGEL_PREPAID.SP_Mobilenumber = TRANSLOG.TRL_MOBILE
WHERE (TRANSLOG.TRL_STATUS = 'NP_FERD')
GROUP BY SERGEL_PREPAID.SP_Mobilenumber, DATEPART(yyyy, TRANSLOG.TRL_TIMESTAMP), DATEPART(ww, TRANSLOG.TRL_TIMESTAMP)
HAVING (AVG(SERGEL_PREPAID.SP_Sum) BETWEEN 200 AND 299)) AS derivedtbl_1
GROUP BY uke, all aar
NB: Using SQL Server 2005. Any tip or solution will be a big help
Best regards Gard S
I'm trying to insert multiple records, each containing a null value for one of the fields, but not having much luck. This is the code I'm using:
Use InternationalTrade
Go
CREATE TABLE dbo.ACCTING_ADJUST
(
stlmnt_instr_id varchar(20) null,
instr_id varchar(20) not null,
[Code] ....
I have a pretty complex query that returns three records. For simplicity sake, the results can be simulated with this query:
Select 5 AS InternalAuditTeamEmployeeID, 1 as InternalAuditTeamID
UNION ALL
Select 11, 2
UNION ALL
Select 14, 3;
I want to take this result and update the Flag field to true in my table tblInternalAuditTeamEmployee (CREATE statement below) for any InternalAuditTeamEmployeeID that is less than or equal to the ones in the results above, but by group. My results would look something like this using the data below and the results above.
InternalAuditTeamEmployeeIDInternalAuditTeamIDEmployeeIDFlag
1 1 619 1
218581
316041
425181
517161
639661
711910
819400
92391
1012340
1129541
1228910
1329500
143321
1539450
I was thinking I could somehow use ROW_NUMBER(PARTITION BY InternalAuditTeamID ORDER BY InternalAuditTeamEmployeeID DESC), but not sure how to get the results of "WHERE <= InternalAuditTeamEmployeeID For each particular group".
CREATE TABLE STATEMENT:
CREATE TABLE [tblInternalAuditTeamEmployee](
[InternalAuditTeamEmployeeID] [int] IDENTITY(1,1) NOT NULL,
[InternalAuditTeamID] [int] NOT NULL,
[EmployeeID] [int] NOT NULL,
[Code] ......
create a sequential aggregate of a column using sum in tsql.
I want a column that will sum up all hours work by transaction date.
for example
Monday my hours work will be 8 and therefore my total transaction hours will be 8.
Tuesday my hours work will be 8 but I want the total hours of my total transaction hours to 16.
Wed my total hours work will be 5 and I want the total hours of my total transaction hours to 21. etc.
Select myname, weekday, hours worked, Totalhours_ToDate =Sum(hours worked) from table
Let's say I have the following table:entry product quality1 A 802 A 703 A 804 B 605 B 906 C 807 D 808 A 509 C 70I'm looking for a way to find the average "quality" value for aSEQUENTIAL GROUPING of the same Product. For exmple, I need anaverage of Entry 1+2+3 (because this is the first grouping of the sameproduct type), but NOT want that average to include row 8 (which isalso Product A, but in a different "group".)I'm sure it can be done (because I can describe it!), but I'll be amonkey's uncle if I can figure out how. I would imagine it wouldinvolve some sort of running tally that references the next record asit goes... to see if the product type has changed. Perhaps use of atemporary table?Muchas gracias!!Cy.
View 9 Replies View RelatedI am extract data from one of our Sql servers with the code below
Code:
Select SectDesc, PrGDesc, 'Product Description'=SetDescF, ProdSet, ProdSect, SectChar, Prdct.ProdGrp, OrigPr
From Prdct
INNER JOIN qryPRDGroupDets on Prdct.ProdGrp=qryPRDGroupDets.PGCode
where supersed =''
And OrigPr Not Like '9%' And OrigPr Not Like '%MDM%'
And LISTPR1>'0' And STANCOST>'0'
Which works fine, but what I need to do is reference the "OrigPr" field and mark it as "valid" or "Invalid", the "OrigPr" the field contains alpha numeric data e.g. A000, A001, A002 - ZZ99 and so on, amongst all of the potential different types of codes we have codes that end in treble Zero (0) e.g. A000 which are valid, but if they end in double 00 e.g. AA00 then this is invalid, the problem I have is I can't just add
Code:
'Marker'= Case When Right(OrigPr, 2) = '00' Then 'Invalid' ELSE 'Valid' End
For it will mark the A000 as invalid, is there a way of getting around this...
Trying to use LIKE / NOT LIKE to identify values that contain any alphanumeric characters outside of A-Z e.g £%$^&*_-{[@ etc etc
The field should contain only values between A-G with a numberic e.g ABCD1234567... but some rows have characters such as above, some have spaces (weeps) , and some have letters outside the A-G range ....
Basically, I'm trying to upload data from one system to another and the system which is receiving data is throwing error after loading few records. On further investigation I found out that the failing records has bad characters embedded. I was wondering if there is way to find out all the bad characters (special characters) in a table. I'm using MSSQL Server 2005 version. Tried using RegGenExpression but no luck.
View 1 Replies View RelatedI need to group records and assign a setid to the group. I have atable with data that looks like thisColA ColB94015 0106594016 0106594015 0108594015 0108633383 0091232601 00912I need to create a resultset using just sql to look like thisColA ColB GRP94015 01065 194016 01065 194015 01085 194015 01086 133383 00912 232601 00912 2The tricky part is resolving the many to many issue. A value in ColAcan belong to multiple values in ColB and a value in ColB can havemultiple values in ColA.
View 6 Replies View Relatedwriting the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
I need to query a table in Database 1 then, using a primary key, pull the columns from a table in Database 2.
The query I'm using in db_1 works:
SELECT * FROM db_1.table_1 WHERE (hcpcs_cd = '20552' AND srvc_units_num>'1 ' AND claim_dos_yr >'2012')
But what I really need are the columns associated with these records in db_2.table_1. There is a primary key in both tables.
Ultimately what I need is all of the columns in db_2.table_1 and the hcpcs_cd, srvc_untis_num, and claim_dos_yr from db_1.table1
Hi
In our sql server we have around 40 windows group.
Say a Windows user = "X"
This X user does not have a direct windows login, he is present in one or more windows groups registered in the sql server.
I need to know throught which group he is logging in.
If I know this , this will help in my auditing process.
Thanks in advance.
Regards
Soorya
I have written a simple routine to do some address checking between sources. The business owner wanted the check done to the address as a whole and to report the address' that did not return a match. The comparison works perfectly fine.
Well, now the customer has come back wanting to know what element(s) of the address make the match fail
Here's the basic address compare:
SELECT *
FROM Full_Address_Map fam
WHERE fam.StreetName + ' ' + fam.RoadType + ' ' + fam.CityName + ' ' + fam.StateName + ' ' + fam.Country COLLATE Cyrillic_General_CI_AI NOT IN
(
SELECT s.base_name + ' ' + s.road_type + ' ' + p.name + ' ' + v.name + ' ' + v.country_name COLLATE Cyrillic_General_CI_AI
[Code] ....
You can see that we are building the address by sticking the 5 columns together with a space in between to create a single string. We are then doing the same thing with the other source data and simply comparing the string. This worked perfectly and was nice and simple... until the customer decided he wanted to see the element(s) causing the issue.
For example, let's say there is an address of '123 Main ST Dallas Texas United States' in one source, but it's '123 Main Dallas Texas United States' in the other. The customer wants to know that it is the road type (ST) that caused the mismatch.
Now, I wrote a bunch of queries to try and single out each element in order to determine if it is the one causing the issue or not. It seems to work partially, but I don't believe it is the best approach and it doesn't work if more than one column is the culprit.
I am looking to create a script that will go through a table a pick out the necessary columns to create a unique record. Some of the tables that I am working with have 200 plus columns and I am not sure if I would have to list every column name in the script or if they could be dynamically referenced. I am working with a SQL server that has little next to no documentation and everytime I type to mere some tables, I get too many rows back.
View 4 Replies View RelatedIn this selection when there is particular segment found in the selection then need to add 2 records per segment otherwise just selected results
--Drop table #list
CREATE TABLE #LIST ( email varchar(20),Segment varchar(20), Addr1 varchar(20), City varchar(20),ST varchar(20), Code int)
--Drop table #subject
CREATE TABLE #Subject (Segment varchar(20), Fname varchar(20), LName varchar(20))
[Code] ....
---Selection results
SELECT L.Segment , l.email, l.Addr1 , l.City ,l.ST, l.code, s.Fname , s.LName into #expectedresult FROM #LIST L JOIN #Subject S ON l.Segment =s.Segment
--Now here what i am trying to get, union the below two record to the above "selection Results" for only segment ='VEC_BAL' or segment = 'PPC_BAL'
--2 records
SELECT l.segment , l.email , Addr1, City, ST, 999 as Code,'' fname, ''lname from #LIST l where email ='mmm@m.com'
SELECT l.segment , l.email , Addr1, City, ST, 888 as Code, '' fname, ''lname from #LIST l where email ='lrk@m.com'
--- expected results please execute below block ( per each segment 2 records will insert if there is segment ='VEC_BAL' or 'PPC_BAL' ). If there is no Vec_Bal or PPC BAL then no additional inserts, if there is only VEC_BAL no PPC_BAL then only 2 records insert
SELECT L.Segment , l.email, l.Addr1 , l.City ,l.ST, l.code, s.Fname , s.LName into #expctresults1 FROM #LIST L JOIN #Subject S ON l.Segment =s.Segment
union
[Code] .....
In a job of migrating from an old database to a new one (with other structure, other server, other version) i'm copying from the source old tables and inserting into the new destination tables. The problem is that some records have inconsistencies (of any kind) and thus are not inserted due to foreign key, not null, etc validation. When a problem occurs none record is copied! and there is my question: How can i perform the copy in wich it copies the good records (without inconsistencies) and leave aside the bad records. I also want to know wich were not copied and better if in the copy process those were put in a temp table or exported to excel for further analisys o its data.
I'm using this model of "migration":
BEGIN TRY
INSERT INTO DESTINTATION_TABLE (
col_d1,
col_d2,
col-d3,
...)
SELECT
col_s1,
dbo.some_function(col_s2),
col_s3 * 100,
...
FROM SOURCE_TABLE join <other_table> ... where <some filters>
END TRY
BEGIN CATCH
print ERROR_MESSAGE()
END CATCH
(for now, with try/catch I've only get to know the error occurred, if some)
Ok, I'm looking to get counts on historical data where the number of records exists between two dates with different years. The trick is the that the dates fall in different years. Ex: Give me the number of records that are dated between 0ct 1, 2013 and July 1, 2014.
A previous post of mine was similar where I needed to get records after a specific date. The solution provided for that one was the following. This let me get any records that occured after May 1 per given Fiscal year.
SELECT
MAX(CASE WHEN DateFY = 2010 THEN Yr_Count ELSE 0 END) AS [FY10],
MAX(CASE WHEN DateFY = 2010 THEN May_Count ELSE 0 END) AS [May+10],
MAX(CASE WHEN DateFY = 2011 THEN Yr_Count ELSE 0 END) AS [FY11],
MAX(CASE WHEN DateFY = 2011 THEN May_Count ELSE 0 END) AS [May+11],
MAX(CASE WHEN DateFY = 2012 THEN Yr_Count ELSE 0 END) AS [FY12],
[Code] ....
I basically need to have CASE WHEN MONTH(OccuranceDate) between Oct 1 (beginning year) and July 1 (ending year).
Hi All,
I have a table called Table1 with a field called DateString with a nvarchar type. The records are like this;
DateString
1/11/2007
9/27/2006
2/22/2008
1/10/2007
11/13/2007
8/21/2007
5/23/2007
2/16/2007
12345678900
11/20/2006
It may not have date records which are just string like in this example is 12345678900. I need a SQL script which can identify those records which are not of date type. Please help.
Thanks,
Zee
Thanks a million..
Zee
I'm using MS SQL Server 2008 and I'm trying to figure out if it is possible to identify what tables / columns contain specific records.
In the example below information generated for the end user, so the column headers (Customer ID, Customer, Address, Phone, Email, Account Balance, Currency) are not necessarily the field names from the relevant tables, they are simply more identifiable headers for the user.
Customer ID CustomerAddress Phone Email Account Balance Currency
js0001 John Smith123 Nowhere Street555-123-456 jsmith@nowhere.com-100 USD
jd2345 Jane Doe 61a Down the road087-963258 jdoe@downthe road.com-2108 GBP
mx9999 Mr X Whoknowsville 147-852369 mrx@whoknows.com0 EUR
In reality the column headers may be called eg (CustID, CustName, CustAdr, CustPh, CustMail, CustACBal, Currency).
As I am not the generator of this report, I would like to know whether or not it is possible to identify the field names and / or what tables they exist in, if I were to used the report info to search for it. For example, could I perhaps find out the field name and table for "jd2345" or for "mrx@whoknows.com", because the Customer ID or Email may not be what the actual fields are called.
I'm not a DB admin and I don't have rights to do a stored procedure on the server. I'm guessing what I want is not so simple to do, but is it possible to do via a query?
hi im creating 2 random numbers, matching one of them (i) to the QuestionsNo column in my database, and then making it display the whole record. the code below generate 2 random numbers but i cannot get the rest of it to work. i need it to match the random number to the QuestionNo field in my database and display the whole record. can anyone help me and tell me where im going wrong please????
Dim i,j as integer
Randomize
i = CInt(Int((10 * Rnd()) + 1))
j = CInt(Int((10 * Rnd()) + 1))
example.InnerHtml = "Random Number : " & i
example1.InnerHtml = "Random Number : " & j
strSQL = "SELECT * FROM Table1 WHERE QuestionNo = " & i &""
Message.text=strSQL.ToString()
SELECThorse1.horse_name AS Child_Horse,
horse2.horse_name AS Sire_Horse,
event.event_name
FROMhorse AS horse1 INNER JOIN horse AS horse2 ON horse1.sire = horse2.horse_id,
entry,
event
WHEREhorse1.horse_id = entry.horse_id
ANDentry.event_id = event.event_id
[Code] ....
I need to list the names of the horses that has entered in the same event as its sire and above is the result that I've got.I'm meant to get only the first two lines of the result since Boxer is father of Flash and Star and they all entered the same event called Dressage. (below is the evidence and refer to the first three rows)
SELECTentry.event_id,
entry.horse_id,
horse.horse_name
FROMentry,
horse
WHEREhorse.horse_id = entry.horse_id
[Code] ....
with my code in the first box, what should I add in order to get the result that I want?
I have a table with about half a million records, each representing a patient in my county.
Each record has a field (RRank) which basically sorts the patients as to how "unwell" they are according to a previously-applied algorithm. The most unwell patient has an RRank of 1, the next-most unwell has RRank=2 etc.
I have just deleted several hundred records (which relate to patients now deceased) from the table, thereby leaving gaps in the RRank sequence. I want to renumber the remaining recs to get rid of the gaps.
I can see what I want to accomplish by using ROW_NUMBER, thus:
SELECT ROW_NUMBER() Over (ORDER BY RRank) as RecNumber, RRank
FROM RPL
ORDER BY RRank
I see the numbers in the RecNumber column falling behind the RRank as I scan down the results
My question is: How to convert this into an UPDATE statement? I had hoped that I could do something like:
UPDATE RISC_PatientList_TEMP
SET RRank = ROW_NUMBER() Over (ORDER BY RRank);
but the system informs that window functions will only work on SELECT (which UPDATE isn't) or ORDER BY (which I can't legally add).
I couldn't find a topic suitable for testing this, so I thought I'd start one.
Here is one way to get the islands without a tally table.declare@test table (symbol char(3), dt smalldatetime)
insert@test
select'abc','01/01/1990' union all
select'abc','01/02/1990' union all
select'abc','01/03/1990' union all
select'abc','01/04/1990' union all
select'abc','01/05/1990' union all
select'def','01/03/1990' union all
select'def','01/04/1990' union all
select'def','01/05/1990' union all
select'def','01/06/1990' union all
select'def','01/07/1990' union all
select'ghi','01/01/1990' union all
select'ghi','01/02/1990' union all
select'ghi','01/06/1990' union all
select'ghi','01/07/1990' union all
select'ghi','01/08/1990'
selectsymbol,
min(dt),
max(dt2)
from(
selectt1.symbol,
t1.dt,
t2.dt as dt2,
(select count(distinct t3.symbol) from @test as t3 where t3.symbol < t1.symbol and t3.dt <= t1.dt) AS r
from@test as t1
inner join@test as t2 on t2.symbol = t1.symbol
wheret2.dt - 1 = t1.dt
) as d
group bysymbol,
r
E 12°55'05.25"
N 56°04'39.16"
Hello everyone,Small and (I think) very simple quesiton;-) which makes me creazy.Let's say I have two tables listed below:T1====IDX====134T2===============IDD fk_IDX===============A1A2A4B1B3B4C4D1D2D3D4I would like to select from table T2 all distinct records IDD whichhave all of fk_IDX containded in T1.The select statement should return in this case ONLY:B and Dbecasue:B has 1,3,4andD has 1,2,3,4 so it has this combination 1,3,4 contained in the T1also.I've tried to do that with group by, with having, in and it neverworks (I always became all records which one of them is in this T1table).Maybe some one from you did try something like that, and can give afast answer.I will be very greatfullGreatingsMateusz
View 2 Replies View RelatedI have a table which is already populated with data (Microsoft SQL 2008). I have now created a new column (int) which i want to populate with sequential numbers so that the new column created will serve let me know how many records exist in the table at a glance.
what SQL statement I need to write that will automatically polulate the newly created column with 1,2,,3,4,5 etc so that I can sort of number the records within the table.
I have 50000 records which I need to number and I really dont want to number the column manually via hand editing.
Lets say I have a list of customers, order date, and an item they ordered. I want to grab the MIN order date by customer, and the associated product. If I do:
Code:
SELECT customer,min(order date),item
FROM mytable
GROUP BY customer
I get an error because item is not aggregated. But I don't want to group on it and i don't want to select a min/max of it. i just want it to carry over the item that is associated with that min order date.
How can I use a Distinct or Group by statement on 1 field when calling All or at least several ones.
Example:
SELECT id_product, description_fr, DiffMAtrice, id_mark, id_type, NbDiffMatrice, nom_fr, nouveaute
From C_Product_Tempo
And I want Distinct or Group By nom_fr
I have two issues I'm trying to deal with in my code.
1. I'm trying to group by first and last name, which are in two different columns
2. I'm trying to take an average of pay per miles.
Neither of these is working well. Actually, neither is working at all.
This is the code!
SELECT
OD.DriverID,
(W.FirstName + W.LastName AS 'Driver'),
DATEADD(dd,(DATEDIFF(dd,0,O.ReadyTimeFrom)),0) AS Date,
DATENAME(dw,O.ReadyTimeFrom) AS DayOfTheWeek,
Count(OD.OrderID) AS 'Total Orders',
SUM(O.Distance) AS OrderMiles,
[Code] ....
Dear experts,
Hi, please do give me your expert advise and opinon on this matter:
I have a table name PerformaceRecords with a few columns, one of which is performance banding.
i.e.
PerformanceBanding
------------
Outstanding
Good
Average
Good
Poor
When I use a group by clause, i.e. Select PerformanceBanding, Count(PerformanceBanding) as ResultCount from PerformanceRecords group by PerformanceBanding
I got the result as
PerformanceBanding ResultCount
---------------------------------
Good 2
Poor 1
Average 1
Outstanding 1
What I want to get is the PerformanceBanding as columns and the Result as rows
i.e.
Good Poor Average Outstanding
----------------------------------
2 1 1 1
how do I go about modifying my SQL select statement to achieve this result?
Thank you in advance for assisting me.
I have a text field that I need to parse out into 4 fields.
30.125x23.625-18.875x25.375
6.1875X19.375-2.4375x15.625
0X0-0x0
26.875X11.375-11.125x22.875
0X0-0x0
0X0-0x0
6.1875X26.875-2.4375x23.125
6.1875X26.875-2.4375x23.125
6.1875X26.875-2.4375x23.125
26.625X14.875-11.125x22.875
26.625X14.875-11.125x22.875
I need to 26.625X14.875-11.125x22.875 should be 26.625 then 14.875 then 11.125 then 22.875
I'm archiving some data. In a 2 step process.
1. Copy old data from each table in LiveDB to same table in ArchiveDB.
2. Delete the data from each table in LiveDB which is in ArchiveDB
Both DBs SIMPLE recovery mode.
Each table has a clustered PK on a single int value. In both DBs
The tables with varchar(max) columns are taking a v.long time to copy over.
IS there anything I can change in the ArchiveDB to make it run faster.
It is the insert that is taking the time. I've tried dropping the clustered PKs in ArchiveDB tables and then rebuilding afterwards but it has not made any difference. After all I am adding data to the ArchiveDB in clustered index order, so wouldn't have expected it to.
How I can change the Archive DB but cannot touch the schema/settings of Live DB.