Search Results

Search found 99645 results on 3986 pages for 'sql server 2005'.

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 214/3986 | < Previous Page | 210 211 212 213 214 215 216 217 218 219 220 221  | Next Page >

  • delete all but minimal values, based on two columns in SQL Server table

    - by sqlill
    how to write a statement to accomplish the folowing? lets say a table has 2 columns (both are nvarchar) with the following data col1 10000_10000_10001_10002_10002_10002 col2 10____20____10____30____40_____50 I'd like to keep only the following data: col1 10000_10001_10002 col2 10____10____30 thus removing the duplicates based on the second column values (neither of the columns are primary keys), keeping only those records with the minimal value in the second column. how to accomplish this?

    Read the article

  • Ubuntu server or Debian server (to run C++ apps developed on Ubuntu)

    - by skyeagle
    I have written a number of C++ server side daemons for my website, using my Ubuntu 9.10 dev machine. The C++ apps I mentioned above are "GUI-less" daemons (and libraries used by the daemons). I am now about to host my website and need to decide whether to go with Debian server or Ubuntu server. In a nutshell, here is the situation: I developed on Ubuntu desktop because I preferred the more friendly GUI I would like to deploy on Debian Server because of the (perceived?) robustness of the Debian server over Ubuntu server (I may be totally wrong here - and in fact, this is really what this question is all about) If Debian server is indeed more robust than Ubuntu server, then I have no choice but to go with Debian server - BUT, will my Ubuntu developed C++ apps run on the server? (or do I need to recompile them on the server? (I'd HATE to have to do this, because I want to keep the server machine clean and light - no GUI, no dev tools etc). This last question is really about binary compatability between Ubuntu and Debian. I want the server to be robust, secure and stable, and simply act as a server (i.e. LAMP and very little else - no GUI etc). Given that requirement, and the fact that I need to run my C++ apps (developed on Ubuntu 9.10), I need advice on which OS to choose for the server. Ideally, any advice will be backed with a reason. I am particularly interested in hearing from people who have been in an identical situation, or done something similar.

    Read the article

  • SQL Server 2008 - A clever way to fill a column with rising integers

    - by cc0
    This is a really silly question probably, I'm working on a database and what I want to do is create a table with an ID (auto increment) and another column: "Number" (I realize it sounds useless but bare with me here please), and I need to fill this "Number" column with values from 1 to 180, each time adding 1 to the previous. What would be a clever "automatic" way of doing that?

    Read the article

  • Having trouble using 'AND' in CONTAINSTABLE SQL SERVER FULL TEXT SEARCH

    - by Joshua
    I've been using FULL-TEXT for awhile but I cannot seem to get the most relevant results sometimes. If I have an field with something like "An Overview of Pain Medicine 5/12/2006" and a user types "An Overview 5/12/2006" So we create a search like: '"An" AND "Overview" AND "5/12/2006"' - 0 results (bad) '"Overview" AND "5/12/2006"' - 1 result (good) The CONTAINSTABLE portion of my query: FROM ce_Activity A INNER JOIN CONTAINSTABLE(View_Activities,(Searchable), @Search) AS KeyTbl ON A.ActivityID = KeyTbl.[KEY] "Searchable" is a field contains the activity title, and start date(converted to string) in one field so it's all search friendly. Why would this happen?

    Read the article

  • INNER JOIN code calculated value with SELECT statement

    - by sp-1986
    I have the following stored procedure which will generate mon to sun and then creates a temp table with a series of 'weeks' (start and end weeks) : USE [test_staff] GO /****** Object: StoredProcedure [dbo].[sp_timesheets_all_staff_by_week_by_job_grouping_by_site] Script Date: 03/21/2012 09:04:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[sp_timesheets_all_staff_by_week_by_job_grouping_by_site] ( @grouping_ref int, @week_ref int ) AS CREATE TABLE #WeeklyList ( Start_Week date, End_Week date, week_ref int --month_name date ) DECLARE @REPORT_DATE DATETIME, @WEEK_BEGINING VARCHAR(10) SELECT @REPORT_DATE = '2011-01-19T00:00:00' --SELECT @REPORT_DATE = GETDATE() -- should grab the date now. SELECT @WEEK_BEGINING = 'MONDAY' IF @WEEK_BEGINING = 'MONDAY' SET DATEFIRST 1 ELSE IF @WEEK_BEGINING = 'TUESDAY' SET DATEFIRST 2 ELSE IF @WEEK_BEGINING = 'WEDNESDAY' SET DATEFIRST 3 ELSE IF @WEEK_BEGINING = 'THURSDAY' SET DATEFIRST 4 ELSE IF @WEEK_BEGINING = 'FRIDAY' SET DATEFIRST 5 ELSE IF @WEEK_BEGINING = 'SATURDAY' SET DATEFIRST 6 ELSE IF @WEEK_BEGINING = 'SUNDAY' SET DATEFIRST 7 DECLARE @WEEK_START_DATE DATETIME, @WEEK_END_DATE DATETIME --GET THE WEEK START DATE SELECT @WEEK_START_DATE = @REPORT_DATE - (DATEPART(DW, @REPORT_DATE) - 1) --GET THE WEEK END DATE SELECT @WEEK_END_DATE = @REPORT_DATE + (7 - DATEPART(DW, @REPORT_DATE)) PRINT 'Week Start: ' + CONVERT(VARCHAR, @WEEK_START_DATE) PRINT 'Week End: ' + CONVERT(VARCHAR, @WEEK_END_DATE) DECLARE @Interval int = datediff(WEEK,getdate(),@WEEK_START_DATE)+1 --SELECT Start_Week=@WEEK_START_DATE --, End_Week=@WEEK_END_DATE --INTO #WeekList INSERT INTO #WeeklyList SELECT Start_Week=@WEEK_START_DATE, End_Week=@WEEK_END_DATE WHILE @Interval <= 0 BEGIN set @WEEK_START_DATE=DATEADD(WEEK,1,@WEEK_START_DATE) set @WEEK_END_DATE=DATEADD(WEEK,1,@WEEK_END_DATE) INSERT INTO #WeeklyList values (@WEEK_START_DATE,@WEEK_END_DATE) SET @Interval += 1; END SELECT CONVERT(VARCHAR(11), Start_Week, 106) AS 'month_name', CONVERT(VARCHAR(11), End_Week, 106) AS 'End', DATEDIFF(DAY, 0, Start_Week) / 7 AS week_ref -- create the unique week reference number --'VIEW' AS month_name FROM #WeeklyList In this section i am creating the week_ref DATEDIFF(DAY, 0, Start_Week) / 7 AS week_ref -- create the unique week reference number I then need to combine it with this select code: DECLARE @YearString char(3) = CONVERT(char(3), SUBSTRING(CONVERT(char(5), @week_ref), 1, 3)) DECLARE @MonthString char(2) = CONVERT(char(2), SUBSTRING(CONVERT(char(5), @week_ref), 4, 2)) --Convert: DECLARE @Year int = CONVERT(int, @YearString) + 1200 DECLARE @Month int = CONVERT(int, @MonthString) **--THIS FILTERS THE REPORT** SELECT ts.staff_member_ref, sm.common_name, sm.department_name, DATENAME(MONTH, ts.start_dtm) + ' ' + DATENAME(YEAR, ts.start_dtm) AS month_name, ts.timesheet_cat_ref, cat.desc_long AS timesheet_cat_desc, grps.grouping_ref, grps.description AS grouping_desc, ts.task_ref, tsks.task_code, tsks.description AS task_desc, ts.site_ref, sits.description AS site_desc, ts.site_ref AS Expr1, CASE WHEN ts .status = 0 THEN 'Pending' WHEN ts .status = 1 THEN 'Booked' WHEN ts .status = 2 THEN 'Approved' ELSE 'Invalid Status' END AS site_status, ts.booked_time AS booked_time_sum, start_dtm, CONVERT(varchar(20), start_dtm, 108) + ' ' + CONVERT(varchar(20), start_dtm, 103) AS start_dtm_text, booked_time, end_dtm, CONVERT(varchar(20), end_dtm, 108) + ' ' + CONVERT(varchar(20), end_dtm, 103) AS end_dtm_text FROM timesheets AS ts INNER JOIN timesheet_categories AS cat ON ts.timesheet_cat_ref = cat.timesheet_cat_ref INNER JOIN timesheet_tasks AS tsks ON ts.task_ref = tsks.task_ref INNER JOIN timesheet_task_groupings AS grps ON tsks.grouping_ref = grps.grouping_ref INNER JOIN timesheet_sites AS sits ON ts.site_ref = sits.site_ref INNER JOIN vw_staff_members AS sm ON ts.staff_member_ref = sm.staff_member_ref WHERE (ts.status IN (1, 2)) AND (cat.is_leave_category = 0) GROUP BY ts.staff_member_ref, sm.common_name, sm.department_name, DATENAME(MONTH, ts.start_dtm), DATENAME(YEAR, ts.start_dtm), ts.timesheet_cat_ref, cat.desc_long, grps.grouping_ref, grps.description, ts.status, ts.booked_time, ts.task_ref, tsks.task_code, tsks.description, ts.site_ref, sits.description, ts.start_dtm, ts.end_dtm ORDER BY sm.common_name, timesheet_cat_desc, tsks.task_code, site_desc DROP TABLE #WeeklyList GO I want to pass the week_ref into the SELECT statement (refer to comment - THIS FILTERS THE REPORT) but the problem is week_ref isnt a valid column as its derived by code. Any ideas?

    Read the article

  • Login problems on SQL EXPRESS using a user

    - by meep
    Hello Serverfault. First time I set up a SQL server, so I hope you can help me out. I have a problem regarding logging in using SQL auth on my SQL EXPRESS 2008. I have added a user though the management interface as you can see on the image below. But as soon as I try to login using SQL auth I get an error the login failed for the user. The server log says: Login failed for user 'zebisgaard'. Reason: Could not find a login matching the name provided. [CLIENT: <named pipe>] Error: 18456, Severity: 14, State: 5. Do you have an idea why? I have triple checked that the username/password is correct, tried to recreate the user and so much more. And all this is localhost.

    Read the article

  • SQL Server iSCSI session issue with a NetApp SAN

    - by Matt Beckman
    We had an issue early this morning when iSCSI issues broke connectivity with a few of our databases (resulting in a SQL Server Error 21). Attempts to DBCC CheckDB did not work, and the only solution was to restart the SQL Service. Is there a known reason why an iSCSI initiator session would reset itself out of the blue? Example below from the NetApp syslog. This set of errors was replicated 4 times (once for each SQL server in production). Only one SQL server was noticeably impacted, however. [san1: iscsi.notice:notice]: ISCSI: iswta, ISID Rule: new connection from same initiator, shutting down old session 7 [san1: iscsi.notice:notice]: ISCSI: iswta, New session from initiator iqn.1991-05.com.microsoft:sql1.example.corp at IP addr 10.xxx.xxx.123

    Read the article

  • SQL Server 2008: CASE vs IF-ELSE-IF vs GOTO

    - by Saharsh Shah
    I have some rules in my application and I have written the business logic of that rules in my procedure. At the time of creation of procedure I came to know that CASE statement won't work in my scenario. So I have tried two ways to perform same operations (using IF-ELSE-IF or GOTO) shown as below. Method 1 Using IF-ELSE-IF conditions: DECLARE @V_RuleId SMALLINT; IF (@V_RuleId = 1) BEGIN /*My business logic*/ END ELSE IF (@V_RuleId = 2) BEGIN /*My business logic*/ END ELSE IF (@V_RuleId = 3) BEGIN /*My business logic*/ END /* ... ... ... ...*/ ELSE IF (@V_RuleId = 19) BEGIN /*My business logic*/ END ELSE IF (@V_RuleId = 20) BEGIN /*My business logic*/ END Method 2 Using GOTO statement: DECLARE @V_RuleId SMALLINT, @V_Temp VARCHAR(100); SET @V_Temp = 'GOTO RULE' + CONVERT(VARCHAR, @V_RuleId); EXECUTE sp_executesql @V_Temp; RULE1: BEGIN /*My business logic*/ END RULE2: BEGIN /*My business logic*/ END RULE3: BEGIN /*My business logic*/ END /* ... ... ... ...*/ RULE19: BEGIN /*My business logic*/ END RULE20: BEGIN /*My business logic*/ END Today I have 20 rules. It can be increase to any number in future. If I can able to use CASE statement then I have not any problem with performance, but I can't do that so I am worried about the performance of my procedure. Also one thing to be noticed that this procedure will execute very frequently by application. My questions are: Is there any way to use CASE statement in my procedure? If not, which method is best to use in my procedure to improve the performance of my code? Thanks in advance...

    Read the article

  • Trying to verify understanding of foreign keys SQL Server

    - by msarchet
    So I'm working on just a learning project to expose myself to doing some things I do not get to do at work. I'm just making a simple bug and case tracking app (I know there are a million this is just to work with some tools I don't get to). So I was designing my database and realized I've never actually used Foreign Keys before in any of my projects, I've used them before but never actually setting up a column as a FK. So I've designed my database as follows, which I think is close to correct (at least for the initial layout). However When I try to add the FK's to the linking Tables I get an error saying, "The tables present in the relationship must have the same number of columns". I'm doing this by in SQLSMS by going to the Keys 'folder' and adding a FK. Is there something that I am doing wrong here, I don't understand why the tables would have to have the same number of columns for me to add a FK relationship between the tables?

    Read the article

  • T-SQL aggregate function Logical error

    - by Yoda
    Hi Guys, Ok I have a data table containing duplicate Reciept numbers and a transaction value for each record, I need to simply list the total for each unique Reciept number, this is obviously a simple problem but I am missing something. Any help is much appriciated > SELECT Gf_Receipt_number AS Reciept, > SUM (Gf_Amount) AS Total FROM > [TestP].[dbo].[Gf] WHERE > Gf_Receipt_number IS NOT NULL GROUP > BY Gf_Amount Would probably help to mention the the error I am recieving is: Column 'TestP.dbo.Gf.Gf_Receipt_number' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    Read the article

  • SQL Joins Excluding Data

    - by Andrew
    Say I have three tables: Fruit (Table 1) ------ Apple Orange Pear Banana Produce Store A (Table 2 - 2 columns: Fruit for sale => Price) ------------------------- Apple => 1.00 Orange => 1.50 Pear => 2.00 Produce Store B (Table 3 - 2 columns: Fruit for sale => Price) ------------------------ Apple => 1.10 Pear => 2.50 Banana => 1.00 If I would like to write a query with Column 1: the set of fruit offered at Produce Store A UNION Produce Store B, Column 2: Price of the fruit at Produce Store A (or null if that fruit is not offered), Column 3: Price of the fruit at Produce Store B (or null if that fruit is not offered), how would I go about joining the tables? I am facing a similar problem (with more complex tables), and no matter what I try, if the "fruit" is not at "produce store a" but is at "produce store b", it is excluded (since I am joining produce store a first). I have even written a subquery to generate a full list of fruits, then left join Produce Store A, but it is still eliminating the fruits not offered at A. Any Ideas?

    Read the article

  • SQL Server view: how to add missing rows using interpolation

    - by Christopher Klein
    Running into a problem. I have a table defined to hold the values of the daily treasury yield curve. It's a pretty simple table used for historical lookup of values. There are notibly some gaps in the table on year 4, 6, 8, 9, 11-19 and 21-29. The formula is pretty simple in that to calculate year 4 it's 0.5*Year3Value + 0.5*Year5Value. The problem is how can I write a VIEW that can return the missing years? I could probably do it in a stored procedure but the end result needs to be a view.

    Read the article

  • SQL Server 2000 + ASP.NET: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

    - by Rick
    I just migrated a development workstation FROM: Windows XP Pro SP3 with IIS 6 TO: Vista Enterprise 64bit with IIS 7 Since the move, one of my pages that accesses an SQL Server 2000 database is receiving the following error from my ASP.NET 2.0 web page: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." I have: enabled Windows Authentication in IIS and web.config disabled Anonymous Authentication in IIS set up Impersonation to run as the authenticated user verified that the logged in user (in this case, me) has access to the appropriate database on the SQL Server verified that my login and impersonation information is correct in the ASP.NET page by checking User.Identity.Name and System.Security.Principal.WindowsIdentity.GetCurrent().Name (both display my username) My connection string using SqlConnection is "Server={SERVER_NAME};Database={DB_NAME};Integrated Security=SSPI;Trusted_Connection=True;" Why is it trying to login with NT AUTHORITY\ANONYMOUS LOGIN? I have to assume it's some setting or web.config entry specific to IIS7 since it worked fine before the migration. NOTE: The SQL Server is Windows authentication only - no mixed mode or SQL only.

    Read the article

  • AutoRestart error in SQL Server logs (reason: Access is denied)

    - by Michael Teper
    My instance of SQL Server 2008 SP1 crashed, which is a separate issue, and I see the following set of messages in the SQL Server Agent log: [139] AutoRestart: Attempting to restart the MSSQLSERVER service (attempt #1)... [368] AutoRestart: Unable to restart the MSSQLSERVER service (reason: Access is denied) Is there an authoritative place that describes what permissions should be assigned to a domain account that the SQL Server Agent runs as? Thank you!

    Read the article

  • jquery thickbox not working in 2005 ASP.NET

    - by Alex
    public void DisplayThickBox(Page page, int width, int height) { string script = "<script type='text/javascript'>"; script += "$(document).ready(function(){"; script += "tb.show('null', 'auto-insurance-redirect.aspx?keepThis=true&TB_iframe=true&height=" + height.ToString() + "&amp;width=" + width.ToString() + "',null);"; script += "});"; script +="</script>"; ScriptManager.RegisterStartupScript(page, page.GetType(), "", script, true); } ^^^Method to display the thickbox...^^^ DisplayThickBox(this, 518, 321); ^^^Call to method in the click event of the button that fires it...^^^ Page is just refreshing and the thickbox is never displayed. I'm trying to call the javascript manually since im doing some other stuff before i display the thickbox in the code behind...

    Read the article

  • cannot connect to <server_name>\sqlexpress

    - by Jackson Sunuwar
    I have tried disabling firing wall and checked sqlbrowser is started but for some reason I cannnot connect to my datbase... called server_name\sqlexpress.. I have a virtual machine and a full scale MS SQL Server 2008 R2 running on it... and I have several other vm running sqlexpress. they run fine and I can connect to them using sqlexpress... but when i try to access from sqlserver... I get this error. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1) Digging deep into the error, I found this Error Number: -1 Severity: 20 State: 0 and finally this... Program Location: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server) at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser() Firewall is turned off on the VM that's running mssqlserver... I turned of firewall on one of the vm that's running the sqlexpress but I still get the error... can someone please help... thank you

    Read the article

  • SQL Query with multiple values in one column

    - by lp1
    I've been beating my head on the desk tring to figure this one out. I have a table that stores job information, and reasons for a job not being completed. The reasons are numeric,01,02,03,etc. You can have two reason for a pending job. If you select two reasons, they are stored in the same column, seperated by a comma. This is anExample from the JOBID table: Job_Number User_Assigned PendingInfo 1 user1 01,02 Now, there is another table named Pending, that stores what those values actually represent. 01=Not enough info, 02=Not enough time, 03=Waiting Review. Example: Pending_Num PendingWord 01 Not Enough Info 02 Not Enough Time What I'm trying to do is query the database to give me all the job numbers, users, pendinginfo, and pending reason. I can break out the first value, but can't figure out how to do the second. What my limited skills have so far: *select Job_number,user_assigned,SUBSTRING(pendinginfo,0,3),pendingword from jobid,pending where SUBSTRING(pendinginfo,0,3)=pending.pending_num and pendinginfo!='00,00' and pendinginfo!='NULL'* What I would like to see for this example would be: Job_Number User_Assigned PendingInfo PendingWord PendingInfo PendingWord 1 User1 01 Not Enough Info 02 Not Enough Time Thanks in advance

    Read the article

  • sql exception arithmetic overflow?

    - by MyHeadHurts
    In my program the user imports a date and it works whenever the year is in 2011 but if i try a date in 2010 i get this error which is weird [ SqlException (0x80131904): Arithmetic overflow error converting int to data type numeric.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392 System.Data.SqlClient.SqlDataReader.HasMoreRows() +157 System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +197 System.Data.SqlClient.SqlDataReader.Read() +9 System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) +78 System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) +164 System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +282 System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +19 System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler) +222 System.Data.DataTable.Load(IDataReader reader) +14 ( @YearToGet int, @current datetime, @y int, @search datetime ) AS SET @YearToGet = 2006; WITH Years AS ( SELECT DATEPART(year, GETDATE()) [Year] UNION ALL SELECT [Year]-1 FROM Years WHERE [Year]>@YearToGet ), q_00 as ( select DIVISION , DYYYY , sum(PARTY) as asofPAX , sum(InsAmount) as asofSales from dbo.B101BookingsDetails INNER JOIN Years ON B101BookingsDetails.DYYYY = Years.Year where Booked <= CONVERT(int, DateAdd(year, (Years.Year - @y), @search)) and DYYYY = Years.Year group by DIVISION, DYYYY, years.year having DYYYY = years.year ), q_01 as ( select DIVISION , DYYYY , sum(PARTY) as YEPAX , sum(InsAmount) as YESales from dbo.B101BookingsDetails INNER JOIN Years ON B101BookingsDetails.DYYYY = Years.Year group by DIVISION, DYYYY , years.year having DYYYY = years.year ), q_02 as ( select DIVISION , DYYYY , sum(PARTY) as CurrentPAX , sum(InsAmount) as CurrentSales from dbo.B101BookingsDetails INNER JOIN Years ON B101BookingsDetails.DYYYY = Years.Year where Booked <= CONVERT(int,@current) and DYYYY = (year( getdate() )) group by DIVISION, DYYYY ) select a.DIVISION , a.DYYYY , asofPAX , asofSales , YEPAX , YESales , CurrentPAX , CurrentSales ,asofsales/ ISNULL(NULLIF(yesales,0),1) as percentsales, CAST((asofpax) AS DECIMAL(5,1))/yepax as percentpax from q_00 as a join q_01 as b on (b.DIVISION = a.DIVISION and b.DYYYY = a.DYYYY) join q_02 as c on (b.DIVISION = c.DIVISION) JOIN Years as d on (b.dyyyy = d.year) where A.DYYYY <> (year( getdate() )) order by a.DIVISION, a.DYYYY ;

    Read the article

  • SQL Overlapping and Multi-Column Indexes

    - by durilai
    I am attempting to tune some stored procedures and have a question on indexes. I have used the tuning advisor and they recommended two indexes, both for the same table. The issue is one index is for one column and the other is for multiple columns, of which it includes the same column from the first. My question is why and what is the difference? CREATE NONCLUSTERED INDEX [_dta_index_Table1_5_2079723603__K23_K17_K13_K12_K2_K10_K22_K14_K19_K20_K9_K11_5_6_7_15_18] ON [dbo].[Table1] ( [EfctvEndDate] ASC, [StuLangCodeKey] ASC, [StuBirCntryCodeKey] ASC, [StuBirStOrProvncCodeKey] ASC, [StuKey] ASC, [GndrCodeKey] ASC, [EfctvStartDate] ASC, [StuHspncEnctyIndctr] ASC, [StuEnctyMsngIndctr] ASC, [StuRaceMsngIndctr] ASC, [StuBirDate] ASC, [StuBirCityName] ASC ) INCLUDE ( [StuFstNameLgl], [StuLastOrSrnmLgl], [StuMdlNameLgl], [StuIneligSnorImgrntIndctr], [StuExpctdGrdtngClYear] ) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY] go CREATE NONCLUSTERED INDEX [_dta_index_Table1_5_2079723603__K23] ON [dbo].[Table1] ( [EfctvEndDate] ASC )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

    Read the article

  • How should I set up my Hyper-V server and network topology?

    - by Daniel Waechter
    This is my first time setting up either Hyper-V or Windows 2008, so please bear with me. I am setting up a pretty decent server running Windows Server 2008 R2 to be a remote (colocated) Hyper-V host. It will be hosting Linux and Windows VMs, initially for developers to use but eventually also to do some web hosting and other tasks. Currently I have two VMs, one Windows and one Ubuntu Linux, running pretty well, and I plan to clone them for future use. Right now I'm considering the best ways to configure developer and administrator access to the server once it is moved into the colocation facility, and I'm seeking advice on that. My thought is to set up a VPN for access to certain features of the VMs on the server, but I have a few different options for going about this: Connect the server to an existing hardware firewall (an old-ish Netscreen 5-GT) that can create a VPN and map external IPs to the VMs, which will have their own IPs exposed through the virtual interface. One problem with this choice is that I'm the only one trained on the Netscreen, and its interface is a bit baroque, so others may have difficulty maintaining it. Advantage is that I already know how to do it, and I know it will do what I need. Connect the server directly to the network and configure the Windows 2008 firewall to restrict access to the VMs and set up a VPN. I haven't done this before, so it will have a learning curve, but I'm willing to learn if this option is better long-term than the Netscreen. Another advantage is that I won't have to train anyone on the Netscreen interface. Still, I'm not certain if the capabilities of the Windows software firewall as far as creating VPNs, setting up rules for external access to certain ports on the IPs of Hyper-V servers, etc. Will it be sufficient for my needs and easy enough to set up / maintain? Anything else? What are the limitations of my approaches? What are the best practices / what has worked well for you? Remember that I need to set up developer access as well as consumer access to some services. Is a VPN even the right choice?

    Read the article

  • filling in the holes in the result of a query

    - by ????? ????????
    my query is returning: +------+------+------+------+------+------+------+-------+------+------+------+------+-----+ | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Bla | +------+------+------+------+------+------+------+-------+------+------+------+------+-----+ | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 13 | | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 14 | | 0 | 0 | 0 | 0 | 0 | 9 | 0 | 0 | 0 | 0 | 8 | 37 | 29 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 374 | 30 | | 0 | 0 | 1 | 0 | 78 | 2 | 4 | 8 | 57 | 169 | 116 | 602 | 31 | | 156 | 255 | 79 | 75 | 684 | 325 | 289 | 194 | 407 | 171 | 584 | 443 | 32 | | 1561 | 2852 | 2056 | 796 | 2004 | 1755 | 879 | 1052 | 1490 | 1683 | 2532 | 2381 | 33 | | 4167 | 3841 | 4798 | 3399 | 4132 | 5849 | 3157 | 4381 | 4424 | 4487 | 4178 | 5343 | 34 | | 5472 | 5939 | 5768 | 4150 | 7483 | 6836 | 6346 | 6288 | 6850 | 7155 | 5706 | 5231 | 35 | | 5749 | 4741 | 5264 | 4045 | 6544 | 7405 | 7524 | 6625 | 6344 | 5508 | 6513 | 3854 | 36 | | 5464 | 6323 | 7074 | 4861 | 7244 | 6768 | 6632 | 7389 | 8077 | 8745 | 6738 | 5039 | 37 | | 5731 | 7205 | 7476 | 5734 | 9103 | 9244 | 7339 | 8970 | 9726 | 9089 | 6328 | 5512 | 38 | | 7262 | 6149 | 8231 | 6654 | 9886 | 9834 | 9306 | 10065 | 9983 | 9984 | 6738 | 5806 | 39 | | 5886 | 6934 | 7137 | 6978 | 9034 | 9155 | 7389 | 9437 | 9711 | 8665 | 6593 | 5337 | 40 | +------+------+------+------+------+------+------+-------+------+------+------+------+-----+ as you can see the BLA column starts from 13. i want it to start from 1, then 2, then 3 etc......I do not want any gaps in the data. The reason there are gaps is because all of the months are 0 for that specific bla how do i get the result set to include ALL values for BLA, even ones that will yield 0 for the months? here are the desired results: +-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+ | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | Bla | +-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+ | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 4 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 6 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 7 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 8 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 9 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 10 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 11 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 12 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 13 | | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 14 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 15 | | … | … | … | … | … | … | … | … | … | … | … | … | … | +-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+ here's my query: WITH CTE AS ( select sum(case when datepart(month,[datetime entered]) = 1 then 1 end) as Jan, sum(case when datepart(month,[datetime entered]) = 2 then 1 end) as Feb, sum(case when datepart(month,[datetime entered]) = 3 then 1 end) as Mar, sum(case when datepart(month,[datetime entered]) = 4 then 1 end) as Apr, sum(case when datepart(month,[datetime entered]) = 5 then 1 end) as May, sum(case when datepart(month,[datetime entered]) = 6 then 1 end) as Jun, sum(case when datepart(month,[datetime entered]) = 7 then 1 end) as Jul, sum(case when datepart(month,[datetime entered]) = 8 then 1 end) as Aug, sum(case when datepart(month,[datetime entered]) = 9 then 1 end) as Sep, sum(case when datepart(month,[datetime entered]) = 10 then 1 end) as Oct, sum(case when datepart(month,[datetime entered]) = 11 then 1 end) as Nov, sum(case when datepart(month,[datetime entered]) = 12 then 1 end) as Dec, DATEPART(yyyy,[datetime entered]) as [Year], bla= CASE WHEN datediff(d, CAST([datetime entered] as DATE), CAST([datetime completed] as DATE))*24 + CONVERT(CHAR(2),[datetime completed],108) >191 THEN 192 ELSE datediff(d, CAST([datetime entered] as DATE), CAST([datetime completed] as DATE))*24 + CONVERT(CHAR(2),[datetime completed],108) END --,datediff(d, CAST([datetime entered] as DATE), CAST([datetime completed] as DATE)) AS Sort_Days, --DATEPART(hour, [datetime completed] ) AS Sort_Hours from [TurnAround] group by datediff(d, CAST([datetime entered] as DATE), CAST([datetime completed] as DATE))*24 + CONVERT(CHAR(2),[datetime completed],108), DATEPART(yyyy,[datetime entered]) , [datetime entered] --[DateTime Completed] ) SELECT ISNULL(SUM(Jan),0) Jan, ISNULL(SUM(Feb),0) Feb, ISNULL(SUM(Mar),0) Mar, ISNULL(SUM(Apr),0) Apr, ISNULL(SUM(May),0) May, ISNULL(SUM(Jun),0) Jun, ISNULL(SUM(Jul),0) Jul, ISNULL(SUM(Aug),0) Aug, ISNULL(SUM(Sep),0) Sep, ISNULL(SUM(Oct),0) Oct, ISNULL(SUM(Nov),0) Nov, ISNULL(SUM(Dec),0) Dec, [year], --,Sort_Hours, --Sort_Days, A.RN Bla FROM ( SELECT *, RN=ROW_NUMBER() OVER(ORDER BY object_id) FROM sys.all_objects) A LEFT JOIN CTE B ON A.RN = CASE WHEN B.Bla > 191 THEN 192 ELSE B.Bla END WHERE A.RN BETWEEN 1 AND 192 GROUP BY A.RN,[year]

    Read the article

  • SQl to list rows in not in another table

    - by SmartestVEGA
    I have the following query which have 1000 rows select staffdiscountstartdate,datediff(day,groupstartdate,staffdiscountstartdate),EmployeeID from tblEmployees where GroupStartDate < '20100301' and StaffDiscountStartDate '20100301' and datediff(day,groupstartdate,staffdiscountstartdate)1 order by staffdiscountstartdate desc i have the following query which have 400 rows: ie the employees in tblemployees and in tblcards select a.employeeid,b.employeeid from tblEmployees a,tblCards b where GroupStartDate < '20100301' and StaffDiscountStartDate '20100301' and datediff(day,groupstartdate,staffdiscountstartdate)1 and a.employeeid=b.employeeid How to list the employees which is there in tblemployees and not in tblcards? ie is 1000-400 = 600 rows ???

    Read the article

  • Selecting fields in SQL Select statements (Dumbest SQL Question)

    - by JC
    Hello all, Here's a dumb question which I can't find an answer to: I have a table which contains 20 fields, a few of which are date/time. I am interested in pulling all the fields. I would like to pull the datetime fields using the to_char function but don't want to individually list out all the other fields. Is there an easy way to do this? I tried select *, tochar(dtfield) as dt2 and select tochar(dtfield) as dt2, * and both give errors. Thanks for all your help! JC

    Read the article

  • What does SQL Server execution plan show?

    - by tim
    There is the following code: declare @XmlData xml = '<Locations> <Location rid="1"/> </Locations>' declare @LocationList table (RID char(32)); insert into @LocationList(RID) select Location.RID.value('@rid','CHAR(32)') from @XmlData.nodes('/Locations/Location') Location(RID) insert into @LocationList(RID) select A2RID from tblCdbA2 Table tblCdbA2 has 172810 rows. I have executed the batch in SSMS with “Include Actual execution plan “ and having Profiler running. The plan shows that the first query cost is 88% relative to the batch and the second is 12%, but the profiler says that durations of the first and second query are 17ms and 210 ms respectively, the overall time is 229, which is not 12 and 88.. What is going on? Is there a way how I can determine in the execution plan which is the slowest part of the query?

    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 | 210 211 212 213 214 215 216 217 218 219 220 221  | Next Page >