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 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 an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' Â values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) Â must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName          Clm ABC-X
X
Page2:
GroupName          Clm ABC-Y
Y
Page3:
GroupName          Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
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.
If these are all used up, and a 26th connection comes in. What happens? is the connection rejected? or does it sit in a queue, waiting to be given a connection?
hi i hv developed an application that runs on the local windows system and interfaces with the MSSQL 7.0 database server using ODBC DSN Connections. This application will be deployed over the LAN with over 100 users.
The MSSQL 7.0 server has only 5 user licenses that limits the concurrent usage to 5 users.
I need a solution whereby all users can access the SQL server database without adding more licenses
I have been searching hi and low on the web to try and find out typically which version of sql server should be used based on the number of concurrent users you will be having.
I believe that Sql Server 2005 Express should only really be used for up to 10 concurrent users while I seem to remember that Standard edition supports up to approx. 33,000 connections.
Does anybody have this information to hand as I need to write a business case to justify the potential upgrade.
Hi,Is it possible to have many different clients conncecting to a web basedserver all using, simultaneously, the same DSN-less connection string heldin a server side include file?Many thanksRay Allison
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.
------------------------------------------------------------------------------------------- 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
Hi, I would like to select data from my source system based on fiscal years. I don't want to hard code anything, e.g. select * from person where startdate > 01042005. I want to try and use a store proc or function. The main requirement is to always retrieve data from source tables for the last two fiscal years (based on todays date - getdate). Our fiscal year starts the 1st April. Therefore, If todays date is 14 October 2007, I would like to select all persons whose start date was greater than 01 April 2005. Is there an easy way to do this in sql server 2005 without hard coding dates?
MSDE will only allow 5 concurrent connections to ...
a particular database? a particular instance? or a particular server?
ie. if i have one application running on one instance of MSDE with 5 connections and i have another application running on another instance of MSDE with 5 connections. would i actually have 10 connections? **both instances on the same machine...
I need to know how many concurrent users are allowed to access the database at any point of time.Will it depend on the type of licence?Where do we set this parameter.Some users are complaining 'time out' problem.Thanks for any help!
I have a stored proc that runs every 4 hours - as a job. The stored proc takes about 3-5 minutes to comple, depending on number of records. To do testing we run that stored proc manually, also. Sometimes 2 or more people may run the same stored proc without knowing that the other person is running. This may create duplicates entries once processed.
What I want to know is, Is there a way to check if that stored procedure is currently running. If so it wont run second time concurrently. (May be semapohres,mutex or something like that?)
(I am trying not to use a table to store whether the stored proc is running or not)
On my server running SQL Server 2000 (SP3). I frequently get thisinformational message in the Event Viewer log.17052 :This SQL Server has been optimized for 8 concurrent queries. This limit hasbeen exceeded by 2 queries and performance may be adversely affected.The application that I am running is a vendor application so I do not haveaccess to the code. I contacted the vendor, but they have never seen thisproblem and cannot recreate it. I checked the Microsoft Knowledge Base forhelp, but I came up empty. It seems like it should be a simpleconfiguration change.Any advice?Thanks,Jeff