I am trying to create a stored procedure where i would like to fetch the next previous and current rows from a particular pagename which is stored in a table with sorting of dateadded
like my products table structure
id
iQty
strProductTitle
strPageName
DtAdded
DtApproved
I need help writing a query that will subtract the values of 2 rows from the same column to display in the result set. Some background information: a table has a sales column that keeps track of sales by the minute, but this is done in a cumulative manner, i.e, sales at row 3(minute 3) = sales recorded @ minute 2 plus sales @ minute 3. Therefor to get the actual sale at min 3, i would have subtract value at row 2 from row 3. make sense? it sounds very easy but I am having a hard time refering back to the previous row and am dealing with more than 1000 rows. i thought about doing a self join on the table but could not get it to do what i want. would appreciate any help i can get. thanks
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.
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.
My current code returns account_number with multiple start_date regardless of the value is same or not. However, I would like to get only the account number when the value on start_date is different within same account_number.
select acct_number count(start_date) from table_A group by acct_number, start_date having(count(start_date) > 1)
I have a table (temp_Order) wherein I use to insert a temporary order... after inserting the temporary order, when the buyer submits the order,I want to loop through the table and fetch the orders made by the buyer and insert those products on another table (Order)...how can I achieve this? im currently using sqlDataSource...
I have to compare the previous row with current row in a table in SQL Server 2000. Please help me how to do this in a optimized way. Table contains nearly 30 columns and rows count is more than 10 digits.
I need to return the previous row value if it is negative in current row. For example, in the below table for ID=7 i need the value 1305(ID=4) since 6,5 are negative values.
I've this query SELECT t1.ID, t1.Date, t1.Time, t1.VALUE FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)
Let's say, current date is 8 AUG 2005 and current time is 2045 So, i will get ID | Date (this is datetime) | Time (this is integer) | Value -------------------------------------------------- 204 | 8/1/2005| 2359 | 90 205 | 8/1/2005| 2250 | 99 206 | 8/1/2005| 1950 | 88 ... ... 207 | 8/7/2005| 1845 | 77 208 | 8/7/2005| 2255 | 77 209 | 8/7/2005| 2140 | 77
Can someone can show me to filter data between t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND TIME>=CurrentTime t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101) AND TIME<=CurrentTime
If current date is 8 AUG 2005 and current time is 2045, so the result shown as follow
ID | Date (this is datetime) | Time (this is integer) | Value -------------------------------------------------- 204 | 8/1/2005| 2359 | 90 205 | 8/1/2005| 2250 | 99 ... ... 207 | 8/7/2005| 1845 | 77
I only have this query, SELECT t1.ID, t1.Date, t1.Time, t1.VALUE FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)
There are two tables testmaster and testdetail. If the value of Price for a particular ID in testdetail is more than the threshold value defined in testmaster, the output should have a new column with value as 'High Value', if the value is less than the threshold the new output should be 'Low Value' other wise 'Ignore'
Example: for ID=3, threshold is defined as 40% in testmaster table, but on 11/12/2015 the new price is 100 which 100% more than the previous value, so the status is High Value as shown below.
ID Date Price Status 1 11/12/2015 100 Low Value
2 11/12/2015 160 Ignore 3 11/12/2015 100 High Value
create table testmaster ( ID int, Threshold int ) create table testdetail ( ID int, Date varchar(20), Price float )
I am looking to pull all records for current & previous calendar year in one query. I know how to pull the current calendar year, but how would I pull current & previous?
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 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.
We are trying to compare our current calendar week (based on Monday being the first day of the week) with the previous calendar week.
I'm trying to produce a line chart with 2 axis:
- x axis; the day of the week (Mon, Tues, Wed etc - it is fine for this to be a # rather than text e.g. 1 = Mon, 2 = Tues etc) - y axis; the cumulative number of orders
The chart needs two series:
Previous Week. The running count of orders placed that week. Current Week. The running count of orders placed this week.
Obviously in such a chart the 'Current Week' series is going not going to have values along the whole axis until the end of the week. This is expected and the aim of the chart is to see the current week compares against the previous week for the same day.
I have two tables:
Orders TableCalendar Table
The calendar table's main date column is [calDate] and there are columns for the usual [calWeekNum], [calMonth] etc.
My measure for counting orders is simply; # Orders: = countrows[orders].
How do I take this measure and then work out my two series. I have tried numerous things such as adapting TOTALMTD(), following articles such as these:
- [URL] ... - [URL] ...
But I have had no luck. The standard cumulative formulas do work e.g. if I wanted a MTD or YTD table I would be ok, it's just adjusting to a WTD that is causing me big issues.
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?
HiI want to write a function that can return a sum for a given daterange. The same function should be able to return the sum for the sameperiod year before.Let me give an example:The Table LedgerTrans consist among other of the follwing fieldsAccountNum (Varchar)TransdateAmountMST (Real)The sample data could be1111, 01-01-2005, 100 USD1111, 18-01-2005, 125 USD1111, 15-03-2005, 50 USD1111,27-06-2005, 500 USD1111,02-01-2006, 250 USD1111,23-02-2006,12 USDIf the current day is 16. march 2006 I would like to have a functionwhich called twice could retrive the values.Previus period (for TransDate >= 01-01-2005 AND TransDate <=16-03-2005) = 275 USDCurrent period (for TransDate >= 01-01-2006 AND TransDate <=16-03-2006) = 262 USDThe function should be called with the AccountNum and current date(GetDate() ?) and f.ex. 0 or 1 for this year / previous year.How can I create a function that dynamically can do this ?I have tried f.ex. calling the function with@ThisYear as GetDate()SET @DateStart = datepart(d,0) + '-' + datepart(m,0) +'-'+datepart(y,@ThisYear)But the value for @dateStart is something like 12-07-1905 so thisdon't work.I Would appreciate any help on this.BR / Jan
In Transact SQL I need to calculate the difference between the current bottom and the previous top. If there is no previous top, then the result would be the current bottom.
So, the result would be 0, 2, 2.5, 1
I must return the value in a table. Thank you all for your help!
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,
Hello,I want to select the names and number of rows of all tables in thecurrent database, whose name starts with 'sys'my query is:select o.[name], sum(i.[rows]) as numbersfrom sysobjects oinner join sysindexes ion (o.[id] = i.[id])where o.[name] like 'sys%'and i.indid=1group by o.[name]the result of the query is (depending of the database) :name numberssysaltfiles 14syscharsets 114syscolumns 4934syscomments 2035sysconfigures 38sysdatabases 7sysdepends 5524sysdevices 6sysfilegroups 1sysfulltextcatalogs 0sysfulltextnotify 0sysindexes 98syslanguages 33sysmessages 3795sysobjects 1285syspermissions 806sysproperties 0sysreferences 0sysservers 1systypes 26sysusers 14sysxlogins 3But when I count the number of rows of "sysobjects", I getnumbers=1298, which is different from the result displayed above andsame goes for "syscolumns".I also tried "dbcc updateusage [0]" to update the SQL Server but Ididnt help me.Can anyone please advice me on this behaviour?
I am working on a report and the data source is Teradata. now I have situation where I want to get order id details based on the current quarter and year I am posting this same data. For TD related queries I do not where to post.
I am having problem with the CRecorset::Update() function. I have declared a CRecordset object in client Application in VC++6.0. I open the recordset in dynamic mode. Before opening the recordset , I have obtained the UPDLOCK on the base table.( Obviously this UPDLOCK is in transaction). When I try to call CRecordset::Update() it gives me exception 16931(There are no rows in the current fetch buffer). I have define a clustered index on this table. But I still get the exception 16931 on calling CRecorset::Update().
Maybe someone here can help me out: I have a Kimball type II dimension, where i track changes in a hierarchy. Each row has a RowStartDate and RowEndDate property to indicate from when to when a certain row should be used.
Now i want to load facts to that table. So each fact will have a certain date associated with it that i can use to lookup the right Id (a certain SourceId can have mulitiple integer Ids when there are historic changes) and then load the facts.
Is there a building block I can use for that? I could do this with SQL scripts but the client would prefer to have as much as possible done in SSIS. The Lookup transformation will only let me specify an equal (inner join where A=B) join, but i need equal for one column (SourceId) and then >= and <= (RowStart and RowEnd) to find the right row version.
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False 'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i ' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
Hi all!I'm trying to write a T-SQL statement that will allow me to do a maintenance for all user table for all databases on my server.This is what I got so far :DECLARE @cStatement varchar(255)DECLARE @dStatement varchar(255)DECLARE T_database CURSOR FOR SELECT '[' + CONVERT(varchar(64),name) + ']' FROM master.dbo.sysdatabases WHERE dbid>6SET nocount ONOPEN T_databaseFETCH NEXT FROM T_database INTO @dStatement WHILE (@@FETCH_STATUS <> -1)begin-- LOOP IMBRIQUÉ POUR PASSER AU TRAVERS DE CHAQUES ELEMENTS-- DE LA BASE DE DONNÉES ACTUELLE.EXEC ('DECLARE T_cursor CURSOR FOR SELECT ''UPDATE STATISTICS ['' + CONVERT(varchar(64),name) + '']'' FROM ' + @dStatement + '.dbo.sysobjects WHERE type = ''U''')OPEN T_cursorFETCH NEXT FROM T_curosr INTO @cStatementWHILE (@@FETCH_STATUS <> -1)beginPRINT(@cStatement)FETCH NEXT FROM T_cursor INTO @cStatementendDEALLOCATE T_cursorFETCH NEXT FROM T_database INTO @dStatement endDEALLOCATE T_databaseBut I get a sh**t load of errors :Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Msg 16916, Level 16, State 1, Line 15A cursor with the name 'T_curosr' does not exist.Please help!!! :beer:
