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?
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
Looking for sql query for this requirement output and default get the current day and time as day 7 with count start of 7 day before. Today is Sun. Thus start day is last sun.
now the time is 22:00 & group by shop plus underline +24 hour format
Sun Mon Tue Wed Thur Fri Sat Sun Total Shop A 20:00-21:00 $2 $10 $15 $5 $2 $10 $0 $100 $xxx Shop A 21:00-22:00 $1 $10 $15 $5 $2 $10 $0 $100 $xxx Shop A 22:00-23:00 $1 $10 $15 $5 $2 $10 $0 $no sales $xxx Total $4 $20 $30 $10 $4 $20 $0 $200 $xxx ------------------------------------------------------------------- Shop Z 22:00-23:00 $20 $15 $5 $2 $10 $0 $no sales $xxx Shop Z 23:00-00:00 $10 $15 $5 $2 $10 $0 $no sales $xxx Total $30 $30 $10 $4 $20 $0 $no sales $xxx
The GrossSaleAmount and NetSaleAmount are calculated fields. But for this post, kindly ignore why I am storing calcuated fields...
QUESTION: What I want to do is to populate another table (the DDL of which is give below) from tblSales in such a manner that the TOTAL sales from each product for each available date is grouped/summed together.
I'm developing a 2 year comparison rolling 12 month Sales report but am having problems with the query I'm using. Because I need to include every month (whether no sales or not) I have a calendar table named metaDates and each table gets outer joined as a result. (Forgive the long query) Please see below.
SELECT Customer.country, Order_Line_Invoice.prodcatid, MetaDates.[Month], MetaDates.[Year], isNull(SUM(Order_Line_Invoice.Sales),0) AS Sales , SUM(Order_Line_Invoice.Cost) AS Cost, ( isNull(SUM(Order_Line_Invoice.Sales),0) - isNull(SUM(Order_Line_Invoice.Cost),0) )AS GM, 'Current 12 Months' AS yearNum FROM MetaDates LEFT OUTER JOIN Order_Line_Invoice ON (MetaDates.Date = Order_Line_Invoice.InvoiceDate AND (Order_Line_Invoice.prodcatid IN (@GroupByFieldFilter)) ) LEFT OUTER JOIN Customer ON (Order_Line_Invoice.CustId = Customer.CustId and Customer.country IN (@country) ) LEFT OUTER JOIN Product ON (Order_Line_Invoice.ProdId = Product.ProdId) WHERE (MetaDates.Date BETWEEN dateadd(m, datediff(m, 0, DATEADD(month,-11,@EndDate) ), 0) AND @EndDate)
GROUP BY MetaDates.[Year], MetaDates.[Month], Customer.country, Order_Line_Invoice.prodcatid
UNION SELECT Customer.country, Order_Line_Invoice.prodcatid, MetaDates.[Month], MetaDates.[Year], isNull(SUM(Order_Line_Invoice.Sales),0) AS Sales , SUM(Order_Line_Invoice.Cost) AS Cost, ( isNull(SUM(Order_Line_Invoice.Sales),0) - isNull(SUM(Order_Line_Invoice.Cost),0) ) AS GM, 'Previous 12 Months' AS yearNum FROM MetaDates LEFT OUTER JOIN Order_Line_Invoice ON (MetaDates.Date = Order_Line_Invoice.InvoiceDate AND (Order_Line_Invoice.prodcatid IN (@GroupByFieldFilter)) ) LEFT OUTER JOIN Customer ON (Order_Line_Invoice.CustId = Customer.CustId and Customer.country IN (@country) ) LEFT OUTER JOIN Product ON (Order_Line_Invoice.ProdId = Product.ProdId) WHERE (MetaDates.Date BETWEEN dateadd(m, datediff(m, 0, DATEADD(month,-23,@EndDate) ), 0) AND dateadd(m, datediff(m, 0, DATEADD(month,-11,@EndDate) ), -1))
GROUP BY MetaDates.[Year], MetaDates.[Month], Customer.country, Order_Line_Invoice.prodcatid ORDER BY MetaDates.[Year], MetaDates.[Month]
That said the second outer join (Customer.country IN (@country)) never gets executed. It's really a simple concept. "Give me last 2 year sales by month (every previous and last month even if no sales) but only for the country the user is in"
Can someone help me out with this sql statement. Any help would be much appreciated.
I am trying to calculate the maximum sales per store where multiple stores exist in the same region (i.e there must be doubles of that row) in order to determine the most profitable store per that region.
E.g. Store Sales A 500 B 200 B 100 C 400 C 800 D 100 D 200 D 700
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.
I am creating a sales report that needs to display Sales statistics for a selected month grouped by Material. Also, it needs to display the same stats for the selected month of the previous year. Finally, it needs to display Year To Date Statistics for the current year and previous year up to and including the selected month.
Currently, I am using 3 queries to do this. The first one gets the statistics the current month grouped by material. The others sprocs get their corresponding information by me passing in the material number and the month and doing the appropriate sum. So, essentially, for a single line of the report, 3 queries are being done. The problem arises in that sometimes there may be as many as 300 materils displayed in a given month, which amounts to alot of db activity and a long delay loading the report.
Is there anyway to get this information in one swoop using some fancy aggregation?
I'm trying to develop a query that provides sales data by Customer.GroupCode in monthly columns as depicted below:
GrpCd JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC TOT Film 5,000 15,000 20,000 Aero Elct 3,000 950 3,950 Desg Edu 150 150
Here€™s a simplified version of the DDL: CREATE TABLE invchead ( invoicenum int NULL , invoicedate datetime NULL , invoiceamt decimal(16, 2) NULL , custnum int NULL )
The query below gets me close but it gives me gives me one row for each customer. So if I have 5 customers with the same group code, I get 5 rows for that group code. I need to modify it or come up with a different approach that gives me only one row for each GroupCode.
SELECT distinct c.Name, c.GroupCode, (SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate) AND DATEPART(month, InvcHead.invoicedate)=1) JAN, (SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate) AND DATEPART(month, InvcHead.invoicedate)=2) FEB, ...... (SELECT SUM(InvoiceAmt) FROM InvcHead WHERE InvcHead.custnum=i.custnum AND DATEPART(year, InvcHead.invoicedate)= DATEPART(year, i.invoicedate)) TOT
FROM InvcHead i INNER JOIN Customer c ON (i.custnum=c.custnum) WHERE i.invoicedate>='1-1-2007' AND i.invoicedate<'1-1-2008'
Grateful for any advice that will get me closer to accomplishing this.
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'";
My requirements are to return the sales for each customer for each store, for the past 6 weeks.
The catch is that I only want those customers which have had sales over 10,000 within the last week.
This is my query:
WITH SET [CustomerList] AS FILTER( ([Store].[Store Name].[Store Name], [Customer].[Customer Name].[Customer Name]), [Measures].[Sales] >= 10000 AND [Date].[Fiscal Week Name].&[2015-01-26 to 2015-02-01]
I need to list customers in a table that represents sales over the years.
I have tables:
Customers -> id | name |... Orders -> id | idCustomer | date | ... Products -> id | idOrder | unitprice | quantity | ...
I am using this SQL but it only gets one year:
SELECT customers.name , SUM(unitprice*qt) AS total FROM Products INNER JOIN Orders ON Orders.id = Products.idOrder INNER JOIN Customers ON Customers.id = Orders.idCustomer WHERE year(date)=2014 GROUP BY customers.name ORDER BY 2 DESC
I need something like this:
customer | total sales 204 | total sales | 2015 | total sales (2014 + 2015) -------- customer A | 1000$ | 2000$ | 3000$ customer B | 100$ | 100$ | 200$
Is it possible to retrieve these values in a single SQL query for multiple years and grand total?
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?
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!
I have a vb.net page that I need to display a list of employees who work in a specific office, based on a MatterID passed in a query string. But, I don't know how to get a value returned from one sql statement into a second. Here's what I'm trying to do... From the QueryString, we know that the MatterID = 4 ( xxx.aspx?MatterID=4) Knowing that the Matterid=4, I query the database to get the OfficeId for that MID (Select OfficeID from tMatter where Mid=4) ~This returns an OfficeID of 6 So, then I need to do another query to get the employees where OfficeID = 6 (Select EmployeeID from tEmployees where OfficeID = 6) How do I do these in one query, or how do I use the Calculated Value for the OfficeID in the 2nd statement?
I have a database where each row has a set of keywords separated bycommas eg .ROW 1 ID,<other columns>,"green,hat,man,boat"ROW 2 ID,<other columns>,"green,boat,seaside"ROW 3 ID,<other columns>,"red,hat"I want users to be able to find similar records to the one they arelooking it (this is art BTW). ie if they are looking at row 1 andclick "Find Similar Works of Art" I want to do a query where I findrecords that have a commonality of keywords and obviously the onesthat match the most keywords should be shown first. The keywords arecurrently just stored comma-separated in a single column but I am notattached to this. Nor are they guaranteed to come in any order (iethey are not alpha sorted) but they could be.Number of records is around 15000 on a hosted server.Any tips for best query/algorithm/data storage to achieve this? orreferences to useful sources?Thanks,RB
I have a SQL query that finds a value which I need to pass into an SSIS variable so I can use it later to set the filename for my data flow task. So far, I have been unsuccessful in determining how this is done.
google searches, etc seem to be fruitless for me, there are so many hits but none really seem to address what I want to do because there are so many things you can do with variables. Likewise the books online and the boks I have on SSIS also only give a very high level of info. I need some examples, if anyone could help I would appreciate it.
Here is the basic gist of my query:
exec SP_GET_FILE_NAME @file_name OUTPUT
I need to take the resulting @file_name and store it in an ssis variable. How?
in the following procedure i want to pass in a value and as a result of the value I want to change the actual body of the query:
example, I pass in the value 'TS2' and I now want my query to see if TS2MIN < 70
i think I am doing it right, but it is throwing an error saying that "Conversion failed when converting the varchar value 'TS2MIN' to data type int."
any idea on what i am doing wrong?
ALTER proc SPU_YearlyTemp 905,'TS2',70,254,2007 @SITEID INT, @SENSOR VARCHAR(10), @MIN INT, @MAX INT, @YEAR INT
AS DECLARE @S AS VARCHAR(12) SET @S=( SELECT CASE @SENSOR WHEN 'TS1' THEN 'TS1MIN' WHEN 'TS2' THEN 'TS2MIN' WHEN 'TS3' THEN 'TS3MIN' WHEN 'TS4' THEN 'TS4MIN' WHEN 'TS5' THEN 'TS5MIN' WHEN 'TS6' THEN 'TS6MIN' WHEN 'TS7' THEN 'TS7MIN' WHEN 'TS8' THEN 'TS8MIN' END AS SENSORVALUE)
select top(10)* into #tempMin from sitecontroldataarchive where siteid = @SITEID and @S < @MIN --this is where the error occurs, SELECT * FROM #tempMin
I need to insert into another table the results of a query. Thought about using a temp table for the first query results, then querying those for the insert, but can't figure out how to pass the search results as a value.
Example:
SELECT ID, NAME FROM tbl2 WHERE ID BETWEEN 1 AND 50
IF @@ROWCOUNT > 0 BEGIN INSERT INTO tblAudit (ID, NAME) VALUES (@ID, @NAME) END
Took a look at using SELECT INTO and INSERT INTO, but counldn't get those to work, either.
I need to select certain rows based on a "datetime" column. I need to select rows from 8am yesterday until 8am today. In Oracle I would use: select * from foo where TIMESTAMP >= trunc(sysdate - 1) + 8/24 AND TIMESTAMP < trunc(sysdate) + 8/24. This would start at 8am yesterday and end at 7:59am today.
I'll try to simplify the problem as much as I can.
There's survey. It has different types of question answer pairs, but the problem is in Multiple Choices, Multiple Answers questions, where user can choose, for example 3 checkboxes from 5 at the web form, for the question.
tbl_results IDresult (primary) IDquestion (foreign, to tbl_question) result (string based on CSV, may contain few IDanswers, because visitor may choose few checkboxes, for example it can be "4,6,7")
Inside those tables are data (I'll put here just two questions):
Example: tbl_questions IDquestion_question 1_________Which OS do you use? 2_________Which databases do you use? tbl_answers IDanswer_IDquestion_answer 1________1_________Windows 2________1_________Linux 3________1_________Mac OS 4________2_________SQL Server 5________2_________MS Access 6________2_________MySQL 7________2_________Oracle 8________2_________Other 9________2_________No, I do not use databases tbl_results IDresult_IDquestion_results ........ 23_______1________1,2 24_______2________4,6,7 ........ Is there a possibility to make query, using columns, but also the data inside the CSV format in results (varchar or char) column?
Result of the query would be: IDquestion_IDanswer_answer 1_________1________Windows 1_________1________Linux 2_________4________SQL Server 2_________6________MySQL 2_________7________Oracle
Further queries based on this query should be able to make statistics (GROUP BY).
Does anyone know of a vendor who provides a packaged application that allows a user to pass T-SQL commands from a browser, to our IIS web server, to our SQL Server database?
I don't know if anyone has encountered this before but here goes:
I've a select clause below: result = "Select * from person where LocalName LIKE N'" + queryLocalName + "'"
queryLocalName is an input field that allows the user to search for non-English characters in the database.
What I'm wondering is what kind of effect is the N in the where clause is having?
I can't seem to get it to work when doing it via the web. I've tested in the database itself, got it to work using the SQL Analyser but when testing on the web, it can't find because ? are appearing in the result.
