I got an answer and that seems the best way for me. It is having a sub query,but i don’t see a way to avoid that. select t2.state,t1.name,t2.powerconsumption FROM table1 t1 JOIN table2 t2 ON t1.companyid =t2.companyid where t2.powerconsumption =(select MAX(t3.powerconsumption) from table2 t3 where t3.state=t2.state and t3.month=”jan”) SQL Fiddle solved SQL query required [duplicate]
You are passing date and time in to your procedure, but when you convert those to dynamic SQL, you are using format 101, which removes the time portion. Try changing the 101 to 126 (there are 2 places within your code where 101 is used, make sure you change both of them. 0 solved compare … Read more
Try this one – CREATE FUNCTION dbo.udf_getCountCallOnDate ( @DateFrom DATETIME , @DateTo DATETIME ) RETURNS INT AS BEGIN RETURN ( SELECT SUM(CallsTakenOnDate) FROM dbo.PhoneCalls WHERE DateOfWork BETWEEN @DateFrom AND @DateTo ) END SELECT DateOfWork = CONVERT(VARCHAR(10), t.DateOfWork, 111) , [Count] = SUM(t.CallsTakenOnDate) FROM dbo.PhoneCalls t WHERE DateOfWork BETWEEN @DateFrom AND @DateTo ORDER BY t.DateOfWork 0 … Read more
Thanks a lot, problem was – I can’t create xsd with above xml, please help me to create it. Answers were – use another version, it was created and then deleted, encoding is wrong. I found the answer myself, I needed just headings which are usually put above the xml. That much. solved Validating xml … Read more
Since you are using SQL Server then you can implement the PIVOT function and if you have an unknown number of period values, then you will need to use dynamic SQL: DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT distinct ‘,’ + QUOTENAME(‘PeriodId’+cast(periodid as varchar(10))) from Periods FOR XML PATH(”), TYPE ).value(‘.’, … Read more
Try this DECLARE @temp TABLE(col1 varchar(20),col2 int, col3 varchar(20)) insert into @temp values (‘data1′, 123 , ’12/03/2009’),(‘data1′, 124 , ’15/09/2009’), (‘data2 ‘,333 ,’02/09/2010’),(‘data2 ‘,323 , ’02/11/2010’), (‘data2 ‘,673 , ’02/09/2014’),(‘data2′,444 , ’05/01/2010’) SELECT (CASE rno WHEN 1 THEN col1 ELSE ” END )AS col1, col2, col3 FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY … Read more
Maybe this works for you SELECT y1.pid ,y1.description ,y2.description FROM ytest AS y1 INNER JOIN ytest AS y2 ON y1.pid = y2.pid AND y1.description = ‘Log’ AND y2.description like ‘Design%’ 4 solved sql server 2008 select same field with different values [closed]
select empid, sum ( datediff ( MINUTE, case when timesheet.timein < @timeframe_start then @timeframe_start else timesheet.timein end, case when timesheet.timeout > @timeframe_end then @timeframe_end else timesheet.timeout end ) ) as total_duration from ( select timein.empid, timein.swipe_time as timein, timeout.swipe_time as timeout from tbltest timein left join tblTest timeout on timein.empid = timeout.empid and timeout.eventtype=”ex” and … Read more
I have several servers running, and I was trying to run this command from a remote server instead of a local one. So I was getting this error because the batch file was obviously not present on the remote server. 7 solved How to run a batch file in xp_cmdshell [closed]
I’m not sure you can do that the way you’re trying, Why don’t you load the image into a variable then use that variable in your insert statement: declare @image varbinary(max) set @image = (SELECT BulkColumn from Openrowset( Bulk ‘C:\Users\Yassine-Kira\Desktop\Templates\ProductImg\elite-book_tcm_133_1096796.png’, Single_Blob) as BikeImage) insert into dbo.Produit values (‘Pc portable’, ‘HP EliteBook série p’, ‘Un ordinateur…’, … Read more
You can achieve what you’re after like below: CREATE TABLE SampleTable ([col1] varchar(1), [col2] int, [col3] int, [date] varchar(16)) ; INSERT INTO SampleTable ([col1], [col2], [col3], [date]) VALUES (‘a’, 11, 0, ‘3/6/2015:0:00:00’), (‘b’, 5, 4, ‘3/6/2015:0:00:00’), (‘c’, 5, 5, ‘3/6/2015:0:00:00’), (‘d’, 3, 0, ‘3/6/2015:0:00:00’), (‘e’, 21, 21, ‘3/6/2015:0:00:00’) ; SELECT t2.SumCol2, sum(t1.col2 * t1.col3) / … Read more
I have prepared a SQL script for you which splits the input string twice then converts is into row data, and finally inserts into a database table To split string data, I used this SQL split string function First of all, you need to create this function on your database. Unfortunately, if you do not … Read more
What I was Missing was Implied permissions Versus Explicit Permissions. I assumed explicted permissions were used, not implied\inheritied permissions. solved List all Securable\Permissions on all tables for all roles and owners
I guess you can use MAX(MID(Emp_ID, 1)) in place of MAX(Emp_ID). If it still does not work, converting string to number should help. Example – MAX(TO_NUMBER(MID(Emp_ID, 1))). Edit – First of all, there is no use for order by. And since you asked for SQL server, use this – SELECT ‘E00’ + CAST (MAX (CONVERT … Read more
Duplicated question (I guess) Looking for a SQL Transaction Log file viewer You can use third party software to read transaction logs. http://www.red-gate.com/products/dba/sql-log-rescue/ http://www.apexsql.com/sql_tools_log.aspx http://www.toadworld.com/products/toad-for-sql-server/w/wiki/10586.log-reader.aspx And if you want to audit truncate command try to audit all commands executed on your database. http://www.databasejournal.com/features/mssql/article.php/3861791/Database-Level-Auditing-with-Microsoft-SQL-Server-2008.htm solved Is there a log in SQL Server where I can read … Read more