Re: How to group data which not the same ? - Database Discussion Boards

First Prev Next

Re: Select Query with column name containing []

Mycroft Holmes16-Dec-09 19:50

Mycroft Holmes

16-Dec-09 19:50

Did not even look at the moniker before answering otherwise I'd have been questioning your sanity.

Doing that is akin to sticking your finger in the power point, may not kill you but your fingers gonna sting for a while.

So did the extra set of brackets work? I flatly refuse to try it out

Never underestimate the power of human stupidity
RAH

Re: Select Query with column name containing []

dan!sh 16-Dec-09 21:16

dan!sh

16-Dec-09 21:16

Mycroft Holmes wrote:
Doing that is akin to sticking your finger in the power point, may not kill you but your fingers gonna sting for a while.

Never tried this.

Mycroft Holmes wrote:
So did the extra set of brackets work?

Yes it did.

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

How to group data which not the same ?

Golden Jing15-Dec-09 23:06

Golden Jing

15-Dec-09 23:06

Dear All,

First i would like to sorry if my expliant not clear.

My issue is that
I want group data in field company name which not equal each other.
It caused by inputting different users.
Example:
Company_Name
ComxxxA Co.
ComxxxA Co.
ComxxxA Co.,Ltd.
ComxxxA Co.,Ltd.
ComxxxA Co.,
ComxxxA Co.,
ComxxxA
ComxxxA
Comx
ComxxxxxxxB Co.,Ldt
ComxxxxxxxB Co.,Ldt
ComxxxxxxxB Co.,Ldt
ComxxxxxxxB Co.,Ldt
ComxxxxxxxB Co
ComxxxxxxxB Co.,
ComxxxxxxxB Co.,

..... (there r alot company name which difference)

So up to example above, what i want is the name is shortest
ComxxxA
Comx
ComxxxxxxxB Co

thanks for your help...

VB.Net

Re: How to group data which not the same ?

Niladri_Biswas16-Dec-09 2:00

Niladri_Biswas

16-Dec-09 2:00

Try this

declare @t table(Company_Name varchar(50))
insert into @t 
	select 'ComxxxA Co.' union all select 'ComxxxA Co.' union all
	select 'ComxxxA Co.,Ltd.' union all select 'ComxxxA Co.,Ltd.' union all
	select' ComxxxA Co.,' union all select 'ComxxxA Co.,' union all
	select 'ComxxxA ' union all select 'ComxxxA ' union all 
	select 'Comx' union all select 'ComxxxxxxxB Co.,Ldt' union all
	select 'ComxxxxxxxB Co.,Ldt' union all select 'ComxxxxxxxB Co.,Ldt' union all
	select 'ComxxxxxxxB Co.,Ldt' union all select 'ComxxxxxxxB Co' union all
	select 'ComxxxxxxxB Co.,' union all select 'ComxxxxxxxB Co.,'

Query:

select  Company_Name
from @t 
where replace(Company_Name, ' ','') not like '%[.,]%'
group by Company_Name

Output:

Company_Name
Comx
ComxxxA 
ComxxxxxxxB Co

Niladri Biswas

Re: How to group data which not the same ?

Golden Jing16-Dec-09 2:29

Golden Jing

16-Dec-09 2:29

Thanks,

Yes it like example.
But it is not enough yet.
Because in this field has more companies like that
up to differenc user enter difference.

So have any solution for protect it ?

VB.Net

[Message Deleted]

Niladri_Biswas16-Dec-09 3:23

Niladri_Biswas

16-Dec-09 3:23

[Message Deleted]

Re: How to group data which not the same ?

Golden Jing16-Dec-09 14:51

Golden Jing

16-Dec-09 14:51

it is not complete what i want.

but i would like to thank to you so much.

VB.Net

Re: How to group data which not the same ?

Luc Pattyn16-Dec-09 15:03

Luc Pattyn

16-Dec-09 15:03

Hi,

you can use SELECT DISTINCT[^] and ORDERBY to get an ordered list of all different company names present.
you can't find the "shortest" names as in your example, it would only give "Comx", not what you said it would.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

ADODB Recordset - using ActiveX Data Object in Access 2007

john john mackey15-Dec-09 12:38

john john mackey

15-Dec-09 12:38

Hi.
I wrote some VBA underlying code for an Access 2000 application and used to create/open recordsets in the following manner:

Dim rstLookupFixedAreas As New ADODB.Recordset
rstLookupFixedAreas.Open "tbl_FixedAreas", CurrentProject.Connection, adOpenStatic, adLockOptimistic

rstLookupFixedAreas.MoveFirst
Do Until (rstLookupFixedAreas.EOF)
    strValueList = strValueList & CStr(rstLookupFixedAreas.Fields(0)) & ";" & rstLookupFixedAreas.Fields(1) & ";"
    rstLookupFixedAreas.MoveNext
Loop

comboFixedArea.RowSource = strValueList
'comboFixedArea.DefaultValue = 1

rstLookupFixedAreas.Close

I did this by including the reference to Microsoft ActiveX Data Object 2.1 Library.

Now that I am using Access 2007, is there a more current method to do the same type of "recordset" operations? (or should i continue with including ADO 2.1 Library)

I want to use either a record index or Find method to get my record pointer.

Thank you for the help.
John John

MS-SQL - Bulk Insert query

22491715-Dec-09 11:50

224917

15-Dec-09 11:50

I have a .csv file with following column:

Col1,Col2

And the data table has one extra datetime column:

Col1,Col2,TimeStamp

While importing data using BULK INSERT from the CSV, is there a way to specify the value for the TimeStamp column too? (Timestamp would be same for all the newly imported rows)

Suhredayan

Re: MS-SQL - Bulk Insert query

Ashfield15-Dec-09 21:01

Ashfield

15-Dec-09 21:01

How about defining a default value for the column?

ALTER TABLE dbo.tablename ADD CONSTRAINT
	DF_TimeStamp DEFAULT '20091218' FOR TimeStamp

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: MS-SQL - Bulk Insert query

22491716-Dec-09 6:14

224917

16-Dec-09 6:14

Ashfield wrote:
How about defining a default value for the column?

That may not work since the timestamp is same only per bulk insert. I do have a solution for this, wrap bulk insert inside a stored proc, and update the the timestamp from SP. But this would introduce few additional steps, was wondering if this can be avoided in case if BULK INSERT already has this feature.

Thank you,
Suhredayan

Question about using conditional SQL statement

James Shao15-Dec-09 4:22

James Shao

15-Dec-09 4:22

I have a time series data in which a numerical indicator fluctuates over time. If I want to find out for which given day, this indicator has crossed above 30 in the past 3 days. Is it possible to write a SQL query that retrieve this information?

The logic to check for the crossing movement is:

IF indicator(day x) > 30 AND indicator(3 days ago from day x) <30 THEN
Return "YES WE HAVE AN UPWARD CROSSING MOVEMENT THROUGH 30 FOR PAST 3 DAYS!"
END IF

Can this be accomplished using a SQL CASE statement with a HAVING clause or something else?

I'd greatly appreciate any help! If possible please provide a sample code Poke tongue | ;-P

Re: Question about using conditional SQL statement [modified]

Ashfield15-Dec-09 4:34

Ashfield

15-Dec-09 4:34

An exists should do it - as far as I can tell from your description

select column_list....
from table1 t1
where indicator > 30
and exists (select 1 from table1 t2 where t2.pk = t1.pk and t2.indicator < 30 and t2.dateadded = dateadd(day,-3,t1.dateadded))

where PK is your primary key

[Edit] I had missed the date bit from the exists originally [/Edit]

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

modified on Tuesday, December 15, 2009 11:17 AM

Re: Question about using conditional SQL statement [modified]

James Shao15-Dec-09 15:02

James Shao

15-Dec-09 15:02

Hi Ashfield, Your code looks exactly like what I need! I'll give it a try after getting home. Smile | :)

Many thanks again.

modified on Tuesday, December 15, 2009 10:19 PM

Re: Question about using conditional SQL statement

Ashfield15-Dec-09 20:57

Ashfield

15-Dec-09 20:57

no problem

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

CROSS POST

dan!sh 15-Dec-09 5:08

dan!sh

15-Dec-09 5:08

Please do not cross post.

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

Re: CROSS POST

James Shao15-Dec-09 12:46

James Shao

15-Dec-09 12:46

Thanks for your help in the other section, sorry about cross-posting. Will try to prevent that in the future. Cry | :((

Sql Doubt

rajanandal15-Dec-09 2:26

rajanandal

15-Dec-09 2:26

Hi everyone,

my table is
6219 HBO 02 0
6220 HBO1 0201 6219
6221 HEAO 0202 6219
6222 HTS 0203 6219
6231 MEAO 0403 6228
6232 MTS 0404 6228
6234 Midde 05 0
6235 Overi 06 0
6228 MBO 04 0
6236 WO 10 0
6237 Post 07 0
6239 Basi NULL NULL
6448 Athe 05001 6234

but i need like this,
6219 HBO 02 0
6220 HBO1 0201 6219
6221 HEAO 0202 6219
6222 HTS 0203 6219
6228 MBO 04 0
6231 MEAO 0403 6228
6232 MTS 0404 6228
6234 Midde 05 0
6448 Athe 05001 6234
6235 Overi 06 0
6236 WO 10 0
6237 Post 07 0
6239 Basi NULL NULL

Nothing is Impossible. Keep always Smiling... Smile | :)

Re: Sql Doubt

Richard MacCutchan15-Dec-09 2:39

Richard MacCutchan

15-Dec-09 2:39

And?

Re: Sql Doubt

rajanandal15-Dec-09 2:53

rajanandal

15-Dec-09 2:53

that is all child records should be displayed next to parent.

Nothing is Impossible. Keep always Smiling... Smile | :)

Re: Sql Doubt

dxlee15-Dec-09 4:27

dxlee

15-Dec-09 4:27

If you tell us that the fourth field is the parent's ID (and the first field is its ID), then we will have an easier time trying to figure out what you want.

modified on Tuesday, December 15, 2009 10:57 AM

Re: Sql Doubt

Niladri_Biswas15-Dec-09 15:18

Niladri_Biswas

15-Dec-09 15:18

Try this.

Inputs:

declare @t table(childid int,name varchar(20),code int,parentid int)
insert into @t values (6219,'HBO',02,0)
insert into @t values(6220,'HBO1', 0201,6219)
insert into @t values(6221,'HEAO',0202,6219)
insert into @t values(6222,'HTS',0203,6219)
insert into @t values(6231,'MEAO',0403,6228)
insert into @t values(6232,'MTS',0404,6228)
insert into @t values(6234,'Midde',05,0)
insert into @t values(6235,'Overi',06,0)
insert into @t values(6228,'MBO',04,0)
insert into @t values(6236,'WO',10,0)
insert into @t values(6237,'Post',07,0)
insert into @t values(6239,'Basi',NULL,NULL)
insert into @t values(6448,'Athe',05001,6234)
select * from @t

Query:

;with cte as
(
	select 
		cast(t1.childid as varchar(1000)) [path]
		, t1.childid
		,t1.name
		,t1.code
		,t1.parentid 
		,0 AS [Level] from @t t1 where parentid =  0 or parentid is null

	union all
	select 
		cast([path] + '/' + cast(t1.childid as varchar(1000)) as varchar(1000)) [path]
		,t1.childid
		,t1.name
		,t1.code
		,t1.parentid 
		,c.[Level]+1 AS [Level]from @t t1
	join cte c
	on c.childid = t1.parentid
)
select childid,name,code,parentid
from cte order by [path]

Output:

childid	name	code	parentid
6219	HBO	2	0
6220	HBO1	201	6219
6221	HEAO	202	6219
6222	HTS	203	6219
6228	MBO	4	0
6231	MEAO	403	6228
6232	MTS	404	6228
6234	Midde	5	0
6448	Athe	5001	6234
6235	Overi	6	0
6236	WO	10	0
6237	Post	7	0
6239	Basi	NULL	NULL

Niladri Biswas

Re: Sql Doubt

rajanandal15-Dec-09 23:28

rajanandal

15-Dec-09 23:28

Thanks for ur timely help...

Nothing is Impossible. Keep always Smiling... Smile | :)

SQL Between Stored Proc

Illegal Operation14-Dec-09 18:08

Illegal Operation

14-Dec-09 18:08

Hi!

Can someone please tell me how would I go about creating a Stored Proc to select records between dates?

I have 2 variables, date1 and date2 I want to create a stored proc as follows:

select * from table between date1 and date2 where the dates are variables in the stored proc.

Illegal Operation

Last Visit: 31-Dec-99 18:00 Last Update: 14-Jun-24 6:33

Refresh

ᐊ Prev 1...700 701 702 703704705 706 707 708 709 Next ᐅ

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.