SQL Server 2012 :: Sales Over Years - Retrieve Values In Single Query For Multiple Years And Grand Total?
Apr 24, 2015
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?
am making a CV program and i need a way to count the experience the user has: i have his begin date and end Date as datetime in an sql server. i can do it programicly but i prefer to do it at the sql side the question: how can i get how much exp he has aka : SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate )) but for all the datarow (he has more than one BeginDate and EndDate (for each job he has one)) P.S i want to be able to use it in a where clause : select * from jobap where -- or HAVING JobAp.ind = JobApExp.JobAp AND SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate )) > CONVERT(int,@Exp) thanks in advance
I have created data model where I'm taking several sources of Point of Sale data (multiple retailers) and combing them using Power Pivot and a custom calendar. We get data retailer direct, which is mostly in weeks, and data from IRI which is in four week buckets. This does not allow me to use the date intelligence DAX functions. I'm brand new to DAX and my experience starts and ends with Excel. (Diagram view and link to file to come after account verification)Â .
The DAX code for calculating LY Sales is:
= CALCULATE (     [Sales $],     FILTER (         ALL ( dCalendar ),         dCalendar[IRIYearNumber]             = VALUES ( dCalendar[IRIYearNumber] ) - 1
[Code] ....
The filters are to prevent items not on the item table from showing on the report, and the customer filter is to prevent all the sales being rolled together as and extra line (with blank customer) on the report.Â
The error happens when I select two periods that are in different years. When I select the 13 periods on 2014 all is well. But when I add a period from 2015 it throws the error below;
ERROR - CALCULATION ABORTED: Calculation error in measure 'dProducts'[LY Sales $]: A table of multiple values was supplied where a single value was expected.Â
Below is the scenario which I have currently in my query. I need to write this query without any hardcode values , so that it will work til n number of years without modifications.
Startdate = CASE WHEN Trandate between '06-04-2013' and '05-04-2014' then '06-04-2013' Trandate between '06-04-2012' and '05-04-2013' then '06-04-2012' Trandate between '06-04-2011' and '05-04-2012' then '06-04-2011' Trandate between '06-04-2010' and '05-04-2011' then '06-04-2010' Trandate between '06-04-2009' and '05-04-2010' then '06-04-2009' Trandate between '06-04-2008' and '05-04-2019' then '06-04-2008' END
------------------------------------------------------------------------------------------- ID Name Start Date End Date -------------------------------------------------------------------------------------------- 001 Project 1 2-2-2003 2-3-2007 002 Project 2 1-24-2003 2-6-2007 003 Project 3 4-10-2005 2-10-2008 004 Project 4 5-20-2006 6-6-2008 ... 015 Project 15 2-20-2006 3-3-2009 ---------------------------------------------------------------------------------------------- What I want is the the following output.
Output 1:
------------------------------------------------------------------------------------------------------------------------------------------------ Year Projects No. Of Projects Starting Months ------------------------------------------------------------------------------------------------------------------------------------------------ 2003 Project 1 , Project 2 2 February, January 2005 Project 3 1 April 2006 Project 4, Project 15 2 May , Feb ------------------------------------------------------------------------------------------------------------------------------------------------
(the order displayed in the months shoudl be in accordance with the order of the projecs in the projects column...) and also the following
Output 2: (this is optional view...) ----------------------------------------------------------------------------------------- Year Start Date Project Name ----------------------------------------------------------------------------------------- 2003
February 2 Project 1 January 24 Project 2 2005 April 4 Project 3 2006 May 5 Project 4 February 20 Project 15 -------------------------------------------------------------------------------------------
I am very much in need of Output 1. Could someone help me,
Using 'TAIL([Time].[Time].[Year].MEMBERS,1)' I can get the current year dynamically. My question is how do I get the previous two years dynamically as well. I've tried a few different ways with no luck. As you can see below I'd like to replace 2014 and 2013 with current year -1 and current year -2.
SELECT { [Time].[Time].[Year].&[2013], [Time].[Time].[Year].&[2014], TAIL([Time].[Time].[Year].MEMBERS,1) }ON COLUMNS, NON EMPTY {[Branches].[Branches].[Region]*[Measures].[Ship Resale S&D Run Rate]} ON ROWS FROM SALES
how we can replace the multiple values in a single select statement? I have to build the output based on values stored in a table. Please see below the sample input and expected output.
I have a DB of professors and information related with them. I created the cube, it consist of: Measures: Measure group Professors: Amount of projects (COUNT proj_id) Amount of pulications (COUNT pub_id) Amount of e_books (COUNT book_id) -------------- Measure group Projects: Distinct amount of projects (DISTINCT COUNT proj_id) -------------- Measure group Publications: Distinct amount of publications (DISTINCT COUNT pub_id) -------------- Measure group E_books: Distinct amount of e_books (DISTINCT COUNT book_id) -------------- Calculated measures: Amnt_Projects iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects]) Amnt_Publications (similar to the above one) Amnt_E_books (similar to the above one) --------------------------- Dimensions: dimPROFESSORS - prof_id -surname -name -gender dimPROJECTS - proj_id -type name -name dimPUBLICATIONS - pub_id -type name -name dimE_BOOKS - book_id -name Date_Projects -date_id -years Date_Publications -date_id -years Date_E_books -date_id -years
For example, when I browse the cube: prof_id____Amount of projects___Distinct amount of projects___Amnt_Projects 1032------------------- 30 --------------------------1----------------1 1070-------------------90 --------------------------2----------------2 1111-------------------0 ---------------------------1----------------0 1137-------------------0 ---------------------------1----------------0 1234-------------------1404--------------------------9----------------9 1721-------------------504--------------------------7----------------7 2661-------------------85 --------------------------5----------------5 ...--------------------...---------------------------...----------------... 6999------------------- 20---------------------------1-----------------1 9956-------------------50---------------------------5-----------------5 Unknown----------------(empty)---------------------(empty)-----------0 Grand Total------------ 2421------------------------11-----------------11
Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be „59“ if for all professors). How could I get the right value to be shown in Grand Total?
I am using SQL Server 2005. I have a DB of professors and information related with them. I created the cube, it consist of: Measures: Measure group Professors: Amount of projects (COUNT proj_id) Amount of publications (COUNT pub_id) Amount of e_books (COUNT book_id) -------------- Measure group Projects: Distinct amount of projects (DISTINCT COUNT proj_id) -------------- Measure group Publications: Distinct amount of publications (DISTINCT COUNT pub_id) -------------- Measure group E_books: Distinct amount of e_books (DISTINCT COUNT book_id) Calculated measures: Amnt_Projects iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects]) Amnt_Publications (similar to the above one) Amnt_E_books (similar to the above one) --------------------------- Dimensions: dimPROFESSORS - prof_id -surname -name -gender dimPROJECTS - proj_id -type name -name dimPUBLICATIONS - pub_id -type name -name dimE_BOOKS - book_id -name Date_Projects -date_id -years Date_Publications -date_id -years Date_E_books -date_id -years
For example, when I browse the cube: prof_id____Amount of projects___Distinct amount of projects___Amnt_Projects 1032------------------- 30 --------------------------1----------------1 1070-------------------90 --------------------------2----------------2 1111-------------------0 ---------------------------1----------------0 1137-------------------0 ---------------------------1----------------0 1234-------------------1404--------------------------9----------------9 1721-------------------504--------------------------7----------------7 2661-------------------85 --------------------------5----------------5 ...--------------------...---------------------------...----------------... 6999------------------- 20---------------------------1-----------------1 9956-------------------50---------------------------5-----------------5 Unknown----------------(empty)---------------------(empty)-----------0 Grand Total------------ 2421------------------------11-----------------11
Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be „59“ if for all professors). How could I get the right value to be shown in Grand Total?
I need to get the Grand Total of the results of this query. The query pulls the total customer quotes for each community in a management company and loads my DataGrid:
This query works fine for individual community totals"SELECT TOP 100 PERCENT vcName, COUNT(DISTINCT vcCustId) AS " & _ " Total FROM dbo.PropReportData WHERE cManagementCo = '" & Session("MgmtCo") & "' and " & _ " (vcEntryDate >= CONVERT(DATETIME, '" & StartDate & "', 102)) AND " & _ "(vcEntryDate <= CONVERT(DATETIME, '" & EndDate & " 11:59:59 PM" & "' , 102))GROUP BY vcName ORDER by vcName"
At first glance you would think that the following query would return the Grand Total for all communities in the management company:
Current Grand Total Query"SELECT COUNT(DISTINCT vcCustID) As gTotal FROM PropReportData WHERE cManagementCo = '" & Session("MgmtCo") & "' AND vcEntryDate >= '" & Session ("StartDate") & "' AND vcEntryDate <= '" & Session("EndDate") & " 11:59:59 PM' "
But here's the problem. If there are multiple customer quotes created for different communites, then the customer(vcCustID) is only counted once in the Grand Total Query because I have to use DISTINCT, which of course only picks up one instance of the customer.
I have a script that produce a result set that is almost complete. I have a new requirement come up to put a Total at the button of the result set of my Query.
I'm using SQL Server 2008 ;WITH CTE AS ( SELECT Case WHen left(MONTHDate,3)='Jan' Then '01' WHen left(MONTHDate,3)='Feb' Then '02' WHen left(MONTHDate,3)='Mar' Then '03' WHen left(MONTHDate,3)='Apr' Then '04' WHen left(MONTHDate,3)='May' Then '05'
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
I just wanted to wish everyone a Happy New Years and I wanted to thank all submitters to this forum for breaking up my code monkey workday and cutting into my productivity and I have found many of these discussions helpful in my preperation for my long procrastinated MCDBA exams.
--Use the following formula to calculate a person's age in years in a stored procedure, --where @dob is the person's date of birth and @yyyymmdd is the date on which to determine the age:
DECLARE @age int DECLARE @dob datetime DECLARE @yyyymmdd varchar(11)
--Notes: --Substitute "getdate()" for the @yyyymmdd variable if you want to determine the person's age right now. --The function divides by 365.25 to allow for leap years and uses the FLOOR function to make sure the function returns an integer. --The function DATEDIFF(year, @dob, @yyyymmdd) doesn't work because Microsoft chose to implement --the "year" part of the function to calculate the number of year boundaries crossed.
HI all,This has puzzled me all morning. I have a int field which reads19691124 (UK dates) this is actually a date 24 November 1969 I need toadd 28 years to this making it 19971124 but I’m stumped!Any ideas anyone?CheersSean*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I work for a charitable organization, am new to this form (and sql programming) and trying to create a flag for unique records indicating the number of consecutive years a donor has given.
I have create a sample db idenifying donor, giving year and total pledges with multiple donor records existing for multiple years having donated.
INSERT INTO mygifts06 (Id,Gift_yr,Pledges) SELECT 155758,2005,15.00 UNION ALL SELECT 155759,2004,25.00 UNION ALL SELECT 155758,2004,40.00 UNION ALL SELECT 155757,2005,100.00 UNION ALL SELECT 155758,2002,30.00 UNION ALL SELECT 155758,2001,120.00 UNION ALL SELECT 155755,2003,15.00 UNION ALL SELECT 155758,2006,80.00 UNION ALL SELECT 155757,2003,65.00 UNION ALL SELECT 155759,2005,400.00
For the above dataset, I am trying to create the following output
Hello to everybody, I'm quite new to the Analysis Services world and would like to create a BI Solution for one of my customers with the following problem: I have sales data from the ERP from different years (2005 and on). My customer wants a report that shows the sales of the current year up to the month he wants to specify (e.g. january - march 2008) compared with the sales of the same period of the previous year (e.g. january - march 2007), divided into product category.
Is there a way to build a cube and then a matrix report in Reporting Services that lets the user specify the period for the current year and shows the same period of the previous year. Do I achieve this behaviour with a calculated member in the cube (e.g. ParallelPeriod)
Any hint (or web like example) would be very appreciated.
Hi and thank you in advance to whomever takes the time to read this entry. I will be as detailed as possible, so I apologize for the length. The information here relates to the automotive industry, but I don't believe that is a very important detail
The overall purpose of this query is to create a compressed set of the data that exists in the database. The de-normalized information is structured like the following (including made-up data):
Code Block PartNumber Make Model Year ------------------------------------------------------- 835100 ACURA INTEGRA 2004 835100 ACURA INTEGRA 2003 835100 ACURA INTEGRA 2001 835100 ACURA INTEGRA 2000 835100 FORD FOCUS 2002
There is any number of part numbers, makes, etc in this mix. The query that I currently use is utilizing MAX and MIN functions to determine year range, but as in the sample above, this does not take into account the fact that there could be a year skipped in the middle. I could probably use a cursor to do this, but would really like to stick to set logic if at all possible.
My existing query is:
Code Block SELECT DISTINCT cwi.PartNumber ,RTRIM(lv.Make) AS Make ,MIN(lv.Year) AS StartYear ,MAX(lv.Year) AS EndYear FROM CWIParts AS cwi INNER JOIN PartTypes AS pt ON cwi.PartTypeID = pt.PartTypeID INNER JOIN PartDetail AS pd ON cwi.PartNumber = pd.PartNumber INNER JOIN Status AS s ON pd.StatusCode = s.StatusCode INNER JOIN LegacyVehicle AS lv ON cwi.LegacyVehicleID = lv.LegacyVehicleID WHERE cwi.PartTypeID = 10 AND s.Status = 'Active' GROUP BY lv.Make ORDER BY cwi.PartNumber ASC ,cwi.Make ASC
In hopes to end up with a result set that has 1 row for each unique part-number, make, and consecutive year range relevant to the part-number and make. I will also need to add the functionality to add other attributes (such as Model and Liters), but those are equality based and can be passed in easily.
I had done most of this programatically in vb.net, but I would much rather push this logic back to SQL Server. Thank you all for any help that you can provide on this topic.
I've set up a SQL7 database with MSAccess97 as a front end. I'm trying to enter a person with a birthdate prior to 1900, get an ODBC call error, "Datetime field overflow". How to enter dates prior to year 1900? Thanks.
i'm interested in improving the format of this query. consider me clueless today, if you will. :) how can i fix this to make it dynamically move over the years? is there something i can do with set manipulation that is smarter than this?
the goal of this query is to return cases per year, where "year" is defined as (Oct 1, YYYY - Sep 30, YYYY+1) instead of the typical YYYY
problem is, i have to write it as some cludgy dynamic sql looping over an incremented year. i don't know of any other way.
again, thanks for reading ... and any help in advance.
SELECTcount(*) as 'Data Points', '2001' as 'Experiment Year' FROM tbl_experiment_data
WHEREstart_date BETWEEN '9/30/2001' AND '10/01/2002' and completion_date BETWEEN '9/30/2001' AND '10/01/2002' and status = 'CaseClosed'
UNION
SELECTcount(*) as 'Data Points', '2002' as 'Experiment Year' FROM tbl_experiment_data
WHEREstart_date BETWEEN '9/30/2002' AND '10/01/2003' and completion_date BETWEEN '9/30/2002' AND '10/01/2003' and status = 'CaseClosed'
UNION
...
expected output....
Data Points______ Experiment Year 32_____________ 2001 102____________ 2002 .... ....
I am trying to get the date of Tuesday from now of the past x Years
The below Query works fine for weeks
DECLARE @x INT SET x= 53 SELECT CONVERT(VARCHAR, DATEADD(WW,-@x,DATEADD(DD, -((@@DATEFIRST + DATEPART(DW, GETDATE()) -3) % 7), GETDATE())) ,23)
The issue is with below query for any passed Number of Years
SET x = 7 CONVERT(VARCHAR,DATEADD(yy, -@x, DATEADD(D, -((@@DATEFIRST + DATEPART(DW, GETDATE()) -3) % 7), GETDATE())) ,23)
So need to get past years Tuesday Date and also verify if week also works for any passed week numbers ? The x parameter can be changed based on the situation
Hi folks,A DTS package we have run for years now no longer works. The specificpart that is not working is a subquery in the SOURCE object of atransformation. The source is based on a Microsoft Data Link to aSybase database (DSN changed a couple months ago but the connectionstring was updated successfully for the new 12.51 version of ASE) andthe destination is a link to a local SQL Server 2000 database.The transformation has always worked and when I remove the subqueryeverything works OK. The problem is that I need the subquery!Does anyone have a clue what is going on?Here is the full query.select TableKey = RVSN_TYPE_ID,TableCode = RVSN_TYPE,RevisionDate = RVSN_DATE,RevisionReasonCode = RSN_CODE,RevisionGroup = RVSN_GRP_ID,RevisedField = (select L.FieldIDfrom tempdb.guest.lkpRevisedField Lwhere L.TableID = R.RVSN_TYPEand L.FieldName = R.CHNG_FLD),RevisedValue = OLD_FLD_VAL,RevisionTimestamp = RVSN_TIMESTAMPfrom RVSN R,tempdb.guest.MaxTimeStamp TSwhere R.RVSN_TIMESTAMP TS.Rtimestampand R.RVSN_TIMESTAMP is NOT NULLJohn H.