SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Query


SQL Query

Author
Message
sundayose
sundayose
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 16
Dear members I have a query below;

use DBMUST
select
[dbo].[tbl_VOUCHER_CAPTURE].dt_VOUCHER_ISSUE_DATE AS Sales_Date,
[dbo].[tbl_VOUCHER_CAPTURE_DETAIL].str_BARCODE AS Voucher,
[dbo].[tbl_SALES_TEAM_MASTER].str_SALES_TEAM_NAME AS Team,
[dbo].[tbl_SALES_EXECUTIVE_MASTER].[str_SALES_EXECUTIVE_NAME] AS BCC,
[dbo].[tbl_DISTRIBUTOR_MASTER].[str_DISTRIBUTOR_NAME]AS CBD,
[dbo].[tbl_DISTRICT_MASTER].str_DISTRICT_NAME AS CBD_District,
[dbo].[tbl_VOUCHER_ISSUE].[dt_VOUCHER_ISSUE_DATE] AS Issuance_Date

from [dbo].[tbl_VOUCHER_CAPTURE]

left Join [dbo].[tbl_VOUCHER_CAPTURE_DETAIL]
on [dbo].[tbl_VOUCHER_CAPTURE].int_VOUCHER_CAPTURE_ID = [dbo].[tbl_VOUCHER_CAPTURE_DETAIL].int_VOUCHER_CAPTURE_ID
Left Join [dbo].[tbl_SALES_EXECUTIVE_MASTER]
on [dbo].[tbl_VOUCHER_CAPTURE].int_SALES_EXECUTIVE_ID = [dbo].[tbl_SALES_EXECUTIVE_MASTER].int_SALES_EXECUTIVE_ID
Left Join [dbo].[tbl_DISTRIBUTOR_MASTER]
on [dbo].[tbl_VOUCHER_CAPTURE].int_DISTRIBUTOR_ID = [dbo].[tbl_DISTRIBUTOR_MASTER].int_DISTRIBUTOR_ID
Left Join [dbo].[tbl_VOUCHER_ISSUE]
On [dbo].[tbl_VOUCHER_CAPTURE_DETAIL].[int_VOUCHER_ISSUE_ID] = [dbo].[tbl_VOUCHER_ISSUE].[int_VOUCHER_ISSUE_ID]
Left Join [dbo].[tbl_VOUCHER_ISSUE_DETAIL]
On [dbo].[tbl_VOUCHER_CAPTURE_DETAIL].int_VOUCHER_ISSUE_DETAIL_ID = [dbo].[tbl_VOUCHER_ISSUE_DETAIL].int_VOUCHER_ISSUE_DETAIL_ID
Left Join [dbo].[tbl_SALES_TEAM_MASTER]
ON [dbo].[tbl_VOUCHER_ISSUE].int_SALES_TEAM_ID = [dbo].[tbl_SALES_TEAM_MASTER].int_SALES_TEAM_ID
Left Join [dbo].[tbl_DISTRICT_MASTER]
ON [dbo].[tbl_DISTRIBUTOR_MASTER].int_DISTRICT_ID = [dbo].[tbl_DISTRICT_MASTER].int_DISTRICT_ID

Where
[dbo].[tbl_VOUCHER_CAPTURE_DETAIL].str_BARCODE Like 'FPUG%'

when I run this code I get an output but for some vouchers, the CB and District output are wrong.
Can some one advise me where am i am going with my query.

Thanks.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25827 Visits: 17509
sundayose (8/9/2013)
Dear members I have a query below;

use DBMUST
select
[dbo].[tbl_VOUCHER_CAPTURE].dt_VOUCHER_ISSUE_DATE AS Sales_Date,
[dbo].[tbl_VOUCHER_CAPTURE_DETAIL].str_BARCODE AS Voucher,
[dbo].[tbl_SALES_TEAM_MASTER].str_SALES_TEAM_NAME AS Team,
[dbo].[tbl_SALES_EXECUTIVE_MASTER].[str_SALES_EXECUTIVE_NAME] AS BCC,
[dbo].[tbl_DISTRIBUTOR_MASTER].[str_DISTRIBUTOR_NAME]AS CBD,
[dbo].[tbl_DISTRICT_MASTER].str_DISTRICT_NAME AS CBD_District,
[dbo].[tbl_VOUCHER_ISSUE].[dt_VOUCHER_ISSUE_DATE] AS Issuance_Date

from [dbo].[tbl_VOUCHER_CAPTURE]

left Join [dbo].[tbl_VOUCHER_CAPTURE_DETAIL]
on [dbo].[tbl_VOUCHER_CAPTURE].int_VOUCHER_CAPTURE_ID = [dbo].[tbl_VOUCHER_CAPTURE_DETAIL].int_VOUCHER_CAPTURE_ID
Left Join [dbo].[tbl_SALES_EXECUTIVE_MASTER]
on [dbo].[tbl_VOUCHER_CAPTURE].int_SALES_EXECUTIVE_ID = [dbo].[tbl_SALES_EXECUTIVE_MASTER].int_SALES_EXECUTIVE_ID
Left Join [dbo].[tbl_DISTRIBUTOR_MASTER]
on [dbo].[tbl_VOUCHER_CAPTURE].int_DISTRIBUTOR_ID = [dbo].[tbl_DISTRIBUTOR_MASTER].int_DISTRIBUTOR_ID
Left Join [dbo].[tbl_VOUCHER_ISSUE]
On [dbo].[tbl_VOUCHER_CAPTURE_DETAIL].[int_VOUCHER_ISSUE_ID] = [dbo].[tbl_VOUCHER_ISSUE].[int_VOUCHER_ISSUE_ID]
Left Join [dbo].[tbl_VOUCHER_ISSUE_DETAIL]
On [dbo].[tbl_VOUCHER_CAPTURE_DETAIL].int_VOUCHER_ISSUE_DETAIL_ID = [dbo].[tbl_VOUCHER_ISSUE_DETAIL].int_VOUCHER_ISSUE_DETAIL_ID
Left Join [dbo].[tbl_SALES_TEAM_MASTER]
ON [dbo].[tbl_VOUCHER_ISSUE].int_SALES_TEAM_ID = [dbo].[tbl_SALES_TEAM_MASTER].int_SALES_TEAM_ID
Left Join [dbo].[tbl_DISTRICT_MASTER]
ON [dbo].[tbl_DISTRIBUTOR_MASTER].int_DISTRICT_ID = [dbo].[tbl_DISTRICT_MASTER].int_DISTRICT_ID

Where
[dbo].[tbl_VOUCHER_CAPTURE_DETAIL].str_BARCODE Like 'FPUG%'

when I run this code I get an output but for some vouchers, the CB and District output are wrong.
Can some one advise me where am i am going with my query.

Thanks.


I see that you are pretty new around here. It is impossible for anybody to have any clue what is wrong with your query based on what you posted. We can't see your screen, we have no idea what your table structures are like, we don't know the business or the process you are working on and we have no idea what values would make "CB and District output are wrong" become "right".

In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

As a side note, you really should use aliases in your queries. It will make this a LOT easier to read. IIRC aliases are going to be required at some point in the future instead of 3 part naming convention in the select list.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84721 Visits: 41069
sundayose (8/9/2013)
when I run this code I get an output but for some vouchers, the CB and District output are wrong.


HOW are they wrong? How did you determine they are wrong? What's the matter with them?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search