SQL 2012 :: Percentage Of Rows That Meet Multiple Criteria?
Jun 2, 2015
I am working on a project that was assigned to me that has to do with data in one of our SQL databases. I have the following query that takes information from a single table and averages test scores for each student.
--Group all scores from same student and average them together
with cte_names as
(
SELECT StudentID, MAX(StudentName) AS StudentName
FROM LDCScores
WHERE schoolYear='2014-2015' AND term = 3
GROUP BY StudentID
[code].....
I now need to take the results from the above query and determine the percentage of students, per school that scored a 2 or greater in grade 7 for each test. For grade 8 scored a 2.5 or greater, grade 9 scored a 3 or greater, grade 10 scored a 3 or greater, grade 11 scored a 3.5 or greater, and grade 12 scored a 3.5 or greater.
I need to show any values where the variance is negative for 3 or more consequtive jobs. So the rows in bold above would be returned.
I've found something here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1022586&SiteID=1 But it's not exactly the same as that returns rows where the dates are consequtive and 2 rows have negative numbers.
Anyone got any suggestions on how to achieve this please?
Is there a quick way to select all the tables in the DB that don't have certain column in the table, so for example getting a list of all tables that don't have columns: A, B or C?
Another combining multiple rows teaser, during a few routines I made a mistake and I would like to combine my efforts. Here is my data:
Code:
Table A
ID DSN VN AX Diag 1111296.54 3212318.00
Both DSNs share the same Patient_id in a seperate table which holds the DSN numbers and their corresponding patients.
Code:
Table B
DSN Patient_id 100000001 200000001
So what I need to do is maintain their unique 'ID' number in Table A but update their DSN numbers to reflect the first instance in Table B. So my data would look like this in both tables.
Code:
Table A
ID DSN VN AX Diag 1111296.54 3112318.00
Note: The second rows DSN changed to 1 from 2
Code:
Table B
DSN Patient_id 100000001 (Duplicate row removed with same patient_id)
The result would look like the above but as you noticed I need to remove the duplicate row that had the different DSN in Table B so that only one DSN remains that can map to multiple rows (IDs) in Table A.
Table A:
DSN can map to multiple rows (IDs) IDs must be unique (aka kept to what they are currently)
Table B:
Second row with same DSN must be removed.
Any takes, ideas? I need to do this on a couple thousand rows....
I have a dataset where i want to select the records that matches my input values. But i only want to try macthing a field in my dataset aginst the input value, if the dataset value is not NULL.
I always submit all 4 input values.
@Tyreid, @CarId,@RegionId,@CarAgeGroup
So for the first record in the dataset i get a succesfull output if my input values matches RegionId and CarAgeGroup.
I cant figure out how to create the SQl script for this SELECT?
I need to calculate the last two columns (noofgrp and grpsize) No of Groups (count of Clientid) and Group Size (number of clients in each group) according to begtim and endtime. So I tried the following in the first Temp table
GrpSize= count(clientid) over (partition by begtime,endtime) else 0 end and in the second Temp Table, I have select ,GrpSize=sum(grpsize) ,NoofGrp=count(distinct grpsize) From Temp1
The issue is for the date of 5/26, the begtime and endtime are not consistent. in Grp1 (group 1) all clients starts the session at 1030 and ends at 1200 (90 minutes session) except one who starts at 11 and end at 1200 (row 8). For this client since his/her endtime is the same as others, I want that client to be in the first group(Grp1). Reverse is true for the second group (Grp2). All clients begtime is 12:30 and endtime is 1400 but clientid=2 (row 9) who begtime =1230 but endtime = 1300. However, since this client begtime is the same as the rest, I wan that client to be in the second group (grp2) My partition over creates 4 groups rather than two.
Hi below sample data incoming from a source that cannot be changed. Please ignore the mishandling of zls. Obviously it is not insurmountable - I am just interested in why it is happening because I cannot explain it. DECLARE @t TABLE(the_data CHAR(73)) SET DATEFORMAT dmySET NOCOUNT ON INSERT INTO @tSELECT ' 11'+SPACE(5)+'1649KN889001 2'+space(10)+'0'+space(10)+'08 01 2002'+space(10)+'04 10 2002'UNION ALLSELECT ' 11'+SPACE(5)+'1649KN889001 2'+space(10)+'109 08 2004'+space(20)+'21 07 2005'UNION ALLSELECT ' 11 13026721XX198734 1'+space(10)+'0'+space(10)+'XXXXXXXXXX'+space(10)+ '09 01 2003' SELECT CAST(REPLACE(REPLACE(date1_text,' ','/'),'XXXXXXXXXX',NULL) AS SMALLDATETIME) AS date_1_prob,CAST(REPLACE(REPLACE(date1_text,' ','/'),'XXXXXXXXXX','') AS SMALLDATETIME) AS date_1_ok_ish,CAST(NULLIF(REPLACE(date1_text,' ','/'),'XXXXXXXXXX') AS SMALLDATETIME)AS date_1_fine, date1_textFROM--derived table - selecting relevant substring(SELECT LTRIM(RTRIM(SUBSTRING(the_data, 44, 10))) AS date1_textFROM @t)AS der_t date_1_prob date_1_ok_ish date_1_fine date1_text----------------------- ----------------------- ----------------------- ----------NULL 2002-01-08 00:00:00 2002-01-08 00:00:00 08 01 2002NULL 1900-01-01 00:00:00 1900-01-01 00:00:00 NULL 1900-01-01 00:00:00 NULL XXXXXXXXXX Can anyone explain the result in the first row first column? Thanks
I concatenate multiple rows from one table in multiple columns like this:
--Create Table CREATE TABLE [Person].[Person_1]( [BusinessEntityID] [int] NOT NULL, [PersonType] [nchar](2) NOT NULL, [FirstName] [varchar](100) NOT NULL, CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED
[Code] ....
This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?
I only want to UniqIds that only have the CODE of ABC... and if it contains ANYTHING other than ABC then It doesnt return that UniqID... Now keep in mind there's multiple different codes.. I'm just looking for a bit of code that drops any ID's that don't have my criteria.
SSMS 2012: when you open up many sql files in the IDE, it starts hiding some tabs and you have to click on the drop down at the right to navigate to the tab you want. Is there a way to make it display more than one row of tabs, so that tabs are not hidden and always displayed?
Basically if the type code is 1 one then move the data to column phone1, if the type is 2 then move it to column phone2.
This would be fairly simple if we always have type codes 1 and 2. But sometimes we can have type 1 and not type 2, or we could have type 2 and not type1.
Right now we only have 2 type codes. But, in the future we could be adding a 3rd type. So that would add a 3rd column (phone3).
Below is my code that I have written. I move the data into a temp table then list it. I am thinking of making this a view to my table. It works just fine. My question is, is there a better and more efficient way of doing this?
CREATE TABLE #Contacts ( id INT PRIMARY KEY, phone1 VARCHAR(15), phone2 VARCHAR(15) )
-- Insert the records for type 1
INSERT INTO #Contacts SELECT id, phone_num, NULL FROM test1 WHERE type_code = '1'
-- Insert the records for type 2, if the id does not exist for type 1
INSERT INTO #Contacts SELECT id, NULL, phone_num FROM test1 WHERE NOT EXISTS ( SELECT 1 FROM #Contacts WHERE #Contacts.id = test1.id ) AND test1.type_code = '2'
-- if the id has both type 1 and 2, update the phone2 column with the data from type 2
UPDATE #Contacts SET phone2 = test1.phone_num FROM #contacts JOIN test1 ON test1.id = #Contacts.id WHERE type_code = '2' SELECT id, phone1, phone2 FROM #Contacts DROP TABLE #Contacts
How I could accomplish taking several rows for one account and concatenate them into one row, for example I have account_num, invoice_date, transaction_num, msg_counter,Message_2,SQL_LAST_UPDATE the special characters &,",!,$,# are used to determine the Message_2 content for a given account_number that are supposed to be together.
I am needing to put all of that accounts_messages in one row to display on a report, the table I am pulling this data from only has a varchar(40) for the message_2, a proprietary source so can't change that length, "I'VE ASKED THEM TO DO THIS, AND THEY REFUSED". So my only option is to insert this data into my table and create a single Message_2 for that account.
00000000332015-01-16 10:09:43.00000&19 confirmation so 2015-01-19 15:34:59.000 00000000332015-01-16 10:09:43.00000"19ACCT 186743. HE SAID RADIO HAD 2015-01-19 15:34:59.000 00000000332015-01-16 10:09:43.00000!19CALLED Carl ABOUT DEACTIVATION OF RADIO 2015-01-19 15:34:59.000 00000000332015-01-16 10:09:43.00000$19FFERENT ACCT # YEARS AGO, BUT 2015-01-19 15:34:59.000 00000000332015-01-16 10:09:43.00000'19I can cancel the (0.00) billing line on 2015-01-19 15:34:59.000
Create table #table (id int identity , from_country varchar(20) , to_country varchar(20),noofdays int, datetravel datetime ) insert into #table(from_country,to_country,noofdays,datetravel) values ('Malaysia','India',2,getdate()-99), ('India','Singapore',4,getdate()-88), ('Singapore','China',5,getdate()-77), ('China','Japan',6,getdate()-66), ('Japan','USA',7,getdate()-55) select * from #table
I want to insert data to another table based on "noofdays" columns. If "noofdays" is 4 then 4 rows should inserted to new table with 1 day increment in "datetravel" column.
Ex : #table 1MalaysiaIndia22015-02-09 02:04:09.247 2IndiaSingapore42015-02-20 02:04:09.247
[code]...
In #table , 1st row noofdays is 2 , so in new table #table_new first 2 rows should inserted with 1 day increment in "datetravel" column.
I have created a single FULLTEXT on col2 & col3. suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1 ON
TBL.col1=TBL1.[key] INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2 ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information. for e.g.; i was thinking to concatinate both fields like col4= ABengineBA + ABBToyotaBBA and in search i use SELECT
TBL.col4 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1 ON
TBL.col1=TBL1.[key] Result = 1 row
But it don't work in following scenario col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1 ON
TBL.col1=TBL1.[key]
Result=0 Row Any idea how i can write second query to get result?
I am needing to combine the Notes field where Number and date are the same...For example
for Number 0000000003 I need notes to Read ('CHK # 2452 FOR $122.49 REJECTED AS NSF ON 2/25/15') the note counter is different for each row, and is combination of special char, 0-Z and looks like the (!) depicts the start of a new Number.
1 ,AU-Australia 1,MM-Myanmar 1,NZ-New Zealand 1,PG-Paua New Guinea 1,PH-Phlippines
Note: we are getting source data from sqlserver tables.
I googled and found below way but did't get the output as required
SELECT A.id, a.country, Split.a.value('.', 'VARCHAR(500)') AS String FROM (SELECT id, country , CAST ('<M>' + REPLACE(country, ' ', '</M><M>') + '</M>' AS XML) AS String FROM #t3) AS A CROSS APPLY String.nodes ('/M') AS Split(a);
I am getting error when I passed multiple rows in less than condition:
create table #t1 ( ID int) INSERT INTO #t1
SELECT 1 UNION ALL SELECT 5 UNION ALL SELECT 8 CREATE TABLE #t2 (ID int) INSERT INTO #t2 SELECT 3 UNION ALL SELECT 20 UNION ALL SELECT 4
SELECT ID FROM #t2 WHERE ID < (SELECT ID FROM #t1)
Error is: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Now I want to create View that will have an exploded resultset based on SrvType.
For SrvType 1 and 2 there will be 2 lines per Itemid - One for 'Amount' anod another for 'Tax1+Tax2'. But for SrvType 3 there will be 3 lines per 'ItemId' - one for 'Amount', one for Tax1 and another for 'Tax2'.
I have a few hundred source records like this. Now sure how to achieve the exploded resultset with a View.
I create a Trigger that allows to create news row on other table.
ALTER TRIGGER [dbo].[TI_Creation_Contact_dansSLX] ON [dbo].[_IMPORT_FILES_CONTACTS] AFTER INSERT AS
[code]...
But if I create an INSERT with 50 rows.. My table CONTACT and ADDRESS possess just one line.I try to create a Cursor.. but I had 50 lines with an AdressID and a ContactID differently, but an Account and an AccountId egual on my CONTACT table :
I have resulting rows from a query similar to the following:
The data is coming from a single table that contains only one coverage code column and one coverage code date, but the end user wants the two coverage code types and dates combined into a single row. So the SELECT looks something like this:
SELECT [Employee ID] = emp.employee_id, [Coverage Code 1] = enr.coverage_code, [Coverage Date 1] = enr.coverage_date, [Coverage Code 2] = case when enr.product_type = 'Accident.Accident' then enr.coverage_code else NULL end,
[Code] ....
I basically want to merge the like Employee ID's together into a single row like the following:
I know I have done this before and it is probably pretty simple.
The following works in query if I specify one student (PlanDetailUID) when running query. If I try to specify multiple students (PlanDetailUID) when running query, I get variable cannot take multiple entries. I assume I would need to replace (variables) in PART 2 with (case statements / using select everywhere) to get around the issue or is there a better way ?
I have created a trigger that is set off every time a new item has been added to TableA.The trigger then inserts 4 rows into TableB that contains two columns (item, task type).
Each row will have the same item, but with a different task type.ie.
I've 2 tables QuestionAnswers and ConditionalQuestions and fetching data from them using CTE join and I'm seeing repetitive rows (not duplicate) like, If you have multiple answers for 1 question, the output is like
where london where paris where toronto
why us why japan why indonesia
I want to eliminate the repetitive question and group them as parent child items.
with cte as ( select cq.ConditionalQuestionID from ConditionalQuestions cq inner join QuestionAnswers qa on cq.QuestionID=qa.QuestionID where cq.QuestionID=5 and qa.IsConditional='Y') select distinct q.Question, a.Answer from QuestionAnswers qa inner join Answers a on a.AnswerID = qa.AnswerID inner join Questions q on q.QuestionID = qa.QuestionID inner join cte c on c.ConditionalQuestionID = qa.QuestionID;
The objective is to identify orders where an order fee has been applied incorrectly. I have multiple orders per customer, my table contains an orderID and a customerID. Currently if the customer places additional orders before the previous orders have been closed/cancelled, then additional fees are being applied.
Let's say I'm comparing order #1 to order #2. I need to identify these rows where the following is true:-
The CustID is the same.
Order #2 has a more recent order date.
Order #2 has a FeeDate Before the CancelledDate of Order #1 (or Order #1 has no cancellation date).
So in the table the orderID:2835692 of CustID: 24643 has a valid order fee. But all the subsequently placed orders have fees which were applied before the first order was cancelled and so I want to update the FeeInvalid column with a 'Y'. The first fee will always be valid.
I think I understand why the code I am trying doesn't achieve the result I want but I can't figure out how to write it correctly. Below is one example of code I've tried and also code to create the table and insert some test data.
update t1 SET FeeInvalid = 'Y' FROM MockData t1 Join MockData t2 on t1.CustID = t2.CustID WHERE t1.CustID = t2.CustID AND t2.OrderDate > t1.OrderDate AND t2.FeeDate > t1.CancelledDate CREATE TABLE [dbo].[MockData]( [OrderID] [float] NULL,
This is on SQL Server 2008. Please find a detailed description and the file of the data, that I am working on.
Requirements:
1. If 'Channel' is not equal to "Omnibus" where the 'Trans Description'is equal to "Purchase" and "Redemption" for one purchase and one redemption that match on 'System' , 'Account TA Number' , 'Product Name' , 'Settled Date' , and where the 'Trade Amount' of the purchase and redemption is within 5%, then display those set of records.
2. If deemed wash trades, allow user to update the purchase and redemption pair 'Trans Description' from "Purchase" to "Exchange In" and 'Trans Description' from "Redemption" with "Exchange out"
System Channel Dealer Name Firm Name Product Cusip Product Name Product Share Class Trade ID Settled Date Account TA Number Trans Description Trade Amount
SCHWABPORTAL US - ASG MILLIMAN MILLIMAN 64128K777 Strategic Income Fund A 29806259 30-Jan-15 000BY00F2RW Redemption $ 25,68,458.15
I am trying to build a query where I want to extract the sum of the scores for each code MCC and get the percentage over the sum of all the scores over the last 90 days
select MCC, sum(score) as total from scores (select Datediff(day, creationdate, getdate()) as Q from scores where Datediff(day, creationdate, getdate()) <90) group by MCC
That would be just the rows 1 6 3 0 2 6 1 0 2 5 3 0
If I use this:
Select CustomerId,PartNum, shipped, QtyInOrder AS MaxOrderedNotShipped from (SELECT [CustomerID] ,[PartNum] ,[QtyInOrder] ,shipped , row_number() over (partition by [CustomerID], PartNum order by QtyInOrder desc) as recid from [SILK].[dbo].[MaxofGroup]) as f where recid =1
there is no restriction, so I get the shipped...If I alter the where clause to work only on not shipped, I get no records...as below
Select CustomerId,PartNum, shipped, QtyInOrder AS MaxOrderedNotShipped from (SELECT [CustomerID] ,[PartNum] ,[QtyInOrder] ,shipped , row_number() over (partition by [CustomerID], PartNum order by QtyInOrder desc) as recid from [SILK].[dbo].[MaxofGroup]) as f where recid =1 and shipped=0
ProdName Amount TranType P1 100 A P1 100 S P2 200 A P2 205 S
In case the ProdName is same, and Amount = or (within +/- 5%) of Amount, I have to update the TranType column as IN/OUT respectively as shown below in the tables.
I am okay with using 2 different tables if needed as in the records comes in one table and then i can reference that table to upload the values in another.
ProdName Amount TranType P1 100 IN P1 100 OUT P2 200 IN P2 205 OUT
The order of the records coming in can be different order, they need not be subsequent.
