Subtract Values From Different Rows Within Query
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
ADVERTISEMENT
Sep 10, 2007
Hi all,
I would like have your help about a query.
In fact, I have a query to retrieve the rows for specific ID.
Like that:
SELECT *
FROM TblUser u
WHERE EXISTS
(
SELECT *
FROM TblScore s
WHERE s.FKIDUser = PKIDUser
)
With this query, I retrieve all users for which ones there are some scores.
Now, I need to get only users with specific score.
In the table TblScore, there is a column ScoreValue.
This column contains a value between 1 and 15
I would like to retrieve the users having score equal to 2,4 and 6
I could add a where clause like that: "and scorevalue in (2,4,6)"
But I want only users having these and only these scores, not less, not more.
So if an user has the following scores: 2,4,6,8, I don't want to retrieve it
If an user has the following scores: 2;4, I don't want to retrieve it.
If an user has the following scores: 2,4,6, I want it.
Someboy would have an idea at my problem ?
Thanks in advance
Jerome
View 7 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
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
View Related
May 8, 2008
I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.
VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).
The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.
I also would like to add two derived variables.
1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.
2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.
My report should look like:
VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState
1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE
1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE
Can someone please help me here?
Thanks,
Romakanta
View 1 Replies
View Related
Mar 25, 2002
I'm stuck. I have a table that I want to pull some info from that I don''t know how to.
There are two colomuns, one is the call_id column which is not unique and the other is the call_status column which again is not unique. The call_status column can have several values, they are ('1 NEW','3 3RD RESPONDED','7 3RD RESOLVED','6 PENDING','3 SEC RESPONDED','7 SEC RESOLVED').
i.e example, this is the existing data.
Call_id Call_Status
555555 3 3RD RESPONDED
235252 7 SEC RESOLVED
555555 7 3RD RESOLVED
325252 6 PENDING
555555 6 PENDING
325235 3 SEC RESPONDED
555555 1 NEW
This is the data I want...
Call_id Call_Status
555555 3 3RD RESPONDED
555555 6 PENDING
555555 7 3RD RESOLVED
The call_id could be any number, I only want the 6 PENDING rows where there are other rows for that call_id which have either 3 3RD RESPONDED or 7 3RD RESOLVED. If someone knows how it would be a great help.
Cheers,
Chris
View 1 Replies
View Related
May 10, 2015
Here is some data that will explain what I want to do:
Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456
Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456
View 4 Replies
View Related
Jan 25, 2008
I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?
-- Ryan
View 7 Replies
View Related
Nov 8, 2007
Hi All,
I am using sql server 2005. I stuck out in a strange problem.
I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468.
But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this.
There are indexes in the tables.
Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving.
But nothing is improving
View 7 Replies
View Related
Jun 25, 2015
I have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.
View 28 Replies
View Related
Feb 5, 2007
Hi guys
I have a column with datas as
"0.0"
"123.0"
"45.0"
i have a query were i have to match the value only. i don't want the decimal and followed by the any thing.
or
how do i extract just the value not the decimal part so that i can compare it with other value.
how do i do that.
thanks
View 10 Replies
View Related
Dec 10, 2007
I have a View in SQL,
SELECT dbo.ATTTblAttendance.LogIn, dbo.ATTTblAttendance.LogOut,
ISNULL(DATEDIFF(Hour, dbo.ATTTblAttendance.LogIn, dbo.ATTTblAttendance.LogOut),0) AS TimeWorked
FROM dbo.ATTTblAttendance
Currently, when Login = 1:00 PM and LogOut = 4:30 PM, TimeWorked = 3.
What I need is for TimeWorked to = 3.5
My ASP.NET page has the following:
<asp:TemplateField HeaderText ="Total Hours" >
<ItemTemplate >
<asp:Label ID="TimeWorked" runat="server" Text='<%# Eval("TimeWorked") %>' ></asp:Label>
</ItemTemplate>
</asp:TemplateField>
The goal is to take a person's dailt time in and time out, and subtract their AM and PM break time to yield their TimeWorked.
Ex. time in > 8:00 AM,
lunch > :30 minutes
time out > 4:30 PM
Total Time Worked today = 8.0 or 7.45, or 7.5, if the time warrant it.
How I get this to subtract correctly?
Ayomide
View 3 Replies
View Related
Jun 24, 2004
Hello everyone. I have a query where I pull all item_codes that start with a 7. I need all the item_codes that start with 7, but I need to subtract the last 3 characters from only the item_codes that are not 71860kit or 71851nggun. How would I be able to accomplish this. What I have below subtracts the last 3 characters from all item_codes including 71860kit and 71851nggun. I want these two to stay in tact when I select them in the query.
This is the Query that I have now!
select datepart(year, date_cust_invoice)as INV_YEAR,
datepart(month, date_cust_invoice)as INV_MONTH,
item_code=left(item_code, len(item_code)-3)
, sum(QTY_SALES)as QTY_SALES_TOTALS, sum(SALES_VAL)as SALES_VAL_TOTALS
from opcsahf
where datepart(year, date_cust_invoice) >= '2003'
and substring(item_code, 1, 1) = '7'
group by datepart(year, date_cust_invoice), datepart(month, date_cust_invoice),
item_code
order by inv_year, inv_month, item_code
All help is appreciated!
View 1 Replies
View Related
Nov 5, 2007
Hi,I am trying to solve the following issue, can anybody throw some lights...Here is some sample data from the tableTIME STAMP PROJECTID FUNCTION2007-10-31-01.10.05.00 3333311111 First2007-10-31-01.10.06.00 3333311111 Second2007-10-31-01.10.08.00 3333311111 Third2007-10-31-01.10.10.00 3333311111 Complete2007-10-31-01.10.11.00 2222244444 First2007-10-31-01.10.12.00 2222244444 Second2007-10-31-01.10.14.00 2222244444 Third2007-10-31-01.10.15.00 2222244444 Complete... and result I am looking something like below PROJECT ID FUNCTION DURATION3333311111 First 0:00:01 (2007-10-31-01.10.06.00 - 2007-10-31-01.10.05.00) 3333311111 Second 0:00:06 (2007-10-31-01.10.08.00 - 2007-10-31-01.10.06.00)3333311111 Third 0:00:02 (2007-10-31-01.10.10.00 - 2007-10-31-01.10.08.00)2222244444 First 0:00:01 (2007-10-31-01.10.12.00 - 2007-10-31-01.10.11.00)2222244444 Second 0:00:02 (2007-10-31-01.10.14.00 - 2007-10-31-01.10.12.00)2222244444 Third 0:00:01 (2007-10-31-01.10.15.00 - 2007-10-31-01.10.14.00)hope the question is clear!thanks for your time, Murali
View 11 Replies
View Related
Oct 18, 2007
I want to fetch all the records which are 2 minutes older
How can I do it?
View 2 Replies
View Related
Sep 8, 2013
select Doctor, count(monthyear)- count(paidinfull) as Patients
from tableA
where paidinfull = 'YES'
group by doctor
So I have a table like
Doctors Patients
------ --------
Burns 12
White 7
where Doctors is a group by from tableA and Patients is a result of the row count of paidinfull = yes minus the rowcount of monthyear.
View 5 Replies
View Related
Sep 30, 2013
I have data for moths which are commulative, for example Feb where month is 02, ecul Jan. + Feb. data. I would like to have the data for only Feb = when month '02' Feb = data for '02' - data for '01'. I would like to writ these commands: For Jan there is no problem to write the command but for other months I am not sure how to write the commands for months other than Jan.
,Jan_qty_1 = case
when [stat_month] = '01' then cast(sum(cast([qty_1_mo] as bigint)) as bigint)
else CAST('0' as int)
end
[Code] ....
here is the data:
ust_code district stat_year stat_monthqty_1_mo
24105220070112619
2410522007022200
2410522007024438
2410522007030
24105220070314434
2410522007042295
2410522007040
2410522007050
2410522007050
241052200706454
View 3 Replies
View Related
Dec 2, 2014
How to subtract days from the given format...
format : Mon, 03 Nov 2014 14:10:00 GMT
and it is in varchar(50) under dates column the data is inserted
View 1 Replies
View Related
Oct 1, 2006
Hi all,
I need help writing a query that will subtract the values of 2 rows from the same column to display in the result set. Some background information: a table has a sales column that keeps track of sales by the minute, but this is done in a cumulative manner, i.e, sales at row 3(minute 3) = sales recorded @ minute 2 plus sales @ minute 3. Therefor to get the actual sale at min 3, i would have subtract value at row 2 from row 3. make sense? it sounds very easy but I am having a hard time refering back to the previous row and am dealing with more than 1000 rows. i thought about doing a self join on the table but could not get it to do what i want.
would appreciate any help i can get. thanks
View 5 Replies
View Related
Oct 13, 2006
Hi,
I have an implementation of the UDT - 3-dimentional vector. In my code I have implemented add, subtract and multiply methods for the type. I have also implemented overloaded operators for +/-/* in my C# code. Those overloaded operator are working as expected in C# tests. However when I€™m trying to use +/-/* operators in T-SQL over my UDT it returns the following error:
Invalid operator for data type. Operator equals add, type equals Vector.
The following fragment does work:
DECLARE @v1 Vector, @v2 Vector, @v3 Vector;
SELECT @v1 = CAST('1,1,1' as Vector), @v2 = CAST('2,2,2' as Vector)
SELECT @v1 'v1', @v2 'v2', @v1.[Add](@v2) 'v1 + v2'
And this fragment does not work:
DECLARE @v1 Vector, @v2 Vector, @v3 Vector;
SELECT @v1 = CAST('1,1,1' as Vector), @v2 = CAST('2,2,2' as Vector)
SELECT @v1 'v1', @v2 'v2', @v1+@v2 'v1 + v2'
I guess that SQL Server is not aware of the operators€™ overload I have implemented in the C# code. Is there any way to instruct SQL Server to use overloaded operators in the T-SQL so the code will look naturally @a + @b instead of @a.[Add](@b) and as a result use standard summary functions SUM() instead of writing user defined aggregate function for the Vector type field?
Maxim
View 4 Replies
View Related
Jun 9, 2008
Hi guys,
I have a table like this.
Declare @tbl as table(Invoiceid int,Dc varchar(100))
Insert into @tbl
Select 1,'101,102,104'union all
Select 2,'105,106,107' union all
Select 3,'110,111,112,113,114' union all
Select 4 ,'115'
I want to split the values in the column Dc.The output should be
Invoiceid Dc
1--------- 101
1--------- 102
1--------- 104
2--------- 105
2--------- 106
2--------- 107
... and so on.
Thanks for any help.
View 3 Replies
View Related
Nov 17, 2005
Hi
i have this variable called order1 in the table called datetable and i want to display out the duplicate orders in sql. how do i do that? do i need to use group by ?
thanks in advance.! any help?
well i actually get the answers by doin this...
select order1 from datetable group by order1 having count(*) > 1
however now i want to compare the values date1 in the first duplicate orders to the second duplicate order...like compare aaaa of the first row and aaaa of the second row ...how to do that? i need to compare the date1 in minutes..i only wan duplicate orders with minutes difference less than 30mins
eg
id Order1 date1
1 aaaaa 20051001 12:45
2 aaaaa 20051001 12:10
3 bbbbb 20031002 15:05
4 bbbbb 20031002 15:50
View 9 Replies
View Related
Jul 30, 2007
Hi
I€™ve a table like this (in SQL Server 2000).
v1 v2
2 8
7 10
11 15
13 17
v1 value of a row must be grater than v2 value of previous row, other wise I need to display that row, in above example, I need to display 2nd & 4th rows.
please advise
Thanks
View 9 Replies
View Related
May 10, 2007
Hi ,
I have a situation where i need to concatnate values from different rows and store it a one string.
sample
dealid date
1 1/5/2007
1 2/4/2009
2 5/5/2004
2 8/5/2006
2 4/8/2006
so for one particular deal how many ever dates there are , i need to concatnate them all separated by a comma(,) and return and one string.
Is ther any way i could do it, Any suggestions appreaciated
Thanks
Ashish
View 4 Replies
View Related
Feb 1, 2008
Good morning all,
I have a table from which I need to select every nine values in one column and insert them into another table as a row with nine different columns. For example, I need:
WERT
1A08
2A08
3A08
56999
25555
25666
12444
55699
55698
To become:
SN1 SN2 SN3 GRN1 GRN2 GRN3 GRN4 GRN5 GRN6
1A08 2A08 3A08 56999 25555 25666 12444 55699 55698
And so on and so forth for the every nine values in the column from the first table. Can somebody please help me with the correct T-SQL query to accomplish this please?
I may also need to do the same thing for other tables where I may have fifteen or so values to be extracted into a different table as rows.
Thanks in advance,
Paul
View 8 Replies
View Related
Mar 29, 2008
Hi Guys,
Does anyone have a good example how to subtract an amount from an sql table?
At present i have a table such as this
id
View 2 Replies
View Related
Sep 15, 1999
Can any one tell me how to subtracts and add hours to the current date and time?
In my case I have to store the Vancouver date and time in Toronto.
Thanks,
T.Lingam
View 1 Replies
View Related
Jan 12, 2005
Hey all, how do you take the current date and subtract a year from it in a SP?
What I want to do is...
Take the current date when the SP is ran, subtract a year, then if my date field is within that range (higher than the date with the subtracted year) it will continue in the query.
Edit: the answer is..
dateadd(yyyy, -1, getdate()) as Date1
View 6 Replies
View Related
Mar 4, 2008
i get an error whan i do this
Code Snippet
SELECT DATEADD(day, -7, StartDate)AS [sevenDayAgo]
FROM dbo.empList
Msg 517, Level 16, State 2, Line 1
Adding a value to a 'smalldatetime' column caused overflow.
my field StartDate is smalldatetime
TNX
View 5 Replies
View Related
Jun 3, 2006
I am building a website in asp.net 1.1 with vb.net 2003 which will have the standings of the teams in our baseball league. Below is the database table I have created.
ID(int) home_team (nvarchar) away_team(nvarchar) win_teampf(nvarchar) lose_teampf(nvarchar)
1 Elmwood Murdock 7 22 Louisville Manley 4 33 Manley Elmwood 9 8
ID is the primary key. What I am attempting to do is add each instance of Elmwood from the win column to output the total number of wins from Elmwood and do the same for Elmwood in the losing team to output the total number of losses. The result will look something like this:
Elmwood: 1 Win 1 Loss. .500
Thanks for your reply.
View 5 Replies
View Related
Jun 29, 2012
I've been working with T-SQL in a MSSQL Server Management Studio (2005) for about a week now. I've been trying to convert some horribly written VB code from a MS Access DB over to SQL so it can be automated on a SQL backend.
Most of the learning process and coding has gone surprisingly well. The problem is with comparing some data to determine which one needs to be flagged.
Three tables to note in bold, with notable fields in italics below them:
EmployeeData
HRID (identity)
ResourceAllocation
ID (identity)
[Last Name] (linked to HRID)
Project
[Resource Start Date]
[Resource End Date]
[Percent Utilization]
tblHCvalues
RAID (linked to ResourceAllocation.ID)
a monthyear and quarteryear for every month and quarter from 2012-2014. IE january12, february12, 1q12, 2q13, etc...
And yes, there are probably a thousand ways to optimize that tblHCvalues, but I'll ask about that later. Just work with the structure I have
Here's how it works: Each employee's data and unique HRID is in the EmployeeData tableAn employee can be on one or multiple projects at any timeThose projects are stored per project in the ResourceAllocation table with a link to the Employee's HRID, and all the other information listed aboveEven though an employee might be on two projects, they can only count for headcount on one project.
We use rules that compare the percent of work being done on a project, and the start and end dates of the employee (resource) on that project to determine which project should be counted for Headcount. The code uses a cursor to go through each HRID, and then pull up all the ResourceAllocation records associated with it.Run the rules to determine which ResourceAllocation record counts toward headcountA stored procedure then runs that fills out the tblHCvalues in the way we want for the project we want
All of it works, except for the rules that compare the things, so that's what I want to focus on in this thread. How do I write these rules:
Here are the rules, and they should work for any number of multiple resource allocations for one employee:
Choose the ResourceAllocation with the greatest [Percent Utilization]If the top ResourceAllocations have equal [Percent Utilization], choose the ResourceAllocation with the earliest [Resource Start Date]If the [Percent Utilization] and the [Resource Start Date] are equal, choose the latest [Resource End Date]If all three fields are equal, choose the first ResourceAllocation (aka, screw it and pick one at random)
I'm sure I could use a bunch of IF statements to compare it all, but even that is complicated to think about. There has to be an easier way, right?
View 6 Replies
View Related
May 8, 2008
I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.
VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).
The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.
I also would like to add two derived variables.
1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.
2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.
My report should look like:
VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState
1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE
1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE
Can someone please help me here?
Thanks,
Romakanta
View 2 Replies
View Related
Jun 5, 2014
I am trying to select all the data which are not associated with values older than a specific value.. For example I have:
paola -- 2
paola -- 3
paola -- 10
jessica -- 3
jessica -- 12
james -- 3
james -- 2
james -- 5
I want to query only those names, that are associated with numbers greater than 3. So, for this example, the result would be only Jessica. the others should be discarded.
View 4 Replies
View Related