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,
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.
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.
I've a table that stores operationcode for each jobnumber. The jobnumber can have multiple operationcode. From the below DDL, I need to show all the jobs that have operation codes as 2001 and 2002. In the below DDL Jobnumber 80011 has both the operation codes 2001 and 2002 so this job will display on the report.
On the other hand Job 80021 only has operationcode 2001 and I do not want this job to show up on the report.
I need to show all the operationcodes for a job if it has operationcode 2001 and 2002.
USE tempdb; GO DECLARE @TEST_DATA TABLE ( DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED , OperationCodeVARCHAR(10) NOT NULL , EmployeeCode VARCHAR(10) NOT NULL
A fast_forward cursor is read only by definition, meaning the rows can't be updated, but I'm not sure if they are insensitive or not. Do they reflect the changes in the database after the cursor is opened?
1) I have a report with many parameters that I want users to be able to pick from. Allow them to pick 1, many or all to build their report dynamically. I'm all set on the TSQL side, but on the Reporting Services side I have to allow each parameter to be null with a default of NULL. In by doing this, the report will auto run, which I do not want to happen. The only resolution I've found thus far was by adding a parameter that does nothing, with a NULL default value. Yet It sticks out like a sore thumb on the report and I want to get rid of it. If I check in "Hidden" in the parameter options, my report errors out stating that the parameter requires a value.
2) Is it possible to have a parameter that has available values from a dataset have a NULL checkbox like those of parameters that do not have available values?
3) Is it possible to add back/forward buttons inside of a report instead of just at the report header by default?
I have a table with the following structure in sql server 2005
create table app( sno int, name varchar(50), add varchar(50), city varchar(50), state varchar(50) )
it contains the follwing data ------------------------------------------ sno name add city state ------------------------------------------ 1 mark street no1 newcity newstate 2 mark street no1 newcity newstate 3 mark street no1 newcity newstate 4 mark street no1 newcity newstate 5 mark street no1 newcity newstate 6 mark street no1 newcity newstate 7 mark street no1 newcity newstate 8 mark street no1 newcity newstate 9 mark street no1 newcity newstate 10 mark street no1 newcity newstate 11 mark street no1 newcity newstate 12 mark street no1 newcity newstate 13 mark street no1 newcity newstate 14 mark street no1 newcity newstate 15 mark street no1 newcity newstate 16 mark street no1 newcity newstate 17 mark street no1 newcity newstate 18 mark street no1 newcity newstate 19 mark street no1 newcity newstate 20 mark street no1 newcity newstate
----------------------------------------
I want to retrive previous 5 records, next 5 records and the record that meet the where condition of a select query.
When I run
select sno,add,name,city,state from app where sno=7
I want the following result
------------------------------------------ sno name add city state ------------------------------------------ 2 mark street no1 newcity newstate | 3 mark street no1 newcity newstate | 4 mark street no1 newcity newstate | -- previous 5 records 5 mark street no1 newcity newstate | 6 mark street no1 newcity newstate | 7 mark street no1 newcity newstate --- searched record 8 mark street no1 newcity newstate | 9 mark street no1 newcity newstate | 10 mark street no1 newcity newstate |--- next 5 records 11 mark street no1 newcity newstate | 12 mark street no1 newcity newstate | ----------------------------------------
if there is a method to get the above result set, kindly post the query.
I am trying to code a rule at the moment which sets a value based on the value of the previous row. This is as far as I have got so far. I am trying to run it as a set based command and I believe I may need to make use of a numbers table.
Anyway the code is....
declare @data table (ID int identity(1,1), DeviceID int, EventTypeID int, EventID int)
[Code] ....
So the output is currently incorrect. Each DeviceID needs to have a ID assigned to the MIN(ID) which isn't yet in the code above. This ID has to be sequential across the full table and not dependent on ID.
Next the rule is coded in the case statement above.
So for each DeviceID, when the EventType goes from 1, 2 or 4, to 0 or 3, the following record after the 0 or 3 will have a new EventID. And conversely when the EventType goes from 0 or 3 to 1,2, or 4, the record that is the 1, 2 or 4 will have a new EventID.
I am novice to intermediate writer of T-SQL. Here is my current Query:
SELECT [FISCALYEAR], [ACCTPERIOD], SUM([ACTIVITYDEBIT]) AS TrialBalanceDebit, [POSTINGTYPE] FROM [dbo].[TB_Lookup] WHERE [POSTINGTYPE]='Profit & Loss' GROUP BY [FISCALYEAR],[ACCTPERIOD], [POSTINGTYPE] ORDER BY acctperiod ASCand this is what is produces.
FISCALYEARACCTPERIODTrialBalanceDebitPOSTINGTYPE 2014 201401 282361372.13000 Profit & Loss 2014 201402 227246272.86000 Profit & Loss 2014 201403 315489534.33000 Profit & Loss 2014 201404 287423793.76150 Profit & Loss 2014 201405 256521290.76000 Profit & Loss 2014 201406 65582951.30000 Profit & Loss
Now I need a way to add another field that takes the TrialBalanceDebit from current ACCTPERIOD and adds it to the Previous ACCTPERIOD TrialBalanceDebit.
Now i want to compare Result and dislay prevterm where student fail:
Now my output would be as: Now I want to compare latest term i.e. Term5 with prev Terms and if found Mismatch in result then i want to display as below:
How do I get first day of last month of previous quarter from today's date? I know my question is little confusing. I need to get 06/01/2014 using t-sql.
I want to find previous date from selected date. below is the sample data.
DECLARE @StartDate SMALLDATETIME = '1/11/2016'
declare @tempdat table(repdate smalldatetime) insert into @tempdat values ('10/26/2015') insert into @tempdat values ('10/29/2015') insert into @tempdat values ('11/1/2015') insert into @tempdat values ('11/27/2015') insert into @tempdat values ('11/25/2015') insert into @tempdat values ('11/20/2015') insert into @tempdat values ('11/10/2015') insert into @tempdat values ('11/10/2015') insert into @tempdat values ('11/11/2015') insert into @tempdat values ('11/11/2015')
Now if i pass the date '10/26/2015' then i want select prev date of passed date. in this example no prev date is available, so result set would be nothing.
if i pass the date '11/10/2015' then result should be '11/1/2015' which is prev small date available in table.
I am working in sqlserver 2008 R2 and below is my sample research query. I am trying to get previous 6 months data.
WITH CutomMonths AS ( SELECT UPPER(convert(VARCHAR(3), datename(month, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0)))) Month ,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0) startdate ,DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N + 1, 0) enddate FROM ( VALUES (1)
[Code] ...
Current output what i am getting:
Expected Output:
I found why the April month i didn't get the $20 because the startdate of my perks CTE '2014-04-03'. If it is '2014-04-01' then i will get the expected output.
But i should not change the the date on perks. How to neglect this date issue and consider the month instead to get the expected output.
I've SSRS sales report to which I need to pass the dates for previous month's start date and end date which I am able to pass using below code. However, since the sales report has data from the past year(2014) I need to pass the dates for last year as well. The below code gives StartDate1 as 2015-02-01 and EndDate1 as 2015-02-28. I need to get the dates for past year like 2014-02-01 as StartDate2 and 2014-02-28 as EndDate2
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101') AS StartDate1, DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231') AS EndDate1
My goal is to update the "PriorInsert" field with the "DateInserted" from the previously inserted record where the WorkOrder, MachineNo, and Operator are all in the same group.
While trying to get to the correct previous record, I wrote the query below.
P.S. The attached .txt file includes a create and insert tbl_tmp sampling.
select top 1 a.ID, a.WorkOrder, a.MachineNo, a.Operator, a.PriorInsert,
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.
I have a table with score info for each group, and the table also contains historical data, I need to get the ranking for the current week and previous week, here is what I did and the result is apparently wrong:
select CurRank = row_number() OVER (ORDER BY cr.CurScore desc) , cr.group_name,cr.CurScore , lastWeek.PreRank, lastWeek.group_name,lastWeek.PreScore from (select group_name, Avg(case when datediff(day, asAtDate, getdate()) <= 7 then sumscore else 0 end) as CurScore
[Code] ....
The query consists two parts: from current week and previous week respectively. Each part returns correct result, the final merged result is wrong.
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
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)
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?
;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.
I am doing some calculations with a table and would like to know how to reference a previous row without having to alter the dates in a query at the start of each month.
For example I have to find a percentage change for a series of values. The table has three columns, is based on end of month dates, and the "return" column is the calculated column where the value is. In the example the return value for date 2006-04-30 is Index of (2006-04-30/ Index 2006-03-31)-1.
Date Index return 2006-03-31 6535 .05130 2006-04-30 6949.29 .06333
I want to add a row to the bottom of the table so that it will give the total of all values above. It's something like this. SELECT Portfolio, Name, Value , Total FROM table
Total is equal to sum of all above values. Can anyone help me to write this query?
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:
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.
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".
