SQL Server 2008 :: Monthly - Updated Values - Keep For Reference
Oct 18, 2015
I have a table with constantly changing data - stock.
I want to start monitoring the value of stock at the end of each month. I can do this with a simple query, export the results to Excel and store on the network.
My question is:
Are there better ways to do this with SQL Server?
I thought of a monthly "job" that does the query and outputs to a file. (Need to be able to look at each month separately though for trend monitoring.)
Then wondered, if I should have an extra table to store the data and write queries on that in the future?
View 1 Replies
ADVERTISEMENT
Sep 22, 2015
I am trying to split the annual cost into monthly numbers based on the contract Period.Since the contract period varies from company to company not sure how to implement the logic.
create table #Invoice
(
Company Varchar(50),
Startdate2015 DateTime,
EndDate2015 DateTime,
ContractPeriod2015 Int,
ContractAmount2015 Float,
[code]..
View 3 Replies
View Related
Feb 20, 2015
In the Reference_Master table I have Total count based on date for each RefCode. Can I get the total count of each RefCode by monthly wise for each year
Reference_Master
RefCode Date Count
100012/18/20074
100012/19/20078
100012/20/20074
100022/18/20073
100022/19/20072
100022/20/20076
[Code] ...
I need the result as below
Result
RefCodeMonth/YearTotalCount
10001FEB-200716
10002FEB-200711
10003Mar-20077
10004Mar-20073
10005Mar-20072
10001FEB-200816
10002FEB-200811
10003Mar-20087
10004Mar-20083
10005Mar-20082
View 8 Replies
View Related
May 14, 2014
I want to aggregate to monthly values for the reading. I want to display Reading value for Oct 2010, November 2010 likewise My question is simple and I have tried to follow the etiquette.
Currently it is displaying.....
MeterIDReadingdateReading
3969 22/10/2013 0:150
3969 22/10/2013 0:300
3969 22/10/2013 0:450
3969 22/10/2013 1:000
3969 22/10/2013 1:150
3969 22/10/2013 1:300
3969 22/10/2013 1:450
3969 22/10/2013 2:001
3969 22/10/2013 2:150
MeterId int
ReadingDate datetime
Reading real
-===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
(
meterID INT PRIMARY KEY,
Readingdate DATETIME,
reading real
)
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY
SELECT '4','Oct 17 2013 12:00AM','5.1709' UNION ALL
SELECT '4','Oct 17 2013 12:15AM','5.5319' UNION ALL
SELECT '4','Nov 17 2013 12:00AM','5.5793' UNION ALL
SELECT '4','Nov 17 2013 14:00AM','5.2471' UNION ALL
SELECT '5','Nov 17 2013 12:00AM','5.1177' UNION ALL
SELECT '5','Nov 17 2013 14:00AM','5.5510' UNION ALL
SELECT '5','Dec 17 2013 15:00AM','5.5128', UNION ALL
SELECT '5','Dec 17 2013 16:00AM','5.5758' UNION ALL
Output should display as
MeterId Period Reading
4 Oct 13 10.20
4 Nov 13 10.40
5 Oct 13 10.20
5 Nov 13 10.40
4 Dec 13 11.15
View 4 Replies
View Related
May 18, 2007
Hi all, I'm new to MDX and am getting very confused with a script.
I'm running into problems breaking down monthly measures to daily values. If I have a monthly measure of 50, I would like to divide it by the number of days in the month to come up with daily values.
I believe I have set the proper granularity for the measurement relationship against the time dimension and have added the following script to my MDX script:
[Date].[Date].Members = [Measures].CurrentMember / [Date].[Calendar].CurrentMember.Parent.Children.Count
When I submit a query like this:
SELECT [Measures].[Measurement Objective] ON 0,
MTD([Date].[Calendar].[Date].&[20070207]) ON 1
FROM [Cube]
Everything looks good. The query returns seven rows, each with a properly scaled version of the monthly measurement. However, when I write the following query to return a single MTD value:
SELECT [Measures].[Measurement Objective] ON 0
FROM [Cube]
WHERE MTD([Date].[Calendar].[Date].&[20070207])
It doesn't work. It gives me the error:
The MDX Function CURRENTMEMBER failed because the coordinate for the
'Calendar Year' attribute contains a set
I'm sure this is just a matter of me misunderstanding MDX. Any help would be appreciate.
Thanks,
Richard
View 5 Replies
View Related
Jul 15, 2015
I wanted to know if it is possible to run a stored proc (report) when a new record is inserted / updated into a certain table. Also if it could do so for records with a column that meet a certain criteria ie. sell description like '%test%'
View 2 Replies
View Related
Jul 9, 2015
I am looking to created a trigger that inserts records into a log table to show the stored porcedure that has updated a specific column to a specific value in a specific table
At present I have:
CREATE TRIGGER [dbo].[trUpdaterTable]
ON [dbo].[t_account]-- A DB level trigger
FOR UPDATE
--Event we want to capture
AS
IF update (document_status)
[Code] ...
However this doesn't appear to bring back the procedure that triggered the update...
The value the trigger should update on is document_status = 0
DDLProcExecutedByEventDate
NULLNULLLOMBDAadministrator2015-06-25 07:42:01.677
NULLNULLLOMBDA im64602015-06-25 07:51:34.503
NULLNULLLOMBDAadministrator2015-06-25 07:52:01.610
NULLNULLLOMBDAadministrator2015-06-25 08:02:01.417
CREATE TRIGGER [dbo].[trTableupdateaccount] ON [DoesMore].[dbo].[t_account]
[Code] ....
View 9 Replies
View Related
May 28, 2008
Hi
I am very new to analysis services and using MDX.
I want to select data from a cube using an MDX statement and show the data on a graph report.
I want to select the daily, weekly, monthly and quarterly descriptions all in one column to make it easy to represent it on the report.
Then set the 'Date' Column to the x-axis and the Value column to the y-axis.
The user also must have the option to not show certain periods (Switch of daily and weekly)
My MDX works when I select from the SQL Management Studio but as soon as I copy the MDX over to the SSRS Report Designer is splits the daily, weekly, monthly, quarterly and yearly values into seperate columns which makes it very difficult to report on.
----
Code
SELECT NON EMPTY { ([Measures].[ValueAfterLogic])} ON COLUMNS,
NON EMPTY { [KPI Values].[KPI Name].[KPI Name].ALLMEMBERS * ORDER(
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Day Of Month] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Week Of Year Name] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Month] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[Quarter Of Year Name] ELSE NULL END +
CASE 1 WHEN 1 Then [Time].[Hierarchy].[YEAR] ELSE NULL END,
[Measures].[ValueAfterLogic],DESC)
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
(SELECT ( {[KPI Values].[KPI Id].&[{97754C54-AB43-403D-A2C2-21C04BDE93E3}] } ) ON COLUMNS
FROM [Workplace])
WHERE ( [KPI Values].[KPI Id].&[{97754C54-AB43-403D-A2C2-21C04BDE93E3}])
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
The case statement will take paramter values when finished
----------------end of code portion
Is this possible or is it suppose to 'split' the columns when moving to SSRS.
Thans in advance
Dev environment - SQL 2008 Feb CTP, VS 2008
View 5 Replies
View Related
Sep 15, 2007
Hello,
Using VS 2008 Beta 2, SQL CE 3.5, on desktop, and Typed Datasets: The INSERT command of dataset table adapter does not return the updated identity of inserted row. Why?
also every time I want to modify the insert command to return the updated identity of inserted row, i get the error: "Unable to parse query text."
(Should I post this in Orcas forum?!)
Regards,
Parham.
View 5 Replies
View Related
Jan 30, 2014
i am using sql server 2008 developer edition on in my desktop pc.i created a database in which there's a table that contains a column named "images" with a data type image for storing images in database.And all is working very fine No issue in this.
the problem arise after i copied the .mdf and .ldf files from my desktop to my laptop which also have same sql server 2008 developer edition.
Coming to the main issue "is I am not able to To save ,update images in that table from my laptop.i tried the best of what i could do but unsuccessful. i formatted my entire laptop as i thought of;basically the same thing works flawless in my desktop but not in my laptop.
View 3 Replies
View Related
Sep 20, 2006
I see some strange behavior when running a package using the SQL Server Agent. The package I run calls a number of child packages, in which I use property expressions to set - among other things - the path to text files I read into a database.
The property expressions are in the form: @[User::ThePath] + "\TheFile.txt". The variable ThePath is read from an XML configuration file at runtime from the "master" package and passed on to the child packages using a parent package variable configuration.
My problem is this: When the package is executed by the SQL Server Agent, the properties for the path of the text files are not updated, and the package fails, as it cannot locate the files. When using DTExecUI to execute the same package, everything runs fine.
Could it be a permissions issue? I don't think so, as the SQL Server Agent runs under the same account as I am logged in when executing with DTExecUI.
Anyone?
View 5 Replies
View Related
Mar 6, 2008
Hi all. I have this problem:
on insert in a new table a have to change one column before insert.
I wrote this trigger:
create trigger SUBSCR_ID_TRANSFER ON dbo.SalesOrderExtensionBase AFTER INSERTAS BEGIN SET NOCOUNT ON;DECLARE @OpportunityID uniqueidentifier;DECLARE @subscrId uniqueidentifier;declare @salesorderid uniqueidentifier;set @salesorderid = (select SalesorderID from inserted)SET @OpportunityID = (SELECT OpportunityId FROM SalesOrderBase where SalesOrderID=@salesorderid)SET @subscrId = (SELECT New_old_subscridId from OpportunityExtensionbase where OpportunityID=@OpportunityID)Update inserted set New_old_subscridId = @subscrIdENDbut SQL Rise the error "The inserted values can not be modified"
how to slove this issue.
Thanks.
View 5 Replies
View Related
Aug 26, 2015
I have two tables.
Emp:
id | Deptno | sdate | edate | locid
1 | 10 |2014-09-19 |2009-09-25 | 100
1 | 10 |2014-11-06 |2009-11-06 | 100
1 | 10 |2014-12-11 |2014-12-14 | 100
1 | 10 |2009-07-01 |2009-07-20 | 100
2 | 20 |2007-07-11 |2007-07-17 | 104
Empl1:
Id | Deptno | edate | refid | refid1
1 | 10 | 2014-11-06 | 201 | 204
1 | 10 | 2014-12-13 | 204 | 202
1 | 10 | 2014-12-12 | 207 | 124
1 | 10 | 2003-05-04 | 105 | 103
2 | 20 | 2007-07-14 | 102 | 106
I am trying to combine these two tables into the output below:
Id | Deptno | locid | current_refid | current_refid1 | last_refid | last_refid1
1 | 10 | 101 | 201 | 204 | 204 |202
2 | 20 | 104 | 102 | 106 | 102 |106
The tables share the common columns id and deptno. To get the above result, the emp1.edate must be between emp.sdate and emp.edate.
If that condition is met, we need to retrieve the refid and refid1 values corresponding to the lowest edate as last_refid and last_refid1. Then we need the refid and refid1 values corresponding to the emp1.edate between emp.stdate and emp.edate to be retrieved as the current_refid and current_ refid1.
select a.id,a.deptno,a.locid,b.refid,b.refid1
from
emp b
[code]...
View 1 Replies
View Related
Jan 29, 2015
I am new in SQL and i need do a query where I need sum values from 2 tables, when i do it the Sum values are not correct. this is my query
SELECT D.Line AS Line, D.ProductionLine AS ProductionLine, D.Shift AS Shift, SUM(CAST(D.DownTime AS INT)) AS DownTime,
R.Category, SUM(Cast(R.Downtime AS INT)) AS AssignedDowntime,
CONVERT(VARCHAR(10), D.DatePacked,101) AS DatePacked
FROM Production.DownTimeReason R
left JOIN Production.DownTimeHistory D
[Code] .....
View 3 Replies
View Related
Jun 8, 2015
I have a table Tbl1 which has 7 columns.This table will be my base table.By using our current application version ,i'll be creating record for Client1. Col1 will have value that application will generate(id).Then i'll be creating Tbl2 with same columns.Then i'll be creating same record for Client1 again ,using our new application version .Col1 will have different (id)value.I would like to compare the rest of the columns if there is any discrepancy caused by new version(columns Col2 -Col7).If there are same ,don't show me anything.
View 9 Replies
View Related
Jul 17, 2015
I have a table of raw data with supplier names, and i need to join it to our supplier database and pull the supplier numbers.
The issue is that the raw data does not match our database entries for these suppliers; sometimes there are extra periods, commas, or abbreviations (i.e. FedEx, FederalExpress, FedEx, inc.) etc. I'm trying to create a query that will search for entries that are similar.
I tried setting a variable to be equal to the raw data field, and then using a LIKE '%@Variable%' to try and return anything that would contain it, but it didnt return any rows.
View 9 Replies
View Related
Jul 29, 2015
Why can i get the numeric values from this string?
{_cpn}=1743; {_cpnll}=4511
Result: 1743, 4511
Position and len of the value can be different...
View 6 Replies
View Related
Sep 17, 2015
I want to concatenate the column_names with the respective values.
Create table #test1 (Id int,Name varchar(10),Country varchar(10))
insert into #test1 values ( 1,'JOHN','USA'),
(2,'SAM','CANADA'),
(3,'HO','CHINA'),
(4,'RAM','INDIA')
select * from #test1
I have temp table with columns (Id,Name, Country). I want to concatenate column_name with their respective values; means i want column_header with every value for the column and then i want to concatenate multiple columns. I am looking for something like below:
ID values
1,NAME-john-COUNTRY-USA
2,NAME-SAM-COUNTRY-CANADA
3,NAME-HO-COUNTRY-CHINA
4,NAME-RAM-COUNTRY-INDIA
Note: This is just a sample .i am looking something dynamic because i have around 50 tables.i can do with query below but since i don't have static columns and table i am looking for something dynamic
select ID, (a+'-'+B) as Value from (
select ID,'NAME'+'-'+NAME as a,'Country'+'-'+Country as b from #test1
View 2 Replies
View Related
Sep 25, 2015
I have to increment sequencial values for the following:
Current Data
Col1 Col2 Col3
12.345.678 0001 32
13.456.789 0002 43
Updated Data
Col1 Col2 Col3
12.345.678 0001 32
12.345.678 0002 32
12.345.678 0003 32
13.456.789 0002 43
13.456.789 0003 43
13.456.789 0004 43
What I need is: Increment up to 3 times the values in Col2. Need to identify the first number in Col2 and increase the number up to 3.
View 2 Replies
View Related
Oct 15, 2015
I've a table that has salescode(124!080) and salesamount(125.65!19.25) and I need to split the columns. Salesman(124) has commission(125.65). Here is the DDL:
USE tempdb;
GO
DECLARE @TEST_DATA TABLE
(
DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
, InvNoVARCHAR(10) NOT NULL
, SalesCode NCHAR(80) NOT NULL
, Amount NCHAR(80) NOT NULL
[code]....
View 6 Replies
View Related
Oct 16, 2015
I need to split the amount equally into 12 months from Jan 2015 through Dec 2015.There is no date column in the table and the total amount has to be splitted equally.Guess I can't use Pivot here because the date column is not there ...How can I achieve this ?
CREATE TABLE #tbl_data (
Region Varchar(25),
Amount FLOAT,
[code]...
View 4 Replies
View Related
Oct 20, 2015
I have imported a txtfile to access and have some problem making the data appear in my wanted way. I want to shift the cells in the first row one step to the left, i.e to overwrite the zeros with the left value. Then I want to assign the first row values as field names.
Current data
Field1 Field2 Field3 Field4 Field5
0 Name Major Sector Hi
karl
per
anna
Wanted outcome
Name Major Sector Hi
karl
per
anna
View 8 Replies
View Related
Oct 26, 2015
This is the database structure I setup:
--Main Table
CREATE TABLE [dbo].[tbl_RF_Items](
[ItemID] [int] IDENTITY(1,1) NOT NULL,
[ProgramID] [tinyint] NOT NULL,
[StatusID] [tinyint] NOT NULL,
[Item] [nvarchar](256) NOT NULL,
)
--Mapping Table
CREATE TABLE [dbo].[tbl_RF_Tags_Map_Items](
[TagID] [int] NOT NULL,
[ItemID] [int] NOT NULL,
[DateModified] [smalldatetime] NULL,
CONSTRAINT [PK_tbl_RF_Tags_Item_Map] PRIMARY KEY CLUSTERED
[Code] ....
This a result set of the Items Map table so far:
TagIDItemID
1284838
1291475
1291480
8284838
8291475
8291480
10284838
10291480
62291475
Each item will have 3 tags. I am having trouble on how to filter the data. For example if i chose TagID 1, 8, and 62, the result set should return only one result. If I do an IN clause, it acts like an OR and I need something to act like an AND.It seems like the only option is to do a dynamic where clause, but there are thousands of items and that might hinder performance of the database. Is there any other option?
View 6 Replies
View Related
Dec 8, 2008
I am trying to build packages Programmatically, added a Script Task and tried to add some references from DLLs. I got an error message showing that there is no template found, and from the event viewer of windows which tells me to run 'VSTA.exe /installvstemplates' or 'devenv.exe /installvstemplates'. I have run these two command and reinstalled the SQL server 2008, but still unable to add any references to the project.
The error msg:
---------------------------Integration Services Script Task---------------------------No template information found.
See the application log in Event Viewer for more details.
View 22 Replies
View Related
Mar 29, 2007
HI, I keep getting this warning with a lookup (full cache mode) that retreives data form a table that contains the following information:
SRCE_SYS TABLE_NAME FIELD_NAME CODE ENGLISH_DESCRIPTION
STATIC STATIC PM_PC_TX_TYPE_CODE G GROSS
STATIC STATIC PM_PC_TX_TYPE_CODE E EXCESS
STATIC STATIC PM_PC_TX_TYPE_CODE F FACULTATIVE
STATIC STATIC PM_PC_TX_TYPE_CODE S SURPLUS
STATIC STATIC PM_PC_TX_RIDER_CODE BOAT BOAT
STATIC STATIC PM_PC_TX_RIDER_CODE RIDER RIDER
STATIC STATIC PM_PC_TX_RIDER_CODE CONT CONTENTS
The column tab matches SRCR_SYS, TABLE_NAME and FIELD_NAME (using constants defined in a derived column transform) .The code column comes from the source. We want to retreive the english_description colum from SRCR_SYS, TABLE_NAME,FIELD_NAME and CODE in the dataflow.
I would normally ignore the warning but sometimes, it seems that the lookup does not match any values and enabling memory restriction on the advanced tab resolve the issue and suppress the warning.
As I said, I keep getting this warning and I don't know why since there are no duplicates in the table? Am I missing something?
Thank you,
Christian
View 3 Replies
View Related
Feb 11, 2015
Which values are best prepared value for given below memory objects
Memory
Parameter
Total memory=
SQL Cache Memory=
Lock Memory=
Optimizer Memory=
Connection Memory=
Granted WorkSpace Memory=
Memory Grants Pending=
Memory Grants Success=
Cache Details:
Cache Hit Ratio=
Cache Used/Min=
Cache Count=
Cache Pages=
Scheduled Jobs:
Job Status=
Run date & time=
Job Time=
Retries Attempted=
Need to know the above performance counters prepared values.
View 2 Replies
View Related
Mar 13, 2015
I have results that are XML data and I am trying to figure out how to parse the data to select certain values from the xml.
example
<InformationRequest xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" teamid="TEAM003341507" playerid="PL341508" gameid="G000000852" playertype="Starter" FolderName="Test" CurrentYear="2015" Ultimateid="P00000688505" xmlns="http://schemas.sports.com/Messages/Stats" />
I would like to write a statement that just pulls the game id G000000852. So just the id right of gameid=.
Not really sure where to start. Table is GAME, and column is XMLDATA.
View 0 Replies
View Related
Mar 20, 2015
We had a requirement that need to sum the data based on quater we will be having 12 months data in the system for an year suppose we have 12 records for 2014 year. jan month sales data should be same when we were in feb month it should sum jan+feb sales and should show in sales column whereas we were in march month it should sum jan+feb+mar sales, then same for next quater also apr month it wil be same value in may it should be apr+may in may sales value etc ....
We will be having date column values as 201401,201402,.....
How can we implement in sql sever performance should be good.
View 1 Replies
View Related
May 13, 2015
I am stuck on a query where I need to display all the month year values even if the corresponding count_booking values are NULL. The requirement is to display the 13 month year period from current date.
For e.g. if the date exists in the current month then starting from May 15 go all the way back to Apr 14.
My current query works in terms of displaying all the 13 months if the count_booking values exist for all the months. However, if some months are missing the values then those months don't show up.how to display all the months even if the values are NULL ? Query below:
SELECT COUNT(m.BOOKING_ID) AS count_booking, LEFT(DATENAME(MONTH, m.CREATE_DT), 3) + ' ' + RIGHT('00' + CAST(YEAR(m.CREATE_DT) AS VARCHAR), 2)
AS month_name
FROM MG_BOOKING AS m
WHERE (m.CREATE_DT >= DATEADD(m, - 13, GETDATE()))
[code]...
View 8 Replies
View Related
May 25, 2015
For finding size values of temp database:
select * from sys.master_files -
size column value here is 1024 for .mdf,size here for .ldf is 64
select * from tempdb.sys.database_files -
size column value here is 3576 for .mdf,size here for .ldf is 224
Why is there a difference and not the same. size columns in the above 2 tables for temp db's do they represent different values ?
View 1 Replies
View Related
Jun 11, 2015
Everytime I try to open up SSMS, I get this error:
"Values cannot be null. Parameter Name: ViewInfo"
When I press ok to login, It doesn't show me the database and system dbs aren't expandable.
View 4 Replies
View Related
Jul 16, 2015
I have created the below function and apply it on a column in a table to replace the below identified values with Blank. It works fine but i have so many different varieties of values i need to add to ths list. Is there any way i insert these values in a table and call the values from that table instead of writing separate SET Statements.
CREATE FUNCTION [dbo].[sv_ReplaceChar] (@badString varchar(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
[code]...
View 4 Replies
View Related
Sep 20, 2015
I have basic knowledge of T-SQL and I am using Cursors to get the first value, the last value and the peak value and some other values from other tables. I found some examples on google but the code I am using is mixed up. I am using multiple Cursors. I need to join three tables to get the result set into the Cursor. The first example uses 2 tables.
@FirstName NVARCHAR,
@LastName NVARCHAR,
@FirstValue decimal,
@HighestValue decimal,
@LastValue decimal
-- First Cursor
DECLARE TESTCURSOR CURSOR
DYNAMIC
[Code] ....
The above code seems totally inefficient but it gives the correct result. Now I want to pull some more value and join a third table (TABLE z) in the above CURSORS and not sure how to make it working using CURSORS.I would like to use the following in the CURSORS above.
SELECT x.publishdate, y.firstname, y.lastname, y.age, z.initialValue AS FirstValue, z.HighestValue AS Highest, z.LastValue AS Last
FROM TABLE x
LEFT OUTER JOIN TABLE y
ON x.id = y.id
INNER JOIN TABLE z
ON x.id = z.id
View 9 Replies
View Related
