473,320 Members | 1,955 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

not coming what i want query from two tables

--------------------------------------------------------------------------------

hi all i am using given below procedure

what i want (i am using sql server2000 with vb.net in window advanceserver2000)

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_information @acc_stmt as varchar(300),@from as smalldatetime,@to as smalldatetime
as
begin
select convert(varchar,c.sales_date,3),c.particular,c.cha llan_no,sum(c.cartoon) as Cartoon,sum(c.total_amount) as Total_amount,convert(varchar,d.debit_date,3),d.par ticular,d.chq_no,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.challan_no,d.debit_dat e,d.particular,d.chq_no,d.amount


union

select convert(varchar,c.sales_date,3),c.particular,c.cha llan_no,sum(c.cartoon) as Cartoon,sum(c.total_amount) as Total_amount,convert(varchar,d.debit_date,3),d.par ticular,d.chq_no,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.challan_no,d.debit_dat e,d.particular,d.chq_no,d.amount


order by convert(varchar,debit_date,3) asc

end
GO
Apr 30 '07 #1
0 938

Sign in to post your reply or Sign up for a free account.

Similar topics

7
by: Phin | last post by:
I need your HELP! I've seen all the posts on using Crystal Reports within vs.net (vb.net) and changing a SQL query at runtime. When I tried to pass in a dataset into the crystal report at...
0
by: Gianfranco | last post by:
Hi I got a problem with 2 tables. I have a table, say A, with x records, coming from a make table query and a table, say B, with y records, coming from another make table query. I need to join...
1
by: Queen's | last post by:
I have a database I'm working with which has tables and reports. The controls in the reports are bound to data, but I'm at a loss to explain where the data is coming from. I have gone so far as to...
4
by: HEATHER CARTER-YOUNG | last post by:
Please help. I have two databases - one I'm designing that will be our in-house data mgmt system (db1) and another that is a federally-mandated system (db2). We must submit data from db2 but don't...
3
by: Hyphessobricon | last post by:
Hallo, Indeed, a count of a query with a group by function gives more records than there are and so for-next structures don't function. How is this to be mended. Anyone? Everyone in fact....
1
by: Good Man | last post by:
Hi there I've noticed some very weird things happening with my current MySQL setup on my XP Laptop, a development machine. For a while, I have been trying to get the MySQL cache to work....
26
by: gswork | last post by:
i hadn't designed a web page from the ground up for about 9 years, then i was asked to do one. I'd dabbled with html and vaigly kept up with some of the developments but other than that i've been...
2
by: =?Utf-8?B?Q2hyaXM=?= | last post by:
How can I run this query against a table in my Access database? I don't know hwo to use it in C#. In VB I would use .Recordset = "some sql statement". How do I do this in C#? //I get a vlaue...
1
by: skbhagour | last post by:
hi all i am using given below procedure what i want (i am using sql server2000 with vb.net in window advanceserver2000) 1.i want records between two given date for particular account...
6
by: jsacrey | last post by:
Hey everybody, got a secnario for ya that I need a bit of help with. Access 97 using linked tables from an SQL Server 2000 machine. I've created a simple query using two tables joined by one...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.