How to count months for these table with inner join of sql server 2000?

Question

How to count months for these table with inner join of sql server 2000?

raghuldrag

Hall of Fame

Points: 3160
More actions
December 30, 2013 at 10:58 pm

#286680

Hi Friends
My table looks like here
create table estr
(
custo int,
company varchar(20),
inv_no varchar(20),
inv_create_date datetime
)
insert into estr(custo,company,inv_no,inv_create_date) values('10','XXX','PKIN/01/13-14','2013-03-01')
insert into estr(custo,company,inv_no,inv_create_date) values('20','XX1','PKIN/03/13-14','2013-04-01')
insert into estr(custo,company,inv_no,inv_create_date) values('20','XX1','PKIN/05/13-14','2013-04-10')
insert into estr(custo,company,inv_no,inv_create_date) values('30','XXT','PKIN/15/13-14','2013-05-11')
create table cust
(
company varchar(20),
inv_no varchar(20),
product varchar(20),
value varchar(20),
prd_cre_date datetime
)
insert into cust(custo,company,inv_no,product, value,prd_cre_date datetime) values('30','XXT','PKIN/15/13-14','MIlk' ,2000,'2013-04-11')
insert into cust(custo,company,inv_no,product, value,prd_cre_date datetime) values('20','XX1','PKIN/05/13-14','MIlk' ,2000,'2013-04-10')
insert into cust(custo,company,inv_no,product, value,prd_cre_date datetime) values('10','XXX','PKIN/01/13-14','Fruit','500','2013-03-01')
insert into cust(custo,company,inv_no,product, value,prd_cre_date datetime) values('20','XX1','PKIN/03/13-14','MIlk', '400','2013-04-01')
Expecting output
custto product val(sum(value)) no.of.months
10 Fruit 500 1
20 Milk 2400 1
30 Milk 2000 1
how to make procedure or code for calculating dates into months?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

kapil_kk SSC-Insane Points: 21316 More actions · Answer 1

raghuldrag (12/30/2013)
Hi Friends
My table looks like here
create table estr
(
custo int,
company varchar(20),
inv_no int
inv_create_date datetime
)
insert into estr(custo,company,inv_no,inv_create_date) values('10','XXX','PKIN/01/13-14','2013-03-01')
insert into estr(custo,company,inv_no,inv_create_date) values('20','XX1','PKIN/03/13-14','2013-04-01')
insert into estr(custo,company,inv_no,inv_create_date) values('20','XX1','PKIN/05/13-14','2013-04-10')
insert into estr(custo,company,inv_no,inv_create_date) values('30','XXT','PKIN/15/13-14','2013-05-11')
create table cust
(
company varchar(20),
inv_no int,
product varchar(20),
value varchar(20),
prd_cre_date datetime
)
insert into cust(custo,company,inv_no,product, value,prd_cre_date datetime) values('30','XXT','PKIN/15/13-14','MIlk' ,2000,'2013-04-11')
insert into cust(custo,company,inv_no,product, value,prd_cre_date datetime) values('20','XX1','PKIN/05/13-14','MIlk' ,2000,'2013-04-10')
insert into cust(custo,company,inv_no,product, value,prd_cre_date datetime) values('10','XXX','PKIN/01/13-14','Fruit','500','2013-03-01')
insert into cust(custo,company,inv_no,product, value,prd_cre_date datetime) values('20','XX1','PKIN/03/13-14','MIlk', '400','2013-04-01')

I think you made some mistake inn hurry while posting data....;-)

First thing look at the table structure, you defined inv_no column as int in estr table and storing varchar values which is not possible and give you error....

Second thing, in your another table cust you are storing company column values as 30,20 etc.. while in estr table it is stored as 'xx1','xxx' so no relation can be defined based on the values of these column...

Please check the table structure and post correct data....

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Split thread. Please post replies to the original thread here, where you will obtain background information and progress to date.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden