How To Order By A Portion Of A Cell Content?
Aug 5, 2007
Hi all,
i've problem with creating a select to order by a column where i will use a specific part of the cell content.
The rows ar looks a follows
ID | Titel | Ausgabe | Seite | kurzInfo........
5 | 3 Must-have-tools | 01/2006 | 32 | Oft sind es die......
6 | RowID in DataControl | 06/2005 | 11 | Gott sei Dank muss .....
now im looking for a solution to sort by the column [Ausgabe] but by the part after the slash so it looks like
........ | Ausgabe | Seite | .......
.........| 06/2005 | 11 | .......
.........| 01/2006 | 32 | .......
Is this possible without a sub select?
Thanks for any help.
View 5 Replies
ADVERTISEMENT
May 7, 2008
I have a table with 30 rows. When the data is got from the DB these 30 rows may or may not be filled but they should be visualized. How can I do this? I mean if only 10 rows are filled with data the other 20 to be visible.
I want if cell content grows than the whole row to change its height. Is it possible?
View 2 Replies
View Related
May 6, 2004
Hi!
What I'd like to do is:
UPDATE table1
SET
A_TEXT_COLUMN = (SELECT another_text_column
FROM table2
WHERE table2_id = @precomputed_id_1)
WHERE table1_ID = @precomputed_id_2
Since the cells are text, this does not work. Since the cell to be updated is in an already exitant row, it's not possible to simply use insert.
I'd like to do something like (PSEUDOcode):
WRITETEXT(table1.A_TEXT_COLUMN, READTEXT(@textptr_initialised_to_point_at_target_c ell))
But the *actual* synatx of WRITETEXT and READTEXT seem totally inappropriate for any such trick...
Any hints or pointers HUGELY appreciated... THANX
View 1 Replies
View Related
Oct 5, 2007
Hi,
I'm working with MRS and I've got a table with a lot of entries. For each value in the table I'm trying to get the text colour to be set to 'red' when the value of the cell is less than 0. Otherwise remain black.
I can do this by setting the colour property cell by cell. But I have a lot of cells in the table. Is there a way to set the statement to apply to ALL cells in the table?
Basically I'm asking if there is a way to set the property in bulk instead of going through tediously cell by cell.
Any help would be much appreciated. Thanks!
View 4 Replies
View Related
Jul 20, 2005
HI,I HAVE AN EXCEL SHEET WITH SOME DATA, I WANT TO IMPORT THAT DATA (CELLBY CELL WITH MANIPULATION) INTO THE SQL SERVER TABLES BY USING STOREDPROCEDURE(IF POSSIBLE).IF ANYBODY HAVE DONE SIMILER TYPE OF JOB OR KNOWING ABOUT IT, PLS. LETME KNOW.THANKS IN ADV.T.S.NEGI
View 4 Replies
View Related
Jun 15, 2006
Hi all
How to get just the date portion from a datetime field from sql server?
thanks a lot
View 5 Replies
View Related
Oct 10, 2005
I asked this question in the mysql forums, but I am also interested in any info regarding MSSQL. I've seen databases with search systems with the functionality I seek below. I just have no idea how they are doing it. Thank you.
We have an mysql inventory database. We want to be able to put in 4984.600 and choose "match any portion" and it finds 4984600 which is in our database.
Does Mysql have a "match any portion" search function? In this case LIKE didn't work which we tried already.
Any ideas? or are we stuck with using MSSQL. We know this will work with MSSQL.
Thank you very much. This is a huge problem for us.
Jim
View 3 Replies
View Related
Mar 27, 2008
I want to extract a particular portion of primary key field data.For example- suppose there are fifty data in PK field starting from 1...50.Now I want only to extract data from 25 to 50.It is bcos I want to retrieve only the new data that is inserted after a specified time.For example-today there is data starting from 1 to 25 which I have retrieved and saved elsewhere.By tomorrow there will be new data starting from 26 to 50 or more.Now I just want to extract this additional data from the database so that I dont need to extract all data again.Could anybody pls tell me how to do this.How can I do it using common SQL bcos I have to make a program for it.
View 1 Replies
View Related
Oct 11, 2005
Hello,
I asked this question about mysql on another forum, but I would also like to know about MSSQL. I may need to switch to this platform if Mysql doesn't work. Here's my problem:
Instead of "match", I am looking for a "match any portion" way to do searches with mysql.
We have an mysql inventory database. We want to be able to put in 4984.600 and choose "match any portion" and it finds 4984600 which is in our database.
Does Mysql have a "match any portion" search function? In this case LIKE didn't work which we tried already.
Any ideas?
Thank you very much. This is a huge problem for us.
Jim
View 3 Replies
View Related
Mar 27, 2008
I want to extract a particular portion of primary key field data.For example- suppose there are fifty data in PK field starting from 1...50.Now I want only to extract data from 25 to 50.It is bcos I want to retrieve only the new data that is inserted after a specified time.For example-today there is data starting from 1 to 25 which I have retrieved and saved elsewhere.By tomorrow there will be new data starting from 26 to 50 or more.Now I just want to extract this additional data from the database so that I dont need to extract all data again.Could anybody pls tell me how to do this.How can I do it using common SQL bcos I have to make a program for it.
View 1 Replies
View Related
Mar 27, 2008
I want to extract a particular portion of primary key field data.For example- suppose there are fifty data in PK field starting from 1...50.Now I want only to extract data from 25 to 50.It is bcos I want to retrieve only the new data that is inserted after a specified time.For example-today there is data starting from 1 to 25 which I have retrieved and saved elsewhere.By tomorrow there will be new data starting from 26 to 50 or more.Now I just want to extract this additional data from the database so that I dont need to extract all data again.Could anybody pls tell me how to do this.How can I do it using common SQL bcos I have to make a program for it.
View 1 Replies
View Related
Oct 15, 2007
Hi,
I know that I can use Round(<number>, integer) to round a number to the nearest integer, but is there a way to simply trip a number of its decimals without rounding and just get the inter portion?
Thanks.
View 13 Replies
View Related
Oct 26, 2006
I'm sure I'm missing something. I am returning the TOP X number of customers by revenue and I'd like to change the number of records returned by passing a parameter but I keep getting an error. @TopX int ( or varchar) SELECT @TopX CompanyName, Amount FROM Sales Where..... Why will this not work?
View 4 Replies
View Related
Oct 25, 2004
I have a SQL DB with a column called time_occurred that is formatted like ( 7/28/2004 7:10:30 AM ).
What I need to do is run a report based on just the month day and year portion. I am using the calendar control so mins, sec and milliseconds are not available nor do I really need them.
I am running the Query with this Stored Procedure
ALTER PROCEDURE dbo.Prodecure1
(
@WhereClause varchar(8000)
)
AS
-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(8000)
-- Enter the dynamic SQL statement into the variable @SQLStatement
SELECT @SQLStatement = 'SELECT column1, column2, etc...' +
@WhereClause
-- Execute the SQL statement
EXEC(@SQLStatement)
RETURN
View 1 Replies
View Related
Jun 19, 2014
i need to cut my string on 3 portion, for exemple my string is :
a) 1.9999-Q1
b) 01.9999-Q11
I need to keep all values before "." (point), and all values after "-" , and also all values between "." and "-", like this:
a) 1
9999
Q1
b) 01
9999
Q11
View 9 Replies
View Related
Apr 17, 2007
Is it possible to read a portion of a file using SSIS. The file format is:
date: 17 Apr 07
input file: input1.unl
output file: output.unl
table1-column1 table1-column2 ..................
123 23445
235 33367
table2-column1 table2-column2 ......................
234 454
444 89877
i have to read only the table1. i have written a routine in c# that reads only table1 but that is too slow. Is there any efficient solution?
View 4 Replies
View Related
Sep 1, 2006
the ssis expression language getdate() function returns the current date with the current time. i only need to get the current date, without the current time. for example: 9/1/2006
how would i construct the proper expression to return this value?
thanks in advance.
View 2 Replies
View Related
Oct 26, 2015
I want to update only time portion of a datetime column as 00:00:00:000
Values are like:
2006-08-28 17:10:10.607
2007-02-10 11:24:12.090
2007-02-10 11:24:14.967
I want to do them like:
2006-08-28 16:10:10.607
2007-02-10 10:24:12.090
2007-02-10 10:24:14.967
update [ALLBD].[dbo].[Terminal]
set [Hour]= '1900-01-01 09:49:00.000'
where ...
View 5 Replies
View Related
Oct 16, 2007
Is there a way to strip off the time portion of a datetime datatype without changing the datatype?
I know I can convert it using CONVERT (NVARCHAR(10), dbo.tblPayments.PaymentDate, 101) but I need to keep it as a datetime datatype?
View 5 Replies
View Related
Jul 13, 2000
Hi,
I have a requirement to be able to select and group records by the date portion of a datetime field. ie ignore the time when grouping so that all records lodged on a particular day are seen together.
I have been able to do this by
- converting the datetime data to the number of days since a given date
- inserting this into a temporary table
- retrieving the the data from the temporary table
- convert the data back to a date using DATEPART to display dd/mm/yy
This then gives me the data grouped as required but seems to be a very difficult solution - Is there an easier way??
Thanks in advance
jan
View 3 Replies
View Related
Apr 1, 2003
Hi all:
first of all, i must say that this website is just awesome...
my question is how do i truncate the time portion in a datetime stamp in a single sql statement.
thanks.
View 9 Replies
View Related
Feb 16, 2004
I have a PHP page where the user enters a date that represents the last day of a timesheet (ts_end) and the hours worked on that timesheet. That is then written into a table where the date is a datetime type. Because the user just enters a date, the time portion of the field is set to 00:00:00. In another place, I need to sum the columns for reports submitted between the beginning of a timesheet (ts_end -6 days) and the ts_end date.
The problem is that chartreviewed values entered on the ts_end date are getting lost because the time part of the ts_end field is 00:00:00 and the time part of the dateentered for the chartreviewed value is not. For instance using 2/4/2004 as the ts_end date looses the 192 charts.
Reporter activity chartsdateentered
2001576 20672 563 2004-01-29 13:55:51.000
2001576 20665 202 2004-02-02 19:54:57.000
2001576 20666 160 2004-02-03 22:48:11.000
2001576 20667 192 2004-02-04 19:41:51.000
I know I can revise the query to look for charts where the dateentered is less than dateadd(d,1,ts_end) and get the right values. It seems like there has to be a way though to tell sqlserver to ignore the time part of a datetime field when querying.
Anybody know what it is?
Thanks,
Ursus
View 4 Replies
View Related
Jul 20, 2005
In VBA I'd use Format(myDateField,"Short Date") to display 1/31/2004instead of 1/31/2004 10:30:25 AMHow can I do this in a stored procedure?lq
View 2 Replies
View Related
Jul 20, 2005
I need to replace a portion of a url in a column as a result ofchanging servers. Is there a SELECT/REPLACE/UPDATE combination querythat can do this. The table has close to a thousand entries and wouldbe nice if a query can be set to do this. Tried the REPLACE examplein the BOOKS ONLINE but it creates syntax error, apparently because itdoes not like the characters in the url and/or wildcards. I don't needto replace the entire url, only the portion before ".com". Thanks inanticipation of your help.Pradip Sagdeo
View 3 Replies
View Related
Oct 23, 2007
I need to identify time spans where members identified as having a condition have NOT had any of 5 specified services in the past 12 months. I have a table (DiabStrata) that identifies time frames for which my data shows a member as having the condition, and I have 5 separate tables with the dates of the relevant services.
I can easily identify when a member hasn't had the service at all, or is lacking it at the start or end of the time frame for which they have the condition, but I'm hitting a wall on how to deal with gaps between the minimum and maximum identification dates.
Code Block
create table dbo.DiabStrata(memberid char(11),Strat tinyint, StratStart datetime, StratEnd datetime)
create table dbo.hba1c(memberid char(11),dos datetime)
insert DiabStrata(
select '1',1,'20060101','20070302'
union
select '1',1,'20070803','20080804'
union
select '2',1,'20020101','20080503')
insert hba1c(
select '1','20060301'
union
select '1','20070301'
union
select '2','20050101')
--Missed Service
Begin
select * into #eval from DiabStrata where strat=1
delete #eval
from #eval left join hba1c on #eval.memberid=hba1c.memberid where hba1c.memberid is null
--repeat for other indicators
update e
set stratstart=min(dos)
from #eval e join hba1c on e.memberid=hba1c.memberid
having min(dos)>stratstart
update e
set stratend=max(dos)+365
from #eval e join hba1c on e.memberid=hba1c.memberid
having max(dos)+365<stratend
delete from #eval where stratstart>stratend
--repeat for other indicators
Desired output is into DiabStrata with a strat of 2 for the time frame for which they have strat 1 but do not have all 5 services within the prior 365 days.
MID Strat StartStrat EndStrat
1 2 1/1/06 - 2/28/06
1 2 3/2/08 - 8/4/08
2 2 1/1/02 - 12/31/04
2 2 1/2/06 - 5/3/08
View 1 Replies
View Related
Apr 22, 2008
Hello, this seems simple but I've been banging my head a while.
I have a data field that is formated like this: NYT/2000-Subways/7510-Electronics Mtc/7540-Data Svcs.
I need to pull out the string after the second / and before the third / in this case the value is 7510-Electronics Mtc but it does change.
I have this function that returns the first value (NYT):
USE [Data_Warehouse]
GO
/****** Object: UserDefinedFunction [dbo].[Get_Dept] Script Date: 04/22/2008 09:22:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Provide the phrase before (ex. 'Worklog Related to'), the field searched (ex. UPDATE_ACTION), and
-- the phrase after (ex. ']').
-- This function returns the string found after any phrase and before any phrase, within any field searched.
ALTER FUNCTION [dbo].[Get_Dept](@in_Phrase_Before varchar(250),@in_Search_Field varchar(250))
RETURNS varchar(250)
AS
BEGIN
Declare @str_String As varchar(250)
Declare @tmp_String as varchar(250)
Set @str_String = null -- to clear the variable buffer
Set @in_Phrase_Before=char(37)+(LTrim(Rtrim(@in_Phrase_Before)))+char(37) -- if 'Null', all strings will be returned
If Left(@in_Search_Field,4) = 'NYT/'
BEGIN
Set @tmp_String = Right(@in_Search_Field,(len(@in_Search_Field) - 4 ) )
Set @in_Search_Field = @tmp_String
END
If (PatIndex(@in_Phrase_Before, @in_Search_Field)) > 0 --Checks to see if the phrase before is in the search field
Set @str_String=SubString(@in_Search_Field,1, (PatIndex(@in_Phrase_Before, @in_Search_Field)-1) )
RETURN @str_String
END
Thanks in advance for the help
View 10 Replies
View Related
Jul 27, 2007
Below is a file that I have loaded into a Sql table:
<btb-root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" btb-num-trans="2" btb-date="2006-11-09" btb-time="22:40:03" btb-sender="BTB" btb-receipient="BPO-USR">
- <btb-request req-method="Asynchronous">
- <req-header>
<req-btb-id>68790</req-btb-id>
<req-client-id>1133</req-client-id>
<req-product>BPO-Exterior</req-product>
<req-loan-number>00000</req-loan-number>
</req-header>
- <req-property-address>
<addr1>1115 TEST DR</addr1>
<city>TEST</city>
<state>TEstate>
<zip>30044</zip>
</req-property-address>
- <req-borrowers borr-type="Borrower">
<first-name>Test</first-name>
<last-name>TE</last-name>
</req-borrowers>
</btb-request>
My goal is to take the btb-date and store it in the same table I loaded the seperate nodes to. Currently I am loading the req-header, req-property-address, and req-borrowers.
This date will be static in that it will remain the same for every record. My goal is to read it in and store it along with each record. Hope someone can give me some help.
Thanks.
View 4 Replies
View Related
Sep 24, 2007
I have a datetime field named 'EntryDate' in one of the sql tables.
I want to update the time portion of this field and provide a default time of 8:00 AM IF the time portion is empty.
How can i do this?
View 6 Replies
View Related
Apr 13, 2015
I have created a crosstab query using the Pivot statement that returns the expected results. The results look similar to the sample below:
ItemKey Description Aflatoxin Coliform Bacteria E_Coli Fumonisin Melamine Moisture Mold Salmonella Vomitoxin (DON) Yeast
1000 Item1000 1 0 0 1 0 1 0 1 1 0
1024 Item1024 1 0 0 1 0 1 0 1 1 0
135 Item135 1 0 0 1 0 1 0 1 1 0
107 Item107 0 0 0 0 0 1 0 1 1 0
106 Item106 1 0 0 1 0 1 0 1 1 0
I'm using this statement to create the result set:
SELECT ItemKey, Description, Aflatoxin, [Coliform Bacteria], [E_Coli],[Fumonisin],
Melamine,Moisture, Mold, Salmonella, [Vomitoxin (DON)], Yeast
FROM
(SELECT tblInventory.ItemKey, tblInventory.Description,
jctProductClassificationRequiredTest.ProductTestClassID, tlbTestType.TestDescription
[Code] .....
Instead of doing a Count for the Pivot (the count will always be either 0 or 1 due to the design of the table being used), I would like to return an "X" for those records with a count of 1, and return a blank (otherwise null) for those records with a count of 0. So, the result set would look like:
ItemKey Description Aflatoxin Coliform Bacteria E_Coli Fumonisin Melamine Moisture Mold Salmonella Vomitoxin (DON) Yeast
1000 Item1000 X X X X X
1024 Item1024 X X X X X
135 Item135 X X X X X
107 Item107 X X X
106 Item106 X X X X X
I tried using a Case statement within the PIVOT portion, but I either did it incorrectly or it's not possible to do use a Case within the Pivot. Can I easily accomplish this?
View 6 Replies
View Related
Apr 21, 2008
I have startdate and enddate. I like startdate to be 4/28/08 12:00:00 and enddate to be 5/4/08 23:59:59. What update statement do i need to run to update table. Currently my table show startdate 2008-04-28 05:00:00.000
enddate 2008-05-04 04:59:59.000.
View 4 Replies
View Related
Aug 4, 2015
I'm trying to translate this portion of VFP code into LINQ query:
select COUNT(ID) as conflicts
from dbo.max4sale where <<thisform.cWhere>>
AND Start_Time >= <<VFP2SQL(m.ltBegin + m.lnStartTime)>>
and Start_time <= <<VFP2SQL(m.ltEnd)>>
AND CONVERT(varchar(5),Start_Time,108) <= <<VFP2SQL(m.lcEndTime)>>
AND CONVERT(varchar(5),End_Time,108) >= <<VFP2SQL(m.lcStartTime)>>
<<m.lcDays>>
Here is my non-working attempt:
var startTime = new DateTime(1900, 1, 1, beginDateTime.Hour, beginDateTime.Minute, 0);
var endTime = new DateTime(1900, 1, 1, endDateTime.Hour, endDateTime.Minute, 0);
var daysOfWeek = dailyLimits.Where(dl => dl.Selected == true).Select(ds => ds.WeekDay).ToList();
if (daysOfWeek.Count() < 7) // not all days of the week selected
[Code] .......
First of all, I see a bug in my logic now as the first part of the query I need to do all the time and only the second part if the count < 7. But that's not my problem - I can not figure out how to make times comparison only using LINQ. Ideally I think I'd like to have cast(start_time as time) >= @p1 as a result to be executed by LINQ.
BTW, I am only getting the error in run-time that Parse can not be interpreted. So, I need to figure out another way of making LINQ recognize my intent of checking time portion of the date only.
View 9 Replies
View Related
Apr 21, 2008
I like to add a day to this date and also make time to 23:59:59. So end result for this table and recrods will be 2008-11-09 23:59:59 and next row 2008-11-16 23:59:59 so on.....
2008-11-08 23:00:00.000
2008-11-15 23:00:00.000
2008-11-22 23:00:00.000
2008-11-29 23:00:00.000
2008-12-06 23:00:00.000
2008-12-13 23:00:00.000
2008-12-20 23:00:00.000
2008-12-27 23:00:00.000
2009-01-03 23:00:00.000
2009-01-10 23:00:00.000
2009-01-17 23:00:00.000
2009-01-24 23:00:00.000
2009-01-31 23:00:00.000
2009-02-07 23:00:00.000
2009-02-14 23:00:00.000
2009-02-21 23:00:00.000
2009-02-28 23:00:00.000
2009-03-07 23:00:00.000
View 3 Replies
View Related
Apr 21, 2008
I have two table I like to change startdate time to 00:00:00.00 and Enddate time to 23:59:59.000
Table 1
ID Startdate Enddate
418 2008-04-28 05:00:00.000 2008-05-04 05:00:00.000
419 2008-05-05 05:00:00.000 2008-05-11 05:00:00.000
420 2008-05-12 05:00:00.000 2008-05-18 05:00:00.000
421 2008-05-19 05:00:00.000 2008-05-25 05:00:00.000
422 2008-05-26 05:00:00.000 2008-06-01 05:00:00.000
423 2008-06-02 05:00:00.000 2008-06-08 05:00:00.000
424 2008-06-09 05:00:00.000 2008-06-15 05:00:00.000
425 2008-06-16 05:00:00.000 2008-06-22 05:00:00.000
426 2008-06-23 05:00:00.000 2008-06-29 05:00:00.000
427 2008-06-30 05:00:00.000 2008-07-06 05:00:00.000
428 2008-07-07 05:00:00.000 2008-07-13 05:00:00.000
429 2008-07-14 05:00:00.000 2008-07-20 05:00:00.000
430 2008-07-21 05:00:00.000 2008-07-27 05:00:00.000
431 2008-07-28 05:00:00.000 2008-08-03 05:00:00.000
432 2008-08-04 05:00:00.000 2008-08-10 05:00:00.000
433 2008-08-11 05:00:00.000 2008-08-17 05:00:00.000
434 2008-08-18 05:00:00.000 2008-08-24 05:00:00.000
435 2008-08-25 05:00:00.000 2008-08-31 05:00:00.000
Table 2
ID Startdate Enddate
445 2008-11-03 06:00:00.000 2008-11-09 06:00:00.000
446 2008-11-10 06:00:00.000 2008-11-16 06:00:00.000
447 2008-11-17 06:00:00.000 2008-11-23 06:00:00.000
448 2008-11-24 06:00:00.000 2008-11-30 06:00:00.000
449 2008-12-01 06:00:00.000 2008-12-07 06:00:00.000
450 2008-12-08 06:00:00.000 2008-12-14 06:00:00.000
451 2008-12-15 06:00:00.000 2008-12-21 06:00:00.000
452 2008-12-22 06:00:00.000 2008-12-28 06:00:00.000
453 2008-12-29 06:00:00.000 2009-01-04 06:00:00.000
454 2009-01-05 06:00:00.000 2009-01-11 06:00:00.000
455 2009-01-12 06:00:00.000 2009-01-18 06:00:00.000
456 2009-01-19 06:00:00.000 2009-01-25 06:00:00.000
457 2009-01-26 06:00:00.000 2009-02-01 06:00:00.000
458 2009-02-02 06:00:00.000 2009-02-08 06:00:00.000
459 2009-02-09 06:00:00.000 2009-02-15 06:00:00.000
460 2009-02-16 06:00:00.000 2009-02-22 06:00:00.000
461 2009-02-23 06:00:00.000 2009-03-01 06:00:00.000
462 2009-03-02 06:00:00.000 2009-03-08 06:00:00.000
View 1 Replies
View Related
