Tracking Sales Changes Over Time
May 15, 2008
My main datasource is a rather poorly written and documented SQL database. I am currently working in SQL Server 2000 but will be upgrading to 2005 in 6 months to a year. There are three sales order tables.
SOMAST Sales Order Master Table
SOITEMS SO Items Table
SORELS SO Releases Table
My employer wants to track how our sales orders change over time. This would be a nightly process. They want to track changes in certain columns such as price and quantity to see if they differed from yesterday and to keep those changes separate in another table or set of tables to track them. This of course would include newly entered sales orders for that day as well. Our current erp system does not support this.
This seems like a huge task to a neophyte like myself, but I am tasked with doing this. Am I correct in assuming the correct method would be a stored procedure that does the following:
1. Check the current tables at end of day today and compare them with a saved version of yesterday's tables.
2. Insert into a 3rd table (or set of them) the differences.
3. Copy today's tables over yesterday's tables so they are available tomorrow.
I realize this task is difficult, but am I at least starting in the right direction?
Experts Only Please. (jk)
View 17 Replies
ADVERTISEMENT
Jul 17, 2007
hi,
i am currently in the process of moving a bunch of jobs into SSIS from another ETL tool. I would like to benchmark the two products against each other by comparing how long each step of an ETL process would take.
I see no way to do this in SSIS, there is the Progress tab but it doesnt list start/time and end/time. Plus I having loops and things which I want to know how long each iteration takes.
Is there a way to track all this?
Thanks
View 5 Replies
View Related
Aug 23, 2014
I have to do a report for each hour compare yesterday each hour of sales amount their output is below, how to write a query.
desire output 08:00 am -23:00pm
shop today Time yesterday current Amt Yest Amt diff amount
001 13:00-14:00 13:00-14:00 $10000 $20000 -10000 (down)
002 14:00-15:00 14:00-15:00 $10000 $15000 500 (up)
Time as at HH:MM
Yesterday Total $20000
today Total $35000
View 2 Replies
View Related
Jul 27, 2006
Hi,
I have built a Sales Forecast model to predict the sales
value.
Along with making historic predictions for previous time
periods I also want to retrieve the actual sales values for those periods.
How can I achieve this in a time series model?
I also would like to know how do mining models store the data.
Do they store the data in the same table/view format
as their respecive data source view or in the Model Content format.
View 3 Replies
View Related
Jul 30, 2015
In a change tracking enabled database I can find the latest change tracking version number by using
Select CHANGE_TRACKING_CURRENT_VERSION() As Latest ChangeTrackingID.
Which will give latest change tracking id (example 1022), Is there a way to find the datetime of this latest change tracking id.
View 3 Replies
View Related
May 19, 2015
i am trying to find a way to link an 'initial' Sale ID of a product to 'future' Sale IDs of products that will trace back to the original Sale ID.For example, if I call the original sale , 'Sale ID #123', how can i link future Sale ID's (child[ren]) and all future sales to the original Sale ID #123? Can I use a Surrogate Key or similar function?
Parent:Sale ID #123
Children:Â Sale ID # 456,
Sale ID #789,
Sale ID #.....
how I can link the original Sales ID (Parent) to Sale ID's (child[ren]) of future purchases currently existing and in the future going forward?
View 4 Replies
View Related
Dec 9, 2014
I have the below data. I need to produce a report that shows customer and total sales who had the max sales by year.
Order ID Cust ID Year Sales
O1 C1 2000 100
O2 C1 2000 150
O1 C2 2000 50
O1 C1 2001 150
O2 C3 2001 200
Report:
Cust ID Year Sales
C1 2000 250
C3 2001 200
View 1 Replies
View Related
Sep 30, 2015
I want to calculate the sum of actual sales until a date and forecast sales after a date.I am not sure what the best approach to this problem is, but I have tried my best with the following approach. Any better ways to solve this (using DAX).
I have created a parameter table that offers the last date of each month as possible choices to the user. I have tried to create a measure that sums actual sales up until this date.
SalesQuantityActual:=IF(HASONEVALUE(parLastActualMonth[Date]);CALCULATE(factSalesActual[Quantity];factSalesActual[Date]<=VALUES(parLastActualMonth[Date]));BLANK())
Unfortunately the measure above does not work.
In addition to the parameter table, I also have a normal date table.
View 2 Replies
View Related
Nov 26, 2007
I have a report which totals sales by customer. Then table footer has a grand total of all customer sales. I would like to get a percent of each customer's sales against the total sales. How do I get the sum from the table footer to use in an individual customer row?
Thanks.
View 3 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
Sep 26, 2003
Please Help.
How do you track changes to objects in SQL Server.
For example changes in stored procedure,views and indexes. What system table or column track or indicate changes in text of sp or views.
Help appreciated.
Regards
View 6 Replies
View Related
Aug 9, 2006
We are building an inventory management system complete with BOM. It is important to track what employee is updating what tables. Currently all such tables have a Date field that is updated when a change is made, and an EmployeeID field which records the employee making the change. I am wondering if someone knows of a better way to track this information. Any suggestions?
View 2 Replies
View Related
Aug 26, 2004
Hi,
I am trying to create a tracking spreadsheet for my SQL Servers in order to keep track of every thing.
Here is a example of what I have:
DateTracking IDServerIssuesSolution
8/26/20046760FORMSCAPEDOCSBackup failSwitch Transaction logs to the D: and left the database backup on the E:
Should I be more detailed and add some more fields and if yes can you recommend some.
Thanks
View 2 Replies
View Related
Mar 6, 2006
I have an application that uses SQL server. Is there a way to find out what SQL statements the application is passing to the SQL server?
I would like to know all kinds of activities going on in the background of the application. I am sure there is a way.
Thanks in advance.
View 11 Replies
View Related
Jul 20, 2005
OK. For DDL, please refer to the classical Northwind ORDERS table,problem/challenge, find the longest duration (start_date andend_date), during which, no orders were placed.FYI, column names by the order of colid per syscolumns:OrderIDCustomerIDEmployeeIDOrderDateRequiredDateShippedDateShipViaFreightShipNameShipAddressShipCityShipRegionShipPostalCodeShipCountryAny idea/approach? TIA.
View 1 Replies
View Related
Sep 5, 2007
Hi
I'm trying to get CDC going, it works however when i query the LSN using the functions i get no changes. The min and max LSN returns null. SQL agent is running, db is on full recovery model etc.
any ideas ?
thanks
View 4 Replies
View Related
Dec 19, 2007
Yesterday I had problem with that a sp that contained join that the developer had forgotten the where part. The sp join very large tables and sp took more 95% dual itanium processor. How do fastest track this down when it accrues. Find the sp or sql that running and consume this much processor resources. I tried with a lot. Sp_who, sp_who2, activity monitor.
View 5 Replies
View Related
Apr 25, 2005
Hi.
I’m having a conceptual problem with tracking sales vs. call center calls.
Each record in the fact table represents a call to the call center.
In this record are various facts and foreign keys that map to marketing campaigns, etc.
The product sold ID is NULL on no sale, and filled in for a sale.
When creating MDX to retrieve data by campaign, for example, to track the number of calls for a campaign vs. it’s sales, that works fine (because there’s a column labeled sale that’s either 0 or 1 for the sale and I just sum it). This way I get the conversion percentage of calls to sales.
But when creating MDX to track the product sold, as soon as I do a crossjoin on campaign and products sold, for example, I lose the total calls- the number of calls is the same as the sales (similar results as a SQL join). Am I doing something wrong or is it conceptually impossible to track this type of metric down to the product when there are NULLs in the fact table? I’ve tried converting the nulls to a NONE category, but that doesn’t stop the crossjoin from not giving me the desired results, plus I then have to filter out NONE as a product.
Is that why a lot of sample warehouses have a separate sales cube?
Thank you,
Richard
View 2 Replies
View Related
Nov 15, 2014
I need to get the sum of sales for the last date of each month group by custom and by month(fecha)
for example, for custom ='q' in month=8 I have 3 items in the last day of the month ='2014-08-15' totalling 13 and so on
DECLARE @sales TABLE
(custom VARCHAR(10) NOT NULL,
fecha DATE NOT NULL,
sales NUMERIC(10, 2) NOT NULL);
INSERT INTO @sales(custom, fecha, sales)
VALUES ('q', '20140708', 51),
[Code] .....
View 2 Replies
View Related
Jun 7, 2006
I have a table tblSales ( DollarAmount,DateSold, Barcode --- ) in SQL MSDE 2000.
What I want is the Top 5 sales for EACH month:
Month TotalSales Barcode
2006-05 Top01 Barcode01
2006-05 Top02 Barcode02
2006-05 Top03 Barcode03
2006-05 Top04 Barcode04
2006-05 Top05 Barcode05
2006-04 Top11 Barcode11
2006-04 Top12 Barcode12
2006-04 Top13 Barcode13
2006-04 Top14 Barcode14
2006-04 Top15 Barcode15
2006-03 Top21 Barcode21
2006-03 Top22 Barcode22
---- --- ----
--- ---- ---
TopNN is SUM(DollarAmount).
I created a table AAAA and use a while loop to insert the data into AAAA.
What I did is:
declare @StartDate and @EndDate, set @StartDate and @EndDate
Delete AAAA
Begin
While (@EndDate<GetDate())
Begin
Insert into AAAA(sales, Month, Barcode)
Select TOP 5 SUM(DollarAmount) AS sales,
LEFT(CONVERT(CHAR(8),DateSold,112),4)+'-'+RIGHT(LEFT(CONVERT(CHAR(8),DateSold,112),6),2) AS Month,
Barcode
from tblSales
where something
group by something
order by sales1 DESC
increase @StartDate and @EndDate by a month
End
Select * from AAAA
It works fine. My question is: Can I get rid of table AAAA?
How can I use T-SQL only to get the correct sets?
Thanks in advance.
Long
View 4 Replies
View Related
Dec 26, 2006
I am quite newbie, really grateful for some help how to create a sql sentence in Reporting Services that would return the best sales person from each shop.. The following returns all the sales persons from each shop
So how to select the top sales person from each shop?
SELECT TOP (10) shop.name, SUM(Cd.Price) AS Sales, Personal.Name AS Salesperson
FROM Purchase INNER JOIN
Personal
ON Purchase.Salesperson_id = Personal.Personal_id RIGHT OUTER JOIN shop
ON Personal.work_id = shop.shop_id FULL OUTER JOIN
Cd ON Purchase.Cd_id = Cd.Cd_id
GROUP BY Shop.Name, Personal.Name
ORDER BY Sales DESC
Or something like this? But how in Rep.Services???
...LEFT OUTER JOIN (
SELECT P.work_id, P.Name, SUM(C.Price) AS TotalSale
FROM @Personal P
INNER JOIN @Purchase B
ON B.Salesperson_id = P.Personal_id
INNER JOIN @Cd C
ON C.Cd_id = B.Cd_id
GROUP BY P.Work_id, P.Name
) D
ON D.Work_id = S.Shop_id
View 4 Replies
View Related
Aug 28, 2006
Above says it. I'm trying to select a random 5% of sales for each salesman for the last day serviced. I've got it all down, but I can't get it down to salesman. I'm using a basic select:
name, address, order, order_id, etc where order_id in (select top 5 percent order_id from MYSERVER group by salesman, order_ID order by newid())
The actual statement's much longer and has more where statements, but the pertinent info is above. All I can manage to do is create very long-winded ways to pick a random 5% of all sales.
View 7 Replies
View Related
Jun 16, 2004
I'm looking for a way to monitor I/O(select, delete, & updates) to a particular SQL Table for a period of time. Any suggestions?
View 3 Replies
View Related
Mar 7, 2006
im trying to create a procedure that will insert/update a small table
that has an ID, AmtBefore & AmtAfter.
this is just a table that i'd be using to monitor other activity on the database.
i'm really pulling a price + ID every hour and i need to track that somehow and know when the procedure runs if a price is lower than it was the last hour, plus of course keep the ID's straight.
does anyone have a good approach to something like this?
thanks for any help on this
rik
View 2 Replies
View Related
Aug 9, 2006
We are building an inventory management system complete with BOM. It is important to track what employee is updating what tables. Currently all such tables have a Date field that is updated when a change is made, and an EmployeeID field which records the employee making the change. I am wondering if someone knows of a better way to track this information. Any suggestions?
View 4 Replies
View Related
Sep 11, 2006
Hi all,
I have several transaction tables on which I need to track the changes. That is I need to maitain
the history of changes. Only few column values are changed often.
Which is the best way for tracking the changes.
1.Store the whole record after the change ?
Or
2.Store the ColumnName & its respective old & new value ?
Or any other better.
Note : UI part & SP's will take care of the tracking & no plans for triggers.
Thanks in advance,
HHA
View 2 Replies
View Related
Apr 4, 2007
Hi all how do I track when a users opens, enters data in a database. How can I track them??
View 1 Replies
View Related
Feb 6, 2004
I have a table in my database and it holds some important information,I want to track the users
who are executing DML commands(select,update,delete) on the data in that table.
Is there any way do that?If so how could i implement that.can any one help me in this regard?
Thanks in advance...
View 7 Replies
View Related
May 10, 2008
Hey everybody,
First thank you for all your help thus far. Now I'm stuck again. I've been doing a lot of reading on triggers and logging information into tables but I've been trying to capture how many times someone enters an item into the search box.
So every time somebody types Gumballs into the search box I want to capture it and the name of the person who is currently logged in. Is there away to do this? Maybe this is something that I should be checking in ASP.NET forums?
Thanks in advanced guys!
View 2 Replies
View Related
Jun 20, 2007
Hello everyone, I have a fairly unique need :) I am trying todetermine the use/clients for databases in my corporation that I ammaintaining, but that noone seems to know what they are for. Many ofthese databases never seem to have anybody connected to them in thecurrent activity.What I'd like to do is find a way to audit the logins, so everytimesomeone connects to a database it simply logs the clients IP address,what login they used, and maybe what time. I've been searching googlefor this and have found tons of information on auditing the logins,but not the clients, such as by ip. Any help in this regard would beGREATLY appriciated!Joshua
View 2 Replies
View Related
Nov 28, 2006
I have a device application that simply needs to upload data to a server. The preferred DB server is Oracle but I've made it work using RDA and SQL Server. The problem I'm having is that it just needs to upload data, whichh I send using the RDA.Push() method. The data arrives just fine, the first time. With every subsequent upload all of the previous data is deleted fromt he server. Apparently RDA is tracking the deletion of the previously uploaded data locally and on the next .Push deleting that data from the server.
My question is: Is it possible to prevent RDA from deleting data on SQL Server? I attempted to delete the rows from the __sysDeletedRows/__sysRowTrack tables but got a "Data is read only" error.
View 1 Replies
View Related
Oct 4, 2007
I hoipe someone can point me in the right direction here.
I have an application with the following requirements (using SQL CE 2 alas)
A set of tables on the server that need to be imported to the handheld. Using rda, I need to get the modifications to these tables from the server (add/edit/delete) but the handheld will never update these tables.
A set of tables on the server that need to be imported to the handheld. The handheld needs to add/edit existing records, and it needs to get any changes from the server.
A set of tables on the server where the handheld needs to import a subset of the records. It needs to add (but not edit) new records, upload the new records to the server, and download any changed (add/edit/delete) records to the handheld.
What tracking options should I use in these 3 cases?
The problem comes in that I need to have some foreign key relationships in the database on the handheld. Since rda munges the names of primary keys (and indexes), I do not know of a good way to add these foreign key constraints. Any suggestions?
Thanks
View 2 Replies
View Related
Sep 21, 2007
Hi,
I want to keep track of data change and want to enter the pk such as testkey and timing in different table. is it possible without having to write trigger on the table!!!
View 1 Replies
View Related
