January 19, 2011 at 3:43 am
Hi,
please provide me an example to finding out how to get such type of records i.e.
''The effective date of the first record of each organization should be at least as early as its first officer record''
Please suggest me how to get such type of records as well if posible please provide me an example it means I will Impliment this on my logic.
My SQL is as below aslo I request you to please modified my sql as per your suggestions...
SELECT cixo.ixo_rlt_code Role,
coe.org_club_id_ext org_ID,
cie.ind_membership_id_ext Member_ID ,
cixo.ixo_start_date Start_Date,
cixo.ixo_end_date End_Date,
ROW_NUMBER () OVER (PARTITION BY cie.ind_membership_id_ext,
coe.org_club_id_ext
ORDER BY cie.ind_membership_id_ext,
coe.org_club_id_ext,cixo.ixo_rlt_code,
cixo.ixo_start_date) sequence_nbr
FROM co_individual_x_organization AS cixo
JOIN co_individual AS ci ON ci.ind_cst_key = cixo.ixo_ind_cst_key
JOIN co_individual_ext AS cie ON cie.ind_cst_key_ext = cixo.ixo_ind_cst_key
JOIN co_organization_ext AS coe ON coe.org_cst_key_ext = cixo.ixo_org_cst_key
JOIN co_organization as co ON co.org_cst_key = coe.org_cst_key_ext
AND (cixo.ixo_start_date>co.org_date_founded AND cixo.ixo_end_date>co.org_date_founded)
WHERE ((cixo.ixo_rlt_code IN('Club President',
'Club Secretary',
'Club Executive Secretary/Director',
'District Membership Development Chair')
AND (cixo.ixo_end_date > '6/30/2004' OR cixo.ixo_end_date IS NULL))
OR cixo.ixo_rlt_code = 'District Governor (DG)')
AND cixo.ixo_start_date <> cixo.ixo_end_date
AND cixo.ixo_delete_flag = 0
AND ci.ind_delete_flag = 0
ORDER BY Member_ID,org_ID,Role,sequence_nbr
Regards,
Kiran R
January 19, 2011 at 4:09 am
Please post table definitions, sample data and expected results as per http://qa.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2011 at 5:20 am
Hi,
Request you to please advise for my above SQL script and give me the solution to finding out my above concenr.
Regards,
Kiran R
January 19, 2011 at 5:29 am
GilaMonster (1/19/2011)
Please post table definitions, sample data and expected results as per http://qa.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2011 at 5:43 am
Hi,
please provide me any solution regarding my above concern it means i can impliment that logic in my sql
Regards,
Kiran
January 19, 2011 at 5:52 am
Please read over the article I've referenced (twice). Please post table definitions (as create table statements), som sample data for us to work with and the expected results so we can test our solutions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2011 at 6:02 am
Hi,
from that above sql i took some records for your testing purpose.
Roleorg_IDMember_IDStart_DateEnd_DateOrg_Found_Datesequence_nbr
Club Secretary448531112006-07-01 00:00:002007-06-30 00:00:001935-03-04 00:00:00.0001
Club Secretary448531112007-07-01 00:00:002008-06-30 00:00:001935-03-04 00:00:00.0002
Club Secretary448531112009-07-01 00:00:002010-06-30 00:00:001935-03-04 00:00:00.0003
Club Secretary448531112010-07-01 00:00:002011-06-30 00:00:001935-03-04 00:00:00.0004.
I need to modify the organization dates so that the position doesn't error out. The effective date of the first record of each organization should be at least as early as its first officer record.
Let me know in case of any information need for this.
Regards,
Kiran R
January 19, 2011 at 6:11 am
I'm assuming you didn't read the article. Please do.
Table definition please - create table (....),
Sample data in a form that it can be easily inserted. INSERT INTO ....
Not what your query currently returns, sample data from the base table.
What should the data look like afterwards?
You haven't explained what the 'first officer record' is. What do you mean by 'position error out'?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2011 at 6:29 am
Hi,
Sorry about this..
please find the below description below if this will work.
please consider District as a "org_ID"..
So District 1050 has a charter date (org_date_founded) of 1 January 1970. The earliest officer date (which could be several records, it doesn’t matter) is 1 July 1939.
This is what the district looks like now in org (only a few fields shown):
org_keyorg_level_nameorg_idorg_seq_nbrorg_dist_founded_dtorg_zone_idorg_rec_eff_dtorg_rec_end_dt
172404District105011/1/19701/1/19706/30/1995
178281District105021/1/1970177/1/19956/6/2079
This is what I think it should look like, taking early officers into account:
org_keyorg_level_nameorg_idorg_seq_nbrorg_dist_founded_dtorg_zone_idorg_rec_eff_dtorg_rec_end_dt
172404District105011/1/19707/1/19396/30/1995
178281District105021/1/1970177/1/19956/6/2079
Regards,
Kiran R
January 19, 2011 at 6:33 am
Wow you're either not able to understand english or you're just trying to piss us off.
READ THE FREAKING ARTICLE that Gail refferred you 3 times to already.
Post the scripts we need to create the tables and data and also tell us exactly the required output.
As long as you DON'T do it you won't get any help.
January 19, 2011 at 6:43 am
GilaMonster (1/19/2011)
Table definition please - create table (....),Sample data in a form that it can be easily inserted. INSERT INTO ....
Not what your query currently returns, sample data from the base table.
If that info is available when I get home, I'll try to help. If it's not, I'm not spending hours of my evening trying to understand, format data for insert, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2011 at 8:21 am
Hi,
please find the below script with samle data this is coming now
CREATE TABLE tmp
(role nvarchar(20),
org_ID av_integer,
Member_ID [av_integer] NULL,
Start_Date [av_date_small] NULL,
End_Date [av_date_small] NULL,
Org_Founded_Date [av_date] NULL,
sequence_nbr int)
go
insert into tmp values ('Club Secretary',4485,172404,'1970-01-01 00:00:00','1995-06-30 00:00:00','1970-01-01 00:00:00.000',1)
insert into tmp values ('Club Secretary',4485,178281,'1995-07-01 00:00:00','2079-06-06 00:00:00','1970-01-01 00:00:00.000',2)
go
and please find the below script with sample data which I want
CREATE TABLE tmp2
(role nvarchar(20),
org_ID av_integer,
Member_ID [av_integer] NULL,
Start_Date [av_date_small] NULL,
End_Date [av_date_small] NULL,
Org_Founded_Date [av_date] NULL,
sequence_nbr int)
go
insert into tmp2 values ('Club Secretary',4485,172404,'1939-07-01 00:00:00','1995-06-30 00:00:00','1970-01-01 00:00:00.000',1)
insert into tmp2 values ('Club Secretary',4485,178281,'1995-07-01 00:00:00','2079-06-06 00:00:00','1970-01-01 00:00:00.000',2)
go
'Please note that start date'
The founded date can stay the same, but the start date for the organization should be earlier.
I dont know what would be the earlier start date for that organization I have to find out from my above SQL script,this is my concern.
please provide me solution
Regards,
Kiran
January 19, 2011 at 8:33 am
You didn't test any of that, did you? hint: the create table statements are going to fail with invalid data type errors. (if they don't on your database, you have user defined data types that we don't)
Those aren't your source tables, they're based off the query that you listed in the first post.
I'm asking for the ACTUAL BASE table definitions and data for the ACTUAL BASE tables. Not what your query as currently written produces. I don't want to write queries based on what your query returns, I want to write queries based on the actual tables that you have, using your query as a starting point.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2011 at 8:33 am
Where do you get the 1935 date from the tmp2 table? I don't see that anywhere in the input.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply