I am trying to select all the data which are not associated with values older than a specific value.. For example I have:
paola -- 2
paola -- 3
paola -- 10
jessica -- 3
jessica -- 12
james -- 3
james -- 2
james -- 5
I want to query only those names, that are associated with numbers greater than 3. So, for this example, the result would be only Jessica. the others should be discarded.
I need to select specific values from all rows where the value of a specific column is "Active"
This part works: SELECT LastName, FirstName, MiddleInit, ClientId FROM dbo.Client
But I want to add: WHERE StatusType = (Active) and how to do this.
I've a table that stores operationcode for each jobnumber. The jobnumber can have multiple operationcode. From the below DDL, I need to show all the jobs that have operation codes as 2001 and 2002. In the below DDL Jobnumber 80011 has both the operation codes 2001 and 2002 so this job will display on the report.
On the other hand Job 80021 only has operationcode 2001 and I do not want this job to show up on the report.
I need to show all the operationcodes for a job if it has operationcode 2001 and 2002.
USE tempdb;
GO
DECLARE @TEST_DATA TABLE
(
DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
, OperationCodeVARCHAR(10) NOT NULL
, EmployeeCode VARCHAR(10) NOT NULL
[Code] ....
I need a query to get the first value greater than a specific threshold value
LoadTemp1 LoadTemp2 LoadTemp3 LoadTemp4 TimeStamp
300 200 100 320 12-10-2013 13:30:29
100 250 113 340 12-10-2013 14:20:12
114 339 209 345 12-10-2013 14:45:01
I need to get the first value >= 340 threshold
I need to make a Benchmark on when the first LoadTemp crosses Threshold of 340 and capture the time.
I am using a table to store different size numbers for example:
Look at the picture below:
But I want this type of output look at the picture below:
How to sort the query to return greater than zero values to sort up and zeros go down.
I am using sql server 2008.
Hi Everyone.
Looong story short. I have a screen with search criteria. Right.
I have table :
tblCandidateLicense
intPkCandidateLicenseID (PK)
intFkCandidateId
intFkLicenseTypeID
And Another table
tblLicenseTypeDef
Columns:
intPkLicenseTypeID (Pk Int)
strDescription
strLicenseCode
intRank (just for ranking)
intRankParent (for grouping types of vehicles)
tblLicenseTypeDef data contains:
1 , MotorCycle < 125,0,1,0
2 , MotorCycle > 125,0,2,0
3 , Car 1 ,0,0,1
4 , Car 2 ,0,1,1
5 , Car 3 ,0,2,1
5 , Car 4 ,0,3,1
tblCandidateLicense data contains
1 , 57, 1
2 , 57, 3
3 , 58, 4
I use the intRank to determine which vehicle is bigger and intRankParent to make groups (so motorbikes and cars searched don't bring back the wrong type)
I want to be able to search on License Type .
But now a person can search for car 4 and motorcyle > 125. Then my query needs to find all the people with car type 4 AND Smaller (car-1-2-3-4) (that is group 4), and motorcycle and smaller (1 and 2) (Group 0)
If a person search for car type Car 3 and motorcycle < 125, then car 1-2-3 and MotorCycle 1 must be returned.
I hope it makes sense.
Hello,
What is the command to select rows between RowNum25 and RowNum30 in a Sql table?
Aditya
Hi Friends...
I have one table , suppose TableA which contains following columns..
I want to select records from this table where time difference is more than 2 min. (tec_insert_date) This means first record time and second one, then second one and third one..and so on..
How to select these records...pls give me some sql query...
thanks in advance
tec_number tec_insert_date tec_description
----------- ------------------------------------------------------ --------113909 2005-12-19 18:59:01.920 Description
113910 2005-12-19 18:59:02.043 Description
113911 2005-12-19 18:59:02.060 Description
113912 2005-12-19 18:59:02.060 Description
113913 2005-12-19 18:59:02.090 Description
113914 2005-12-19 18:59:02.090 Description
113915 2005-12-19 18:59:02.263 Description
113916 2005-12-19 18:59:02.437 Description
113917 2005-12-19 19:00:34.703 Description
113918 2005-12-19 19:00:34.720 Description
113919 2005-12-19 19:00:34.937 Description
113920 2005-12-19 19:00:35.607 Description
113921 2005-12-19 19:00:35.607 Description
113922 2005-12-19 19:00:35.840 Description
113923 2005-12-19 19:00:35.903 Description
113924 2005-12-19 19:00:36.200 Description
113925 2005-12-19 19:00:36.200 Description
113926 2005-12-19 19:00:36.217 Description
113927 2005-12-19 19:00:36.217 Description
113928 2005-12-19 19:00:36.217 Description
113929 2005-12-19 19:00:36.230 Description
113930 2005-12-19 19:00:36.230 Description
113931 2005-12-19 19:00:36.230 Description
113932 2005-12-19 19:00:36.230 Description
113933 2005-12-19 19:00:36.323 Description
113934 2005-12-19 19:00:36.450 Description
113935 2005-12-19 19:00:38.863 Description
113936 2005-12-19 19:00:38.943 Description
113937 2005-12-19 19:00:38.943 Description
113938 2005-12-19 19:00:39.100 Description
113939 2005-12-19 19:00:39.147 Description
113940 2005-12-19 19:00:39.300 Description
113941 2005-12-19 19:00:40.920 Description
113942 2005-12-19 19:01:23.363 Description
113943 2005-12-19 19:03:03.533 Description
113944 2005-12-19 19:03:08.197 Description
113945 2005-12-19 19:03:12.853 Description
113946 2005-12-19 19:03:14.087 Description
113947 2005-12-19 19:04:40.730 Description
113948 2005-12-19 19:04:44.843 Description
113949 2005-12-19 19:04:51.030 Description
113950 2005-12-19 19:04:56.313 Description
113951 2005-12-19 19:04:58.950 Description
113952 2005-12-19 19:09:19.950 Description
113953 2005-12-19 19:09:21.277 Description
113954 2005-12-19 19:09:21.340 Description
113955 2005-12-19 19:09:24.397 Description
113956 2005-12-19 19:09:35.660 Description
113957 2005-12-19 19:09:46.480 Description
113958 2005-12-19 19:09:47.790 Description
113959 2005-12-19 19:10:23.713 Description
113960 2005-12-19 19:10:23.980 Description
113961 2005-12-19 19:11:02.307 Description
113962 2005-12-19 19:11:52.040 Description
113963 2005-12-19 19:12:31.800 Description
113964 2005-12-19 19:12:31.940 Description
113965 2005-12-19 19:12:31.940 Description
113966 2005-12-19 19:13:10.500 Description
113967 2005-12-19 19:13:10.500 Description
113968 2005-12-19 19:13:50.697 Description
113969 2005-12-19 19:13:50.697 Description
113970 2005-12-19 19:13:50.743 Description
113971 2005-12-19 19:13:50.870 Description
113972 2005-12-19 19:13:50.883 Description
113973 2005-12-19 19:14:09.290 Description
113974 2005-12-19 19:14:47.680 Description
113975 2005-12-19 19:14:47.693 Description
113976 2005-12-19 19:14:48.630 Description
113977 2005-12-19 19:14:48.630 Description
113978 2005-12-19 19:14:49.127 Description
113979 2005-12-19 19:14:49.127 Description
113980 2005-12-19 19:14:49.160 Description
113981 2005-12-19 19:14:49.300 Description
113982 2005-12-19 19:14:49.313 Description
113983 2005-12-19 19:15:07.287 Description
113984 2005-12-19 19:15:07.783 Description
113985 2005-12-19 19:21:43.093 Description
113986 2005-12-19 19:22:01.920 Description
113987 2005-12-19 19:22:01.920 Description
113988 2005-12-19 19:22:20.780 Description
113989 2005-12-19 19:22:38.547 Description
113990 2005-12-19 19:22:38.547 Description
113991 2005-12-19 19:22:39.340 Description
113992 2005-12-19 19:22:39.340 Description
113993 2005-12-19 19:22:40.263 Description
113994 2005-12-19 19:22:41.010 Description
113995 2005-12-19 19:22:41.867 Description
hi
i m using row count in order to get first 16 rows from a specific table...now i want to get rows from row no. 16 to 32 (or any no which i want)...can any one tell meee how can i query it using sql server 200
Hello everybody
I'm workung on a report which will both function as a online report but also as a printed sheet. i've got a table which has to be sortable, so i made a user sort. however, to fit the report onto a page, i had to make a additional header row, which contains only the sorting arrows. now, i'd like this row not to apear on the printed out version, how would i do this?
Alternativly, would it be possible to not show the user sort icons (down- and upwards arrows), but to make the header fields clickable? This would solve my problem as well.
thanks for any advice
exmap
Hi,
I have a table where I want to use the sum function, but only on the rows with a positive value.
Is there any way to do this?
I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.
VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).
The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.
I also would like to add two derived variables.
1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.
2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.
My report should look like:
VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState
1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE
1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE
Can someone please help me here?
Thanks,
Romakanta
In my datawarehouse fact table I have a column (revenue) that I want to populate based on the values of number of columns, for simplicity, say just 2 columns, 'productid' and 'affiliateid'.
I have a revenue lookup table, with those same 2 columns and the amount. So far so simple, but rather than have one row for every possible combination, I use 0 to mean default. For instance, all the affiliates have the same revenue value apart from a couple, so instead of 200 rows identical except for the affiliateid, I have one row with a '0' for the affiliateid and 4 rows with specific affiliateIDs where it differs from the default.
E.G.
AffiliateID, TypeID, Revenue
0, 1, £50
22, 1, £55
33, 1, £57
0,2,£60
22, 2, £66
33, 2, £69
To update the values, I join to the revenues table twice, one for both columns matching, and once for the default. I.E.
UPDATE facttable SET revenue = ISNULL(rev1.revenue, ISNULL(rev2.revenue,0))
FROM facttable FT
LEFT OUTER JOIN revenues rev1 ON FT.AffiliateID = rev1.AffilateID and FT.TypeID = rev1.TypeID
LEFT OUTER JOIN revenues rev2 ON rev1.AffilateID = 0 and FT.TypeID = rev1.TypeID
(In fact, this is over-simplified, because in fact there are 3 columns, so I have to have 8 joins like this).
This works very well, and cuts down the management of revenues significantly, there are a few 100 rows instead of the more than 100,000 there would be if I put every possible combination of values in its own row.
However, now there is a requirement to increase the granularity of the revenue allocation up to 5 columns, which makes 36 joins and there could well be more columns added later.
Has anyone come across a situation like this (and found a neater solution).
Im Working with stored procedure. How can i compare Columns with specific values. I want to get the greater values of those column and inserted it to other columns. i want something like these
CASE
WHEN a> b,c,d THEN a
WHEN b> a,c,d THEN b
WHEN c> a,b,d THEN c
WHEN d> a,d,c THEN d
is there any ways to implement this? i got an error..
thanks please help..
Hi.
I had no idea what to name the topic so I hope this is ok.
I feel like I am losing it, even though I am still learning SQL Server - I dont use it much in terms of technical/complex queries and so on but I do use SQL on a regular (almost) basis. I am all about making sure it is secure and effecient and performance and so on - just to give a bit of background about myself.
Say for instance I have an ASP.NET website, which I have developed a discussion board from scratch using SQL Server to store all the information.
Say for instance, we have a topic, and that topic will be "locked".
Would I be correct in saying that on such a table, "Topics", there should be a field which is known as "ActiveStatus" or "IsLocked", and depending on if the thread is locked or not, that value is set on the table field? Am I correct in saying this?
If not - then what is the correct way of stating/retrieving if the topic is locked, in other words, what is the best way to have control over the thread so you can lock the topic?
I cannot think of another way but to store this one value in SQL on this topic table, along with other fields. I would then get the result by calling the procedure from ASP.NET and accordingly, set an image button on the webpage to either "locked" or "post reply"
Sorry for being silly, but I want to confirm if I am about to do this correctly or not. I want to make sure I am doing the best practice all the time, and enjoy doing it naturally. What is the best design/decision on such a scenario/situation?
Many thanks for your input, I greatly appreciate it! :-)
I am working on a multi-page datagrid that pulls data from a database. The issue I am running into is the SQL select query. What I have is a table to 55 items. What I need to do is grab the first 35 of those items and bind the resulting DataReader to the grid, print the page, then grab the remaining 20 items, bind to the grid and print the page.
I can use "SELECT TOP 35 FROM Table" to get the first 35 items, but I don't know how to get the remaining 20 items. Is there a way to say something like "SELECT TOP 20 FROM Table" but specify only the rows that begin after row 35?
I tried doing this with an ArrayList but couldn't bind it to the DataGrid.
Any suggestions?
I have a table, multiple columns, thousands of rows.
Six of the columns is the data that i need to work with...
col1, col2, col3, col4, col5, col6
col1 and col2 - go together - example. col1 = amount col2 = description
col3 and col4 - go together col3 = amount col4 description
col5 and col6 - go together col5 amount and 6 description
i need to pull search the table based on an auto number "id" and pull in the necessary two columns that correspond with a set value in the description.
example:
if col4 has "fee applied" in the description i need to pull the amount.
Please help...
Thank you in advance
hi,
I would like to create a user defined SQL function which returns the number of rows which meets certain condition, and the average value of one of the culomns. I cannot find a code example for it. Please help.
Thanks,
Dror.
Hi,
Im using a select statement to query out the data from a table.
ex:- select * from tblperson
Now this select statement is returning me 500 rows
I want to see only the records between 100 - 200 out of these 500 rows..
Anyone help
Hi
I'm no expert at SQL, I can't figure out how to solve that problem.
The following statement:
SELECT TeamMemberId, max(MonthId) as MonthId
FROM Position
INNER JOIN [Month] m
INNER JOIN [Year] y ON m.YearId = y.Id AND (y.Id = @yearId OR y.Id < @yearId)
ON m.Id = Position.MonthId
GROUP BY TeamMemberId
Returns
TeamMemberId MonthId
1 14
6 20
which is exactly what I want: The greates MonthId for each TeamMember in a specific year.
But I need more data for this postition, like the PositionName.
But the statement:
SELECT TeamMemberId, max(MonthId) as MonthId, PositionName
FROM Position
INNER JOIN [Month] m
INNER JOIN [Year] y ON m.YearId = y.Id AND (y.Id = 2 OR y.Id < 2)
ON m.Id = Position.MonthId
GROUP BY TeamMemberId, PositionName
Returns
TeamMemberId MonthId PositionName
1 2 ***. d. Geschäftsführung
6 20 ***. d. Geschäftsführung
1 14 CEO
6 16 CEO
The red rows are the ones I wanna get.
How can I achieve that only the max. MonthId rows are returned, even when I need to select more columns?
Any help is much appreciated.
Regards,
Stefan
Hi All
Lets say i have that table:
Month Sales
1 30
1 10
1 10
1 20
2 30
2 30
2 15
3 1
How can i get the sum of the sales where month=1?
I need to do it in the Expression of a text box
Hello. I have a somwhat simple question.
I have a table inside my database where i have some columns. Id like to extract distinct values from one column and inser them into another. My table is named article and id like to slect all the destinct values from the column naned type inside my article table. Then i need to insert my values into a new table called type but i would also like the to have 2 columns inside my type table. 1 called counter witch is an auto increment PK, and another one named type where the results from my query would be inserted.
Iv tried a veriety of querys but none of them have managed to do this.
Could anyone help me construct this query?
Give this data set;
declare @BO table (Col1 varchar(50), Col2 varchar(50))
insert into @BO values
('01','009920140516102116'),
('071710811019600001000005',''),
('070534524264000001000005',''),
('001806505517000001000005',''),
[Code] ....
select * from @BO
The 2 digit number that appears on line 1, 7 and 13 (only in this example) i need to have added to the begin of each value below it until the next 2 digit number is encountered. The desired result set would look like:
declare @BOD table (Col1 varchar(50), Col2 varchar(50), col3 varchar(50))
insert into @BOD values
('01','009920140516102116',''),
('071710811019600001000005','','01071710811019600001000005'),
('070534524264000001000005','','01070534524264000001000005'),
[Code] ....
I'd like to limit my query results to only items that match any part of a dynamic csv string table but am having some trouble (postgres SQL). Details: I need to calculate how many hours our staff spends seeing clients. Each staff has different appointments that can count toward this. The specified appointments for each staff are listed as comma separated values. My existing query calculates the appointment hours for each staff in a given time period.
However, I need limiting my query to only include specified activities for each staff. My current where clause uses IN to compare the appointment (i.e. activity) listed in the staff's schedule with what is listed an an approved appointment type (i.e. performance target activity). The query runs but it seems to only count one of the activities listed in the csv rather then count all the activities that match with the csv.
select (sum (kept)/60) from (select distinct rpt_scheduled_activities.staff_id as sid,
rpt_scheduled_activities.service_date, rpt_scheduled_activities.client_id,
from rpt_scheduled_activities inner join rpt_staff_performance_target on rpt_scheduled_activities.staff_id = rpt_staff_performance_target.staff_id where
[code]...
I have a very large table , and from that table I need just 2 records with column1 = 'A' and column1 = 'B' .
Here I don't think if I can not use OR or IN or Case operators because I need exactly 2 records not more.
Hello all, thanks in advance for any help you might be able to give.
I'm familiar with the Top command but I need something else to help in a project I'm working on.
I would like to select rows 1 through 100000 from a specific table in one query then 100001 through 200000 in a second query and 200001 through 300000 in a third and so on until I have gone through all rows. There happens to be 424000 in the table I'm working on.
Any help is appreciated.
I've the following data:
<Hour>11</Hour><Hour>12</Hour><Hour>15</Hour><Hour>18</Hour>
And my goals is to the extract the following example:
11,12, 15, 18
How Can I do That?
I have a DB with some tables and, on certain tables, i've a column named "ID_COMPUTER".
I need to find all the rows where id_computer=<specific_value> from all the tables of my database, when column "ID_COMPUTER" exists.
Pseudo-code would be: select * from db.* where id_computer=<specific_value>
how to write this ?
write a query which retrieves only unique rows excluding some columns.
IdStatusmanager Team Comments Proj number Date
19391New XUnassigned One 3732.0 16-Apr-14
19392Can YCustomer Two 3732.0 17-Apr-14
19393Can YCustomer Two 3732.0 17-Apr-14
19394Can YCustomer One 3732.0 18-Apr-14
19395New YCustomer One 3732.0 19-Apr-14
19396New YCustomer One 3732.0 21-Apr-14
19397New ZCustomer One 3732.0 20-Apr-14
In the above table project number and id shouldn't be considered and I should get the unique rows considering rest of columns and sorted based on date. Expected result is
IdStatusmanager Team Comments Proj number Date
19391New XUnassigned One 3732.0 16-Apr-14
19392Can YCustomer Two 3732.0 17-Apr-14
19394Can YCustomer One 3732.0 18-Apr-14
19395New YCustomer One 3732.0 19-Apr-14
19397New ZCustomer One 3732.0 20-Apr-14
19396New YCustomer One 3732.0 21-Apr-14
I have huge export files in a DB and i need to check if there are any datasets that have the same value in the first column, but a different in another one, via a query of course.
Like this:
ID IS NULL
1 1
2 1
3 0
1 0
The expected ID i get as a result of my query should be 1 in this case.
I need to show the total amount of rows in a specific table?
The query is as follows:
As part of the planning process to expand the database that supports Northwind operations, the IT manager would like to know how many rows are currently in specific tables so that he can conduct capacity planning.
The results needed include two columns, TableName( containing all the tables in the database and Rows, which contain the total amount of all the rows per table).
I have a text file which I used as an initial load of data, containing a little over a million rows. I would like to produce a daily "update" file, which contains changes to existing rows, and creation of new rows. What is the best method of updating the existing table in power pivot?
View 2 Replies View RelatedThis is primarily a question for DAX/Power BI. I'm trying to figure out how to count rows for a particular column where only a specific value exist. For instance, I'm trying to count how many times a certain field contains a true value or false value across multiple columns.
I found this formula here: [URL] ....
=CALCULATE(COUNTROWS('table'), ALLEXCEPT('table', 'table'[n_country]))
However, I'm not quite sure how to adapt it to count only rows where a value is equal to True, False, 17, "foobar", etc...