How To Select Data For A Given Month Or All Records
Mar 29, 2007
I posted this question last night and thought I had an answer. I have a date field. I want to be able to filter records by the month of the date. To do this, I pass the integer of the month. But I also want to be able to return all the records if no month integer is passed. So functionally,
select * from table where (MONTH([AD ENDS]) = @month)
will return all records where [Ad Ends] is in January if @month = 1 and all records if @month is empty. The solution I got last night was to use
select * from table where (MONTH([AD ENDS]) = ISNULL(@month, MONTH([AD ENDS])))
If @month is null, all records are returned. I was focused on another aspect of the page when this was posted. It worked in the designer, so I thought I was set. This morning I realized I don't know how to pass a null variable in a querystring. Since a querystring is a string, it probably can't be done.
Another suggestions was to change this programmatically. But is there a way to do this in the dataset? I'm using SQL Server 2005, a strongly typed dataset and the designer.
Diane
The answer i got last night was to
View 4 Replies
ADVERTISEMENT
Nov 28, 2007
I have one SQL Table with 2 columns as below
Column1: ProductionDate - DateTime - Not NULL
Column2: Quantity - Int - Not NULL
Now There are 2 Records in Table
1-1-2007, 5
1-3-2007, 7
Output of Result should be as below
1-1-2007 5
1-2-2007 0
1-3-2007 7
1-4-2007 0
1-5-2007 0
1-6-2007 0
.
.
.
1-31-2007 0
Means Query should return all the dates of Month with Quantity and if no entry in Table then 0 for Quantity.
How to Do it? Please suggest with Query
View 5 Replies
View Related
Mar 24, 2008
select * from tbl where nmonth between datepart(mm,DATEADD(month, -2, getdate())) and datepart(mm,getdate())
this query is returning my data between 1(jan) and 3 (mar)
if i change my query to get all datas between nov to mar
select * from tbl where nmonth between datepart(mm,DATEADD(month, -4, getdate())) and datepart(mm,getdate())
this query is not returning data since the month between 11 to 3
Guys,Help me out to correct this syntax.
View 4 Replies
View Related
Jul 27, 2015
I have a table with dates and values and other columns. In a proc i need to get the result as Month and the values for all the months whether or not the data exists for the month.
The Similar table would be-
create table testing(
DepDate datetime,
val int)
insert into testing values ('2014-01-10 00:00:00.000', 1)
insert into testing values ('2014-05-19 00:00:00.000', 10)
insert into testing values ('2014-08-15 00:00:00.000', 20)
insert into testing values ('2014-11-20 00:00:00.000', 30)
But in result i want the table as -
Month Value
Jan1
Febnull
Marnull
Aprnull
May10
Junnull
Julnull
Aug20
Sepnull
Octnull
Nov30
Decnull
View 9 Replies
View Related
Nov 9, 2015
I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.
I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount
My Desired output
My current query
SELECT
RG.Id AS Id,
RG.Name AS Name,
ISNULL(SUM(AC.Amount), 0) AS Amount,
RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]
[Code] ....
View 6 Replies
View Related
Aug 11, 2015
Most of the data is in one table.
Company 1-Jan 1-Feb 1-Mar 1-Apr
RSP RSP RSP RSP
NON-RELO $295 1 $0 0 $1,400 7 $0 0 $1,195 4 $0 0 $4,700 8 $0 0
AMERICAN ESCROW & CL//AECC $2,650 4 $0 0 $3,720 8 $0 0 $2,339 4 $0 0 $2,460 2 $0 0
American Internation//AIRCO $9,131 30 $2,340 9 $10,927 35 $2,340 9 $9,142 31 $2,600 10 $18,406 54 $3,900 15
American Internation//AIR $20,611 63 $1,820 8 $23,892 75 $1,040 4 $35,038 111 $3,120 12 $3,778 16 $1,560 6
American Internation//Ab $64,248 206 $6,240 24 $59,800 187 $5,200 20 $87,115 264
I did something similar doing just record counts but this is far more complicated. I'm at a loss that this is even possible.
SUM(CASE datepart(month, tbFile.openedDate) WHEN 1 THEN 1 ELSE 0 END) AS 'January',
View 2 Replies
View Related
May 5, 2015
I have two tables. Users and records. I need to select only the users that not has lines recorded in the other table. How could I do that?
Example:
ID| Name| Access 1|Access 2|
----------------------------
1 | Axel | True |False |
2 | Ivan | False |False |
3 | Bob | True |False |
4 | Sue | False |False |
ID| Points| Month| Year|User_1|User_2|
--------------------------------------
1 | 2 | 5 | 2015| 2 | 1 |
2 | 5 | 5 | 2015| 2 | 1 |
3 | 1 | 5 | 2015| 3 | 1 |
Then I want to run a select in the users table, only with the users that hasn't records in the second table.
In the example, the second table has User_1 as the user who receives the points and the User_2 is the user who give the points. Then I would know what user didn't receive 'points' yet.
View 3 Replies
View Related
Jul 29, 2015
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table
View 9 Replies
View Related
Sep 27, 2006
Today's month is 9 as in September. How can I retrieve records entered last month?This is what I got so far... WHERE (Classes.CLWhenDt >= DATEADD([MONTH], - 1, GETDATE()))
View 4 Replies
View Related
Apr 15, 2008
hi friends,
how to display the records for current month?
View 7 Replies
View Related
Apr 3, 2014
I have a problem with a date in my sql view, I need the records from the 3 last months but without the current month, if I execute my view right now I have the records from January to april but I just need from January to march, I must have always the 3 previous month but I don't know how I can do it
(dbo.frhkrg.fakdat >= DATEADD(MM, - 3, GETDATE()))
View 2 Replies
View Related
Jan 11, 2005
I'm storing records that contain a date/time data type. I am needing two links on a reports page (asp), the first should return all records for the current month and the second link should return all records for the last three months (including current month). I have no idea how to just sort by month.
I'm also not sure what to include here in this post to help you answer my question. On the form that is submitted initially the text field is named "txtSubmitDate" and in the database it's stored in a field called "submitdate" and is 8 characters in length.
I've tried:
'SELECT TODAY'S MONTH
SqlJunk = "SELECT * FROM eom WHERE MONTH(submitdate) = MONTH(GETDATE())-1"
'SELECT TODAY'S MONTH and the last 2 months
SqlJunk2 = "SELECT * FROM eom WHERE MONTH(submitdate) = MONTH(GETDATE()) OR MONTH(submitdate) = MONTH(GETDATE())-1 OR MONTH(submitdate) = MONTH(GETDATE())-2 ORDER BY submitdate ASC"
These are not working because it can't handle the change in year (going from january 2005 back to december 2004, etc).
Any ideas?
View 7 Replies
View Related
May 23, 2008
I'm working on project for school that involves building a query in a video store database. The query is suppose to pull the total number of movies rented the previous month. I can get it to work if I physically put in the dates. However, part of the requirements is to set it up so the date range is auto calculated. The following is the code I have
SELECT COUNT(RecordNumber) AS TotalRentalsForMonth FROM RentalHistory
WHERE TransactionDate BETWEEN (YEAR(getdate()), MONTH(getdate()), 1)
AND (YEAR(getdate()), MONTH(getdate())+1, 0)
I get the following error message when I try to run it:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.
Anyone have an idea where my mistake is within the date range
View 1 Replies
View Related
Oct 7, 2015
I am trying to get count of records by month wise when they select year .It was showing the out put correctly but its showing months arer in numbers,but I want to display Jan,Feb ...
SELECT DISTINCT Standard, COUNT(Standard) AS Total,month(ReportDate) Month
FROM CPTable where
year(ReportDate) = '2015'
GROUP BY Standard, Standard ,
month(ReportDate)
Output
Standard Total Month
NULL 0 1 //Jan
NULL 0 2 //Feb
NULL 0 3
NULL 0 4
NULL 0 5
OSHA 18001, 1 5
NULL 0 6
NULL 07
NULL 08
OSHA 18001,158
TL 9000,18
NULL 09
OSHA 18001,139
View 4 Replies
View Related
Sep 12, 2006
Example table structure:
Id int, PK
Name varchar
AddDate smalldatetime
Sample data:
Id Name AddDate
1 John 01/15/2005
2 Jane 01/18/2005
.
.
.
101 Jack 01/10/2006
102 Mary 02/20/2006
First, I need to find the month which has the most records, I finally produced the correct results using this query but I am not convinced it's the most efficient way, can anyone offer a comment or advice here?
select top 1 count(id), datename(mm, AddDate) mth, datepart(yy, AddDate) yr
from dbo.sampletable
group by datename(mm, AddDate), datepart(yy, AddDate)
order by count(id) desc
Also, I'm really having trouble trying to get the overall average of records per month. Can anyone suggest a query which will produce only one number as output?
View 3 Replies
View Related
Nov 2, 2006
Hello
Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:
This is a simple matrix
test a
text b
text c
text d
text e
text f
text g
This is a matrix with all the same row-height.
test a
text b
.
text c
.
.
text d
text e
text f
text g
.
.
Thx you a lot
View 3 Replies
View Related
Apr 5, 2008
Hello what I'd like to display the following in a matrix report:
Parameter selected: 3 (March), 2008 (Year)
Monthly TO Summed up
ArtNo March <=March
1210 20,500 50,900
1220 21,200 64,000
1230 15,400 40,300
... ... ...
So, in the rows I have the articles and in the column the selected month via parameter. In another column I need to sum up all monthly values up to the selected month, meaning in this example the sum of jan, feb and mar per article.
View 3 Replies
View Related
Oct 8, 2015
Im trying to get query by selecting the month from dropdownlist and display the records .by using the below query I need to enter the date in tecxtboc then it will show the output
select Standard, Total, MonthName
from (SELECT Standard, COUNT(Standard) AS Total,
datename(month, ReportDate) as [MonthName]
FROM CPTable where
ReportDate >= @ReportDate
[Code] .....
View 5 Replies
View Related
Aug 4, 2015
We have some requirement in PowerBI reports. Here we have a table and having Date, Events columns. Below is the sample data..we are creating a measure to calculate the average of the event count for month.We need a measure for calculating Average of Event count per month= sum(Events for a month)/numberofdays in the month.Example for January month : sum(343423)/31 (31 number of days in January)
When we write this measure using DAX query in Excel we are getting semantic error.Tried sample formula : Average:=SUM([Events])/EOMONTH([EventDate],1)
writing this DAX command for measure.After having this data ready, we are creating PowerBI reports on this data.
View 6 Replies
View Related
Jul 3, 2001
Given any datetime value, I need SQL to derive the last day (numeric) for the month in the datetime value..
eg. "2001-06-22 14:24:56.563" --> Last numeric day of June 2001 is 30
eg. "2000-02-06 23:11:05.323" --> Last numeric day of Feb 2000 is 29
eg. "1999-02-07 08:34:10.037" --> Last numeric day of Feb 1999 is 28
thx...
View 2 Replies
View Related
May 15, 2007
hi,
i am a nubie, and struggling with the where clause in my stored procedure. here is what i need to do:
i have a gridview that displays records of monthly view of data. i want the user to be able to page through any selected month to view its corresponding data. the way i wanted to do this was to set up three link buttons above my gridview:
[<<Prev] [Selected Month] [Next>>]
the text for 'selected month' would change to correspond to which month of data was currently being displayed in the gridview (and default to the current month when the application first loads).
i am having trouble writing the 'where' clause in my stored procedure to retrieve the selected month and year.
i am using sql server 2000. i read this article (http://forums.asp.net/thread/1538777.aspx), but was not able to adapt it to what i am doing.
i am open to other suggestions of how to do this if you know of a cleaner or more efficient way. thanks!
View 2 Replies
View Related
Apr 25, 2007
elo!
actually im using interbase but id like to know the equivalent SQL code so that i can explore how to do it in interbase.
i have a table with a date field. i would like to select all entries which belong to a certain month. this is how i do it:
"select * from table WHERE (cast(datefield as varchar) like :MNT)"
:MNT is a parameter equivalent to "1/%/2007" for example for january
can you please help me convert this statement to a valid query?
"select * from table where month of datefield in [month]"
i hope you can help me.
thanks in advance
View 2 Replies
View Related
Apr 11, 2008
How can i return month in the format of 'April'.
When i am trying select month(getdate()) i am getting 4 but i am looking for text format - April.
Please correct format.
Thanks
Dk
View 2 Replies
View Related
May 18, 2004
hi guys.
I have a datetime column in my SQL server database.. I need to select the value from the table by passing month and year only..
any suggestions..??
Thanks in advance..
:)
View 1 Replies
View Related
Mar 7, 2006
Hi,
Support, I have a table order and the field are orderid,customerid,orderdate. What i need to do is that I want customer order list as how many order per week in a month.
Can anyone help me please.
Regards
View 2 Replies
View Related
Nov 9, 2014
This is my Statement.
SELECT a.FIRMA,
a.PSNR,
a.FELDNR,
a.PFLFDNR,
a.INHALT AS Cardnr,
a.PFGLTAB,
[Code] .....
But it deliver me also rows which are not in valid. I need only rows from last month from 01.10 until 31.10 and this rows are over the time. How can i select dynamically all row between 01.10 and 31.10 ?
FIRMAPSNRFELDNRPFLFDNRCardnrPFGLTAB PFGLTBISKSTNRPSPERSNRPSVORNAPSNACHNA
13980211DU9572014111220321231000043000957xxxxx xxxxxx
13990211DU9552014110120321231000010000955xxxxx xxxx
14030211DU9602014120120321231000086000960xxxxxxxxx
View 10 Replies
View Related
Nov 13, 2015
I have table with below scheme
Year Month Day RELH
------ -------- ------ -------
1973 4 1 50
...... etc
1973 4 30 20
.....etc
1974 5 1 50
....etc
1974 5 30 99
I need to get years where average of RELH of months within year meet
Month 4 Average(RELH) > 33 and
Month 5 Average(RELH) > 60 and
Month 6 Average(RELH) < 33
How can I achieve this?
View 4 Replies
View Related
Jan 13, 2008
How to get all the days of a month using select statement in sql server 2000
please help
thanks
View 1 Replies
View Related
Dec 21, 2006
I tried to find it on the Internet, but was unsuccessful. If someone can help me with a select statement that will return JUST the current month and year or day and month. I would really appreciate it.
Thanks
View 4 Replies
View Related
Aug 16, 2007
Dear All
I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
Thanks Ross
View 3 Replies
View Related
Jul 15, 2013
I have this column Sdate in my Employees table. This value represent the start date working.
I need to get all of the employees that between 8 and 10 month of work from today. How can I do it?
View 3 Replies
View Related
Jun 29, 2015
I have 3 month of record in my table. if i pass 2 and 10, i need to select the record of between 2 and 10 days of record of every month. if i pass 10 and 20, it should select the record between 10 and 20 of every month. How to query for that?
View 8 Replies
View Related
Sep 28, 2007
Hi,
How to select max of year-month in the Reporting Services. For example I have 3 fields :
1. Year-Month (format : yyyy-mm)
2. City
3. Amount Sales
In the bar chart graph, I have put the City as X-Categories, Amount Sales as Data
And I want to filter only the lastest year month in the database.
Can we do this without going to database and set the filter there ?
Thanks
best regards,
Tanipar
View 4 Replies
View Related