Edit / Update Query - Change Current Sales Information For A Specific Product
Apr 11, 2012
I am currently working on a website that deals with sales of products. For one of my pages for the website I need it to be able to change the current sales information for a specific product.
The top part of the following code selects the specific product however I cannot get the update query to work.
Code:
$describeQuery = "SELECT p.ID, p.NAME, dt.[Year], dt.[Month], dt.SalesVolume FROM Products p
join
(select ProductCode, sum(SalesVolume) as SalesVolume, [Year], [Month] from MonthlySales
group by ProductCode, [Year], [Month])dt
on dt.ProductCode = p.ID WHERE [NAME] = '$desiredProduct' AND [Year] = '$desiredYear' AND [Month] = '$desiredMonth'";
$editQuery = "UPDATE MonthlySales SET SalesVolume = '$NewSales' WHERE ID = '$desiredProduct' AND Year = '$desiredYear' AND Month = '$desiredMonth'";
I have a sales order table that has customer, sales, product and sales date. I am looking to return the first sales date by customer for a specific product.
SalesTable:
Customer Product Sales Date Sales First Bike Date
Jon Bike 12/4/2011 $10.00
[Code] ....
I would like to return the date of the first bike purchase date by each customer and repeat that date for each row and customer. Can I use function to complete? I have a datedim table as well related to the sales date.
Lest Say with File-stream we have a table document where the file-stream column is located, then we have another table called product-document where the productID and the Document-node are both foreign key of the Product and the Document tables respectively. if i want to query the file-stream document for a specific product, i can just join all three tables, but with file-table there is something i probably didn't get as how to query the file table and get a document for a specific product.
We are trying to forcast product sales for next three months based on their sales for previous 12 months. In this case, Microsoft Time Series algorithm requires the sales data to be present for each product for past 12 months (???). However, our products have typical life span of 6 months and obviously the new products will not have sales before they were added. Any help will be very much appreciated.
I need to write an MDX query which will return a flag that indicates if a product has a gap of more than 2 consecutive months in terms of positive sales. So I calculate if there is a gap of 3 months. If yes, then return 1 else null. The calculation below is scoped at product level.
IIF(([month-3 sales]<=0 AND [month-4 sales]<=0 AND [month-5 sales]<=0) OR ([month-4 sales]<=0 AND [month-5 sales]<=0 AND [month-6 sales]<=0) OR ([month-5 sales]<=0 AND [month-6 sales]<=0 AND [month-7 sales]<=0) ............... OR ([month-12 sales]<=0 AND [month-13 sales]<=0 AND [month-14 sales]<=0)),1,NULL)
Unfortunately I cannot do this calculation at relational level as a product may have a gap of more than 2 months at country level but may not have this at a higher level in geography.
optimizing this as the performance is bad and other calculated measures are dependent on this. Based on this calculation, I can write a scope which sums up all products so that I get the count of products with a gap of more than 2 months in sales.
I have also tried CASE statement and NESTED IIFs but the performance is worse than the above.
I need to calculate “NET_SALES” and “MARGIN_PERCENT” for each month of the current year … the following returns the same values for each month in the list, which are for the current month. Taking out the GROUP BY line works fine for an overall number.
I have a table with all my orders lines, I need to identify (DAX formula) for a specific customer which is the first order of a specific article (first date filtered for customer and article) and use this information to tag the order line for that article for the specific customer as "Newly Ordered article" if the order date = first order date or as "Reordered article" if the date on the specific order line is subsequent to the first order date.
I imagine I need to combine a Lookup date filtered for customer and article and use it with a IF formula.
Similar to this I would also like to define that if the first order for a product for the specific custumer is older then a certain date, then this would be defined as and historical customer for the specific article, if the first order on the article is more recent the a specific date, then this will be defined as "new customer for that article".
We are trying to setup a Virtual Machine for our developers and would like to put SQL Server 2014 (Developer Edition)pre-installed. Each of our devs have their own MSDN accounts so they would have access to their own installers / product keys.So, two questions:How do we pre-install it without a product key or install it then remove the product key?How can they change the product key once they boot up?
Riding MVJ's excellent F_TABLE_DATE function, I often use this view for current date and time information.SELECTCURRENT_TIMESTAMP AS Now, DATEADD(MINUTE, DATEDIFF(MINUTE, 0.00069445, CURRENT_TIMESTAMP), 0) AS previousMinute, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CURRENT_TIMESTAMP), 0) AS thisMinute, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, CURRENT_TIMESTAMP), 0.00069445) AS nextMinute, DATEADD(HOUR, DATEDIFF(HOUR, 0.0416667, CURRENT_TIMESTAMP), 0) AS previousHour, DATEADD(HOUR, DATEDIFF(HOUR, 0, CURRENT_TIMESTAMP), 0) AS thisHour, DATEADD(HOUR, DATEDIFF(HOUR, 0, CURRENT_TIMESTAMP), 0.0416667) AS nextHour, DATEADD(DAY, DATEDIFF(DAY, 1, CURRENT_TIMESTAMP), 0) AS previousDay, DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0) AS thisDay, DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 1) AS nextDay, DATEADD(WEEK, DATEDIFF(WEEK, 7, CURRENT_TIMESTAMP), 0) AS previousWeek, DATEADD(WEEK, DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP), 0) AS thisWeek, DATEADD(WEEK, DATEDIFF(WEEK, 0, CURRENT_TIMESTAMP), 7) AS nextWeek, DATEADD(MONTH, DATEDIFF(MONTH, 31, CURRENT_TIMESTAMP), 0) AS previousMonth, DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 0) AS thisMonth, DATEADD(MONTH, DATEDIFF(MONTH, -1, CURRENT_TIMESTAMP), 0) AS nextMonth, DATEADD(QUARTER, DATEDIFF(QUARTER, 92, CURRENT_TIMESTAMP), 0) AS previousQuarter, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, CURRENT_TIMESTAMP), 0) AS thisQuarter, DATEADD(QUARTER, DATEDIFF(QUARTER, -1, CURRENT_TIMESTAMP), 0) AS nextQuarter, DATEADD(YEAR, DATEDIFF(YEAR, 365, CURRENT_TIMESTAMP), 0) AS previousYear, DATEADD(YEAR, DATEDIFF(YEAR, 0, CURRENT_TIMESTAMP), 0) AS thisYear, DATEADD(YEAR, DATEDIFF(YEAR, -1, CURRENT_TIMESTAMP), 0) AS nextYearFor example, If I want this weeks data I just join against this view and useWHEREDtCol >= thisWeek AND DtCol < nextWeekOr previous months dataWHEREDtCol >= previousMonth AND DtCol < thisMonth EDIT: Faster week calculations.
Is there any way to change product key after installation of SQL Server 2005 or must be SQL Server reinstalled. We have used wrong one for our customer because carelessness. Any problem if customer use product with old product key, but have a legal licence for product?
I have the following script that calculates Sales by month and current year.
We run a Fiscal year from April 1st thru March 31st.
So April 2012 sales are considered Fiscal Year 2013.
Is there a way I can alter this script to get Fiscal Year Totals?
select ClassificationId, YEAR(inv_dt) as Year, cus_no, isnull(sum(case when month(inv_dt) = 4 then salesamt end),0) as 'Apr', isnull(sum(case when month(inv_dt) = 5 then salesamt end),0) as 'May', isnull(sum(case when month(inv_dt) = 6 then salesamt end),0) as 'Jun', isnull(sum(case when month(inv_dt) = 7 then salesamt end),0) as 'Jul',
[Code] ....
Data returned looks like the following.
ClassificationID Year Cus_no Apr May June .... 100 2012 100 $23 $30 $400 100 2013 100 $40 $45 $600
What I would need is anything greater than or equal to April to show in the next years row.
I have one database configured with the Recovery Model "Simple".
I am getting alot of full transaction log messages... is this supposed to happen?
Another question is:
Imagine i am in a middle of a big select into statement... and in another query i run the backup truncate log... am i going to loose information on the other batch ("select into?")??
In my application (infopath browser forms), First I get current user name thru System.Environment.UserName, after that, i want to check whether this user has access (read & write) permission to certain database, How do I do that? One way I tried before is to get all the users and stored the names in an array for comparing in my program, but can not figure out the SQL command (to get the database users), if there it is, please kindly tell me. Or if I can just pass the current user to check? Thanks in advance.
Sorry about my English, it is not my natural language and thanks for your help. I have installed the Personal Site Starter Kit, everything work perfect except register users. When a new user try to register as a new user he receives an error, caused because the database is "read-only". In IIS the database has read and writing permissions and the directories where the aplication is. How can I change the database permissions?
Server Error in '/personalweb' Application.
Failed to update database "C:INETPUBWWWROOTPERSONALWEBAPP_DATAASPNETDB.MDF" because the database is read-only. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Failed to update database "C:INETPUBWWWROOTPERSONALWEBAPP_DATAASPNETDB.MDF" because the database is read-only.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
I've deployed my ssis pkg to the server and created a sql job to run this pkg. So far, everything is fine. Today, I got a request to change some variables inside the package which is part of the .dtsconfig. I want to edit the deployed .dtsConfig but it won't allow me and always complained that this file has been opened by another program. I am sure i've closed my ssis designer and other notpad, why can't I edit and save .dtsconfig file?
What query should I run? 1. To determine whether a column is a rowguid or not using C# .NET 1.1 2. To add/modify column information and be able to set/change: - Primary key - Column Name - Data Type - Length - Allow Null - Default value - Precision - Scale - Identity - Identity Seed - Identity Increment - Row guid
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?
One of my excel 2013 power pivot report was migrated from old server to new server after migration i changed the excel power pivot connection string to connect with new server but the workbook connections is still taking the old connection string of old server and there is no option of changing workbook connection string .
I am able to edit the powerpivot connection but workbook connections are not getting updated they are still taking old server connection string.
At the following MDX code , I want to get the aggregate of measure only for members that are also in the specified last time (like in examp 01/06/2015) . I tried existing and exists, but without any lack.
WITH MEMBER A AS (b)+(C) MEMBER [Measures].[Aggregate] AS Aggregate(DAYTIME].[Month].&[2013-01-01T00:00:00]:[DAYTIME].[Month].&[2015-06-01T00:00:00], ([Measures].[D])
I'm working in Visual Basic 6 using SQL Server 6.5 connecting through a DSN (using ODBC)
I have a query "object.OpenRecordset("SELECT * FROM table", dbOpenDynamic, 0, dbOptimistic" returning a recordset. I loop through the recordset, edit a char field for each record and update it.
The first update works fine but the second update (and then on) fails returning an error message ODBC call failed and I also get an error S1106.
I ran the code in debugger mode and noticed that as soon as it updates the first record, the pointer seems to move to the next record before even doing the "MoveNext" (based on the field contents), but the AbsolutePosition remains fixed until MoveNext is executed. At that point, if I do a "MovePrevious", the record is not there anymore and has totally disappeared from the recordset. What is happening and how do I get around that????
The data types ntext and nvarchar are incompatible in the equal to operator. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: The data types ntext and nvarchar are incompatible in the equal to operator.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Thanks , John
one of my SQL Developer member had one observation that, size of the parameter 'Parameter_XYZ' in certain stored procedure had changed from 25 to 255 during some production fixes, however suddenly its looks like that, someone has changed it back to 25 instead of 255.
DECLARE @Parameter_XYZ varchar(25);
Can we figure out in which sprint/drop the stored procedure was changed and the Parameter_XYZ back to 25. Can any log recovery mechanism will get such details.
Can we get stored procedure text between different alteration.
Hi, I want to write a sp with structure: PROCEDURE TEST @dbname1 varchar(25) @dbname2 varchar(25) AS 1. change current database to another, example for DB1 database 2. execute commands or block commands in DB1 3. change current database to another, example for DB2 database 4. execute commands or block commands in DB2 ... Can I do that? Who can help me? Thanks. qhbaby@hotmail.com
Let say I have this table in MS SQL server table transaction(date,sales)
how to query to get result like this (date,sales,sum(sales last 7 day)) I'm thinking about using self join, but it means I must have to self join 7 times to get the total sales for the last 7 day. Is there any better way to do this? or maybe special function within MS SQL server.
note: i'm not looking for total sales per week group by each week, but total last 7 day sales for each day
