SQL 2012 :: Incorporate Several Queries In 1
Dec 18, 2014
I am wondering if it is possible to make 1 query that gives several, different resultsets.
I am working on a database for speedskating times, in particular I am working on seasonlists
The query I use for this i (in simplified form)
SELECT LastName, FirstName, Country, Time
FROM TbExample
WHERE Distance = '500' and Gender = 'Man' My question is about the "WHERE Distance = '500' and Gender = 'Man' " part.
I need the combinations 500-Man, 1000-Man, 1500-Man, 500-Women, 1000-Women, 1500-Women.
In the current setup I need to make 6 queries.
Is it possible to make some kind of (as they call it in Excel) Array (or 2: 1 for distances, 1 for gender) that runs through all possible combinations?
View 9 Replies
ADVERTISEMENT
Jan 9, 2014
We have accounts that pay for a particular "premium" service. It's entirely possible an account paid for this service for three consecutive months in 2013, then stopped paying, then started paying again. Why I'm trying to establish is, for the FIRST period of time the accout paid for this service, for how many consecutive months did they pay? Here is my test data:
if object_id('tempdb..#SampleData') is not null
drop table #SampleData
go
if object_id('tempdb..#DateAnalysis') is not null
drop table #DateAnalysis
go
-- Create temp tables for sample data
create table #SampleData
(AccountID int,
RandomDate datetime)
create table #DateAnalysis
(RowID int identity(1,1),
AccountID int,
RandomDate datetime,
NextDate datetime,
LeadInMonths int)
-- Insert some sample data
insert into #SampleData values
(1, '1/1/13'), (1, '2/1/13'), (1, '3/1/13'), (1, '6/1/13'), (1, '10/1/13'), (1, '11/1/13'), (1, '12/1/13'),
(2, '1/1/13'), (2, '4/1/13'), (2, '5/1/13'), (2, '6/1/13'),
(3, '2/1/13'), (3, '3/1/13'), (3, '4/1/13'), (3, '9/1/13'), (3, '10/1/13'), (3, '11/1/13')
-- Use lead function to determine how many months are between
-- consecutive dates per account
; with DateInterval as
(select AccountID, RandomDate,
NextDate = lead (RandomDate, 1, NULL) over (partition by AccountID order by RandomDate)
from #SampleData)
insert into #DateAnalysis
select AccountID, RandomDate, NextDate,
datediff(mm, RandomDate, NextDate) as 'Lead'
from DateInterval
where NextDate is not null -- Last row will contain NULL for NextDate. Don't include these rows.
-- Show the results
select *,
'NTile' = NTILE(3) over (partition by AccountID order by RandomDate),
'RowNum' = row_number() over (partition by AccountID order by RandomDate)
from #DateAnalysis
Results (this is not getting me what I'm looking for):
RowIDAccountIDRandomDateNextDateLeadInMonthsNTileRowNum
11 2013-01-012013-02-01111
21 2013-02-012013-03-01112
31 2013-03-012013-06-01323
41 2013-06-012013-10-01424
51 2013-10-012013-11-01135
61 2013-11-012013-12-01136
72 2013-01-012013-04-01311
82 2013-04-012013-05-01122
[code]....
This is what I'm trying to achieve:
RowIDAccountIDRandomDateNextDateLeadInMonthsRankForThisLeadInMonths
11 2013-01-012013-02-0111
21 2013-02-012013-03-0111
31 2013-03-012013-06-0132
41 2013-06-012013-10-0143
51 2013-10-012013-11-0114
61 2013-11-012013-12-0114
[code]....
The problem comes with accounts like AccountID = 1. They paid consecutively to start, then skipped, then started paying consecutively again. When using window functions, I'm running into trouble attempting to partition by AccountID and LeadInMonths. It's putting all the LeadInMonths = 1 together and that will give me skewed results if I want to know the earliest and latest date within the FIRST consecutive range of dates where the account paid. I've tried NTILE but it expects an integer and there's no telling how many "tiles" would be in AccountID partition.
I've looked at the OVER clause and the new "ROWS BETWEEN" syntax and still cannot get the desired results.
View 5 Replies
View Related
Oct 5, 2005
Hello everyone,
Please i need your help...
I dont know how to place the field 'strTitle and datBorrowed " in my email? Not as an attachment though....Just write it in the mail as part of message body...
I use this SQL select statement to retrieve the strTitle and datBorrowed fields
strSQL += @"Select replace(strtitle,'[Original Book] - ',''), datBorrowed from tblBooks where convert(varchar(10),datBorrowed,101) = convert(varchar(10),(getdate() - 1),101) ORDER BY strTitle asc";
Now, I have the following code to write the email
static void SendTest()
{
int iEmailLanguage = 0;
MailMessage objMail;
objMail = new MailMessage();
objMail.From = MAIL_FROM;
objMail.To =MAIL_TO;
objMail.Subject = "Books Borrowed Yesterday";
objMail.Body = Dict.GetVal(iEmailLanguage, "EMAIL_MESSAGE");
objMail.Attachments.Add(new MailAttachment(strAttachment));
SmtpMail.SmtpServer = SSMTP_SERVER;
SmtpMail.Send(objMail);
}
And the body of the email is this......
Dict.AddVal(0, "EMAIL_MESSAGE", "*** This e-mail is automatically generated. ***" +
"*** PLEASE DO NOT REPLY TO THIS E-MAIL. ***" +
"" +
"Books Borrowed Yesterday are:" +
"" +
"" +
"Thank you," +
"" +
"eLibrarian" +
"" +
"================================================== ===============" +
"" +
"This e-mail is automatically generated by the Library system." +
"Please do not reply.");
i need to put or wedge the data i got from the SQL Statement into this or after the line "Books Borrowed Yesterday are:" +
So how should i do this?
View 3 Replies
View Related
Feb 26, 2007
What is the best approach to utilize a recursive CTE (Common Table Expression) to filter a resultset? The CTE function is used in all application queries to limit recursively the final resultset based on a provided hierarchical organization identifier. i.e. join from some point in the organization chart on down based on an organization id. I would prefer that the query could be run real-time. i.e. not having to persist the prediction portion of the results to a sql relational table and then limiting the persisted results based on the CTE function.
It appears that I can use a linked server to access the prediction queries directly from SQL Server (link below). I believe that I might also be able to deploy a CTE recursive function within a .net assembly to the Analysis Server but I doubt that recursive functionality is availalble without a linked SQL Server.
Executing prediction queries from the relational server
http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3914.aspx
View 1 Replies
View Related
Nov 2, 2015
We have two queries that run nightly and we'd like to combine them and only have one result set instead of two. What's the best way to combine these? The only difference is the Table the information is being pulled from.
Query 1:
set nocount on
select
case
when datalength(MICRACCTNUMBER) = 4 then convert(char(20),('001 000000000000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 5 then convert(char(20),('001 00000000000'+MICRACCTNUMBER))
when datalength(MICRACCTNUMBER) = 6 then convert(char(20),('001 0000000000'+MICRACCTNUMBER))
[code].....
Again, the only difference is the Table the info is coming from...
View 3 Replies
View Related
Nov 6, 2015
what the ideal CPU count and Max Degree of Parallelism are for a 3rd party database server.The server has 12 CPUs, 32GB RAM and all database sizes add up to < 30GB so they can all fit in memory (I tried to force this by doing a select * from every table). On certain payroll days, the CPU gets maxed out to 100% for a few seconds.
MAXDOP was originally set to the default 0. We later changed it to 8 based on several 'best-practices' articles. However the vendor suggests to change it to 1 (no parallelism), while others suggest changing it to 4, so that one run-away query doesn't hog most of the CPUs.
I'd like to find out how many CPUs are actually being used by queries. There is a Degree of Parallelism event in URL.... The BinaryData column says :
0x00000000, indicates a serial plan running in serial.
0x01000000, indicates a parallel plan running in serial.
>= 0x02000000 indicates a parallel plan running in parallel.- What does "parallel plan running in serial" mean ?
I see a lot of 0x01000000, and a few 0x08000000's in my trace.How can i determine whether one query is hogging CPUs and if reducing it to 4 will work?
View 4 Replies
View Related
May 13, 2014
Is there an easy way to convert Access Queries to SQL Views without doing it manually?I have used the Databse tool to migrate tables, but cannot see to find something similiar for queries.
View 3 Replies
View Related
Jul 10, 2014
We recently upgraded to sql server 2012. We have xxx-D-011 as OLTP server and yyy-D-011 as distributor server.
The log is showing deadlocks every day between application queries/updates and replication jobs.
A fragment of the log about the deadlock is included below.
2014-07-10 15:31:05.94 spid13s deadlock-list
2014-07-10 15:31:05.94 spid13s deadlock victim=process37ced3498
2014-07-10 15:31:05.94 spid13s process-list
2014-07-10 15:31:05.94 spid13s process id=process37ced3498 taskpriority=0 logused=0 waitresource=OBJECT: 8:532249001:0 waittime=357 ownerId=860304057 transactionname=SELECT lasttranstarted=2014-07-10T15:31:05.090
[code]....
View 9 Replies
View Related
Jul 14, 2014
I have to create a SQL job which will run around 50 queries and email results when the query gets some results.These are like quality checks which run to check errors in the system so if any query(out of 50 queries) returns some results an email with the details will be sent .So if 5 queries return results 5 emails with the details will be sent .
I think of something like A table which has one column as the query .What will be the best way of handling such a scenario , may be need an SSIS package with steps ?
View 3 Replies
View Related
Oct 21, 2015
Is there a way to setup a trace to show only direct TSQL statements triggered on my server? note I don't want to capture Procedure calls or the statements called within the procs.
Actually many people are firing direct SQL statements on server. And some are coming from entity framework as well. I just want to capture those.
View 1 Replies
View Related
Feb 27, 2014
I am using Server 2012 and very new to SQL. I have a request from a physician for a list of his patients that meet a criteria. This is stored in a temp table names #cohort.
Using this cohort he wants each row to be one patient with a list of labs, vitals, etc. Three items are the most recent lab value and date. I could query each lab individually and place it into a temp table and then join all temp tables at the end, but I am trying to move past that and have all labs in one temp table. All temp tables are joined with PatientSID.
I tried to do something for just 2 labs, but it is not working. There could be nulls values when joined with the #cohort table.
Individually the SELECT statements pull in the most recent lab value and date, but I cannot get them into a temp table with one row of PatientSID and then the lab value and date if they exist.
IF OBJECT_ID ('TEMPDB..#lab') IS NOT NULL DROP TABLE #lab
SELECT
cohort.PatientSID
,SubQuery1.LabChemResultNumericValueAS 'A1c%'
,SubQuery1.LabChemCompleteDateTimeAS 'A1c% Date'
,SubQuery2.LabChemResultNumericValueAS 'LDL'
[Code] .....
View 1 Replies
View Related
Jun 9, 2014
So I'm trying out full-text indexing for the first time and, in particular, FileTables in SQL Server 2012. I've followed a Microsoft walkthrough and everything seems to be ok. However, when I query the table using the CONTAINS keyword, I get no results (a regular query to make sure there are records in the table returns the expected number of results).
I'm now trying to troubleshoot, and have been using the FULLTEXTCATALOGPROPERTY function, but I don't understand the results.
If I run SELECT FULLTEXTCATALOGPROPERTY(N'CatlogName',N'ItemCount'), I get a result of 51. There are 96 documents in the NTFS folder where the documents are stored, and the table has 96 records, so I don't know where 51 is coming from. 55 of the documents are .DOC files, the rest are .PDF, and some (or maybe all) of the PDFs are scanned images of documents, which I don't expect to be indexed, so maybe that explains it. And in another thread in these forums, a poster suggests that the result for this function should be either 0 or 1, with 0 meaning that no documents are pending indexing, but maybe I've misunderstood that.
If I run SELECT FULLTEXTCATALOGPROPERTY(N'CatalogName',N'UniqueKeyCount'), I get a result of 2. I have got two full-text indexes in this catalog (one on the FileTable, one on a regular table with FT enabled). Is this result therefore expected? Again, reading online seems to suggest that a result of 0 is desirable, but I don't understand why, and if it is I don't understand why my result is 2!
I've now also run SELECT* FROM sys.dm_fts_index_keywords(DB_ID('DatabaseName'), Object_ID('dbo.FileTableName)), which I believe is supposed to list all of the indexed words from the table specified. I get one row returned, as follows:
keyword: 0xFF
display_term: END OF FILE
column_id: 2
document_count: 40
So basically, it's not indexed any words at all. And why is the document count only 40 when there are 96 documents in the folder and table?
View 2 Replies
View Related
Jul 21, 2014
know if there is any way out to run execution plan for parameterized queries?
As application is sending queries which are mostly parameterized in nature and values being used are very robust in nature, So i can not even make a guess.
View 1 Replies
View Related
Feb 22, 2015
I need to create a few select queries based on an er diagram.
These are my Create Table statements and import statements:
Create Table Agent (Aid integer primary key, Pid integer, aName text);
Create Table Product (Pid integer primary key, pName text);
Create Table Supplier (Sid integer primary key, sName text);
Create table Supplies (Sid integer, Pid integer, price decimal(8,2));
.import agent.txt Agent
.import product.txt Product
.import supplier.txt Supplier
.import supplies.txt Supplies
I think I got all my create table statements are correct.
I need to Find the number of agents for each supplier that has at least one agent. The result should be tuples of the form (sid, sName, number of agents)
-Select Sid, sName, count(Aid) from Agent A join Supplier S on (S.Sid = A.Sid) group by S.Sid, S.sName, Aid;
But it gives me this error: no such column: A.Sid
Im thinking I might have a problem with my create table statement and/or primary key statements?
View 9 Replies
View Related
Sep 16, 2015
Our developers have gotten this idea lately that instead of having many small stored procedures that do one thing and have small parameter lists that SQL can optimize query plans for, its better to put like 8-10 different queries in the same stored procedure.
They tend to look like this:
create procedure UberProc (@QueryId varchar(50))
as
if @QueryId = 'First Horrible Idea'
begin
select stuff from something
end
if @queryid = 'Second really bad idea'
begin
select otherstuff from somethingelse
end
I see the following problems with this practice:
1) SQL can't cache the query plan appropriately
2) They are harder to debug
3) They use these same sorts of things for not just gets, but also updates, with lots of optional NULLable parameters that are not properly handled to avoid parameter sniffing.
View 9 Replies
View Related
Feb 3, 2015
At one of your client sides we have configured Always on with synchronous mode.Also we have schedule rebuild index and update statistics job which runs in night every alternate day. the issue is there are more then 100 sleeping queries which is blocking update statistics job.
I have to stop update statistics job manually once i come to office manually.
Once I have killed blocking sleeping query but then other sleeping query blocked it and so on.
View 4 Replies
View Related
Jul 20, 2015
I need to search for such SPs in my database in which the queries for update a table contains where clause which uses non primary key while updating rows in table.
If employee table have empId as primary key and an Update query is using empName in where clause to update employee record then such SP should be listed. so there would be hundreds of tables with their primary key and thousands of SPs in a database. How can I find them where the "where" clause is using some other column than its primary key.
If there is any other hint or query to identify such queries that lock tables, I only found the above few queries that are not using primary key in where clause.
View 2 Replies
View Related
Feb 16, 2014
Since upgrading from SQL Server Management Studio 2008 R2, I've noticed that it no longer autosaves queries that have not been manually saved first. If a file has been manually saved the autorecover files end up in the following directory:
%appdata%MicrosoftSQL Server Management Studio11.0AutoRecoverDatSolution1
However, I have ended up in the situation where I have unsaved queries when my computer has crashed and have not been able to recover them.
I have also found references to .sql files stored in temp files in the following directory, but the files here seem to be very haphazardly caught:
%userprofile%AppDataLocalTemp
View 2 Replies
View Related
Feb 27, 2014
In one of our requirement, I want all the query details for the SQL query batch that got executed for the day. I know, we can get sql query from dm_ exec_ query_stats. But I want all sql query along with their session details ie. ExecutedDateTime, SessionId, UserID etc. I have tried using sys.dm_ exec_ sessions. But it contains only last executed query details for all the sessions. how to obtain all the session details for all the query executed for the day in the server.
View 7 Replies
View Related
Jul 20, 2005
HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
View 1 Replies
View Related
May 26, 2015
SQL Server 2012 Data Tools was working fine for me but something must've changed, now every time I try to create a new SSIS project I get:
The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT)).
When I try to open an existing project I get:
exception has been thrown by the target of an invocation
external component has thrown an exception (SSISUpgrade)
The issue seems to only arise with SSIS projects.I have already uninstalled SQL Server 2012 and reinstalled it and that didn't work.I tried to install Visual Studio 2012 Data Tools with BI and that also crashes when I try to create an SSIS project.Output of SQL Server SELECT @@VERSION is:
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
SQL Data Tools page info:
Microsoft SQL Server Integration Services Designer
Version 11.0.2100.60
Microsoft Visual Studio 2010
Version 10.0.40219.1 SP1Rel
Microsoft .NET Framework
Version 4.5.51641 SP1Rel
View 5 Replies
View Related
Feb 13, 2015
I have several users with an unusual problem with SSMS 2012. When they attempt to connect to a database using the "Connect to Server" dialog box, the connection just hangs. Sometimes after about 15 minutes the connection will be successful. Other attempts simply spin seemingly endlessly. Users experiencing this issue are both running SSMS 2012 on Win 7 Pro (64 bit). The following troubleshooting steps have been tried:
1. When the user runs SSMS "As Administrator" the connections work almost instantly. (Elevating privileges is not a solution in our environment)
2. Wireshark shows that SSMS does not try to hit the SQL server when the user experiencing this issue clicks connect.
3. I can create a new virgin user on the PC and that login experiences the same problem.
4. A complete rip and re-install of SSMS 2012 does not resolve the issue.
View 3 Replies
View Related
Jan 29, 2008
Presently using CONVERT(VARCHAR(11), [ExpiryDate], 100) to get close to the correct format.Only stumbled across this by accident so am assuming there might be better hidden 'treasures' .Formatting dates seems to be my biggest time-consuming activity and I just don't seem to get better at it!
View 4 Replies
View Related
Aug 9, 2006
Hi,
I am using visual web developer2005 express edition and finding hard time to get my query run in this i am making my own login page as i have few more things to ask to user before they get logged in so i am not using the login control.
i want to write my own query without help of sqlDataSource control from start something like
sqldatasource con=new sqldatasource;
con.connection String=""
then what all things will come........ ???
and please give me some poitners to some articles which help one to do the requested.
Regards,
View 1 Replies
View Related
Dec 7, 2007
I have an update query which either inserts a row or increases quantity, depending if row exists or not. It works, better than my explanation probably.After that query could be a good time to count total of all calculated sub sums.Something like this. previous queryEND goSELECT SUM(SubTotal)FROM dbo.t_Shoppings I have tried this on the tool which has a long name, but I think my way didn't work. (Microsoft sql server management studio express)Is this possible or do I have make and call another stored procedure.I can send my sp if someone wants.
View 4 Replies
View Related
Dec 18, 2007
how can i execute for example
2 queries together, in a single stored procedure Select top 20 * from Product where Active=1select Count( *) from product if i execute such one how can i get the 2 results in vb/c# ?
View 3 Replies
View Related
Jun 18, 2004
hi which is bettere to use a quesry from the code or to use a Stored Procedure and call the SP from the code
View 3 Replies
View Related
Oct 21, 2004
Hi All,
Not sure if I've got the correct place for this question. But, I'm trying to create and sql query to list the lates 10 items in a database. So far I haven't had any luck finding this.
All I have is a normal query (below). Can anyone help me please?
SELECT * FROM pages WHERE show = 'yes' ORDER BY id Desc;
Regards,
Rich
View 2 Replies
View Related
Jan 23, 2006
Hi,I have 5 tables in sql database, naming Book, Category, Subject, UserDownload, User.In Book table, BookID, BookTitle, CategoryIDIn Category table, CategoryID, CategoryNameIn Subject table, SubjectID, SubjectName, CategoryIDIn UserDownload table, UserID, BookIDIn User table, UserID, UserNameI used Book to store information of books. Those books has many categories. In those categories, there is also some subjects.When user downloads book, I update UserDownload table.The result I want to get is, Top Ten Download Subject. How can I get? Please help me.
View 1 Replies
View Related
Jul 16, 2002
Could anyone help me?I need all the commands like select,from etc.Basically I need to learn writing queries etc.Please
View 1 Replies
View Related
Aug 3, 2000
I just upsized my Access2K db to SQL. I am using Front Page 2000 for my website. When I had the database as Access, I was able to use one of my Access queries as my record source for my data base. I was able to choose between my queries AND my tables as the source for my records. Now that I've upsized, I am no longer given that choice. My only choices are the tables. Unfortunately, my database is designed to pull records from a query, not just a table. So my question is, in FP2000, how do I use a QUERY from my newly upsized SQL db as my record source?
Thank you all very much in advance.
View 5 Replies
View Related
Feb 7, 2003
Hi!
Where to write MDX Queries and how to call the query in Analysis Services.
Please help me on this.
View 2 Replies
View Related
May 1, 2001
I'd like to know how (if it's possible) to write a TRIGGER in SQL that will, when a limit has been reached, remove an entry from a table...
Any ideas? Thanks.
View 1 Replies
View Related
