Results 1 to 17 of 17

Thread: SQL query for multiple months

  1. #1

    Thread Starter
    Member
    Join Date
    Aug 2016
    Posts
    37

    Exclamation SQL query for multiple months

    query for multiple months report
    I have 2 table
    1-ItemMaster
    ItemCode, ItemName

    2-ItemSale
    ItemCode, SaleDate,Amt

    i wanted to have query as follows
    user will select give date as '01/01/18' to 05/30/18'

    query output should like this

    TotalNoItems, GivenMonthDays, TotalSale

    TotalNoItem(count Item from ItemMaster)
    GivenMonthDays(Month Days from Every month given in dates)
    Totalsale(Totasale from every month given in dates)

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: SQL query for multiple months

    Are you saying, without actually saying, that you want aggregate sales for each day in the specified range? For each day, you want the number of items sold, the date of those sales and the total value? Does the SaleDate column contain just date or date and time? What database?

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL query for multiple months

    Nevermind that he's not specifying which DBMS he's using...... *gazingintoacrystalball*
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  4. #4

    Thread Starter
    Member
    Join Date
    Aug 2016
    Posts
    37

    Re: SQL query for multiple months

    Quote Originally Posted by jmcilhinney View Post
    Are you saying, without actually saying, that you want aggregate sales for each day in the specified range? For each day, you want the number of items sold, the date of those sales and the total value? Does the SaleDate column contain just date or date and time? What database?
    sorry i missed some columns
    it should be like this
    TotalNoItem GivenMonthDays Totalsale Month-Year
    20 31 90 Jan-2018
    20 28 105 Feb-2018

    Name:  Presentation1.jpg
Views: 946
Size:  8.0 KB

    sql database
    for each month
    Last edited by yousufkhan73; Aug 6th, 2018 at 05:58 AM.

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL query for multiple months

    All cool and nice...
    but what DBMS are you using?
    What datatype are the dates stored in the DB?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: SQL query for multiple months

    Quote Originally Posted by yousufkhan73 View Post
    sql database
    There's no such thing as a SQL database, or else every database is a SQL database, given that they basically all use the SQL language for data definition and manipulation. Do you mean SQL Server? MySQL? SQLite? PostgreSQL? Some other database that doesn't actually have "SQL" in the name? Be clear. Vague or ambiguous names and terms promote confusion. If we guess and we guess wrong, it's a waste of everyone's time.

  7. #7

    Thread Starter
    Member
    Join Date
    Aug 2016
    Posts
    37

    Re: SQL query for multiple months

    sorry bro
    database is SQL Server 2012,

    1-ItemMaster
    ItemCode - integer
    ItemName - varchar

    2-ItemSale
    ItemCode - integer,
    SaleDate- Date ,
    Amt-Decimal

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL query for multiple months

    Don't forget the Datatype/Format of the Datefield.......
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  9. #9

    Thread Starter
    Member
    Join Date
    Aug 2016
    Posts
    37

    Re: SQL query for multiple months

    sorry bro
    database is SQL Server 2012,

    1-ItemMaster
    ItemCode - integer
    ItemName - varchar

    2-ItemSale
    ItemCode - integer,
    SaleDate- Date ,
    Amt-Decimal

  10. #10
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: SQL query for multiple months

    Do you know how to join the two tables? Do you know how to aggregate using SUM and COUNT and then GROUP BY non-aggregated columns? That's stuff that you can easily learn for yourself and should already know before trying to do this.

    The only tricky part is the grouping. You'd generally group by a column but doing so on SaleDate in this case will give you rows for each day, assuming that it contains date only and not time. The trick, then is to not 'GROUP BY SaleDate' but, instead, 'GROUP BY CONVERT(varchar(8), SaleDate, NNN)' where NNN is the code for a date format that contains only month and year values. That way, all dates within the same month will produce the same value and be aggregated. The number of days in the month is something that I'll let you figure out by doing appropriate web searches.

  11. #11
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL query for multiple months

    Well,
    TotalNoItem is a COUNT
    GivenMonthsDay is a EOMONTH (at least that would be my first try)
    TotalSale is a SUM
    The Month-Year is a FORMAT(@date, 'mmm-yyyy')
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  12. #12
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL query for multiple months

    Quote Originally Posted by jmcilhinney View Post
    Do you know how to join the two tables?
    Looking at his design, that isn't even necessary. He wouldn't need his mastertable for the query....
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  13. #13
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: SQL query for multiple months

    Quote Originally Posted by Zvoni View Post
    Looking at his design, that isn't even necessary. He wouldn't need his mastertable for the query....
    Valid point. I guess I just assumed because it was mentioned but you're right that it doesn't figure into the actual query.

  14. #14
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: SQL query for multiple months

    What Sql query do you have so far?

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  15. #15

    Thread Starter
    Member
    Join Date
    Aug 2016
    Posts
    37

    Re: SQL query for multiple months

    Quote Originally Posted by Ecniv View Post
    What Sql query do you have so far?
    declare @fdate as date
    declare @edate as date
    set @fdate='03/01/13'
    set @edate='06/30/13'

    ;with Items(TotalNoItem ) as (select count(ItemCode ) from ItemMaster),
    Months(TotSaleItem, Mnthyr) as (select count(ItemCode ) ,month(SaleDate) from ItemSale where SaleDate between @fdate and @edate
    group by month(SaleDate))

    select Items.TotalNoItem ,months.TotSaleItem,Months.Mnthyr from Items,Months

    this is what i tried and got the result only i am not able to get days of month and year \
    so now try to help me out

  16. #16
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL query for multiple months

    First off: As mentioned, you don't need to query the Mastertable, just your sales-table
    and for everything else there are enough hints for you to get the query correct
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  17. #17
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: SQL query for multiple months

    Well, i'm bored right now.
    Note: UNTESTED (since i don't have an MSSQL2012)
    SQL Code:
    1. SELECT COUNT(ItemCode) AS TotalNoItem, GivenMonthsDay, SUM(Amt) AS TotalSales, MonthYear FROM
    2. (SELECT
    3. ItemCode,
    4. DAY(EOMONTH(SaleDate)) AS GivenMonthsDay,
    5. Amt,
    6. FORMAT(SaleDate, 'mmm-yyyy') AS MonthYear
    7. FROM ItemSale
    8. WHERE
    9. SaleDate BETWEEN '01/01/18' AND '05/30/18'
    10. ORDER BY SaleDate)
    11. GROUP BY
    12. GivenMonthsDay, MonthYear
    Last edited by Zvoni; Aug 9th, 2018 at 02:04 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Posting Permissions

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



Click Here to Expand Forum to Full Width