Databases
StackOverflow2013
Postsdbo
POGenerate Zeroes when no rows where selected
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POGenerate Zeroes when no rows where selected
    primarykey
    data
    text
    <p>Good Day Every one</p> <p>i have this code </p> <pre><code> SELECT 'Expired Item -'+ DateName(mm,DATEADD(MM,4,AE.LOAN)) as [Month] ,COUNT(ISNULL(PIT.ID,0))'COUNT' ,SUM(ISNULL(PIT.KGRAM,0))'GRAMS' ,SUM(ISNULL(PH.AMOUNT,0))'PRINCIPAL' FROM #AllExpired AE INNER JOIN Transactions.ITEM PIT ON AE.MAINID=PIT.MAINID INNER JOIN Transactions.HISTO PH ON AE.MAINID=PH.MAINID GROUP BY DATENAME(MM,(DATEADD(MM,4,AE.LOAN))) UNION ALL /*SELECT EXPIRED AFTER 5 MONTHS*/ SELECT 'Expired Item -'+ DateName(mm,DATEADD(MM,5,AE.LOAN)) as [Month] ,COUNT(ISNULL(PIT.ID,0))'COUNT' ,SUM(ISNULL(PIT.KGRAM,0))'GRAMS' ,SUM(ISNULL(PH.AMOUNT,0))'PRINCIPAL' FROM #ExpAfterFiveMonths E5 INNER JOIN Transactions.ITEM PIT ON E5.MAINID=PIT.MAINID INNER JOIN Transactions.HISTO PH ON E5.MAINID=PH.MAINID INNER JOIN #AllExpired AE ON AE.MAINID=E5.MAINID GROUP BY DATENAME(MM,(DATEADD(MM,5,AE.LOAN))) UNION ALL /*SELECT EXPIRED AFTER 6 MONTHS*/ SELECT 'Expired Item -'+ DateName(mm,DATEADD(MM,6,AE.LOAN)) as [Month] ,COUNT(ISNULL(PIT.ID,0))'COUNT' ,SUM(ISNULL(PIT.KGRAM,0))'GRAMS' ,SUM(ISNULL(PH.AMOUNT,0))'PRINCIPAL' FROM #ExpAfterSixMonths E6 INNER JOIN Transactions.ITEM PIT ON E6.MAINID=PIT.MAINID INNER JOIN Transactions.HISTO PH ON E6.MAINID=PH.MAINID INNER JOIN #AllExpired AE ON AE.MAINID=E6.MAINID GROUP BY DATENAME(MM,(DATEADD(MM,6,AE.LOAN))) </code></pre> <p>and it works fine, the problem is that when the Select statements retrieved no rows they become empty instead of replacing zeroes</p> <p>instead of Generating the word month with 0 0 0 it just pops out empty in which i dont like,, </p> <p>can you help me achive that? the result should be something like this</p> <pre><code>------------------------------------------------------------------ MONTH | Count | Grams | Principal | October |123123 | 123123 | 123123213 | November | 0 | 0 | 0 | // this should appear if no rows where selected instead of blank </code></pre> <p>here is my code to generate the items inside temptables</p> <pre><code>SELECT TE.MAINID ,TE.EXPIRY ,TE.LOAN ,PM.STORAGE into #AllExpiredAfterFiveAndSix FROM #ExpiredAfterFiveandSixMon TE inner join Transactions.TABLEMAIN PM on TE.MAINID = PM.MAINID inner join #AllExpired E4 on E4.MAINID=TE.MAINID WHERE ((cast(TE.EXPIRY as date) &lt; cast(TE.newloandate as date)) OR(TE.NewLoanDate is null and ((cast(TE.EXPIRY as date) &lt; cast(PM.DATERED as date)) or PM.STATUS = 7 or PM.STATUS = 5)) ) AND (PM.STORAGE BETWEEN 3 AND 14 OR PM.STORAGE=17) /*EXPIRED AFTER 5 MONTHS*/ select AE.MAINID ,AE.LOAN ,AE.STORAGE ,ae.EXPIRY into #ExpAfterFiveMonths from #AllExpiredAfterFiveAndSix AE inner join #AllExpired E4 on E4.MAINID=AE.MAINID where MONTH(AE.EXPIRY)= MONTH(dateadd(mm,1,E4.EXPIRY)) /*EXPIRED AFTER 6 MONTHS*/ select AE.MAINID ,AE.LOAN ,AE.STORAGE ,ae.EXPIRY into #ExpAfterSixMonths from #AllExpiredAfterFiveAndSix AE inner join #AllExpired E4 on E4.MAINID=AE.MAINID where MONTH(AE.EXPIRY)= MONTH(dateadd(mm,2,E4.EXPIRY)) CREATE NONCLUSTERED INDEX IDX_ExpAfterFiveMonths ON #ExpAfterFiveMonths(MAINID) CREATE NONCLUSTERED INDEX IDX_ExpAfterSixMonths ON #ExpAfterSixMonths(MAINID) </code></pre> <p>i hope you can help me because im just a starter in sql </p> <p>i have tried using is NULL as what you have seen above but i do not know if i implement it correctly </p> <p>Hoping for your Kindness and consideration thank you :)</p> <p>****EDIT**</p> <p>The temp tables contains no values to start with, i just want it to output 0 instead of blank.</p>
    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
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POGenerate Zeroes when no rows where selected
      1. USuser2705620
      1. VTBountyStart
    2. VO
      singulars
      1. POGenerate Zeroes when no rows where selected
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCan you post the schema of your permanent tables?
      singulars
      1. POGenerate Zeroes when no rows where selected
      1. USLloyd Banks
    2. CO@LloydBanks what do you mean by schema? im sorry im new in t-sql
      singulars
      1. POGenerate Zeroes when no rows where selected
      1. USuser2705620
    3. COThe layout of the tables, so the column names and their types
      singulars
      1. POGenerate Zeroes when no rows where selected
      1. USLloyd Banks
 

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