Results 1 to 1 of 1
  1. #1
    Join Date
    Oct 2013
    Posts
    2

    Unanswered: SQLite questions

    I am using SQLite


    1)
    Code:
     select c.CustomerID, round(IFNULL(sum(IFNULL(od.NetSales,0)),0),2) as NetSales180
                         from customer c LEFT JOIN orders o
                       ON (c.CustomerID = o.CustomerID)
                      INNER JOIN order_details od ON (o.OrderID = od.OrderID)
                        where o.PurchaseDateTime BETWEEN datetime('now', '-6  MONTH') AND  datetime('now', '-3  MONTH')
                        group by c.CustomerID
    This would not yield any results.
    I have to select a full month to work on.
    E.g. if today is 15th January
    6 months before is December, I need to include sales happened
    on 1st of December last year, as this statement would exclude that
    range.


    2)
    Code:
    select c.CustomerID, IFNULL(c1.NetSales180 ,0), IFNULL(c2.NetSales90, 0), IFNULL(ABS(c1.NetSales180 - c2.NetSales90),0)
    from customer c LEFT JOIN
    
    (select c.*, round(IFNULL(sum(IFNULL(od.NetSales,0)),0),2) AS NetSales180
    				      from customer c INNER JOIN orders o
    					   ON (c.CustomerID = o.CustomerID)
    					 INNER JOIN order_details od ON (o.OrderID = od.OrderID)
    					 where strftime('%m', o.PurchaseDateTime) BETWEEN strftime('%m',datetime('now', '-6 MONTH')) AND strftime('%m', datetime('now', '-3  MONTH')) group by c.CustomerID) c1
         ON c.CustomerID = c1.CustomerID
         LEFT JOIN
         (select c.*, round(IFNULL(sum(IFNULL(od.NetSales,0)),0),2) AS NetSales90
    				      from customer c INNER JOIN orders o
    					   ON (c.CustomerID = o.CustomerID)
    					 INNER JOIN order_details od ON (o.OrderID = od.OrderID)
    					 where strftime('%m', o.PurchaseDateTime) BETWEEN strftime('%m', datetime('now', '-3  MONTH')) AND strftime('%m',datetime('now'))
       
       group by c.CustomerID) c2
       ON c.CustomerID = c2.CustomerID
    I don't understand why abs(X) doesn't work which give me zeros?
    Thanks
    Jack
    Last edited by lucky7456969; 10-05-13 at 06:48.

Posting Permissions

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