PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
SQL query for multiple months-VBForums
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
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,375

    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?
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

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

    Re: SQL query for multiple months

    Nevermind that he's not specifying which DBMS he's using...... *gazingintoacrystalball*
    One System to rule them all, One IDE to find them,
    One Code 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.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  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: 66
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
    1,500

    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?
    One System to rule them all, One IDE to find them,
    One Code 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.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,375

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  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
    1,500

    Re: SQL query for multiple months

    Don't forget the Datatype/Format of the Datefield.......
    One System to rule them all, One IDE to find them,
    One Code 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.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  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
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,375

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

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

    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')
    One System to rule them all, One IDE to find them,
    One Code 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.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

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

    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....
    One System to rule them all, One IDE to find them,
    One Code 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.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  13. #13
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,375

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  14. #14
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Java (JSP) Hell!
    Posts
    5,304

    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
    1,500

    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
    One System to rule them all, One IDE to find them,
    One Code 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.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

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

    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.
    One System to rule them all, One IDE to find them,
    One Code 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.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width