SQL Server 2012 :: Using Unions To Write Out Each Select Query As Distinct Line In Output
Aug 20, 2015
Basically I'm running a number of selects, using unions to write out each select query as a distinct line in the output. Each line needs to be multiplied by -1 in order to create an offset balance (yes this is balance sheet related stuff) for each line. Each select will have a different piece of criteria.
Although I have it working, I'm thinking there's a much better or cleaner way to do it (I use the word better loosely)
Example:
SELECT 'Asset', 'House', TotalPrice * -1
FROM Accounts
WHERE AvgAmount > 0
UNION
SELECT 'Balance', 'Cover', TotalPrice
FROM Accounts
WHERE AvgAmount > 0
What gets messy here is having to write a similar set of queries where the amount is < 0 or = 0
I'm thinking something along the lines of building a table function contains all the descriptive text returning the relative values based on the AvgAmount I pass to it.
View 6 Replies
ADVERTISEMENT
Apr 21, 2015
Using below script to export the select statement result to .xls
declare @sql varchar(8000)
select @sql = 'bcp "select * from Databases..Table" queryout c:bcpTom.xls -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql
But result is not exporting in seperate tabs, all 4 column details are exporting in single cell.
how to export the data in columns to separate tabs in excel.
View 2 Replies
View Related
Aug 13, 2014
I've the following T-SQL block that I'm executing where I want to print the following output in next line but not sure how to do this:
This is printing everything in one line:
SET @BODYTEXT = ' Dear ' + @fname +',We realize you may have taken ' + @course_due + ' in '+ @month_last_taken +'.'
How do I do this:
SET @BODYTEXT = ' Dear ' + @fname +
',We realize you may have taken ' + @course_due + ' in '+ @month_last_taken +'.'
Also how can I create a table in this variable, something like this:
(TABLE) LIST THE COURSE CODE, COURSE NAME , EMPLOYEE ID, EMPLOYEE NAME
(Course Name) (Last Completed) (Now due in Month/year)
My T-SQL code:
DECLARE @email varchar(500)
,@intFlag INT
,@INTFLAGMAX int
,@TABLE_NAME VARCHAR(100)
,@EMP_ID INT
,@fname varchar(100)
[Code] ......
View 2 Replies
View Related
Jul 14, 2014
How can i get the total number of each distinct value in the column and write it to another table e.g.
MyTable
Id Fruit
1 Apple
2 Banana
3 Apple
4 Watermelon
5 Banana
6 Watermelon
7 Apple
Result
Fruit Count
Apple 3
Banana 2
Watermelon 2
View 9 Replies
View Related
Mar 2, 2015
I have SQL Server 2012, and I'm trying to execute a bcp with a queryout and it is not working with line break, I will send you an example of a simple query my real case is a very long query that contains line breaks but this example behaves the same:
DECLARE @StoredProcedure AS NVARCHAR(4000) ,
@sql AS NVARCHAR(4000)
SET @StoredProcedure = N'"SELECT *
from clarity.[dbo].[ACCESS_DEP]"'
[code]...
Of course that is not the solution as I have a very long query that requires line breaks.
View 1 Replies
View Related
Mar 25, 2014
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.
View 9 Replies
View Related
Apr 30, 2015
One of my varchar columns in a table has multiple key words enclosed in a pattern of special characters.
Eg: William Shakespeare was an English [##poet##], [##playwright##], and [##actor##], widely regarded as the greatest [##writer##] in the English language and the world's pre-eminent [##dramatist##]. He is often called England's national [##poet##] and the "Bard of Avon". His extant works, including some collaborations, consist of about 38 plays, 154 [##sonnets##], two long narrative [##poems##], and a few other [##verses##], of which the authorship of some is uncertain. His plays have been translated into every major living language and are performed more often than those of any other [##playwright##].
I need to write to query to find all distinct key words that are enclosed within [## and ##]. My query should yield the following results from the string in the example above
[##actor##]
[##dramatist##]
[##playwright##] -- 2 occurrances, but I need it only once in my result set
[##poems##]
[##poet##] -- 2 occurrances, but I need it only once in my result set
[##sonnets##]
[##verses##]
[##writer##]
I need to run this on a large table, so I am looking for the best possible way to minimize any performance issues.
Just give you sample code, I have provided below 2 separate snippets, one with table variable and another with temp table.
DECLARE @MyTable TABLE (MyString VARCHAR (8000))
INSERT @MyTable VALUES ('William Shakespeare was an English [##poet##], [##playwright##], and [##actor##], widely regarded as the greatest [##writer##] in the English language and the world''s pre-eminent [##dramatist##]. He is often called England''s national [##poet##] and the "Bard of Avon". His extant works, including some collaborations, consist of about 38 plays, 154 [##sonnets##], two long narrative [##poems##], and a few other [##verses##], of which the authorship of some is uncertain.
[code].....
View 7 Replies
View Related
Jan 23, 2015
I have to write a Stired Procedure with the following functionality.
Write a simple select query say (Select * from tableA) result is
ProdName ProdID
----------------------
ProdA 1
ProdB 2
ProdC 3
ProdD 4
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?
View 2 Replies
View Related
Jan 29, 2014
Below is the sample data
create table #sample
(name varchar(100),
id int)
insert into #sample values ('customerid','15339119')
insert into #sample values ('Title','15339119')
insert into #sample values ('firstname','15339119')
insert into #sample values ('prevcr','2146822710')
insert into #sample values ('currcr','2146822710')
insert into #sample values ('brandcode','2146822710')
I need output as attached ...
View 5 Replies
View Related
Mar 23, 2015
I am working on a code that will convert the query output into a html query output. That is the output of the query will be along with html tags so that we can save it as a html file.
The stored procedure that i have used is downloaded from symantec website and is working fine. Below is the code of that stored procedure.
/****** Object: StoredProcedure [dbo].[sp_Table2HTML] Script Date: 12/21/2011 09:04:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Table2HTML] (
@TABLENAME NVARCHAR(500),
@OUTPUT NVARCHAR(MAX) OUTPUT,
[Code] ....
The code works fine and i am able to get the output with html tags. The problem occurs when i insert stylesheet in the code. I tried to enforce styles using a stylesheet for the table returned in my sql code, so that it will look good. below is the stylesheet code that i inserted between <head> and </head> tags.
<style type="text/css">table.gridtable { font-family: verdana,arial,sans-serif; font-size:10px; color:#333333;border-width:1px; border-color: #666666; border-collapse: collapse; } table.gridtable td {border-width: 1px; padding: 8px; border-style: solid; border-color: #666666; background-color: #ffffff;}</style>
If I run the procedure without the style sheet code, it works fine. but when i run the procedure with style sheet code i am getting the below errors.
Msg 105, Level 15, State 1, Line 98
Unclosed quotation mark after the character string '</table></body><'.
Msg 102, Level 15, State 1, Line 98
Incorrect syntax near '</table></body><'.
[Code] .....
I checked the code and i am not able to find what is the mistake. I tried changing the quotation mark but it didn't worked.
View 6 Replies
View Related
Jun 25, 2013
I need to update the result depending upon the count of distinct entries.
Example
ID Employee Region State
1 ABC AMEA MI
2 DEF AMEA MI
3 XYZ APAC TX
I want the result as below
ID Employee Region State
1 ABC AMEA MI-1
2 DEF AMEA MI-2
3 XYZ APAC TX
since the count of Region is 2
I tried using DECLARE @intFlag INT and stuff but wasn't able to get the solution.
View 2 Replies
View Related
Mar 21, 2008
I have two tables one with current data and a second with the same design that holds purged history.
I was going to create view and then jsut use a where clause to filter both tables but I figured it would be faster if the where clause was passed into each query as opposed to the whoel view having to load and then be filtered.
Select PatientName, MemberId
FROM CLAIMS
WHERE MemberID = @MemberID
UNION
Select PatientName, MemberId
FROM CLAIMSHistory
WHERE MemberID = @MemberID
But it appears that if there are no records in the first statement that the whole thing fails. I tried the union all operator with out any luck either.
Now if had a view like this;
Create view vAllClaims as
Select PatientName, MemberId
FROM CLAIMS
UNION
Select PatientName, MemberId
FROM CLAIMSHistory
And said select * from vAllClaims where memberid = 12345 would the query optimizer put the build the where statement onto each subquery or pull all the data first and query it?
View 4 Replies
View Related
Sep 25, 2015
I am trying to write a stored procedure that is an INSERT INTO <sql table> FROM <other sql tables> WHERE <where clause>.
I need to insert 3 columns from multiples SQL tables into one SQL table. Here is my code snippet:
ALTER PROCEDURE [dbo].[sp_insert_test]
@MID INT OUTPUT,
@CIDINT OUTPUT,
@MemberNVARCHAR OUTPUT
[Code] ....
i cannot pass any of the values to the tblVotings table and not sure how to enter the @MID, @CID and @Member to the INSERT INTO statement.
View 7 Replies
View Related
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
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
Nov 9, 2006
when i use select :
select flattened
(select productid, $support from [model name].[table] )......
i have result with many record same.
so i write :
select flattened
(select distinct productid, $support from [model name].[table] )......
but when i run, it's error. ( don't know what error).
how can i do to get table record with not same record (loop)
note : it write in DTS and i use sql 2000
View 4 Replies
View Related
Aug 30, 2006
Hi
I have Lookup task to determine if source data should be updated to or insert to the customer table. After Lookup task, the Error Output pipeline will redirect to insert new data to the table and the Output pipeline will update customer table. But these two tasks will be processing at the same time which causes stall on the process. Never end.....
The job is similiart to what Slow Changing Dimention does but it won't update the table at the same time.
What can I do to avoid such situation?
Thanks in advance,
JD
View 4 Replies
View Related
Feb 6, 2008
Hello, I want to do a DISTINCT SELECT query with more than one field, for example a ID field with a Type field, as if both fields make the primary key, like (ID 1 ,Type 1) ,( ID 1, Type 2) and (ID 2, Type 1) is ok but not (ID 1, Type1) and (ID 1,Type 1) if its not possible to do a distinct with more than one then what other techniques are possible to get the duplicate data out. the reason why I want to use distinct is that I can use that query to export that data to where both of these fields make the primary key.
Thanks in advance
View 5 Replies
View Related
Oct 26, 2006
I need to return the current case cost for every UPC in my table. In my current query I return case costs that have an effective date of today or earlier. The problem is that in my results, one UPC may have two or more case costs that were are effective <= GETDATE(). I can sort it by effective date (DESC) so I know the first of every UPC in my results will be the current effective case cost, but how do I modify my query so that in my result set I only get the first of every UPC?
Here is my query:
SELECT factCaseCosts.nUpcKey, factCaseCosts.dCaseCost, factCaseCosts.dtEffectiveDate
FROM factCaseCosts
WHERE (factCaseCosts.dtEffectiveDate <= GETDATE())
ORDER BY dtEffectiveDate DESC
Here is my current result set:
52023.762006-08-01 00:00:00
52023.762006-02-18 00:00:00
52123.762006-08-01 00:00:00
52123.762006-02-18 00:00:00
52230.362006-08-01 00:00:00
52230.362006-02-18 00:00:00
52323.762006-08-01 00:00:00
52323.762006-02-18 00:00:00
I only want the first 520 returned, the first 521 returned, the first 522 returned, and the first 523 returned.
How can I do this?
Thanks!
View 3 Replies
View Related
Feb 18, 2015
This is part of a query I have. I want to display the diagnosis code attached to a patient. Sometimes there are more than one which is why I have tried distinct. I am getting the message 'subquery returned more than 1 value...'
If I use MIN or MAX, its giving me the min possible diagnosis code alphabetically or the max, not the one actually assigned to the patient which is what I want.
(SELECT DISTINCT (Diagnosis.DiagnosisCode) FROM Diagnosis
LEFT OUTER JOIN CourseDiagnosis ON CourseDiagnosis.DiagnosisSer = Diagnosis.DiagnosisSer
LEFT JOIN Course ON Course.CourseSer = CourseDiagnosis.CourseSer
JOIN Patient ON Patient.PatientSer = Course.PatientSer
WHERE Diagnosis.ObjectStatus = 'Active' AND
Diagnosis.Description not like 'ERROR%' AND
Diagnosis.DiagnosisTableName not in ('RTDS Dates')),
View 10 Replies
View Related
Dec 7, 2006
Hello,
I'm reasonably new to SQL.
I have a table of information about meetings with various people. In it, I have the person's name and the date/time of the meeting (simplified example).
I need to write a query that SELECTs only the most recent meeting for each person.
Any ideas would be greatly appreciated.
View 2 Replies
View Related
Jul 20, 2005
Hi,I have a table as followingaa Text1 aa, Join Bytes!, 15267aa Text1 aa, Join Bytes!, 16598aa Text1 aa, Join Bytes!, 17568aa Text2 aa, Join Bytes!, 25698aa Text3 aa, Join Bytes!, 12258I have to write a query as follows ...SELECT DISTINCT TOP 500 fldText, fldContact, fldItemidFROM tableWHERE fldCat = 10 AND CONTAINS (fldText, 'Text1')In the example you can see the table has rows in which text and contact ordouble but with different itemid's. Now my employer wants me to show only 1row when text and contact or the same. He doesn't mind which itemid I show.... but I have to show one.I've an idea of how to do this using a cursor and a temporary table but Iguess that will be fatal for the performance because then I have to loopthrough all selected rows, check each row with all other rows and store theprimary key in the temporary table if dedected it isn't double. AfterwardsI can execute ... SELECT ... FROM TABLE where primary key in (selecttemp_primarykey from #temptable).I hoped I could do everything in 1 "easy" SELECT but I should not know how?Any ideas are much appreciated.Thanks a lot.Perre Van Wilrijk.
View 1 Replies
View Related
Apr 7, 2007
Hi
If i use this code i cant get the data showed, it show nothing."SELECT COUNT(DogImageDate) AS Amount, DogImageDate, DogImageID FROM EnggaardImages WHERE DogImageDate NOT LIKE (SELECT TOP 1 DogImageDate FROM EnggaardImages ORDER BY DogImageDate DESC;) GROUP BY DogImageDate ORDER BY DogImageDate DESC;"
But if i use this code i get data showed"SELECT COUNT(DogImageDate) AS Amount, DogImageDate, DogImageID FROM EnggaardImages GROUP BY DogImageDate ORDER BY DogImageDate DESC;"
Then i get Images(6)Images(1)Images(1)
But i dont want the first to be showed, therefor i use the Select TOP 1, so i get a look like this
Images(1)Images(1)
But i cant get it to work.
View 2 Replies
View Related
May 28, 2014
There are about 10 select statements in a store procedure.
All select statements are need.
Is it possible to output only the result of last select statement?
View 2 Replies
View Related
Nov 18, 2013
I have a requirement like below .
Input table will be like below:
eventdata:
accountID deviceID timestamp speedKPH address
--------- -------- ---------- -------- -------------
preva1 bolero 1359089006 15 Ullalu Road
preva1 bolero 1359088796 0 Ullalu Road
preva1 bolero 1359088886 0 Ullalu Road
preva1 bolero 1359088888 8.47 Ullalu Road
preva1 bolero 1359088986 0 Ullalu Road
preva1 bolero 1359088988 45 Ullalu Road
preva1 bolero 1359088996 21 Ullalu Road
preva1 bolero 1359088998 0 Ullalu Road
preva1 bolero 1359089006 15 Ullalu Road
preva1 bolero 1359089009 12 Ullalu Road
preva1 bolero 1359089006 15 Ullalu Road
preva1 bolero 1359089016 0 Ullalu Road
preva1 bolero 1359089026 0 Ullalu Road
So here i need output table like below:
stoppagedetails:
accountID deviceID from_timestamp to_timestamp diff
--------- -------- ---------- -------- -------------
preva1 bolero 1359088796 1359088888 92
preva1 bolero 1359088986 1359088988 2
preva1 bolero 1359088998 1359089006 8
preva1 bolero 1359089016
How to write mysql query for the above requirement.
View 3 Replies
View Related
Jul 9, 2014
I want to write sql query which runs in a background on cyclic basis. Basically i want to count the row entries of 1 table and store the data and the count in two distinct columns.
View 3 Replies
View Related
May 11, 2015
What I really need is to find the entries that have the word LVAD but on either side there should be a space chartacters.In other words I don't want to see the following coming from the query "SILVADENE ([PHI] SULFADIAZINE)"How can we write this query to search only for any entry that has the word " LVAD " ?
Select top 100 * FROM tbl_abc
where
AIMS_Value like '%LVAD%'
View 2 Replies
View Related
May 22, 2015
using below code to replace the city names, how to avoid hard coding of city names in below query and get from a table.
select id, city,
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(city,
'JRK_Ikosium', 'Icosium'), 'JRK_Géryville', 'El_Bayadh'),'JRK_Cirta', 'Constantine'),'JRK_Rusicade', 'Philippeville'),
'JRK_Saldae', 'Bougie')))
New_city_name
from towns
View 3 Replies
View Related
Aug 27, 2015
I have a table (ScriptTable) which holds a groupID Nvarchar(10) ,SQLStatement Nvarchar (150)
Table Fields =
GroupID SQLStatement
1234 Select CUSTNO, CUSTNAME,CUSTADDRESS from custtable where customerNo = 'AB123'
9876 Select CUSTNO, CUSTNAME,CUSTADDRESS from custtable where customerNo = 'XY*'
What I need is to take each select statement in turn and add the data into a temp table. I can use any method but it needs to be the most efficient. There can also be a varying number of select statements to run through each time my job is run.
View 6 Replies
View Related
May 2, 2008
i want to select the values of column passed from the user like
connectionsizein or connectionsizemm
how can i do
i am using sql server 2005
View 2 Replies
View Related
Mar 22, 2008
Hi,
I wonder if its possible to perform a ORDER BY clause in an SELECT DISTINCT sql query whereby the AS SINGLECOLUMN is used. At present I am recieving error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. My guess is that I cant perform the Order By clauses because it cant find the columns individually. It is essentail I get this to work somehow...
Can anyone help? Thanks in advance
Gemma
View 10 Replies
View Related
Jan 10, 2014
I'd like to ask how you would get the OUTPUT below from the TABLE below:
TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B
OUTPUT:
category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6
The code would go something like:
Select category, count(*), .... as id's
from TABLE
group by category
I just need to find that .... part.
View 3 Replies
View Related
Jul 6, 2007
Hi, I have the following script segment which is failing:
CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude))
INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache
When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."
Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.
The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.
Any ideas?
View 2 Replies
View Related