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

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

How to get these counts.

Posted By:      Posted Date: October 22, 2010    Points: 0   Category :Sql Server

I have one table with countries code..










i need to get the count of these countires as

local 3 ,foreign 3 in single query....

without writing procedure....


plz suggest...

View Complete Post

More Related Resource Links

SQL Reporting Services - Page X of XX counts in header

I have searched high and low for how to calculate page numbering in the header where the global values do not apply.   I have a report that generates 300+ invoices and I need the page numbers to reflect page x of xx for that invoice only.  My report page breaks for each customer invoice.  I have found code to get the page number in the header and I have found code to get the total pages in the body.  These two solutions cannot be combined.    Has anyone found a workaround in code behind or some other method?I would appreciate any guidance.   Side note: thank you all for all your postings and solutions.  They have been highly valuable as I am new to SSRS.

Getting tags and their counts for a given URL Base?

I'm fighting with the SocialTagManager since a few hours to get something incredibly simple done: For a given URL Base, give me all tags (from the Tags and Notes) and how often they are used, if the tags start with a certain string. So I have a Page http://myportal/SomePage.aspx that is called with an ID, like  http://myportal/SomePage.aspx?ID=1 http://myportal/SomePage.aspx?ID=2 etc. Each ID-Page can be tagged by users.   In SQL, this is trivial:   SELECT TOP 10 InputTermLabel, COUNT(InputTermLabel) AS Count FROM dbo.SocialTags WHERE InputTermLabel LIKE 'su%' AND UrlID IN ( SELECT UrlID FROM dbo.Urls WHERE Url like 'http://myportal/SomePage.aspx%') GROUP BY InputTermLabel ORDER BY Count DESC   I can't find a proper way to do this through the Object Model. My current approach is this:   public IDictionary<string, int> GetTags(string query) { var result = new Dictionary<string, int>(); using (var site = new SPSite(_siteId, SPUserToken.SystemAccount)) { var baseUrl = Config.GetSocialUrlBase(site.RootWeb); var stm = new SocialTagManager(SPServiceContext.GetContext(site)); var terms = stm.GetAllTerms(new Uri(baseUrl),0); foreach (var term in terms) { if (!term.Term.Name.StartsWith(query, StringComparison.InvariantCultureIgnoreCase)) continue; var tc = stm.GetUrls(term.Term); int usageCoun

A tsql report with multiple counts and pivioted description attributes

Hello,   I have an assignment in which I am to prepare a report displayed in excel to return counts of various attributes in the database. However, the count is a bit challenging because it requires counting certain attributes in a pivot format illustrated below. The counts for each category by recruiter, rolled up by Division, within the specified time frame.   The tables that I would have to deal with are as follows:   User: Has the number of Accounts…user accounts with no Recruiter are unassigned Recruiter: Has the recruiter information LoginHistory: Has a record of all logins EForm: Has the various forms, for example: Release and Authorization, Provider Service Agreement EFormUserXref: You can filter it to return the Physican AppForms                                                                       Surgery                                      &n

Getting counts by 2nd Date Dimension Attribute with Snapshot Style Fact Table

  I have an MDX question finding hard to solve.  I have a Snapshot Fact Table with a snapshot of the records in the source system for each batch date.  All records in the fact table are assigned the batch date with the batch date key.  There are many records for each day and each batch date is an entire copy of the source records.  So, the grain of the fact table is one record for each batch date that exists in the source system.  These facts rows have another date in them for when the record was entered.  This date is different from the batch date in that the batch date is based on the day the batch was processed and the entered date is based on when the record was entered.  If a record was entered many days before, its batch date will be today but its entered date will be several days ago.  Therefore each day a copy of all the records entered the previous batch date and all the records added on today's batch date are present. Fact Table : FactSnaphshotKey (surrogate for easier administration) BatchDateKey (link to batch date dimension – date dimension, first in dimension list so it is used for semi aggregate measures) EnteredDateKey (link to entered date dimension – date dimension) Facts Count – measure for fact table - default measure from Analysis Services cube 2 Dim

left and right counts in tree in sql server 2000

hi all.,i need a help in sql server 2000 stored procedure which gives left and right tree counts to parent without using curzors..using curzors it is dead slow to execute the store procedure when the tree grows.. so i need just left and right count of parent node..can any one help me on this problem. thank you. 

Need multiple distinct counts, have 1 fact and 1 dimension

I am using SSAS 2005. I have 1 fact table and 1 dimension. I would like to create multiple distinct counts in 1 Measure group, at least I would like them to appear as in 1 measure group to end-user. I have tried role-playing dimensions, and a roll your own approach that work in limitation but didn't scale. Any help and advice would be great.

Help with Percentage Counts for Prior 3 months

I am trying to calculate the counts of issues for the prior 3 months as a percent of the total of all issues for the prior 3 months. eg. For Sept. I would need the counts for Jun, Jul, Aug for each issue type divided by the total number for all issue types for those 3 months..

For the numerator I used the following and have confirmed that it is correct via SQL queries. 

CREATE MEMBER CURRENTCUBE.[Measures].[PctUniqueIssuesNumerator] AS Aggregate
( {[IssueDate].[ProgMonth].CurrentMember.Lag(3) : [IssueDate].[ProgMonth].CurrentMember.Lag(1)}
    [InvoiceIssueType].[Invoice Issue Type].CurrentMember
    [Measures].[Invc Id Distinct Count - IssueFact]

For the denominator I am using this MDX but doesnt seem to be correct. Should I be doing it differently? I need the sum of [Invc Id Distinct Count - IssueFact] for all issues types for the prior 3 months.

CREATE MEMBER CURRENTCUBE.[Measures].[PctUniqueIssuesDenominator] AS Sum(
    DESCENDANTS([InvoiceIssueType].[Invoice Issue Type].[All],1),

Getting multiple counts ordered by Groups with a TOP X thrown in as well!


Hi, I have a result set (it's from multiple tables), that outputs the following values thus:

Department Statement Rating

IT Factor X 5

IT Factor Y 4

IT Factor X 3

SALES Factor Y 5

IT Factor X 4


Now, what I want to get out of this is a dataset detailing per department which had the highest number of records for a given statement (e.g. factor x) with the avergage.  However I don;t want to output all of them, just the highest.


So, the output would be something like:

Department Top Statement TotalCount  AvgRating

IT Factor X 3 4

SALES Factor Y 

Query to return conditional counts both distinct and non distinct.


I have the following table structure:

Table EmailTracking



Table EmailTrackingDetail


The following query returns the default recordset:


SELECT     TOP (200) EmailTracking.EmailAddress, EmailTrackingDetail.TranType, EmailTrackingDetail.TranDate
FROM         EmailTracking INNER JOIN
                      EmailTrackingDetail ON EmailTracking.RecordID = EmailTrackingDetail.EmailTrackingRecordID







2010-09-21 16:59:29.980



2010-09-21 17:19:58.790



2010-09-27 12:00:30.940

Verify record counts after sincronization


Hi, i need try get records count in publication and sql mobile db after syncronize my app.

The scenary is: W2000+SQL2000 and app pocket pc 2003 with sql mobile 3.0.




Countdown timer counts double


Hi there, i added a countdown timer to show seconds before page gets refreshed. But every tick counts 2 seconds instead of 1 .. so it goes from 30 > 28 > 26 instead of 30 > 29 > 28 ..

On page load I set the label value to 30.

The update panel contains following code for 2 labels and timer:

<asp:UpdatePanel ID="UpdatePanel3" runat="server">
            <asp:Label ID="lblRefresh" runat="server" Text="Volgende Refresh"></asp:Label>: 
            <asp:Label ID="lblRefreshTeller" runat="server" Text=""></asp:Label> sec
            <asp:AsyncPostBackTrigger  ControlID="Timer2" />
            <asp:Timer ID="Timer2" runat="server" Interval="1000" OnTick="Timer2_Tick"></asp:Timer>



Code behind:

Protected Sub Timer2_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer2.Tick
        Dim seconds As Integer = Integer.Parse(lblRefreshTeller.Text)
        If seconds > 0 Then

Test table counts from one database to another


we are moving to a new erp system and for piece of mind i would like to verify every table in the DW with row count against the test site DW. is there not a way to do this quickly? I am so not a sql user much but i just would think there should be a way to say if table count in dw X <> table count in dw Y then Z.

Parsing Words from Multiple Recordsets - Returning With Word Counts


I've had too many sleepless nights trying to work this out - I hope I can get some help here.

I have three distinct ADO recordsets that I need to parse words from and return to the client with sorted full word counts (less a predefined dictionary of innocuous words e.g. a, an, the, of.) across all three, references to the original record and references to the original source. The user needs to be able to drill in and out of this result set.

I'm going to simplify the layout for clarification purposes.

Let's say each data source has three identical fields 'updatedTime' (DateTime), 'text'(VARCHAR(255)) and 'ID'.

User goes to the site and searches for the word 'frog'.
User is returned with a word count of every record that has the word 'frog' in the 'text' field sorted by the top count down.
'green' 18 times in table A, 12 times in table B, 22 times in table C
'hop' 16 times in table A, 13 times in table B, 6 times in table C.
'roadkill' 12, 11, 4
'red' 10, 12, 3
And so on.

User clicks on the word 'green'.
Now only words with both 'frog' AND 'green' in the original record show up (again, with the counts and sorted by count).
A breadcrumb is built, so the user can click on 'frog' again to kill that filter.
User decides he doesn't want the default timeframe (6 months) and adjusts a filter

Get counts of saturdays and sundays between the start date and end date


Dear Folks,

I need to write a funcation to get the counts of saturday and sundays between the start date and end date ,Please help me to modify the query or any other method to get the conuts

Thanks to all in advance for any suggetion.

Input is start data and end date

on below i have mentioned a query which one will give the count but its giving wrong count

case - 1 if the start date and end date is same its giving wrong count


case -2 it is not considering end date








@weekdays as

Query to find the counts for each day on weekly basis


hi all,

i have a table called dailycounts  which has columns tranid(int), receivedate(date)

Below is the data in the table

tranid    receivedate
1           2010-01-01 12:00:00.000
2           2010-01-01 12:00:00.000
3           2010-01-01 12:00:00.000
32         2010-01-02 12:00:00.000
324       2010-01-02 12:00:00.000
3211     2010-01-03 12:00:00.000
31111   2010-01-03 12:00:00.000

I want the output as below


WeekNum  Mon  Tue  Wed   Thrs   Fri   Sat   Sun

 1               0      0     0       0        3      2      2

So i want the count(tranid) for each day and then i have to split it across the weeks.


pls help...


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