For example, i have a query result like this: UserID Key 1 AN 1 AC 1 MN 2 KK 2 VC I would like to generate result like this: UserID Keys 1 AN*AC*MN 2 KK*VC Is there any way to do it in sp?
I want to write a query to generate a report. I have a date column which will be holding all the business dates. No dates of Saturday and Sunday are allowed. What I am looking for is, I want to get the result of every 5th business day of each month. A month could start with any day. I just want only the 5th business day.
Here's a challenge that is killing me:I've got 2 web servers and a SQL Server and about 5,000 'users' whostay connected to the site all day. I have a page that is supposed tobe 'real-time', so to do this, I have a 1px frame that refreshes every15 seconds (so the other frame doesn't have to reload all the time--thetop only reloads when a new record or a changed record hits the db).The real time data can be filtered in about 8 different ways.Currently, I have each user querying a table that contains 1 record,including the max ID and the most current insert/update posting date.The browser contains a cookie with that date. When the browser receivesnotification that there is some new info on the server, it refreshesthe top page and reloads the data. This is happening for all users. So,I thought to eliminate the 5,000 users running the same (or closevariations) of the same query each time a records is inserted/updated,that I would generate an XML file with the current day's data.In a dev environment this works 'ok'. I'm doing this by running anActiveX job on the SQL Server that calls a stored proc (FOR XML) andwrites the content to a file. Then from the web servers, I'm queryingthis file for the new timestamp and then if newer than the cookie,grabbing the XML (using the httprequest in the ASP XMLDOM) and usingXSLT to transform the data instead of parameterizing the queries.Theoretically I love this solution. Problems happen in a LIVEenvironment where either the file is being written to or the job isn'table to run. When 2 records are trying to be written within the samesecond, the file isn't being written (or maybe that the http requestingthe XML is keeping the file locked?)....anyway...this is a HUGE problethat I can't seem to solve. Once we roll to .NET I think storing thedataset in cache and updating cache (still don't know how I'll triggerthat without each user checking the db)....Long winded, sorry...help?
I am creating a key-wording module where I want to search data using the comma separated words.And the search is categorized into comma ',' and minus '-'. Take a look on the example what I exactly want to do is
7 woman,girl,Digital Tablet,working,smiling,happiness,hand on chin
If serch text is = Man,Businessman then result AS_ID is =1,2 If serch text is = Man,-Businessman then result AS_ID is =3 If serch text is = woman,girl,-Working then result AS_ID is =4,5
Now with the above result, On every record I have to fire a query Select SUM(sale), SUM(scrap), SUM(Production) from tableB where ProdID= ["ProdID from above query"].How to write this query in a Stored Procedure so that I can get the required SUM columns for all the ProdID's from first query?
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value ----------- ----- colB 123 colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
I am facing a problem that following query give me different result whenever I execute the query, it returns me same number of records but not all the rows are same everytime it is executed.
Select[Field1] From ( SelectRow_number() Over(Partition By [Field2], [Field3] Order By [Field2], [Field3], [Field4] Desc) ID, * From[dbo].[Table1] ) A WhereID > 1 OrderBy [Field1]
Those highlighted in yellow colours are duplicate records, I need to remove one of them.
I have a script that produce a result set that is almost complete. I have a new requirement come up to put a Total at the button of the result set of my Query.
I'm using SQL Server 2008 ;WITH CTE AS ( SELECT Case WHen left(MONTHDate,3)='Jan' Then '01' WHen left(MONTHDate,3)='Feb' Then '02' WHen left(MONTHDate,3)='Mar' Then '03' WHen left(MONTHDate,3)='Apr' Then '04' WHen left(MONTHDate,3)='May' Then '05'
I have some data which is vertical...I want to create a pivot query in SQL that will give me a result that is horizontal like this. I cannot find a way of doing it without lots of IF or CASE statements?
how to write a query to get current date or end of month date if we pass year and month as input
Eg: if today date is 2015-09-29 if we pass year =2015 and month=09 then we have to get 2015-09-29 if we pass year =2015 and month=08 then we have to get 2015-08-31(for previous months we have to get EOMonth date & for current month we have to get current date).
The generated script output is in a order of stored procedure first and then table.
REQUIREMENT: My stored procedure is dependent on table. So, I need the table script first and then stored procedure.
Note: I can generate two separate scripts for table and stored procedure, But in a just curiosity to know, Is there any way, can we re order the Generate Scripts output in SQL Server.
HI, I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected) The following file has been saved successfully: C:outputcustomers.rpt 10826 bytes
I choose Query select to a file then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend invalid TLV record
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC. Tables I have are structured as follows:
SHOWS showID showTitle
SHOWACCESS showID remoteID
VIDEOS videoDate showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle, (SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID) FROM shows, showAccess WHERE shows.showID = showAccess.showID AND showAccess.remoteID=21 ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate. Can anyone shed some light on where I am going wrong?
I need to return a computed value and have no idea how to do it.
I have a table of dates: Column DataType RowID int Person nvarchar(20) Percent real Date smalldatetime
What I need is the Annual Fiscal Year Average. The fiscal year starts and ends in June. I need it to add all Percent values between the months of June 2001 - June 2002, June 2002 - June 2003, and so on and on....
A result set like this...
Person - FiscalYear - FiscalYearResult John D - 2001 - .58 John D - 2002 - .52 John D - 2003 - .50 Jane D - 2001 - .58 Jane D - 2002 - .52 Jane D - 2003 - .50
so on and so on...
How do I write this in Transact-SQL to get this result set...
I am trying to generate a script to drop 15 tables which have dependencies across the database. Is there a script that could generate the drop stataments based on the child table first , parent table last strategy?
I am using SQL 2012 SE. I have 2 databases say A and B with same structure and relationships. There are 65 tables in each database. A is already replicating data to database C for 35 tables. Now I need to move data from A to B which is greater than getdate()-1 everyday for all the tables and once the move is done I need to delete this data from A. And samething the next day. Since this is for 65 tables its challenging to identify the insert order. Once the insert order is identified the delete order will be the reverse of it.
Is there a tool or any SP that could generate the insert order script? The generate scripts data only is generating the entire data and these databases are almost 400GB. So I thought of generating a schema only script and then create an empty database with it and then generate data only to identify the order of insert. But it wont generate anything since there is no data.
I am wanting to fire-off an email with the failed jobs anytime they are deposited into a table. My syntax fires off an email even when the table does not contain data, it just sends a blank email. this will only generate an email if teh table contains data?
if exists (Select from FailedJobs) exec msdb.dbo.sp_send_dbmail @profile_name = 'DatabaseMail' @recipients = 'asdfasdfsdf@aafas.com' @from_address = 'asdfasdfacasca@cc.com' @query = 'Select * from failedjobs' @subject = 'List Of Failed Jobs' @attach_query_result_as_file = 1;
I need to generate the week ranges like this format :
Here from date and to date would be picked up from the table but just to make you understand i have hard coded it but this is the real date which is falling inside the table.
Note : Week should be generated from Monday to Sunday within desired date range
I have 5 different tables with same structure. Each table has an account column and another column count. I am trying to generate a report based of the columns in each table. Account is same in all tables with same account numbers but count will be different.
I have to insert YEAR WEEKNUMBER STARTDATE ENDDATE values to a datatable (sayweekrange), if I pass 2011 as year.
Week range starts at 2011-03-28 to 2011-04-03 (because in my database 2010 last week range ends with 2011-03-27) like this I have to generate for 52 weeks.
I want to write a stored procedure, that takes only year as parameter. with this year I have to generate week ranges and insert into my table as shown above.
I want generating Valid date ranges from any list of dates.
The List of Dates could be generated from the below TSQL -
SELECT '2015-06-02' [Date] UNION ALL SELECT '2015-06-13' UNION ALL SELECT '2015-06-14' UNION ALL SELECT '2015-06-15' UNION ALL SELECT '2015-06-16' UNION ALL SELECT '2015-06-22' UNION ALL SELECT '2015-06-23' UNION ALL SELECT '2015-06-24'
And the expected output should look like -
SELECT '2015-06-02' FromDate, '2015-06-02' ToDate UNION ALL SELECT '2015-06-13' FromDate, '2015-06-16' ToDate UNION ALL SELECT '2015-06-22' FromDate, '2015-06-24' ToDate
