Results 1 to 9 of 9

Thread: SQL within SQL

  1. #1
    Join Date
    Dec 2010
    Location
    Manchester
    Posts
    24

    Unanswered: SQL within SQL

    Hi guys im having trouble with this query,

    Code:
    SELECT      x.*, CASE WHEN daysremaining >= 0 THEN cast(DaysRemaining AS varchar(8)) + ' Days' WHEN daysremaining < 0 AND 
                            invoicestatus = 'UNPAID' THEN 'Due' WHEN daysremaining < 0 AND invoicestatus = 'PAID' THEN 'Paid' END AS DueDaysRemaining
    FROM          
    
    SELECT      invoiceid, clientid, invoicetype, invoicedescription, discountamount, expertid, invoicetotal, receivedamount, balance, invoicestatus, vat,
                                                        
    
    SELECT      TOP 1 period
    FROM           tab_dat_terms
    WHERE       tab_dat_terms.expertid = expertid AND tab_dat_terms.termsdesc = invoicedescription) AS Period, DATEDIFF(d, 
    CONVERT(datetime, CONVERT(varchar(10), GETDATE(), 111), 111), DATEADD(d, ISNULL
                                                        
    
    SELECT      TOP 1 period
    FROM          tab_dat_terms
    WHERE      tab_dat_terms.expertid = expertid AND tab_dat_terms.termsdesc = invoicedescription), 0), invoicedate)) 
                                                    AS DaysRemaining
    
    
    FROM           vw_acc_invoicesummary v
    WHERE       (invoicetype = 'PUR')) x

    the trouble, starts is with the period, does not differentiate between items in tab_dat_terms, instead of retrieving the unique record for each expert ID, it always grabs the same single record!

    any ideas?

    i have check the table itself and the data is correct, its definatley this query!

    Thanks in advance

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    TOP without ORDER BY makes no sense at all

    which tab_dat_terms row did you want for each expert?

    by the way, your query has a syntax error, it could not possibly have run exactly as you posted it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2010
    Location
    Manchester
    Posts
    24
    Quote Originally Posted by r937 View Post
    TOP without ORDER BY makes no sense at all

    which tab_dat_terms row did you want for each expert?

    by the way, your query has a syntax error, it could not possibly have run exactly as you posted it
    You are correct, have just realised that it is not the original, i had mad on or two changes in that one sorry!

    here is original cod

    Code:
    SELECT      x.*, CASE WHEN daysremaining >= 0 THEN cast(DaysRemaining AS varchar(8)) + ' Days' WHEN daysremaining < 0 AND 
                            invoicestatus = 'UNPAID' THEN 'Due' WHEN daysremaining < 0 AND invoicestatus = 'PAID' THEN 'Paid' END AS DueDaysRemaining
    FROM          (SELECT      invoiceid, clientid, invoicetype, invoicedescription, discountamount, expertid, invoicetotal, receivedamount, balance, invoicestatus, vat,
                                                        (SELECT      TOP 1 period
                                                          FROM           tab_dat_terms
                                                          WHERE       tab_dat_terms.expertid = expertid AND tab_dat_terms.termsdesc = invoicedescription) AS Period, DATEDIFF(d, 
                                                    CONVERT(datetime, CONVERT(varchar(10), GETDATE(), 111), 111), DATEADD(d, ISNULL
                                                        ((SELECT      TOP 1 period
                                                            FROM          tab_dat_terms
                                                            WHERE      tab_dat_terms.expertid = expertid AND tab_dat_terms.termsdesc = invoicedescription), 0), invoicedate)) 
                                                    AS DaysRemaining
                            FROM           vw_acc_invoicesummary v
                            WHERE       (invoicetype = 'PUR')) x

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, but TOP without ORDER BY still makes no sense

    which tab_dat_terms row did you want for each expert?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2010
    Location
    Manchester
    Posts
    24
    Im trying to use the period column of the table to perform a calculation,

    and ORDER BY i shall put in,

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Batou View Post
    Im trying to use the period column of the table to perform a calculation,
    yeah, i can see that

    will you post again when you figure out what the ORDER BY should be?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2010
    Location
    Manchester
    Posts
    24
    Code:
    SELECT      x.*, CASE WHEN daysremaining >= 0 THEN cast(DaysRemaining AS varchar(8)) + ' Days' WHEN daysremaining < 0 AND 
                            invoicestatus = 'UNPAID' THEN 'Due' WHEN daysremaining < 0 AND invoicestatus = 'PAID' THEN 'Paid' END AS DueDaysRemaining
    FROM          (SELECT      invoiceid, clientid, invoicetype, invoicedescription, discountamount, expertid, invoicetotal, receivedamount, balance, invoicestatus, vat,
                                                        (SELECT      TOP 1 period
                                                          FROM           tab_dat_terms
                                                          WHERE       tab_dat_terms.expertid = expertid AND tab_dat_terms.termsdesc = invoicedescription
                                                          ORDER BY period ASC) AS Period, DATEDIFF(d, CONVERT(datetime, CONVERT(varchar(10), GETDATE(), 111), 111), DATEADD(d, 
                                                    ISNULL
                                                        ((SELECT      TOP 1 period
                                                            FROM          tab_dat_terms
                                                            WHERE      tab_dat_terms.expertid = expertid AND tab_dat_terms.termsdesc = invoicedescription
                                                            ORDER BY period ASC), 0), invoicedate)) AS DaysRemaining
                            FROM           vw_acc_invoicesummary v
                            WHERE       (invoicetype = 'PUR')) x
    ORDER BY DueDaysRemaining

    okay added the ORDER BY
    and parses just fine!

    have you any ideas on how to solve my data issue,

    as thats what is causing the most trouble!

    thanks
    Last edited by Batou; 01-21-11 at 10:12.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    SELECT x.*
         , CASE WHEN DaysRemaining >= 0 
                THEN cast(DaysRemaining AS varchar(8)) + ' Days' 
                WHEN DaysRemaining < 0 
                 AND invoicestatus = 'UNPAID' 
                THEN 'Due' 
                WHEN DaysRemaining < 0 
                 AND invoicestatus = 'PAID' 
                THEN 'Paid' 
            END AS DueDaysRemaining
      FROM ( SELECT v.invoiceid
                  , v.clientid
                  , v.invoicetype
                  , v.invoicedescription
                  , v.discountamount
                  , v.expertid
                  , v.invoicetotal
                  , v.receivedamount
                  , v.balance
                  , v.invoicestatus
                  , v.vat
                  , p.first_period
                  , DATEDIFF(d
                            ,CONVERT(datetime,CONVERT(varchar(10),GETDATE(),111),111)
                            ,DATEADD(d,COALESCE(p.first_period,0),v.invoicedate)
                            ) AS DaysRemaining
               FROM vw_acc_invoicesummary v
             LEFT OUTER
               JOIN ( SELECT expertid
                           , termsdesc
                           , MIN(period) as first_period
                        FROM tab_dat_terms 
                      GROUP
                          BY expertid
                           , termsdesc ) AS p
                 ON p.expertid = v.expertid
                AND p.termsdesc = v.invoicedescription
              WHERE v.invoicetype = 'PUR'
           ) x
    ORDER 
        BY DueDaysRemaining
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Dec 2010
    Location
    Manchester
    Posts
    24
    Thanks that has worked brilliant!

    you have been a great help,
    well deserving of your reputation!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •