I want the display like this if posible.. The first to character of the field period is the Month January to December.
Id January February March April ..............
001 5300 00 1000 150 .............
002 1500 00 00 00 .................
select id,qty into #lastyrend from table where dt='12/31/2007'--last yr end
select id, qty into #lastmonthend from table where dt='05/31/2008' --last month end
select id, qty into #lastqtrend from table where dt='03/31/2008'--last qtr end
select id,qty into #lastweeked from table where dt='05/27/2008'
select id,#lastyrend .qty,
case when #lastmonthend.qty is null then #lastyrend .qty as lastmonthend
case when #lastmonthend.qty is null then #lastyrend .qty when #lastqtrend .qty is null then #lastmonthend.qty .qty else #lastqtrend .qty as lastqtr
case when #lastmonthend.qty is null then #lastyrend .qty when #lastqtrend .qty is null then #lastmonthend.qty when #lastweeked .qty is null then #lastqtrend .qty else #lastweeked .qty as lastweekend.
from table left join table.id=#lastyrend .id left join table.id=#lastmonthend.id left join table.id=#lastqtrend.id left join table.id=#lastweeked.id
IN my query I am taking values from a table for different date series.
logic is if there is no value for a particular date then take the previous date value.
Problem --------------
in my query #lastqtrend .qty is null but #lastweeked .qty has the value.
as per my third case statement I expected I will take the #lastweeked .qty
but since #lastqtrend .qty is null it shows the value of #lastmonthend.qty I think here after the
case when #lastmonthend.qty is null then #lastyrend .qty when #lastqtrend .qty is null then #lastmonthend.qty ---- after executing this line below line is not executing when #lastweeked .qty is null then #lastqtrend .qty else
I am facing problems as the data in sqlserver is not case sensitive. The data in parent key may be capital/lower case and the same data in the child table may be lower/capital case. While migrating the data from sqlserver database to other databases(like oracle) its giving error as the data not found in parent key, though the data found in parent table . This is just because the case sensitive in oracle. But according to my knowledge its better if the sqlserver also supports data case sensitive.
Take a small example Need a table to store all the alphabets in a table the table structure is
CREATE TABLE [dbo].[ALPHABET] ( [Alphabet] [varchar] (1) NOT NULL , [Description] [varchar] (50) NOT NULL ) GO
The data is
Alphabet Description
a Small a
b Small b
C Capital C
A Capital A
sqlserver wont allow to insert data 'A' and gives error "voilation of primary key", though a & A are different according to this table.
I tried with NVARCHAR datatype also. The same problem here also. Sqlserver atleast should support data case sensitivity for NVARCHAR datatype as this can store different languages. May be in other languages the entire meaning may be differ with case differences. Even in english language some words meaning will differ with case differences. For reference can refer english dictionary
Does anyone know how to how to performance case-insensitive search onXML data type in SQLServer 2005? Or I have to convert all the xml datato lower case before I store it?Thanks in advance.John
I have created an SQL server table in the past on a server that was all case sensative. Over time I found out that switching to a server that is not case sensative still caused my data to become case sensative. I read an article that said you should rebuild your master database then re-create your tables. So after rebuilding the master database, a basic restore would not be sufficient? I would have to go and manually re-create every single table again?
Can someone point me to a tutorial on how to search against a SQL Server 2000 using a case insensitive search when SQL Server 2000 is a case sensitive installation?
We have a static class that makes an HTTPWebRequest to get XML data from one of our vendors. We use this as input to a stored proc in SQLServer2005. When I compile this class and call it from a console application in visual studio it executes in milliseconds, everytime. When I compile it, create the assembly and clr function and execute it in SQLServer, it takes around 14 seconds to execute the first time, then on subsequent requests it is again really fast, until I wait for 10 seconds and re-execute, once again it is slow the first time and then fast on subsequent requests. We do not see this behavior when executing outside SQLServer. Makes me think that some sort of authentication is perhaps taking place the first time the function is run in SQLServer? I have no idea how to debug this further. Anyone seen this before or have any ideas?
Here is the class:
Code Snippet
using System; using System.Collections.Generic; using System.Text; using System.Net; using System.IO;
namespace Predict.Services { public static class Foo { public static string GetIntradayQuote(string symbol) { string returnQuote = "";
HttpWebRequest request = (HttpWebRequest)(WebRequest.Create("http://data.predict.com/predictws/detailed_quote.html?syms=" + symbol + "&fields=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,28,30"));
StreamReader streamReader = new StreamReader(response.GetResponseStream());
returnQuote = streamReader.ReadToEnd();
streamReader.Close(); response.Close();
return returnQuote; } } }
When I run call it from a console app it is fine.
I compile it into a dll and then create the assembly and function as follows:
Code Snippet
drop function fnTestGetIntradayQuoteXML_SJS
go
drop assembly TestGetIntradayQuoteXML_SJS
go
create ASSEMBLY TestGetIntradayQuoteXML_SJS from 'c:DataBackupsCLRLibrariesTestGetIntradayQuote_SJS.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
go
CREATE FUNCTION fnTestGetIntradayQuoteXML_SJS(@SymbolList nvarchar(max)) RETURNS nvarchar(max) AS EXTERNAL NAME TestGetIntradayQuoteXML_SJS.[Predict.Services.Foo].GetIntraDayQuote
go
declare @testing nvarchar(max)
set @testing = dbo.fnTestGetIntradayQuoteXML_SJS('goog')
print @testing
When I execute the function as above, again, really slow the first time, then fast on subsequent calls. Could there be something wrong with the code, or some headers that need to be set differently to operate from the CLR in SQLServer?
I am trying to 'load' a copy of a SQLServer 2000 database to SQLServer 2005 Express (on another host). The copy was provided by someone else - it came to me as a MDF file only, no LDF file.
I have tried to Attach the database and it fails with a failure to load the LDF. Is there any way to bypass this issue without the LDF or do I have to have that?
The provider of the database says I can create a new database and just point to the MDF as the data source but I can't seem to find a way to do that? I am using SQL Server Management Studio Express.
We need to install CI database on CS server, and there are some issueswith stored procedures.Database works and have CI collation (Polish_CI_AS). Server hascoresponding CS collation (Polish_CS_AS). Most queries and proceduresworks but some does not :-(We have table Customer which contains field CustomerID.Query "SELECT CUSTOMERID FROM CUSTOMER" works OK regardless ofcharacter case (we have table Customer not CUSTOMER)Following TSQL generate error message that must declare variable @id(in lowercase)DECLARE @ID INT (here @ID in uppercase)SELECT @id=CustomerID FROM Customer WHERE .... (here @id in lowercase)I know @ID is not equal to @id in CS, but database is CI and tablenames Customer and CUSTOMER both works. This does not work forvariables.I suppose it is tempdb collation problem (CS like a server collationis). I tried a property "Identifier Case Sensitivity" for myconnection, but it is read only and have value 8 (Mixed) by default -this is OK I think.DO I MISS SOMETHING ????
I am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?
I'm chasing after a documetn that was available on one of the Microsoftwebsites that was titled somethign like "MS SQL Server Best Practices"and detailed a nyumber of best practices about securing the server.Included in this was revoking public access to the system tableobjects.Can someone post the URL where I can pick this up, or drop me a note oncontacting them for a copy of the document?
I have an app that uses a sqlserver 2000 jdbc driver to connect to a sqlserver 2000.
Is it possible to do a direct replacement of sqlserver 2000 with sqlserver 2005 express just by reconfiguring the app to point to the express? The app would still be using the sqlserver 2000 jdbc driver to try and make the connection.
If that is a possibility, what can be some differences in the configuration? Previously with 2000 the config information I entered is:
server name: "machinename"( or ip). I've also tried "machiname/SQLEXPRESS"
DB name: name of db instance
port: 1433(default)
user and pass.
My attempts so far results in
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket."
and
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unable to connect. Invalid URL."
I have an SQLServer Mobile database, and I would like to know if there is a way to upgrade it to SQLServer 2005 (.mdf) database. My database has no records in it, just the structure (tables etc). What I am actually asking is if I can create automatically a new SQLServer 2005 Database with the same structure as my existin SQLSErver Mobile database
I am curious with using replication in sql server 2005 one way from db A (source) replicating to db B(destination) in which db A has a collation of CS and db B has a collation of CI. Will there be any problems with this scenario? Thanks in advance!
I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.
Previously, I had been duplicating the CASE logic for both columns, like so:
Code Block...beginning of SQL view... shipment_status = CASE [logic for condition 1] THEN 1 WHEN [logic for condition 2] THEN 2 WHEN [logic for condition 3] THEN 3 WHEN [logic for condition 4] THEN 4 ELSE 0 END, shipment_status_text = CASE [logic for condition 1] THEN 'Condition 1 text' WHEN [logic for condition 2] THEN 'Condition 2 text' WHEN [logic for condition 3] THEN 'Condition 3 text' WHEN [logic for condition 4] THEN 'Condition 4 text' ELSE 'Error' END, ...remainder of SQL view...
This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.
This is what I'd like to do:
Code Block ...beginning of SQL view... shipment_status = CASE [logic for condition 1] THEN 1 WHEN [logic for condition 2] THEN 2 WHEN [logic for condition 3] THEN 3 WHEN [logic for condition 4] THEN 4 ELSE 0 END,
shipment_status_text =
CASE shipment_status
WHEN 1 THEN 'Condition 1 text'
WHEN 2 THEN 'Condition 2 text'
WHEN 3 THEN 'Condition 3 text'
WHEN 4 THEN 'Condition 4 text'
ELSE 'Error'
END, ...remainder of SQL view...
This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.
Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?
I am in the process of planning a server upgrade to sql2005 x64.
I created 2 linked servers: one to a SQL2000 sp4 server and one to a SQL7.0 SP3.
I have the following error when I query the linked servers. OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "Unspecified error". OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.". Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "IVDM2K". The provider supports the interface, but returns a failure code when it is used.
I am aware of KB 906954. http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
I applied the instcat.sql on the SQL2000SP4 server and my linked server issues for that one are gone.
However, I ran the instcat.sql script on the SQL7.0 sp3 server and the linked server is still giving me an issue.
Hi, I am new to SQL Server 2005. I tried connecting to my local machine by using my machine name as Server name and then tried running the following query: SELECT * FROM SYS.Objects. It gives me following error: Invalid object name 'sys.objects'.
Whereas, if I connect to my local machine using mahcinenameSQLEXPRESS, then the above mentioned query runs fine.
Why is this difference? What is the difference when I login in these 2 different ways. Any help would be appreciated.
I am working on a C#/asp.net web application. The application has a text box that allows a user to enter a name. The name is then saved to the database. Before the name is saved to the database, I need to be able to check if the name already exists in the database. The problem here is that what if the name is in the database as "JoE ScMedLap" and somoene enters the name as "Joe Schmedlap" which already exists in the database,but just differs in case. In other words how do deal with case sensitiviy issues.
Yesterday I received a response to my CI/CS Collation problem and therecommendation was to try and restore a CI Collation database to a CSCollation database. After creating a blank CS database a full restore(Force restore over existing database) does change the Collation toCI. I'm unsure as to how I can restore without changing theCollation. Any suggestions?
Is there a way to transfer data from a SqlServer db to a SqlServer Express db. I tried to use the backup file of SqlServer, but this file is not valid for SqlServer Express. Or there any alternatives?
I have SQLServer 2005 runs well for months and stop working after install SqlServer2005 SP1. I try to reinstall the SQLServer 2005 but I have problem when install work station component on my and the error is "There is a problem with this Windows Installer package. A program run as part of the setup did not finish as expected. Contact your support personnel or package vendor". Please help me to fix this bug. I do not want to reformat my machine.
I just can't get the case to work for me in this view. CASE WHEN Isnull(dbo.Payments.AmountPaid,'No') THEN 'No' WHEN dbo.Payments.AmountPaid >0 THEN 'Yes' END AS Payment_StatusThe error I get is: An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'. ALTER VIEW [dbo].[AffiliationPayments]ASSELECT dbo.Affiliations.AffiliationType, dbo.Affiliations.AffiliationDescription, dbo.Affiliations.AffiliationEnd, dbo.Payments.PaymentDate, dbo.Payments.AmountPaid, dbo.Affiliations.Client_ID,CASE WHEN Isnull(dbo.Payments.AmountPaid,'No') THEN 'No' WHEN dbo.Payments.AmountPaid >0 THEN 'Yes' END AS Payment_StatusFROM dbo.Affiliations LEFT OUTER JOIN dbo.Payments ON dbo.Affiliations.Client_ID = dbo.Payments.Client_ID
I have a deadline that is set ahead of time and will not change. I need to insert this deadline for all new users, so I want to set in the DB and I have tried to insert the deadline into the db using a case(below)
Error -2147217900
Error -2147217900
Incorrect syntax near the keyword 'CASE'. ALTER PROCEDURE prcStartWeeks @UserID VARCHAR(50) ASSET NOCOUNT ONDECLARE @Result INTDECLARE @ID INTDECLARE @Weeks INTDECLARE @DeadLine VARCHAR(8)SET @ID = 0SET @Weeks = 1SELECT @ID = ISNULL((SELECT peID from tblUserInfo where UserID = @UserID),0)IF @ID = 0 BEGIN SELECT @Result = -1 ENDELSE BEGIN WHILE @Weeks < 18 BEGIN CASE @Weeks WHEN '1' THEN @DeadLine = '5/9/2006' WHEN '2' THEN @DeadLine = '9/15/2006' WHEN '3' THEN @DeadLine = '9/22/2006' WHEN '4' THEN @DeadLine = '9/29/2006' WHEN '5' THEN @DeadLine = '10/6/2006' WHEN '6' THEN @DeadLine = '10/13/2006' WHEN '7' THEN @DeadLine = '10/20/2006' WHEN '8' THEN @DeadLine = '10/27/2006' WHEN '9' THEN @DeadLine = '11/3/2006' WHEN '10' THEN @DeadLine = '11/10/2006' WHEN '11' THEN @DeadLine = '11/17/2006' WHEN '12' THEN @DeadLine = '11/21/2006' WHEN '13' THEN @DeadLine = '11/28/2006' WHEN '14' THEN @DeadLine = '12/5/2006' WHEN '15' THEN @DeadLine = '12/12/2006' WHEN '16' THEN @DeadLine = '12/19/2006' WHEN '17' THEN @DeadLine = '12/28/2006' END INSERT INTO tblUserWeekly(PEID,WeekID,Points,DeadLine) VALUES(@ID,@Weeks,0,@DeadLine) SET @Weeks = @Weeks + 1 END SELECT @Result = 0 END
I want to use case in my date range to get last weeks range. I have a similar proc that uses variables, but my application UI will not allow this in production.
Error incorrect syntax near '=' on line 15
-- begin select Company ,Carrier ,Client ,DatePaid ,DateBilled ,cast(PremiumReceived as money) ,cast(PolicyAmount as money)
from view_billing
where cast(datebilled as datetime) between
(cast(datebilled as datetime) = case when datepart(dw,getdate()) = 2 then getdate()-8 when datepart(dw,getdate()) = 3 then getdate()-9 when datepart(dw,getdate()) = 4 then getdate()-10 when datepart(dw,getdate()) = 5 then getdate()-11 when datepart(dw,getdate()) = 6 then getdate()-12 when datepart(dw,getdate()) = 7 then getdate()-13 end)
and cast(datebilled as datetime) = case when datepart(dw,getdate()) = 2 then getdate()-1 when datepart(dw,getdate()) = 3 then getdate()-2 when datepart(dw,getdate()) = 4 then getdate()-3 when datepart(dw,getdate()) = 5 then getdate()-4 when datepart(dw,getdate()) = 6 then getdate()-5 when datepart(dw,getdate()) = 7 then getdate()-6 end
I have a query using Transact-SQL and it is moderately complex. I was wondering if someone could tell me if it would be better to use IF...ELSE statements or CASE, WHEN...THEN statements.
What I have is something like this...
Code:
SELECT something something code = CASE
WHEN something IN (A list of things) IF something else THEN 4
I have just though of doing IF...ELSE statements to keep it simply, but this query needs to run in a relative quick time frame. I guess my question is, is IF...ELSE faster or is it faster to use a CASE statement?
INSERT INTO LTG VALUES ('Luis',1,0,0) INSERT INTO LTG VALUES ('Hector',0,1,0) INSERT INTO LTG VALUES ('Alejandro',0,0,1)
DECLARE @S INT SET @S = 1
SELECTName, SUM (B1), SUM (B2), SUM (B3) FROMLTG GROUP BY Name HAVING CASE @S WHEN 1 THEN SUM (B1) != 0 WHEN 2 THEN SUM (B1) != 0 WHEN 3 THEN SUM (B1) != 0 END
