-
Aug 6th, 2018, 05:13 AM
#1
Thread Starter
Member
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)
-
Aug 6th, 2018, 05:20 AM
#2
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?
-
Aug 6th, 2018, 05:37 AM
#3
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
-
Aug 6th, 2018, 05:39 AM
#4
Thread Starter
Member
Re: SQL query for multiple months
Originally Posted by jmcilhinney
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
sql database
for each month
Last edited by yousufkhan73; Aug 6th, 2018 at 05:58 AM.
-
Aug 6th, 2018, 05:52 AM
#5
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
-
Aug 6th, 2018, 05:56 AM
#6
Re: SQL query for multiple months
Originally Posted by yousufkhan73
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.
-
Aug 6th, 2018, 06:02 AM
#7
Thread Starter
Member
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
-
Aug 6th, 2018, 06:02 AM
#8
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
-
Aug 6th, 2018, 06:03 AM
#9
Thread Starter
Member
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
-
Aug 6th, 2018, 06:10 AM
#10
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.
-
Aug 6th, 2018, 06:11 AM
#11
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
-
Aug 6th, 2018, 06:22 AM
#12
Re: SQL query for multiple months
Originally Posted by jmcilhinney
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
-
Aug 6th, 2018, 07:07 AM
#13
Re: SQL query for multiple months
Originally Posted by Zvoni
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.
-
Aug 6th, 2018, 09:41 AM
#14
Re: SQL query for multiple months
What Sql query do you have so far?
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...
-
Aug 7th, 2018, 01:24 AM
#15
Thread Starter
Member
Re: SQL query for multiple months
Originally Posted by Ecniv
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
-
Aug 7th, 2018, 01:48 AM
#16
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
-
Aug 7th, 2018, 03:36 AM
#17
Re: SQL query for multiple months
Well, i'm bored right now.
Note: UNTESTED (since i don't have an MSSQL2012)
SQL Code:
SELECT COUNT(ItemCode) AS TotalNoItem, GivenMonthsDay, SUM(Amt) AS TotalSales, MonthYear FROM (SELECT ItemCode, DAY(EOMONTH(SaleDate)) AS GivenMonthsDay, Amt, FORMAT(SaleDate, 'mmm-yyyy') AS MonthYear FROM ItemSale WHERE SaleDate BETWEEN '01/01/18' AND '05/30/18' ORDER BY SaleDate) GROUP BY 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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|