DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: A complicated SQL statement

  1. #1
    Join Date
    Aug 2004
    Posts
    43,023

    A complicated SQL statement

    [Originally posted by Liju John]

    i have a table called sales in an msaccess database.
    the colums in the table are
    billno,date,itemname,quantity,rate.

    I have two dates date1 and date2.

    i want to query the table so that the output will contain the
    following columns
    item name,the total quantity of each item sold before date1,
    total quantity of each item sold between date1 and date2 and
    a fouth column that contains closing stock of each item as on date2.

    Is this˙ possible?.

    Can any one given me the SQL query command that performs
    this operation.
    thanks
    liju

  2. #2
    Join Date
    Aug 2004
    Posts
    43,023

    Re:A complicated SQL statement

    [Originally posted by Jose Sanchez]

    Yes,it is possible, you must work to do it
    but you can.

    How...
    If you use DAO 3.6 or recent ADO˙ from vb6
    or recent access you can do somethink like this:

    ˙ select itemname,sum(TqBeforeDate1),sum(TqBetweenD1D2),sum(cStock)
    from
    (
    select itemname, sum(quantity) as TqBeforeDate1,0 as tqbetweenD1D2,0 as sStock from MyTable where
    date < cvdate('xx/xx/xx') group by itemname

    union all

    select itemname, 0 as TqBeforeDate1,sum(quantity) as tqbetweenD1D2,0 as sStock from MyTable where
    date between cvdate('xx/xx/xx') and cvdate('zz/zz/zz') group by itemname

    union all

    select itemname, 0 as TqBeforeDate1,0 as tqbetweenD1D2,quantity as sStock from MyTable where
    date = cvdate('zz/zz/zz')

    )
    group by itemname;

    Notice: you create 3 sql queries with same
    structure, you assign 0 as value you will get
    in other queries, then you make a union
    of three and cover that with a sql query
    that sum all results and show each item.

    This is a way... hope this help


  3. #3
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:A complicated SQL statement

    [Originally posted by Liju John]

    thanks for the reply.
    i will test it and get back
    thnaks
    liju

  4. #4
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:A complicated SQL statement

    [Originally posted by Liju John]

    I found a more simple solution
    Just try it

    SELECT [itemname], sum(IIf([date]<datevalue(date1),[qty],0)) AS opqty, sum(IIf([date]>=datevalue(date1) And [date]<=datevalue(date2),[qty],0)) AS sales, sum(IIf([date]>datevalue(date2),[qty],0)) AS closeqty
    FROM sales
    GROUP BY [itemname];

    Isn't it more better?

    Liju


  5. #5
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:A complicated SQL statement

    [Originally posted by Liju John]

    I found a more simple solution
    Just try it

    SELECT [itemname], sum(IIf([date]<datevalue(date1),[qty],0)) AS opqty, sum(IIf([date]>=datevalue(date1) And [date]<=datevalue(date2),[qty],0)) AS sales, opqty+sales AS closeqty
    FROM sales
    GROUP BY [itemname];

    Isn't it more better?

    Liju


  6. #6
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Re:A complicated SQL statement

    [Originally posted by Jose Sanchez]

    Yes, it is a very very good solution.
    thanks,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center