Group_Code *****Station_nbr*****Beg_Eff_Date*****End_eff_date
00000002 D01G00733 1/1/2007 8/31/2007
00000002N D01G00733 4/1/2007 8/31/2007
00000002W D01G00733 1/1/2007 12/31/2007
For the report I just want to be able to pick up the first and the last line.
Using dbo.Station_group Table
Thank you for the help
Hi
I have a table with a user column and other columns. User column id the primary key.
I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key
Thanks.
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.
Hi there !
Thanks for taking the time to read this thread.
I don't know whether anyone has this problem, but I am definitely not using the right keywords to search for a thread.
My situation is this...
I have a dataset that has values to fill cells to multiple tables in a report.
However, I only want to select specific data from the dataset to fill textboxes and others.
I cannot change the stored procedure, but the sample of the data is shown below:-
Row Stat Val
0 dtRpt1 02/01/2005
1 Value1 1
2 Value2 2000
3 dtMailSent 02/28/2005
4 Value3 0
5 Value4 5
6 Value5 658
I know it looks weird, but the row really represents which "row" or textbox is it to fill with the Val. The Stat Column is just a way to make sure that I am filling the right values.
so my new report would have multiple tables to denote different categories.
In my first table, I tried putting the cells as follows:-
(expressions are highlighted in italics and bold)
TextBox1 =IIF(Fields!Row.Value =0, Fields!Val.Value,"")
Table1
Column1
DetailRow1 =IIF(Fields!Row.Value =1, Fields!Val.Value,"")
DetailRow2 =IIF(Fields!Row.Value =2, Fields!Val.Value,"")
Table2
Column1
DetailRow1 =IIF(Fields!Row.Value =3, Fields!Val.Value,"")
DetailRow2 =IIF(Fields!Row.Value =4, Fields!Val.Value,"")
DetailRow3 =IIF(Fields!Row.Value =5, Fields!Val.Value,"")
DetailRow4 =IIF(Fields!Row.Value =6, Fields!Val.Value,"")
I only expect this report to print out one page holding the previous values.
However, it ended up printing like this
----------------------------------------------------------
Table1
Column1
DetailRow1 1
DetailRow2
Column1
DetailRow1
DetailRow2 2000
Table2
Column1
DetailRow1 02/28/2005
DetailRow2
DetailRow3
DetailRow4
Table2
Column1
DetailRow1
DetailRow2 0
DetailRow3
DetailRow4
Table2
Column1
DetailRow1
DetailRow2
DetailRow3 5
DetailRow4
Table2
Column1
DetailRow1
DetailRow2
DetailRow3
DetailRow4 658
------------------------------------------------------
I tried putting it into the headerrows instead of DetailRows, and it ended up printing the last value.
Is there anyway to do this ? print all the values out in one table ? I tried using textboxes, but I think I got my expression wrong.
Is this the correct expression ?
=IIF((Fields!Row.Value,"Dataset") =1, (Fields!Val.value, "Dataset"), "")
and it give me an error
The value expression for the textbox textbox5 contains an error: [BC30455] Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.
Appreciate any advice or suggestion for this scenario !
Thanks!
Bernard
HI How to get 100 ,500 and 1000 or (any particular record) record fromDatabase .Shafqat Soomro
View 3 Replies View RelatedUsing Transact-SQL how can I copy all fields except one from one record to another? The field in question being the identity field. Since, this field cannot be duplicated a simple INSERT statement fails. How can I specify an exclusion list of fields?
-Sumit
I have two tables in my database: order_id with fields order (text) and comp_ID (int) and another table called customers with comp_ID (int) and company name (text) and other company information fields. The link between the two tables is the comp_ID. With every order that's made the company that made the order is stored with it in the order_id table. If I type in the order id (text), I want to be able to use the order id to search the order_id table and find out what the comp_ID of the company that made that order is. Then use that comp_ID to pull up the record of company information from the customers table with the same comp_ID. Is there some way to do this in one query? Or how do I accomplish this?
View 4 Replies View RelatedI need to build a CTE query to find for the same Cabstamp (document) where i have different Origin.
I know that i can build this with a correlated subquery, but i´am curious about using CTE.
I post sample create Script:
create table #temp (Cabstamp varchar(10), account varchar(10),document varchar(15), origin varchar(2), debit numeric(10,2), credit numeric(10,2), datalc datetime)
insert into #temp (Cabstamp,account,Document, origin, debit, credit, datalc)
select 'ADM12345',111,'CMP-01','FO',1000,0, '20150110'
union
select 'ADM12345',112,'CMP-01','FO', 500, 0,'20150110'
union
select 'ADM12345',6811,'CMP-01','DO',0,1500,'20150110'
union
[code]....
Hello,Is it possible? Can I select a specific record of the cursor to besent to a seperate function to do all the computations etc.?Regards,VS
View 5 Replies View RelatedI have written a SQL statement.There is a table called customer.It contains all customer data with customerid as PK.There is another table called logs and it contains customerid as foreign key and it contains a field to keep more than 90 days older user accounts.That field name is "Checked"
What I need get all records from these 2 tables and remove/hide more than 90 days older customers from record set.See my illustration.
I have written this code but I dont understand how to remove more than 90 days older user from result (because customer table doesnt contain a record called "Checked")
SELECT *
FROM [dbo].[Customers],[dbo].[VIESLog] WHERE [dbo].[VIESLog].[Checked] < DATEADD(day, -90, GETDATE())
I have a table of "applicants" with unique applicant id and another table "reviews" with reviews which has unique id and Emplid and contains general program name like Math and then may contain 3 addition rows for specific program like Calculus, algebra, geometry etc.
There may or may not be a record for each applicant id in table reviews or there can be 1 or more than one record in reviews based on level of review( General or Specific).
All the general reviews has “Math” as Program_code but if there are more reviews, they can have Program_code like “Cal” , “Abr”, “Geo”
I want to join the tables so I can get all the records from both the tables where Program_code in reviews table is “Math” only.
That is I want to join the table and get all the records from reviews table where the program_code is “Math” only
How can I do that?
I have a case where I need to randomly select two different serial numbers (a list of serial number are already in a table) and assign it to two variables. Or select a specific row from a list of results. Is there a direct way to do this?
e.g.
declare @var1 varchar(10), @var2 varchar(10)
select top 1 @var1=Serial from Table order by Serial
select top 1 @var2=Serial from Table where ServTag not in (@var1) order by Serial
select @var1, @var2
:confused:
I've a select SELECT Field1, Field2, Field3 FROM Tablethe problem is that I can have filled Field1 OR Field2 ... I would like to create a new column in the result for see only the filled field if Field1 is = null or = ' ' I want to see Field2 if Field2 is = null or = ' ' I want to see Field1
View 2 Replies View RelatedHello,
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
If I have a table split up among partitions A, B, C, how do I select only from partition C?
~Le
I need to check if the latest record in a table for the current client, based on EFFECTIVE_DATE, has a value of 'Y' in the STATUS field. Sample records for client #19:
CLIENTEFFECTIVE_DATESTATUS
------ -------------- ------
19 08/13/2010Y
1901/10/2013N
1902/03/2014Y
If I run:
SELECT CLIENT, max(EFFECTIVE_DATE), STATUS
FROM FTABLE GROUP BY CLIENT, STATUS
I get the two latest records since STATUS is different:
CLIENTEFFECTIVE_DATESTATUS
------ -------------- ------
1901/10/2013N
1902/03/2014Y
I wrote the following and it works, but it may be causing 'Insufficient Disk Space' error from the database. Is there any way to write this query without temp table?
DECLARE @TEMPMAX
TABLE (CLIENT INT, EDATE DATE)
INSERT INTO @TEMPMAX (CLIENT, EDATE)
SELECT CLIENT_NO, max(EFFECTIVE_DATE)
[Code] ....
CLIENTEFFECTIVE_DATESTATUS
------ -------------- ------
1902/03/2014Y
How to select specific character when you have a different length of characters. For example I have a columm where I have character ":". How to select only ":"?
View 2 Replies View RelatedHi all,Right now I have this SELECT statement:SELECT MUSIC_PK, MUSIC_TITLE, MUSIC_TITLE2, MUSIC_ORIGINAL_SINGER Now I want to add a second SELECT statement (same as above, but with an additional column) if the record was added within the 10 days (while still select other records, I just need to select an additional column). How would I do that? The additional column that I want to select is called MUSIC_NEW (so I can display if the record is new within 10 days.) I have a column in my table named DATE_ADDED, how do I go to calculate if it's within the 10 days since the day it was added to the current time?Thank you very much,Kenny.
View 8 Replies View RelatedI 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 (InfoTable) with a field with XML info like this:
<Status>
<IdStatus>1<IdStatus>
<NomStatus>Status 1<NomStatus>
<IdStatus>2<IdStatus>
<NomStatus>Status 2<NomStatus>
]Code] ...
I want to be able to select then NomProcess where IdProcess=3, for instance.
How can I do it?
Dear GroupI wonder whether you can give me a syntax example for a SQL Statement.Lets assume I've a table containing three columns ContactID (Primary Key),Firstname and Lastname.I would like to write a stored procedure which returns me the first tenrecords and increments an outside variable each time it runs.E.g If I run it the first time I pass the variable as 0 and it will returnme the first ten records and returns the variable value 1.When run a second time, I will pass the variable as 1 and it will return merecords 11-20 and sets the variable to 2 and so on...The difficult thing is how to tell to return me records 11-20. I can't usethe ContactID as someone might have deleted a row and e.g. ContactID 18 ismissing. In this case I only would get 9 rows returned. It always should beten.Thanks very much for your time and efforts!Kind Regards,Martin"There are 10 types of people in this world: Those that understand binaryarithmetic, and those that don't."
View 3 Replies View RelatedI have table with below scheme
Year Month Day RELH
------ -------- ------ -------
1973 4 1 50
...... etc
1973 4 30 20
.....etc
1974 5 1 50
....etc
1974 5 30 99
I need to get years where average of RELH of months within year meet
Month 4 Average(RELH) > 33 and
Month 5 Average(RELH) > 60 and
Month 6 Average(RELH) < 33
How can I achieve this?
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
In SQL Server I can select a specific column in from of * like so:
Code:
select test_column_1,* from testtable1
I've been googling around and cannot seem to be able to find a definitive answer.
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.
Hi friends,I need to select columns for specific data from all tables in DataBasePls give me reply asap Regards,M.Raj
View 4 Replies View RelatedI have a table that has for each shop a value that can change over time.For example
BK_POS 1 --> Segment A
BK_POS 1 --> Segment /
What I would like to achieve is to get all distinct Shops (BK_POS) from the table above, but if for that specific pos a row exists where the segment = "/" then I do not want to take this BK_POS in my select query.More concrete, the for example above I do not want to select BK_POS 1 because he has one row where the segment = "/".
i have table that has rows as below
DECLARE @Table TABLE
(minv_code INT,
alert_msg varchar(10),
alert_time Datetime)
[code]...
i want to select the data priority wise. the o/p should look like
e.g first row - 873939, 'Meter', '7/24/2015 3:31:22'
second row - 873939, 'Tamper', '7/24/2015 3:30:00'
third row - 873939, 'Reverse', '7/24/2015 3:31:18'
fourth row -873940, 'Tamper', '7/24/2015 3:31:22'
fifth row - 873940, 'Reverse', '7/24/2015 3:30:00'
I would like to exec a select statement in VB/C++ to return first 100 records? What is the SQL statement should be?
Thanks,
Sam
I have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'
Example Record 1. Elland **REQUIRES BOOKING IN***
Example Record 2. Theale, Nr Reading, Berkshire
Example Record 3. Stockport
How do I achieve this in a CASE Statement?
The following two case statements return the correct results, but I some how need to combine them into a single Statement?
,LEFT(Address ,CASE WHEN CHARINDEX(',',Address) =0
THEN LEN(Address )
ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'
,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0
THEN LEN(Address)
ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'
Hi
Here is my example: I have the following table
ID Name Phone
--- ---- -----
1 John 1234567
1 John H.
2 Dave 9876543
2 Dave Smith
I want to select only one record of each ID, the output should be
ID Name Phone
--- ---- -----
1 John 1234567
2 Dave 9876543
It doesn't matter which record to select, but I need just a single complete record. I can't use select distinct ID because it will not show the other fieldnames.
Thanks
I have a sql query that looks for values in a few different databases....
is there a way to select the last record in the table b/c I am pulling the hours worked on jobs and one person may have 3 job titles but i want it to show the balance of hours under the most recent job... Instead all the jobs are showing the same value even though only one of them had those hours under it. I think a way to get around this is to select the last value b/c that is under the job title most recently worked and they are in order by timesheets which can be ordered by date...any ideas?