I created a query in Sybase SQL Anywhere and used the following column generation instructions. Could someone help with general guidelines as far as using it in SQL Server 2005 Express?
Thanks.
"(if C_Base_Data.PLDate is not null then (Floor(cast((DATEDIFF(day, c_Base_Data.PLDate , _Ref_Date.refdate)/365.25)as Decimal(5,1)))) else 0 endif) as Current_Age,"
"(if Current_Age = 0 then 'T.unpl' else 'Planted' endif) as T_Unpl"
Hi, I got a problem. I installed Microsoft SQL Server Management Studio Express 2005 version. And I created a Compact database. I created an connection in SSMSE to connect the database and opened a query form. then, i run the following sql:
Select * from Table1
It returned 3 records to me. After that, I used program to insert record into this table. Then i ran this sql again, it still show me 3 records. I closed the query form, and re-created a new query form, then run the sql, it returned 4 records to me.
Why? It's very strange and difficult to operate, right? Is there anyone know how to make the SSMSE to return whole records without any close query form and re-create query form operation?
hi , I new in sql reporting services. I have a report with a query . now for every record it printed I need to create another query to another table and to some calc and print the value . how do I do this .
in other reporting program that I used before, they allow me to create multi body report. each body report allow me to defined a query . thks
I want to create a empty new table (if it doesn't exist) when the the page/program starts. Maybe with copying field names from other table.It would be nice to have its name personal for each user. But I don't know if it possible.Also, what event is best place for this. Perhaps like this:dim username as string = Page.User.Identity.NameDim tablename as stringtablename= shopping_basket+usernamecreate from t_product new @tablename
In the following script What "U" stands for: IF OBJECT_ID('dbo.sample_table', 'U') IS NOT NULL Regards Abdul USE AdventureWorks GO IF OBJECT_ID('dbo.sample_table', 'U') IS NOT NULLDROP TABLE dbo.sample_table GO CREATE TABLE dbo.sample_table (c1 int NOT NULL, c2 char(10) NULL, c3 datetime NULL, CONSTRAINT PK_sample_table PRIMARY KEY (c1) ) GO
flowid and stepid for both tables match; meaning that if i found a record in task with a certain taskid, i could query stepdefinition with the same flowid and stepid to find the steptype.
well, i wanna do it the other way around. I query stepdefinition to find a list of flowids and stepids for a specific steptype.
select flowid, stepid from stepdefinition where steptype = -3
Now, I want to find all taskids in task for each flowid/stepid combination
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[INSQUERY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[INSQUERY] GO
CREATE TABLE [dbo].[INSQUERY] ( [Query] [varchar] (8000) COLLATE Arabic_CI_AS NULL ) ON [PRIMARY] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[KS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[KS] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CreateInsQryEx]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[CreateInsQryEx] GO
----------------------------------
CREATE PROCEDURE [dbo].CreateInsQryEx @Tablename varchar(50) = NULL, @SelectPart varchar(7500) = '*', @FilterPart varchar(7500) = NULL AS TRUNCATE TABLE INSQUERY DECLARE @QUERY VARCHAR(7500) DECLARE @ROW int, @NROW int DECLARE @DATATYPE varchar(20) SET @NROW = 1 DECLARE @COL int, @nCOL int DECLARE @VALUE varchar(7500) DECLARE @SELECTPART1 VARCHAR(7500) IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[KSINSERTQRY]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1) DROP TABLE [DBO].[KSINSERTQRY]
SET NOCOUNT ON DECLARE @InsQuery varchar(7500) IF(@TableName IS NULL) BEGIN RAISERROR ('TableName Cannot be Blank', 16, 1)
END
SELECT @Query = 'SELECT ' + @SelectPart + ' INTO KSINSERTQRY FROM ' + @TableName IF(@FilterPart IS NOT NULL) BEGIN SELECT @Query = @Query + ' WHERE '+@FilterPart END EXEC(@Query)
Alter Table KSINSERTQRY Add ICOL int IDENTITY (1,1 ) TRUNCATE TABLE KS
INSERT INTO KS (COLU,Datatype) SELECT '['+COLUMN_NAME+']',DATA_TYPE from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'KSINSERTQRY'
SELECT @ROW = COUNT(*) FROM KSINSERTQRY WHILE (@NROW <= @ROW) BEGIN SET @SELECTPART1 = NULL SELECT @COL = COUNT(*) FROM KS SET @NCOL = 1
DECLARE @COLNAME varchar(50) SET @InsQuery = 'INSERT INTO ' + @TableName + ' ' IF(@SelectPart <> '*') BEGIN SET @InsQuery = @InsQuery + '(' + @SelectPart + ')' END
SET @InsQuery = @InsQuery + 'VALUES (' WHILE (@NCOL < = @COL) BEGIN SELECT @COLNAME = COLU,@DATATYPE = datatype FROM KS WHERE IDCOL = @NCOL SET @QUERY = 'UPDATE KS SET [VALUE] = (SELECT CONVERT(VARCHAR(7500),' + + @COLNAME + ') FROM KSINSERTQRY WHERE ICOL = ' + convert(varchar(3),@NROW) +') WHERE COLU = ''' + @COLNAME + '''' --SELECT @Query EXEC(@QUERY)
SELECT @VALUE = Convert(Varchar(7500),[VALUE]) FROM KS WHERE COLU = @COLNAME
SET @VALUE = CASE @Datatype when 'varchar' then '''' + '''' + @VALUE + '''' + '''' when 'nvarchar' then '''' + '''' + @VALUE + '''' + '''' when 'text' then '''' + '''' + CONVERT(VARCHAR(7500),@VALUE) + '''' + '''' when 'char' then '''' + '''' + @VALUE + '''' + '''' when 'nchar' then '''' + '''' + @VALUE + '''' + '''' when 'smalldatetime' then '''' + '''' + @VALUE + '''' + '''' when 'datetime' then '''' + '''' + @VALUE + '''' + '''' ELSE @VALUE END SET @QUERY = 'UPDATE KS SET [VALUE] = '+ '''' + Convert(Varchar(7500),@VALUE) + ''' WHERE COLU = ''' + @COLNAME + '''' EXEC(@QUERY) SET @NCOL = @NCOL +1 END SELECT @SELECTPART1 = COALESCE(@SELECTPART1 + ',', ' ') + ISNULL(CONVERT(VARCHAR(5000),[VALUE]),'NULL') FROM KS WHERE RTRIM(LTRIM(COLU)) <> '[ICOL]' --SELECT @SELECTPART1 SET @InsQuery = @InsQuery + @SELECTPART1 + ')' INSERT INTO INSQUERY VALUES (@InsQuery) SET @NROW = @NROW + 1 END SELECT * FROM INSQUERY
I have a table of baseballTeams with columns baseballTeamName and a baseballTeamID
A have another table (baseballGames) which consists of baseball games which includes a homeTeamID and a visitingTeamID. Both of these columns are linked to the baseballTeamID from the baseballTeams table
I have been successful at joining the two tables and Selecting the baseballTeamName from the baseballTeams table for either the hometeam or visiting team as shown below:
SELECT gameID, homeTeamID, baseballTeamID,baseballTeamName FROM baseballGames, baseballTeams WHERE baseballGames.homeTeamID=baseballTeams.baseballTeamID
...but I cannot figure out how to select BOTH the homeTeam name and the visitingTeam name from the baseballTeam table
CREATE PROCEDURE GetSearchResults @ParameterValue @varchar(max) --WITH ENCRYPTION as select * from OPS_Projects select * from OPS_Clients
go
I have this stored procedure that has one Parameter ....that parameter value will be a value that can be matched from eithier one of these tables but i am not sure how I need to write query...my logic I would like to read like this
Select * From OPS_Projects Where ThisData = ParameterValue
If this query returns data then, just return that data If not run next query
Select * From OPS_Project Where ThisOtherData = ParameterValue
If this query returns data then, just return that data If not run next query.... and so on....
Is this the best way to do to create a search query???? Any Help I am open for suggestions! Thanks!
Code Snippet Declare @DBName as varchar(100) Declare @Query as varchar(8000)
SELECT @DBName = AccountDBName FROM Config Where SomeID=SomeValue
Set @Query =' SELECT ReciptItems.acc_TopicCode, ReciptItems.acc_DetailCode, ReciptItems.acc_CTopicCode, SUM(ReciptItems.TotalInputPrice + ReciptItems.TotalOutputPrice), a.MoeenName_L1 FROM ReciptItems LEFT OUTER JOIN ' + @DBName + '.dbo.Categories AS a ON ReciptItems.acc_TopicCode = a.TopicCode GROUP BY ReciptItems.acc_TopicCode, ReciptItems.acc_DetailCode, ReciptItems.acc_CTopicCode, a.MoeenName_L1'
Hello everybody,I want to ask how to create query use the solve the problem of this..Example : I want to display the month january between february... Sorry if my english is not good...Thank you...
Hi,I was wondering if someone can help me. I have 4 tables in an SQL Server 2005 database, for purposes sake called 'table1', 'table2', 'table3' and 'table4'. They all have the same data structure between them. The columns inside them have the exact same design. For purposes sake called 'column1', 'column2', 'column3' and 'column4'. Alot of these tables have duplicate records scattered between them, so basically I want to create 'table5' with the same column names but only unique records. Can someone specify the syntax I need for this. I'm pretty sure it's a union query I need so am scouring as I type this also. Many Thanks Robert
I need to be able to have users run a query under the 'sa' account that will create another account that will just be used for reading and writing to a specific database. Is there a way to do this with just a sql script or can you only set up logins and accounts from the enterprise manager?
hi, here I create a cross tab query in access 97 this is the code
TRANSFORM Count([1].ORD_NBR) AS [The Value] SELECT [1].SRMGR_NAME, Count([1].ORD_NBR) AS [Total Of ORD_NBR] FROM 1 GROUP BY [1].SRMGR_NAME PIVOT [1].ASR_SUPP;
I export this table to sqlerver 6.5 and run the same code. IT DIDNOT WORK My question is how can I implement crosstab query in sql server 6.5?
I need to use mssql to create a ranking of some kind. This is the situation: I need to assign position to a list of students based on thier scores. e.g Student Score Position StudentA 56 4 StudentB 78 1 StudentC 66 2 StudentD 56 4
I need to create the positions based on the scores of the ctudents. I will appreciate any assistance. Thank you.
I am running MSFT SQL 2008 with a CRM system. In that CRM system we have defined "COMMUNICATIONS" that have a number of different "TYPES" These communications are tied to Companies, which in our business is a resident of our community. The resident has the ability to request a 1 time service (TRAVEL) that would be recorded with one "COMMUNICATION" , or a reoccuring service (DAILY CARE) that would be recorded with two "COMMUNICATIONS" (Start/Stop). THe communication ID does appear to be sequential, but may not be entered sequentially.
we would like to be able to create a timeline for a specific resident around 3 specific types of transactions. (AWAY, RETURN, CS) There should be some strong predictive value to these data points.
ABSENCES AWAY = Start of an absence from the building RETURN = End of an absence from the building A person during their reisdency will have multiple Absences An absence could start on the same day a previous absence ends Absences vary in length CS CS0-CS10 (each is a different TYPE) of communicatio
[code]....
Query in DESIGN View
SELECT TOP 100 PERCENT comm_trantype AS Type, comm_trandate AS Date, CmLi_Comm_CompanyID AS CompID, Comm_CommunicationId AS CommID, ROW_NUMBER() OVER (PARTITION BY CmLi_Comm_CompanyID ORDER BY Comm_CommunicationId, comm_trandate) AS Seq FROM CRM_CSLDB.dbo.vCommunication
[code]....
Results from QUERY in DESIGN VIEW
TYPE DATECOMPIDCOMMIDSEQ AWAY2011-02-24 00:00:00.00051747531 RETURN2011-03-31 00:00:00.00051747542 AWAY2011-03-28 00:00:00.00064740681 RETURN2011-04-30 00:00:00.00064752972
SELECT TOP (100) PERCENT dbo.vCommunication.comm_trantype AS csTrans, dbo.vCommunication.comm_trandate AS csTranDate, dbo.vCommunication.CmLi_Comm_CompanyID AS CompanyID, dbo.vCommunication.Comm_CommunicationId AS Comm_ID, dbo.CSL_resident.Name, Comm_CommunicationId AS CommID, ROW_NUMBER() OVER (PARTITION BY CmLi_Comm_CompanyID ORDER BY Comm_CommunicationId, comm_trandate) AS Seq FROM dbo.vCommunication INNER JOIN
I still don't get some of the more complex stuff in SQL, so I've got a question about grouping:
I've got a table with articles and a table how much articles I got in stock. The ArticlesInStock-table might contain more than one row for an article, so to check how many items I got in stock I use this group by-SQL:
SELECT [ArticleID], SUM([InStock]), SUM([InStock] * [Price]) FROM dbo.[ArticlesInStock] GROUP BY [ArticleID]
this does work nicely. But now I'd like to get the order no of the article, too (and maybe sort by it), and this is where I get stuck.
I thought it might be something like this:
SELECT [ArticleID], SUM([InStock]), SUM([InStock] * [Price]), [Articles].[OrderNo] FROM dbo.[ArticlesInStock], dbo.[Articles] WHERE dbo.[ArticlesInStock].[ArticleID] = dbo.[Articles].[ArticleID] GROUP BY [ArticleID]
I am creating a web application that uses a using a web service to get data for my reports. Since the webservice only accepts 1 parameter called "sql" (the sql select statement), I am using the report's query string to get the data.
Here is the data source and dataset info I am using:
DataSource Name: WebService Type: XML Connection string: http://localhost/myWeb/myWebService.asmx Credentials: No credentials
DataSet Query tab: Name: WebService Data source: WebService Command type: Text Query String: <Query><SoapAction>......</SoapAction></Query>
Here is a sample of the <Query> string that I use when I first build the report:
<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath> </Query> ------------------------------------------------------------------------------ When the user selects a report in the web application, they are prompted for information about the sql statement, and then I can rebuild the <Query> xml fragment, substituting the new sql statemet for the default one. for example, the statement "Select * From Reports" would be replaced with "Select * From Customers where LN = 'Smith'".
Then I want to attach that new <Query> statement to the report and run it. How can I set this information in the report? I can't find anything that talks about it, but there must be some way!
I have a data with mutliple esn but different auditdate and opid. I will pull this data filtering by date and opid. My requirements is not to include the opid = 51 but need to get the desired opdesc for this esn that contains opid=51.
See below sample ddl and desired result. I dont want do include the opid = 51 because it will create a duplicate in transaction instead retain the opid 5
example: esn T9000000000019829505 has multiple rows with different auditdate and opid. retain the records for opid is equal to 5 but get the opdesc for opid is equal 51.
reate table #test (esn nvarchar(35), dateaudit datetime, opid int) insert into #test(esn,dateaudit, opid)values('352452060499834','2015-05-12 20:32:39.490',5) insert into #test(esn,dateaudit, opid)values('352452060499834','2015-07-06 17:35:14.210',5) insert into #test(esn,dateaudit, opid)values('T9000000000019829505','2015-01-14 15:18:45.620',5)
use AFMIF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name='uye_idler') drop table uye_idlerCREATE TABLE uye_idler ( uye_no int NOT NULL, ) GO DECLARE @uye_numarası int Set @uye_numarası = 1 WHILE @uye_numarası < 15001 BEGIN INSERT INTO uye_idler VALUES (@uye_numarası) Set @uye_numarası = @uye_numarası + 1 EndHi , I am able to create 15000 records by the codes above .By the same way I want to create random texts as ASD ,WER,SAD,DFG etc. How can I do this ? Thanks ...
SELECT FriendName from Friends where RegionId = 23 I would like to create a comma delimited list of 'FriendName' column values in above query (example - Mike,John,Lisa,Emburey). How would I do that?
Using Sql Server 2005 Express and Management Studio I need to create a SQL insert statement for the contents of a table (FullDocuments) so that I can run the query on another server with that same table schema (FullDocuments) and the contents will automatically be inserted into the new instance of the FullDocuments table.In Management Studio I have used "Script Table as" for the create table query. The second instance of FullDocuments has been created on the remote server. Now how do I generate an insert query for the contents of FullDocuments so that the contents can be moved/inserted to the new instance of the table?Thanks for any help provided.