August 9, 2013 at 9:20 am
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.
August 9, 2013 at 9:52 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 9, 2013 at 9:28 pm
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
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply