I am pretty much brand new to reporting services so I apologize for any simple questions I may ask.
I have created a call report that shows data broken down by day and grouped by week. Everything works great. However, if there is no data on a certain day is does not show me that day on the report. Is there a way I can make it display the day even if there is not data for that day and just give me 0 values? Or do I need to modify my query?
Thanks in advance.
Hello,I would like to show the result fo each day of the past 30 days. I might not have data to display for everyday but I still want the date to show up01/27/2007 resultA01/28/2007 resultB01/29/2007 .....02/26/2007 resultC02/27/2007 resultD
Thanks for your helpArnold
I am using sqldatasource to make a connection to my access database. I have a table with a field called "last_activity" (Date/Time datatype).I want to make a query which shows users/players (the rows in the table) that have been active the last 14 days, so I have to use "last activity" as parameter.But how do I code this? <asp:SqlDataSource runat="server" id="race_a_car_Source" ProviderName="<%$ ConnectionStrings:speedracerConnectionString1.ProviderName %>" ConnectionString="<%$ ConnectionStrings:speedracerConnectionString1 %>" SelectCommand="SELECT * FROM [race_a_car] WHERE ([last_activity] >= ?)"> <SelectParameters> <asp:parameter DefaultValue=" what should I write here? " Name="last_activity" Type="DateTime" /> </SelectParameters> </asp:SqlDataSource>
View 3 Replies View RelatedI want to show this kind of output
UserID UserName 1 2 3 30
OR
UserID UserName 1 2 3 31
user data saved in db select distinct UserID,Name from Userss Where IsActive=1 and order by UserID and i want to just calculate no of days in month based on year and month name supplied by user. one way i can do it. first i will create a temporary table and in loop add many columns to that table and later dump user data to specific column.
I have an SQL code below which removes weekends and non working days when calculating days difference between two dates:
ce.enquiry_time represents when the enquiry was logged
(DATEDIFF(dd, ce.enquiry_time, getdate()) + 1)
-(DATEDIFF(wk, ce.enquiry_time, getdate()) * 2)
-(CASE WHEN DATENAME(dw, ce.enquiry_time) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END)
-(SELECT COUNT(*) FROM nonworking_day WHERE nonworking_day.nonworking_date >= ce.enquiry_time AND nonworking_day.nonworking_date < dateadd(dd,datediff(dd,0,getdate()),1))
It works but I don't understand how it works it out. I am having issues understanding each coloured piece of code and how it works together.
Hello,I am writing a query to select records added to a table today, in the last 3 days, in the last 7 days, and so on.Here is what I have (which seems that its not working exactly). -- total listed today
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 0-- total listed yesterday
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 1-- total listed in the last 3 days
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 3I'd like to be able to select the count for records added within the last X number of days. Can someone please help me out? Thanks so much in advance.
Hi all,
I have two problems.
1. I downloaded ChartFXRSTrial and created one chart, and able to deploy it in ReportServer, but the problem is the reports are not showing there, i checked the configuration of .dll files in the help provided by chartFX, but couldn't get anything, so could help me on this.
2. How to give Tooltip for the X- axis or Y- axis values in the ReportServer , i tried using chartproperties of .rdl file, but didnt understand it. can help me on this, and one more i tried with Dundas too, If im giving tooltip as #valy then, it is showing samething in reportserver instead the values of 'Y-axis'.
Thanks,
Mahesh Manthena
How would I bring back a column that shows all the days from the begining of the year to the current date. Also make sure this moves onto the next year.
View 9 Replies View RelatedDid a bit of searching, but couldn't find it. I'm looking to get data from the past 7 days:
select * from inquiry
where created_date is within the last 7 days
Thanks,
Hey heyI've written my site in asp.net 2 and have set up roles and various logins etc which work beautifully locally. Now, when i upload to my host I get the: An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be
caused by the fact that under the default settings SQL Server does not
allow remote connections. (provider: SQL Network Interfaces, error: 26
- Error Locating Server/Instance Specified)The thing is, I've checked my connection string a million times and it's perfect, I've also checked all the database settings on my host and they're all fine. However, when i use SQL Server Management Studio Express to copy my database from the local to the remote server (from a tutorial on these forums), I get the "Operation completed successfully" message yet when I expand my tables, there is no data in them. I've tried copying and pasting the data in manually but i still get the same issue. It's driving me absolutely insane! Anyone any ideas?Thanks
Hi all,
I have a table in which I have two fields in my DB.
FromDate and ToDate.
Both are stored as Varchar(MAX).
I would like to have an SP which gives me the Days in Between them.
Regards,
Naveen.
Is there any way to find out in sql server that whether it is Monday to friday or saturday sunday.I have to write a stored procedure in which the logic shoukld be done only if there is a weekend.if saturday or sunday select * from status_tempPlease let me know if there is any function that is available in sql
View 4 Replies View RelatedIs there anyway to get SQL to convert a date to days of the week?
Example, I have a query where the date is "01/01/2006" but I would like it to display "Sunday". Thank you.
How can I select records from a schedule table that have end dates older than 90 days?
Can I do getdate() -90?
Code:
select *
from dbo.schedule
where enddate <> endate-(90 days?)
Hi,
I just want to know, is there anything more better solution than this one to find out the no of days in a month ?
I have done this but I am not satisfied,anybody has a smarter solution?
Plz comment..
My Solution :
/* check leap year*/
if year(getdate())%4<>0
set @noofdays=(select case month(getdate())-1
when 1 then 31
when 2 then 28
when 3 then 31
when 4 then 30
when 5 then 31
when 6 then 30
when 7 then 31
when 8 then 31
when 9 then 30
when 10 then 31
when 11 then 30
when 12 then 31
end )
else
set @noofdays=(select case month(getdate())-1
when 1 then 31
when 2 then 29
when 3 then 31
when 4 then 30
when 5 then 31
when 6 then 30
when 7 then 31
when 8 then 31
when 9 then 30
when 10 then 31
when 11 then 30
when 12 then 31
end)
Joydeep
I have a little query that returns me all the days in a month, but the days are not in the order I need. They come out like so..
January 10
January 11
January 12
January 13
January 14
etc
January 19
January 2
January 20
January 21
January 22
here is my sample code
select [month] + ' ' + [day] as [Date] from mytable
where [month] = 'january'
and [year] = '2004'
Thanks, Jeff
Hi, I used the following query to update my columns according to the change of time and days of the week. But my query is not working as it should. Today is Tuesday and the time now is 9am so according to my query Mon_Night column should be updated but Sat_Night column is being updated when i execute the query. Please advice..
declare @Weekday bit, @hour int
select @Weekday = datepart(dw,getdate()),@hour= datepart(hh,getdate())
Update Weekly set
Sat_Night=CASE WHEN (@Weekday= 1 and (@hour> 7 AND @hour<=19))
THEN ALD.EngTime ELSE NULL END,
Sun_Day=CASE WHEN (@Weekday= 1 and (@hour > 18 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END,
Sun_Night=CASE WHEN (@Weekday= 2 and (@hour> 7 AND @hour<=19))
THEN ALD.EngTime ELSE NULL END,
Mon_Day=CASE WHEN (@Weekday= 2 and (@hour > 18 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END,
Mon_Night=CASE WHEN (@Weekday= 3 and (@hour> 7 AND @hour<=19))
THEN ALD.EngTime ELSE NULL END,
Tue_Day=CASE WHEN (@Weekday= 3 and (@hour > 18 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END,
Tue_Night=CASE WHEN (@Weekday= 4 and (@hour> 7 AND @hour<=19))
THEN ALD.EngTime ELSE NULL END,
Wed_Day=CASE WHEN (@Weekday= 4 and (@hour > 18 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END,
Wed_Night=CASE WHEN (@Weekday= 5 and (@hour> 7 AND @hour<=19))
THEN ALD.EngTime ELSE NULL END,
Thu_Day=CASE WHEN (@Weekday= 5 and (@hour > 18 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END,
Thu_Night=CASE WHEN (@Weekday= 6 and (@hour> 7 AND @hour<=19))
THEN ALD.EngTime ELSE NULL END,
Fri_Day=CASE WHEN (@Weekday= 6 and (@hour > 18 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END,
Fri_Night=CASE WHEN (@Weekday= 7 and (@hour> 7 AND @hour<=19))
THEN ALD.EngTime ELSE NULL END,
Sat_Day=CASE WHEN (@Weekday= 7 and (@hour > 18 AND @hour < 7))
THEN ALD.EngTime ELSE NULL END
from ALD join Weekly on Weekly.TesterID = ALD.TesterID
How would I do a date range from the first day of this year to the getdate(), and so it will change once the new year hits as well?
View 6 Replies View RelatedI've have these following table
tbllocation
Main_ID | Date_Taken | Time |Hit
-----------------------------------------
206 | 5/9/2008 | 100 | 2
206 | 5/9/2008 | 200 | 3
206 | 5/6/2008 | 300 | 6
201 | 5/1/2008 | 400 | 5
201 | 5/4/2008 | 500 | 9
201 | 5/7/2008 | 600 | 2
204 | 5/2/2008 | 700 | 2
204 | 5/3/2008 | 800 | 4
204 | 5/6/2008 | 900 | 2
203 | 5/7/2008 | 100 | 2
203 | 5/8/2008 | 200 | 3
203 | 5/9/2008 | 300 | 6
202 | 5/4/2008 | 400 | 5
202 | 5/3/2008 | 500 | 9
202 | 5/8/2008 | 200 | 3
205 | 5/2/2008 | 300 | 6
205 | 5/1/2008 | 400 | 5
205 | 5/9/2008 | 500 | 9
tblSetValue
Main_ID | Hit2
---------------
206| 3
201| 5
204| 3
203| 1
202| 8
205| 7
*Main_ID is a primary key
Condition
1. Let's say, the current date is 5/9/2008
2. Result only display the last 7 days data. From above data. it's mean only pickup from 5/3/2008 to 5/9/2008
3. Every Main_ID only pickup the MAX Hit
4. Diff (column on the fly) = Hit - Hit2
The expected result shown as follow
tblResult
Main_ID | Date_Taken | Time | Hit | Hit2 | Diff
-----------------------------------------------
206| 5/6/2008 | 300 | 6 | 3 | 3
201| 5/4/2008 | 500 | 9 | 5 | 4
204| 5/3/2008 | 800 | 4 | 3 | 1
203| 5/9/2008 | 300 | 6 | 1 | 5
....
....
....
Anyone can help me to built the query?
dear gurus,
I want to get the working days between two days..
in a single query.
i will give the start_date '06-02-2008',end_date '06-13-2008' the result should be as below.
06-02-2008Monday
06-03-2008Tuesday
06-04-2008Wednesday
06-05-2008Thursday
06-06-2008Friday
06-09-2008Monday
06-10-2008Tuesday
06-11-2008Wednesday
06-12-2008Thursday
06-13-2008Friday
Thanks in advance.
cool...,
Dear gurus..,
I need to get all the days in a year in a single query.
Thanks in advance.,
cool...,
Hello,
I have a table with a field Expiration of smalldatetime. I want to only display the data that has 60 days or more between Expiration and todays date. Any help would be highly appreciated.
I am looking to do the following:
CurrentDate - admit date /24 (round down to whole number)
I Have a date range and i want to calculate the number of days - the weekends(saturdays and sundays) so that only businessdays are obtained.
A simple table's example:
Name, surname, datebeg, dateend
How can can it be done with sql?
BB
So Im trying to find the number of days in a certain month I know how I want to do but cant figure out how to code it
i want to take the first date of the getadate() and then ad a month then subtract 1 day and ill have the number of days in a month how would i code that?
Hi Everyone,I use the following to get records from the last two days in MySql:where date_entered <= curdate() and date_entered >=DATE_SUB(curdate(),INTERVAL 2 day)I'm looking to do the same in MS-Sql server but I'm just not getting it.I've got this so far which does not work:where hit_date <= GETDATE() and hit_date >= DATE_SUB(GETDATE(),INTERVAL 2day)then I tried this:WHERE hit_date >= DATEDIFF(GETDATE(), (GETDATE()-2)>Essentially, I need all records from the last two days.Any help or guidance in this matter would be greatly appreciated.-JohnyB
View 5 Replies View RelatedHi,
I want to get the no of saturdays that are coming between given span of days,
ex:- if i give dates as 04/05/08 and 04/25/08 , i want to get the no of saturdays between these dates
can anyone help
I have an integer based on
1 Sunday
2 Monday
4 tuesday
8 weds
16 thurs
32 Fri
64 saturday
if multiple days were selected it would use a logical or such as weds and tues = (4+8=12) I am trying reverse get the days back. What is the best to do that. Thanks
Ludwig
Hi,
Does anyone has the DAYS360 excel formula in a function in sqlserver ?? I did this one
FUNCTION dbo.fnDays360_EXCEL
(
@startDate DateTime,
@endDate DateTime
)
RETURNS int
AS
BEGIN
RETURN (
(CASE
WHEN Day(@endDate)=31 THEN 30
ELSE Day(@endDate)
END) -
(CASE
WHEN Day(@startDate)=31 THEN 30
ELSE Day(@startDate)
END)
+ ((DatePart(m, @endDate) + (DatePart(yyyy, @endDate) * 12))
-(DatePart(m, @startDate) + (DatePart(yyyy, @startDate) * 12))) * 30)
END
But there is a bug, if the end date is bigger then february, february must have 30 days and not 28 or 29...
Does anyone has the solution ?
Thanks
Hello,I am writing a SQL query to count records in a table from the last "X" number of days.There is a dateTime column that I want to base this off of:Lets say.. I want to count the records that were added in the last 3 days. What is the query I should use to get this value?Here is what I have and it seems to not be working correctly:SELECT @TotalListedLast3Days = COUNT (*) FROM myTable WHERE DATEDIFF(Day, DateAdded, getdate() ) <= 3Can someone help so I am able to get the count of records added in the last "X" days?Thank you very much.
View 2 Replies View Relatedi have id, start_date, end_date
View 2 Replies View RelatedBottom is my table structure. please some one can give me the sql code to add 3 days to the existing date.
SemesterID intSemesterLongID varcharSemesterIdentifier charSemesterIdentifierName varcharSemesterName varcharSemesterStart datetimeSemesterEnd datetimeSemesterNameAlt varcharEnrollmentDeadline datetimeRegistrationStart datetimeRegistrationEnd datetimeLengthInWeeks varcharBulletinStartDate datetimeBulletinEndDate datetimeClassroomStartDate datetimeClassroomEndDate datetimeFacClassStartDate datetimeFacClassEndDate datetimePlanningStart datetimePlanningEnd datetimeGoToTeach datetimeTOButAppears datetimeTOButDisappears datetimeSignature datetimeTOButDue datetimeBookListDue datetimeProfAccess datetimeExamDue datetimeSCGDue datetimeGradesDue datetimeExtGradesDue datetimeTOExt datetimeSessionPlanning datetimeTODue datetimeSCGStart datetimeTOExpire datetimeSRPTOCDue datetimeSRPCopyDue datetimeSRPCCCFormDue datetimeTOID intSemesterIdentifierID intCode varcharTitleIVDescription varcharCreatedBy varcharModifiedBy varcharDateCreated datetimeDateModified datetime
Hi,
I have a textbox with date selected from calendar... Now i put in another textbox to enters number of day(30 or 120 or other), this number may vary...
so my qns is how to add the n numbers of days entered to the date selected? i stored date as smalldatetime
Help is appreciated