Transact SQL :: Cumulative Totals - Rolling Window

Aug 14, 2015

I'm trying to generate a cumulative total with a rolling window of 13 time periods..Previously I was able to do left outer join to the same table 13 times to add the quantity field but it appears with the migration to SQL Server 2014, that many left outer joins is not possible (query that would run in 3 mins is taking well over 15 hours now)..

View 7 Replies


ADVERTISEMENT

Rolling Data In Forecast And Cumulative Totals Question

Oct 12, 2007



I have a result set that looks like this:





Code Block







Quarter

Year

EstimatedValue

ClosePercent

EstimatedCloseDate


4

2007

100000

50

12/31/07 5:00 AM


4

2007

20000

50

11/30/07 5:00 AM


4

2007

20000

90

10/30/07 5:00 AM


1

2008

278000

50

3/31/08 5:00 AM


4

2007

200000

50

11/30/07 5:00 AM


4

2007

225000

90

10/31/07 5:00 AM


4

2007

36500

90

10/31/07 5:00 AM


4

2007

80000

90

10/31/07 5:00 AM


4

2007

107200

90

10/31/07 5:00 AM


4

2007

225000

75

12/31/07 5:00 AM


4

2007

35000

50

12/31/07 5:00 AM




I have create a simple tabular rolling forecast report (with cumulative totals) from today (October) thru the next 12 months that looks like this. It smartly works no matter when the report is generated, by starting with this CurrentMonth and moving forward by using 1,2,3,4,etc. in the dateadd: =MonthName(datepart("m",dateadd("m",1,Now())))

The report sample (formatting lost in dropping it in here):




Code Block
















Close Pct
October
November
December
January
February

25.%
$0
$0
$26,625
$0
$0

50.%
$237,500
$110,000
$262,500
$0
$0

75.%
$56,250
$0
$891,075
$0
$0

90.%
$1,051,830
$0
$0
$0
$0

Monthly Total
$1,345,580
$110,000
$1,180,200
$0
$0

Cumulative Total
$1,345,580
$1,455,580
$2,635,780
$2,635,780
$2,635,780





It is working fine....there doesn't seem to be anything wrong with it (all numbers total correctly, etc.), but it is very unelegant.....and I know there must be a better way.

In the righthand most month (which would be September 2008) column, I have a formula that produces the amount (the Monthly Total amount is the same):




Code Block=sum(iif(datepart("m",dateadd("m",11,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0)))




and for the Cumulative Total Amount it gets really hideous, as it is trying to add up all of the totals across the board:




Code Block
=sum(iif(datepart("m",dateadd("m",0,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",1,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",2,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",3,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",4,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",5,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",6,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",7,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",8,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",9,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",10,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0))+iif(datepart("m",dateadd("m",11,Now()))=datepart("m",Fields!EstimatedCloseDate.Value),Cdec(Fields!estimatedvalue.Value*Fields!ClosePct.Value*.01),cdec(0)))




I have searched high and low for examples of reports that do something similar.

Can anyone offer any advice?

Thanks.

View 2 Replies View Related

Trying To Get Daily Totals From Cumulative Totals In A Pivot

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

Using SET As An Alternative To Cursors For Rolling Totals

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

Rolling 12M Totals Performance Issues

May 15, 2008



Hi,

We are using SQL Server 2005 Analysis Services in x64 environment.

We use many 12M metrics implemented as calculated measures. A typical 12M calc measure looks like this:


Sum({LastPeriods(12, ClosingPeriod([Time].[Month],

Tail(Existing [Time].[Month].Members).Item(0)))}

, [Measures].[Revenue])

, FORMAT_STRING = '#,#';


The front end is based on OWC.

These are performing extremely poorly and worse compared to our older SQL Server 2000 environment. Is there anything obvious from this formula that would make it worse?

I appreciate any guidance as to what I should look for.

thanks
MJ

View 6 Replies View Related

Carrying Cumulative Totals Over Months

Apr 11, 2008



I am making a report for a car dealer. Each month a certain number of cars will be placed on the lot and a certain number will be taken off. I want to make a report that will show the inventory levels at any point in time. (I will further refine the report by color, origin, model, etc later.) I am currently listing each car in my inventory table, flagging it with a "removed" flag when it is sold. My flag for adding a car is 1 and removing a car is -1. It's easy enough to sum the table and get the current inventory level but I'm having trouble looking back over time.

Say in January I had 35 cars on the lot. I sold 20 and added 5. In February I sold 9 and added 10. In March I sold 12 and added 5.

I need the report to break every month. But on the break I need to add all the cars in the table minus cars sold. For the January report it should show a total (cars added - sold) in the table. Each month I need to take a new grand total of all cars added minus cars sold up to the end of that month OR cars added that month minus cars sold that month added to the running total from the previous month.

Is there some sort of calculate measure I could stick in my cube that would carry these totals? Or any other ideas?

Thanks.

View 1 Replies View Related

Cumulative Totals In Chart : Report Builder

Jan 24, 2007

Hi,

I am trying to display a line chart with cumulative totals over period of 12 months in a fiscal year. I know this can be achieveable in report designer using "Running value function".

Any idea how to achieve the same in Report builder ?

thanks in advance.

Here is the data :

Month Count

July 2

Aug 3

Sept 2



Expected output should be

Month Count

July 2

Aug 5(July count + Aug.Count)

Sept 7(July count + Aug count + Sept Count)

Regards,

bala

View 1 Replies View Related

MDX Problems With Cumulative Totals Of DistinctCount Measure In Function PeriodsToDat

Mar 26, 2005

Hello Experts,

I have a Problem with cumulative totals of DistinctCount of customers while working in the MDX-Query-Designer of Analysis Services.
The measure [Measures].[DC Customer] is a distinct count of CustomerNr of Customers who have purchased products.
I use

With Member [Measures].[DC Custumer kum] As 'Sum (Periostodate([Time].[Year]),[Measures].[CD Customer kum]'
Select {[Measures].[DC Custumer],[DC Custumer kum]} on colums,
[Productgroup].[Maingroup].members on rows
From Sales
Where [Time].[All_Time].[2005].[March]

but it returns the sum of [Measures].[DC Customer] and added the January, February and March value.
I need to get the value of customers who have purchased products from january to march.

Can anyone help?
Thank you in advance

View 1 Replies View Related

Transact SQL :: Cumulative Sum On Value Column

Apr 28, 2015

How would I iterate through this table and do a cumulative sum on the value column :

I'm trying to get the following result:

View 5 Replies View Related

Transact SQL :: How To Get Cumulative Value In Query

Aug 18, 2015

I have table like below, its period wise ,here the value get cumulative period wise.

amtname period
10CHR201202
20TNG201202
10CHR201203
20TNG201203

View 3 Replies View Related

Transact SQL :: Calculate Rolling Year Sales?

Aug 19, 2015

I have 2 tables as follows:

table A-NewParts
PartNumber
Description
LaunchDate
a123
product a
4/1/2009

[code].....

I want to get end result as sales for each new product for a rolling period from Launch date as year1 sales,year2 sales...and so on which I got through case statements.I am stuck on how to get the total Sales for all products (including new products) in the same rolling period based on the different launch dates for each new product.

PartNumber
Description
LaunchDate
Yr1 Sales
Total Yr1 Sales
Yr2 Sales
Total Yr2 Sales
a123

[code].....

View 3 Replies View Related

Transact SQL :: Cumulative Values Quarter And Half Yearly Wise

Nov 23, 2015

Having table like below. Here want to cumulative the values quarter and half yearly wise...

declare @table table 
(month varchar(10),
value int)
insert into @table values('apr' ,100 )
insert into @table values('may' ,200 )
insert into @table values('jun' ,300 )

[Code] ....

Like wise the data should added...

View 3 Replies View Related

Transact SQL :: Query To Show Totals

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

Transact SQL :: Running Totals In A View

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

Transact SQL :: Converting Union Statements Into Census Row Totals

Aug 20, 2015

I have some old code consisting of over 30 Union statements, unioning 7/1/15-8/1/15 data according to the below code.  Each block is the same except for the date.  How could I rewrite these 30 union statements into a more concise query?

Each client (people_id) has a distinct agency_id_number.  So the code below totals the number of distinct clients/day and groups them by programs and facilities.

Sample data that you can use to test this report:

IF OBJECT_ID(N'tempdb..#sample_census') IS NOT NULL drop table #sample_census

create table #sample_census
(
start_date datetime,
program_name varchar(25),
facility_name varchar(25),
agency_id_number varchar(25)
)

[Code] ....

View 3 Replies View Related

Transact SQL :: Top Percent In A Window Function OVER

Oct 11, 2015

I want to create a subset of a geography table. My intention is to select only a few cities with a where and after that select a percent of village for each of the cities. My query look like this:

 SELECT TOP 160 [CodMunicipio] ,[NombreMunicipio] ,[CodProv],[Provincia] ,[COMUNIDAD AUTÓNOMA]
      ,[Longitud ETRS89/REGCAN95],[Latitud ETRS89/REGCAN95]
      ,[XUTM ETRS89/REGCAN95],[YUTM ETRS89/REGCAN95],[Bandera]
   --INTO [PracticasSCD].DBO.DIMGEOGRAFIA
FROM [SportFunDW].[dbo].[DimGeografia2] TABLESAMPLE (3 PERCENT)

[Code] ....

I have tried with tablesample but it doesn´t return what I expect because I have many villages from one city (for example 15 % of it) and only 2 % of other. How can I achieve my goal?

View 5 Replies View Related

Transact SQL :: Query For Month Wise Running Totals Of Sales Amount?

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

Transact SQL :: Window Function To Count Number Of Rows In The Previous 24 Hours?

Nov 16, 2015

is it possible to use the window functions to count the number of rows in the previous 24hours from the current row.

I have a table of events like:

User, TimeStamp, etc...

I want to identify the row which is the event number 50 in the past 24 hours.

does the window functions can do this? and how?

the ROW PRECEDING etc... appear to not have enough feature to support time related function.

Stream-insight is able to create this type of query, but I have to do it directly in SQL.

View 6 Replies View Related

Reporting Services :: Calculating Grand Totals From Group Totals

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

How To Connect To Sql Server As Other's Window Login Rather Than User's Window Login Thorugh ASP.NET.

Dec 14, 2006

Dear members,

In MSDN, it says that it is recommended to use windows authentication to connect to SQL Server rather than use mixed authentication.

I create user deltasqluser on windows OS, and I specify in my webform ASP.NET script below :

protected System.Web.UI.WebControls.Label Label1;
private string _connString = @"data source=deltasql2000;initial catalog=northwind;integrated security=false;user id=deltasqluser";
/*
comment : I login to my windows as deltakoronx, and I want to every user (including me), connected to sql server through IIS, will be identified as deltasqluser not as user's login (impersonate)
*/
private void Page_Load(object sender, System.EventArgs e)
{
SqlConnection conn = new SqlConnection(_connString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select suser_sname()";

conn.Open();


string userName = cmd.ExecuteScalar() as string;
conn.Close();
conn.Close();
Label1.Text = userName;
}


at web.config, I add :
<identity impersonate="false" userName="deltasqluser" password="" />


at IIS webApplication1's properties, tab "Directory Security", at "Authentication and access control" section, I checked "enable anonymous access" with user : DELTAIUSR_DELTA and checked "Integrated Windows Authentication",


at query analyzer, I login as "sa" and execute script below :
exec sp_grantdbaccess 'deltasqluser','northwind'


when I run the ASP.NET script, error at conn.Open(); with error message :
Login failed for user 'NT AUTHORITYNETWORK SERVICE'.


What should I do so that IIS login to SQL Server as user deltasqluser not as "NT AUTHORITYNETWORK SERVICE" ?

Regards,

Koronx

View 1 Replies View Related

Open Report In New Window: Window.open Method Gives Error

Jun 26, 2006



Hi,

I am using SSRS Microsoft SQL Server Reporting Services Designers
Version 9.00.1399.00. I want to open linked report in new window.

I tried whats mentioned in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=240172&SiteID=1 but i get an error on Window.Open method.

How do I solve the problem?

Thanks

View 1 Replies View Related

Rolling-back

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

Rolling Back DTS

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

Cumulative Log

Sep 7, 2007

Is it possible to create a cumulative log using SSIS? basiclly I have 5 logs which hold failed records. I would like to create a cumulative log and send it via email using SSIS.
thoughts?

thanks

View 6 Replies View Related

Rolling Back In SQL Server

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

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 View Related

Rolling Total Per Month

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

Rolling Back Inserts

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

Rolling Back Inserts

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

Rolling Month Graph

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

12 Month Rolling Data

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

Rolling Up Spans Without Breaks Between Them

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

Transaction Not Rolling Back

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







Copyrights 2005-15 www.BigResource.com, All rights reserved