SQL 2012 :: Query That Gives Info Twice?

Aug 13, 2015

I would like to do a query that would give me the info twice, with just a small difference.

Let's say I have a table with elements A, B and C. Whatever it is, it does not matter.

I would like to get all the data so I just do that:

SELECT A, B, C

Then I would like to add a bit of text, so I'll add:

SELECT A, B, C, 'Text1' as D

The thing is that I would like every single elements (A,B,C) to be repeated twice: Once with Text1, once with Text2.

Let's say my data looks like that:

1 2 3
2 1 3
4 1 2

Then my query would give me this:

1 2 3 Text1
2 1 3 Text1
4 1 2 Text1
1 2 3 Text2
2 1 3 Text2
4 1 2 Text2

(in no particular order)

Is there a way I can do this in just one query?

View 9 Replies


ADVERTISEMENT

SQL Server 2012 :: Query Pulling Latest Info Data From Table

Aug 29, 2014

Let's say I have a table of data as per the below..

I'm trying to extract only the green highlighted items..

The rules applied are: Only the latest data concerning all cases, and only 1 line (the latest) per case.

As you can see in the image, I don't want the 2nd,3rd, and 4th record extracted cause they are all superseded by more recent records (identified as they are further in the table).

I've considered using either Distinct or Having? but can't get that to work.. If I could use Distinct but then ensure it's the latest record in the table that would be perfect.

View 7 Replies View Related

SQL 2012 :: Export Dead Lock Info To User?

Dec 10, 2014

How to export the dead lock information to the user ?

View 2 Replies View Related

SQL 2012 :: Extended Events Capturing Info On Dev Environment But Not On PRO?

Jan 27, 2015

I do have the following Extended Event session on my Dev box;

CREATE EVENT SESSION [sp_showplan] ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan(SET collect_database_name=(1)
ACTION(sqlserver.plan_handle)
WHERE ([package0].[equal_uint64]([object_type],(8272)) AND [sqlserver].[equal_i_sql_unicode_string]([object_name],N'MyStoreProcedure')))
ADD TARGET package0.event_file(SET filename=N'E:DBA_AuditSP_Exec.xel',metadatafile=N'E:DBA_AuditSP_Exec.xem')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

The idea is being able to capture the execution plan when the program invokes the store procedure, regardless of the database.

This works on my Dev box. When I manually trigger "MyStoreProcedure" from database A , the event is saved. The same thing happens when I do that from database B. Ok ... so far, so good.

So I went to the live production environment and setup my Extended Events session. But it's saving nothing. I was able to check that the store procedure was executed on several databases but my extended events session never grabbed the plan.

What could be the reason for this? Memory starvation maybe? Is there something I am doing wrong?

View 5 Replies View Related

Can We Pass Some Info From Sql Query To Dos

Oct 3, 2004

suppose i get date from MSsql how do i pass that to dos command is there a way so i can maipulate date and pass on to dos command to run at certain time

View 14 Replies View Related

Query The Latest Info

Jan 6, 2005

Hi All,

I have thsi list of record

Chasis Status Date
pl1 sold 10/20/2004
pl1 return 10/21/2004
pl2 sold 10/24/2004
pl2 return 10/25/2004
pl3 sold 11/01/2004
pl4 sold 11/03/2004
pl4 return 11/04/2004
pl4 sold 11/06/2004


sp i want to list out cars that status solid has been sold

so in this case only pl3 and pl4 can be display. So anyone can advise me on this. thanks

Regards,
Shaffiq

View 6 Replies View Related

Multiple Types Of Info In One Query?

Sep 21, 2006

Hi,

I am trying to display a report from a bug tracker that shows the number of issues logged per month, per year and how many issues have been closed in that month. I have no difficulty displaying the number of issues that have been logged within a particular months, but when I need to display the number of issues closed - I get stuck. I am calling from one table 'bugs', which contains date_reported, status_id. I can do a count on the number of bugs between certain dates but I cannot list the number of closed issues in that exact time. I get the feeling I need a select statement within a select statement? If anyone can help. Thanks

View 3 Replies View Related

Amending Query To Get Info From New Table

Oct 19, 2013

I used to use this code to extract coupon usage from two tables orders and coupons

select distinct o.OrderDate, p.SKU, c.CustomerID, c.CustomerLevelID, c.Email, c.FirstName, c.LastName from customer c
join dbo.Orders_ShoppingCart os with (NOLOCK) on os.CustomerID = c.CustomerID
join dbo.Product p with (NOLOCK) on p.ProductID = os.ProductID
join dbo.Orders o with (NOLOCK) on o.OrderNumber = os.OrderNumber

[code]....

Following a database upgrade the Coupon Code is no longer used. Instead there are two new SQL tables called Promotions and PromotionsUsage.. The link between the two tables are

ID in Promotions

and

PromotionID in PromotionUsage

Both are the same value. So in order to see new orders for coupon usage how would I change this line in the original SQL to retireve this?

View 5 Replies View Related

Dates In Query Analyser - More Info

Apr 21, 2006

I am trying to test sp in Query Analyser as below but get the error

Server: Msg 8114, Level 16, State 4, Procedure spRMU_GetChaseFiles, Line 0
Error converting data type varchar to datetime.

How do I put the date as a parameter ? I think it may be to do with the fact that MaxDateOut is an expression in my view that the sp is based on. It is taking this as char when it should be datetime. So, how do I make this expression type date ?

EXEC spRMU_GetChaseFiles
@strStartDate ='01/01/05',
@strEndDate = '21/04/06',
@strDepartment = 'Central Units',
@strDivision = 'Personnel',
@strSection = ''

View 20 Replies View Related

How To Query Active Users - Where Is This Info Stored ???

Jul 25, 2001

I know how to look at active users from Enterprise Manager, but how can I query out the information - what table is that info stored in ??? I don't want a list of all the logins, I just want the list of active users....

Help... thanks in advance,
Nancy

View 2 Replies View Related

Is There A System Table With Timestamp Info Or DTS Job Info?

May 7, 2007

I want to be able to see when records have been added to a table. The issue is we have a DTS job scheduled to run every night. The developer who wrote it password protected it and doesn't work here anymore. I want to add a step to this series of DTS jobs and want to run it just prior to his job. Is there a way to see when the records are being added or when this job is being run? Thanks again, you guys are the best.

ddave

View 3 Replies View Related

SQL 2012 :: Persist Security Info And Integrated Security In Connection String

Dec 4, 2014

I use from sql server 2008. and c#

what is the best connectionstring?

I don't know if i use Persist Security Info and Integrated Security or not?

And if yes then their value must be true or false?

View 1 Replies View Related

Query Info Between Time Range & Date Range

Aug 16, 2006

I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!

View 6 Replies View Related

SQL Server 2012 :: Adding Count To Query Without Duplicating Original Select Query

Aug 5, 2014

I have the following code.

SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,

[code]...

However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.

View 2 Replies View Related

SQL Server 2012 :: How To Pull Value Of Query And Not Value Of Variable When Query Using Select Top 1 Value From Table

Jun 26, 2015

how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...

create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)

[code]...

View 4 Replies View Related

SQL Server 2012 :: Query To Get Min Value

Feb 7, 2014

Below is what I have so far Also I am getting other FRID besides 0 as well.

Select P.PartID, P.mansPart, P.Cost As PartCost, V.PartID, V.PartNo, V.price As VendPrice, M.title, C.CoName, P.FRID
From Parts As P
Right Join Vend2Part As V
On P.PartID = V.PartID And P.FRID = V.FRID
Left Join companies As C

[Code] ....

View 4 Replies View Related

SQL 2012 :: Query With Three Tables?

Jul 22, 2015

I currently have a query that uses three tables [Table1, Table2, Table3]. Each table only has two columns: Group and something else. I want a query that gives me the group and those three something else.

Table1: Group and Info1
Table2: Group and Info2
Table3: Group and Info3
Query: Group, Info1, Info2, Info3

The groups never repeat in a single table.

However, the groups are not identical in every table. I want the groups to never repeat twice: All the groups should appear if they are there at least once in one of the table, and provide Info1, Info2, Info3 (blank if it's not there in its table, filled if it is).

That should be a fairly simple query, I just can't seem to make it work so groups don't repeat.

Little visual example.

Table1: Group - Info1
1 - a
2 - b
4 - c
5 - d

Table2: Group - Info2
1- aa
3- bb
4- cc

Table3: Group - Info3

5- aaa

The query would give me this:

1 - a - aa - ""
2 - b - "" - ""
3 - "" - bb - ""
4 - c - cc - ""
5 - d - "" - aaa

View 3 Replies View Related

SQL Server 2012 :: Add Total To Query

Dec 18, 2013

I have a table with the Group name and Total Count by group . I need to add a 'Total' and summation of all counts at the end .

CREATE TABLE #TempTable
(GroupName VARCHAR(10),NumberOfCases INT
INSERT INTO #Temp Table (GroupName,NumberOfCases)
VALUES ('Grp A',10)
INSERT INTO #Temp Table (GroupName,NumberOfCases)
VALUES ('Grp B',20)

[code]....

View 5 Replies View Related

SQL Server 2012 :: Query To Fill RAM?

Apr 28, 2014

I work for a federal government client who restricts us to using virtual machines for dev work. Now the VM team has gotten MUCH more aggressive with "right-sizing" (which always means down-sizing) our virtual machines. I have a Dev server with 8 GB RAM and I need that much when developing, but I don't develop all the time. So I want it to look like it needs all that RAM before they bust me down to 1 GB, which they will do.How can I get SQL Server to use up all the memory I've allocated to it? Is there a relatively easy query to do this?

View 8 Replies View Related

SQL 2012 :: Interpreting Query Statistics

Jun 5, 2014

I'm designing a new database which will be the back-end to a heavily-used web-based application (all these terms are relative - I guess the use won't be that heavy in the grand scheme of things, I'm only talking 100 users or so at the very most). Data from the old application database will be migrated to this one, and the old database is around 7GB in size after 5 years of use.

I have two different ways of linking some tables in mind, one which is slightly more complex than the other but which potentially has benefits over the simpler method. However, I'm concerned that I might be 'over-cooking' the design, and that performance would suffer as a result, so I've tried creating the two different versions of the database (the part of it I'm concerned with, anyway), one for each of the solutions I've got in mind, migrated the data into the relevant tables and carried out some queries on the data to collect some statistics.

The problem is that, whilst I can see that the more complex method is more expensive, as expected, I don't really understand if the difference is significant. Since I don't know what the numbers in the Client Statistics window actually mean (there are no units! I'm guessing times are in milliseconds?), or how much of real-world impact the difference will have, I'm finding it hard to interpret my statistics and come to a decision.

Querying the entirety of my tables to return ~20,000 records listing one column from each of the main tables I'm playing with, the simpler method had a Total Execution Time of 199, and the more complex a Total Execution Time of 272. Is that the statistic I should be most concerned with? Is that a difference I should be concerned about? Is the difference likely to be magnified when the database is much larger and in use, such that a difference of 73 milliseconds in this test scenario could end up being as much as a whole second in production, for example?

View 1 Replies View Related

SQL 2012 :: Incremental Load Of CDC Using Query

Jun 6, 2014

I required query for Incremental Load of CDC using Query

View 1 Replies View Related

SQL 2012 :: Mapped Credential Is Not Used In Any Query?

Jul 9, 2014

Due to a previous (mis)configuration, i need to grant readwrite permission on a share from a MSSQL DB User.The SQL user will launch t-sql queries on demand and they cannot be scheduled.

I've created a credential object in SSMS, configured it with the correct AD user and mapped it to the MSSQL DB user.Now, if i execute a simple t-sql backup:

BACKUP DATABASE [DB] TO DISK = N'IP.ADD.RE.SSshareDB.bak' WITH NOFORMAT, NOINIT, NAME = N'DB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

I get an access denied error, monitoring the sqlserv.exe process via procmon, i see that the Sql Server process is not impersonating the AD user configured in the credential, it still try to access using the local machine account .

View 1 Replies View Related

SQL 2012 :: Source As Query In CDC In SSIS

Aug 13, 2014

I am working on task where I need to load data into DWH from OLTP. Load needs to b incremental load. For this I am planning to use CDC, to track changes and only load data which has been new inserts/updates/deletes. But to load data to destination, there are couple of joins, which I need to do. Is there a way in CDC, where I can use query as source?

View 0 Replies View Related

SQL 2012 :: Obtain Two Columns In A Query?

Aug 19, 2014

i want to obtain two columns in a query but i donĀ“t know how to.ex.

col1 col2
1 A
1 B
2 A
1 C

I need something like this.

col1 col2
1 A, B, C
2 A
Col1 = Table Z
Col2 = Table Y

View 6 Replies View Related

SQL 2012 :: Query Notifications For More Than Just Cache?

Jan 13, 2015

All the recommendations I see from Microsoft docs is to limit the use of Query Notifications (QNs) to notifying connected clients when changes to mostly-static reference or configuration data occur, and to keep the number of overall query forms in play and connected clients to a minimum. Any way regarding a more integral use of QNs and Service Broker from a web app to notify n-web servers (farm) of an update to data that could be updated concurrently and quite frequently, or with a system where the technique is used extensively with lots of different query forms?

View 0 Replies View Related

SQL 2012 :: Same Query But Different Execution Plan

May 15, 2015

I have same query but when executed from different server use different plan. when it runs on QA box it is faster and when it runs on PRD it is slow.

Is it possible to force SQL Server to use QA plan by giving a hint?

View 2 Replies View Related

SQL 2012 :: What Is A Remote Connection And Query

Jun 10, 2015

What is a remote connection and query ? And what is a local connection and query? If i'm on management studio on my pc connected to a SQL Server 2012 that is on a server, am I remote ? I didn't had a timeout in SSMS for the following query. Considering the remote query timeout setting is 600

USE Database_name
go

BEGIN
WAITFOR DELAY '00:11';
select * from [dbo].[Table_Name]
END;
GO

View 3 Replies View Related

SQL 2012 :: Get All Installed Hotfix With A Query?

Jul 15, 2015

is it possible to get all installed Hotfix with a Query?

like this?

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

View 0 Replies View Related

SQL 2012 :: Converting Query To XML Result?

Jul 15, 2015

I'm struggling to modify the T-SQL query below to return the results as XML.

SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID IN (119, 122);

The XML result document should have the following structure:

<Root>
<ProductModelData id="119">
<Name>Bike Wash</Name>
</ProductModelData>
<ProductModelData id="122">
<Name>All-Purpose Bike Stand</Name>
</ProductModelData>
</Root>

View 2 Replies View Related

SQL 2012 :: Query Execution With Rules

Jul 24, 2015

I need to implement one logic similar to rule engine. Below is the example.how to execute all queries in second table order?? what is the best way to implements this

declare @tblRules AS Table (RuleNo INT, RuleDesc NVARCHAR(500), RuleQuery NVARCHAR(MAX), QueryExecutionInterval NVARCHAR(50))
declare @tblRuleResults AS Table (RuleResultID INT, RuleNo INT, ExecuteTime DateTime, NextExecutionTime DateTime, Result NVARCHAR(10))
INSERT INTO @tblRules VALUES ('1','Fail - 2 times within 1 Hour','XXX','Every 15 Minutes')
INSERT INTO @tblRules VALUES ('2','Fail- 2 times within 2 Hour','YYY','Every 30 Minutes')

[code]....

View 2 Replies View Related

SQL 2012 :: Writing A Query To A File

Oct 22, 2015

I am trying to write a query to a file with a unique name and pipe "|" delimited. Problem is in 2012 the sp_cmd does not exist. I am currently using the code

set @filepat = 'C:Temp'
set @filename = 'test.txt'
set @sqlCommand = 'SQL_CMD -S (local) -E -d SqlAndMe -q "EXEC ExportData" - "' +@filepath + @filename + '" -h-1'
EXEC master..xp_cmdshell @sqlCommand

View 1 Replies View Related

SQL 2012 :: Query For History Table?

Nov 3, 2015

I have order history table that tracks who worked on a order

ID. ColA. ColB. ColC
1. Process 1234
2. Work. 7666
3. Return. 6789
4. Work. Null Role1
5. Return. 6538

I want a query to return recent colB or ColC where colA or colB are not null. In this case row 4

I have order history table that tracks who worked on a order

Another example

ID. ColA. ColB. ColC
1. Work. 1234
2. Process. 7666
3. Return. 6789

I want a query to return most recent colB or ColC where colB or ColC are not null. In this case row 2

View 1 Replies View Related

SQL Server 2012 :: How To Reduce Joins In Query

Nov 13, 2013

I have select query with 10 Joins in place with different i want to improve the performance of the query how do i join tables with out joins.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved