.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

returning a date with a count of zero if it doesn't exist in the date range?

Posted By:      Posted Date: October 09, 2010    Points: 0   Category :ASP.Net

i have the following query.

SELECT DATEADD(dd,0, DATEDIFF(dd,0,accesseddate)) AS hitdate, COUNT(hitid) AS hitsperday
FROM hits h
ON h.urlid = u.urlid
WHERE (accesseddate BETWEEN DATEADD(d, -6, GETDATE()) AND GETDATE() AND u.userid = @userid)
GROUP BY DATEADD(dd,0, DATEDIFF(dd,0,accesseddate))

but if there were no "hits" for a particular date range in the last week i only get the dates returned where there were hits. i need to get all the days returned and where there were no results, i need a zero returned.

hitdate            hitsperday

2010-10-07     2
2010-10-06     58
2010-10-04     1645
2010-10-03     1192 

what i need to return is this:     

hitdate                 hitsperday

2010-10-08     0
2010-10-07     2
2010-10-06     58
2010-10-05     0
2010-10-04     1645
2010-10-03     1192

any suggestions?

thank you very much.

View Complete Post

More Related Resource Links

List ALL Dates in Date Range When Selecting Count


Say I have a table called "Visits", and I want to create a query that counts the visits per day. 

The way I would accomplish this would be to create a query as such:

SELECT Count(VisitId) as VisitCount, Convert(varchar(32), VisitDateTime, 101) as VisitDate
FROM Visits
WHERE VisitDateTime BETWEEN '10/1/2010' AND '10/5/2010'
GROUP BY VisitDate

The problem I am trying to overcome is that if there were no visits in a given day, that day does not show in my list.  For example, if there were 3 visits on the 1st, 2 on the 2nd, none on the 3rd, and 5 on the 4th, my result set would look like:

3, 10/1/2010
2, 10/2/2010
4, 10/4/2010

How can I create my query so that I get all days in the date range listed in the result set regardless of whether the VisitCount is zero?


adhoc reporting - data doesn't refresh on date range change


I created an adhoc report model using SQL Server 2008 reporting services. I am using data from a SQL 2000 database sitting on a SQL 2000 server. I created reports with start date and end date parameters, everything worked fine.


Now my ad hoc report model uses a SQL 2000 database (this database is a replication of the database I was using)  sitting on a SQL 2008 server. When I change parameters like start date and end dates, data doesn’t refresh. It shows the same data for any date range.


Has anyone seen this problem? What could be done to fix the issue. Is there any setting on the SQL server to fix this?



If a date is null or doesn't exist return another date



I have 2 dates which are calculated members:

SELECT ReportingMonday ON 0
FROM DataCube

SELECT [FirstCubeDate] ON 0
FROM DataCube

ReportingMonday= StrToSet("[Dim Date].[Date Key].&[" + IIF(WEEKDAY(NOW())<=2,FORMAT(DATEADD("W",-5-Weekday(NOW()),CDATE(Now())),"yyyyMMdd"),FORMAT(DATEADD("W",2-Weekday(NOW()),CDATE(Now())),"yyyyMMdd")) + "]")

FirstCubeDate = HEAD([Dim Date].[Date Key].[Date Key].Members,1)

What I want is a named set to say:

if ReportingMondayis null or blank (so there isn't a report monday e.g. the data started on Tuesday) then return FirstCubeDate

Any Ideas?




ListViewControl - how to group items by their value, for eg. date range



I have a table containing courses, columns are 

Name - "Yoga Classes"

DateStart - "2/23/2010"

DateEnd - "2/24/2010"

I am using ListView Control to bind it, is there a way to display them and group them by Month? 

for eg.


Piano Class (10th Jan 2010 - 11th Jan 2010)
Guitar Class (10th Jan 2010 - 11th Jan 2010)


Yoga Class (15th Feb 2010 - 16th Feb 2010)

Thanks for help.

Need help in Date Range

HI Guys I need help to write a query I have a table Employee: empIDEmployeeName Task StartDt                                EndDt     1      xxx                  1    1950-01-01 00:00:00.000 1979-12-31 00:00:00.000     2     yyy                  2    1980-01-01 00:00:00.000 2009-06-30 00:00:00.000     3      aaa                 3    2009-06-30 00:00:00.000 2010-01-31 00:00:00.000     4     bbb                  4    2010-02-01 00:00:00.000 null when I pass one variable with date like 20070101 I need check the date ranges and get the exact record(this case 2nd records)

Question Regarding Date Range

I have a requirement to confirm how many employees were 'active' on a given date.  The employee table which I'm using contains the following fields (among others):  1.   Row_Start_Date (when an employee started with the company) 2.  Row_End_Date (when an employee terminated with the company (if applicable)) What would be the best way to design this?  For example, let's say an employee started on 1/1/2010 (Row_Start_Date) and is still employeed (Row_End_Date is null).  If an analyst wants to see how many employees were active on 5/1/2010, how do I design it so that this employee is counted.  If I build a date dimension off of Row_Start_Date, then this employee would be reflected on 1/1/2010, but not on 5/1/2010.  Hopefully this makes sense, and I appreciate any assistance/suggestions in advance... KoleKRS

Export Data to Excel Based on Date Range

I have data from multiple tables that I would like to display in a gridview via a stored procedure.  I need to select the data based on a date range and display it.  Once displayed I need to include a button that would allow the user to export the gridview data to an excel spreadsheet.  Does anyone have an out of the box solution or know of a tutorial I can use to accomplish this feat?  I am currently using 2.0 for this project. I am newish to .net. Thanks in advance. Here is my stored procedure that calls the data from multiple tables.USE [DATABASENAME] GO /****** Object: StoredProcedure [dbo].[GetUsersAll] Script Date: 09/03/2010 08:20:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetUsersAll] AS SELECT aspnet_Users.UserName, aspnet_Membership.Email, Team.Name, MemberTeamRole.InsertDate, MemberTeamRole.ManagerApprovedDate, MemberTeamRole.RegistrarApprovedDate, MemberTeamRole.RoleName, MemberInfo.firstname, MemberInfo.lastname FROM aspnet_Membership INNER JOIN aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId INNER JOIN MemberInfo ON aspnet_Users.UserId = MemberInfo.memberid LEFT OUTER JOIN MemberTeamRole ON aspnet_Membership.UserId = MemberTeamRole

MDX Date Range calculated member

Hi, I have [Sales Amount] and [Date]. How can I create a calculated member that will aggregate only the values from [Sales Amount] where month([Date]) is 'Jan' or 'Feb' and year is CurrentYear ? Then 'Mar' & 'Apr' and so on.... Thank you. D.

Date Range Problem

How would I go about returning the closest ReleaseDate to DateIn? The record I would like to return is (2010-07-18 00:00:00.000 2010-07-17 00:00:00.000) CREATE TABLE #test ( ID INT ,DateIn DATETIME ,ReleaseDate DATETIME ) INSERT INTO #test VALUES(1, '07/18/2010', '01/10/2010') INSERT INTO #test VALUES(2, '07/18/2010', '07/17/2010') SELECT * FROM #test WHERE CONVERT(CHAR(10), '07/17/2010', 101) <= CONVERT(CHAR(10), '07/18/2010', 101) AND CONVERT(CHAR(10), '07/18/2010', 101) <= CONVERT(CHAR(10), '01/10/2010', 101) DROP TABLE #test  

Date maskedEdit with watermark doesn't update on invalid date


I'm trying to use the TextBoxWatermarkExtender with MaskedEditExtender/Validator and have found a bug (I think).

In the textbox I enter an invalid date such as 55-55-5555 and exits the textbox. I would then expect the watermark to be shown, but it doesn't. Instead the prompchar is shown (like __-__-____).

If I simply give focus to the textbox and then another control, the watermark is shown.

Is there a workaround such as manually force the TextBoxWatermarkExtender to update.

I'm not the best with javascript, I tried to call focus() and blur() on the textbox in the textbox's onBlur event using window.setTimeout. This works in Firefox, but not IE8, also there has to be a better way I think :-)

            <asp:TextBox ID="TextBox5" runat="server" MaxLength="1" onBlur=""/>
            <asp:ImageButton ID="ImgBntCalc" runat="server" ImageUrl="~/images/Calendar_scheduleHS.png" CausesValidation="False" />

        <ajaxToolkit:TextBoxWatermarkExtender ID="TBWE2" run

T-SQL query, average of daily time periods over a date range



I'm building a report in Crystal Reports using a SQL command against a T-SQL 2005 telephony database.

I need to be able to run the query across a given datetime range, 6 months for example and bring back a 5 day display (Mon-Fri) with a group for every 15 minute interval in each day.

The group figures need to contain an average of the amount of calls presented for each 15 minute interval on any day across the whole datetime range, so for example the Monday 10:00 - 10:15 figure would be an average of all calls presented in every 10:00-10:15 range on each of the Mondays that fall within the datetime range.

I've got a query built now that gives total presented figures grouped by these intervals across one week but I can't figure out how to do this average function across a range.

Does anyone have any idea how I'd go about accomplishing this? I'm pretty new to SQL but keen to learn so any pointers on functions to research etc would be very much appreciated.

Thanks alot in advance, Andy.

Query for detail view across one week included below for table/fields etc...



count(DISTINCT Calls.SessionID) as Presented, min(Calls.startDateTime) as DateTime

INNER JOIN QueueDetail
ON Calls.sessionID =  Queues.sessionID
AND Calls.sessionSeqNum =  QueueDet

Validate (Javascript) if input date is within this range...


I'd like to validate if the inputed date is within this range:

3 weeks before today's date to 3 weeks after today's date 

The following Javascript code checks if the date is within either today's date or 2 weeks before:

var dateSystem = (dateToday.getMonth() + 1) + dtCh + dateToday.getDate() + dtCh + dateToday.getFullYear();
            var prev = new Date();
            //past date 14 dates before today
            prev.setDate(prev.getDate() - 14);
            var pastDate = (prev.getMonth() + 1) + dtCh + (prev.getDate()) + dtCh + prev.getFullYear();
            var time1 = new Date(dateSystem).getTime();
            dtStr = month + dtCh + day + dtCh + year;
            var dateuser = new Date(dtStr);
            var time2 = dateuser.getTime();
            if (time1 - time2 <= 1209600000 && time2 <= time1) {
                return true;
            } else {
                alert("Vaccines/Services date must be within this date range: " + pastDate + " to " + dateSystem);
                return false
            return true


I'm not sure how to modify. I actually got the code above from these forums.

Help is much appreciated!

Performance problem with date range JOINs



when I am joining two tables where one table has two columns which specify a date range and the other table has one column with a single date which must be in that range for a join, then the performance is not so hot. The T-SQL example only shows the basic query scheme, in reality there are appropriate indexes (but not on the date columns since I found them not helpful) and the DateRange column has about 100 mio rows and the Incident table about 200,000 rows. The query currently takes hours, I must speed it up by at least factor 10.

TIA for any ideas. Regards

	[DateRangeID] [int] NOT NULL PRIMARY KEY,
	[RangeTypeID] [smallint] NOT NULL,
	[StartDate] [date] NOT NULL,
	[EndDate] [date] NOT NULL)

	[IncidentID] [int] NOT NU

Query acting weird.. larger date range works in 1min 26sec and a smaller range say 1 month - 3 month


I have no clue why my query is acting weird

If i try to run it for 1/1/2010 9/30/2010 the query takes around 1min 26 sec and  return around a million rows

and If i run for 8/1/2010 to 8/31/2010 it takes forever to it...

basically i am getting data from 5 tables and putting it in a temp table and then updating that temp table 2-3 times with some information and then displaying it.

I am stumped as to why it works fast for a longer date range and runs the  snail for a small period of time..

I am in the verge of pulling my hair and going crazy..

any help will be appreciated.




remote execution of ssis package using wcf doesn't like date parameter


I have a ssis package remotely executed by a C# windows application that runs ok in context of SSIS installed on the desktop; I created a wcf windows service that the C# application executes, creates a copy of the package with the date in the variable, but errors on the first SQL task component that requires the date. The copy executes using the SSIS utility. What is possibly the issue with wcf and the date type in the SSIS package? Here is the error:

OnError,SRVR2BLADEA3,MIC\1347259,Clear CII ImportDate,{AB3CF5BA-C114-40F6-9ECB-4C7620D97829},{8B1C10CB-580F-4BFE-BC7D-0B97C181E307},9/27/2010 3:29:56 PM,9/27/2010 3:29:56 PM,-1073548784,0x,Executing the query "" failed with the following error: "The type initializer for '<Module>' threw an exception.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Here is the code:

case @"E:\SSIS Packages\CIISCImport.dtsx":

//string dtValue = (prms[iPrms - 1].Split('=').GetValue(1).ToString()) + " 12:00";

vars["User::ImportDate"].Value = DateTim

Date range checking


Suppose I have a date range:

             15/09/2010  -  25/09/2010 

my user will provide another date range for example:       17/09/2010  -  22/09/2010

I want to check that user provided date range is within my date range.

Checking works if both of user provided dates are between my range. But I need to check if one of user provided date is in my date range and another is out of my date range. For example:

07/09/2010  -  22/09/2010

17/09/2010  -  27/09/2010

Here 22/09/2010 is in my date range and 17/09/2010 is also in my range.

I need to write a sql query that check user dates are in my dates.

Any help will be appritiated.

Date range queries


Hi All,


We have several reports where the user can specify a date range for the where clause, i.e. {[Date].[Date].[01 Jan 2008]:[Date].[Date].[ 01 Jan 2010]}. Since the date range can be very large, we are just wondering if the query processor can generate “smarter” execution plans so that the Aggregations on year and month level can be used…


Thanks in advance for your help and input.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend