How to add year to membercode already exist on table firstprintcardfooter

  • ahmed_elbarbary.2010

    SSCrazy

    Points: 2599

    Problem

    Cannot add year to this query 

    SELECT FirstPrintCardFooter.FooterNotes,FirstPrintCardFooter.PrintFlag, dbo.VMainMembers.TransactionNo,dbo.VMainMembers.PaymentDate, dbo.Members.MemberCode, dbo.Members.SpecialCode, dbo.Members.Name,CAST(CAST(LEFT(dbo.Members.SpecialCode, CHARINDEX('/', dbo.Members.SpecialCode + '/') - 1) AS nvarchar) AS int) AS expr1 ,CAST(CAST(substring(dbo.Members.SpecialCode, CHARINDEX('/', dbo.Members.SpecialCode)+1 , len(dbo.Members.SpecialCode) - CHARINDEX('/', dbo.Members.SpecialCode)) AS nvarchar) AS int) AS EXPR2    FROM    dbo.Members LEFT JOIN         dbo.VMainMembers ON SUBSTRING(dbo.Members.SpecialCode, 0, CHARINDEX('/', dbo.Members.SpecialCode, 0))=SUBSTRING(dbo.VMainMembers.SpecialCode, 0, CHARINDEX('/', dbo.VMainMembers.SpecialCode, 0)) LEFT JOIN FirstPrintCardFooter ON MEMBERS.MemberCode = FirstPrintCardFooter.MemberCode WHERE FirstPrintCardFooter.MemberCode IS NULL  and dbo.VMainMembers.TransactionNo is not null and VMainMembers.Year=2018 order by expr1 ,EXPR2

    in query above i get data from vmainmembers view then display it in case of not exist on table FirstprintcarsFooter 

    to summarize problem i need to add year also on this condition

    WHERE FirstPrintCardFooter.MemberCode IS NULL and check also year in table FirstprintcardFooter must be same as vmainmembers

    Example

    VmainMemvers view and subldgecode1  represent membercode

    SerialYearTransactionNoPaymentDateMySourceMyKeySubLdgCodeType1SubLdgCode1SpecialCodeTrxOrderSerial8523201849802018-08-16 00:00:00.000MEM1-2018-15715000015/1852312840201891502018-09-13 00:00:00.000MEM1-2018-17717000017/112840134772018110882018-09-29 00:00:00.000MEM1-2018-32732000032/113477134822018110942018-09-29 00:00:00.000MEM1-2018-1881738000038/113482

    Firstprintcardfooter table

    SerialTrxYearTrxTypeBranchCodeCardLineNoMemberCodeSpecialCodeUserNameFooterNotesTransactionNoPaymentDatePrintFlag1201811115000015/1ADMINGOODNULLNULLNULL

    Result of first query written in this post above as below :

    12840201891502018-09-13 00:00:00.000MEM1-2018-17717000017/112840134772018110882018-09-29 00:00:00.000MEM1-2018-32732000032/113477134822018110942018-09-29 00:00:00.000MEM1-2018-1881738000038/113482

    but main problem it not check year also 

    actually i need to check member exist or not on table firstprintcardfooter and year also on firstquery on this post 

    meaning if subldgecode1 for view vmainmembers not same as membercode for firstprintcardsfooter and year exist on view vmainmembers not same as year on firstprintcardfooter then not display

    see first case

    vmainmemberssubldgecode1   year15       2018firstprintcardfootermembercode    year15      2019

    in this case not exist and it will show one record from view vmainmembers

    Second Case

    vmainmemberssubldgecode1   year15       2018firstprintcardfootermembercode    year15      2018in this case exist and view viewmemberswill not show any record

    first query on this post do that but not year condition added

    so that how to add year also?

  • Jonathan AC Roberts

    SSCoach

    Points: 16994

    ahmed_elbarbary.2010 - Monday, January 28, 2019 9:13 PM

    Problem

    Cannot add year to this query 

    SELECT FirstPrintCardFooter.FooterNotes,FirstPrintCardFooter.PrintFlag, dbo.VMainMembers.TransactionNo,dbo.VMainMembers.PaymentDate, dbo.Members.MemberCode, dbo.Members.SpecialCode, dbo.Members.Name,CAST(CAST(LEFT(dbo.Members.SpecialCode, CHARINDEX('/', dbo.Members.SpecialCode + '/') - 1) AS nvarchar) AS int) AS expr1 ,CAST(CAST(substring(dbo.Members.SpecialCode, CHARINDEX('/', dbo.Members.SpecialCode)+1 , len(dbo.Members.SpecialCode) - CHARINDEX('/', dbo.Members.SpecialCode)) AS nvarchar) AS int) AS EXPR2    FROM    dbo.Members LEFT JOIN         dbo.VMainMembers ON SUBSTRING(dbo.Members.SpecialCode, 0, CHARINDEX('/', dbo.Members.SpecialCode, 0))=SUBSTRING(dbo.VMainMembers.SpecialCode, 0, CHARINDEX('/', dbo.VMainMembers.SpecialCode, 0)) LEFT JOIN FirstPrintCardFooter ON MEMBERS.MemberCode = FirstPrintCardFooter.MemberCode WHERE FirstPrintCardFooter.MemberCode IS NULL  and dbo.VMainMembers.TransactionNo is not null and VMainMembers.Year=2018 order by expr1 ,EXPR2

    in query above i get data from vmainmembers view then display it in case of not exist on table FirstprintcarsFooter 

    to summarize problem i need to add year also on this condition

    WHERE FirstPrintCardFooter.MemberCode IS NULL and check also year in table FirstprintcardFooter must be same as vmainmembers

    Example

    VmainMemvers view and subldgecode1  represent membercode

    SerialYearTransactionNoPaymentDateMySourceMyKeySubLdgCodeType1SubLdgCode1SpecialCodeTrxOrderSerial8523201849802018-08-16 00:00:00.000MEM1-2018-15715000015/1852312840201891502018-09-13 00:00:00.000MEM1-2018-17717000017/112840134772018110882018-09-29 00:00:00.000MEM1-2018-32732000032/113477134822018110942018-09-29 00:00:00.000MEM1-2018-1881738000038/113482

    Firstprintcardfooter table

    SerialTrxYearTrxTypeBranchCodeCardLineNoMemberCodeSpecialCodeUserNameFooterNotesTransactionNoPaymentDatePrintFlag1201811115000015/1ADMINGOODNULLNULLNULL

    Result of first query written in this post above as below :

    12840201891502018-09-13 00:00:00.000MEM1-2018-17717000017/112840134772018110882018-09-29 00:00:00.000MEM1-2018-32732000032/113477134822018110942018-09-29 00:00:00.000MEM1-2018-1881738000038/113482

    but main problem it not check year also 

    actually i need to check member exist or not on table firstprintcardfooter and year also on firstquery on this post 

    meaning if subldgecode1 for view vmainmembers not same as membercode for firstprintcardsfooter and year exist on view vmainmembers not same as year on firstprintcardfooter then not display

    see first case

    vmainmemberssubldgecode1   year15       2018firstprintcardfootermembercode    year15      2019

    in this case not exist and it will show one record from view vmainmembers

    Second Case

    vmainmemberssubldgecode1   year15       2018firstprintcardfootermembercode    year15      2018in this case exist and view viewmemberswill not show any record

    first query on this post do that but not year condition added

    so that how to add year also?

    The problem is the space and return keys aren't working properly on your keyboard. You need to throw it away and buy a new one.

  • doug.brown

    SSCertifiable

    Points: 5526

    Here is the query with some formatting:
    SELECT
        firstprintcardfooter.footernotes,
        firstprintcardfooter.printflag,
        dbo.vmainmembers.transactionno,
        dbo.vmainmembers.paymentdate,
        dbo.members.membercode,
        dbo.members.specialcode,
        dbo.members.NAME,
        Cast(Cast(LEFT(dbo.members.specialcode, Charindex('/', dbo.members.specialcode + '/') - 1) AS NVARCHAR) AS INT) AS expr1,
        Cast(Cast(Substring(dbo.members.specialcode, Charindex('/', dbo.members.specialcode) + 1,
                Len(dbo.members.specialcode) - Charindex('/', dbo.members.specialcode)) AS NVARCHAR) AS INT) AS EXPR2
    FROM 
        dbo.members
        LEFT JOIN dbo.vmainmembers ON Substring(dbo.members.specialcode, 0, Charindex('/', dbo.members.specialcode, 0))
                                        = Substring(dbo.vmainmembers.specialcode, 0, Charindex('/', dbo.vmainmembers.specialcode, 0))
        LEFT JOIN firstprintcardfooter ON members.membercode = firstprintcardfooter.membercode
    WHERE
        firstprintcardfooter.membercode IS NULL
        AND dbo.vmainmembers.transactionno IS NOT NULL
        AND vmainmembers.year = 2018
    ORDER BY
        expr1,
        expr2

    One thing to note right off - the last two conditions in your WHERE clause turn the vmainmembers join into an INNER join.  If that is supposed to be a LEFT JOIN, you'll want to move those conditions to the join criteria.  Also I would recommend using table aliases to increase readability, particularly given your long table names.  Beyond that, I confess I don't understand the exact issue you're having. 

  • ninjamy

    SSC Enthusiast

    Points: 117

    I * think* you are saying you only want vmainmembers to return if they do not exist in the firstprincardfooter for the same year on that vmainmember record. If so, adding the dates to the Left Join on firstprintcardfooter would make sure that you are taking the that into account.


    LEFT JOIN firstprintcardfooter ON members.membercode = firstprintcardfooter.membercode AND firstprintcardfooter.year = vmainmembers.year

Viewing 4 posts - 1 through 4 (of 4 total)

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