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


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

TSQL to get Week of Day

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

Hi all,

I need to write a automatic query. Please see below for example.

Date: 4/4/2010 (Monday), 4/5/2010 (Tuesday), 4/6/2010 (Wednesday)

So from every Monday - Sunday (End of the week) the result = Week of October 4th 2010

Date       Day          Wanted Result

4/4/2010 Mon          Week of Oct 4 2010

4/5/2010 Tue          Week of Oct 4 2010

4/6/2010 Wed         Week of Oct 4 2010

4/7/2010 Thur         Week of Oct 4 2010

4/8/2010 Fri            Week of Oct 4 2010

4/9/2010 Sat           Week of Oct 4 2010

4/10/2010 Sun         Week of Oct 4 2010

4/11/2010 Mon         Week of Oct 11 2010

 

Thanks loadzzz in advancee




View Complete Post


More Related Resource Links

dropdownbox which selects records based on criteria (TODAY, THIS WEEK, THIS MONTH, etc)

  

I have a dropdownbox which I want to use to search records based on a timescale. The gridview populates results based from that search.

So here is how I want to work this;

If a user selects "today" - a list of records input today are populated. If "this week" is selected, a list of all records added within that week from todays date is populated.

I created a forum on this issue previously but to no avail whatsoever. How do I work around this problem?

Help would be much appreciated



TSQL: Passing array/list/set to stored procedure (MS SQL Server)

  
Passing array/list/set to stored procedure is fairly common task when you are working with Databases. You can meet this when you want to filter some collection. Other case - it can be an import into database from extern sources. I will consider few solutions: creation of sql-query at server code, put set of parameters to sql stored procedure's parameter with next variants: parameters separated by comma, bulk insert, and at last table-valued parameters (it is most interesting approach, which we can use from MS SQL Server 2008). Ok, let's suppose that we have list of items and we need to filter this items by categories ("TV", "TV game device", "DVD-player") and by firms ("Firm 1", "Firm2", "Firm 3). It will look at database like this So we need a query which will return us list of items from database. Also we need opportunity to filter these items by categories or by firms. We will filter them by identifiers. Ok, we know the mission. How we will solve it? Most easy way, used by junior developers - it is creating SQL-instruction with C# code, it can be like this List<int> categories = new List<int>() { 1, 2, 3 };   StringBuilder sbSql = new StringBuilder(); sbSql.Append( @" select i.Name as ItemName, f.Name as FirmName, c.Name as CategoryName from Item i inner join Firm f on i.FirmId =

Approach using Regex in TSQL text manipulation

  
Hi there I have this following data: 0297144600-4799               0297485500-5599 The 0297485500-5599 based on observation always on position 31 char from the left which this is an easy approach. But I would like to do is to anticipate just in case the data is like this below which means the position is no longer valid: 0297144600-4799      0297485500-5599      0297485600-5699 As you can see, I guess the first approach will the split by 1 blank space (" ") but due to number of space is unknown (varies) how do I take this approach then? Is there any method to find the space in between and shrink into 1 blank space (" "). I am exploring Regex not sure how to do this and what I can think of is that if the blank is space can somehow reduce from xxx to 1 and I can handle this the rest. Thanks dewacorp.alliances

Looking for TSQL statement that returns customers with greatest number of order in descending order

  
What would be an efficient TSQL SELECT statement that joins customer and order tables, counts the orders for each customer (group by) and returns a result for all customers with at least one order, and also in descending order from customers with the most orders to the least?

XQeury in TSQL

  
Why can i use it like this: SET @xml.modify(' delete if(/employees/employee/name[sql:variable("@index")][contains(.,@type)]) then (/employees/employee/name[sql:variable("@index")]/@type) else() ') but can not use it like this: SET @xml.modify(' insert attribute type {string("Normal")} as first into (/employees/employee/name)[sql:variable("@index")] ')

Tsql

  
this particular query takes the tototal execution time of 20 minutes and 52 seconds is there any posibilities to make this particular query faster? select   state_name,dist_name,tahsil_name,isnull(sum(CASE WHEN T_rabbit.Village_code like '0%' and T_rabbit.sb_code between 1401 and 1405 then rm end ),0)as rm,isnull(sum(CASE WHEN T_rabbit.Village_code like '0%' and T_rabbit.sb_code between 1401 and 1405 then rf end ),0)as rf,isnull(sum(CASE WHEN T_rabbit.Village_code like '0%' and T_rabbit.sb_code between 1401 and 1405 then rm+rf end ),0)as TotalRabbit,isnull(sum(CASE WHEN T_Dog.Village_code like '0%' and T_Dog.sb_code ='1300' then dm end ),0)as dm,isnull(sum(CASE WHEN T_Dog.Village_code like '0%' and T_Dog.sb_code ='1300' then df end ),0)as df,isnull(sum(CASE WHEN T_Dog.Village_code like '0%' and T_Dog.sb_code ='1300' then dm+df end ),0)as TotalDog,isnull(sum(CASE WHEN T_Elephant.Village_code like '0%' and T_Elephant.sb_code ='1500' then em end ),0)as em,isnull(sum(CASE WHEN T_Elephant.Village_code like '0%' and T_Elephant.sb_code ='1500' then ef end ),0)as ef,isnull(sum(CASE WHEN T_Elephant.Village_code like '0%' and T_Elephant.sb_code ='1500' then em+ef end ),0)as TotalElephant from state_master inner   join dist_Master ON Dist_Master.State_code=state_master.State_code INNER   JOIN Tahsil_Master ON Tahs

XQuery in TSQL

  
Why can i use it like this:   SET @xml.modify(' delete if(/employees/employee/name[sql:variable("@index")][contains(.,@type)]) then (/employees/employee/name[sql:variable("@index")]/@type) else() ')   but can not use it like this:   SET @xml.modify(' insert attribute type {string("Normal")} as first into (/employees/employee/name)[sql:variable("@index")] ')  

SSAS Calendar Week commencing with split at month

  
Hi all, Wonder if someone can help. Our CFO wants to see a cube calendar that shows week commencing but stops at the end of month. For example if the dates are Monday  28th   Tuesday  29th   Wednesday 30th   Thursday 1st    Friday  2nd Saturday 3rd Sunday   4th So for this example the week commencing is the 28th of Month 1 and Thursday is week commencing 1st of the next. I am having a problem seeing how I can do this. If I add the weeks to a standard calendar I get the week commencing 28th which includes the 7 days, naturally. I cannot help feeling I am missing something obvious. Cheers

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

Report Level Security in SSRS using Tsql

  
So i am Brand new in this area i have developed reports before and deployed them but never got an oppurtunity to work on security and who gets to see what.. Purpose of the view is thats what im thinking..   We have a reports database has 100 reports . What we are trying to do is have a faster way to manage report subscriptions. The tables i have is Analysts, SalesRep,SalesRepSubs and dbo.users.Dbo users are our web portal users where they can view reports. I want to create a table in which every report is viewed per security level. We have 6 layers of security layer 1 is lowers to layer 6 being highest. This is the relationship between tables Salesrep(SRID PK) Salesrep_sub (SRID_SUBID PK) Analysts : (AnalystIDPK) Users: The Table has all Users who are Salesrep,Sales Repsub and Analysts)(PKID PK) and we have cols SRID, SRID_SUBID & AnalystID) in this table. use Pricing go Alter View dbo.User_Access   AS select S.SR_name,u.SRID,sr.SRsub_name,u.SRID_SubID,U.Userid,u.username,u.Email, u.SecurityLevel,a.AnalystID from dbo.Users u join tbl_Analyst a on a.AnalystID=u.UserID join tbl_SalesRep s on s.SRID=u.SRID Join tbl_SalesRep_Sub sr on sr.SRID_SubID=u.SRID_SubID My question is what do i do next? Add this view to Report server? or how can i make it functionl?????????FM

Is there a way to disable the day and week links within the Calendar itself?

  
I'm able to hide the top icons. I'm wondering if there's a way to keep a user from clicking the top of a date or the side of the week and navigating them to another calendar view. I only want to keep them within the month view. Thanks in advance! -Mike

Report Level Security in SSRS using Tsql

  
So i am Brand new in this area i have developed reports before and deployed them but never got an oppurtunity to work on security and who gets to see what.. Purpose of the view is thats what im thinking..   We have a reports database has 100 reports . What we are trying to do is have a faster way to manage report subscriptions. The tables i have is Analysts, SalesRep,SalesRepSubs and dbo.users.Dbo users are our web portal users where they can view reports. I want to create a table in which every report is viewed per security level. We have 6 layers of security layer 1 is lowers to layer 6 being highest. This is the relationship between tables Salesrep(SRID PK) Salesrep_sub (SRID_SUBID PK) Analysts : (AnalystIDPK) Users: The Table has all Users who are Salesrep,Sales Repsub and Analysts)(PKID PK) and we have cols SRID, SRID_SUBID & AnalystID) in this table. use Pricing go Alter View dbo.User_Access   AS select S.SR_name,u.SRID,sr.SRsub_name,u.SRID_SubID,U.Userid,u.username,u.Email, u.SecurityLevel,a.AnalystID from dbo.Users u join tbl_Analyst a on a.AnalystID=u.UserID join tbl_SalesRep s on s.SRID=u.SRID Join tbl_SalesRep_Sub sr on sr.SRID_SubID=u.SRID_SubID My question is what do i do next? Add this view to Report server? or how can i make it functionl?????????FM

TSQL Stored Procedure

  
Hi, I have a rather odd Stored Procedure I need to write, I was wondering if anyone could help me?   I have two tables which I need to basically match up and get information from.   Table: FormField - FormFieldID (PK) - Name   Table: FormFieldValues - FormFieldValue - FormFieldID - Value (Value of the FormField) - FormKey (This is to keep the form values together, for example Bob's DOB is 01/01/1975)   Example View   FormField.Name          FormFieldValue                   FormKey First_Name                 Bob                                    1 First_Name                 Jim                                     2 DOB                 &nbs

tsql

  
how would i convert a given number into an alphabet. suppose if user gives a number '1224' then the output should be 'one thousand two hundred and twenty four'. if anyone please reply.

how to concatenate special characters in TSQL ?

  
hi how can i insert special characters like ' to my select output ?? for example, i tried to get the following output but it just doesnt work.. i am using SQLS2K btw, SELECT ' Hi. this is called the 'modern age' ' note that i need those to ' s with the words modern age :)   thanks in advance :)

Grouping by week, but show day range

  
SELECT SUM(T.SPEED_OCCURRENCES) AS TOTAL_SPEED_OCCURRENCES, CONVERT(NVARCHAR, DATEPART(WEEK, T.DATE), 101) AS STRDATE FROM VEHICLE_TRENDS T, @VIEWABLE V WHERE T.VEHICLE_ID = V.VEHICLE_ID AND T.DATE BETWEEN @START_DATE AND DATEADD(DAY, 1, @END_DATE) GROUP BY DATEPART(WEEK, T.DATE) ORDER BY [STRDATE] ASC; So we have a query that returns results like: TOTAL_SPEED_OCCURRENCES       STRDATE 34                                               34 45                                               35 67                                               36 Where STRDATE is the week number. This is correct grouping, but we want STRDATE to select the range of day

How I select Start of week is Friday from date of Year ?

  
Hello everyone, I am tring to solve How i select Start date is Friday from week? Details for which i want to show I have dtmDate column in a query. from that date i display week of that date as per using (ww,dtmDate) as dtmWeek, and parameters are StartDate & EndDate Suppose I select StartDate as 1/1/2007 & End Date as 12/31/2007   I  do not want to show Output as: Weeks                      Column1          Column2 1/1/2007 (Thuesday)       5                     6 1/2/2007 (Wed)               4                     2 | | 1/5/2007  (Friday)            1                      8 1/6/2007 (Saturday)        7                     
Categories: 
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