Component not showing "Show Advanced Editor" in menu

Hi,
I've developed a couple of components now and I know I've seen this problem before but I can't remember how to solve it. My component is built, it has it's own (blank at the moment) UI and it seems to work fine except that there is no right click option to look at the advanced editor. The code is almost identical to another component I wrote that works just fine.
Any ideas anyone?
Thanks
Charlie.The component has more than one input. This apparenty silently disables Advanced Editor support.

complicated query

Hi Guys

I Have not been able to solve this problem from quiete a while now.

I am using sql server 2005.

I have got a table which contains these columns - start date, end date and volumes
if the month in the start date is same as that of end date, the volume remains same, else if the months in the two dates are different, then i have to distribute the volume in such a way that some part will go in the first month and the rest in the other month.. i have to somehow calculate (or prorate) the volume according to the no of days in each month

I have to perform a query on this table so that I can group the volumes for different months and different years.

Here is the sample data...

Service Start

Service End

FMIS Code

No of Units

Year

Month

Volumes

01-Oct-00

15-Aug-01

6440

32

?

?

?

01-Oct-00

30-Sep-02

6441

40

?

?

?

01-Oct-02

22-May-03

6440

78

?

?

?

01-Oct-02

23-May-03

6990

87

?

?

?

06-Mar-03

31-Jul-03

6997

102

?

?

?

07-Mar-03

31-Jul-03

6744

3

?

?

?

01-May-03

31-May-03

6440

789

?

?

?

23-Jun-03

31-Aug-03

6447

1000

?

?

?

29-Jun-03

30-Jun-03

6440

981

?

?

?

30-Jun-03

31-Jul-03

6000

50

?

?

?

01-Jul-03

08-Jul-03

6002

54

?

?

?

01-Jul-03

13-Jul-03

6000

562

?

?

?

I have to calculate the columns coloured in blue..

Please help guys!

Thanks

Mita

I don't fully understand the purpose of the year and month columns. Should they relate to the Service Start or Service End or are they a count of the years and months over which the volume is spread?

Presumably you then want to spread the volume over however many months. Should these volumes remain an integer value?

Months and years are not the best units of measure for the reasons you state in your post. I believe weeks is a much better measure (although we all know our managers know better ;) ). Obviously a Quantity spread over three months from Jan to Mar is not the same as the same quantity spread over say Oct to Dec. I'm sure you know this, and a query will not be impossible, but there is going to be either

a) an error in the results or

b) an inconsistency in results over different portions of the year.

Which would you prefer?

|||

Hi Thanks for replying

Well I have been doing this query in access from quiete a long time and am doing it for each individual month.

Therefore I need to worko out a way to make this less complicated. The reason why I included year and month was because when the month and year in start date and end date are not same,then we have to distribute the number of units in such a way for ex if start date is 20th oct and end date is 10th nov then we have to calculate the result by finding out volumes for 20 days in october and (october month) and 10 days for november(november month).

I have pasted the exact coding which I do in Access query for individual month(its a bit scary!)

This one is for Year 2004

Jan 2004

IIf([Service End]<#1/01/2004#,0,IIf([Service Start]>#31/12/2003# And [Service end]<#1/02/2004#,[MyTable]![NoOfUnits],IIf([Service Start]<#1/01/2004# And [Service end]<#1/02/2004#,([Service End]-#31/12/2003#)/([Service End]-[Service Start]+1)*[MyTable]![NoofUnits],IIf([Service Start]<#1/01/2004# And [Service End]>#31/01/2004#,31/(1+[Service End]-[Service Start])*[MyTable]![NoofUnits,IIf([Service Start]>#31/01/2004#,0,(#1/02/2004#-[Service Start])/([Service End]-[Service Start]+1)*[MyTable]![NoofUnits])))))

Feb 2004

IIf([Service End]<#1/02/2004#,0,IIf([Service Start]>#31/01/2004# And [Service end]<#1/03/2004#,[MyTable]![NoofUnits],IIf([Service Start]<#1/02/2004# And [Service end]<#1/03/2004#,([Service End]-#31/01/2004#)/([Service End]-[Service Start]+1)*[MyTable]![NoofUnits],IIf([Service Start]<#1/02/2004# And [Service End]>#28/02/2004#,29/(1+[Service End]-[Service Start])*[MyTable]![NoofUnits],IIf([Service Start]>#28/02/2004#,0,(#1/03/2004#-[Service Start])/([Service End]-[Service Start]+1)*[MyTable]![NoofUnits])))))

and so on for the rest of the months.....

Complicated problem to solve in sql, please help!

please help me solve this problem i have with my database!
i have 2 tables :
table A
--
id
name
table B
--
id
tablea_id
title
now, what i need is this:
return ALL joined rows from table B and A but it should only return maximum
2 rows with the same tablea_id.
looks easy, but NOT :)
thanks.yaniv danan wrote:
> please help me solve this problem i have with my database!
> i have 2 tables :
> table A
> --
> id
> name
>
> table B
> --
> id
> tablea_id
> title
> now, what i need is this:
> return ALL joined rows from table B and A but it should only return maximu
m
> 2 rows with the same tablea_id.
> looks easy, but NOT :)
> thanks.
Please post proper specs: DDL, sample data and show your required
result. Without that information the answers you get may be mostly
untested guesswork. "Maximum 2 rows" for example begs the obvious
question, which 2 rows do you want?
Here are my assumptions about your table structure:
CREATE TABLE A (id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(10) NOT
NULL);
CREATE TABLE B (id INTEGER NOT NULL PRIMARY KEY, tablea_id INTEGER
REFERENCES A (id), title VARCHAR(10) NOT NULL);
Here's one possible answer:
SELECT A.id, A.name, B.id, B.title
FROM A,B
WHERE A.id = B.tablea_id
AND B.id IN
(SELECT TOP 2 id
FROM B AS C
WHERE C.tablea_id = A.id
ORDER BY id) ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Yaniv
create table #a
(
col1 int not null primary key,
col2 char(1)
)
insert into #a values (1,'a')
insert into #a values (2,'b')
insert into #a values (3,'c')
create table #b
(
col1 int not null ,
col2 int,
col3 char(1)
)
insert into #b values (1,100,'a')
insert into #b values (1,100,'b')
insert into #b values (1,100,'b')
insert into #b values (2,500,'a')
insert into #b values (2,22,'b')
insert into #b values (3,5,'a')
insert into #b values (3,66,'b')
insert into #b values (3,66,'b')
select * from #b
where ( select count(*) from #b t
where t.col1=#b.col1 and t.col2=#b.col2
) <=2
UNION ALL
select TOP 2* from #b E
where(
select count(*) from #b
wherecol2 = E.col2
and col2 = E.col2
) >= 3
"yaniv danan" <yaniv@.hacx.org> wrote in message
news:ue$$wyyJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> please help me solve this problem i have with my database!
> i have 2 tables :
> table A
> --
> id
> name
>
> table B
> --
> id
> tablea_id
> title
> now, what i need is this:
> return ALL joined rows from table B and A but it should only return
> maximum 2 rows with the same tablea_id.
> looks easy, but NOT :)
> thanks.
>|||Which two out of several rows? Using TOP without an ORDER BY may yield
unexpected results.
It would help if you explain the reasons for this a bit more.
ML
http://milambda.blogspot.com/|||TRY THIS ........
CREATE TABLE TABLE_A (id int, name varchar(255))
CREATE TABLE TABLE_B (id int, tablea_id int, title varchar(255))
TABLE_A
id name
1 A
2 B
3 C
4 D
id tablea_id title
1 1 ABC
2 1 DEF
3 1 PQR
4 2 XYZ
SELECT B.* FROM TABLE_B B
WHERE B.tablea_id IN
(SELECT TOP 2 A.ID
FROM TABLE_B B2
INNER JOIN TABLE_A A
ON A.ID = B2.tablea_id
ORDER BY A.ID )
ORDER BY B.ID
"yaniv danan" <yaniv@.hacx.org> wrote in message
news:ue$$wyyJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> please help me solve this problem i have with my database!
> i have 2 tables :
> table A
> --
> id
> name
>
> table B
> --
> id
> tablea_id
> title
> now, what i need is this:
> return ALL joined rows from table B and A but it should only return
> maximum 2 rows with the same tablea_id.
> looks easy, but NOT :)
> thanks.
>|||SELECT * FROM tableA as a INNER JOIN tableB as b ON a.id = b.tablea_id
WHERE b.id IN (SELECT TOP 2 id FROM tableB as b2 WHERE b2.tableA_id = a.id)
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"yaniv danan" <yaniv@.hacx.org> wrote in message
news:ue$$wyyJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> please help me solve this problem i have with my database!
> i have 2 tables :
> table A
> --
> id
> name
>
> table B
> --
> id
> tablea_id
> title
> now, what i need is this:
> return ALL joined rows from table B and A but it should only return
maximum
> 2 rows with the same tablea_id.
> looks easy, but NOT :)
> thanks.
>|||thanks for the help, but i think i need to make some things clear (sorry):
table B also have a "AddedDate" column
and the query should return only the TOP 2 newest rows from table B (using
the value in "addeddate").
ofcourse the query most be efficient enough by being very quick on getting
the results.
p.s.
When saying returning rows, i mean returning all the rows from the join
between the two tables :)
thanks, yaniv.
"yaniv danan" <yaniv@.hacx.org> wrote in message
news:ue$$wyyJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> please help me solve this problem i have with my database!
> i have 2 tables :
> table A
> --
> id
> name
>
> table B
> --
> id
> tablea_id
> title
> now, what i need is this:
> return ALL joined rows from table B and A but it should only return
> maximum 2 rows with the same tablea_id.
> looks easy, but NOT :)
> thanks.
>|||yaniv danan wrote:
> thanks for the help, but i think i need to make some things clear (sorry):
> table B also have a "AddedDate" column
> and the query should return only the TOP 2 newest rows from table B (using
> the value in "addeddate").
> ofcourse the query most be efficient enough by being very quick on getting
> the results.
> p.s.
> When saying returning rows, i mean returning all the rows from the join
> between the two tables :)
> thanks, yaniv.
>

>From my previous post:
Please post proper specs: DDL, sample data and show your required
result. Without that information the answers you get may be mostly
untested guesswork.
See: http://www.aspfaq.com/etiquette.asp?id=5006
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Complex query. Can anyone solve this one

Hi
A little complex one
What i really want is that my table contains duplicate
firstname,lastname and dob.
what I need to do is check the two fields datecreated and
datelastupdated and retain the latest record based on
date of these two columns and update the other records
with OLD.
so the result I want is retain row 1 as datelastupdated
is latest for John Li as it is and update the 2 John Li
as John (OLD) Li
and also retain record 5 as datecreated is the latest so
retain this one and update the other one as OLD.
I have about 700 records to do and some are 2 duplicates
and some 3.
How can i write a query/cursor to solve this one.
DDL and DML are posted
CREATE TABLE Employees (
[EmployeeId] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [varchar] (45) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastName] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Dob] [datetime] NOT NULL ,
[DateCreated] [datetime] NOT NULL ,
[DateLastUpdated] [datetime] NULL ,
[EmployerId] [int] NOT NULL
) ON [PRIMARY]
GO
insert into employees
values('John','Li','1971-02-09 00:00:00','2004-12-06
12:20:48.577','2005-02-01 17:21:33.047',111)
insert into employees
values('John','Li','1971-02-09 00:00:00','2004-03-26
12:20:48.577','2004-02-01 17:21:33.047',111)
insert into employees
values('John','Li','1971-02-09 00:00:00','2004-12-10
12:20:48.577','2003-02-01 17:21:33.047',111)
insert into employees
values('Tom','Lan','1979-05-07 00:00:00','2005-03-22
14:00:15.207','2004-04-21 13:20:20.273',111)
insert into employees
values('Tom','Lan','1979-05-07 00:00:00','2004-10-22
14:00:15.207','2004-09-25 13:20:20.273',111)
--list the duplicates
select firstname,lastname,dob,count(*) as count
from employees e
group by firstname,lastname,dob
having count(*)>1
order by count(*) desc
--list the table
select * from employeesHi
Adding a status column...
CREATE TABLE Employees (
[EmployeeId] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [varchar] (45) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastName] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Dob] [datetime] NOT NULL ,
[DateCreated] [datetime] NOT NULL ,
[DateLastUpdated] [datetime] NULL ,
[EmployerId] [int] NOT NULL,
[Status] CHAR(3) NULL
) ON [PRIMARY]
GO
insert into employees( [FirstName],
[LastName],
[Dob],
[DateCreated],
[DateLastUpdated],
[EmployerId] )
values('John','Li','19710209 00:00:00','20041206 12:20:48.577','20050201
17:21:33.047',111)
insert into employees( [FirstName],
[LastName],
[Dob],
[DateCreated],
[DateLastUpdated],
[EmployerId] )
values('John','Li','19710209 00:00:00','20040326 12:20:48.577','20040201
17:21:33.047',111)
insert into employees( [FirstName],
[LastName],
[Dob],
[DateCreated],
[DateLastUpdated],
[EmployerId] )
values('John','Li','19710209 00:00:00','20041210 12:20:48.577','20030201
17:21:33.047',111)
insert into employees( [FirstName],
[LastName],
[Dob],
[DateCreated],
[DateLastUpdated],
[EmployerId] )
values('Tom','Lan','19790507 00:00:00','20050322 14:00:15.207','20040421
13:20:20.273',111)
insert into employees( [FirstName],
[LastName],
[Dob],
[DateCreated],
[DateLastUpdated],
[EmployerId] )
values('Tom','Lan','19790507 00:00:00','20041022 14:00:15.207','20040925
13:20:20.273',111)
--list the duplicates
select firstname,lastname,dob,count(*) as count
from employees e
group by firstname,lastname,dob
having count(*)>1
order by count(*) desc
--list the table
UPDATE e
SET status = 'Old'
FROM employees e
WHERE EXISTS ( SELECT 1 FROM Employees p where p.firstname = e.firstname and
p.lastname = e.lastname and p.dob = e.dob and
e.[DateLastUpdated] < p.[DateLastUpdated] )
select * from employees
This is ignoring datecreated as it seems to be obsolete once it has been on
older record has been updated!
John
"Apok" wrote:

> Hi
> A little complex one
> What i really want is that my table contains duplicate
> firstname,lastname and dob.
> what I need to do is check the two fields datecreated and
> datelastupdated and retain the latest record based on
> date of these two columns and update the other records
> with OLD.
> so the result I want is retain row 1 as datelastupdated
> is latest for John Li as it is and update the 2 John Li
> as John (OLD) Li
> and also retain record 5 as datecreated is the latest so
> retain this one and update the other one as OLD.
> I have about 700 records to do and some are 2 duplicates
> and some 3.
> How can i write a query/cursor to solve this one.
> DDL and DML are posted
> CREATE TABLE Employees (
> [EmployeeId] [int] IDENTITY (1, 1) NOT NULL ,
> [FirstName] [varchar] (45) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [LastName] [varchar] (30) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Dob] [datetime] NOT NULL ,
> [DateCreated] [datetime] NOT NULL ,
> [DateLastUpdated] [datetime] NULL ,
> [EmployerId] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> insert into employees
> values('John','Li','1971-02-09 00:00:00','2004-12-06
> 12:20:48.577','2005-02-01 17:21:33.047',111)
> insert into employees
> values('John','Li','1971-02-09 00:00:00','2004-03-26
> 12:20:48.577','2004-02-01 17:21:33.047',111)
> insert into employees
> values('John','Li','1971-02-09 00:00:00','2004-12-10
> 12:20:48.577','2003-02-01 17:21:33.047',111)
>
> insert into employees
> values('Tom','Lan','1979-05-07 00:00:00','2005-03-22
> 14:00:15.207','2004-04-21 13:20:20.273',111)
>
> insert into employees
> values('Tom','Lan','1979-05-07 00:00:00','2004-10-22
> 14:00:15.207','2004-09-25 13:20:20.273',111)
> --list the duplicates
> select firstname,lastname,dob,count(*) as count
> from employees e
> group by firstname,lastname,dob
> having count(*)>1
> order by count(*) desc
> --list the table
> select * from employees
>|||No But there is record where datecreated is the latest
one
your query does not cover that
I need to compare both the dates and find the latest one
leave that one and update the rest

>--Original Message--
>Hi
>Adding a status column...
>CREATE TABLE Employees (
> [EmployeeId] [int] IDENTITY (1, 1) NOT NULL ,
> [FirstName] [varchar] (45) COLLATE
>SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [LastName] [varchar] (30) COLLATE
>SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Dob] [datetime] NOT NULL ,
> [DateCreated] [datetime] NOT NULL ,
> [DateLastUpdated] [datetime] NULL ,
> [EmployerId] [int] NOT NULL,
> [Status] CHAR(3) NULL
> ) ON [PRIMARY]
>GO
>
>insert into employees( [FirstName],
>[LastName],
>[Dob],
>[DateCreated],
>[DateLastUpdated],
>[EmployerId] )
>values('John','Li','19710209 00:00:00','20041206
12:20:48.577','20050201
>17:21:33.047',111)
>insert into employees( [FirstName],
>[LastName],
>[Dob],
>[DateCreated],
>[DateLastUpdated],
>[EmployerId] )
>values('John','Li','19710209 00:00:00','20040326
12:20:48.577','20040201
>17:21:33.047',111)
>insert into employees( [FirstName],
>[LastName],
>[Dob],
>[DateCreated],
>[DateLastUpdated],
>[EmployerId] )
>values('John','Li','19710209 00:00:00','20041210
12:20:48.577','20030201
>17:21:33.047',111)
>
>insert into employees( [FirstName],
>[LastName],
>[Dob],
>[DateCreated],
>[DateLastUpdated],
>[EmployerId] )
>values('Tom','Lan','19790507 00:00:00','20050322
14:00:15.207','20040421
>13:20:20.273',111)
>
>insert into employees( [FirstName],
>[LastName],
>[Dob],
>[DateCreated],
>[DateLastUpdated],
>[EmployerId] )
>values('Tom','Lan','19790507 00:00:00','20041022
14:00:15.207','20040925
>13:20:20.273',111)
>--list the duplicates
>select firstname,lastname,dob,count(*) as count
>from employees e
>group by firstname,lastname,dob
>having count(*)>1
>order by count(*) desc
>--list the table
>UPDATE e
>SET status = 'Old'
>FROM employees e
>WHERE EXISTS ( SELECT 1 FROM Employees p where
p.firstname = e.firstname and
>p.lastname = e.lastname and p.dob = e.dob and
>e.[DateLastUpdated] < p.[DateLastUpdated] )
>select * from employees
>This is ignoring datecreated as it seems to be obsolete
once it has been on
>older record has been updated!
>John
>"Apok" wrote:
>
and
datelastupdated
Li
so
duplicates
>.
>|||Apok,
It's not clear what you mean by "latest record based on
date of these two columns." But here's a try:
update employees set
Status = 'Old'
where exists (
select * from employees as Ecopy
where Ecopy.FirstName = employees.FirstName
and Ecopy.LastName = employees.LastName
and (
(Ecopy.DateCreated > employees.DateCreated
and Ecopy.DateCreated > employees.DateLastUpdated)
or
(Ecopy.DateLastUpdated > employees.DateCreated
and Ecopy.DateLastUpdated > employees.DateLastUpdated)
)
)
Steve Kass
Drew University
Apok wrote:

>Hi
>A little complex one
>What i really want is that my table contains duplicate
>firstname,lastname and dob.
>what I need to do is check the two fields datecreated and
>datelastupdated and retain the latest record based on
>date of these two columns and update the other records
>with OLD.
>so the result I want is retain row 1 as datelastupdated
>is latest for John Li as it is and update the 2 John Li
>as John (OLD) Li
>and also retain record 5 as datecreated is the latest so
>retain this one and update the other one as OLD.
>I have about 700 records to do and some are 2 duplicates
>and some 3.
>How can i write a query/cursor to solve this one.
>DDL and DML are posted
>CREATE TABLE Employees (
> [EmployeeId] [int] IDENTITY (1, 1) NOT NULL ,
> [FirstName] [varchar] (45) COLLATE
>SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [LastName] [varchar] (30) COLLATE
>SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Dob] [datetime] NOT NULL ,
> [DateCreated] [datetime] NOT NULL ,
> [DateLastUpdated] [datetime] NULL ,
> [EmployerId] [int] NOT NULL
> ) ON [PRIMARY]
>GO
>
>insert into employees
>values('John','Li','1971-02-09 00:00:00','2004-12-06
>12:20:48.577','2005-02-01 17:21:33.047',111)
>insert into employees
>values('John','Li','1971-02-09 00:00:00','2004-03-26
>12:20:48.577','2004-02-01 17:21:33.047',111)
>insert into employees
>values('John','Li','1971-02-09 00:00:00','2004-12-10
>12:20:48.577','2003-02-01 17:21:33.047',111)
>
>insert into employees
>values('Tom','Lan','1979-05-07 00:00:00','2005-03-22
>14:00:15.207','2004-04-21 13:20:20.273',111)
>
>insert into employees
>values('Tom','Lan','1979-05-07 00:00:00','2004-10-22
>14:00:15.207','2004-09-25 13:20:20.273',111)
>--list the duplicates
>select firstname,lastname,dob,count(*) as count
>from employees e
>group by firstname,lastname,dob
>having count(*)>1
>order by count(*) desc
>--list the table
>select * from employees
>
>