SQL Sever 2012, I have a table where I store data related with a Log, the columns of the table are three, IDTable, DateLog and CountLog, the column CountLog is a cumulative value, each new rows can increment this value, my query will receive two parameters related with the Date, @dateFrom and @dateTo, with this information is necesary to get the diff between two months, for example:
CREATE TABLE MyTable ( IDTable INT IDENTITY NOT NULL, DateLog DATE NOT NULL, CountLog INT NOT NULL )
[code]....
In this case I need to apply a filter with the Dates:
@dateFrom ='2014-01-01', @dateTo ='2014-04-28'
But according with this filter I need to limit the results only for this period between the date, but I need that the first month (January 2014) take the information of the max value from previous month even not included in the filter, in this case the results will be the next:
With the results, for the first mont (January) is, according to the range of times, January is the lower limit,the last cumulative value of CountLog before the first month of the range was 2 (December 2013), the max value of the CountLog for this first month was 7, the diff is 7-2 equal to 5 , in the case of the next month (February) the max value of the CountLog was 10, the diff is 10 - 7 equal to 3 and this way.
Can any one tell me how to subtracts and add hours to the current date and time? In my case I have to store the Vancouver date and time in Toronto. Thanks, T.Lingam
I've to write a function to return a comma delimited values from a table columns
If a table has Tab1 ( Col1,Col2,Col3).
E.g. as below ( the columnName content I want to use as columns for my pivot table
CREATE FUNCTION [RPT].[GetListOfCol] ( @vCat NVARCHAR(4000) ) RETURNS @PList AS BEGIN SELECT @PList += N', [' + [ColumnName] +']' FROM [ETL].[TableDef] WHERE [IsActive] = 1 AND [Category] = @vCat RETURN; END;
I want out put to be as below, I am getting this output from the select query independently by declaring @Plist variable and passing @vcat value, now I want it to be returned from a function when called from a select query output ,Colum1,column2,....
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID AS SickBalance
and the table should be shown as below after executing the query
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.* FROM PRODUCT A CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
i should write a service that will run every 1 hour.
this service should check a folder.
inside this folder there are some xml files, all of them in the same format exactly.
when the service encounters with this xml file, it should open it,pull some data out of it, and update a table in the db.
NOW - I HAVE 2 OPTIONS, AND THIS IS THE DILEMMA:
should i write a clr function to do this task, or should i just write a standard c# windows service, that will open a connection to the db when it should perform update?
I am migrating the BE of an Access app. to SQL server 2012. I need to get the user's login name (Windows Authentication login). This can be done using xp_cmdshell, but, xp_cmdshell is considered dangerous and I wouldn't be able to run it once I deploy the app. to the company servers (currently I have SQL server on my computer and as an admin I can enable xp_cmdshell to run, but IT doesn't allow it in company servers for security reasons).
Another question, is it possible to send data from the logged in user from Access to SQL server? What I need to do is let SQL know the username of the logged in user, then, use it to filter the data on SQL. Idea is that user can only run queries for his data (he can't view other user's data unless he is a manager or an admin (currently the app. in Access logs the user in automatically if his Windows Domain username is found in the user's table, and set's his role found in the Roles table). It is this functionality that is giving me some problems to migrate to SQL.
I created a function that uses the System_User SQL built-in function, this retrieves the SQL login username, but, the app. uses 1 SQL local account to connect to the server, so in essence it doesn't work as I need the Windows Domain account username.
I have set up two SQL server (e.g. Server1 and Server2) which are set up to use Windows Authentication with my login (e.g. login1).On Server1 I have created a linked server to Server2, I have then selected the Security tab and added my local login and selected impersonate. I have then selected ‘Be Made using the login’s current security context’.I then receive the error message ‘The linked server has been updated but failed a connection test. Do you want to edit the linked server properties?’
DECLARE @MaxCountHistogram TABLE ( MaxId INT IDENTITY PRIMARY KEY NOT NULL, PublicationId INT NOT NULL, ProviderId INT NOT NULL, DateLog DATETIME NOT NULL, Amount FLOAT NOT NULL ) INSERT INTO @MaxCountHistogram VALUES(432,3,'20150530',10.2564),(432,3,'20150630',13.2564),(432,5,'20150530',8),(432,5,'20150630',13),(433,3,'20150530',9),(433,3,'20150630',11),(433,5,'20150530',13),(433,5,'20150630',21)
I need to take for each Publication and Provider and getting the diferential between two different months, for example:
Period Delta Amount Provider PublicationId 20150530 10.2564 3 432 20150630 3 Result of (13.2564- 10.2564 ) 3 432
We recently moved our servers from one domain to another. Now, we can't grant Windows users access to any databases. We CAN create the user IDs successfully via Enterprise Manager, and get a corresponding row in MASTER..SYSXLOGINS, but can't click on the Database Access tab in the SQL Server Login Properties dialog and grant accesss to any databases. When we try to do so, we get error 15401, "Windows user or group xxx not found". The underlying call is to MASTER..SP_GRANTDBACCESS, and running that via Query Analyzer returns the same error (naturally)
Looking through the code of SP_GRANTDBACCESS, I've determined that what is failing is a call to the undocumented TSQL function GET_SID. This proc takes two parameters, the first is either G<nt group name> or U<nt user name> and the second is NULL in the call in SP_GRANTDBACCESS. If I execute
SELECT GET_SID('U<valid user>', NULL)
it returns NULL, however, if I run
SELECT SUSER_SID(<valid user>)
then I get the Windows SID of whatever valid user name I supply.
We have 3 servers in question, namely production, development and test. We noticed the problem on production. Curiously enough, development and test worked fine.
NOW THE PLOT THICKENS. If I run this query on the dev box....
SELECT SUSER_SID(<id>), GET_SID('U<id>',NULL)
...the first function returned the SID, BUT THE SECOND FUNCTION DID NOT!!! How could that be? Clearly GET_SID was working inside of SP_GRANTDBACCESS, but not as a discrete call. So I went into the master database and added code to print out the SID returned by GET_SID to the proc. Lo and behold, SP_GRANTDBACCESS promptly failed with a 15401 error. It continues to get 15401s now, even after I returned it to the original code. What gives? Now my dev box has the same error production has, and all I did was recompile SP_GRANTDBACCESS a couple of times. FWIW, I did *not* ever make any changes to SP_GRANTDBACCESS on production.
Why doesn't GET_SID() work outside of SP_GRANTDBACCESS? Why did recompiling SP_GRANTDBACCESS break it permanently?
It almost seems like the query compiler can't correctly link a call to GET_SID to the correct function in some DLL, except I thought that the compiled code didn't survive a restart, and all TSQL procs were recompiled the first time they were called after a restart. If that's the case, then the compiler is - or at least was - producing a functioning compiled version of SP_GRANTDBACCESS after every restart.
where the job_type columIn is of the char(1) type, NTBSTRINGBIND is the vartype argument in the dbbind() function.
However, what I have gotten is nothing more than a null string from the job_type column. I have alternatively changed the vartype argument to STRINGBIND, CHARBIND and even INTBIND, but the results are the same.
Who can tell me the tricks to retrieve a char(1) column from SQL server?
Hello All, I need to determine that a piece of XML in a character field or variable is well-formed XML. I need to store this validation result (valid or not valid flag) in the table. Does SQL provide some function for this? Is there any other way? Thank you! David
HI there, Is there a function on transact SQL which test if a character belong to expression,I want to select from a table only the address that contain '@'.
The input is like this : xxxxxxxx=yyyyy=key_id=12345xyxyx
I would like to use the right function and get 12345xyxyx alone. The function must search for the first '=' symbol from the right of the word and get the characters from the end till the '=' symbol.
Is it ill-advised to have columns whose values pull from scalar functionsusing other fields in the record as parameters? For example, if I havecreate table a(iID int primary key)create table b(iID int ,iDetail int,CONSTRAINT PK PRIMARY KEY(iID,iDetail),CONSTRAINT FK FOREIGN KEY (iID) REFERENCES a(iID))Let's say in table b I put price information for each detail and in table aI'd like to put a column that sums these prices for the children of eachrecord. Should I make a computed column that references a function usingiID as a parameter? Or would it be better to create a view for this kind ofpurpose?Regards,Tyler
Hello, I am trying to send to colums to SQL CLR function and get some results. I want the CLR code be like:
Code Snippet public void DoSomething(SqlDouble[] a, SqlDouble[] b, out SqlDouble x, out SqlDouble y, out SqlDouble z) { //Do Something... x = .... y=... z=... }
I have a non-partitioned table (TableToPartition) and I want to apply an existing partition scheme (PartSch) to it using a query. I didn't find any option so I used the StorageCreate Partition wizard to generate the script.why this clustering magic needed if it is dropped at the end? Isn't there another way without indexing to partition a table, say something with ALTER TABLE? (SQL Server 2012)
BEGIN TRANSACTION CREATE CLUSTERED INDEX [ClusteredIndex_on_PartSch_635694324610495157] ON [dbo].[TableToPartition] ( [ID] )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PartSch]([ID]) DROP INDEX [ClusteredIndex_on_PartSch_635694324610495157] ON [dbo].[TableToPartition] COMMIT TRANSACTION
Hi All, Once again I find myself smacking my head into a wall (and it will probably be for naught). I'm trying to find a specific character in a string within a specific record. I've tried substring, but it's not quite getting me where I want to be. I even tried "nesting" the substring and got nowhere (but I could have had the syntax wrong when I nested it).
Here is a sample
Select substring(productimagefile, 20, 5) from my table
What I need is something like this:
Select substring(findoneof(productimagefile, _r, 1), 1, 5) from my table
Actually, on the start of the substring, I need it to be where it finds the _r, just not sure how to tell it that. The replace function is the closest I've seen to what I need, but I don't want to replace it with anything, I just need to find it in the string.
So, if anyone has any ideas, I'd be grateful (and my head would stop hurting....)
I currently have the fllowing Stored Procedure. When I pass the the Url of the web service in the parameters, I'm having a sp_OAMethor read response failed error.
I don't know how to pass the parameter as well as the name of the function in the Web Service I'm calling. Maybe I'm all wrong here with this code too?
Thanks for any help.
ALTER PROCEDURE [dbo].[pTAPServiceWeb]
@sUrl varchar(200),
@response varchar(8000) out
AS
DECLARE @obj int
DECLARE @hr int
DECLARE @status int
DECLARE @msg varchar(255)
EXEC @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
I have a table with duration values for different machine states. I 'm trying have a sum of the duration value of each state ( the duration sum , was an earlier question).
I want to create a subset of a geography table. My intention is to select only a few cities with a where and after that select a percent of village for each of the cities. My query look like this:
I have tried with tablesample but it doesn´t return what I expect because I have many villages from one city (for example 15 % of it) and only 2 % of other. How can I achieve my goal?
SSMS does not like mine! THis is the error that I receive:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
And this is my syntax:
Select employeeID ,COUNT(case when rehirestatus IN (select rehirestatus from regionalemptable where rtrim(storename) = 'Location1') THEN userID ELSE 0 END) + COUNT(case when rehirestatus IN (select rehirestatus from globalemptable where rtrim(storename) = 'Location1') Then userID ELSE 0 End) FROM production GROUP BY employeeID ORDER BY employeeID
I have an SP that is working, it creates a Union between 3 tables then creates a single table - I am using Visual studio Grid Control to view this by a browser, its working. at the bottom of the SP is a line of code that calls a function, the function queries a View. When I run the SP, SQL returns the data from the Union plus the data from the select statement on the Function. This all works but I can't get the data returned by the function query onto a browser.
I am doing it this was as the parameters in my SP are also used when calling the function, works really well. Is GTotal a return value?
Here is my function:
USE [LOGONs] GO /****** Object: UserDefinedFunction [dbo].[FX_AnnaulBudget1] Script Date: 23/09/2015 12:27:50 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[Code] ....
...and here is my SP:
USE [LOGONs] GO /****** Object: StoredProcedure [dbo].[USP_AnnualBudget] Script Date: 23/09/2015 12:57:46 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[USP_AnnualBudget]
Update query to modify a Url in the text column with another url
Ex:
Col1 Col2 1 An unexpected event occurred https://abc.def.com/default/_workitem/10325 3 This alert occurs when service jobs run on https://abc.def.com/default/_workitem/10118 10 This alert fired to indicated that error with https://abc.def.com/default/_workitem/10150 to Col1 Col2 1 An unexpected event occurred https://abc.def.com/default/_workitem/11111 3 This alert occurs when service jobs run on https://abc.def.com/default/_workitem/11111 10 This alert fired to indicated that error with https://abc.def.com/default/_workitem/11111
I have a really basic question. The following SQL query works for me:
Select EnterUserID, Enterdate from tblCsEventReminders where EnterDate >= Convert(datetime, '2015-04-01')
I am essentially trying to write a query to count the number of user logins after a certain date, so I need to count 'EnterUserID' but I am having problems getting the count() function to work.
I have a column with datas as "0.0" "123.0" "45.0" i have a query were i have to match the value only. i don't want the decimal and followed by the any thing.
or how do i extract just the value not the decimal part so that i can compare it with other value.
I have two tables that contain product SKUs (12-character strings): Table 1Product IdSKU... Table 2ProductVariantIdSKU... I need to find the MAX (i.e., last used) SKU that exists in either table. I did write two sps, one for each table that I can compare in code and use the larger (latest) one but I am not that facile with JOINS, etc., so I can't figure how how to create a single sp to return the value I am looking for--although I assume this must not only be possible but trivial to more experienced SQLers. Thanks! Duncan
