I have written a sql statement and know I should do it different using case statements but not sure how sugestions please

• kat35601

  SSC Eights!

  Points: 825

  More actions

  May 19, 2017 at 8:05 am

  #330364

  How would I write past15.past_15,  ,past30.past_30,past60.past_60,  ,tot.total_inv as case statements

  
SELECT past15.past_15
    ,past30.past_30
    ,past60.past_60
    ,tot.total_inv
    ,totmoney.total_money
    ,og.cmoCustomerPaymentTermsID
    ,og.cmoOrganizationID
    ,og.cmoName
    ,og.cmoAccountManagerEmployeeID
    ,og.cmoAddressLine1
    ,og.cmocity
    ,og.cmoState
    ,og.cmoPostCode
    ,og.cmoPhoneNumber
    ,og.cmoCustomerGroupID
    
    
    FROM Organizations og
LEFT JOIN OrganizationLocations ON cmlOrganizationID = cmoOrganizationID
LEFT JOIN (
    SELECT count(arpARInvoiceID) AS past_15
        ,cmoOrganizationID
    FROM ARInvoices
    LEFT JOIN Organizations ON arpShipOrganizationID = cmoOrganizationID
    LEFT JOIN OrganizationLocations ON cmlOrganizationID = arpShipOrganizationID
        AND cmlLocationID = arpShipLocationID
    WHERE arpInvoiceType = 1
        AND arpInvoiceDate >= DATEADD(year,-1,GETDATE())
        AND cmoSupplierStatus = 0
        AND arpPaidDate > arpDueDate + 15
        AND arpPaidDate < arpDueDate + 30
    GROUP BY cmoOrganizationID
    ) AS past15 ON past15.cmoOrganizationID = og.cmoOrganizationID
LEFT JOIN (
    SELECT count(arpARInvoiceID) AS past_30
        ,cmoOrganizationID
    FROM ARInvoices
    LEFT JOIN Organizations ON arpShipOrganizationID = cmoOrganizationID
    LEFT JOIN OrganizationLocations ON cmlOrganizationID = arpShipOrganizationID
        AND cmlLocationID = arpShipLocationID
    WHERE arpInvoiceType = 1
        AND arpInvoiceDate >= DATEADD(year,-1,GETDATE())
        AND cmoSupplierStatus = 0
        AND arpPaidDate > arpDueDate + 30
        AND arpPaidDate < arpDueDate + 45
    GROUP BY cmoOrganizationID
    ) AS past30 ON past30.cmoOrganizationID = og.cmoOrganizationID
LEFT JOIN (
    SELECT count(arpARInvoiceID) AS past_60
        ,cmoOrganizationID
    FROM ARInvoices
    LEFT JOIN Organizations ON arpShipOrganizationID = cmoOrganizationID
    LEFT JOIN OrganizationLocations ON cmlOrganizationID = arpShipOrganizationID
        AND cmlLocationID = arpShipLocationID
    WHERE arpInvoiceType = 1
        AND arpInvoiceDate >= DATEADD(year,-1,GETDATE())
        AND cmoSupplierStatus = 0
        AND arpPaidDate > arpDueDate + 60
    GROUP BY cmoOrganizationID
    ) AS past60 ON past60.cmoOrganizationID = og.cmoOrganizationID
LEFT JOIN (
    SELECT count(arpARInvoiceID) AS total_inv
        ,cmoOrganizationID
    FROM ARInvoices
    LEFT JOIN Organizations ON arpShipOrganizationID = cmoOrganizationID
    LEFT JOIN OrganizationLocations ON cmlOrganizationID = arpShipOrganizationID
        AND cmlLocationID = arpShipLocationID
    WHERE arpInvoiceType = 1
        AND arpInvoiceDate >= DATEADD(year,-1,GETDATE())
        AND cmoCustomerStatus = 2
    GROUP BY cmoOrganizationID
    ) AS tot ON tot.cmoOrganizationID =og.cmoOrganizationID
LEFT JOIN (
    SELECT sum(arpFullInvoiceSubtotalBase) AS total_money
        ,cmoOrganizationID
    FROM ARInvoices
    LEFT JOIN Organizations ON arpShipOrganizationID = cmoOrganizationID
    LEFT JOIN OrganizationLocations ON cmlOrganizationID = arpShipOrganizationID
        AND cmlLocationID = arpShipLocationID
    WHERE arpInvoiceType = 1
        AND arpInvoiceDate >= DATEADD(year,-1,GETDATE())
        AND cmoCustomerStatus = 2
        
    GROUP BY cmoOrganizationID
    ) AS totmoney ON totmoney.cmoOrganizationID = og.cmoOrganizationID
WHERE cmoCustomerStatus = 2


• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  May 19, 2017 at 8:12 am

  #1943312

  Here's an example on how to do it. Try to complete it with the missing columns.
  Ask any questions you might have.
  SELECT past.past_15
  ,past.past_30
  ,past.past_60
  ,tot.total_inv
  ,totmoney.total_money
  ,og.cmoCustomerPaymentTermsID
  ,og.cmoOrganizationID
  ,og.cmoName
  ,og.cmoAccountManagerEmployeeID
  ,og.cmoAddressLine1
  ,og.cmocity
  ,og.cmoState
  ,og.cmoPostCode
  ,og.cmoPhoneNumber
  ,og.cmoCustomerGroupID
 
 
  FROM Organizations og
LEFT JOIN OrganizationLocations ON cmlOrganizationID = cmoOrganizationID
LEFT JOIN (
  SELECT count(CASE WHEN arpPaidDate > arpDueDate + 15 AND arpPaidDate < arpDueDate + 30 THEN arpARInvoiceID END) AS past_15
   ,count(CASE WHEN arpPaidDate > arpDueDate + 30 AND arpPaidDate < arpDueDate + 45 THEN arpARInvoiceID END) AS past_30
   ,count(CASE WHEN arpPaidDate > arpDueDate + 60 THEN arpARInvoiceID END) AS past_60
   ,cmoOrganizationID
  FROM ARInvoices
  LEFT JOIN Organizations ON arpShipOrganizationID = cmoOrganizationID
  LEFT JOIN OrganizationLocations ON cmlOrganizationID = arpShipOrganizationID
   AND cmlLocationID = arpShipLocationID
  WHERE arpInvoiceType = 1
   AND arpInvoiceDate >= DATEADD(year,-1,GETDATE())
   AND cmoSupplierStatus = 0
  GROUP BY cmoOrganizationID
  ) AS past ON past.cmoOrganizationID = og.cmoOrganizationID
WHERE cmoCustomerStatus = 2

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2