Adapting Running Totals To Query
Jul 8, 2013
I'm trying to adapt a formula for running totals to my Query, have some difficulties in declaring the tables correctly.
Formula: SELECT Ordinal, QTY, QTY+COALESCE((SELECT SUM(QTY)
FROM Table b
WHERE b.Ordinal < a.Ordinal),0) AS RunningTotal
FROM Table a
ORDER BY Ordinal
Now my Table is rather lengthy and is described by this query:
Select X.Ord, X.QTY
From (Select.... )X
Now in order to adapt the formula for running totals I did:Select X.Ord, X.QTY, X.QTY+COALESCE((SELECT SUM(X.QTY)
FROM X b
WHERE b.Ord < a.Ord),0) as RunningTotal
From (Select....)X a
Order by X.Ord
It gives my an ncorrect syntax error near a. I guess I miss the (double) declaration of the Table.
View 4 Replies
ADVERTISEMENT
Nov 28, 2012
I have a sales tables which looks as below.
DEPARTMENT
Barnd_Name
Item_Group
   S_DATE
     S_AMOUNT
Administration
IBM
[code]....
Now i need Month Wise Running Totals.but i should check the following group as show below i that order
1) DEPARTMENT
1) Brand
3) Item Group
4) Month
View 12 Replies
View Related
Oct 1, 1999
I have a situation where I want to get running totals of a potentionally very large table. Say I have a million records with a table with few fields.
Table structure like this
UID value
1 3
2 9
3 4
4 7
5 2
I want to return a result set of something like this
select uid, value, (rtotal???) from table
uid value rtotal
1 3 3
2 9 12
3 4 16
4 7 23
5 2 25
This is to be used for sort of a lotery. Say I have 1 million users with a variety of points tward the lotery. I total the points, is say 5 million, determined
the winner to be say 3,234,563 Now I have to determine which uid is the winner. So I planned to to do a running total till the winning value
is less then or equal to the running total and I have my winner. I am looking for a fast way to do this on a large database.
Any Ideas?
View 1 Replies
View Related
Jan 12, 2004
Hi Folks,
I have a problem that I know that i should be able to code up but have drawn a blank due to it being monday. Anyway......
Have a table :
create table test_registrations
(
date_maint smalldatetime,
user_name1 varchar (255),
user_address1 varchar (255),
total_users int
)
go
If i have a number of registrations on a particular date then I can tell all how many users have registered in any date by :
select date_maint , count (1)
from test_registrations
group by date_maint
order by date_maint desc
go
The qestion is how can I keep a total registared users count. Say if I have 10 users join on the 1st of Jan and 15 on the 3rd then I want
the total users for the users on 1st to read 10 and total users on the 3rd to read 25.
I know i should be able to code this up but I'm being a dumb ass. Can someone show me a way to code it. Is it some sort of correlated sub query to keep a running total ?
View 3 Replies
View Related
May 23, 2008
On SQL Server 2005 at least, this works efficiently where we have an indexed row number.
It does seem to be very sensitive to the join condition in the recursive part of the CTE: changing it to the equivalent "ON T.rn - 1 = RT.rn" results in a scan of T each time instead of a seek!
DROP TABLE dbo.T
-- rn must have contiguous values for this to work:
CREATE TABLE dbo.T (rn int PRIMARY KEY, f float NOT NULL)
-- 100000 random floats between 0 and 1:
INSERT INTO dbo.T
SELECT n+1 AS rn, RAND(CAST(NEWID() AS binary(4))) AS f
FROM dbo.Numbers
GO
;WITH RT AS (
SELECT rn, f AS rt
FROM dbo.T
WHERE rn = 1
UNION ALL
SELECT T.rn, RT.rt + T.f
FROM RT
INNER JOIN dbo.T AS T
ON T.rn = RT.rn + 1
)
SELECT *
INTO dbo.TRT
FROM RT
--ORDER BY rn
OPTION (MAXRECURSION 0)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'Worktable'. Scan count 2, logical reads 600001, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'T'. Scan count 100000, logical reads 200002, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3500 ms, elapsed time = 3724 ms.
(100000 row(s) affected)
View 4 Replies
View Related
Jun 11, 2008
Now my code below brings everything i want it too, the problems comes is I need to get a running total of sales for each day. Currently it shows the sales for a store for each day and what there projections should be. I need a running total for each day so if you were to have todays date it would have the sum(sales) between today and the first or the month that im in. but still show what the total was on the 10th, 9th, and so on.
Declare @Brand as varchar(10)
DECLARE @StartDate datetime
Declare @EndDAte as Datetime
Set @Brand = 'business'
SELECT @StartDate=CAST('1/1/'+CAST(YEAR(GETDATE()) AS varchar(4)) AS datetime)
SET @EndDate =CAST('12/31/'+CAST(YEAR(GETDATE()) AS varchar(4)) AS datetime)
Select ttProjection.StoreID,S.StoreName , ttProjection.DailyProjection, ttProjection.DAYS, ISNULL(ttSales.Sales,0) as Sales
From
/**********Finds projection per day****************/
(Select StoreID, Projection, Projection/Cast(DaysInMonth as INT) as DailyProjection, DAYS
From
(Select StoreID, Projection as Projection,
Month, Day(DateAdd(m, 1,DateAdd(d,1 - Day(Month), Month))-1) As DaysInMonth
From Reporting.dbo.RetailSalesComparison_ProjectionsView
Where StoreID between 12000 and 12999
)ttTemp
Right Join
(SELECT DATEADD(dd,number,@StartDate) as DAYS
FROM
(
select number from master..spt_values
WHERE type='p'
union all
select number+256 from master..spt_values
WHERE type='p'
) as s
where DATEADD(dd,number,@StartDate)<=@EndDate)ttDays on Month(ttTemp.Month) = Month(ttDays.DAYS))ttProjection
Left Join
(Select Date, StoreID, Sum(Sales) as Sales
From Reporting.dbo.RetailSales_byStore_byDay
Group By Date, StoreID)ttSales
on ttProjection.StoreID = ttSales.StoreID
and ttProjection.DAYS = ttSales.Date
Inner Join DelSolNet2.dbo.Store S on ttProjection.StoreID = S.StoreID
Where Month(Days) = Month(getdate())
Order By Days, ttProjection.StoreID
View 3 Replies
View Related
Jan 19, 2007
We are trying to create a report that shows a Week to Date, Month to Date, and Year to Date
Week to Date Month to Date Year to Date
Item Number
I've tried using an if statement (if date = current week, Qty, 0) and then sum the data but I get an error message that reportitems can only be summed in the page header and footer. I've also tried running totals, but it doesn't like the iif statement. Any ideas on how to do this?
Nancy
View 6 Replies
View Related
Apr 8, 2008
I have a table for which I need a “special� running total. More specifically there should be a close communication between the credit and the debit column. The scenario should be in the following order
“Credit�“Debit�“Balance credit�“Balance debit�
6 0 6 0
5 0 11 0
0 4 7 0
0 9 0 2
3 0 1 0
0 5 0 4
0 2 0 6
I have to point out that this is done in a grouped form, where the CustormerID is the grouped clause. Now I have achieved this to a point where the calculations work only if the credit column is bigger than 0, but when the account starts with debit I get only sums of the column not the needed subtractions. What am I missing? Thanks in advance.
DECLARE @PrevRunBal MONEY --Overall running total
SET @PrevRunBal = 0
DECLARE @PrevGrpBal MONEY --Running total resets when account changes
SET @PrevGrpBal = 0
DECLARE @PrevRunCnt INT --Overall running count (ordinal rank)
SET @PrevRunCnt = 0
DECLARE @PrevGrpCnt INT --Running count resets when account changes
SET @PrevGrpCnt = 0
DECLARE @PrevAcctID INT --The "anchor" and "account change detector"
SET @PrevAcctID = 0
changes
SET @PrevGrpBalP = 0
update Temp
SET --===== Running Total
@PrevRunBal = RunBal = @PrevRunBal + dolguva,
-- @iznos =RunBal= dolguva - pobaruva + @sdol -@spob,
--===== Grouped Running Total (Reset when account changes)
@PrevGrpBal = Bcredit = CASE
WHEN CustomerID = @PrevAcctID
THEN CASE
WHEN CREDIt > DEBIT or CREDIT = 0
THEN (@PrevGrpBal+CREDIT)-(DEBIT)
WHEN DEBIT>CREDIT or DEBIT =0
THEN (@PrevGrpBal+DEBIT)-DEBIt
END
ELSE CASE
WHEN CREDIT >DEBIT
THEN (CREDIT)
WHEN DEBIT > CREDIT
THEN DEBIT -- restarts from 0 if only 1 rec.
END
END,
--===== Running Count (Ordinal Rank)
@PrevRunCnt = RunCnt = @PrevRunCnt + 1,
--===== Grouped Running Total (Ordinal Rank, Reset when account changes)
@PrevGrpCnt = GrpCnt = CASE
WHEN CustomerID = @PrevAcctID
THEN @PrevGrpCnt + 1
ELSE 1 -- Restarts count at "1"
END,
--===== "Anchor" and provides for "account change detection"
@PrevAcctID = CustomerID
Apologies if I'm posting in a wrong forum
Nik
View 2 Replies
View Related
Apr 22, 2008
Hi There,
I have the following query:
SELECT NATNLACCT, IDCUST, TEXTSNAM, AMTBALDUEH
FROM
VIEW_ARCUS
where amtbaldueh != .000
order by NATNLACCT
but i want to display the data something similar as below. How do I create a column to display with the natinlacct name(I have many) concatenated to Total and then sum amtbaldueh?
Natinlacct idcust textsnam amtbaldueh
Doda 1234 abcd $101
Doda 5678 efgh $200
Doda 9876 ijkl $300
Doda Total $601
Nava 5847 jhgf $230
Nava 5487 lfde $130
Nava 3587 lrsd $100
Nava Total $460
Thanks
Rhonda
View 5 Replies
View Related
Apr 8, 2008
I have a table for which I need a €œspecial€? running total. More specifically there should be a close communication between the credit and the debit column. The scenario should be in the following order
Credit
Debit
Balance credit
Balance debit
6
0
6
0
5
0
11
0
0
4
7
0
0
9
0
2
3
0
1
0
0
5
0
4
0
2
0
6
I have to point out that this is done in a grouped way where the CustormerID is the grouped clause. Now I have achieved this to a point where the calculations work only if the credit column is bigger than 0, but when the account starts with debit I get only sums of the column not the needed subtractions. What am I missing? Thanks in advance.
DECLARE @PrevRunBal MONEY --Overall running total
SET @PrevRunBal = 0
DECLARE @PrevGrpBal MONEY --Running total resets when account changes
SET @PrevGrpBal = 0
DECLARE @PrevRunCnt INT --Overall running count (ordinal rank)
SET @PrevRunCnt = 0
DECLARE @PrevGrpCnt INT --Running count resets when account changes
SET @PrevGrpCnt = 0
DECLARE @PrevAcctID INT --The "anchor" and "account change detector"
SET @PrevAcctID = 0
changes
SET @PrevGrpBalP = 0
update Temp
SET --===== Running Total
@PrevRunBal = RunBal = @PrevRunBal + Credit,
--===== Grouped Running Total (Reset when account changes)
@PrevGrpBal = Bcredit = CASE
WHEN CustomerID = @PrevAcctID
THEN CASE
WHEN CREDIt > DEBIT or CREDIT = 0
THEN (@PrevGrpBal+CREDIT)-(DEBIT)
WHEN DEBIT>CREDIT or DEBIT =0
THEN (@PrevGrpBal+DEBIT)-DEBIt
END
ELSE CASE
WHEN CREDIT >DEBIT
THEN (CREDIT)
WHEN DEBIT > CREDIT
THEN DEBIT -- restarts from 0 if only 1 rec.
END
END,
--===== Running Count (Ordinal Rank)
@PrevRunCnt = RunCnt = @PrevRunCnt + 1,
--===== Grouped Running Total (Ordinal Rank, Reset when account changes)
@PrevGrpCnt = GrpCnt = CASE
WHEN CustomerID = @PrevAcctID
THEN @PrevGrpCnt + 1
ELSE 1 -- Restarts count at "1"
END,
--===== "Anchor" and provides for "account change detection"
@PrevAcctID = CustomerID
View 13 Replies
View Related
Jul 13, 2006
Hi I have a table with the following structure:
UserID__DateJoined
I want to produce a query that shows on a month/year bases the number of users joined for each month AND show a running total of membership, I'm almost there....just got a problem getting the running totals:
this is what I have so far:
SELECT DATEPART(MONTH, DateJoined) AS Month, DATEPART(YEAR, DateJoined) AS Year, COUNT(UserID) AS NuAccounts
FROM Accounts_Users
GROUP BY DATEPART(MONTH, DateJoined), DATEPART(YEAR, DateJoined)
ORDER BY DATEPART(YEAR, DateJoined), DATEPART(MONTH, DateJoined)
How do I produce a column that shows the running total of accounts?
thanks for any help
View 6 Replies
View Related
Sep 14, 2015
I have a ms-sql 2012 for this task. I read that running totals / sum over is better supported here.
There are customers with an account where they can insert or withdraw cash. When they insert, the inserted amount will get a date where it expires. After the expiration date, the cash becomes unavailable.
I'm not sure how to design the tables to meet my needs, but this is my best guess. And below is the first problem in the view.
Table: deposit    This table will hold all deposits
customerId    (string)
balanceType    (string)
transactionDate   (date)Â
expiresOnDate   (date)
amount     (decimal)
transactionText   (string)
The data set for the deposit table could be:
1 Bonus 01-05-2015 30-04-2016  500  Inserted 500
2 Bonus 01-05-2015 30-04-2016  500  Inserted 500
3Â Bonus2Â 01-01-2015Â 31-12-2015Â Â 100Â Â Inserted 100
2Â Bonus2Â 01-01-2015Â 31-12-2015Â Â 100Â Â Inserted 100
Table: withdrawal  This table will hold all withdrawals
customerId    (string)
balanceType    (string)
transactionDate   (date)Â
amount     (decimal)
transactionText   (string)
The data set for the withdrawal table could be:
2Â Bonus2Â 01-04-2015Â -100Â Â Needed 100
2Â Bonus2Â 02-04-2015Â -100Â Â Needed 100
2Â Bonus2Â 03-01-2015Â -100Â Â Needed 100
3Â Bonus2Â 10-04-2015Â -50Â Â Â Took out 50
3Â Bonus2Â 11-04-2015Â -100Â Â Took out 100
[Code] .....
Now I need to combine the two tables in a view with a running total, ordered by customerId, balanceType and transactionDate
customerId
balanceType
transactionDate
amount
Total
Text
The view must show this, when selecting all records:
1 Bonus 01-05-2015 500  500  Inserted 500
1 Bonus 01-07-2015 -100 400  Yes, got 100
1 Bonus 02-07-2015 -100 300  Yes, got 100
1 Bonus 03-07-2015 -100 200  Yes, got 100
[Code] ....
And a last view that will show distinct totals for each customer and balanceType
customerId
balanceType
totalBalance
1 Bonus -200
2 Bonus -300
2Â Bonus2Â -200
3Â Bonus2Â -550
View 8 Replies
View Related
May 28, 2015
A while back, a "quirky update" method was proposed for lightning fast running totals based on the three-part MSSQL UPDATE's SET statement and tally tables. However, some claimed this was not 100% absolutely guaranteed behavior.
How does the new OVER clause compare in terms of performance ?
DECLARE @Tbl TABLE
(
pk int not null primary key identity,
N int
)
INSERT INTO @Tbl (N) SELECT TOP 1000 1 FROM syscolumns a CROSS JOIN syscolumns b
SELECT pk, SUM(pk) OVER (ORDER BY pk )
FROM @Tbl
View 9 Replies
View Related
May 13, 2015
get the desired results for the following sample data set. I was able to come up with a query that returns the the expected results however only for a given day, so I'd need to union several select statements the get the desired results which is definitely not ideal. I'd like to pass a parameter in (number of days) instead of doing a unions for each select.
DECLARE @T TABLE (Id INT, Category VARCHAR(1), [Date] DATE)
INSERT INTO @T
SELECT 1 AS Id, 'A' AS Category, '2015-5-13' AS ActivationDate UNION ALL
SELECT 1, 'A', NULL UNION ALL
SELECT 1, 'A', '2015-5-13' UNION ALL
SELECT 1, 'A', NULL UNION ALL
[code]....
View 9 Replies
View Related
Sep 23, 2015
I have table named #t1 and three columns. CODE, Column1, and Column2.
create table #t1 (
CODE NVARCHAR(20),
COLUMN1 NUMERIC(18,2),
COLUMN2 NUMERIC(18,2)
)
And i have some data:
INSERT INTO #t1 (CODE,COLUMN1,COLUMN2)
VALUES ('432', 0,100),
('TOTAL FOR 432',0,100),
('4320001',0,250),
('4320001',50,0),
('4320001',0,140),
('4320001',300,0),
('TOTAL FOR 4320001',350,390),
('432002',200,0),
('432002',0,100),
('TOTAL FOR 432002',200,100)
drop table #t1
I want to have 4 column (named BALANCE). Balance must be column that represent running totals between two columns (Column1 - Column2) for each group of data. For each group total must start from zero.after total 432 it starts to count again for total 4320001 and again for total 432002. I'm using MS SQL SERVER 2014.
View 9 Replies
View Related
Mar 9, 2006
I've created a Report Builder Column chart with months as the x-axis
(category) and revenue as the y-axis (value). This correctly displays
the revenue for each month, but I'd like to display a running total of
the revenue for the year.
I'm currently displaying:
Jan $2
Feb $1
Mar $4
I want to display:
Jan $2
Feb $3
Mar $7
I know this is possibly using the RunningTotal formula in Reporting
Services, but I'd like to do this in Report Builder so the users can
change and create their own graphs with running totals. Is there a way to do this in Report Builder?
Thanks.
View 1 Replies
View Related
Oct 10, 2014
For my SSRS 2012 report I've created a dataset to extract the TOP 20 amounts.
I've create a stacked column chart to put these amounts as a values.
But in the chart I can see only ten amounts and not all 20 ones.
20 is a fixed number.
How can I adjust the number of columns in the chart in order to show all 20 amounts?
View 0 Replies
View Related
May 9, 2015
I have some data grouped in a table by a certain criteria, and for each group it is computed a subtotal for the group. Of the values from each of the group, I want to create a grand total on the report by adding every subtotal from each group.
Example:
...
....
Group1 Â Â Â Â Â Â Â Value
              10
              20
Sub Total 1: Â Â Â Â 30
Group2 Â Â Â Â Â Â Â Â Value
               15
               25
Sub Total 2: Â Â Â Â Â 40
Now, I would like to be able to add subtotal 1 (30) to subtotal 2 (40) and my grand total would be 70. Can I accomplish this task in SSRS?
View 5 Replies
View Related
Jul 20, 2005
I've got some SQL that works as far as returning a recordset from a series ofUNION statements.viz:SELECT whateverUNION thisUNION thatUNION otherNow I want to group and sum on it's results.Started out tying:SELECT * FROM(union stuff)....but couldn't even get past the syntax check.Where I'm headed is a sort of pivot table presentation of some hours dataassociated with various projects with a column for each of six date ranges.Bottom line: can somebody give me a pointer to the syntax needed to wrap thoseUNION statements and then select/group/sum their results?--PeteCresswell
View 9 Replies
View Related
Oct 2, 2006
I have been providing sales data for a few months now from a table that is set up like this:
Date WorkDay GasSales EquipmentSales
9/1/2006 1 100.00 200.00
9/4/2006 2 50.00 45.00
etc.
As can be seen, the data is daily, i.e., on the first workday of September we sold one hundred dollars in gas and two hundred dollars in equipment. On the second workday of September we sold fifty dollars in gas and forty-five dollars in equipment.
Now, however, the data I have to pull from is cumulative. So, using the last table as an example it would look like this:
Date_WorkDay_GasSales_EquipmentSales
9/1/2006 1 100.00 200.00
9/4/2006 2 150.00 245.00
etc.
To make things more complicated, the powers that be wanted this data presented in this fashion:
Total Sales:
1_2_etc.
300.00 95.00 etc.
So, I have been doing a pivot on a CRT to get the data to look like I want. The code is like this:
with SalesCTE (Month, WorkDay, [Total Sales])
as
(
SELECT
datename(month, cag.date),
cag.WorkDay AS [Work Day],
sum(cag.sales_gas + cag.sales_hgs) AS [Total Sales]
FROM CAG INNER JOIN
Branch ON CAG.[Oracle Branch] = Branch.OracleBranch
group by cag.date, cag.WorkDay
)
select * from SalesCTE
pivot
(
sum([Total Sales])
for WorkDay
in ([1],[2],[3],[4],[5],,[7],,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])
) as p
So, my question is:
How do I get the data to give back daily totals instead of the cumulative amounts for each workday? If the query was a simple one, I'd do something like
select [1] as [Day 1], [2]-[1] as [Day 2], [3]-[2] as [Day 3], etc.
but the query is far from normal, with the CRT and the pivot. I can't seem to get it to work how I'd like.
Any advice/answers? Thanks in advance!!!
P.S. I don't know how to get it to quit with the freakin' smileys.... I suppose you can figure out what my code is really supposed to look like above. Needless to say, it doesn't include a devil face and a damn music note...
View 12 Replies
View Related
Feb 26, 2008
Been trying to wrap my head around this one, I'm sure its quite possible but can't figure it.I've got a massive table of Items, each of which has a category. I want to bring back a list of categories and a total number of items in each category, so a result set which would look like:Category NoOfItemsInCategory--------------------------------------------------Category1 15Category2 1287Category3 25How would I write this query? I've been messing around with Group By and Distinct but can't get the effect I'm wanting.Thanks!
View 1 Replies
View Related
Jun 5, 2007
Hi all,
I hope someone can help me with this problem, cos I'm a bit lost.
We have a system and its needs to check whether an employee is assigned to a Group or not. With one table, its not a problem.
Select Count(CardID) from Table Where GroupID is Null.
Now we created a different table for different Employees.
I basically need to do the following
Select Count(CardID) as HRCount from Table1 Where etc, etc
Select Count(CardID) as FPCount From Table2 Where etc, etc
However I need ONE total, if possible at once.
There is no relation between the tables.
To SUM it up, I need ONE total from TWO separate tables.
Is it possible?
Thanks and Regards
Claudio
View 2 Replies
View Related
Aug 28, 2015
This is a query that produces a table with garbage data, but (I think) will get the point across of what I need. SQL Server 2008
Create Table SanitationGarbage
(
saleid int
,projectname varchar(200)
,typeofsale varchar(200)
[code]....
Now my select query below does not group the data like I need it to, nor does it show a total row like I need it to. How does this need to be written so the data is displayed in the proper formatting?
Select
projectname
,Count(saleID) As [Total Sales]
,Count(case when typeofsale = 'Final' then saleID else null end) As [Final Sales]
,Count(case when typeofsale = 'Pending' then saleID else null end) As [Pending Sales]
FROM SanitationGarbage
GROUP BY projectname
order by projectname asc
[code]....
View 4 Replies
View Related
Jun 29, 2007
First off, here is the application.
An airport baggage handling system distributes bags using multiple conveyors.
Bag counts are logged every 15 minutes. There is a count for each conveyor.
Example Log Table layout is as follows (The TIME column is DateTime, the Convx columns are TinyInt)
Time Conv1 Conv2 Conv3 Conv4 Conv5 Conv6
i 3 2 3 4 2 1
i+15min 2 3 4 2 2 2
i+30min etc.....................
i+45min
i+60min
etc...
The management team wants a throughput report which will take the following parameters in order to filter the results:
Begin Date
End Date
Time Interval (selectable as 15mins, 30 mins, 45mins, 60 mins and Daily)
My question is this. Given that my raw data has 1 row for every 15 minutes, if they select 60 minutes as their interval I need to run the query with the start and end dates but Sum every 4 rows and display it as 1 row, likewise if they select 30 minute interval, I need to sum every 2 rows. How do I run a query and SUM the Conv count data for every x number of rows and use the 1st TIME value in the returned x row summary?
Thanks for your help and let me know if I need to clarify anything
View 3 Replies
View Related
Sep 1, 2006
If I start a long running query running on a background thread is there a way to abort the query so that it does not continue running on SQL server?
The query would be running on SQL Server 2005 from a Windows form application using the Background worker component. So the query would have been started from the background workers DoWork event using ado.net. If the user clicks an abort button in the UI I would want the query to die so that it does not continue to use sql server resources.
Is there a way to do this?
Thanks
View 1 Replies
View Related
Apr 9, 2006
I hope I am not asking about something that has been done before, but Ihave searched and cannot find an answer. What I am trying to do is torun a query, and then perform some logic on the rowcount and thenpossibly display the result of the query. I know it can be done withADO, but I need to do it in Query Analyzer. The query looks like this:select Varfrom DBwhere SomeCriteriaif @@Rowcount = 0select 'n/a'else if @@Rowcount = 1select -- this is the part where I need to redisplay the resultfrom the above queryelse if @@Rowcount > 1-- do something elseThe reason that I want to do it without re-running the query is that Iwant to minimize impact on the DB, and the reason that I can't useanother program is that I do not have a develpment environment where Ineed to run the queries. I would select the data into a temp table, butagain, I am concerned about impacting the DB. Any suggestions would begreatly appreciated. I am really hoping there is something as simple as@@resultset, or something to that effect.
View 6 Replies
View Related
Nov 19, 2004
I have an update query running which to just now has been running for 22 hours running on two tables 1 a lookuptable that has just been created within the batch the other a denormalised table for doing data analysis on
the query thats causing teh problem is
--//////////////////////////////////// this is the one thats running
Print 'Update Provider 04-05 EmAdmsCount12mths : ' + CAST(GETDATE() AS varchar)
GO
Update Provider_APC_2004_05
set EmAdmsCount12mths =
(Select COUNT(*)-1
from Combined_Admissions
where ((Combined_Admissions.NHSNumber = Provider_APC_2004_05.NHSNumber) or
(Combined_Admissions.PASNUMBER = Provider_APC_2004_05.PDDISTNO)) and
(Combined_Admissions.AdmDate BETWEEN DateAdd(yyyy,-1,Provider_APC_2004_05.AdmDate) AND Provider_APC_2004_05.AdmDate) AND
Combined_Admissions.AdmMethod like 'Emergency%')-- and
-- CA.NHSorPrivate = 'NHS'))
FROM Provider_APC_2004_05, Combined_Admissions
any help in improving speed would be most welcome as there are 3 more of these updates to run right after this one and the analysis tables are almost double the size of this one
Dave
View 6 Replies
View Related
Jul 8, 2013
I have 2 requests for desperate Hélio..
1) is there any way to run a query over a query without having to create a table with the results of the first query? (would drop table work? If so, how?
2) how can i define input variables the same way i do in excel? I am trying to run a couple of simulations based on 2 core inputs (in excel i would just do a data table)
View 7 Replies
View Related
Apr 10, 2008
Hi guys. I'm looking for help to run a query that should come out looking like this
unit reports_to
Development Dept Representative Div
Systems Group Development Dept
Design Section Systems Group
Production Spec Section Systems Group
Proposal Section Systems Group
This is the table i have to work from
UNITNOORGNAME PARENTBUDGET
2000Representative Div1000459000.0000
2100Development Dept2000391000.0000
2110Systems Group 2100332000.0000
2111Proposal Section211087000.0000
2112Design Section 2110132000.0000
2115Production Spec Sect211068000.0000
I can't see any way of doing this query though I know the PARENT is the reports_to column and that the UNITNO is the Department but can't find any way to run the query. Hoping you guys can help
Thanks
View 10 Replies
View Related
Jun 20, 2007
Presumably, an RDL data set is only queried once before generating a report. And presumably, this is true even if the data set feeds a list control. However, I'd like to know if I can cause a data set to be queried repeatedly from a list control. I need to do this, because the data set contains aggregate functions that would be impacted by each "pass" the list control would make.
Is it possible to make a data set queried repeatedly in this fashion?
View 1 Replies
View Related
Aug 22, 2007
I have a pretty complex query that aggregates lots of data and inserts multiple rows of that data into a reporting table. When I call this SPROC from SQL Server Management Studio, it executes in under 3 seconds. When I try to execute the same SPROC using .NET's SqlCommand object the query runs indefinitely until the CommandTimeout is reached. Why would this SPROC behave differently with the same inputs, but being called from .NET? Thanks for your help!
View 3 Replies
View Related
Feb 7, 2003
When I execute the following stored procedure it runs for about a minute.
CREATE PROCEDURE EquipmentListByProduct
(
@iProdTypeId int
)
AS
SET NOCOUNT ON
DECLARE@iError int, @iRows int
SELECT pn.prodTypeId, pn.prodId, pn.prodName
FROM prodNames pn
WHERE pn.prodTypeId = @iProdTypeId
SELECT@iError = @@ERROR, @iRows = @@ROWCOUNT
IF ( @iError <> 0 )
BEGIN
RETURN@iError
END
IF ( @iRows = 0 )
BEGIN
RETURN-1
END
RETURN@iError
GO
The table only has 22 records.
Do I need to index the table? If so how do I do this?
View 4 Replies
View Related
Jun 12, 2002
Ok,
here's a funky one That I can't find an expanation for. If I go into EM and choose a table from a database and return all rows, I get immediate results and can start browsing records. If I go into query analyzer and do a select * on the same table, it takes up to 20-25 minutes to return the result set. This used to only take like 5 mins. What gives? Anyone seen this before?
View 1 Replies
View Related
