Databases
StackOverflow2013
Postsdbo
POSelecting specific Data per month
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSelecting specific Data per month
    primarykey
    data
    text
    <p>So i have a table that would determine the <strong>Region Code</strong> of a <strong>Branch</strong> Depending on the <strong>Month</strong>,<br> Lets Say from <code>January to february it's Region code would be 1,</code> <code>from February to March it would be 2</code> and <code>for the month of april upto date would be 3</code></p> <p>so here is the sample look of the table <img src="https://i.stack.imgur.com/1Oh0c.png" alt="enter image description here"></p> <p>i have a code that gets the data from a table, but what i want to achieve is that if the LoanDate of the selected Data is within the Dates above (between fld_Datefrom and fld_Dateto) it would use the fld_BranchRegion that is indicated like above above. (EG. the Loandate of the date is <code>2013-01-12 00:00:00</code> it would use the RegionCode <strong>4A</strong> as indicated above and if the data is <code>2013-02-04 00:00:00</code> it would use the Region code <strong>3</strong></p> <p>here is the code i use</p> <pre><code>SELECT TE.LOAN ,bp.REGION ,BP.ID ,TE.AMOUNT ,te.ID FROM #TrackExpired TE inner join Transactions.TBLMAIN PM on TE.ID = PM.ID inner join #track BP on BP.ID=cast(TE.ID/1000000000000 as decimal(38,0)) WHERE ((cast(TE.EXPIRATION as date) &lt; cast(TE.newloandate as date)) OR(TE.NewLoanDate is null and (cast(TE.EXPIRATION as date) &lt; cast(PM.REDEEMED as date))) or ((TE.NewLoanDate is null and PM.REDEEMED is null) and (PM.STATUS = 7 or PM.STATUS = 5)) ) </code></pre> <p>The problem with this is that it generates duplicate values so i have 3 occurances of the dates in the <strong>#track</strong> table the number of the Data is also outputted 3 times with different Region Code!!</p> <p><strong>Instead of outputting them i would like to achive on selecting the Region Code from **#track</strong> Based on the loan date of the Data.**</p> <p>i just want to achieve that instead of outputting all of the region code, it would just use the Region code that is between the ranges based on the <strong>#track</strong> table provided..</p> <p>Any Help? or other approach?? thank you!. sorry im new to SQL.</p> <p><strong>EDIT</strong> here is the code to create the temp tables. </p> <p><strong>#trackexpired</strong> </p> <pre><code> SELECT PH.ID ,PH.LOAN ,PH.EXPIRATION ,PH.AMOUNT ,(SELECT T3.LOAN FROM Transactions.HISTO T3 INNER JOIN ( SELECT MIN(T2.ID) as pawnhisto FROM Transactions.HISTO T2 WHERE T2.ID &gt; PH.ID AND PH.ID = T2.ID ) T4 ON T4.pawnhisto = T3.ID )as 'NewLoanDate' INTO #TrackExpired FROM Transactions.HISTO PH INNER JOIN Transactions.MAIN PM ON PM.ID=PH.ID WHERE YEAR(PH.LOAN) = @YEAR </code></pre> <p><strong>#track</strong></p> <pre><code> Select bt.CODE ,bp.ID ,AREA ,REGION ,NCODE ,FROM ,isnull(fld_Dateto,GETDATE()) as fld_Dateto into #sort from Transactions.tbl_BranchTracking bt inner join Reference.tbl_BranchProfiles bp on bt.CODE = bp.CODE Select * into #track from #sort where @YEAR &gt;= year(FROM) and @YEAR &lt;= year(fld_Dateto) </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser2705620
    1. USuser2705620
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSelecting specific Data per month
      1. USTeamDataViz.
      1. VTFavorite
    1. COThese don't appear to be duplicates. Look at the PawnMainID field values; they are all unique.
      singulars
      1. POSelecting specific Data per month
      1. USBill Stidham
    2. CO@BillStidham those are just sample If you would scroll them up the set of 4A is the same of set data with 3 as region code. those are just sample screenshots
      singulars
      1. POSelecting specific Data per month
      1. USuser2705620
    3. CO@BillStidham please check again the question i have edited the screenshots thank you.
      singulars
      1. POSelecting specific Data per month
      1. USuser2705620
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload