Transact SQL :: Accumulate Values Based On Time For Each Day
Jun 10, 2015
I have a table Mytable which has two fields (Date, Value) and record is based on every hour like this:
Date Value
1999-01-01 00:00:00 10
1999-01-01 01:00:00 8
1999-01-01 02:00:00 6
...
1999-01-01 23:00:00 12
1999-01-02 00:00:00 9
1999-01-02 01:00:00 5
...
1999-01-02 23:00:00 2
How can I use a query to get accumulate value for the day? the result should look like this:
Date Value Accumulated_Value
1999-01-01 00:00:00 10 10
1999-01-01 01:00:00 8 18
1999-01-01 02:00:00 6 24...
1999-01-01 23:00:00 12 36
1999-01-02 00:00:00 9 9
1999-01-02 01:00:00 5 14...
1999-01-02 23:00:00 2 16
The accumulated value should be based on every hour for the day and then repeat for the second day and so on.
What is the best way to do this? I am using SQL 2008
View 2 Replies
ADVERTISEMENT
Apr 28, 2015
I have a log table in which alarm events are logged (a many relation to a main table) where EventId is the foreign key in the log table.
In this table all events related to a given incident (EventID) are logged, but it is specifically two eventtypes which is of interest, thats the "into alarm" represented by an integer value "0" in the column "EventTypeId" and the "restored represented by an integer value of "2". Each state of change has a timestamp of datetime.For each incident (EventId) there can be multiple "into alarm" and "restore" rows, there can also be other events which has no interests. Only the timediff between each "0" and "2" accumulated is of interest.
There's one more little twitch, it can occure that when the query is run, the "2" restored state is not yet present, the query should not return a result for that "0" into alarm when the "2" restored is not present, unless there was a complete alarm/restore session.
CREATE TABLE [dbo].[AlarmHistoryLog](
[EventId] [int] NOT NULL,
[EventSeq] [int] IDENTITY(1,1) NOT NULL,
[EventTypeId] [int] NOT NULL,
[EventTime] [datetime] NOT NULL,
[EventDescription] [nvarchar](250) NOT NULL,
[Comment] [nvarchar](4000) NULL,
[UserID] [int] NULL,
[ReceiverID] [int] NULL
) ON [PRIMARY]
View 5 Replies
View Related
May 24, 2008
How would be a query that read detailed sales from several dates and accumulated them in a table that has months as fields
MSSQL 2005
Table : DETAIL
Customer_Date___________Value
1________01-Jan-2008____1,234.00
2________01-Feb-2008____3,456.00
3________01-Mar-2008______500.00
4________01-Apr-2008____1,567.00
1________01-Apr-2008____2,123.00
Table : TOTAL
Year__Customer_Jan______Feb______Mar_____Apr ...
2008__1________1,234.00_0________0_______2,123.00
2008__2________0________3,456.00_0_______0
2008__3________0________0________500.00__0
2008__4________0________0________0_______1,567.00
Thanks for your help
JG
View 5 Replies
View Related
Nov 2, 2015
I have some transactions with the same card number that needs to add value amount to its existing balance. For example:
Card Number Balance Amount Issue Date Issue Branch.
4000111122223333 $100.00 10/1/2015 123 <= This is an existing row in Card Number SQL table.
Now, the same card number with additional $50 dollars that I want to add to this card number to make the total to become $150. This additional $50 is from another transaction table. On the contrary, I will have -$20 from the same card number in different transaction that I will need to deduce $150-$20 to become $130. How can I update the card number table with debit/credit transactions to keep the outstanding balance?
View 4 Replies
View Related
Nov 2, 2015
I have the following result query :
Which is produce by following SP :
SELECT
P.StoreID,
P.InventoryDate,
P.ProductID,
SUM(PIT.Quantity * P.ItemUnitWeight) AS TotalWeight,
SUM(PIT.UsedQuantity * P.ItemUnitWeight) AS UsedWeight,
[code]....
What is the way to add a calculated columned Named "Stocked" which will represent the sum of "Remaining" column for each rows ?
View 9 Replies
View Related
Jun 12, 2015
I need swapping values in rows of tables based on primary.
I will explain my scenario with the below example.
ID Name Value
--- -------- --------
1 name1 202
2 name1 203
3 name2 203
4 name2 202
5 name3 204
In the above example, I want to swap Value 202 and 203 but its not straight forward. I want to swap in a way that, I need to group by Name column and check if 203 comes first in the group then swap else don't.
In the above example, I need to swap the values for name2 but not for name1. Below query is performing a straight forward swap.
UPDATE TblTest
SET Value
=
CASE
WHEN
Value
=202
THEN203
ELSE202
END
WHERE
Valuein(202,203)
View 10 Replies
View Related
Aug 5, 2015
equipmentid downtimestartdate downtimeenddate dowtime
a3er 2015-03-15 02:00 2015-03-17 23:00 69
b6e4 2015-03-18 13:00 2015-03-20 04:00 39
i have many rows(in our production table, thousands of rows are there) like above in a table and i want like below output(in output total 6rows only)
equipmentid downtimestartdate downtimeenddate dowtime
a3er 2015-03-15 02:00 2015-03-15 24:00 22
a3er 2015-03-16 00:00 2015-03-15 24:00 24
a3er 2015-03-17 00:00 2015-03-15 23:00 23
[code]...
View 2 Replies
View Related
Apr 26, 2015
I have two tables A(uname,address,full_name) and B(uname,full_name). I want to update table A for all matching case of uname in table B.
View 5 Replies
View Related
Oct 27, 2015
I'm trying to Update a column table based on values from another table but I need these values are random.My query looks like this and doesn´t work
DECLARE @Rowini int,
DECLARE @lastrow int
SET @Rowini = 1
SET @Lastrow = 80000
[code]...
View 8 Replies
View Related
Sep 22, 2015
In a table I have some rows with flag A & B for a scode, some scode with only A and some are only B flags.
I would like to fetch all rows with flag A when both flags are present, no rows with B should be fetched. Fetch all rows when only single flags are present for a scode.How to achieve this using TSQL code.
View 2 Replies
View Related
Sep 1, 2015
I have the following report I need to create with 2 parameters. An equal OR not equal. I need the report to have a drop down that has equal to '1024' or a drop down option that IS NOT equal to '1024'. I also need the WHERE clause to return the equal or not equal based on the user selection inside of SSRS.
SELECT user1 AS [Company], reference AS [PAI_REF], statenumber,
LEFT(user4, 7) AS [Supplier Code],
user4 AS [Company Information],
user8 AS [Transaction Type], user2 AS[Invoice Number],
--CONVERT(VARCHAR,CONVERT(Date, user3, 103),101) AS [Invoice Date],
[routeName] AS [Route], username AS [User Name]
[Code] ....
View 2 Replies
View Related
Jul 22, 2015
I have 2 tables each containing a material type. Table 1 contains material from their 3D application. Table 2 contains material with specific values that is not ours and we cannot rename or edit the data. I need a type of junction or mapping table that can connect the user material to the preset material. for example:
User Material = Wood-MDF
Preset Material = MDF Panel
I figured that i would make this table with 3 fields (ID, UserMaterialID, PresetMaterialID).How would i then construct a query view / Stored procedure that would return the Preset data values based on the user material id?
View 2 Replies
View Related
Sep 9, 2015
I am trying to pull the records which are being affected i.e, comparing the values and if not same then populating the record.
I am using the below condition in where clause however i am getting runt time error as "Conversion failure when converting date and/or time from character string"
Condition in Where clause:
cast(isNull(tab1.Col1,'') as datetime) <> cast(isNull(tab2.col1,'') as datetime)
Note: Both col1 columns are of type nvarchar
View 6 Replies
View Related
Aug 7, 2007
Hi all,
I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.
I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.
For example if the date is August 07, 2007 10:00 AM UTC,
then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.
Similarly for other Time Zones it should display the time accordingly.
Is this possible in SSRS 2005?
Any pointers will be usefull...
Thanks in advance
sudheer racha.
View 5 Replies
View Related
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
Nov 13, 2015
I am working with a data set containing several years' of monetary values. I have entries for past dates and the associated values, and I also have entries for future dates. I need to populate the values of the future date records with the values from the same date the previous year. Is there any way this can be done in Power Pivot?
View 6 Replies
View Related
Feb 27, 2014
I have a script that I use after some amount of data massaging (not shown). I would like to be able to change the
1) denominator value (the value 8 in line 32 of my code) based on how many columns are selected by the where clause:
where left(CapNumber,charindex('_', CapNumber)-1) = 1where capNumber is a value like [1_1], [1_4], [1_6]...[1_9] capNumber can be any values from [1_1]...[14_10] depending upon the specialty value (example: Allergy) and the final number after the equal sign is a number from 1 to 14)
2) I'd like to dynamically determine the series depending upon which values correspond to the specialty and run for each where: left(CapNumber,charindex('_', CapNumber)-1) = n. n is a number between 1 and 14.
3) finally I'd like to dynamically determine the columns in line 31 (4th line from the bottom)
If I do it by hand it's 23 * 14 separate runs to get separate results for each CapNumber series within specialty. The capNumber series is like [1_1], [1_2], [1_3],[1_4], [1_5], [1_6], [1_7], [1_8],[1_9]
...
[8_4],[8_7]
...
[14_1], [14_2],...[14_10]
etc.
Again, the series are usually discontinuous and specific to each specialty.
Here's the portion of the script (it's at the end) that I'm talking about:
--change values in square brackets below for each specialty as needed and change the denom number in the very last query.
if object_id('tempdb..#tempAllergy') is not null
drop table #tempAllergy
select *
into #tempAllergy
from
dbo.#temp2 T
[Code] ....
If I were to do it manually I'd uncomment each series line in turn and comment the one I just ran.
View 6 Replies
View Related
Oct 3, 2015
I hope to update a DateTime column value with a Time input parameter. Poor attempt below but it looks like the @ApptTime param is coming in as 10:45:00.0000000 and I might have an existing @SendOnDate as: 2015-10-05 07:00:00.000...I hope to end up with 2015-10-05 10:45:00.000
ALTER PROCEDURE [dbo].[SendEditUPDATE]
@QuePoolID int=null
,@ApptTime time(7)
,@SendOnDate datetime
[code]...
View 14 Replies
View Related
Apr 21, 2015
SELECT
CONVERT(VARCHAR(10),attnc_chkin_dt,101) as INDATE,
CONVERT(VARCHAR(10),attnc_chkin_dt,108) as TimePart
FROM pmt_attendance
o/p
indate 04/18/2015
time part :17:45:00
I need to convert this 17:45:00 to 12 hours date format...
View 8 Replies
View Related
May 19, 2006
How do I:Select f1, f2, f3, from tb1 where f1=Select f1 from tb2 where f1='condition'?
View 3 Replies
View Related
Aug 3, 2015
How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?
View 5 Replies
View Related
Jul 28, 2015
I have a string variable
string str1="1,2,3,4,5";
I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :
declare @id varchar(50)
set @id= '3,4,6,7'
set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype
select *from ehsservice where id in(@id)
But this query throws following error message:
Conversion failed when converting the varchar value '3,4,6,7' to data type int.
View 4 Replies
View Related
Apr 23, 2007
[RS 2005]
Given the starting and stopping points (time values), how do I generate values between these points.
For example, if I have 08 (representing Hour) as a starting point and 12 as a stopping point.
From this I would like to generate a data sequence like 08, 09, 10, 11, and 12.
So how do I accomplish this? In SQL or in the RS?
The only thing I can think of is using a WHILE loop and a temporary table in SQL (not to keen on doing this).
//Håkan
View 7 Replies
View Related
Aug 26, 2015
I developed the following T-SQL query that runs successfully, but I was looking for a more efficient and concise way to do this. Is there a CTE that can replace all of these case statements? I've updated my query as below. Although this sample query works, it's not working for my real data. Instead, I get an error. At the bottom is the error part of my real query.I copied all of the tables from the first query block below. But when I wrote the bottom query block, it underlined in red the words "answer" and "question." It says "Invalid column name".
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#hard_values')
) DROP TABLE #hard_values;
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#dummy_data')
) DROP TABLE #dummy_data;
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#temp')
[code]...
View 13 Replies
View Related
Aug 9, 2014
I would like to accumulate the 'diferencia' column by 'codigo'.
DECLARE @stock_prices TABLE
(codigo char(7)
,fecha date
, precio decimal(10,3)
)
INSERT INTO @Stock_prices
[Code] ...
View 7 Replies
View Related
Sep 7, 2015
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B
red - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red - pen
blue - pencil
blue - highlighter
blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
red blue green
pen 2 0 1
marker 0 1 0
pencil 1 3 0
highlighter 1 1 1
We tried Sum but not able to display in single row.
View 2 Replies
View Related
Oct 14, 2015
I am trying to audit data quality based on some defined data quality rules. The rules are stored in tables and processed using stored procedures. I am facing a problem while generating audits. Let's say I am trying to audit data in OrderDetail table. The table design is mentioned below...I inserted some sample data into the table using RedGate data generator.The audit table output I am expecting is as mentioned in the screenshot below
Its the PrimaryKeyAttributeValues column I am facing problems with. I am using STUFF function within a dynamic SQL query to get the primary key's as a list of comma separated values.
View 2 Replies
View Related
Jul 9, 2015
I have a table with 2 columns and my source data looks like this..
PolicyNum InsCode
1ABC12 1001
1ABC12 1002
1ABC12 1003
1ABC12 1004
1ABC12 1005
[Code] ....
My output should look like this..I need T-sql to get below output.
PolicyNum InsCode1 InsCode2
1ABC12 1001 1005
1ABC12 1002 1006
1ABC12 1003 1004
1ABC20 1001 1005
[Code] ...
Basically it's converting certain row values to new column. Every PloicyNum will have 1001 to 1006 Fixed InsCode values as a group.
Rule-1: InsCode value 1001 should always mapped to 1005
InsCode value 1002 should always mapped to 1006
InsCode value 1003 should always mapped to 1004
Rule-2: For a policyNum, If any Inscode value is missed from the group values 1001 to 1006, still need to mapped with corresponding values as shown in Rule-1
In the above sample data..
for PolicyNum - 1ABC20 , group values 1003,1006 are missing
for PolicyNum - 1ABC25 , group values 1002,1003,1004,1005,1006 are missing
Create Table sampleDate (PolicyNum varchar(10) not null, InsCode Varchar(4) not null)
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1001')
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1002')
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1003')
[Code] ....
View 14 Replies
View Related
Mar 24, 2014
I need accumulate the months by Quarter measurement. For example we're in March now and I need put these code in Where clause like:
Where MyDate > DATEADD(mm, -11, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) ...
here DATEADD(mm, -11, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) makes Apr of last year included because that is the first month in 2nd Quarter of last year. But in next month we're in Apr and I need make -11 as -12 in above code to include Apr of last year. And same reason if we're in Feb. 2014 then I need change -11 to -10.
So how can I calculate this to make the number fit into the code or some other way to get the same effect? I need get all last 4 quarters based on current month (include current Quarter).
View 2 Replies
View Related
Dec 12, 2007
Hi
Why can't I loop a cursor and add values to a string in Sql server ?
Code Block
ALTER FUNCTION [dbo].[fn_Get_Project_String]
()
RETURNS nvarchar(255)
AS
BEGIN
DECLARE @Prosjekt nvarchar(255), @Pro nvarchar(255)
DECLARE c1 CURSOR
FOR select nvarchar3
FROM dbo.AllUserDataCopy
OPEN c1
FETCH NEXT FROM c1 INTO @Pro
WHILE @@FETCH_STATUS = 0
BEGIN
select @Prosjekt = '<item>' + @Pro + '</item>' + ''
FETCH NEXT FROM c1 INTO @Pro
select @Prosjekt = @Prosjekt + @Pro
END
CLOSE c1
DEALLOCATE c1
RETURN @Prosjekt
END
Ivar
View 4 Replies
View Related
May 19, 2008
Hi!
Need some help building a query that does the following :
I have 2 Time Dimensions ; Time (Transdate) and ClosedDate (ClosedDate)
In my report/query, if [Time].CurrentMember = [Time].[YMD].[YMD].[2006].[200610].[20061031] I want to FILTER out all ClosedDate < [ClosedDate].[YMD].[YMD].[2006].[200610].[20061031]
Both Time Dimensions are Year -> Month -> Day and have the same Members.
I have every option available, using calculated Members and/or Measures to do this.
The report I'm creating is Aging of Receivables : Balance / 30 days / 60 days / etc.. But for the Aging, I need to filter like explained above.
Appreciate all help!
Regards,
Stian Bakke
View 3 Replies
View Related
Feb 16, 2005
can i make a trigger to fire based on given date and time?
View 2 Replies
View Related
Feb 14, 2005
While using a DateTime field, is there an easy way of filtering based on time of day? Ex. Anything that happened after 3:00 PM on any given day?
The easiest way I've found of doing so is :
Code:
SELECT *
FROM MyTable
WHERE ({ fn MOD(DATEDIFF(ss, '01/01/2000 00:00:00', [Date]), 3600 * 24) } >= DATEDIFF(ss, '00:00:00', '15:00:00'))
To me that seems a bit complicated... but it works...
Anyone that knows of a better way any help would be appreciated (or if you don't think there's a better way knowing that would help as well)
-MBirchmeier
View 5 Replies
View Related