1
Answer

How to add year to membercode already exist on table firstpr

ahmed elbarbary

ahmed elbarbary
6y
612
1

Problem

Cannot add year to this query 

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

    1. Serial  Year    TransactionNo   PaymentDate MySource    MyKey   SubLdgCodeType1 SubLdgCode1 SpecialCode TrxOrderSerial  
    2. 8523    2018    4980    2018-08-16 00:00:00.000 MEM 1-2018-15   7   15  000015/1    8523  
    3. 12840   2018    9150    2018-09-13 00:00:00.000 MEM 1-2018-17   7   17  000017/1    12840  
    4. 13477   2018    11088   2018-09-29 00:00:00.000 MEM 1-2018-32   7   32  000032/1    13477  
    5. 13482   2018    11094   2018-09-29 00:00:00.000 MEM 1-2018-1881 7   38  000038/1    13482  


Firstprintcardfooter table

    1. Serial  TrxYear TrxType BranchCode  CardLineNo  MemberCode  SpecialCode UserName    FooterNotes TransactionNo   PaymentDate PrintFlag  
    2. 1   2018    1   1   1   15  000015/1    ADMIN   GOOD    NULL    NULL    NULL  


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

    1. 12840   2018    9150    2018-09-13 00:00:00.000 MEM 1-2018-17   7   17  000017/1    12840    
    2. 13477   2018    11088   2018-09-29 00:00:00.000 MEM 1-2018-32   7   32  000032/1    13477    
    3. 13482   2018    11094   2018-09-29 00:00:00.000 MEM 1-2018-1881 7   38  000038/1    13482  

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

  1. see first case  
  2.   
  3. vmainmembers  
  4.   
  5. subldgecode1        year  
  6.   
  7. 15                   2018  
  8.   
  9. firstprintcardfooter  
  10.   
  11. membercode          year  
  12.   
  13. 15                  2019  
  14. in this case not exist and it will show one record from view vmainmembers  

in this case not exist and it will show one record from view vmainmembers
Second Case
  1. Second Case  
  2.   
  3. vmainmembers  
  4.   
  5. subldgecode1        year  
  6.   
  7. 15                   2018  
  8.   
  9. firstprintcardfooter  
  10.   
  11. membercode          year  
  12.   
  13. 15                  2018  
  14.   
  15. in this case  exist and view viewmembers  
  16. will not show any record  
  17. first query on this post do that but not year condition added  
  18.   
  19. so that how to add year also?  

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

so that how to add year also?

Answers (1)
Next Recommended Forum