January 28, 2019 at 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?
January 29, 2019 at 6:33 am
ahmed_elbarbary.2010 - Monday, January 28, 2019 9:13 PMProblemCannot 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 ,EXPR2in 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/113482Firstprintcardfooter 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.
January 29, 2019 at 2:27 pm
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.
January 30, 2019 at 8:03 am
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply