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?
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 RelatedI 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
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
I want to update a field with a trigger only if a specific field is updated.
When I try the code below, it updates the field when any field in the record is updated. Is there a way to only make look at picked_dt?
ALTER TRIGGER [dbo].[UpdatePickedDate]
on [dbo].[oeordlin_sql]
after update
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[Code] .....
I am trying to make a field that has info from othe fields
Table = Page0
Fields = LName, FName, SS
I want a new field (Folder) to be all three fields.. for example
LName= Smith
FName= John
SS= 1234
I want to update Folder = Smith_John_1234
Hi guys,
I need to get a column with the sum of the field "SUF" from table "JurnalTransMoves_1" when that field ("SUF") is ordered by the field "REFERENCE" from table "Stock", and Show the value only once.
The desired result should by something like:
Stock.REFERENCE
JurnalTransMoves.SUF
SUM(JurnalTransMoves.SUF) Group By Stock.REFERENCE
5752
10
60
5752
20
5752
30
5753
400
3000
5753
500
5753
600
5753
700
5753
800
5754
7
15
5754
8
Is there any chance to do that?
Thanks in advance,
Aldo.
Code Snippet
SELECT
Accounts.FULLNAME AS 'ACCOUNTS.FULLNAME',
Accounts.ACCOUNTKEY AS 'ACCOUNTS.ACCOUNTKEY',
Accounts.FILTER AS 'ACCOUNTS.FILTER',
Accounts.SORTGROUP AS 'ACCOUNTS.SORTGROUP',
AccSortNames.SORTCODENAME AS 'AccSortNames.SORTCODENAME',
Accounts.CreditTermsCode AS 'Accounts.CreditTermsCode',
CreditTerms.DETAILS AS 'CreditTerms.DETAILS'
CreditTerms.CURRENF AS 'CreditTerms.CURRENF'
CreditTerms.MONTH AS 'CreditTerms.MONTH',
CreditTerms.DAYS AS 'CreditTerms.DAYS',
CreditTerms.SHAREPRC AS 'CreditTerms.SHAREPRC',
CreditTerms.TEMF AS 'CreditTerms.TEMF',
CASE
WHEN CAST(Accounts.VatExampt AS int) = 0 THEN 'x'
WHEN CAST(Accounts.VatExampt AS int) = 1 THEN 'y'
ELSE 'Undefined' END AS 'VAT',
Stock.DOCUMENTID AS 'Stock.DOCUMENTID',
DocumentsDef.DOCNAME As 'DocumentsDef.DOCNAME',
CASE
WHEN CAST(Stock.DOCUMENTID as int) = 1 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 3 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 35 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 120 THEN Stock.DOCNUMBER
WHEN CAST(Stock.DOCUMENTID as int) = 31 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 44 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 34 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 43 THEN Stock.REFERENCE
WHEN CAST(Stock.DOCUMENTID as int) = 40 THEN Stock.REFERENCE
ELSE '' END AS 'Invoice No',
Stock.VALUEDATE AS 'Stock.VALUEDATE',
JurnalTrans.DESCRIPTION AS 'JurnalTrans.DESCRIPTION',
JurnalTrans.REF2 AS 'JurnalTrans.REF2',
JurnalTransMoves.SUF AS 'JurnalTransMoves.SUF',
JurnalTransMoves_1.SUF AS 'JurnalTransMoves_1.SUF',
JurnalTransMoves.TRANSID AS 'JURNALTRANSMOVES.TRANSID'
FROM
JURNALTRANSMOVES AS JurnalTransMoves_1
INNER JOIN JURNALTRANSMOVES AS JurnalTransMoves
INNER JOIN (SELECT DISTINCT JURNALTRANSID, RECEIPTSTOCKID, FULLMATCH, TABLFNUM, CKCODE, RSORT, RUSEFID FROM RECEIPTJURNALMATCH) AS ReceiptJurnalMatch_1 ON ReceiptJurnalMatch_1.JURNALTRANSID = JurnalTransMoves.ID
INNER JOIN ACCOUNTS AS Accounts ON JurnalTransMoves.ACCOUNTKEY = Accounts.ACCOUNTKEY
INNER JOIN JURNALTRANS AS JurnalTrans ON JurnalTransMoves.TRANSID = JurnalTrans.TRANSID
INNER JOIN STOCK AS Stock ON JurnalTrans.STOCKID = Stock.ID ON JurnalTransMoves_1.TRANSID = JurnalTrans.TRANSID AND JurnalTransMoves_1.ACCOUNTKEY = Accounts.ACCOUNTKEY
LEFT OUTER JOIN ITEMS AS Items
INNER JOIN STOCKMOVES ON Items.ITEMKEY = STOCKMOVES.ITEMKEY
INNER JOIN ITEMSORTNAMES AS ItemSortNames ON Items.SORTGROUP = ItemSortNames.ITEMSORTCODE ON Stock.ID = STOCKMOVES.STOCKID
LEFT OUTER JOIN ACCSORTNAMES AS AccSortNames ON Accounts.SORTGROUP = AccSortNames.ACCSORTCODE
LEFT OUTER JOIN CREDITTERMS AS CreditTerms ON Accounts.CREDITTERMSCODE = CreditTerms.CREDITTERMSCODE
LEFT OUTER JOIN DOCUMENTSDEF AS DocumentsDef ON Stock.DOCUMENTID = DocumentsDef.DOCUMENTID
WHERE
Accounts.SORTGROUP Between '3001' And '3020'
AND Accounts.ACCOUNTKEY IN ('123456')
ORDER BY Accounts.ACCOUNTKEY
hi, please help me, i have a data driven site, i made a field that i call "Status"so this is how it should happens, there are three different gridviews in a single page, gridview1 filter is [status] = ok, gridview2 filter is [status] = fine, gridview3 filter is [status] = complete, so all gridview has its own <asp:hyperLinkField> at first the value of "Status" is ok, so it will appear in the gridview1, so when the user click on the <asp:HyperLinkField> of gridview1 it will go to a new page with a form view, this page has a button that when click will change the value of the status field to "fine", so that it will no longer appear to the gridview1 but it will now appear to gridview2 and same with the next <asp:HyperLinkField> click until it appears to the final gridview. so my question is how can i make the button that is capable of changing (update) the value my database field (status) to a specific value, that is not visible to the user thru codebehind or plain asp.net codes. please help me, if you need more info just reply.ThanksSALAMAT PO!
View 1 Replies View RelatedI am attempting to combine to queries that I created and am not having success. I need to get a count on a field for a specific value and have to do this by gender and by an age range. So here is the query I came up with. I don't get any errors produced but I also don't get any results.
$query = "SELECT p1.sex, age_group, COUNT(CASE WHEN p4.q1 = 'Yes' THEN p4.q1 END) AS heart_attack FROM (select p1.sex,".
" CASE WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate())) BETWEEN 11 AND 20 THEN '11-20'".
" WHEN datediff(year, dateadd(day, -datepart(dayofyear, GetDate()), p1.birthday) , dateadd(day, -datepart(dayofyear, GetDate()), GetDate()))
[code]....
Hi
Can someone write a query for me that would return names of tables that would slelect all tables of particular database and show if table has set primary key and if it has then if key has Identity Specification set to true?
something like:
SELECT
TABLE_NAME
,HAS_PRIMARY_KEY
,KEY_HAS_IDENTITY_SPECIFICATION
FROM
...this is where you come in
ORDER BY
TABLE_NAME
Hi
Can someone write a query for me that would return names of tables that would slelect all tables of particular database and show if table has set primary key and if it has then if key has Identity Specification set to true?
something like:
SELECT
TABLE_NAME
,HAS_PRIMARY_KEY
,KEY_HAS_IDENTITY_SPECIFICATION
FROM
...this is where you come in
ORDER BY
TABLE_NAME
Do triggers work on the field level or only for full table updates, etc...?
I want to have a trigger that will change a field on another table based on the update of a specific field on a table.
Could anyone provide a sample please, if this is possible?
Thank you!
Greetings!I have the 3 raw data tables below, and would like to extract somedata. My current query is really cumbersome, does anyone know a betterway to get all the info (attach the right state to the lowest zip code)in one step? Thanks a lot! Here are the details.- Ideal Output, for each person , keep(1) person_id (From Table A)(2) The earliest open_date of accounts starting with 2 (From Table A)(3) ssn (From Table B)(4) Zip and state info (From Table C), but only keep the lowest zip andstate.-Table A -person_idaccountopen_date10001220000015/15/200310001220000026/20/200410001300000012/2/200210002220000038/12/200410002220000049/15/200410002300000022/16/2005-Table B -person_idzip state1000111111AA1000122222CC1000233333BB--Table C -person_idssn100011234567100022345678-Ideal Output-person_idmin_openssnzipstate100015/15/2003123456711111AA100028/12/2004234567833333BBHere is what I did:Select a.person_id, min(a.open_date), b.ssn, min(c.zip) as zip,cast (0 as varchar) as stateInto output[color=blue]>From TableA as a[/color]Join TableB as bon a.person_id=b.person_idJoin TableC as con a.person_id=c.person_idWhere a.account like '2%'Group by a.person_id, b.ssnOrder by a.person_idUpdate outputSet output.state=b.state[color=blue]>From output as a[/color]Join TableC as bon a.person_id=b.person_idand a.zip=b.zip
View 2 Replies View RelatedI'm sure there's a way to do this, but I can't figure it out. I want to see if a field has a semi-colon in it and if it does display one thing, if not display another. But it doesn't just have a semicolon. It's a list of names, and if there is more than one name there will be a semi-colon between them.
I was hoping this would work:
=IIF(Fields!ID.Value LIKE "%;%", "yes", "no")
but it doesn't... I'm looking for something like:
=IIF(Fields!ID.Value CONTAINS ";", "yes", "no")
Any ideas?
Im trying to select rows on the following criteria
My app has a user that can have x jobs, each job has related entries which are marked has unread or read. I need to return any jobs that contain any unread entries also I need to return other jobs that have no entries marked as read.
I am able to return the following:
Comment UnReadMy Job 164
Comment UnReadFor Cam 166
Comment ReadThe Job 157
Comment ReadThird 159
Comment ReadMy Job 164
On Site Visit ReadMy newest job for log test 167
but as you can see jobid 164 appears in both groups and I need it to be one or the other
I tried using the if exists statement and was only able to return one group either unread or read, not both. I've tried everything but I'm new and I figure that theres got to be a more elegant way. Heres my sql:
CREATE PROCEDURE spGetJobsByUnreadAndReadByUserID
@UserID INT
AS
BEGIN
IF EXISTS(SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact,
Master_Jobs.Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who
FROM Master_Jobs INNER JOIN
Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN
Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN
User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN
Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN
Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN
Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND
Job_Assignments.UserID = Users.UserID
WHERE Users.UserID = @UserID AND-- Note.FK_UserID = User_Notes.FK_UN_UserID AND
BackUp_Read = 'UnRead')
BEGIN
SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact,
Master_Jobs.Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who
FROM Master_Jobs INNER JOIN
Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN
Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN
User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN
Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN
Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN
Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND
Job_Assignments.UserID = Users.UserID
WHERE Users.UserID = @UserID AND-- Note.FK_UserID = User_Notes.FK_UN_UserID AND
BackUp_Read = 'UnRead'
ORDER BY BackUp_Read ASC
END
ELSE
BEGIN
SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact,
Master_Jobs.Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who
FROM Master_Jobs INNER JOIN
Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN
Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN
User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN
Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN
Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN
Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND
Job_Assignments.UserID = Users.UserID
WHERE Users.UserID = @UserID AND-- Note.FK_UserID = User_Notes.FK_UN_UserID AND
BackUp_Read = 'Read'
ORDER BY BackUp_Read ASC
END
END
GO
Thanks again
Dear All,Suppose in the program a record is added to a table whoseprimary key is a identity field. If I really want to get the lastestvalue for that field after the insertion, is it the best way to useIDENT_CURRENT() to obtain this value?Thanks for your kind attentionYours faithfully,Benny
View 2 Replies View RelatedI’ve got an issue with extracting specific data from one field using charindex. Here’s an example of the dataset:
Cl_nr Var_data
20059942 ?;;300134BL10;?;;;;;;
20059958 ;2698;020225PU20;?;;;;;;
20059975 ;;100777ST20;?;;;;;;
20059976 ;;;;;;;;;
11001980 ;;051168PU20;?;;;;;;1001980
20034832 ;;060253BO10;?;;;;;;
20055246 ;;1108731;?;?;;;;;
20043656 ;;1022509;?;;;;;;
20059366 ;;1181750;31-12-2015;2;;;;;
20052712 ;;230626NO10;?;;;;;;
Goal is to get the data after the 2<sup>nd</sup> ; until the next ; starts. Ideal would be to catch everything between the 2<sup>nd</sup> ; en 3<sup>rd</sup> ; (number should be 10 characters).
If I try to select this data just using charindex it only goes until it finds the first ; (of course), what’s the best approach in this?
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 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?
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
If I have a table split up among partitions A, B, C, how do I select only from partition C?
~Le
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 RelatedGroup_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
I'm trying to find a specific string (a name) and replace it with another inside of a VARCHAR(7000) field. Unfortunately, there are names like Ted and Ken that I'm trying to replace. I would like to leave words like Broken, admitted, etc... intact.
UPDATEtbl
SETBody = LEFT(REPLACE(tbl.Body, pm.OldFirstName, p.FirstName), 7000)
FROM Table tbl
JOIN Person p ON p.PersonID = tbl.PersonID
JOIN PersonMap pm ON pm.PersonID = p.PersonID AND LEN(pm.OldFirstName) > 2
WHEREtbl.Body LIKE '%[^a-z]'+pm.OldFirstName+'[., ]%
'The problem I'm running into is that the '[, ]%' in the LIKE excludes any record that ends with the FirstName because it is requiring either a space, comma or period after the name. Is there some way to add an empty string to the list of acceptable characters as that would cover any scenario in the data? I would prefer not to add all characters except space, comma and period, but I guess I could do that.
Hi 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?
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
I've only been doing sql 2005 for a couple of months with minimal training so there's a lot I don't know.
What I'm trying to do is load a nested table (industry & customer totals) based on a value from the table it's nested in.
This is the relationship of the data.
I have at the highest group, an industry code, then a customer, then the part and then the fiscal year.
What I'm trying to accomplish is to get a group total (footer) for the (1) industry code and (2) the customer code. This footer would contain the fiscal years (ascending) and their monthly totals. I would like to take the industry code from table one and pass it to the select statement in the dataset that feeds the nested table. I've read this is not possible to load a dataset field into a parm but I've seen where some people know how to work around this. If you reply, please explain in simple terms. Thanks!
industry Customer Year OCT NOV DEC
001 - Signposts
M12345
Part 1
2006 5 6 2
2007 0 3 1
Part 2
2006 4 3 0
2007 1 0 7
Customer M12345 totals
2006 9 9 2
2007 1 3 8
M45678
Part 3
2007 8 4 7
2008 3 4 8
Part 4
2006 3 8 7
2007 5 6 6
Customer M45678 totals
2006 3 8 7
2007 13 10 13
2008 3 4 8
Industry 001 totals
2006 12 17 9
2007 14 13 21
2008 3 4 8
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.