hi all i am using given below procedure
what i want (i am using sql server2000 with vb.net in window advanceserver20 00)
1.i want records between two given date for particular account statement.
2.if record is present in both tables than all the colums of query has value.
3.if not any side than corresponding table columns fill with null and other with
value.
4.i want to group records based on challan no and acc_stmt for same date in credit_details.
5.i want to group records based on debit date and acc_stmt for same date in debit_details.
6.there is two date columns in query sales date and debit date
and i require this format of date dd/mm/yyyy
i am showing this result in crystal report.
this given procedure works well till 4 step.
so please help me solve this query.
CREATE PROCEDURE credit_debit_in formation @acc_stmt as varchar(300),@f rom as smalldatetime,@ to as smalldatetime
as
begin
select convert(varchar ,c.sales_date,3 ),c.particular, c.cha llan_no,sum(c.c artoon) as Cartoon,sum(c.t otal_amount) as Total_amount,co nvert(varchar,d .debit_date,3), d.par ticular,d.chq_n o,d.amount from
credit_details c left outer join debit_details d
on(c.acc_stmt=d .acc_stmt and c.sales_date=d. debit_date)
where c.acc_stmt=@acc _stmt and
sales_date between @from and @to
group by c.sales_date,c. particular,c.ch allan_no,d.debi t_dat e,d.particular, d.chq_no,d.amou nt
union
select convert(varchar ,c.sales_date,3 ),c.particular, c.cha llan_no,sum(c.c artoon) as Cartoon,sum(c.t otal_amount) as Total_amount,co nvert(varchar,d .debit_date,3), d.par ticular,d.chq_n o,d.amount from
credit_details c right outer join debit_details d
on(c.acc_stmt=d .acc_stmt and c.sales_date=d. debit_date)
where d.acc_stmt=@acc _stmt and
debit_date between @from and @to
group by c.sales_date,c. particular,c.ch allan_no,d.debi t_dat e,d.particular, d.chq_no,d.amou nt
order by convert(varchar ,debit_date,3) asc
end
GO
what i want (i am using sql server2000 with vb.net in window advanceserver20 00)
1.i want records between two given date for particular account statement.
2.if record is present in both tables than all the colums of query has value.
3.if not any side than corresponding table columns fill with null and other with
value.
4.i want to group records based on challan no and acc_stmt for same date in credit_details.
5.i want to group records based on debit date and acc_stmt for same date in debit_details.
6.there is two date columns in query sales date and debit date
and i require this format of date dd/mm/yyyy
i am showing this result in crystal report.
this given procedure works well till 4 step.
so please help me solve this query.
CREATE PROCEDURE credit_debit_in formation @acc_stmt as varchar(300),@f rom as smalldatetime,@ to as smalldatetime
as
begin
select convert(varchar ,c.sales_date,3 ),c.particular, c.cha llan_no,sum(c.c artoon) as Cartoon,sum(c.t otal_amount) as Total_amount,co nvert(varchar,d .debit_date,3), d.par ticular,d.chq_n o,d.amount from
credit_details c left outer join debit_details d
on(c.acc_stmt=d .acc_stmt and c.sales_date=d. debit_date)
where c.acc_stmt=@acc _stmt and
sales_date between @from and @to
group by c.sales_date,c. particular,c.ch allan_no,d.debi t_dat e,d.particular, d.chq_no,d.amou nt
union
select convert(varchar ,c.sales_date,3 ),c.particular, c.cha llan_no,sum(c.c artoon) as Cartoon,sum(c.t otal_amount) as Total_amount,co nvert(varchar,d .debit_date,3), d.par ticular,d.chq_n o,d.amount from
credit_details c right outer join debit_details d
on(c.acc_stmt=d .acc_stmt and c.sales_date=d. debit_date)
where d.acc_stmt=@acc _stmt and
debit_date between @from and @to
group by c.sales_date,c. particular,c.ch allan_no,d.debi t_dat e,d.particular, d.chq_no,d.amou nt
order by convert(varchar ,debit_date,3) asc
end
GO