I'd appreciate some help with the issue below - my SQL is a bit rusty and was never that hot to be frank. I'm using SQL Server 2000 (although have a test box with 2005 Express also). I've trawled MSDN and a few forums but can't find the solution (maybe I don't know what I'mm looking for!), so any help would be marvellous...
I have a table with a field called 'IRV' containing a string of comma-separated values. I want to be able to query a point in that string and count the number of times a given value appears. So...as an example, I want to count how many times '1' appears at position 7 in the IRV. I can create SQL to do this as follows:
SELECT COUNT(X) AS is1
FROM myIRVtable
WHERE (SUBSTRING(IRV, 7, 1) = '1')
So far so good. However, it is also possible that the value at position 7 in this string could be '2' (or '3', or '4', etc) - and rather than re-running the query again and again to get these values, I'd like to do it in one hit.
How can I combine all this together - anyone have any brilliant solutions?
I have a table that is linked to other tables in one to many relationship.I have a query using LEFT OUTER JOINs to join the tables together.There are multiple counts in the query and count numbers are messed up.(if there are 4 records from one table and 3 from the other - it shows 12 as the count) Your help is appreciated.
I need to build a table to combine data into a single table but I need it to include a count on more than 1 column. For example, I have a table containing a store number, an isbn, an on hand quantity, and an on order quantity. It's probably easier to explain with an example. I have a source table containing this:
My multiple level nested corelated query is not fetching correctresult. It work fine on small set of data, but fails on larger set ofdata. Any clue?Explaining data storing and discussing design would be tough for mehere, still to show you how complex I have created my life, here is thequery:select(SELECT Top 1 RowNSBranchID FROM AssoExtBranchToNSBranchMstM AM-- MMMWHERE AM.RowExtSysID IN(SELECT RowID FROM ExternalSystemMstM WHERE ExtSysID =(SELECT ExtSysID FROM ExternalSystemMstM WHERE SF = 'Active' ANDRowID =(SELECT MAX(RowID) FROM ExternalSystemMstM WHERE MCStatus = 2 ANDExtSysCode = UM.SystemCode)))AND RowExtBranchID IN(SELECT RowID FROM ExternalBranchMstMWHERE ExtBranchID =(SELECT ExtBranchID FROM ExternalBranchMstMWHERE ROWID =(SELECT RowID FROM ExternalBranchMstMWHERE ROWID =(SELECT MAX(ROWID) FROM ExternalBranchMstM WHERE MCStatus = 2 ANDExtBranchCode = UM.UpBranchCodeAND RowExtSysID IN(SELECT RowID FROM ExternalSystemMstM WHERE ExtSysID =(SELECT ExtSysID FROM ExternalSystemMstM WHERE SF = 'Active' ANDRowID =(SELECT MAX(RowID) FROM ExternalSystemMstM WHERE MCStatus = 2AND ExtSysCode = UM.SystemCode))))AND (SF = 'Active'))))AND AM.SF = 'Active'order by AssoID desc,TrackID desc) nsbranchid, UM.*fromTmpInProcessData062005MstM UM
I just inherited an app, where I have two tables that look like this: [Owners] -------- Owner ...
[Cases] ------- Owner Status Assigned ...
I need a query to get results that look like this: [Results] -------- Owner # of cases records where Status='Open' # of cases records Where Status='Pending' # of cases records WHERE Status<>'Closed' AND Assigned=''
I have one query that works already, but it's using several nested selects. I know I ought to be able to do this using group by instead, and I like to know how.
I have a table called enablers , with the following data
title Raiser Assignedto book Fred John Apple Peter Peter Orange Bill Roger Cup John Fred
For each time a users name appears in the raiser column they get 1 point, for each time a users name appears in the Assignedto column they get 1 point , but if their name appears in both Raiser and Assignedto for a particular row they only get 1 point not 2 points, I then need a count of raiser points plus a count of assignedto points to give a total points score ( raised plus assignedto)..I am looking how to get the output like below
Name Total Points Fred 2 Peter 1 Bill 1 John 2 Roger 1
Hi, my code repeats the same correct count numbers for every person in a query. I need them to count differently for each person in a table. I am using Access 2007 for now. Thanks.
SELECT tblOutlookTask.AssignedTo,DCount("*","tblOutlookTask","PercentComplete=100") AS Expr1, DCount("*","tblOutlookTask","PercentComplete<>100") AS Expr2 FROM tblOutlookTask Group By tblOutlookTask.AssignedTo;
I also tried this but it says 'missing operator on the second line' doesn't like the when in case maybe?
SELECT COUNT(CASE WHEN PercentComplete=100 THEN 1 ELSE NULL END) as Completed, COUNT(CASE WHEN PercentComplete<>100 THEN 1 ELSE NULL END) as Active, COUNT(CASE WHEN PercentComplete<>100 And [DueDate]<Date() And [DueDate]>Date()-7 And DueDate<Date() And DueDate>Date()-7 THEN 1 ELSE NULL END) as Overdue, COUNT(CASE WHEN tblOutlookTask.PercentComplete<>100 And [DueDate]<Date() And [DueDate]>Date()-14 And tblOutlookTask.DueDate<Date() And tblOutlookTask.DueDate>Date()-14 THEN 1 ELSE NULL END) as Offtrack FROM tblOutlookTask;
I am trying to insert into a row in SQL 2012 a default value for one column and then the results of some select count(*) queries into the other columns.Here is my example:
insert into x64_hourly_activity (Hour,Ingests,Restores,Proxies, Tape_Onsite, Tape_Offsite) select (select count(*) from x64_Ingest_metrics_temp where datepart(hh, date2) = 00 and datepart(mi, date2) <= 59), (select count(*) from x64_restore_metrics_all_temp where datepart(hh, date2) = 00 and datepart(mi, date2) <= 59), (select count(*) from x64_proxy_metrics_raw_temp where datepart(hh, date2) = 00 and datepart(mi, date2) <= 59), (select count(*) from x64_tape_metrics_temp where datepart(hh, date2) = 00 and datepart(mi, date2) <= 59 and Storage_Loc = 'XYZ'), (select count(*) from x64_tape_metrics_temp where datepart(hh, date2) = 00 and datepart(mi, date2) <= 59 and Storage_loc = 'JKL')
What is the statement I use to insert 12AM as a value for Hour?
Hi, I am trying to write a query that gets the percentage of students in specific racial groups in specific schools. Some ethnicity values of students are null so I have to use left joins. My query is below, when I run it I get the error "join expression not supported", I've tried a couple different ways of doing it but I always get that error or "syntax error in from clause". Can anybody help me with formatting multiple and nested left joins in general?
Thanks in advance.
drop table percentMinorities; create view percentMinorities as select s1.schoolid, round(count(s2.studentid)/count(s1.studentid),2) as percentWhite, round(count(s3.studentid)/count(s1.studentid),2) as percentBlack, round(count(s4.studentid)/count(s1.studentid),2) as percentHispanic, round(count(s5.studentid)/count(s1.studentid),2) as percentAsian
from
students as s1 left join (students as s2 left join (students as s3 left join (students as s4 left join students as s5 on s4.studentid is not null and s5.ethnicity = 'A') on s3.studentid is not null and s4.ethnicity = 'H') on s2.studentid is not null and s3.ethnicity = 'N') on s1.studentid is not null and s1.ethnicity = 'O'
Here is the desired counted output, I would like to pull distinct Date, MachineNumber, TestName and then count how many times they occur in the raw data form.I do need to perform a case on the date because right now its in a datetime format and I only need the date.
I am pulling three columns with the same names from 8 different tables. What I need to display the date, machine & test name and count how many times a test was run on a machine for that date. I have a feeling this can be handled by SSAS but haven't built an analysis cube yet because I am unfamiliar with how they work. I was wondering if this is possible in a simple query. I tried to set something up in a #Temp table. Problem is the query takes forever to run because I am dealing with 1.7 Million rows. Doing an insert into #temp select columnA, columnB, columnC from 8 different tables takes a bit.
SELECT right('0' + cast(month(s.closing) as varchar(2)), 2) + '/' + cast(year(s.closing) as varchar(4)) as ClosingDate, p.name as Project, SUM(s.amount) as TotalSales FROM Sales s JOIN Project p ON p.projectID = s.projectID WHEREs.closing >= DATEADD(mm, -12, GETDATE()) GROUP BY right('0' + cast(month(s.closing) as varchar(2)), 2) + '/' + cast(year(s.closing) as varchar(4)), p.name
This will give me the grouping by month/year and project.
On my company site, I have created a database that is for the purpose of tracking google adwords, as well as pages that the user visits. For instance, if you were to search for "guitars" and then click our ad, an entry is created in the database like thisKeyword: SessionGUID: PageVisited: VisitedDateTime:Guitars lkjfeilfjskdlfjsije ~ViewCategory.aspx?Cat=Guitars,KW=Guitars 12/01/2000 12:00amGuitars lkjfeilfjskdlfjsije ~ViewProduct.aspx?ProductID=1253&SubProductID=3 12/01/2000 12:03amGuitars lkjfeilfjskdlfjsije ~Search.aspx?Q=BC%20%Rich 12/01/2000 12:05am Pretty much, in our administrative area, I want to be able to have a table that would generate these results: Keyword Total HitsGuitars 3So im guessing that obviously I would need to do a select distinct for the Keyword column, but how do I also have a column showing a count of the records?
The row counts I am getting from SQL Enterprise Manager and QueryAnalyzer are different. When I double click a table in EnterpriseManager it gives me a row count of say 1000. However, when I select *from that same table in Query Analyzer it returns 1028 rows. Anyoneknow why or better yet, how to fix it? Thanks!
I have been struggling over the following problem for a few days... i was wondering if anyone could shed some light...!
I have the following query:
SELECT Field1, Field2 FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID WHERE Table2.Field1 = ( SELECT TOP 1 Field1 FROM Table2 WHERE Field3='X' ORDER BY Date1) AND Table2.Field2 = ( SELECT TOP 1 Field2 FROM Table2 WHERE Field3='X' ORDER BY Date1)
Is there a better way to do this. I was thinking of something very similar to the below query (Which doesnt work):
SELECT * FROM Table1 A INNER JOIN Table2 B ON (A.ID=B.ID) INNER JOIN ( SELECT TOP 1 * FROM Table2 WHERE Field3='X' ORDER BY Date1 ) C ON (A.ID=C.ID) WHERE B.Field1 = C.Field1 B.Field2 = C.Field1
Using c# in the compact framework, is there a way to do a parameterized query for counting the number of records where a specified column is null. I tried all of these, but none of them work:
cmd.Add(new SqlCeParameter("@IntColumn", SqlInt32.Null)); cmd.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";
cmd.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32)); cmd.Parameters["@IntColumn"].Value = SqlInt32.Null; cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";
I need to pass 3 column values and one Formula string into 4 replace statements and output the result in one column.
Nesting them in the usual way doesn't seem to work as that only allows for one column.
My table consits of four columns...PF (numeric), Hours (numeric), TotalNumber INT, and Formula (nvatchar)
My function needs to search and replace the Formula column for instances of all the three number columns and output the formula as a mathmatical formula rather than a string.
Here is what I have so far which works fine if all three columns have a value, but if only one is null then it will retrun NULL and not the other two values.
FUNCTION GetFormula (@numPF NUMERIC(10,2), @numHours NUMERIC(10,2), @intTotalNumber INT, @strFormula nvarChar(200)) RETURNS nvarchar(200) AS BEGIN DECLARE @strExpression nvarchar(200)
I am using Server 2012 and very new to SQL. I have a request from a physician for a list of his patients that meet a criteria. This is stored in a temp table names #cohort.
Using this cohort he wants each row to be one patient with a list of labs, vitals, etc. Three items are the most recent lab value and date. I could query each lab individually and place it into a temp table and then join all temp tables at the end, but I am trying to move past that and have all labs in one temp table. All temp tables are joined with PatientSID.
I tried to do something for just 2 labs, but it is not working. There could be nulls values when joined with the #cohort table.
Individually the SELECT statements pull in the most recent lab value and date, but I cannot get them into a temp table with one row of PatientSID and then the lab value and date if they exist.
IF OBJECT_ID ('TEMPDB..#lab') IS NOT NULL DROP TABLE #lab SELECT cohort.PatientSID ,SubQuery1.LabChemResultNumericValueAS 'A1c%' ,SubQuery1.LabChemCompleteDateTimeAS 'A1c% Date' ,SubQuery2.LabChemResultNumericValueAS 'LDL'
Hi,I'm having problems constructing a nested join. It's quite complex, sohere's a simplfied example of the problem. Any thoughts on what I'mdoig wrong - or if I've got the whole approach wrong are welcome.I've two tables :-one is a contact table contacting name, addresses etc. Three of thefields represent users - 'created by', 'last modified by' and 'owner'.They contain usernames - eg. JDOE, BSMITH etc.The other table contants usernames and new ID codes.What I want to do is create a new dataset by joining the contacts tablewith the user table on all three fields - so the new dataset containsthe ids for the creator, last modifier and owner.I've tried things similar to:select c.*, u1.id, u2,id, u3.idfrom contact cleft outer join users u1left outer join users u2left outer join users u3on (u3.username = c.owner)on (u2.username = c.modified)on (u1.username = c.creator )But it compains that"The column prefix 'c' does not match with a table name or alias nameused in the query."The problem is referencing c (contact) through the whole set of joins.I would like to do this in some similar format as the query is within acursor and post-processing would be very long-winded.Thanks
do i need to nest a query in RS if i want a calculated column to be compared against a multi value variable? It looks like coding WHERE calcd name in (@variable) violates SQL syntax. My select looked like
SELECT ... ,CASE enddate WHEN null then 1 else 0 END calcd name FROM... WHERE ... and calcd name in (@variable)
I am calling stored procedure called GetCommonItemCount within another stored procedure called CheckBoxAvailability, the first stored procedure should return a count to second stored procedure and based on that some logic will be executed.
I have 2 problems in that
1. The result is not coming from first stored so the variable called @Cnt is always 0 although it should be 18 2. At the end i need to see in the output the result from second stored procedure only while now i am seeing multiple results of record sets coming.
I have attached the scripts also, the line i described in step1 is
Hi,Please can somone help me with a nested SQL query. I have two tables please see belowTable 1CallIDEmployeeIDCallSummaryCallStatusTable 2CallHistoryIDCallIDDataAddedCallActionI would like to return the CallID, EmployeeID, CallSummary and CallStatus from Table 1, and also display the last CallAction from Table 2.This is a helpdesk database so a Call will have many CallActions i.e. Open, Held, Assigned Internal. How do I return the last CallAction Added against the selected CallID, I know I use the DateAdded but not sure about nested statements.The results I would like to return to the user would look like this:-Call ID: 1EmployeeID: 1Call Sumary: SQL ProblemCall Status: OpenCall Action (Last Action): Assigned Internal.
I want to write one query which will select multiple distinct records from one table For e:g Lets say in a table i have 3 fields name,tel_no,sex Now i want to list all the records which are distinct in each of these fields like distinct name,distinct address
Important Tables: Product (table of products) --ProductID --ProductName
ProductCategories (Associates a Product with one or more categories) --ProductID --CategoryID
Category (table of categories that a product may fall under) --CategoryID --CategoryName
Information:
Basically I have a product that falls into two categories. Therefore there are two records in the ProcuctCategories Table. I am trying to create a query that will find all products that are in categories 1 & 2.
Attempted Solution: SELECT * FROM Product WHERE (ProductID IN (SELECT CategoryID FROM ProductCategories WHERE CategoryID =1)) AND (ProductID IN (SELECT CategoryID FROM ProductCategories WHERE CategoryID =2))
This returned zero records though it should have returned the product that is in categories 1&2.
HiI have 2 tables. The first has employee information and the second haspayroll information. I need to find out people who are not in thepayroll but in the employee table.Since the payroll has multiple instances i have to filter it and findout for each payroll.I don't think i have explained it very well so here is the data set.hope someone can help me with this.Thanks in advancepritTbl EmployeePlanIDSSN100111111111110012222222221001333333333TblPayrolldetailIDNumPlanID SSN11001111111111110012222222222100122222222221001333333333Required RESULT required(Missing employees from payroll)IDNumSSN13333333332111111111
Hello Everyone,I am trying to create a query for the purpose of a nested repeater relation. The information needs to be pulled from one table. I have shortened the columns to the ones that are required.table - PagesIDPageNameParentPageIDSo, take the following example:ID 14, PageName - Service A, ParentPage ID = 6ID 15, PageName - Service B, ParentPage ID = 6ID 36 PageName - Client 1, ParentPage ID = 14ID 37 PageName - Client 2, ParentPage ID = 14ID 38 PageName - Client 3, ParentPage ID = 15ID 39 PageName - Client 4, ParentPage ID = 15 So, I want to create a query that will get my nested repeater to display as follows:Service A Client 1 Client 2Service B Client 3 Client 4What I have come up with so far is:SELECT * from tbl_Pages WHERE ParentPageID IN (Select ID From tbl_Pages)SELECT p.ParentPageID, p.PageName, p.ID FROM tbl_Pages pThe relation would be based off ParentPageID. I keep getting errors that either there is no unique value or the relation is null. What am I am missing here?
Hi,I'm probably missing something obvious (either that or doing this totally wrong).I'm trying to use a nested loop to generate the following results:Unit Day1 Day2 Day3 Day4 Day5Name1 25 45 89 54 76Name2 48 54 81 74 98What I have so far is this:WHILE @FCount < @TotalFoodUnitsBEGINSELECT (SELECT Unit FROM tbl_acc_FoodVenues WHERE UnitID = (@FCount + 1)) AS Unit WHILE @FDCount < @Days BEGIN SELECT (SELECT FdRevenue_a FROM tbl_acc_aud_SportsAudits WHERE AudDate = DATEADD(day, @FDCount, @pdStartDate)) AS Rev SET @FDCount = @FDCount + 1 END SET @FCount = @FCount + 1ENDAny suggestions please
I am trying to write a query that does not use inner joins to see if it returns rows faster than a query using inner joins. My trouble is that I don't know how to write the syntax for the 3rd table that I am comparing against. Can I nest Exists in a Where clause? If so how is the 2nd exists statement added to the query? see my sample query below ================================================== === 'With 2 tables
SELECT DISTINCT s1.ID, s1.SITE FROM SITE_TBL s1 WHERE EXISTS (SELECT * FROM DEVICE_TBL d1 WHERE s1.ID = d1.SITE_ID AND d1.DELETEFLAG <> 'D') ORDER BY SITE
'with 3 tables this doesn't work
SELECT DISTINCT s1.ID, s1.SITE FROM SITE_TBL s1 WHERE EXISTS (SELECT * FROM DEVICE_TBL d1 WHERE s1.ID = d1.SITE_ID AND d1.DELETEFLAG <> 'D') AND (SELECT * FROM BIG_TBL b1 WHERE d1.fqdn = b1.xyz) ORDER BY SITE
================================================== === Thanks for the help Jim
Hi, I have the following scenario that I am not sure how to best tackle. Any advice or examples is appreciated.
I am creating a stored proc that requires a code to be passed to it. In return data gathered from 3 different tables will be returned. The big catch is that 1 of the tables resided in a differenct database.
So, here is the data layout.
Database 1, Table 1 contains the following fields: Job, CustID, ShipID, and ShipMethod.
So the first question is how should the stored proc look with an input parameter of "Job" and output of Job, CustID, ShipMethod, ShipID, Address, City, State, Zip, and CustomerName?
Secondly, which database should the stored proc reside?
Again, any advise, suggestions, pointers, etc. are appreciated.
Can anybody please tell me if a query such as this (Valid in MS Access) can work in SQL Server:
SELECT Description, Sum(Total) FROM ( SELECT Description, Total FROM Table_A UNION ALL SELECT Description, Total FROM Table_B UNION ALL SELECT Description, Total FROM Table_C ) GROUP BY Description
The group of unions work by themselves, but when I try to nest an outer query to do some a Summation(), I have syntax errors.
Any insight would be greatly appreciated. Thank you.
SELECT SUM (dbo.HOLDING.Shares_Par_Value_Qty * dbo.ASSET.Current_Prc) AS MarketValue FROM dbo.HOLDING INNER JOIN dbo.ASSET ON dbo.HOLDING.Property_Num = dbo.ASSET.Property_Num Group by dbo.HOLDING.Account_ID
Account_ID is the same in both the queries ie in both the tables Holding and Account.
I need the output like this
Select Account_Id, Branch_Cd, MarketValue from -------
But MarketValue should be calculated exactly in the above method. How do I combine these two queries. I need it asap. Help me out.
