Rolling 13 Months In Query
Jun 23, 2008
Hi,
I'm writing a query that will be calculating a number of business performance measures including the following...
Average Stock:
This is calculated by taking the actual stock value of the product category per month and dividing it by 13 if we have over 12 month's history, or if a new product, then by the number of months we have had it in stock for.
Cost of Goods Sold Annualised:
This is an average figure for the year of the sold landed costs for the product category over a 13 month period if we have over 12 months history. If it's a new product, then it's for the number of months we have had stock.
My question is, how do I tell the query to go back 13 months from the current month?
View 1 Replies
ADVERTISEMENT
Nov 5, 2012
I'm in need of creating a rolling sum to find the sum of 3 previous months gas production. Below is an example of the basic data I will be using to find the sum.
OperatorIDRRCID YearReported MonthReported YearCode DateReported GasProduction
71054993207201222012022012-02-01 89
71054993207201212012012012-01-01 721
710549932072011122011122011-12-01 353
710549932072011112011112011-11-01 409
710549932072011102011102011-10-01 544
71054993207201192011092011-09-01 707
71054993207201182011082011-08-01 506
71054993207201172011072011-07-01 536
71054993207201162011062011-06-01 472
I want the sum of the Year & Month of 2012-2 to be the sum of the gas production for 2012-1, 2011-12, and 2011-11.
View 1 Replies
View Related
May 4, 2015
I am trying to calculate the sum of gross sales over a rolling 6 month period in PowerPivot and don't even know how to start.
View 2 Replies
View Related
Dec 4, 2007
Hi
I need to write something in T-SQL which will return a record set from a table of IT hardware. The record set should comprise all items that are older than 3 years from the current date. Each hardware item has a purchase date against it so it should not be too difficult. I am considering the DATEDIFF function but I am not sure how best to write it.
Any suggestions ??
Thanks
View 2 Replies
View Related
Oct 18, 2007
Please refer to this table in this discussion:
Charges
Date
1
1/1/07
2
2/1/07
3
3/1/07
4
4/1/07
5
5/1/07
6
6/1/07
7
7/1/07
8
8/1/07
9
9/1/07
10
10/1/07
11
11/1/07
12
12/1/07
What i'm trying to do is return a result with total charges from 3 months previous based on a date parameter or just as a whole for that matter. For example:
If my date parameter is 6/14/07, i'd like my total charges to be 15 (6+5+4).
If my date parameter is 7/10/07, the charges would be 18 (7+6+5)
I hope that makes sense of where i'm going with this. I've played with this using the "Guru's Guide" solution by Ken Henderson, but still to no avail yet. Here's his code syntax:
Code Block
SELECT a.DayCount, a.Sales, SUM(b.Sales)
FROM Sales a CROSS JOIN Sales b
WHERE (b.DayCount <= a.DayCount) AS RunningTotal
GROUP BY a.DayCount,a.Sales
ORDER BY a.DayCount,a.Sales
Here is the result set i'm looking for:
Charges
Date
1
1/1/07
2
2/1/07
6
3/1/07
9
4/1/07
12
5/1/07
15
6/1/07
18
7/1/07
21
8/1/07
24
9/1/07
27
10/1/07
30
11/1/07
33
12/1/07
Each date's charges are a culmination of 3 months worth of charges.
Any help would be greatly appreciated. I'll be trying to figure this one out concurrently and if i do, i'll post back!
Thanks in advance!
View 6 Replies
View Related
Jun 18, 2015
I am using MS SQL 2012 and have a pretty simple table dbo. Migration Breakdown with sample data as follows.
DepartDateTime ZoneMovement
2015-06-26 14:00:00.000 6 to 4
2015-06-26 14:00:00.000 11 to 7
2015-06-26 15:30:00.000 9 to 6
2015-06-26 21:00:00.000 7 to 3
2015-06-27 08:01:00.000 7 to 4
[code]....
What I am trying to do is parse the data set to find out when we have more than three like movements ex. 3 to 10 within ANY rolling 72 hour period. I have looked at the SQL Window Functions OVER with a ROW | RANGE subclause, but I can't find out how to tackle this rolling 72 hour business.
View 9 Replies
View Related
May 13, 2008
Hi All,
I need to display the rent not paid months in the year. How can I form the query?
Help me.
Kamal.
View 7 Replies
View Related
Jan 31, 2008
Hi,
I need to count last 3 months records. The query I'm trying to use was based on last 8 days. How can I use this query in months and not days? Or should I use a new query?
Code Snippet
SELECT COUNT(Column1) AS [N� de Cartões], Column2 AS [Tipo de Cartão], Column4 AS Periodicidade
FROM main_client_file
WHERE (Column9 >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 3), 0)) AND (Column9 <= DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 1), 0))
GROUP BY Column2, Column4
Thx.
View 4 Replies
View Related
Mar 27, 2008
I have the following table
FeedBack Type Date
test2 positive 03/15/08
tes3 negative 03/01/08
.. ....
in my page i need to select the number of negative/positive comments within the last
1 month, 6 months, 12 months
How can I accomplish that?
thanks
View 5 Replies
View Related
Feb 5, 2008
I have a table like FK_ID, Value, Date (here FK_ID is foreign key)this table getting updated frequently by daily bases that means one record per one day(For example in January month it has maximum 31 records or minimum 0 records, in February it has maximum 28 or 29 or minimum 0 records, based on calender year)I need to query this table to get missing dates in between particular monthsfor example for one FK_ID has only 25 records in Jan 2008 month and in Feb 2008 it has 10 records , so i need to get those missing 6 dates from JAN month and 18 dates from FEB monthhow can i query this
View 2 Replies
View Related
Sep 16, 2004
Hi,
I need to write a select query to get last six months data from a table , on monthly wise.Can anyone help me in writing this query
I am using sql server 2000.
eg: Jan...data
feb...data
TIA,
Ravi
View 1 Replies
View Related
Jun 15, 2006
I have a proc to get the rowcounts for the given date range.
I have to get the row counts for each day going back to 6 months on the table.
With this proc i can get one day's row couts.. i need to loop through for all dates.
Please can someone get me the code for this.
create proc p_rowcounts
@Date1 datetime,
@Date2 datetime
SELECT
count (*) as 'Number of Rows', @Date1 as Date
FROM
Table1 (nolock)
WHERE ModifyTime >= @Date1 and ModifyTime < @Date2
thanks for the help.
View 3 Replies
View Related
Oct 18, 2007
I have a data set that returns the following
Customer
pncount
pn%
monthval
ABC
5
30
01/01/2007
DEF
9
20
01/01/2007
GHI
4
40
01/01/2007
ABC
DEF
4
90
02/01/2007
GHI
7
10
02/01/2007
ABC
4
5
03/01/2007
DEF
GHI
6
100
03/01/2007
but I need this
01/01/2007
01/01/2007
02/01/2007
02/01/2007
03/01/2007
03/01/2007
abc
5
30
4
5
def
9
20
4
90
ghi
4
40
7
10
6
100
Any Idea Help
View 13 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
Mar 27, 2001
If I issue -
update x set y = 'z'
is there a way to undone/roll-back what was done in SQL 7 ?
Thanks very much.
-FB
View 2 Replies
View Related
Sep 8, 2004
I have created a DTS Package that does the following:
1 Delete all data from table 1 (SQL Task)
2 Import Data from .csv file into table 1 (Data import)
3 insert records from table 1 into table 2 when they dont exist in table 2 (SQl Task)
This all works fine, but now i want a rollback function in step 1 and 2.
So when Step 1 is finished and something goes wrong i want the deleted data back.
Can somebody help me?
Thanks in advance.
View 1 Replies
View Related
Feb 19, 2004
One of our engineers here by mistake deleted some very important data without any begin trans block and we need the data back very badly.. We have not closed the session as of now.. The engineer was logged in as sa to the DB.
Can someone please help soon on urgent basis...
View 6 Replies
View Related
Dec 1, 2014
We have an inventory of devices we service and wish to show the total numbers of active devices per month going 12 months back. How would I go about this?
View 2 Replies
View Related
Feb 16, 2006
I'm performing a stored proc that has 4 inserts. I only want the inserts to complete as a batch. If one fails, I want to rollback the whole transaction. Does anyone know the syntax?? :)
View 4 Replies
View Related
Mar 3, 2006
I want to insure that each of my insert statements in a stored proc are rolled back if any of the inserts fail. I already have the below statement with error handling but is this correct? It seems to me that all the steps should be made part of an entire transaction so if one part fails then it all fails. Can someone help me w/ the syntax of this??
CREATE PROCEDURE Addrecords AS
--USERS
INSERT INTO [Production].[dbo].[USERS]([LastName], [UserName], [EmailAddress], [Address1], [WorkPhone], [Company], [CompanyWebsite], [pword], [IsAdmin], [IsRestricted],[AdvertiserAccountID])
SELECT dbo.fn_ReplaceTags (convert (varchar (8000),Advertisername)), [AdvertiserEmail], [AdvertiserEmail],[AdvertiserAddress], [AdvertiserPhone], [AdvertiserCompany], [AdvertiserURL], [AccountNumber],'3',0, [AccountNumber]
FROM production
WHERE not exists (select * from users Where users.Username = temp.AdvertiserEmail)
AND validAD=1
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN
END
--PROPERTY
INSERT INTO [Production].[dbo].[Property]([ListDate],[CommunityName],[TowerName],[PhaseName],[Unit], [Address1], [City], [State], [Zip],[IsActive],[AdPrintId])
SELECT [FirstInsertDate],[PropertyBuilding],[PropertyStreetAddress],PropertyCity + ' ' + PropertyState + ' ' + PropertyZipCode as PhaseName,[PropertyUnitNumber],[PropertyStreetAddress],[PropertyCity], [PropertyState], [PropertyZipCode],'0',[AdPrintId]
FROM [Production].[dbo].[Temp]
WHERE AdvertiserEmail IS NOT NULL
AND validAD=1
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN
END
View 2 Replies
View Related
Feb 12, 2007
Hi,
trying to develop a report that shows the sum total for each month during a specified date range.
I have parameters asking for the start date and end date of the date range, the report needs to show the months in between this date range.
One of the fields in the dataset is durationminutes which I need to sum for each month then divide by 60 to show hours. I then need to show the total of each month in a bar graph (with each month along the x axis).
If anyone can shed some light on how I am going to do this it would be greatly appreciated.
Thanks.
View 5 Replies
View Related
Oct 3, 2007
Hi All,
I wanna pull a 12 month rolling data and donno how to do that, can anybody help ?
Problem:
lets say if i run the query today i should get data between oct-3-2006 and oct-3-2007, my main colums is monthyear which is date datatype.
Appreciate your help.
Thanks
Chinna
View 1 Replies
View Related
Nov 10, 2006
I'm trying to figure out how to this do with TSQL and preferablythrough joins rather than by using cursors or temp tables. I havemultiple time spans for multiple individuals that I would like torollup together where they don't actually have a gap in coverage.ID StartDate EndDateZ000001 01/01/1996 9/30/1996Z000001 10/01/1996 12/31/1998Z000001 01/01/1999 04/30/1999Z000001 01/01/2000 12/31/2000Z000001 01/01/2001 12/31/2001I would like to be able to "roll these up" in order to reflect theactual time frames so that it would like this:ID StartDate EndDateZ000001 01/01/1996 4/30/1999Z000001 01/01/2000 12/31/20014/30/1999 to 01/01/2000 is, of course, a legitimate break where therewas a date difference of more than one day.Thanks in advance for any ideas.
View 7 Replies
View Related
Mar 19, 2007
I have a stored procedure that calls another stored procedure with thefirst stored procedure opening a transaction:BEGINSET XACT_ABORT ONBEGIN TRANSACTIONdoes various updates/insertscalls 2nd stored procedure to proccess updates/inserts common to manyother stored proceduresdoes more various updates/insertscommitENDThe problem I'm having is that within the 2nd stored procedure is thatif it encounters an error, it does not roll back the entiretransaction and I finish up with missing records in the database. Amusing this in the 2nd stored procedure:if(@TypeId1 = @TypeId2 and @Line1 <'' and @Line2 <'')beginRAISERROR('error message', 16, 1)RETURNendWhat could the problem be? From what I've read, it seems as thoughyou can't have an open transaction within one sp that calls another spand it maintains the same transactoin? Is this corrrect?I tired the following too, and I still couldn't get it to work. Anyideas anyone?************ sp 1 ***********Declare @AddressError char(3)SET XACT_ABORT ONBEGIN TRANSACTIONexec Sp2@AddressError OUTPUT,@variable1,@variable2,etc. etc************** sp 2 *****************@AddressError char(3) OUTPUT,if(@TypeId1 = @TypeId2 and @Line1 <'' and @Line2 <'')beginRAISERROR('error message', 16, 1)RETURNendSET XACT_ABORT ONBEGIN TRANSACTIONprocess updates/insertsSet @AddressError = 'no'Commit******** back to sp 1************If @AddressError <'no'BEGINrollback transactionENDcontinue doing updates/insertscommit
View 1 Replies
View Related
Jun 15, 2007
Is database mirroring compatible between SQL Server 2005 (Standard Edition) SP1 and SP2? I currently run SP1, and will shortly be introducing a failover partner. I'd like to have the failover partner installed as SP2, start the mirroring, manually failover, then upgrade the existing server to SP2 as well.
Is this a supported upgrade method, or will I need to upgrade the existing server to SP2 before setting up database mirroring? The SP2 installation instructions and readme file don't appear to mention mirroring at all (at least not that I can find).
Many thanks for any assistance.
View 4 Replies
View Related
Oct 9, 2006
I'm trying to create an accumulating field based on a set of records. I need to fill in daily amount balances that accumulates on a daily basis. But I can't seem to figure out how to create a total for the daily dates and have it add on additional amounts if needed.
Here's some sample data:
5 6 20 1 200.00 5/5/20000
5 6 20 1 -149.00 5/8/2000
5 6 20 1 100.00 5/10/2000
Now I already have a table with the dates created via a stored procedure. I have a set of dates from 5/5/2000 to 5/8/2000. So that results set should look like this:
5 6 20 1 200.00 5/5/2000
5 6 20 1 200.00 5/6/20000
5 6 20 1 200.00 5/7/2000
5 6 20 1 51.00 5/8/2000
5 6 20 1 51.00 5/9/2000
5 6 20 1 151.00 5/10/2000
....
I'm trying to creating a rolling sum that accumulates the amount field for each daily record and if a new amount is listed, then roll that amount into the total. If you have any suggestions about how to perform this rolling total via TSQL or SSIS, I would greatly appreciate it.
Thanks
Greg
View 1 Replies
View Related
Jun 16, 2007
Hi,
I have a case where I read from SQL Server DB and write to a flat file.
I have one Data Flow Task inside which I have a OLEDB source component that feeds rows to a script component that writes to a flat file. I have set the txn attributes for the container to "Required" and "Read committed" . But I find that rows are written to flat file even when I throw an exception from my script component. Question is how do I prevent rows from being written to the flat file if error/exception happens. I want the whole process to be in a single transaction.
Thanks
View 3 Replies
View Related
May 13, 2008
Dear All
I have the following table structure and following data
create table emp(empid char(4),empname char(10),empsal money,empsalPercent money,empsalRuningTot money)
empid empname empsal empsalpercente001 tom 20.00 8.69e002 *** 40.00 17.39e003 harry 15.00 6.52e004 masum 25.00 10.86e005 rana 30.00 13.04e006 dipak 60.00 26.08e007 rana 40.00 17.39
now would like to make the following
1) want to sort first emp table based on empsalPercent field on descending order physicallyProb : am only able to select the same thru descending order but am unable to make it physical shortingselect * from emp order by empsalPercent desc
2) after make descending order for empsalpercent I want to make RunningTotal for the EmpsalPercent column
pls helpsamir
View 3 Replies
View Related
Sep 4, 2001
I had a need to update a column in a table with a calculated value based on the value of the preceeding value in the table (which in turn was based on the preceeding value, etc...).
Although this can easily solved via a cursor, I was able to solve this with a single set statement that first updates a variable (which can be part of a calculation using the 'current' row) and using the knowledge that SET statements are applied 'in order' (top-down).
Most importantly: the technique relies on my observation that the SET statement processes a table in clustered index sequence, or load sequence in a heap table.
I've included a very simple example of this technique below.
I'm curious if anyone is using this (undocumented?) technique and if my assumptions are valid (I'm using this on much larger tables with more complex calculations).
EXAMPLE:
CREATE TABLE #tmp (
num int IDENTITY(1,1) NOT NULL,
val int NULL)
-- load 100 test rows
DECLARE @x int
SET @X = 1
WHILE @X <= 100
BEGIN
INSERT #tmp (val) VALUES (NULL)
SET @X=@X+1
END
DECLARE @i int
SET @i = 1
UPDATE #tmp -- this is a -very- simple example of technique!
SET @i=num+@i, -- @i now has current totaled value + current-row int value
val=@i -- val now stores current subtotal
View 1 Replies
View Related
Feb 15, 2006
We have installed SQL Server 2000 Service Pack 4 recently and have had various issues with some of our Stored Procedures. (which we didn't have with SP 3)
We are looking at our options regarding rolling back to Service Pack 3.
Can this be done ?
&
If so, is there a method documented on how to do this ?
Any advice would be appreciated.
View 3 Replies
View Related
Apr 4, 2013
I have a stored proc that is executed from within another stored proc, both of these procs run transactions in them that update data.
Now if either of these 2 SPs fail i want to be able to roll back the transactions that have occured so that the data doesn't change.
View 5 Replies
View Related
Apr 9, 2008
Ive got an insert statement that fails, and below that I have code like the following:
IF @@ERROR <> 0
BEGIN
-- Roll back the transaction
ROLLBACK
-- Raise an error and return
RAISERROR ('Error INSERT INTO Address.', 16, 1)
print 'test was here'
RETURN
END
However, there is now rollback and the inserts below it are going through.
what do i have wrong ?
View 5 Replies
View Related
Mar 29, 2007
Don't know if this is possible or not:
Goal: set security on running specific stored procedures based upon user login and databse access
I have some DBA's who want to retain full control of databses / stored procedures as they now have but I want to restrict or rollback some of the changes that were implemented when the sql 2005 was set up. The sql 2005 EE is in a clustered system and uses Mixed Mode Authentication.
An example of what I want to restrict: The DBA's want to be able to view and kill processes for the different databases that are installed under their instance. The problem is other customer databases are also under the same instance.
Is their a way I can combine or have the stored procedure sp_lock only show the processes for the databases they have access to based upon their login? My concern is they will kill a process and affect the other customers.
Thanks for any info or pointers.
carl
View 1 Replies
View Related
