.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 Customer Count of all categories in each time period

Posted By:      Posted Date: September 19, 2010    Points: 0   Category :Sql Server


Lets say I have the following Table:

CustomerNumber | Date | MovieCategory
1 |2010-01-01|A
1 |2010-02-01|A
1 |2010-03-01|A
1 |2010-04-01|A
1 |2010-05-01|A
1 |2010-06-01|D
1 |2010-07-01|A
2 |2010-01-01|A
2 |2010-02-01|A
2 |2010-03-01|D
2 |2010-04-01|D
2 |2010-05-01|D
2 |2010-06-01|C
2 |2010-07-01|C
3 |2010-01-01|B
3 |2010-02-01|B
3 |2010-03-01|B
3 |2010-04-01|C
3 |2010-05-01|C

What I want to do is count the number of customers for each movie type in each date. Now I started with the following simple query

Select Date, MovieCategory, Count(CustomerNumber)
From AboveTable
Group By Date, MovieCategory
Order by Date, MovieCategory

which would be okay, but if no customer has a MovieCategory of say 'B' on a particular date then the results of the above query won't return a row that has

Date| B| 0     (date here could be any date).


which is really what I need. Is there a good way to

View Complete Post

More Related Resource Links

AGGREGATE over a time period: how to behave like a distinct count ? (aggregate on the full period an


I would like to compute a measure "MyMeasure" over the last 12 months :

Aggregate({[Event DATE].[Calendar].CurrentMember.Lag(11):[Event DATE].[Calendar].CurrentMember},[Measures].[MyMeasure])

MyMeasure is not a distinct count, so the results returned is the SUM of the distinct count performed on each month over the last 12 month.

What I would like to have is the distinct count over a full year (all months taken together).

For example, suppose that MyMeasure is a simple measure that always returns 1.

The aggregation returns 12 (1 for each month then SUM). I would like it to return 1 (measure on the whole period).

Can somebody help me that's really a very big issue I have to fix !


Measure - New Customer Count

I need to create a measure which counts the number of new customers for each time period. My fact table contains the customer number and its easy to create a distinct count of customers per month/week/year. I'm thinking I need to obtain the first order date for the given customer and compare if the select period is within the time frame, then include or exclude.  

The semaphore time-out period has expired

Hi, We have a job that does use linked server to copy some data from a MS SQL 2000 database and insert it into a database which runs in MS SQL Server 2005.This job is configured in the 2005 server. Offlate we started encountering the error OLE DB provider "SQLNCLI" for linked server "RSSDR" returned message "Communication link failure". [SQLSTATE 01000] Error Number::121 Error Message::TCP Provider: The semaphore time-out period has expired. Initially thought its a network issue but network team have confirmed there were no network issue at the specified time.Is it something related to network adapters,card or some other firware. Any help would be highly appreciated. Best Regards, Zainu    

MDX Query Measure with time period

Hi! How to set up the period in MDX? I need the measure what would be seted up by default 4, 29… days from today. For axample Amount from current day (02.09.2010) till 4 days from today is 06.09.2010. As Result I have to get a report: Buy form Vendor Amount today 02.09.2010 Amount +4days 02-06.09.2010 Amount +29days 02.09-30.09.2010 A 10000 250000 333333 B 150000 222222 555555 C 666666 444444 1222222 Sincerely, Milena

Count numbers of days (no time table in datasource)

I read the following regarding adding a measure for counting the number of days: The trick is to create new measure group, which will have only one dimension - Time, and a measure with Aggregation Type 'Count' bound to the Day attribute of Time dimension (or any other attribute if we need to count something different from days). The most important setting on this new measure group is to leave IgnoreUnrelatedDimensions=true - this will allow to use this measure across any other dimension in the cube. Howerver, this solution assumes that one has a time dimension based on a table in the datasource. I have a Time dimension that created was by the BIDS wizard that is not in my source database but appearantly invisibly hidden on the AS server without direct access to it. (If I try to add a new measure group (as needed according to the post above) then I can only choose from tables that have nothing to do with time). Can somebody explain how I can do this without a specific timetable in my datasource? Do I need to add a timetable just for this purpose? Can it be stand-alone or do I need to add a relationship with all current tables that have date-columns in them? (I am a finance manager that is building a sales cube based on Navision 3.6, which is already working quite nicely, but still very much a newby)

SPD to escalate workflows over a period of time

Hello,   I am hoping that you can help me, I have designed an InfoPath form that I am using for an expenses approval, but I would like to set it so if the approver has an Out of Office, the workflow is submitted to a different user. And if the workflow exceeds a specific time range then the workflow is escalated.   I am using SPD to create these workflows, rather than the in built MOSS workflows.   You're help, as always, is greatly appreciated.   Kind Regards, Dayna

Passing checkboxlist variables and returning one row at a time

I am designing a page for my students to practice their Latin vocabulary.  I am having two problems with the design: 1) working with checkboxes, and 2) display one returned row at a time. 1) First, students select from CheckListBox1 those parts of speech (nouns, verbs, adjectives, etc.) they want to review, and then they click Button1 ("Submit"), which fires off a SELECT statement against a table named CoreVocab in a SQLEXPRESS database.  How do I pass each selected part of speech from CheckListBox1 as a variable into the WHERE statement when the user clicks Button1? Do I place this code in the Button1_Click on the .cs page? 2)  Ideally, the table returns the first row and Button1 changes to "Next," which the student then clicks to see the next Latin word.  How do I get the Repeater table to show only one row at time until the Next button is clicked? And how do I get the same button to executed two functions (fire off the SELECT statement and then show the next row)?  For what it's worth, I'm using the SqlDataReader command, since data will only be retrieved, not modified. Thanks. I'm new at this.  But I really need to do something for my suffering students. Oliphant

How to concatenate measures over time period?



For an external tool I have to prepare Sales ([Measures].[Sales]) figures for the last 5 months like this: 12345|12343|21423|12344|12343. So basically, I need one measure that displays these values concatenated. How can I create this? (In my MDX query I would have this measure on columns and i.e. another dimension like country on rows).



SSAS2005 Cube - Watch Customer on two points in Time



I need some help on the following problem:

I have got a customer status, let's say N for New, A for Active, I for Inaktive.

When a customer buys the first time his status will be N.
When he buys a second time his status changes to A.
After some time without an order his status is set to I.

This status changes are recorderd in a history table.

The question:

How many customers with status "N" at date(t1) and how many of those customers have status "N", "A" and "I" at date(t2).

e.g. on 01. Jan. 2010 i have 500 customers with status "N".
On 01. Jul. 2010 i want to know how many of those 500 customers from the 01. Jan. have status "A".
Let' say 200 have status A.

Output should be t1_U = 500 and t2_A = 200.

Would it be posible to do answer this question in a cube?
I have tried out some things but didn't get an plausible result.
With 2 messures (one for point1 in time, a second for point2 in time) it will not give me 200 in the second messure, but 220. I think a there is missing a relationship between the two messures on customer base.
So at point 2 there are 20 more customers with status "A" on point2 in time thats right, but they didn't exist on poi

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


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.

Customer Timer Job runs at different time


Hi All,

I have a custom timer job that I set to run at 8AM every day.(code below)

It does not run at 8AM instead it runs randomly during the day(but only once though)

How do I make sure that it runs at 8AM every day. Anything wrong in my code:

Thanks in advance.



override void FeatureActivated(SPFeatureReceiverProperties properties)



// register the the current web


Sharepoint site getting time period expired



I am having a file which is contain 70,000 data. i try to upload this file into sql server table using front end sharepoint site.i am getting time out period expired error.

But i have added connection timeout=100000 property in connection string still i am facing the same problem

So please help me to solve this issue



How to filter customer count on the previous 3 months


Hi, I  want to see  the  total  number  of  new customers  for  the  last  3  months  and also the  total  the  total  number of  existing customers.I have created a meaure called "Customer Count" which  gives me  the  total number of  customers. How will  I use the date range in MDX  to filter these?

I  want to see  this  in MDX for  a  KPI. Can anyone  help  out?




Returning record from History Table as it would have existed at a specific date/time


I have a table that uses a trigger to save changes (old values) to a history table. This process is working fine, and I have reports that detail the history of a record for the end user. My problem is that the end user now wants a report that will retrieve how the record looked at a specific date/time.

Source Table:

Id	First	Last	Active	Date
1	John	Doe	1	10/25/2010
2	Jane	Smith	1	10/25/2010

History Table:

Id	SourceId	First		Last	Active	Date
1	1		Jonathon	NULL	NULL	5/1/2000
2	2		NULL		Gray	NULL	10/1/2000
3	2		NULL		Smith	NULL	6/15/2003
4	2		NULL		Doe	NULL	11/23/2008

If the user queries for SourceId 2 on 1/1/2009, they need to get:

Id	First	Last	Active
2	Jane	Smith	1

I've used COALESCE searching multiple fields, but I need to figure out how to

NetworkStream ReadTimeOut doesn't wait for the time out period



I am using the TCPClient and the NetworkStream classes to establish a TCP connection and read the data from the network.
The NetworkStream object is set with a time out of 15 sec. The intent is, if the stream can't see any data from the network within 15 sec,
it will throw an exception and return. Something like myStream.ReadTimeOut = 15000. I set this time out once in the beginning just after the TCP connection  and the NetworkStream are created.

Though majority of the times (70%) it behaves nice and waits for the time out period to expire before throwing the System.IOException,
other 30% of the times, it just doesn't wait 15 sec before throwing the exception. It doesn't even wait for 2-3 milliseconds before throwing the exception.

I verified this using another tool, wireshark to time and detect the traffic.

Is the NetworkStream object doing some kind of optimization? Or do I have to do anything special to make it to wait for 15 sec?


Tech Crawler


Count people quantity for date period



I have following table:

id          staff_id                store_No                start_date                 end_date

1              st1                          s1                     09/25/2010                10/5/2010      

2              st1                          s2                     10/06/2010       

SQL Server Query request for start and end period of time


Hi all

I have event table where I need to select records between days. my statements look like

  CONVERT(char(8),Event_Table.Event_time,112) BETWEEN 'request start date' AND 'request end date'

The Event_time is DateTime format.


Now everything look fine except from if I need the statement to request the date between as Date_start and Date_End any time it run. The idea is to request a for start and end period of date any time the scrpt run. the script will be run as DOS batch file. 

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