SQL Server 2012 :: How To Subtract A Column From A Query Result And Add Difference To Another
Mar 20, 2015
I have a query which provides the below result set:
1165 6
1,173.0013
9740 6
9820 13
2271 6
2287 13
10,952.006
11,029.0013
4,074.006
4,103.0013
I want to achieve something like below. It should subtract the '13' row to '6' row and provide another column with the result. the '6' and '13' category code share the same Key.
1165 6 -8.00
1,173.0013-8.00
9740 6 -80
9820 13 -80
[code]...
View 5 Replies
ADVERTISEMENT
May 26, 2014
I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.
View 1 Replies
View Related
Aug 15, 2014
New column calculation
CREATE TABLE MAIN
(
ORDERNO VARCHAR(20),
LASTUPDATEDDATE DATE,
ORDERCLIENTINITIALFEE NUMERIC ,
[Code] .....
---OUTPUT
--=======
INSERT INTO MAIN VALUES ('1000', '1/1/2014',3000,1000,700,1500)
INSERT INTO MAIN VALUES ('1000', '3/5/2014',1000,2000,650,200)
INSERT INTO MAIN VALUES ('1000', '5/10/2014',500,5000,375,125)
INSERT INTO MAIN VALUES ('1000', '11/20/2014',100,2000,400,300)
INSERT INTO MAIN VALUES ('1000', '8/20/2014',100,3500,675,1300)
[Code] ....
View 2 Replies
View Related
Sep 23, 2014
I'm using SQL Server 2012 and I need to run a query against my database that will output the difference between 2 dates (namely, DateOfArrival and DateOfDeparture) into the correct month column in the output.
Both DateOfArrival and DateOfDeparture are in the same table (let's say GuestStay). I will also need some other fields from this table and do some joins on some other tables but I will simplify things so as to solve my main problem here. Let's say the fields needed from the GuestStay table looks like below:
I need my query to output in the following format:
How to write this query?
View 9 Replies
View Related
Dec 12, 2013
I have my sql tables and query as shown below :
CREATE TABLE #ABC([Year] INT, [Month] INT, Stores INT);
CREATE TABLE #DEF([Year] INT, [Month] INT, SalesStores INT);
CREATE TABLE #GHI([Year] INT, [Month] INT, Products INT);
INSERT #ABC VALUES (2013,1,1);
INSERT #ABC VALUES (2013,1,2);
[code]....
I have @Year and @Month as parameters , both integers , example @Year = '2013' , @Month = '11'
SELECT T.[Year],
T.[Month]
-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
,
(SELECT SUM(Stores)
FROM #ABC
WHERE [Year] = T.[Year]
AND [Month] = T.[Month]) AS [Sum_Stores],
(SELECT SUM(SalesStores)
[code]....
What I want to do is to add more columns to the query which show the difference from the last month. as shown below. Example : The Diff beside the Sum_Stores shows the difference in the Sum_Stores from last month to this month.
Something like this :
+------+-------+------------+-----------------+-----|-----|---+-----------------
| Year | Month | Sum_Stores |Diff | Sum_SalesStores |Diff | Sum_Products |Diff|
+------+-------+------------+-----|------------+----|---- |----+--------------|
| 2013 | | | | | | | |
| 2013 | | | | | | | |
| 2013 | | | | | | | |
+------+-------+------------+-----|------------+--- |-----|----+---------| ----
View 3 Replies
View Related
Aug 9, 2014
I wanted to know the best way to achieve the following results. I have a table that I need output sequential range of vouchers in a table. For instance I have the following data in a column called vouchers. The output will consist of a years worth of vouchers, so voucher numbers may contain gaps and so the need to have a sequential range that has a From and To output. The query needs to know the min and max within that numerical range and then output the next min and max range until it gets to the end.
The data looks like:
ABCD-001869202
ABCD-001869203
ABCD-001869204
ABCD-001869205
ABCD-001869209
ABCD-0018692010
ABCD-0018692011
ABCD-001869309
ABCD-001869310
ABCD-001869311
ABCD-001869312
ABCD-001869313
ABCD-001869314
Desired out put:
From To
ABCD-001869202 ABCD-001869205
ABCD-001869209 ABCD-0018692011
ABCD-001869309 ABCD-001869314
I have tried the following, but it does not quite do what I need it to do, so not sure if I am taking the right approach:
SELECT voucher vouchers,right(voucher, charindex('-', voucher) + 3) voucher
INTO #tempVoucher
FROM LEDGERJOURNALTRANS
where TRANSDATE between '10/1/2013' and '7/31/2014' and VOUCHER like 'APIN%'
[Code] ...
View 6 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
May 25, 2015
Here the SELECT query is fetching the records corresponding to ITEM_DESCRIPTION in 5 separate transactions. How to change the cursor to display the 5 records in at a time in single transactions.
CREATE TABLE #ITEMS (ITEM_ID uniqueidentifier NOT NULL, ITEM_DESCRIPTION VARCHAR(250) NOT NULL)INSERT INTO #ITEMSVALUES(NEWID(), 'This is a wonderful car'),(NEWID(), 'This is a fast bike'),(NEWID(), 'This is a expensive aeroplane'),(NEWID(), 'This is a cheap bicycle'),(NEWID(), 'This is a dream holiday')
---
DECLARE @ITEM_ID uniqueidentifier
DECLARE ITEM_CURSOR CURSOR
[Code] ....
View 1 Replies
View Related
Jul 27, 2015
I have a table with dates and values and other columns. In a proc i need to get the result as Month and the values for all the months whether or not the data exists for the month.
The Similar table would be-
create table testing(
DepDate datetime,
val int)
insert into testing values ('2014-01-10 00:00:00.000', 1)
insert into testing values ('2014-05-19 00:00:00.000', 10)
insert into testing values ('2014-08-15 00:00:00.000', 20)
insert into testing values ('2014-11-20 00:00:00.000', 30)
But in result i want the table as -
Month Value
Jan1
Febnull
Marnull
Aprnull
May10
Junnull
Julnull
Aug20
Sepnull
Octnull
Nov30
Decnull
View 9 Replies
View Related
Oct 14, 2015
I have the following table
Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
1 |John |Baker
2 |Carl |Lennon
3 |Marion |Herbert
Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
1 |1/1/14 |12/30/14
2 |1/1/15 |12/30/15
[code]...
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID AS SickBalance
and the table should be shown as below after executing the query
EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance
View 4 Replies
View Related
Sep 7, 2006
I have a table like this:
Bottom Top
0 2
4 6.5
9 14
15 17
In Transact SQL I need to calculate the difference between the current bottom and the previous top. If there is no previous top, then the result would be the current bottom.
So, the result would be 0, 2, 2.5, 1
I must return the value in a table. Thank you all for your help!
Karen
View 9 Replies
View Related
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
Feb 25, 2012
When I run query in excel it gives result with different column sequence. The same query gives result with different column sequence when used in query analyzer or VBA Macro. E.g., Select * from ABC.
result in Excel 2003 SQL OLE DB query
col-A col-B col-C
values...
Result with Query Analyzer and VBA Macro
col-c col-B col-A
values...
View 3 Replies
View Related
May 9, 2015
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...
Below is what I have:
CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC
[Code] ....
View 4 Replies
View Related
Jan 11, 2005
Simple example would look like that in MS SQL
SELECT 'a' AS (SELECT language_name FROM language WHERE language_id = 1)
So that the display is
English
a
as we assume that
SELECT language_name FROM language WHERE language_id = 1
returns only English
I have tried to that with a variable but it does not work
declare @that as varchar(15);
set @that = (select language_name_u from language where language_id = 1);
select 'a' as @that
LOL
I just tried another way as i was going to post
declare @that as varchar(15);
set @that = (select language_name_u from language where language_id = 1);
select 'a' as "@that"
and it worked!
Posting anyway so people might answer with a better solution with no variable
Thanks a lot
Mordan
View 1 Replies
View Related
Aug 6, 2015
I have the following query and where I need to use the t_PrevSession.DischargeTime which is in the nested query that is bolded below. How do i bring it up to the main select statement?
SELECT
s.facilityid,
s.sessionid,
s.MRN,
[code]....
View 2 Replies
View Related
Jul 20, 2005
I am firing a query like:SELECT TaskName, StartDate FROMTasksWHERE StartDate >= '01-Aug-2003'Now the result comes as:TaskName StartDate--------------------------Task1 01-Aug-2003Task5 10-Oct-2003etc.Now what I want is this:Sl. No. TaskName StartDate----------------------------------1 Task1 01-Aug-20032 Task5 10-Oct-2003How do I get the Sl. No. field (it does not exist in the table).Can I do it?-surajit
View 11 Replies
View Related
Nov 23, 2007
Hi,
I'm wondering is it possible to store query result to column directly?
for example I have 2 tables
Cache
----------
Cache_ID (int)
Cache_content (Text)
cache_created (Datetime)
Orders
-------------
Order_ID
Order_amount
Order_Date
Order_Delivered
and the query is
select * from order where order_date = '2007-11-23' and 'Order_delivered='N'
I want the result to be stored in cache table either csv or xml
I'm using sql 2000 server.
any idea?
View 7 Replies
View Related
Jun 3, 2004
I'm having a bit of a trouble explaining what I'm trying to do here.
I have 3 "source" tables and a "connecting" table that I'm going to use
tblContacts - with contactID, ContactName etc
tblGroups - with GroupID, GroupName
tblSubGroups - with SubGroupID, GroupID and SubGroupName (groupID is the ID for the parent Group from tblGroups)
They are related in a table called
tblContactsGroupConnection - with ContactID, GroupID and SubGroupID
One contact can be related to many subgroups.
What I want is a list of all contacts, with their IDs, names and what groups they are related to:
ContactID, ContactName, [SubGroupName1, SubGroupName2, SubGroupName3]
ContactID, ContactName, [SubGroupName1, SubGroupName3]
ContactID, ContactName, [SubGroupName3]
I'm sure there's a simple solution to this, but I can't find it. Any help appreciated. :)
Kirikiri
View 1 Replies
View Related
Mar 12, 2015
I have the following query that displays 2 values. I want to add a column with the percentage ([Providers With Security]
/ProviderTotal) * 100
SELECT (SELECT COUNT(DISTINCT NPI) FROM HS140_Rpt_Tmp_ForSummary WHERE Market = s.Market) AS ProviderTotal,COUNT(DISTINCT NPI) AS [Providers With Security]
FROM HS140_Rpt_Tmp_ForSummary s
WHERE s.[Security] = 'Yes'
GROUP BY Market
How can I do this?
View 1 Replies
View Related
Apr 21, 2005
Hello Friends
My problem is
Suppose Query is - Select * from tbl_Employee
TBL_EMPLOYEE HAS ONLY TWO COLUMNS NAME,POST
I need that an extra column get inserted in result through query showing serial Number with each row.So that query result look like this.
Serial Name Post
1 XYZ QER
2 SDF OPO
3 WER IPO
If any body knows please post the solution its urgent.
View 11 Replies
View Related
Apr 23, 2004
I have a query that returns a list of results from the database and insert the results into a datatable. Is there any way to add a column to the query result that has autonumbers in it. for example the first record would have 1 the next 2, the next 3, and so on?
View 4 Replies
View Related
Feb 26, 2015
Is there a way to display a column alias as part of the result set column labels?
View 9 Replies
View Related
May 24, 2007
i am creating a log file, and at 1 point I record how many rows are deleted after an update process.. I want to also record which productID are actually deleted... how would I write that?!
Basically I need know how I would get the list, and I am using PRINT command... I need to somehow write a loop that works through the list and PRINTS to the file....
Thanks in advance
View 1 Replies
View Related
Sep 18, 2007
Hi,
I need to create a derived column for each row in a SQL dataset.
This derived column needs to be created by passing across two values from the SQL dataset and querying an Oracle table based on those parameters. If the Oracle query returns a record(s) then the derived column should be set to 1 otherwise leave it as default (0).
One of these parameters needs to check a date range so I can't use a Lookup Transformation...any ideas how I can accomplish this ?
Thanks
View 5 Replies
View Related
Nov 20, 2007
I have a query that uses rollup to get totals and top 101 to restrict the rows to top 100 rows plus the total column. The total column shows total for all rows not just the top 100 which is exactly what i need. The only thing I need now is a row that shows total for all rows that have been excluded, or the total row - total of the top 100.
Here's the query: (Biggest factor i have to keep in mind is query speed.)
Code Block
WITH CTE_name AS (
SELECT TOP 101 b.name,
SUM(ISNULL(CAST(b.launched AS BIGINT),0)) AS quantityPrepared,
SUM(ISNULL(CAST(b.bounced AS BIGINT),0)) AS bounced,
SUM(ISNULL(CAST(b.delivered AS BIGINT),0)) AS delivered
FROM dimension tt
INNER HASH JOIN batch b ON (b.batch_id = tt.batch_id)
WHERE tt.datelaunched >= '11/19/2007'
AND tt.datelaunched < '11/20/2007'
AND tt.id = 1
GROUP BY b.name WITH ROLLUP
ORDER BY SUM(ISNULL(CAST(b.launched AS BIGINT),0)) DESC
)
SELECT
name,
quantityPrepared
bounced,
delivered
FROM CTE_name
ORDER BY 2
View 1 Replies
View Related
Mar 12, 2007
Hi,
I've searched quite a bit for help with this syntax but have given up.
I need help with the where clause of a query using SQL SERVER that selects records a certain number of days before the current date. I have tried this and it's incorrect syntax:
WHERE (fldDate < ({ fn NOW() - 500 })
Can someone please help me out with correct syntax for this? thanks much.
View 3 Replies
View Related
Apr 4, 2014
I have an issue with Delete statement.In the code given below (its a part of actual proc),if we use TRUNCATE to clean the temp tables, everything goes fine.But if I use DELETE in place of truncate, system skips the IF loop 'if (@script_type = 1 OR @script_type = 2)'I am not able to understand this behavioral difference between DELETE and TRUNCATE.Recently the database is being used for replication, but that should not be a reason.
SELECT @max_rows = COUNT('X') FROM #temp_table1
SET@row_cnt = 1
WHILE @row_cnt <= @max_rows
BEGIN
[code],...
View 2 Replies
View Related
May 19, 2014
I have a table with a list of jobs along with their start and end datetime values.
I am looking for a function which will return the time taken to process a job using a start date and an end date. If the date range covers a Saturday or Sunday I want the time to ignore the weekends.
Example
Start Date=2014-05-15 12:00:00.000
End Date=2014-05-19 13:00:00.000
Total Time should be: 2 Days, 1 Hour and 0 Minutes
View 5 Replies
View Related
Sep 4, 2015
I have a table with appdt as first appointment date and the another record for the same customer# has follow up appointment.
Each customer is uniquely identified by a customer#
I need to find out if the customer came back after 200 days or more when the first appointment date was between jan12014 and Aug 31 2014. I am only interested in first follow up appointment after 30 days or more.
How can i do that in a query?
View 5 Replies
View Related
Dec 31, 2013
I have a request where i would like to get the start date/time and end date/time and flag (with an int) which hours (24 hour clock) have values between the two dates. Example car comes into service on 2013-12-25 at 0800 and leaves 2013-12-25 at 1400 the difference is 6 hours and i need my table to show
Column: Hour_6 Value: 0
Column: Hour_7 Value: 0
Column: Hour_8 Value: 1
Column: Hour_9 Value: 1
Column: Hour_10 Value: 1
Column: Hour_11 Value: 1
Column: Hour_12 Value: 1
Column: Hour_13 Value: 1
Column: Hour_14 Value: 0
As i'm working away at it i'm trying to figure out how i could use a Time Dimension table for this but dont really see much. So far i have the difference between the two times in hours (hour_diff) and the start hour (min_hour) so i would like to do something where i update the first hour (min_hour) and update columns based on the numbers of hours (hour_diff)
View 9 Replies
View Related
Mar 17, 2015
How do I find the time difference when the dates are in one column? I need to find hours and minutes between each row.
CREATE TABLE #Time ([TimeStamp] DATETIME, TimeDiff INT)
INSERT INTO #Time (TimeStamp)
VALUES ('2014-09-02 07:51:02.810'), ('2014-09-02 07:48:09.567'), ('2014-09-02 08:37:09.647')
, ('2014-09-02 16:16:42.593'), ('2014-09-02 08:06:13.387'),('2014-09-02 14:32:00.113')
DROP TABLE #Time
View 6 Replies
View Related
Apr 27, 2015
using below query to compare columns in two tables.
SELECT
Col1 = ISNULL(a.name,b.name),
Col2 =
CASE
WHEN ISNULL(a.name,'') = '' THEN 'Table B'
WHEN ISNULL(b.name,'') = '' THEN 'Table A'
[code]...
How to export the result to EXCEL from SQL Server 2008 R2.
View 7 Replies
View Related
