.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

Query to find the counts for each day on weekly basis

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server

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...


View Complete Post

More Related Resource Links

Find SQL Query when report is executed

Hi All, I am using SSRS 2008 R2 verison of reporting services. I have a report and the data source for this report is Oracle 10i, it takes around 2 mins to execute the report. I want to find out the query, which is sent by the reporting services to the Oracle. Is there a way to find it out. Please help me with this. Thanks Vineeshvineesh1701

Please help me to find out distinct value from linq to sql query given.

profileuserDataContext db = new profileuserDataContext(); var Q = (from p in db.edufinals join RM in db.edufinals on p.UserName equals RM.UserName where p.name.StartsWith(srcval) select new { p.name, RM.ProfielID, RM.ProfielPic, RM.city }).OrderBy(x => x.name).Distinct().Take(10);//, p.Key.ProfielID, p.Key.ProfielPic, p.Key.city, p.Key.country foreach (var pg in Q.Distinct()) { Response.Write(pg.name); ..................................... .................................... } how to refine this query to get distinct "name" column? Please help me to find out exact value.

Workflow AND/OR Tasks - how can I create one that occurs/recurrs on a weekly basis.

Hi All, Any ideas how I could configure SharePoint 2010 to trigger a workflow for a site member that notifies them to perform  a specific task ? I have a timesheet template that I intend to create a content type from. the use case is that each site member needs to fill out a timesheet at the start of the week to indicate what work they will be doing for the week. Is it possible ( using customisation ) to create a workflow so that a site member is prompted to create a new timesheet at the start of everyweek ? Alternatively can a recurrent task be created instead ? i.e. on the monday of every week create a new task for each site member to "create new weekly timesheet" Thanks and regards, Counie  

MDX Query - Find only new item that purchased in the last day or during the last week

Hi to all I whould like to write a MDX query to get new items that purchased in the last day and not purchased in the last 7 days or 30 days  , Also to find new items that purchased in the last 7 days and not purchased in the last 30 days , this is what i done until now CREATE   SET CURRENTCUBE.[FindNewWordIn_1_Day]   AS Filter   (nonempty([Keyword].[Keyword Name].members, ([Measures].[Count keyword],   NONEMPTY (Tail( Filter( [Time].[Date].MEMBERS ,   Not IsEmpty ([Measures].[Count keyword]) ), 1) ))) ,([Measures].[Count keyword], [Time].[Date].&[2010-09-10] )=0);   The value of [Time].[Date].&[2010-09-10] should be change to period that i want to check , ( 1 day, 7 day , 30 day)   the other one is lateset day with value NONEMPTY (Tail( Filter( [Time].[Date].MEMBERS , Not IsEmpty ([Measures].[Count keyword]) ), 1) with for you help ,   ofer  

[Sharepoint 2007] How to send a task from calendar on a weekly basis ?

Hi all, I have created a sharepoint calendar. I would like to create a task every week. This task should show the details of a meeting. Also a document should be uploaded every week to this meeting by the adminsitrator.   Every week a user should receive a new weekly remainder in the tasks list at 9 am.   I have been using the workflow but it is sending just one task for all the series. I would like to trigger a new task every week based on the calendar.   How can i achieve that ?   Thanks

VWD 2010 and Linq to Sql: can't find a query model for...



I have added a linq to sql layer in my MVC 2 application.

This is the code I am using:

using (ProductPortalDataContext ctx = new ProductPortalDataContext())
                var Title = from aps in ProductPortal.Models.AppSettingsView
                            where aps.LanguageID = Lang
                            select aps.WebSiteTitle;

It says to be unable to find a quey model for AppSettings view and then says "where not found".

Tried googling, nothing came up. Tried searching here(first two pages) and nothing that seemed relevant popped up.

But the best of all, created a new ASP.NET applicatio, imported the model using Linq to SQL and it compiled!

No idea what I am doing wrong... hints?

(some time later...)

Ok, I tried adding a button and a label to the asp.net application j

SPView query to find if the current user belongs to a Group


HI All,

I´m trying to write a query for a list view that should return all items if the current user belong to a group. If the user doesn´t belongs to this group, no items should be returned.

So far, I could find many equal examples about using the CAML Membership element in a comparison with an AssignetTo field. But not making a comparison between the current user and a specific Sharepoint Group.

Any suggestions, please?


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

find situation about a query that it is executing now

Hi all. I am a c# programmer. I have a update query that it need 2 minutes for executing (for each reason). I don't want discuss about its performance. My question is: How can I find situation about a query that it is executing now and it has not executed completely yet. for example How can find my c# code how many records are affected by my query every 10 seconds while that query is executing? Is sql profiler says that to us?

How do find what query is being executed on specific session ID?



I have a SP which writes to a table all processes which are being executed when application decides to stop waiting for results of the query. It works very well but problem is that it does not dump all SPIDs but only some of them which are being executing for a long time. Common pattern that a lot of SPIDs will be blocked by single SPID which will not be part of the output which is being generated. How do I dump all SPIDs and what they are doing along with SP below?


 'blk object' = t1.resource_associated_entity_id,

 (SELECT SUBSTRING(text, t3.statement_start_offset/2 + 1,
 		(CASE WHEN t3.statement_end_offset = -1 
 			THEN LEN(CONVERT(nvarchar(

query to find the 5th highest salary from an employee table..help plz...


 Thanks in advance

Xpath query to find an Node who's attribute contains a forward slash - Going crazy


If  I have a .XML file that contains the following example:

<Socks Style ="Business/Business Casual/Casual"


When I try to use the Xpath expression below to find the element above by searching for the value contained in the Style Attribute, the expression returns null:

//Socks [@Style='Business/Business Casual/Casual'] 

I downloaded xPath virtualizer and I noticed that the expression above is adding the namespace alias when it finds the embedded forward slash contained in the value of the Style Attribute (which is of course not what I want to happen).  This is driving me up a tree!!  Isn't there a way to escape the forward slash?  I know it is the slash that is the problem because for testing purposes I substituted dashes (-) in one of the nodes' Style Attribute values and then changed the xpath expression to match and it returned the exact node.

My goal is to do some replacements on all elements with the Style attribute based on each contents but I can't even try that because of this roadblock.

Please help anyone



How to find records based on CAPITAL and small letters in SQL Query?



I stumbled upon this scenario at my work and it is really something I was not expecting. I know that in general SQL itself is not a CASE SENSITIVE language ... so it will not make any difference if i write select *, or SELECT * or SeLEct * ...

But recently I came across a situation where I am required to find results from a table where more than one record exists with just a difference of Captial and Small letter word. Let me explain my problem in detail,

I work for a government client, in one of their table they have list of all state statues and out of them some have exactly same string except in that string some letters are capitalized, e.g.  46b-12 (A) and 46b-12 (a) ... now my question is how can i write a query which can find only record that I search, e.g if I search select desc from statues where code='46b-12 (a)' ... it should find only one record not two.

Query to find the domain and username


Hi experts,

 Any one pls tel  me the query to find the current working domain name with username in sql server.



WMI Query to find the other Sp sites in the network


Is there a WMI query to find the Sharepoint sites in the network?


Sql query to find continuos sequence of numbers




I have a table of structure below.

create table table_1

(id bigint identity(1,1),

stringnum varchar(20)



insert into table_1 values('2130')

insert into table_1 values('2131')

insert into table_1 values('21320')

insert into table_1 values('21321')

insert into table_1 values('21322')

insert into table_1 values('21323')

insert into table_1 values('21324')

insert into table_1 values('21325')

insert into table_1 values('21326')

insert into table_1 values('21327')

insert into table_1 values('21328')

insert into table_1 values('21329')

insert into table_1 values('2133')

insert into table_1 values('2134')

insert into table_1 values('2135')

insert into table_1 values('2136')

insert into table_1 values('2137')

insert into table_1 values('2138')

insert into table_1 values('2139')




I am trying to compress these stringnums i.e for example

if a complete range exists say 21320 21321,21322...21329 (i.e if the number is ranging from 0-9) then the last digit of the number can be eliminated so that the result is

2132 ( which is equivalent to 21320-21329 )


this elimination of la

Cannot find execute any query, stored procedure not found even if it is there


Hey guys,

I am getting frustrated with this problem, I dont know what i did, but now I cannot execute any stored procedured when I could last time.

When I use my asp.net application to run the query, it finds the stored procedure but when I execute it is sql management studio it says it cannot find the stored procedure even though it is there.

I tried to execute other procedures and the samething happens. Even when I try a simple query it says it cannot find the table

I could execute the query if i placed Use [databasename] in front, but even with this, I cannot execute stored procedures.

does any1 know how to fix this?

Ju Lian

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