HI all,
This has puzzled me all morning. I have a int field which reads
19691124 (UK dates) this is actually a date 24 November 1969 I need to
add 28 years to this making it 19971124 but I’m stumped!
Any ideas anyone?
Cheers
Sean
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
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?
Of database experience do you have?
And what are they?
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.
Get hammered and do not drive.
--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)
SELECT @dob = '12/06/1966'
SELECT @yyyymmdd = GETDATE()
SELECT @age = FLOOR(DATEDIFF(day, @dob, @yyyymmdd) / 365.25)
PRINT CONVERT(varchar, @age)
--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.
remember if you drink too much, get a taxi and charge it to your company.
View 4 Replies View RelatedHi there.
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.
CREATE TABLE mygifts06 (Donor_id varchar (10), Gift_yr nvarchar (4), Tot_pledges numeric (16,2))
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
Donor_id 2_consec_gifts 3_consec_gifts 4 consec_gifts
--------- -------------- -------------- --------------
155755000
155757000
155758110
155759100
Do I need to use a cursor for this task? I lack experienced in using cursors is there an alternative method someone could suggest?
Thanks in advance.
SELECT * FROM TEST NOLOCK
WHERE FY_CD = DATEPART(YEAR, GETDATE())
This gives me last years - but they want last years 2007 and 2008
Any suggestions
I want an sql query that wil give me listing of 20 years from current year.
View 2 Replies View RelatedI have a table name employee and datetime column named dateofbirth, how can i write a select statement to show their, date of birth and age in years?
This is how i did it, i couldn't finish it, Any feedback would be very thankful.
select name, datebirth, year(dateofbirth)
from employee
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.
Best regards
Alessandro
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.
- Jay Soares
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.
thanks for reading.
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
.... ....
DECLARE @MyDate datetime
SET @MyDate = '2/28/2009'
SELECT DATEADD(year,-1,@MyDate)
Desired result '2/29/2008'
This below seems to work. (Subtract a year and then find the last day of the month for that date, set to midnight)
SELECT DATEADD(dd, DATEDIFF(dd,0,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(year,-1,@MyDate))+1,0))), 0)
Is this a reasonable approach? I thought the SQL Server data and time functions were aware of leap year?
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.
View 1 Replies View RelatedHi All,
I have the following table "Project"
-------------------------------------------------------------------------------------------
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?
Gurj
I am trying to sort this simplied table:
ID - Time
1 2000-2001
2 2002-
3 2001-2003
4 1999
5 2005-2006
I want this as a result:
1999
2000-2001
2001-2003
2005-2006
2002-
Because the "-" means "continues", it the thing is still activated, so if it makes it easier, i could put the today's year afterwards during the query, if it ends with a - ...
Now, simply doing a
SELECT * FROM [table] ORDER BY TIME;
Sorts it "perfectly", apart from the "2002-" is just placed before 2005 and after 2001.
So, of course, it fails on all entries with a leading "-" ...
Right when I clicked "submit", of course, I can simply replace all entries with a time ending with "-", with the todays Year, so at least they will get at the end of the query...
Well, have to do a union, first sorting all without the "-", then sorting all with the "-", and that should be it...
I'm looking to partition a big table into years.
I have a big table with data going back to 2001.
I need to partition this into file groups each for the last 5 years, and the rest into its own file group
I was going to convert the inherent date time field into the format int(yyyymmdd) and partition on that field.
Is this an acceptable way to achieve this? Is there a better way to partition on date? [not time]
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
Hi there,
for displaying purposes I'm trying to convert number of months into years and months. ie. 35 months = 2 years and 11 months.
how can I do this within a T-SQL script?
Thanks for your help.
Manny
I already read a lot about the inscope-function and how it is used to display variances over time periods. But I don't know where to start, as there is no tutorial how to setup this functionality. What I want to display within the report is the following:
Code Snippet
Months
ProductGroup Article Year 1 2 3 4 5 ....
Bicycles 1020 2007 1500 2000 etc.
2008 3000 3000
Var. abs. 1500 1000
Var. % 100% 50%
1025 2007 0 1000
2008 500 1200
Var. abs. 500 200
Var. % 500% 20%
Motorcycles etc.
Hello,
I see the following in connection strings all the time: providerName="System.Data.SqlClient"/>
For example:<connectionStrings><add name="classifiedsConnection" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|classifiedsdb.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/></connectionStrings>
What is providerName="System.Data.SqlClient" ? What is it used for?Thanks, Jim
I have a table in my MS SQL 2000 database called News which has a field caled NewsDate. This is a standard Date field which stores the info in this format: 3/1/2001.
I want to create a query that returns one row for each year that there is a story.
For example, if I had this data...
3/1/2001, 6/27/2003. 9/17/2003, 1/1/2006, 4/5/2006
the query would return this result:
2001
2003
2006
This is the query I've started with:
SELECT DISTINCT NewsDate FROM News ORDER BY NewsDate DESC
What modifier can I apply to the NewsDate field to extract JUST the year from the table? If this were ASP I would try something like Year(Date), but, of course, I can't do that here.
Is this even possible? I've been looking up date functions, but haven't found anything that will work in a select statement. ANY and ALL advice will be greatly appreciated.
Given this table;
DECLARE @table TABLE (HolidayDate DATE, HolidayName NVARCHAR(50))
INSERT INTO @table
( HolidayDate, HolidayName )
VALUES ('2012-01-01','New Years Day'),
('2012-01-16', 'MLK Day'),
('2012-02-20', 'Presidents Day'),
[code]....
How to get a result set that shows a new column called PreviousHolidayDate with the corresponding holidays last years date?
HolidayDateHolidayNamePreviousHolidayDate
1/1/2012New Years DayNULL
1/16/2012MLK DayNULL
2/20/2012Presidents DayNULL
4/6/2012Good FridayNULL
5/28/2012Memorial DayNULL
[code]....
formula to return patients ages 6 months to 5 years?
create table dbo.TEST
(
MRN varchar(10),
[Code]....
I am trying to put in a check to ensure that the YearEnrolled - DateOfBirth is at least 16 years.
CREATE TABLE Student(
[DateOfBirth] [date] NOT NULL,
[YearEnrolled] [date] NOT NULL,
SELECT DATEPART(yyyy,DateofBirth) AS BirthYear,
CONSTRAINT DateOfBirth_CHECK CHECK(YearEnrolled-BirthYear =<16),
);
calculating a rolling median over a period of 3 years.
I already calculate median and I've tried to calculate rolling median over a period of 3 years as below.
 MEDIAN([Date].[Year].CurrentMember.Lag(3):[Date].[Year].CurrentMember,[Measures].[median])
What this does is, it calculates the median of the medians over the period of 3 years. But, what I'm looking for is the overall median of the underlying measure over a period of 3 years.
What I have now:
Year1 - 41,52,73; Â Median1 - 52
Year2 - 6,9,12; Â Median2- 9
Year3 - 24,68,89; Median3 - 68
Overall Median of 9,52,68 - 52
What I need:
Year1 - 41,52,73; Â Median1 - 52
Year2 - 6,9,12; Â Median2- 9
Year3 - 24,68,89; Median3 - 68
Overall Median of 41,52,73,6,9,12,24,68,89 is 41Â
I have simple query that works fine if I write it every time. I want to get this one time group by each year and week ending. here us the query:
Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/09'
Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/16'
Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/23'
How can I get the count for each week year and each week ending? Weekending 05/16 will include ID's from weekending 05/09 as well and week ending 05/23 will include all the ID's from 05/16 and 05/09 and so on...
Expected
[care session quarter]
Q3-14
Q4-14
Q1-15
Q2-15
Q3-15
Currently the output am getting shown below
care session quarter
Q1-15
Q2-15
Q3-14
Q3-15
Q4-14
I am using this [care session quarter] column in the group by clause to achieve but no success.IF I use date column in  the select clause and Group by clause then it comes correctly but groups by all dates which is not required.
Ideally I want show only quarter aggregates. The [Date Dimension] table has the column [care session quarter]  which stores all the quarters of years along with dates for each day. i..e I have all columns in [Date Dimension] table as shown below
Column_name
DATE_KEY
temp_date
DATE_NAME
DATE_WEEKDAY_NAME
DATE_IS_WORKDAY
DATE_NUMBER_IN_WEEK
[Code] ....