Search Results

Search found 45340 results on 1814 pages for 'sql date functions'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 276/1814 | < Previous Page | 272 273 274 275 276 277 278 279 280 281 282 283  | Next Page >

  • SQL to get rows (not groups) that match an aggregate

    - by xulochavez
    Given table USER (name, city, age), what's the best way to get the user details of oldest user per city? I have seen the following example SQL used in Oracle which I think it works select name, city, age from USER, (select city as maxCity, max(age) as maxAge from USER group by city) where city=maxCity and age=maxAge So in essence: use a nested query to select the grouping key and aggregate for it, then use it as another table in the main query and join with the grouping key and the aggregate value for each key. Is this the standard SQL way of doing it? Is it any quicker than using a temporary table, or is in fact using a temporary table interanlly anyway?

    Read the article

  • Given a Date "03/13/2010", using that in a MYSQL Where Clause?

    - by nobosh
    I would like to pass a MYSQL query via Coldfusion the following date: 03/13/2010 So the query filters against it like so: SELECT * FROM myTable WHERE dateAdded before or on 03/13/2010 I'd also like to be able to take 2 dates as ranges, from: 01/11/2000, to: 03/13/2010 SELECT * FROMT myTable WHERE dateAdded is ON or Between 01/11/2000 through 03/13/2010 thanks

    Read the article

  • SQL - Link to more table information

    - by BioXhazard
    I have a datagrid with 3 of 5 fields from a SQL table. The first databound was changed to an asp:hyperlink so the field can be clicked. What I want to do is, when the user clicks an object in the field, it sends the ID to another page. From there, it uses the request in order to display the required information which is found in the same SQL table. For example: User clicks on a cell in the first column, it brings them to a page with more information on that cell.

    Read the article

  • Hibernate HQL to basic SQL (no joins)

    - by CC
    Hello everybody, I working on a project with Hibernate and we need to replace Hibernate with some "home made persistence" stuff. The idea is that the project is big enough, and we have many HQL queries. The problem is with the queries like select a,b from table1, table2 on t1.table1=t2.table2 Basically all joins are not supported by our "hand made persistence" stuff. What I would need, is to be able to do some sort of transcoder, which will take as a input the HQL queries and output some SQL, but SQL without joins I hope you get the idea. My persistence layer does not supports joins. Does anybody has any idea about something like that? Some framework, or something? Thanks alot everybody. C.C.

    Read the article

  • Numeric literals in sql server 2008

    - by costa
    What is the type that sql server assigns to the numeric literal: 2. , i.e. 2 followed by a dot? I was curious because: select convert(varchar(50), 2.) union all select convert(varchar(50), 2.0) returns: 2 2.0 which made me ask what's the difference between 2. and 2.0 type wise? Sql server seems to assign types to numeric literals depending on the number itself by finding the minimal storage type that can hold the number. A value of 1222333 is stored as int while 1152921504606846975 is stored as big int. thanks

    Read the article

  • Installing SharePoint 2010 and PowerPivot for SharePoint on Windows 7

    - by smisner
    Many people like me want (or need) to do their business intelligence development work on a laptop. As someone who frequently speaks at various events or teaches classes on all subjects related to the Microsoft business intelligence stack, I need a way to run multiple server products on my laptop with reasonable performance. Once upon a time, that requirement meant only that I had to load the current version of SQL Server and the client tools of choice. In today's post, I'll review my latest experience with trying to make the newly released Microsoft BI products work with a Windows 7 operating system.The entrance of Microsoft Office SharePoint Server 2007 into the BI stack complicated matters and I started using Virtual Server to establish a "suitable" environment. As part of the team that delivered a lot of education as part of the Yukon pre-launch activities (that would be SQL Server 2005 for the uninitiated), I was working with four - yes, four - virtual servers. That was a pretty brutal workload for a 2GB laptop, which worked if I was very, very careful. It could also be a finicky and unreliable configuration as I learned to my dismay at one TechEd session several years ago when I had to reboot a very carefully cached set of servers just minutes before my session started. Although it worked, it came back to life very, very slowly much to the displeasure of the audience. They couldn't possibly have been less pleased than me.At that moment, I resolved to get the beefiest environment I could afford and consolidate to a single virtual server. Enter the 4GB 64-bit laptop to preserve my sanity and my livelihood. Likewise, for SQL Server 2008, I managed to keep everything within a single virtual server and I could function reasonably well with this approach.Now we have SQL Server 2008 R2 plus Office SharePoint Server 2010. That means a 64-bit operating system. Period. That means no more Virtual Server. That means I must use Hyper-V or another alternative. I've heard alternatives exist, but my few dabbles in this area did not yield positive results. It might have been just me having issues rather than any failure of those technologies to adequately support the requirements.My first run at working with the new BI stack configuration was to set up a 64-bit 4GB laptop with a dual-boot to run Windows Server 2008 R2 with Hyper-V. However, I was generally not happy with running Windows Server 2008 R2 on my laptop. For one, I couldn't put it into sleep mode, which is helpful if I want to prepare for a presentation beforehand and then walk to the podium without the need to hold my laptop in its open state along the way (my strategy at the TechEd session long, long ago). Secondly, it was finicky with projectors. I had issues from time to time and while I always eventually got it to work, I didn't appreciate those nerve-wracking moments wondering whether this would be the time that it wouldn't work.Somewhere along the way, I learned that it was possible to load SharePoint 2010 in a Windows 7 which piqued my interest. I had just acquired a new laptop running Windows 7 64-bit, and thought surely running the BI stack natively on my laptop must be better than running Hyper-V. (I have not tried booting to Hyper-V VHD yet, but that's on my list of things to try so the jury of one is still out on this approach.) Recently, I had to build up a server with the RTM versions of SQL Server 2008 R2 and Sharepoint Server 2010 and decided to follow suit on my Windows 7 Ultimate 64-bit laptop. The process is slightly different, but I'm happy to report that it IS possible, although I had some fits and starts along the way.DISCLAIMER: These products are NOT intended to be run in production mode on the Windows 7 operating system. The configuration described in this post is strictly for development or learning purposes and not supported by Microsoft. If you have trouble, you will NOT get help from them. I might be able to help, but I provide no guarantees of my ability or availablity to help. I won't provide the step-by-step instructions in this post as there are other resources that provide these details, but I will provide an overview of my approach, point you to the relevant resources, describe some of the problems I encountered, and explain how I addressed those problems to achieve my desired goal.Because my goal was not simply to set up SharePoint Server 2010 on my laptop, but specifically PowerPivot for SharePoint, I started out by referring to the installation instructions at the PowerPiovt-Info site, but mainly to confirm that I was performing steps in the proper sequence. I didn't perform the steps in Part 1 because those steps are applicable only to a server operating system which I am not running on my laptop. Then, the instructions in Part 2, won't work exactly as written for the same reason. Instead, I followed the instructions on MSDN, Setting Up the Development Environment for SharePoint 2010 on Windows Vista, Windows 7, and Windows Server 2008. In general, I found the following differences in installation steps from the steps at PowerPivot-Info:You must copy the SharePoint installation media to the local drive so that you can edit the config.xml to allow installation on a Windows client.You also have to manually install the prerequisites. The instructions provides links to each item that you must manually install and provides a command-line instruction to execute which enables required Windows features.I will digress for a moment to save you some grief in the sequence of steps to perform. I discovered later that a missing step in the MSDN instructions is to install the November CTP Reporting Services add-in for SharePoint. When I went to test my SharePoint site (I believe I tested after I had a successful PowerPivot installation), I ran into the following error: Could not load file or assembly 'RSSharePointSoapProxy, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. I was rather surprised that Reporting Services was required. Then I found an article by Alan le Marquand, Working Together: SQL Server 2008 R2 Reporting Services Integration in SharePoint 2010,that instructed readers to install the November add-in. My first reaction was, "Really?!?" But I confirmed it in another TechNet article on hardware and software requirements for SharePoint Server 2010. It doesn't refer explicitly to the November CTP but following the link took me there. (Interestingly, I retested today and there's no longer any reference to the November CTP. Here's the link to download the latest and greatest Reporting Services Add-in for SharePoint Technologies 2010.) You don't need to download the add-in anymore if you're doing a regular server-based installation of SharePoint because it installs as part of the prerequisites automatically.When it was time to start the installation of SharePoint, I deviated from the MSDN instructions and from the PowerPivot-Info instructions:On the Choose the installation you want page of the installation wizard, I chose Server Farm.On the Server Type page, I chose Complete.At the end of the installation, I did not run the configuration wizard.Returning to the PowerPivot-Info instructions, I tried to follow the instructions in Part 3 which describe installing SQL Server 2008 R2 with the PowerPivot option. These instructions tell you to choose the New Server option on the Setup Role page where you add PowerPivot for SharePoint. However, I ran into problems with this approach and got installation errors at the end.It wasn't until much later as I was investigating an error that I encountered Dave Wickert's post that installing PowerPivot for SharePoint on Windows 7 is unsupported. Uh oh. But he did want to hear about it if anyone succeeded, so I decided to take the plunge. Perseverance paid off, and I can happily inform Dave that it does work so far. I haven't tested absolutely everything with PowerPivot for SharePoint but have successfully deployed a workbook and viewed the PowerPivot Management Dashboard. I have not yet tested the data refresh feature, but I have installed. Continue reading to see how I accomplished my objective.I unintalled SQL Server 2008 R2 and started again. I had different problems which I don't recollect now. However, I uninstalled again and approached installation from a different angle and my next attempt succeeded. The downside of this approach is that you must do all of the things yourself that are done automatically when you install PowerPivot as a new server. Here are the steps that I followed:Install SQL Server 2008 R2 to get a database engine instance installed.Run the SharePoint configuration wizard to set up the SharePoint databases.In Central Administration, create a Web application using classic mode authentication as per a TechNet article on PowerPivot Authentication and Authorization.Then I followed the steps I found at How to: Install PowerPivot for SharePoint on an Existing SharePoint Server. Especially important to note - you must launch setup by using Run as administrator. I did not have to manually deploy the PowerPivot solution as the instructions specify, but it's good to know about this step because it tells you where to look in Central Administration to confirm a successful deployment.I did spot some incorrect steps in the instructions (at the time of this writing) in How To: Configure Stored Credentials for PowerPivot Data Refresh. Specifically, in the section entitled Step 1: Create a target application and set the credentials, both steps 10 and 12 are incorrect. They tell you to provide an actual Windows user name and password on the page where you are simply defining the prompts for your application in the Secure Store Service. To add the Windows user name and password that you want to associate with the application - after you have successfully created the target application - you select the target application and then click Set credentials in the ribbon.Lastly, I followed the instructions at How to: Install Office Data Connectivity Components on a PowerPivot server. However, I have yet to test this in my current environment.I did have several stops and starts throughout this process and edited those out to spare you from reading non-essential information. I believe the explanation I have provided here accurately reflect the steps I followed to produce a working configuration. If you follow these steps and get a different result, please let me know so that together we can work through the issue and correct these instructions. I'm sure there are many other folks in the Microsoft BI community that will appreciate the ability to set up the BI stack in a Windows 7 environment for development or learning purposes. Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!

    Read the article

  • TFS API Change WorkItem CreatedDate And ChangedDate To Historic Dates

    - by Tarun Arora
    There may be times when you need to modify the value of the fields “System.CreatedDate” and “System.ChangedDate” on a work item. Richard Hundhausen has a great blog with ample of reason why or why not you should need to set the values of these fields to historic dates. In this blog post I’ll show you, Create a PBI WorkItem linked to a Task work item by pre-setting the value of the field ‘System.ChangedDate’ to a historic date Change the value of the field ‘System.Created’ to a historic date Simulate the historic burn down of a task type work item in a sprint Explain the impact of updating values of the fields CreatedDate and ChangedDate on the Sprint burn down chart Rules of Play      1. You need to be a member of the Project Collection Service Accounts              2. You need to use ‘WorkItemStoreFlags.BypassRules’ when you instantiate the WorkItemStore service // Instanciate Work Item Store with the ByPassRules flag _wis = new WorkItemStore(_tfs, WorkItemStoreFlags.BypassRules);      3. You cannot set the ChangedDate         - Less than the changed date of previous revision         - Greater than current date Walkthrough The walkthrough contains 5 parts 00 – Required References 01 – Connect to TFS Programmatically 02 – Create a Work Item Programmatically 03 – Set the values of fields ‘System.ChangedDate’ and ‘System.CreatedDate’ to historic dates 04 – Results of our experiment Lets get started………………………………………………… 00 – Required References Microsoft.TeamFoundation.dll Microsoft.TeamFoundation.Client.dll Microsoft.TeamFoundation.Common.dll Microsoft.TeamFoundation.WorkItemTracking.Client.dll 01 – Connect to TFS Programmatically I have a in depth blog post on how to connect to TFS programmatically in case you are interested. However, the code snippet below will enable you to connect to TFS using the Team Project Picker. // Services I need access to globally private static TfsTeamProjectCollection _tfs; private static ProjectInfo _selectedTeamProject; private static WorkItemStore _wis; // Connect to TFS Using Team Project Picker public static bool ConnectToTfs() { var isSelected = false; // The user is allowed to select only one project var tfsPp = new TeamProjectPicker(TeamProjectPickerMode.SingleProject, false); tfsPp.ShowDialog(); // The TFS project collection _tfs = tfsPp.SelectedTeamProjectCollection; if (tfsPp.SelectedProjects.Any()) { // The selected Team Project _selectedTeamProject = tfsPp.SelectedProjects[0]; isSelected = true; } return isSelected; } 02 – Create a Work Item Programmatically In the below code snippet I have create a Product Backlog Item and a Task type work item and then link them together as parent and child. Note – You will have to set the ChangedDate to a historic date when you created the work item. Remember, If you try and set the ChangedDate to a value earlier than last assigned you will receive the following exception… TF26212: Team Foundation Server could not save your changes. There may be problems with the work item type definition. Try again or contact your Team Foundation Server administrator. If you notice below I have added a few seconds each time I have modified the ‘ChangedDate’ just to avoid running into the exception listed above. // Create Linked Work Items and return Ids private static List<int> CreateWorkItemsProgrammatically() { // Instantiate Work Item Store with the ByPassRules flag _wis = new WorkItemStore(_tfs, WorkItemStoreFlags.BypassRules); // List of work items to return var listOfWorkItems = new List<int>(); // Create a new Product Backlog Item var p = new WorkItem(_wis.Projects[_selectedTeamProject.Name].WorkItemTypes["Product Backlog Item"]); p.Title = "This is a new PBI"; p.Description = "Description"; p.IterationPath = string.Format("{0}\\Release 1\\Sprint 1", _selectedTeamProject.Name); p.AreaPath = _selectedTeamProject.Name; p["Effort"] = 10; // Just double checking that ByPassRules is set to true if (_wis.BypassRules) { p.Fields["System.ChangedDate"].Value = Convert.ToDateTime("2012-01-01"); } if (p.Validate().Count == 0) { p.Save(); listOfWorkItems.Add(p.Id); } else { Console.WriteLine(">> Following exception(s) encountered during work item save: "); foreach (var e in p.Validate()) { Console.WriteLine(" - '{0}' ", e); } } var t = new WorkItem(_wis.Projects[_selectedTeamProject.Name].WorkItemTypes["Task"]); t.Title = "This is a task"; t.Description = "Task Description"; t.IterationPath = string.Format("{0}\\Release 1\\Sprint 1", _selectedTeamProject.Name); t.AreaPath = _selectedTeamProject.Name; t["Remaining Work"] = 10; if (_wis.BypassRules) { t.Fields["System.ChangedDate"].Value = Convert.ToDateTime("2012-01-01"); } if (t.Validate().Count == 0) { t.Save(); listOfWorkItems.Add(t.Id); } else { Console.WriteLine(">> Following exception(s) encountered during work item save: "); foreach (var e in t.Validate()) { Console.WriteLine(" - '{0}' ", e); } } var linkTypEnd = _wis.WorkItemLinkTypes.LinkTypeEnds["Child"]; p.Links.Add(new WorkItemLink(linkTypEnd, t.Id) {ChangedDate = Convert.ToDateTime("2012-01-01").AddSeconds(20)}); if (_wis.BypassRules) { p.Fields["System.ChangedDate"].Value = Convert.ToDateTime("2012-01-01").AddSeconds(20); } if (p.Validate().Count == 0) { p.Save(); } else { Console.WriteLine(">> Following exception(s) encountered during work item save: "); foreach (var e in p.Validate()) { Console.WriteLine(" - '{0}' ", e); } } return listOfWorkItems; } 03 – Set the value of “Created Date” and Change the value of “Changed Date” to Historic Dates The CreatedDate can only be changed after a work item has been created. If you try and set the CreatedDate to a historic date at the time of creation of a work item, it will not work. // Lets do a work item effort burn down simulation by updating the ChangedDate & CreatedDate to historic Values private static void WorkItemChangeSimulation(IEnumerable<int> listOfWorkItems) { foreach (var id in listOfWorkItems) { var wi = _wis.GetWorkItem(id); switch (wi.Type.Name) { case "ProductBacklogItem": if (wi.State.ToLower() == "new") wi.State = "Approved"; // Advance the changed date by few seconds wi.Fields["System.ChangedDate"].Value = Convert.ToDateTime(wi.Fields["System.ChangedDate"].Value).AddSeconds(10); // Set the CreatedDate to Changed Date wi.Fields["System.CreatedDate"].Value = Convert.ToDateTime(wi.Fields["System.ChangedDate"].Value).AddSeconds(10); wi.Save(); break; case "Task": // Advance the changed date by few seconds wi.Fields["System.ChangedDate"].Value = Convert.ToDateTime(wi.Fields["System.ChangedDate"].Value).AddSeconds(10); // Set the CreatedDate to Changed date wi.Fields["System.CreatedDate"].Value = Convert.ToDateTime(wi.Fields["System.ChangedDate"].Value).AddSeconds(10); wi.Save(); break; } } // A mock sprint start date var sprintStart = DateTime.Today.AddDays(-5); // A mock sprint end date var sprintEnd = DateTime.Today.AddDays(5); // What is the total Sprint duration var totalSprintDuration = (sprintEnd - sprintStart).Days; // How much of the sprint have we already covered var noOfDaysIntoSprint = (DateTime.Today - sprintStart).Days; // Get the effort assigned to our tasks var totalEffortRemaining = QueryTaskTotalEfforRemaining(listOfWorkItems); // Defining how much effort to burn every day decimal dailyBurnRate = totalEffortRemaining / totalSprintDuration < 1 ? 1 : totalEffortRemaining / totalSprintDuration; // we have just created one task var totalNoOfTasks = 1; var simulation = sprintStart; var currentDate = DateTime.Today.Date; // Carry on till effort has been burned down from sprint start to today while (simulation.Date != currentDate.Date) { var dailyBurnRate1 = dailyBurnRate; // A fixed amount needs to be burned down each day while (dailyBurnRate1 > 0) { // burn down bit by bit from all unfinished task type work items foreach (var id in listOfWorkItems) { var wi = _wis.GetWorkItem(id); var isDirty = false; // Set the status to in progress if (wi.State.ToLower() == "to do") { wi.State = "In Progress"; isDirty = true; } // Ensure that there is enough effort remaining in tasks to burn down the daily burn rate if (QueryTaskTotalEfforRemaining(listOfWorkItems) > dailyBurnRate1) { // If there is less than 1 unit of effort left in the task, burn it all if (Convert.ToDecimal(wi["Remaining Work"]) <= 1) { wi["Remaining Work"] = 0; dailyBurnRate1 = dailyBurnRate1 - Convert.ToDecimal(wi["Remaining Work"]); isDirty = true; } else { // How much to burn from each task? var toBurn = (dailyBurnRate / totalNoOfTasks) < 1 ? 1 : (dailyBurnRate / totalNoOfTasks); // Check that the task has enough effort to allow burnForTask effort if (Convert.ToDecimal(wi["Remaining Work"]) >= toBurn) { wi["Remaining Work"] = Convert.ToDecimal(wi["Remaining Work"]) - toBurn; dailyBurnRate1 = dailyBurnRate1 - toBurn; isDirty = true; } else { wi["Remaining Work"] = 0; dailyBurnRate1 = dailyBurnRate1 - Convert.ToDecimal(wi["Remaining Work"]); isDirty = true; } } } else { dailyBurnRate1 = 0; } if (isDirty) { if (Convert.ToDateTime(wi.Fields["System.ChangedDate"].Value).Date == simulation.Date) { wi.Fields["System.ChangedDate"].Value = Convert.ToDateTime(wi.Fields["System.ChangedDate"].Value).AddSeconds(20); } else { wi.Fields["System.ChangedDate"].Value = simulation.AddSeconds(20); } wi.Save(); } } } // Increase date by 1 to perform daily burn down by day simulation = Convert.ToDateTime(simulation).AddDays(1); } } // Get the Total effort remaining in the current sprint private static decimal QueryTaskTotalEfforRemaining(List<int> listOfWorkItems) { var unfinishedWorkInCurrentSprint = _wis.GetQueryDefinition( new Guid(QueryAndGuid.FirstOrDefault(c => c.Key == "Unfinished Work").Value)); var parameters = new Dictionary<string, object> { { "project", _selectedTeamProject.Name } }; var q = new Query(_wis, unfinishedWorkInCurrentSprint.QueryText, parameters); var results = q.RunLinkQuery(); var wis = new List<WorkItem>(); foreach (var result in results) { var _wi = _wis.GetWorkItem(result.TargetId); if (_wi.Type.Name == "Task" && listOfWorkItems.Contains(_wi.Id)) wis.Add(_wi); } return wis.Sum(r => Convert.ToDecimal(r["Remaining Work"])); }   04 – The Results If you are still reading, the results are beautiful! Image 1 – Create work item with Changed Date pre-set to historic date Image 2 – Set the CreatedDate to historic date (Same as the ChangedDate) Image 3 – Simulate of effort burn down on a task via the TFS API   Image 4 – The history of changes on the Task. So, essentially this task has burned 1 hour per day Sprint Burn Down Chart – What’s not possible? The Sprint burn down chart is calculated from the System.AuthorizedDate and not the System.ChangedDate/System.CreatedDate. So, though you can change the System.ChangedDate and System.CreatedDate to historic dates you will not be able to synthesize the sprint burn down chart. Image 1 – By changing the Created Date and Changed Date to ‘18/Oct/2012’ you would have expected the burn down to have been impacted, but it won’t be, because the sprint burn down chart uses the value of field ‘System.AuthorizedDate’ to calculate the unfinished work points. The AsOf queries that are used to calculate the unfinished work points use the value of the field ‘System.AuthorizedDate’. Image 2 – Using the above code I burned down 1 hour effort per day over 5 days from the task work item, I would have expected the sprint burn down to show a constant burn down, instead the burn down shows the effort exhausted on the 24th itself. Simply because the burn down is calculated using the ‘System.AuthorizedDate’. Now you would ask… “Can I change the value of the field System.AuthorizedDate to a historic date” Unfortunately that’s not possible! You will run into the exception ValidationException –  “TF26194: The value for field ‘Authorized Date’ cannot be changed.” Conclusion - You need to be a member of the Project Collection Service account group in order to set the fields ‘System.ChangedDate’ and ‘System.CreatedDate’ to historic dates - You need to instantiate the WorkItemStore using the flag ByPassValidation - The System.ChangedDate needs to be set to a historic date at the time of work item creation. You cannot reset the ChangedDate to a date earlier than the existing ChangedDate and you cannot reset the ChangedDate to a date greater than the current date time. - The System.CreatedDate can only be reset after a work item has been created. You cannot set the CreatedDate at the time of work item creation. The CreatedDate cannot be greater than the current date. You can however reset the CreatedDate to a date earlier than the existing value. - You will not be able to synthesize the Sprint burn down chart by changing the value of System.ChangedDate and System.CreatedDate to historic dates, since the burn down chart uses AsOf queries to calculate the unfinished work points which internally uses the System.AuthorizedDate and NOT the System.ChangedDate & System.CreatedDate - System.AuthorizedDate cannot be set to a historic date using the TFS API Read other posts on using the TFS API here… Enjoy!

    Read the article

  • Looking for terminology for the relation of a subject and a predicate

    - by kostja
    While writing some predicates for collection filtering I have stumbled over the choice of the right words for the relation of the subject and the predicate (English is a foreign language for me). What I ended up writing was "Subjects matching this predicate..." This seems to be incorrect, since predicates are functions and not regular expressions. But saying "Subjects for which this predicate returns true..." sounds awkward to me as well.. So what would be the correct term?

    Read the article

  • LINQ/LAMBDA filter query by date [on hold]

    - by inquisitive_one
    I'm trying to use LINQ to SQL to retrieve earnings data for a particular date range. Currently the table is set up as follows: Comp Eps Year Quarter IBM .5 2012 2 IBM .65 2012 3 IBM .60 2012 4 IBM .5 2011 2 IBM .7 2013 1 IBM .8 2013 2 Except for Eps, all fields have a data type of string or char. Eps has a data type of double. Here's my code: var myData = myTable .Where(t => t.Comp.Equals("IBM") && Convert.Int32(string.Format("{0}{1}", t.Year, t.Quarter)) <= 20131); I get the following error when I tried that code: Method 'System.String Format(System.String, System.Object, System.Object)' has no supported translation to SQL How can I select all Eps that has a year & quarter less than "20132" using a lambda expression?

    Read the article

  • Dates set according to location not chosen language

    - by aspersieman
    I recently installed Ubuntu 13.10 chose my location as Thailand/Bangkok, keyboard layout and language as South African English. Everything seems to be set up correctly except wherever a date is displayed it is displayed in Thai, instead of English. This is most noticeable in the clock indicator (see images included for what I mean). How can I change my dates to English (South African locale if possible).

    Read the article

  • Complex query in mysql

    - by Satish
    I have two tables reports and holidays. reports: (username varchar(30),activity varchar(30),hours int(3),report_date date) holidays: (holiday_name varchar(30), holiday_date date) select * from reports gives +----------+-----------+---------+------------+ | username | activity | hours | date | +----------+-----------+---------+------------+ | prasoon | testing | 3 | 2009-01-01 | | prasoon | coding | 4 | 2009-01-03 | | gautam | coding | 1 | 2009-01-05 | | prasoon | coding | 4 | 2009-01-06 | | prasoon | coding | 4 | 2009-01-10 | | gautam | coding | 4 | 2009-01-10 | +----------+-----------+---------+------------+ select * from holidays gives +--------------+---------------+ | holiday_name | holiday_date | +--------------+---------------+ | Diwali | 2009-01-02 | | Holi | 2009-01-05 | +--------------+---------------+ When I used the following query SELECT dates.date AS date, CASE WHEN holiday_name IS NULL THEN COALESCE(reports.activity, 'Absent') WHEN holiday_name IS NOT NULL and reports.activity IS NOT NULL THEN reports.activity ELSE '' END AS activity, CASE WHEN holiday_name IS NULL THEN COALESCE(reports.hours, 'Absent') WHEN holiday_name IS NOT NULL and reports.hours IS NOT NULL THEN reports.hours ELSE '' END AS hours, CASE WHEN holiday_name IS NULL THEN COALESCE(holidays.holiday_name, '') ELSE holidays.holiday_name END AS holiday_name FROM dates LEFT OUTER JOIN reports ON dates.date = reports.date LEFT OUTER JOIN holidays ON dates.date = holidays.holiday_date where reports.username='gautam' and dates.date>='2009-01-01' and dates.date<='2009-01-10'; I got the following output +----------+-----------+---------+------------+ | date | activity | hours | holiday | +----------+-----------+---------+------------+ |2009-01-05| coding | 1 | Holi | +----------+-----------+---------+------------+ |2009-01-10| coding | 4 | | +----------+-----------+---------+------------+ but I expected this +----------+-----------+---------+------------+ | date | activity | hours | holiday | +----------+-----------+---------+------------+ |2009-01-01| Absent | Absent | | +----------+-----------+---------+------------+ |2009-01-02| | | Diwali | +----------+-----------+---------+------------+ |2009-01-03| Absent | Absent | | +----------+-----------+---------+------------+ |2009-01-04| Absent | Absent | | +----------+-----------+---------+------------+ |2009-01-05| Coding | 1 | Holi | +----------+-----------+---------+------------+ |2009-01-06| Absent | Absent | | +----------+-----------+---------+------------+ |2009-01-07| Absent | Absent | | +----------+-----------+---------+------------+ |2009-01-08| Absent | Absent | | +----------+-----------+---------+------------+ |2009-01-09| Absent | Absent | | +----------+-----------+---------+------------+ |2009-01-10| Coding | 4 | | +----------+-----------+---------+------------+ How can I modify the above query to get the desired output(for a particular user (gautam in this case))?

    Read the article

  • Accommodating hierarchical data in SQL Server 2005 database design

    - by Remnant
    Context I am fairly new to database design (=know the basics) and am grappling with how best to design my database for a project I am currently working on. In short, my database will keep a log of which employees have attended certain health and safety courses throughout the year. There are multiple types of course e.g. moving objects, fire safety, hygiene etc. In terms of my database design I need to accommodate the following: Each location can have multiple divisions Each division can have multiple departments Each department can have multiple functions Each function can have multiple job roles Each job role can have different course requirements Also note that the structure at each location may not be the same e.g. the departments within divisions are not the same across locations and the functions within departments may also differ. Edit - updated to better articulate problem Let's assume I am just looking at Location, Division and Department and I have my database as follows: LocationTable DivisionTable DepartmentTable LocationID(PK) DivisionID(PK) DepartmentID(PK) LocationName DivisionName DepartmentName There is a many-to-many relationship between Locations and Divisions and also between Departments and Divisions. Suppose I set up a 'Junction Table' as follows: Location_Division LocationID(FK) DivisionID(FK) Using Location_Division I could easily pull back the Divisions for any Location. However, suppose I want to pull back all departments for a given Division in a given Location. If I set up another 'Junction Table' for Division and Department then I can't see how I would differentiate Division by Location? Division_Department DivisionID(FK) DepartmentID(FK) Location_Division Division_Department LocationID DivisionID DivisionID DepartmentID 1 1 1 1 1 2 1 2 2 1 2 1 2 2 2 2 Do I need to expand the number of columns in my 'Junction Table' e.g. Location_Division_Department LocationID(FK) DivisionID(FK) DepartmentID(FK) Location_Division_Department LocationID DivisionID DepartmentID 1 1 1 1 1 2 1 1 3 2 1 1 2 1 2 2 1 3

    Read the article

  • Select multiple records from sql database table in a master-detail scenario

    - by Trex
    Hello, I have two tables in a master-detail relationship. The structure is more or less as follows: Master table: MasterID, DetailID, date, ... masterID1, detailID1, 2010/5/1, .... masterID2, detailID1, 2008/6/14, ... masterID3, detailID1, 2009/5/25, ... masterID4, detailID2, 2008/7/24, ... masterID5, detailID2, 2010/4/1, ... masterID6, detailID4, 2008/9/16, ... Details table: DetailID, ... detailID1, ... detailID2, ... detailID3, ... detailID4, ... I need to get all the records from the details table plus the LAST record from the master table (last by the date in the master table). Like this: detailID1, masterID1, 2010/5/1, .... detailID2, masterID5, 2010/4/1, ... detailID3, null, null, ... detailID4, masterID6, 2008/9/16, ... I have no idea how to do this. Can anybody help me? Thanks a lot. Jan

    Read the article

  • LINQ to Sql: Insert instead of Update

    - by Christina Mayers
    I am stuck with this problems for a long time now. Everything I try to do is insert a row in my DB if it's new information - if not update the existing one. I've updated many entities in my life before - but what's wrong with this code is beyond me (probably something pretty basic) I guess I can't see the wood for the trees... private Models.databaseDataContext db = new Models.databaseDataContext(); internal void StoreInformations(IEnumerable<EntityType> iEnumerable) { foreach (EntityType item in iEnumerable) { EntityType type = db.EntityType.Where(t => t.Room == item.Room).FirstOrDefault(); if (type == null) { db.EntityType.InsertOnSubmit(item); } else { type.Date = item.Date; type.LastUpdate = DateTime.Now(); type.End = item.End; } } } internal void Save() { db.SubmitChanges(); } Edit: just checked the ChangeSet, there are no updates only inserts. For now I've settled with foreach (EntityType item in iEnumerable) { EntityType type = db.EntityType.Where(t => t.Room == item.Room).FirstOrDefault(); if (type != null) { db.Exams.DeleteOnSubmit(type); } db.EntityType.InsertOnSubmit(item); } but I'd love to do updates and lose these unnecessary delete statements.

    Read the article

  • SQL Latest photos from contacts (grouped by contact)

    - by kitsched
    Hello, To short version of this question is that I want to accomplish something along the lines of what's visible on Flickr's homepage once you're logged in. It shows the three latest photos of each of your friends sorted by date but grouped by friend. Here's a longer explanation: For example I have 3 friends: John, George and Andrea. The list I want to extract should look like this: George Photo - 2010-05-18 Photo - 2010-05-18 Photo - 2010-05-12 John Photo - 2010-05-17 Photo - 2010-05-14 Photo - 2010-05-12 Andrea Photo - 2010-05-15 Photo - 2010-05-15 Photo - 2010-05-15 Friend with most recent photo uploaded is on top but his or her 2 next files follow. I'd like to do this from MySQL, and for the time being I got here: SELECT photos.user_id, photos.id, photos.date_uploaded FROM photos WHERE photos.user_id IN (SELECT user2_id FROM user_relations WHERE user1_id = 8) ORDER BY date_uploaded DESC Where user1_id = 8 is the currently logged in user and user2_id are the ids of friends. This query indeed returns the latest files uploaded by the contacts of the user with id = 8 sorted by date. However I'd like to accomplish the grouping and limiting mentioned above. Hopefully this makes sense. Thank you in advance.

    Read the article

  • SQL query to calculate running group counts on time-phased data

    - by spong
    I have some data, like this: BUG DATE STATUS ---- ---------------------- -------- 9012 18/03/2008 9:08:44 AM OPEN 9012 18/03/2008 9:10:03 AM OPEN 9012 28/03/2008 4:55:03 PM RESOLVED 9012 28/03/2008 5:25:00 PM CLOSED 9013 18/03/2008 9:12:59 AM OPEN 9013 18/03/2008 9:15:06 AM RESOLVED 9013 18/03/2008 9:16:44 AM CLOSED 9014 18/03/2008 9:17:54 AM OPEN 9014 18/03/2008 9:18:31 AM RESOLVED 9014 18/03/2008 9:19:30 AM CLOSED 9015 18/03/2008 9:22:40 AM OPEN 9015 18/03/2008 9:23:03 AM RESOLVED 9015 19/03/2008 12:27:08 PM CLOSED 9016 18/03/2008 9:24:20 AM OPEN 9016 18/03/2008 9:24:35 AM RESOLVED 9016 19/03/2008 12:28:14 PM CLOSED 9017 18/03/2008 9:25:47 AM OPEN 9017 18/03/2008 9:26:02 AM RESOLVED 9017 19/03/2008 12:30:30 PM CLOSED Which I would like to transform into something like this: DATE OPEN RESOLVED CLOSED ---------------------- -------- -------- -------- 18/03/2008 9:08:44 AM 1 0 0 18/03/2008 9:12:59 AM 2 0 0 18/03/2008 9:15:06 AM 1 1 0 18/03/2008 9:16:44 AM 1 0 1 18/03/2008 9:17:54 AM 2 0 1 18/03/2008 9:18:31 AM 1 1 0 18/03/2008 9:19:30 AM 1 0 2 18/03/2008 9:22:40 AM 2 0 2 18/03/2008 9:23:03 AM 1 1 2 18/03/2008 9:24:20 AM 2 1 2 18/03/2008 9:24:35 AM 1 2 2 18/03/2008 9:25:47 AM 2 2 2 18/03/2008 9:26:02 AM 1 3 2 19/03/2008 12:27:08 PM 1 2 3 19/03/2008 12:28:14 PM 1 1 4 19/03/2008 12:30:30 PM 1 0 5 28/03/2008 4:55:03 PM 0 1 5 28/03/2008 5:25:00 PM 0 0 6 i.e. keeping running counts of bugs with each status. This is easy enough to code up using cursors, but I'm wondering if any of you SQL gurus out there can help with a query to achieve this? Ideally for mysql, but I'm curious to see anything that will work.

    Read the article

  • Consecutive absences in MySQL

    - by pepersview
    Hi, I have 2 tables in my db: students and absences. In students: id_student, s_name; and in absences: id_student, date, day(the day of the week as number-it's not so important); I would like to get the students that were absent for more than 2 days consecutive and the dates. At the moment I have the next query: /*To control when the days are in the same week*/ SELECT id_student, date, dayname(date),date_add(date, INTERVAL 1 DAY) AS a, dayname(date_add(date, INTERVAL 1 DAY)) AS an, date_add(date, INTERVAL 2 DAY) AS b, dayname(date_add(date, INTERVAL 2 DAY)) AS bn FROM absences AS a WHERE id_student IN (SELECT id_student FROM absences WHERE id_student = a.id_student AND date = date_add(a.date, INTERVAL 1 DAY) ) AND id_student IN (SELECT id_student FROM absences WHERE id_student = a.id_student AND date = date_add(a.date,INTERVAL 2 DAY) ) UNION /*To control when the days jump to the next week*/ SELECT id_student, date, dayname(date),date_add(date, INTERVAL 3 DAY) AS a, dayname(date_add(date, INTERVAL 3 DAY)) AS an, date_add(date, INTERVAL 4 DAY) AS b, dayname(date_add(date, INTERVAL 4 DAY)) AS bn FROM absences AS a WHERE id_student IN (SELECT id_student FROM absences WHERE id_student = a.id_student AND date = date_add(a.date, INTERVAL 3 DAY) ) AND id_student IN (SELECT id_student FROM absences WHERE id_student = a.id_student AND date = date_add(a.date,INTERVAL 4 DAY) ) /* To avoid the case (Monday-Thursday-Friday) to be consider as consecutive days*/ AND WEEKDAY(date) !=0 Thanks

    Read the article

  • Is there a set based solution for this problem?

    - by NYSystemsAnalyst
    We have a table set up as follows: |ID|EmployeeID|Date |Category |Hours| |1 |1 |1/1/2010 |Vacation Earned|2.0 | |2 |2 |2/12/2010|Vacation Earned|3.0 | |3 |1 |2/4/2010 |Vacation Used |1.0 | |4 |2 |5/18/2010|Vacation Earned|2.0 | |5 |2 |7/23/2010|Vacation Used |4.0 | The business rules are: Vacation balance is calculated by vacation earned minus vacation used. Vacation used is always applied against the oldest vacation earned amount first. We need to return the rows for Vacation Earned that have not been offset by vacation used. If vacation used has only offset part of a vacation earned record, we need to return that record showing the difference. For example, using the above table, the result set would look like: |ID|EmployeeID|Date |Category |Hours| |1 |1 |1/1/2010 |Vacation Earned|1.0 | |4 |2 |5/18/2010|Vacation Earned|1.0 | Note that record 2 was eliminated because it was completely offset by used time, but records 1 and 4 were only partially used, so they were calculated and returned as such. The only way we have thought of to do this is to get all of the vacation earned records in a temporary table. Then, get the total vacation used and loop through the temporary table, deleting the oldest record and subtracting that value from the total vacation used until the total vacation used is zero. We could clean it up for when the remaining vacation used is only part of the oldest vacation earned record. This would leave us with just the outstanding vacation earned records. This works, but it is very inefficient and performs poorly. Also, the performance will just degrade over time as more and more records are added. Are there any suggestions for a better solution, preferable set based? If not, we'll just have to go with this.

    Read the article

  • Is it worth a try LINQ to SQL as a beginner to an ORM?

    - by Pandiya Chendur
    Thus far used sql server stored procedures for all my web applications... Now thought of moving to an ORM... I would like to ask SO users about LINQ to SQL Is Linq to sql worth a try as a beginner to an ORM? or should i look for some others... Any suggestion... EDIT: I have a sql server 2005 database with all tables.... How to use this db with Linq to sql?

    Read the article

  • Does running a SQL Server 2005 database in compatibility level 80 have a negative impact on performa

    - by Templar
    Our software must be able to run on SQL Server 2000 and 2005. To simplify development, we're running our SQL Server 2005 databases in compatibility level 80. However, database performance seems slower on SQL 2005 than on SQL 2000 in some cases (we have not confirmed this using benchmarks yet). Would upgrading the compatibility level to 90 improve performance on the SQL 2005 servers?

    Read the article

  • How do I create a Linked Server in SQL Server 2005 to a password protected Access 95 database?

    - by Brad Knowles
    I need to create a linked server with SQL Server Management Studio 2005 to an Access 95 database, which happens to be password protected at the database level. User level security has not been implemented. I cannot convert the Access database to a newer version. It is being used by a 3rd party application; so modifying it, in any way, is not allowed. I've tried using the Jet 4.0 OLE DB Provider and the ODBC OLE DB Provider. The 3rd party application creates a System DSN (with the proper database password), but I've not had any luck in using either method. If I were using a standard connection string, I think it would look something like this: Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Test.mdb';Jet OLEDB:Database Password=####; I'm fairly certain I need to somehow incorporate Jet OLEDB:Database Password into the linked server setup, but haven't figured out how. I've posted the scripts I'm using along with the associated error messages below. Any help is greatly appreciated. I'll provide more details if needed, just ask. Thanks! Method #1 - Using the Jet 4.0 Provider When I try to run these statements to create the linked server: sp_dropserver 'Test', 'droplogins'; EXEC sp_addlinkedserver @server = N'Test', @provider = N'Microsoft.Jet.OLEDB.4.0', @srvproduct = N'Access DB', @datasrc = N'C:\Test.mdb' GO EXEC sp_addlinkedsrvlogin @rmtsrvname=N'Test', @useself=N'False',@locallogin=NULL, @rmtuser=N'Admin', @rmtpassword='####' GO I get this error when testing the connection: TITLE: Microsoft SQL Server Management Studio ------------------------------ "The test connection to the linked server failed." ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Test" reported an error. Authentication failed. Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Test". OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Test" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.". (Microsoft SQL Server, Error: 7399) ------------------------------ Method #2 - Using the ODBC Provider... sp_dropserver 'Test', 'droplogins'; EXEC sp_addlinkedserver @server = N'Test', @provider = N'MSDASQL', @srvproduct = N'ODBC', @datasrc = N'Test:DSN' GO EXEC sp_addlinkedsrvlogin @rmtsrvname=N'Test', @useself=N'False',@locallogin=NULL, @rmtuser=N'Admin', @rmtpassword='####' GO I get this error: TITLE: Microsoft SQL Server Management Studio ------------------------------ "The test connection to the linked server failed." ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "Test". OLE DB provider "MSDASQL" for linked server "Test" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed". OLE DB provider "MSDASQL" for linked server "Test" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed". OLE DB provider "MSDASQL" for linked server "Test" returned message "[Microsoft][ODBC Microsoft Access Driver] Cannot open database '(unknown)'. It may not be a database that your application recognizes, or the file may be corrupt.". (Microsoft SQL Server, Error: 7303)

    Read the article

  • How do i setup a Window server 2008 R2 + SQL server 2008 VPS ?

    - by Spencer Lim
    I wish to deploy a trusted apps at the secured way... i got one empty VPS (no operating system) but i don't know how could i install Window server 2008 R2 and SQL server 2008 the version i got is genuine enterprise/ datacenter and sql enterprise the main purpose is used to deploy ASP.Net v4 MVC 2 and XBAP Apps + LINQ also use SQL server for my window application with someway to make it able to remote access May i know anyone here could teach me or introduce some source for me on how to setup the domain, IP, OS and feature all thing, please... i felt confuse here @.@

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 272 273 274 275 276 277 278 279 280 281 282 283  | Next Page >