T-SQL (SS2K8) :: Current Year Begin And End Dates

May 6, 2014

I am working on some payroll related code which currently has the following hard-coded CASE statement (I won't include the entire thing, it is lengthy):

AND b.TCDateTime BETWEEN '2013-01-01 0:00' and '2013-12-31 23:59'

I want to get rid of the hard coding, and have this use current year dates. So for 2014, it should reference rows where the TCDateTime is >='2014-01-01 0:00' AND <'2015-01-01 0:00'..I think the following would accomplish this, but would like confirmation that this is the 'best' way (and that it will work!)

SELECT CONVERT(DATETIME, CONVERT(CHAR(4), DATEPART(yyyy, GETDATE())) + '0101') AS curryrbegin
--output should be yyyy-01-01 00:00:00.0 where yyyy is current year
SELECT CONVERT(DATETIME, CONVERT(CHAR(4), DATEPART(yyyy + 1, GETDATE()))
+ '0101') AS nextyrbegin
--output s/b nextyear-01-01-00:00:00.0

Then, change the CASE statement to read:

AND (b.TCDateTime >= curryrbegin AND < nextyrbegin)

View 8 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Returning Results That Fall Within Current Financial Year?

Jun 3, 2014

I am using MSSQL Server 2008R2 and I am interested in returning rows from a 'financial' table that fall within the current year (each row contains a 'Entered Date'). I am located in Australia so my financial year consists of all entries between the date 01/07/xx to the 30/06/yy.

Perhaps using the datediff() function, or other functions as required to achieve what I need?

View 3 Replies View Related

Fiscal Year Totals - Calculating Sales By Month And Current Year

Sep 18, 2013

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.

View 2 Replies View Related

Leap Year Dates Causing Error When You Pull Dates For Non Leap Year

Feb 28, 2008



I'm trying to generate this query, that displays Budget Current Year , Actual Current Year and Prior Year Revenue. When It comes to the Budget and Actual everything works fine, however when I try to add the query for the Prior Year I get an error, and I realized that the leap date is causing the error


Here is what I'm trying to generate






InnCodeID
Quarterly
Monthly
Days
Period
Year
BARmRev
AARmRev
PYRmRev

ADDIS
Q1
Jan
1
1
2008
NULL
NULL


ADDIS
Q1
Jan
1
1
2008
3462.14
5107.65


ADDIS
Q1
Jan
1
1
2008
NULL
NULL


ADDIS
Q1
Jan
1
1
2008
NULL
NULL



Here is the error that I'm getting:



Code Snippet

Msg 242, Level 16, State 3, Line 1

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

(4834 row(s) affected)



Here is my Transact-SQL Syntax (summarized because I Couldn't post it):

SELECT

(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01'

AND Year(dbo.Trans.TR_Date) = Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1'

THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN

(SELECT Trans1.TR_Amount

FROM dbo.Trans Trans1

WHERE Trans1.TR_Dept = '10' AND TR_Main = '5120' AND TR_Sub = '01' AND trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date)

AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Year(dbo.CurrentDate.CurrDate) AS varchar(4))) AS datetime)

AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND trans1.datatype = dbo.Trans.DataType) ELSE NULL END) * - 1 AS BARmRev,


--AA Script Here AS AARmRev,

(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01' AND Year(dbo.Trans.TR_Date)

= Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1' THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN

(SELECT SUM(Trans1.TR_Amount)

FROM dbo.Trans Trans1

WHERE RIGHT(RTRIM(Trans1.TR_Dept), 2) = '10' AND Trans1.TR_Main = '5120' AND Trans1.TR_Sub NOT BETWEEN '04' AND '05' AND

trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2))

+ '/' + CAST(Year(dbo.CurrentDate.CurrDate)-1 AS varchar(4))) AS datetime) AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND

trans1.datatype = '0') ELSE NULL END) * - 1 AS PYRmRev



FROM dbo.EntityDef INNER JOIN

dbo.Trans ON dbo.EntityDef.ED_Property_ID = dbo.Trans.TR_Entity INNER JOIN

dbo.CurrentDate INNER JOIN

dbo.DimTime ON YEAR(dbo.DimTime.TimeDate) = YEAR(dbo.CurrentDate.CurrDate) ON dbo.Trans.TR_Date = dbo.DimTime.TimeDate

WHERE (dbo.EntityDef.ED_Property_ID = 'ADDIS')

ORDER BY dbo.EntityDef.ED_Property_ID, dbo.DimTime.TimeDate


I appreciate all your help

Thanks

View 13 Replies View Related

Compare Given Period In Current Year / Previous Year

Mar 15, 2006

HiI want to write a function that can return a sum for a given daterange. The same function should be able to return the sum for the sameperiod year before.Let me give an example:The Table LedgerTrans consist among other of the follwing fieldsAccountNum (Varchar)TransdateAmountMST (Real)The sample data could be1111, 01-01-2005, 100 USD1111, 18-01-2005, 125 USD1111, 15-03-2005, 50 USD1111,27-06-2005, 500 USD1111,02-01-2006, 250 USD1111,23-02-2006,12 USDIf the current day is 16. march 2006 I would like to have a functionwhich called twice could retrive the values.Previus period (for TransDate >= 01-01-2005 AND TransDate <=16-03-2005) = 275 USDCurrent period (for TransDate >= 01-01-2006 AND TransDate <=16-03-2006) = 262 USDThe function should be called with the AccountNum and current date(GetDate() ?) and f.ex. 0 or 1 for this year / previous year.How can I create a function that dynamically can do this ?I have tried f.ex. calling the function with@ThisYear as GetDate()SET @DateStart = datepart(d,0) + '-' + datepart(m,0) +'-'+datepart(y,@ThisYear)But the value for @dateStart is something like 12-07-1905 so thisdon't work.I Would appreciate any help on this.BR / Jan

View 3 Replies View Related

Sales For Current Year To Previous Year

Oct 20, 2007

Guys,

I wanted to find the ratio: (sales made for current year 2007 - sales made for previous year 2006)/sales made for previous year 2006.

so, the result should be something like this:

Year: Sales: %change in sales:
2005 100 10%
2006 200 20%
2007 300 30%

How do I write a query for this...??? so that i can plot this in a chart in SSRS.

somebody help me.

View 4 Replies View Related

Better Way To Use BETWEEN Begin And End Dates

Sep 24, 2006

/*Subject: How best to use BETWEEN Begin and End Dates to find out if anemployeewas/is member of any group for a certain date range?You can copy/paste this whole post in SQL Query Analyzer or ManagementStudio andrun it once you've made sure there is no harmful code.I am working on an existing database where there is code that is usingBETWEEN logic and three different OR conditions to search for a user thathas worked between begin and end date parameters that you search for.For me the three WHERE conditions with the Begin and End dates are a littleconfusing so I would like to know if there's a better/simpler way to writethis.1- I have groups table with GroupID, Name2- I have employees table with EmployeeID, LastName, FirstName3- I have employeegroups table where the EmployeeID has the GroupID he/shewas/is a member of and from what Begin to what End dates.The employee can never be a member of two groups in any date interval.The employee always was/is a member of a group from a certain to a certaindate and then the next group he/she is a member of a group begins 1 dateafter the previous group membership's end date. Therefore If I worked from2006-01-01 to 2006-01-31 and then I changed group, well in this databasethenext group dates would begin at 2006-02-01 till an Open Ended default dateof2009-12-31.I can also be a member of a group for 1 day: 2006-05-05 to 2006-05-05Please continue to read below at the bottom.*/USE tempdbGOIF EXISTS (SELECT * FROM sysobjects WHERE name = 'EmployeeGroups' AND xtype= 'U')BEGINTRUNCATE TABLE EmployeeGroupsDROP TABLE EmployeeGroupsENDGOIF EXISTS (SELECT * FROM sysobjects WHERE name = 'Groups' AND xtype = 'U')BEGINTRUNCATE TABLE GroupsDROP TABLE GroupsENDGOIF EXISTS (SELECT * FROM sysobjects WHERE name = 'Employees' AND xtype ='U')BEGINTRUNCATE TABLE EmployeesDROP TABLE EmployeesENDGOCREATE TABLE dbo.Groups(GroupID int NOT NULL,Name varchar(50) NOT NULLCONSTRAINT PK_Groups PRIMARY KEY NONCLUSTERED(GroupID))GOCREATE TABLE dbo.Employees(EmployeeID int NOT NULL,LastName varchar(50) NOT NULL,FirstName varchar(50) NOT NULLCONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED(EmployeeID))GOCREATE TABLE dbo.EmployeeGroups(EmployeeID int NOT NULL,GroupID int NOT NULL,BeginDate datetime NOT NULL,EndDate datetime NOT NULL,CONSTRAINT PK_EmployeeGroups PRIMARY KEY NONCLUSTERED(EmployeeID,GroupID),CONSTRAINT FK_EmployeeGroups_Employees FOREIGN KEY(EmployeeID) REFERENCES Employees(EmployeeID),CONSTRAINT FK_EmployeeGroups_Groups FOREIGN KEY(GroupID) REFERENCES Groups(GroupID))GOINSERT Groups (GroupID, Name)SELECT 1, 'Group1' UNION ALLSELECT 2, 'Group2' UNION ALLSELECT 3, 'Group3' UNION ALLSELECT 4, 'Group4'GOINSERT Employees (EmployeeID, LastName, FirstName)SELECT 1, 'Davolio', 'Nancy' UNION ALLSELECT 2, 'Fuller', 'Andrew' UNION ALLSELECT 3, 'Leverling', 'Janet' UNION ALLSELECT 4, 'Peacock', 'Margaret' UNION ALLSELECT 5, 'Buchanan', 'Steven'GOINSERT EmployeeGroups (EmployeeID, GroupID, BeginDate, EndDate)SELECT 1, 3, '1990-01-01', '2004-10-15' UNION ALLSELECT 1, 4, '2004-10-16', '2004-10-16' UNION ALLSELECT 1, 1, '2004-10-17', '2099-12-31' UNION ALLSELECT 3, 2, '1999-11-15', '2002-02-22' UNION ALLSELECT 3, 4, '2002-02-23', '2099-12-31' UNION ALLSELECT 4, 3, '2006-05-17', '2099-12-31'GO--SELECT * FROM Groups--SELECT * FROM Employees--SELECT * FROM EmployeeGroupsDECLARE @EmployeeID INTEGERDECLARE @BeginDate DATETIMEDECLARE @EndDate DATETIMEPRINT 'First example of querying...'SET @EmployeeID = 1SET @BeginDate = 'Sep 18 2005 12:00:00:000AM'SET @EndDate = 'Sep 24 2006 12:00:00:000AM'-- This is the code logic being used in the database I am looking at.SELECT *FROM EmployeeGroupsWHERE EmployeeGroups.EmployeeID = @EmployeeIDAND ((EmployeeGroups.BeginDate <= @BeginDate AND EmployeeGroups.EndDate


Quote:

View 2 Replies View Related

SQL Server 2012 :: Compare Current Year Data With Last Year Data - Day Adjusted

May 25, 2015

Our business get orders through the week with the weekends (Fri & Sat) orders being higher than weekdays. Im wanting to graph this years data with last years and possible the years before but to compare days in such a way that the all the weekdays line up. so comparing 2015 week 1 with 2014 week 1 but with 03/01/2015 (Sat) lining up with 04/01/2014 (Sat) etc.

I'm looking for alternatives to adding or removing days from the dates to solve this issue, i have a date dimension table for the past 5 years that i can use to compare calendar week 201401 with calendar week 201501 but I am finding it a bit inflexable.

View 5 Replies View Related

T-SQL (SS2K8) :: Calculate Sum Of Dates Minus Repetitive Dates

Jul 18, 2014

Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,

[code]....

View 9 Replies View Related

Get The Current Year Using SQL

Aug 13, 2004

Hi everyone,

Currenly, I'm getting the current year in my code, and passing this to my stored procedure.

I'm sure there's a way just to get the current year (ie '2004') just by using SQL.

I was just about to post this question here when I did a bit more digging around and found my solution:

DATEPART(yyyy, GETDATE())

This will get the year.

Hope this helps someone searching for the answer!

View 2 Replies View Related

SQL - Current Year

May 30, 2006

Hello,

I'm trying to write a function which will give me, among other things, the current year.

I'm brand new to SQL so don't really know how to use functions properly yet, although from my VB knowledge, the structure looks very similar.

I've tried SELECT GETDATE() but it gives me an error about getdate not being valid inside a function.

How can I get this to work please ?

Cheers,
J.

View 4 Replies View Related

How Can I Convert 12/2/ To 12/2/current Year

Nov 19, 2007

I have a field in my database that holds a date, the only part of the date I care about is the month and day (12/2/). I'm trying to use this field in a view column to show the for example 12/2/ and the current year (2007). Also if the month and day have passed like 11/1/ then it would be next year (2008).
Can anyone help me with this?

View 7 Replies View Related

How To Get 20 Years From Current Year

Sep 30, 2014

I want an sql query that wil give me listing of 20 years from current year.

View 2 Replies View Related

Insert Current Year

Mar 30, 2006

how to insert current year into table,

I tried ,
insert into tablename values('','',year)

i also need to know how to insert month , day

View 2 Replies View Related

Select Current Year ?

Mar 30, 2006

what is the query to find

current year
current month
current day
using sql query analyser

View 4 Replies View Related

How To Get Current Year From SQL Query?

Oct 22, 2007

How to get current year from SQL query?

View 10 Replies View Related

T-SQL (SS2K8) :: Insert Into Table Dates In Between Two Dates

Feb 28, 2015

I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.

CREATE TABLE hotel_guests
(
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL

[code]...

View 7 Replies View Related

Current Month && Year Query?

Jan 18, 2007

I am writing a usage query, I need to determine what the current month and year is. If the current month is 1, then I need Year-1.  I keep getting this error Line 5: Incorrect syntax near '='.
If I comment out the case statement just evaluate the  (YEAR(User_Date) = YEAR(DATEADD(yy, - 1, GETDATE())))or (YEAR(User_Date) = YEAR(GETDATE())) by itself, the query works. What I am missing? Anybody?1    SELECT     CONVERT(char(10), User_Date, 101) AS userdate, COUNT(*) AS CNT, User_Name2    FROM         Users3    WHERE     (User_Name = 'Joe Bob') AND (MONTH(User_Date) = MONTH(DATEADD(mm, - 1, GETDATE()))) AND 4    case  MONTH(User_Date) when  1 then 5      (YEAR(User_Date) = YEAR(DATEADD(yy, - 1, GETDATE())))6    else (YEAR(User_Date) = YEAR(GETDATE()))7    END8    GROUP BY CONVERT(char(10), User_Date, 101), User_Name9    ORDER BY CONVERT(char(10), User_Date, 101), User_Name 
 

View 5 Replies View Related

Subtract A Year From Current Date

Jan 12, 2005

Hey all, how do you take the current date and subtract a year from it in a SP?

What I want to do is...

Take the current date when the SP is ran, subtract a year, then if my date field is within that range (higher than the date with the subtracted year) it will continue in the query.

Edit: the answer is..

dateadd(yyyy, -1, getdate()) as Date1

View 6 Replies View Related

Trigger - How To Insert Current Year Value

Apr 6, 2014

I have the following working correctly as a trigger, however, I want to change one of the values (2016) to calculate the current year's value.. so in 2017, it would put 2017.

CREATE TRIGGER TRG_NEW_EQUIPMENT
ON ATHLETE AFTER INSERT
AS
BEGIN
INSERT INTO Equipment (Equipment_ID, Equipment_Model, Equipment_Year, Equipment_Brand, Equipment_Color, Equipment_Condition_Rating)
VALUES ('150','Big Spin','2016','K2','Blue','5')
END;
GO

View 3 Replies View Related

How To Find The Current Year And Week

Jul 20, 2007

Hi,
I have a Serial No which has a length as 14.For eg IL010730123456. IL01 is The Default Code. 07 is the current year and 30 is the week of the year. 123456 is Serial. How shoud I find The Year and week with the help of this serial no ie The First day of the 30th week of 2007.Is it Possible?

Plz Help me with an appropriate solution.
Thanks.....

View 5 Replies View Related

Default Parameter = Current Year

Sep 12, 2007

I would like to default my first parameter, which is year, to the current year. I'm querying against a cube. I've tried setting the default =Non-queried =year(today), tried a few MDX field names, and also tried setting the current year as the default in my data set. I can't seem to get the parameter to accept a default.

View 3 Replies View Related

Date In Current Year Or Last Two Months

Apr 17, 2008

Hello

I had a requirement to filter records for the current year only... I went back to the user and explained that the logic wasn't sound... eg if somebody did the search early in January then they would probably get no results back. My suggestion was to go back two months if the current date is in January or february.

So, this is what I have.......




Code Snippet

where cs.startdate > case when year(cs.startdate) = year(getdate()) and month(getdate()) > 2 then
convert(char(4),year(getdate()))+'-01-01' else dateadd(month,-2,getdate()) end





Is there a neater/better way of coding this?

Jon

View 5 Replies View Related

How Can I Get Current Year In The User Defined Function.

Nov 17, 2005

I have a user defined function in datebase SQL 2000. function looks like
create function Getcurrentdate(@month int, @day int) returns smalldatetimebegin
declare date1 as smalldatetime--get current year --convert month, day and year into current date. then return
return date1end
my problem was , after using getDate(). I get error meassage which is "can'not use getDate() inside user function"How can I get current year in the user defined function. Thanks

View 3 Replies View Related

SQL 2012 :: Load Records For Prior And Current Year

Aug 3, 2015

I have some my below requirment to loading some last year and currnet year records for some ID's in my table,

We have to load the ID's that are active at the end of the year for the prior year and ID's that are active as of today for the current year.Here is the scenario when the ID is currently terminated but active at the end of the prior year and the record is not in the table.so, we didn’t load the count for the prior year

Here prior year is 2015-2015 and Current year is 2015-2016

CREATE TABLE remp_year
(ID INT,
STATUS NVARCHAR(100) NULL,
START_DATE DATE NULL,
END_DATE DATE NULL,
date_year nvarchar(10) NULL)INSERT INTO remp_year VALUES (10,'Active','2015-05-26','2015-12-31','2015-2016');

[Code] ...

Here ID 20 and 50 for terminated records is the prior year records so it should count for the last year and those are active in this year those will count for this year.

View 3 Replies View Related

Date Query Problem Last 8 Days, Different Year From Current

Feb 19, 2008

Hi,

I'm having problems creating a query that brings the results based on last 8 days on date column (column9) and diferrent year from the current one.

If I run the each filter,




Code SnippetYear(Column9) <> Year(GetDate())




and




Code Snippet
(Column9 >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 8), 0)) AND (Column9 < DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 7), 0))



it returns the right values.

So far I've got (it returns no results):




Code Snippet
SELECT Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9, Column10, Column11, Column12, Column13, Column14,
Column15, Column16, Column17, Column18, Column19, Column20, Column21, Column22, Column23, Column24, Column25, Column26, Column27,
Column28, Column29, Column30, Column31, Column32, Column33, Column34, Column35, Column36, Column37, Column38, Column39, Column40,
Column41, Column42, Column43, Column44, Column45, Column46, Column47, Column48, Column49, data_anulacao, data_instalacao
FROM Contratos01
WHERE Year(Column9) <> Year(GetDate()) AND (Column9 >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 8), 0)) AND (Column9 < DATEADD(Day, DATEDIFF(Day, 0, GETDATE() - 7), 0))
ORDER BY Column1


Please give me your input.

THX.

View 10 Replies View Related

Transact SQL :: Compare Event Time Value With Current Year And Month

Aug 27, 2015

I have the following code block

CREATE TABLE #tbl_1 (event_time DATETIME2, SID INT ,NAME VARCHAR(20) )
INSERT INTO #tbl_1 VALUES ('2015-08-27 13:47:24.123','150','abc')
INSERT INTO #tbl_1 VALUES ('2015-09-27 13:47:24.123','149','acb')
INSERT INTO #tbl_1 VALUES ('2015-10-27 13:47:24.123','148','cba')
CREATE TABLE #tbl_2 (event_time DATETIME2, SID INT ,NAME VARCHAR(20) )
INSERT INTO #tbl_2
SELECT * FROM #tbl_1 where ? SELECT * FROM #tbl_2

My requirement is to insert values into #tbl2 that are in current month which are event_time values '2015-08-27'

View 4 Replies View Related

Transact SQL :: Pull Records For Current And Previous Calendar Year

Jun 16, 2015

I am looking to pull all records for current & previous calendar year in one query. I know how to pull the current calendar year, but how would I pull current & previous?

select id, list_date

from tableA

where list_date > DATEADD(year,-1,GETDATE())

View 5 Replies View Related

SQL Query - DateTime - Percentage Between Two Dates For A Year (Jan. 01 - Dec. 31)

Jan 14, 2008

Hello, I need to find the percentage of a a given contract start and end date for the year given.For example, the contract_start date is 05/08/2000 and the contract_end date is 04/30/2010, of course this will be 100% if you want to find the percentage for year 2008    but if you wanted to find the percentage for year  2010, then the percentage would be something like 42% (appr. 5 months) (b/c it would for year 2010, the contract would be from 01/01/2010 thru 04/30/2010)I need to find the percent from the beginning of the year, to the end.a few examples:    start: 01/01/2007   end: 05/01/2007   if for year: 2007; this is 4 months @33.33%    start: 05/01/ 2006   end: 06/01/2007  if for year: 2007, then 6 months @ 50% (01/01/2007 - 06/01/2007); but if it was for year 2006 then it would be 7 months @ 58% (05/01/2006 thru 12/31/2006)     start: 01/01/2000   end: 03/01/2010 for year: 2008 then 12 months @ 100% Any help would be valued. Thank you! 

View 4 Replies View Related

Getting Year/quarter/month/day In The Right Order (was Dates)

Feb 28, 2005

I have a program that calls queries (OLAP system) the system includes a dimension of date: Year, Quater, Month, Week

When the result appears in the table, it is not in order? Only the year is in oredr and after that each heirachy is wrong and not in order....not sure how to do this!!!

any help would be grateful!!! not sure what I should be looking at.....

View 14 Replies View Related

Fighting With Dates Trying To Lookup Month And Year

Apr 2, 2007

Hi all I this stored procedure is suppose to turn give the users the ability to put the month and date in so they can see how many each inspector did for that month and year.



Create Procedure LookupYearMonth
AS
SELECT I.[Last Name], I.[First Name],
[Date by Month] = DATENAME(month, MT.[Date] + ' ' + DATENAME(year, MT.[DATE]),
[Count Of Main Table] = count(*)
FROM Inspectors I
INNER JOIN [Main Table] MT
ON (I.ID = MT.Inspector)
GROUP BY I.[Last Name], I.[First Name],
DATENAME(month, MT.[Date] + ' ' + DATENAME(year, MT.[DATE])
WHERE (MT.[Date] >= CONVERT(datetime, @EnterMonthYear)) AND
(MT.[Date] < DATEADD(month, 1, CONVERT(datetime, @EnterMonthYear)))

GO

View 14 Replies View Related

Query Earliest Dates Of Each Year In A Table

Nov 30, 2011

I have to find the earliest date for any existing calendar year in a table...

For instance.....

ClientID Year
1 1/1/2000
1 2/1/2000
2 3/1/2000
2 4/1/2001
2 5/1/2001

The results I need are....

ClientID Year
1 1/1/2000
2 3/1/2000
2 4/1/2001

It is all contained in one table. I have got the earliest years by using the YEAR() function and grouping by it. The only problem is that I am having a problem joining the table back onto itself with the subselect because of it's an aggregate.

Here is the first join I have....

SELECT DT.ClientID, Year1 FROM
(
SELECT ClientID, YEAR(MyDate) as Year1
FROM MyTable
) AS DT
GROUP BY ClientID, Year1
ORDER BY ClientID, Year1

I want to use that as my derived table and then join back to it... but it's not working...

View 1 Replies View Related

SQL Server 2012 :: Selecting Max Between Two Dates For A Given Year

Jul 23, 2015

I have three tables: EMP (ID, NAME), EMPDATE (ID, STARTDATE, ENDDATE), YEAR(YEAR)

I would like to get the most recent date within a given year per each EMP? For example, EMPID 1 can be enrolled in many programs, each program has start end dates. I need to list the most recent date an employee was enrolled (max date between START AND END DATE which ever is most recent enrollment) for a given year. For example, for 2014 his/her most recent enrollment should be 10/23/2014 for year 2014 and 2013-10-24 for year 2013.

SELECT ID, EMP.NAME, DTE.StartDate, DTE.ENDDATE, year
FROM
EMP_DATE DTE join
EMP_INFO EMP on EMP.ID = DTE.ID join
YEAR YR on YR.YEAR = YEAR(DTE.STARTDATE)

DATA SAMPLE:

EMP
ID NAME
1 JOHN

EMP_INFO
ID STARTDATE ENDDATE
12013-10-24 2014-03-11
12014-06-13 2014-03-11
12014-06-15 2014-03-11
12014-09-08 2014-03-11
12014-09-12 2014-03-11
12014-09-14 2014-03-11
12014-01-13 2014-05-17
12014-05-14 2014-06-09
12014-06-10 2014-06-16
12014-08-31 2014-09-04
12014-09-05 2014-09-06
12014-09-07 2014-10-23

YEAR

Year
2012
2013
2014
2015

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved