Author |
Topic |
nounou
Starting Member
7 Posts |
Posted - 2012-02-05 : 11:47:29
|
hello all. im new here and i need a bit of help :)i have the tableID DATE AMOUNTi have many records with same DATE BUT DIFFERENT AMOUNT.I need a query tha ask me the start date and the end date and when i give the dates i need to give me back the sum of those amounts. please help me :)thnx in advance |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-05 : 12:10:59
|
[code]SELECT SUM(AMOUNT)FROM TableWHERE DATE >= @StartDateAND DATE < @EndDate +1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
nounou
Starting Member
7 Posts |
Posted - 2012-02-05 : 12:30:21
|
thnx for the reply but i type it as i see it here with @ and doens work... i think i do smthg wrong... i have thin in my queryPARAMETERS [Start Date] DateTime, [End Date] DateTime;SELECT Sum(amount)FROM tableGROUP BY dateHAVING (((table.[date])>=[Start Date] And (table.[date])<=[End Date]));e.xi give start date 1/1/2012 (where the amount is 10) and end date 2/1/2012(where the amount is 20)and instead of giving me the sum (30) the qyery gives me 1/1/2012 102/1/2012 20 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-05 : 12:36:58
|
quote: Originally posted by nounou thnx for the reply but i type it as i see it here with @ and doens work... i think i do smthg wrong... i have thin in my queryPARAMETERS [Start Date] DateTime, [End Date] DateTime;SELECT Sum(amount)FROM tableGROUP BY dateHAVING (((table.[date])>=[Start Date] And (table.[date])<=[End Date]));e.xi give start date 1/1/2012 (where the amount is 10) and end date 2/1/2012(where the amount is 20)and instead of giving me the sum (30) the qyery gives me 1/1/2012 102/1/2012 20
@ represents parametersthat's what you pass as input from users------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
nounou
Starting Member
7 Posts |
Posted - 2012-02-05 : 12:44:46
|
Thank you very much ...but i cant fix it :( |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-05 : 12:52:29
|
quote: Originally posted by nounou Thank you very much ...but i cant fix it :(
didnt understand what exactly your problem is?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
nounou
Starting Member
7 Posts |
Posted - 2012-02-05 : 12:57:04
|
i have this tableID DATE AMOUNT1 1/1/2012 10.0002 2/1/2012 20.0003 3/1/2012 30.000And i want to input start date and end date and find me the sum amount e.x i put start date 1/1/2012 and end date 2/1/2012 and the query i need to give me back the sum of those two dates. (30.000) |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-05 : 12:57:45
|
Where does this come from?PARAMETERS [Start Date] DateTime, [End Date] DateTime;Is this SQL Server or something else?--Gail ShawSQL Server MVP |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-05 : 12:59:47
|
quote: Originally posted by nounou i have this tableID DATE AMOUNT1 1/1/2012 10.0002 2/1/2012 20.0003 3/1/2012 30.000And i want to input start date and end date and find me the sum amount e.x i put start date 1/1/2012 and end date 2/1/2012 and the query i need to give me back the sum of those two dates. (30.000)
Exactly what Visakh postedSELECT SUM(AMOUNT)FROM TableWHERE DATE >= @StartDateAND DATE < @EndDate +1and you pass 1/1/2012 as the parameter @StartDate and 2/1/2012 for the parameter @EndDate--Gail ShawSQL Server MVP |
|
|
nounou
Starting Member
7 Posts |
Posted - 2012-02-05 : 13:00:43
|
i dont know :P im new with this :Pbut i need this i posted last... :) |
|
|
nounou
Starting Member
7 Posts |
Posted - 2012-02-05 : 13:05:54
|
i dont want only for those specific dates... my table has many records. and many dates... from 2011 up today. i just need to type start date and end date and find the sum . i dont know if im writting something wrong but could tou please writte me the exact commands? the table name is INPUTS and the field areID DATE AMOUNT |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-02-05 : 13:40:02
|
This is Visakh's query with 3 lines added, just so I can explain:--------------------------------------------DECLARE @StartDate DATETIME, @EndDate DATETIME;SET @StartDate = '20110101';SET @EndDate = '20120131';SELECT SUM(AMOUNT)FROM INPUTSWHERE DATE >= @StartDate AND DATE < @EndDate +1;------------------------------------------- Copy that into a SQL Server Management Studio query window and make sure the connection points to your database. Then, run the query (Query->Execute from the top menu) That will find you the sum from Jan 1, 2011 to Jan 31, 2012. Now, if you want to use a different set of dates, for example May 25, 2011 to May 31, 2011, change the 20110101 to 20110525 and change the 20120131 to 20110531 and run the query again.Is that what you are looking for? If that is not it, post 2 or 3 sample input sets and the corresponding output that you want to see. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-05 : 13:49:21
|
quote: Originally posted by nounou i dont want only for those specific dates... my table has many records. and many dates... from 2011 up today. i just need to type start date and end date and find the sum . i dont know if im writting something wrong but could tou please writte me the exact commands? the table name is INPUTS and the field areID DATE AMOUNT
Thats why you've parameter placeholders through which you pass whatever values you want and get data for that------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-05 : 13:53:09
|
quote: Originally posted by nounou i dont want only for those specific dates... my table has many records. and many dates... from 2011 up today. i just need to type start date and end date and find the sum .
And that is exactly what the query will do. The @StartDate and @EndDate are parameters. Set them to whatever the dates are that you want as start and end dates and run the query and you will get the sum between those dates.If you're running this from Management Studio, use the form that sunitabeck showed. If this is from a C# app or something similar then you'd use the ado.net parameters collection--Gail ShawSQL Server MVP |
|
|
nounou
Starting Member
7 Posts |
Posted - 2012-02-05 : 16:58:30
|
thank you a lot i fix it :)) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-05 : 19:24:47
|
Glad that sorted it out finally!------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
csteve274
Starting Member
3 Posts |
Posted - 2012-02-10 : 07:38:55
|
Hi,You can find the better database management software here. Hope this will meet your requirement.unspammedThanks |
|
|
|