SQL Server 2012 :: Insert Row With Values From Previous Row If Does Not Exist?

Jan 7, 2014

CREATE TABLE #MyTable
(
Teams VARCHAR(10),
StartDate DATETIME,
Count INT
)
INSERT INTO #MyTable (Teams,StartDate,Count)
SELECT 'Team A', '01/01/2014',10

[Code] ....

'Team A' Has No records for '01/02/2014' and '01/03/2014' so I need to insert values of '01/01/2014' StartDate for Team A for the missing dates

So 'Team A' will now have 3 records

Team A2014-01-01 00:00:00.00010
Team A2014-01-02 00:00:00.00010
Team A2014-01-03 00:00:00.00010

'Team B' Has No records for '01/03/2014' so I need to insert values of '01/02/2014' StartDate for Team B for the missing date

So 'Team B' will now have 3 records

Team B2014-01-01 00:00:00.00030
Team B2014-01-02 00:00:00.00040
Team B2014-01-03 00:00:00.00040

As for 'Team C' we have values for all 3 dates, no inserts needed.

View 3 Replies


ADVERTISEMENT

SQL Server 2012 :: Generate Months Based On Previous Values

Jul 7, 2015

I have a data that with month values ranging from jan 2012 till july 2013 with some values associated with it.

I want to generate months automatically after july 2013 till december 2013 in sql something like the below one:

Is there a way in sql to do this?

View 2 Replies View Related

SQL Server 2012 :: Query To Find The Difference In Values From Previous Month?

Dec 12, 2013

I have my sql tables and query as shown below :

CREATE TABLE #ABC([Year] INT, [Month] INT, Stores INT);
CREATE TABLE #DEF([Year] INT, [Month] INT, SalesStores INT);
CREATE TABLE #GHI([Year] INT, [Month] INT, Products INT);
INSERT #ABC VALUES (2013,1,1);
INSERT #ABC VALUES (2013,1,2);

[code]....

I have @Year and @Month as parameters , both integers , example @Year = '2013' , @Month = '11'

SELECT T.[Year],
T.[Month]

-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
,
(SELECT SUM(Stores)
FROM #ABC
WHERE [Year] = T.[Year]
AND [Month] = T.[Month]) AS [Sum_Stores],
(SELECT SUM(SalesStores)

[code]....

What I want to do is to add more columns to the query which show the difference from the last month. as shown below. Example : The Diff beside the Sum_Stores shows the difference in the Sum_Stores from last month to this month.

Something like this :

+------+-------+------------+-----------------+-----|-----|---+-----------------
| Year | Month | Sum_Stores |Diff | Sum_SalesStores |Diff | Sum_Products |Diff|
+------+-------+------------+-----|------------+----|---- |----+--------------|
| 2013 | | | | | | | |
| 2013 | | | | | | | |
| 2013 | | | | | | | |
+------+-------+------------+-----|------------+--- |-----|----+---------| ----

View 3 Replies View Related

SQL Server 2012 :: Check To See If Multiple VALUES Exist In A Table

Dec 6, 2013

I know how to check for a sinle vlaue but how do I chekc to see if multiple values exist. I need to check for certain email addresses from a list that I have.

Let us say I ahve 3 email addresses, I want to check for all of them in a table and for eevery email address that is present I want to print something like "You email address is XXX" and if one of those 3 is not found my results should look like

"You email address is XXX"
YYYYY not found
"You email address is ZZZZ"

I'm attaching some TSQL that I tried on [AdventureWorks2012].[Person].[EmailAddress]

/****** Select ALL if where an email address is present in the list ******/
SELECT EmailAddressID,EmailAddress
FROM [AdventureWorks2012].[Person].[EmailAddress]
WHERE EmailAddress IN
(
'ken0@adventure-works.com', --1
'terri0@adventure-works.com', --2

[Code] ....

-- Test to see if a single email address is present

IF EXISTS
(
SELECT EmailAddress FROM [AdventureWorks2012].[Person].[EmailAddress]
WHERE EmailAddress IN ('25rob0@adventure-works.com')
)
BEGIN
SELECT 'Email address is presnt'

[Code] ....

When I check multiples using EXISTS it works as per its design and says YES even if a single item is present.

View 4 Replies View Related

SQL Server 2012 :: Insert Values In A Single Table With Four Columns From 4 Different Sources?

Jan 30, 2014

I am trying to insert values in a single table with four columns from 4 different sources. is it possible to run these 4 insertions in parallel. all these insertion are independent of each other

View 3 Replies View Related

SQL Server 2012 :: Insert Rows Based On Number Of Distinct Values In Another Table

May 20, 2014

I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.

Example:

Temp table: (Contains only one field with all distinct values in table 1)

Days_in_warehouse
20
30
40

Table 1 :

PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20
2 5 30
3 7 40

Updated Table 1:

PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20 20
1 10 20 30
1 10 20 40
2 5 30 20
2 5 30 30
2 5 30 40
3 7 40 20
3 7 40 30
3 7 40 40

How can I update Table 1 to see desired results?

View 3 Replies View Related

SQL Server 2008 :: Get Previous Values Based On Separate DB / Table?

Apr 24, 2015

I am pulling down out of range values from a single table on one database to a different table on a different database on a different server (one i have full access to). Basically, it looks something like this:

id1 value1 prev_value1 value2 prev_value2 date prev_date
id2 value1 prev_value1 value2 prev_value2 date prev_date
id3 value1 prev_value1 value2 prev_value2 date prev_date

all the "prev"'s are null. I want to do one do one query that will get me the previous values and dates for each id from the original database. how to do this.

View 0 Replies View Related

SQL Server 2012 :: Get Previous Years Holiday Date?

Jan 6, 2015

Given this table;

DECLARE @table TABLE (HolidayDate DATE, HolidayName NVARCHAR(50))
INSERT INTO @table
( HolidayDate, HolidayName )
VALUES ('2012-01-01','New Years Day'),
('2012-01-16', 'MLK Day'),
('2012-02-20', 'Presidents Day'),

[code]....

How to get a result set that shows a new column called PreviousHolidayDate with the corresponding holidays last years date?

HolidayDateHolidayNamePreviousHolidayDate
1/1/2012New Years DayNULL
1/16/2012MLK DayNULL
2/20/2012Presidents DayNULL
4/6/2012Good FridayNULL
5/28/2012Memorial DayNULL

[code]....

View 4 Replies View Related

SQL Server 2012 :: Don't Start One Statement Until The Previous Is Finished

Mar 3, 2015

I am working with a stored procedure that needs to roll up a week number column once a week - columns are numbered 1-10, 1 being this week, 2 being last week and so forth

Once a week the 10th column is deleted, the 9th becomes 10, the 8th becomes the 9th and so forth and the 1st is calculated the week numbers are getting all screwed up - and we think it's because one statement starts before the one before it completes the statements go like this:

delete theTable where week_num=10;
update theTable set weeknum=10 where weeknum=9;
update theTable set weeknum=9 where weeknum=8;
and so forth

is that the reason? is there any way not to start one statement until the one before it finishes?

View 2 Replies View Related

SQL Server 2012 :: Trigger For Updates On A Row Using Previous Record Value?

Mar 9, 2015

I am looking to update a record from a previous row. So if there is a value of total goods in week 1, i want that value to carry forward to the value of goods in week 2. Is there any SQL as an example of the best way to accomplish this? I can query it using lag() which works great but i need the source data itself to update as the end-users are accessing the data via lightswitch, so when they save a change, i want the trigger (or whatever you recommend) to update the source table.

View 9 Replies View Related

SQL Server 2012 :: Running Totals For Previous X Days?

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

SQL Server 2012 :: How To Remove Next And Previous Record Amount Equal To 0

Jun 2, 2014

Having below data. SHCOMP (Customer ID), CBLNAM ( Customer name), SHDESC (service description), SHAMT (service charge), SHTYPE (F-> From T-> To)

CREATE TABLE #Temp(
[SHCOMP] [numeric](7, 0) NOT NULL,
[SHDESC] [char](35) NOT NULL,
[SHTYPE] [char](1) NOT NULL,
[SHAMT] [numeric](9, 2) NOT NULL,

[Code] ....

I need to remove records that have the same ID, Name, Description and Amount summary = 0, For example

SHCOMP CBLNAM SHDESC SHAMT SHTYPE

123 cust1 desc1 45 F remove
123 cust1 desc1 -45 T remove
123 cust1 desc1 45 F remove
123 cust1 desc1 -45 T remove
123 cust1 desc1 45 F

[Code] ....

Results

SHCOMP CBLNAM SHDESC SHAMT SHTYPE

123 cust1 desc1 45 F

123 cust1 desc1 -35 T

234 cust3 desc2 30 F

here is what I did but didn't work because sum is <> from 0 so is leaving all records

select SHCUST, SHDESC, ABS(SHAMT) SHAMT
into #t1
FROM #Temp
group by SHCUST, SHDESC, ABS(SHAMT)
having SUM(SHAMT)=0
order by SHCUST

delete S from #Temp S
join #t1 T on T.SHCUST = S.SHCUST and T.SHDESC = S.SHDESC

View 5 Replies View Related

T-SQL (SS2K8) :: Count Number Of Values That Exist In A Row Based On Values From Array Of Numbers

Apr 16, 2014

How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:

Table Name: test1
Columns: m1 (int), m2 (int), m3 (int) >>> etc
Array/Row1: 1 2 3 4 5 6 7 8 9 10

------
Table Name: test2
Columns: n1 (int), n2 (int), n3 (int), n4 (int), n5 (int)

Row 1: 3, 8, 18, 77, 12
Row 2: 1, 4, 5, 7,18, 21
Row 3: 2, 4, 6, 8, 10

Answer: 2 out of 5
Answer: 4 out of 5
Answer: 5 out of 5

View 2 Replies View Related

SQL Server 2012 :: Add Columns To Result That Don't Exist In Table?

Oct 7, 2015

I am trying to produce a query result that will be using a Case statement to determine values based on scores in a table for each row. The result needs to be exported to be used to upload to a state reporting website. My problem is that the state requires in the CSV file that is uploaded a lot of fields that we do not actually have in the database table we are creating the result set from. After I receive my result set using the Case statement, is there a way to add additional columns that don't actually exist in a table so I can export directly from SQL?

View 6 Replies View Related

SQL Server 2012 :: Replace All Values In String With Values From Look Up Table

Mar 19, 2014

I have a table that lists math Calculations with "User Friendly Names" that look like the following:

([Sales Units]*[AUR])
([Comp Sales Units]*[Comp AUR])

I need to replace all the "User Friendly Names" with "System Names" in the calculations, i.e., I need "Sales Units" to be replaced with "cSalesUnits", "AUR" replaced with "cAUR", "Comp Sales Units" with "cCompSalesUnits", and "Comp AUR" with "cCompAUR". (It isn't always as easy as removing spaces and added 'c' to the beginning of the string...)

The new formulas need to look like the following:

([cSalesUnits]*[cAUR])
([cCompSalesUnits]*[cCompAUR])

I have created a CTE of all the "Look-up" values, and have tried all kinds of joins, and other functions to achieve this, but so far nothing has quite worked.

How can I accomplish this?

Here is some SQL for set up. There are over 500 formulas that need updating with over 400 different "look up" possibilities, so hard coding something isn't really an option.

DECLARE @Synonyms TABLE
(
UserFriendlyName VARCHAR(128)
, SystemNames VARCHAR(128)
)
INSERT INTO @Synonyms
( UserFriendlyName, SystemNames )

[Code] .....

View 3 Replies View Related

SQL Server 2012 :: Selecting Matching Rows Which Exist In Particular String?

Apr 3, 2015

Suppose I have string like

@strname varchar= = '3 April 15 abcd Oh rrrrrrrAAAAdd HJHJG'

and table contains two columns having rows like,

ID text
1 abcd ER
2 abcd AS
3 abcd Oh
4 xyz TR
5 azs WS
6 abcd O
7 OP trx

how can I search a ID's which are exist in my string.

result should be,

3 abcd Oh
6 abcd O

View 4 Replies View Related

SQL Server 2012 :: Select Matching Rows Which Exist In Particular String?

Apr 14, 2015

Suppose I have string like

DECLARE @strname varchar(50) = 'i_ncm_ai04';
DECLARE @SAMPLE_DATA TABLE
(
SD_ID INT NOT NULL
,SD_TEXT VARCHAR(10) NOT NULL

[Code] ......

how can I search a ID's which are exist in my string.

result should be,

3 i_ncm
8 i_ncm_a

View 2 Replies View Related

How To Sum Previous Row Values?

May 15, 2008

Hi,

I need your help for my SQL query. I have a table like this

ClmA ClmB ClmType
------+------+----------
1 | 10 | 0
2 | 20 | 0
3 | 30 | 1
4 | 40 | 0
5 | 50 | 1

And its the result that i want to get.

ClmA ClmB ClmType ClmResult
------+------+---------+-------
1 | 10 | 0 | 10
2 | 20 | 0 | 30
3 | 30 | 1 | 30
4 | 40 | 0 | 70
5 | 50 | 1 | 80


Let me explain. When retrieving a row, an extra column should be added.It's value should be the sum of previous rows whose type is the same with the encountered one. I made it with a function but it's performance was terible with large tables. I have tables larger then fifty housands rows.

View 7 Replies View Related

Getting Previous Values Of A Field

Mar 15, 2006

Hi,In oracle I have a LAG function using which I could get the previousvalue of a field.Do we have anything similar to that in SQL Server or access?ThanksDevi

View 4 Replies View Related

Most Effecient Way To Get Previous Row Values?

Jan 23, 2007

Wow, this board has gotten really busy lately - maybe 2007 is the year that a lot more people start using SSIS :)

Anyway my question is this: If I have an ordered set of data in the data flow and I want to add a column, lets just say "previousID" that basically has the ID value of a column from the row immediately before it - what is the most effecient way of doing that?

I've done much more complicated things with running averages, mean, etc by creating an asynchronous script transformation, pushing the data into a datatable in memory and looping through row by row using variables etc to do the calcs... but I just have this feeling that there is a "lighter, faster, easier" way for just getting previous row's value (with some special rows like first row has a null etc) than looping through a datatable row by row.

Can you push the buffer into an array (if so anyone have an example script) and use simple "n-1" logic? (ie using the array index)

 

 

View 13 Replies View Related

Copy Values From Previous Row

Aug 26, 2006

I'm guessing this is a fairly straight forward need, but want to make sure I am using the correct set of tasks:

In the dataflow, some values I need to carry forward from the previous row, such as a balance that I need to carry forward for the current customer record. This is similar to a running total, only I am not summing anything, but just carrying over from the previous records value (assuming dataset is sorted correctly, first by customer #, then by date).

Do I need the Dervied Column transform, and use a variable to store the previous value, or is there another transform that would be better suited?

Thanks

Kory

View 4 Replies View Related

Average Of Previous Values

May 9, 2008


How to write Stored Procedure to convert All seconds to Minutes AND finding average.

Ex:

My Table1:

SYMBOL TIME PRICE

EUR A0-FX 2008-05-09 11:37:31.203 1.54035
EUR A0-FX 2008-05-09 11:37:30.030 1.54034
EUR A0-FX 2008-05-09 11:37:28.860 1.54033
EUR A0-FX 2008-05-09 11:37:41.673 1.54032
EUR A0-FX 2008-05-09 11:37:59.720 1.54031

EUR A0-FX 2008-05-09 11:38:09.000 1.54033
EUR A0-FX 2008-05-09 11:38:35.877 1.54032
EUR A0-FX 2008-05-09 11:38:59.767 1.54041


OutPut:

SYMBOL TIME PRICE

EUR A0-FX 11:37 1.54031
EUR A0-FX 11:38 1.54041

I know this how to write ..




;WITH cte
AS
(
SELECT
SYMBOL,
[Time],
Price,
ROW_NUMBER() OVER(PARTITION BY CONVERT(CHAR(5), CAST(Time AS DATETIME), 114) ORDER BY CAST(Time AS DATETIME) ASC) AS rn_1,
ROW_NUMBER() OVER(PARTITION BY CONVERT(CHAR(5), CAST(Time AS DATETIME), 114) ORDER BY CAST(Time AS DATETIME) DESC) AS rn_2
FROM
Table1 WHERE SYMBOL='EUR A0-FX'
)

SELECT SYMBOL='EUR A0-FX',CONVERT(CHAR(5), CAST(Time AS DATETIME), 114) AS [Time],MAX(CASE WHEN rn_2 = 1 THEN Price ELSE NULL END) AS [Close] FROM cte

GROUP BY
CONVERT(CHAR(5), CAST(Time AS DATETIME), 114)
ORDER BY
CAST(CONVERT(CHAR(5), CAST(Time AS DATETIME), 114) AS DATETIME);



But I want to add some additional code in my procedure like AVERAGE of Previous 5 Price Values.

Ex:

SYMBOL TIME PRICE

EUR A0-FX 11:37 1.54031 ß1
EUR A0-FX 11:38 1.54041 ß2
EUR A0-FX 11:39 1.54021 ß3
EUR A0-FX 11:40 1.54081 ß4
EUR A0-FX 11:41 1.54071 ß5 Previous 5 Average PRICE Values.
(1.54061)

EUR A0-FX 11:42 1.54091 ß6
EUR A0-FX 11:43 1.54021 ß7
EUR A0-FX 11:44 1.54081 ß8


My Final
Out Put:

SYMBOL TIME PRICE AVERAGE
EUR A0-FX 11:42 1.54091 ß6 (1.54061)
EUR A0-FX 11:43 1.54021 ß7 (1.54091)
EUR A0-FX 11:44 1.54081 ß8 (1.54071)

At 11.42 time average is 1-5 price values
At 11.43 time average is 2-6 price values
At 11.44 time average is 3-7 price values


View 2 Replies View Related

T-SQL (SS2K8) :: Set Current Row Using Values In Previous Row

Feb 25, 2015

I've tried all sorts of code i.e. cross apply, running totals, etc. Cannot get this to work. I am trying to add a previous row value but only doing it for each group.

Source records
DECLARE @tbl table (Item int, Sequence int, StartTime datetime, Duration int)
INSERT INTO @tbl (Item,Sequence,StartTime, Duration) VALUES (1,1,'2/25/2015 12:00 am',10),(1,2,null,20),(1,3, null,22),(2,1,'2/25/2015 1:00 am',15),(2,2,null,30),(2,3, null,45),(2,4, null,5)
select * from @tbl

ItemSequenceStartTimeDuration
1102/25/15 0:0010
12null 20
13null 22
2102/25/15 1:0015
22null 30
23null 45
2 4 null 5

I would like to set the start time of the next row to be equal to the previous row time + duration. I know the start time of each group of 'Items' when the 'Sequence' number = 1. The last 'duration' value in the group would be ignored.

My expected output would be:

ItemSequenceStartTimeDuration
1102/25/15 0:0010
1202/25/15 0:1020
1302/25/15 0:3022
2102/25/15 1:0015
2202/25/15 1:1530
2302/25/15 1:4545
2402/25/15 2:305

View 7 Replies View Related

How To Check Previous And Next Record Values

May 22, 2008

Hi all,

I wanted to check the previous and next record values.

For example:

sKey NextKey PreviousKey

1 2 Null
2 8 1
8 5 2
5 null 8

I wanted to check the value of NextKey of Prev record and Skey of Next record.

Any idea?

Regards
Helen

View 5 Replies View Related

Fast Way To Get Values From Previous Record

Jul 20, 2005

Hello,I know that I've seen this question asked on here before, but I can'tfind an answer that gives me the performance that I need.I have a table that stores events for users:CREATE TABLE Lead_Action_History (lead_action_seq INT IDENTITY NOT NULL,lead_action_date DATETIME NOT NULL,lead_seq INT NULL,operator_id VARCHAR(20) NOT NULL,call_time INT NOT NULL,CONSTRAINT PK_Lead_Action_History PRIMARY KEY (lead_action_seq) )GOThe table has a foreign key to another table through the lead_seqcolumn:CREATE TABLE Lead_Master (lead_seq INT IDENTITY NOT NULL,state CHAR(2) NOT NULL,CONSTRAINT PK_Lead_Master PRIMARY KEY (lead_seq) )GOI need to write a query that will give me a sum of call_time brokendown by a column that is in the table joined through the lead_seq.However, if the lead_seq for a row is NULL then I need to use thelead_seq for the previous row (based on lead_action_date) for the sameoperator.This is what I came up with:SELECT LM.state, SUM(call_time)FROM Lead_Action_History LAHINNER JOIN Lead_Master LM ON (LM.lead_seq = LAH.lead_seq)OR (LAH.lead_seq IS NULLAND LM.lead_seq = (SELECT TOP 1LAH2.lead_seqFROMLead_Action_History LAH2WHERELAH2.operator_id = LAH.operator_idAND LAH2.lead_seqIS NOT NULLORDER BYLAH2.lead_action_date DESC))GROUP BY LM.stateThe problem is that Lead_Action_History has millions of records andany solution that I've found involves one or more subqueries on itwhich kills performance. I am going to look at using a covering indexwith the solution above, but I thought that someone here might haveanother way of doing this.I can't really change the structure, but I can play with the indexing.I would still be curious though how other people model this type oftemporal data in a way that makes it easy to work with.Thanks!-Tom.

View 4 Replies View Related

How To Check Previous And Next Record Values

May 22, 2008

Hi all,

I wanted to check the previous and next record values.

For example:

sKey NextKey PreviousKey

1 2 Null
2 8 1
8 5 2
5 null 8


Ex : In the first record of the table, the NextKey is pointing to 2.
So the next record of Skey will be 2. The Next Key for this record is 8. Like wise the next record of this should have the Skey as 8.

Now I need to check whether the NextKey and SKey are correct for all rows.

For that I need to check the previous record of "Next key" and next record of "Skey".

Any idea?

Regards
Helen

View 5 Replies View Related

T-SQL (SS2K8) :: Carry Forward Values From Previous Rows

Mar 23, 2014

I am working on a rewards program and I have a table whenever customer completes a trip, his total fare,business points earned for that particular trip and respective Promotional points gets inserted.

Now I have a scenario whenever customer business points accumulates to 10 then need to award 3 promotional points.

If Business Points=14 for a single trip then for the first 10 points respective Promo points will be awarded and the remaining 4 points should get carry forward for the next trip and this 4 points should get accumulated with the next trip Business Points and so on.

Basically need to check for every 10 Business points accumulated award some Promo points and carry forward remaining points.

Here is the sample table structure and data :

CREATE TABLE [dbo].[tblRedeems]
(
[Mobileno] [varchar](50) NOT NULL,
[TripNo] [int] NOT NULL,
[CustomerName] [varchar](50) NULL,
[TripEndTime] DATETIME NOT NULL,

[Code] .....

View 5 Replies View Related

Query Which Calculates A Field Value Based On Previous Row's Values

Jan 18, 2007

I need to write a t-sql query that will take the value of the previousrecord into consideration before calculating the current row's newcolumn value...Here's the situation...I have a query which return the following table structure...Full_Name Points----------------- ------------Name1 855Name2 805Name3 800Name4 775Name5 775Name6 741etc.... etc...I need to create a calculated column that tells me where the personranks in point position. The problem i run into is that in thesituation where two or more people have the same point value i need thecalculated rank column to display the same rank number (i.e. 4th orjust "4") I'm not sure how to to take into consideration the previousrow's point value to determine if it is the same as the current onebeing evaluated. If i new they were the same i could assign the samerank value (i.e. 4th or just "4").If any one has any insight that would be great.ThanksJeremy

View 2 Replies View Related

Calculate The Growth Percentage Or Previous Dates Values

Feb 7, 2008

This code displays dates, File name, and File size for four seperate dates 11/20/2007 , 11/30/2007, 12/30/2007 and 01/31/2007 . I'm trying to show the percentage growth from date to date (ie 11/20/2007 -11/30/2007 percentage growth)

is there a way i can get the previous date file size for each entry, so i can have a variable for the calculation. Or i can get the calculate it within this code (ie database_size_mb / ((database_size_md ) where database_size_datetime -1) *100
or whatever the formula is for percentage growth.




Code Snippet
SELECT
Database_Size_Datetime,
Database_file_name,
Database_Size_Mb
FROM RC_STAT.dbo.Tbl_Database_Statistics AS Tbl_Database_Statistics_1
GROUP BY Database_Size_Datetime, Database_file_name, Database_Size_Mb

this is what it displays now:


2007-11-20 00:00:00.000 ACTReplication_Data 442.5000
2007-11-30 00:00:00.000 ACTReplication_Data 442.5000
2007-12-31 00:00:00.000 ACTReplication_Data 442.5000
2008-01-31 00:00:00.000 ACTReplication_Data 442.5000
2007-11-20 00:00:00.000 ACTReplication_Log 14.8125
2007-11-30 00:00:00.000 ACTReplication_Log 109.7500
2007-12-31 00:00:00.000 ACTReplication_Log 112.9375
2008-01-31 00:00:00.000 ACTReplication_Log 115.5625
2007-11-20 00:00:00.000 BAMArchive 0.6875
2007-11-30 00:00:00.000 BAMArchive 0.6875
2007-12-31 00:00:00.000 BAMArchive 0.6875
2008-01-31 00:00:00.000 BAMArchive 0.6875
2007-11-20 00:00:00.000 BAMArchive_log 0.4922
2007-11-30 00:00:00.000 BAMArchive_log 0.4922
2007-12-31 00:00:00.000 BAMArchive_log 0.4922
2008-01-31 00:00:00.000 BAMArchive_log 0.4922

View 1 Replies View Related

Time Series: Number Of Previous Values Used In Model

Nov 8, 2006

As the Microsoft Time Series algorithm implementation is based upon the Autoregressive Tree approach described in:

C. Meek, D. M. Chickering, D. Heckerman. Autoregressive Tree Models for Time-Series Analysis. In Proc. 2nd Intl. SIAM Conf. on Data Mining, 2002 (SDM-02). SIAM, pp. 229 €“ 244. http://www.siam.org/meetings/sdm02/proceedings/sdm02-14.pdf.

The model estimated is refererred to as an instance of "... autoregressive tree models of length p, denoted ART(p). An ART(p) model is an ART model in which each leaf node of the decision tree contains an AR(p) model, and the split variables for the decision tree are chosen from among the previous p variables in the time series..." (see the last paragraph of p. 2 of the paper).

What is the value of "p" used in the Microsoft Time Series implementation -- specifically, how many previous time series variables are used in estimating the model? It doesn't appear that this value can be specified in the algorithm parameters -- is that correct?

Thanks,

- Paul

View 1 Replies View Related

T-SQL (SS2K8) :: Stored Procedure To Truncate And Insert Values In Table 1 And Update And Insert Values In Table 2

Apr 30, 2015

table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt

process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)

* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.

View 2 Replies View Related

SQL 2012 :: How To Get Previous Statements In A Batch

Feb 25, 2015

I have an interesting problem. A number of spids are being blocked by a single select statement. The select statement is the same as returned from sp_who2, sysprocesses, sp_whoisactive of dbcc inputbuffer. It is not waiting on anything and has status as sleeping.

Clearly it is not 'just' a sleeping select statement as I can see over a thousand locks held by the spid on 2 user databases and tempdb. I'm working on the theory there is a begin transaction with a bunch of statements and no closing commit. But I want to be able to prove that. How can I show what statements were previously executed as part of this transaction?

Additional Info: SQL 2012 Enterprise Edition. This is a test server but is a reproduction of a live issue. At this point the application team cannot isolate the code causing the problem, only the set of processes the code resides in.

View 2 Replies View Related

Transact SQL :: Undo Update And Bring Back Records To Their Previous Values

Aug 7, 2015

I have a table with 1 million records. I want to update only 400 records. The update statement is provided by a 3rd party vendor. Once i run the update statement it will update all the 400 records. Once the table is updated the users will validate the table

if the update is successful or not. What i'm looking for is:

1) Is there a way to identify what records were updated.
2) If the update done is not what the users wanted i need to undo and bring back the 400 records to their previous values.

I'm on sql server 2008.

View 34 Replies View Related







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