SQL Query

  • 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.

  • 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/

  • 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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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