Create WHERE Statement That Will Calculate Values From Current Fiscal Year To Last Complete Month

Feb 3, 2015

I'm trying to create a WHERE statement that will calculate values from our current fiscal year to the last complete month.I'm using code that was created for us that does the calculations for our entire fiscal years. I thought I had fixed the WHERE statement to work like we wanted last year, but it appears to be broken now after trying it again in January and February. I'm guessing my WHERE statement only works for March and up, but how to get it to work for every month. Most attempts I'm trying it's just returning very large and inaccurate values.

I included my WHERE statement below of what I originally had that worked last year. The @BeginYear/Month/etc are retrieved from a different table and @Month is just set to MONTH(GETDATE())-1.

WHERE
(YEAR(SA3.DocumentDate)=@BeginYear AND MONTH(SA3.DocumentDate)>=@BeginMonth AND MONTH(SA3.DocumentDate)<=@Month)
OR
(YEAR(SA3.DocumentDate)=@EndYear AND MONTH(SA3.DocumentDate)<=@EndMonth AND MONTH(SA3.DocumentDate)>=@Month)

View 6 Replies


ADVERTISEMENT

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

Variables And Grouping - Calculate Net Sales And Margin Percentage For Each Month Of Current Year

Mar 11, 2014

I need to calculate “NET_SALES” and “MARGIN_PERCENT” for each month of the current year … the following returns the same values for each month in the list, which are for the current month. Taking out the GROUP BY line works fine for an overall number.

SALES_MONTH, NET_SALES, MARGIN_PERCENT
January, 1246627.69, 24
February, 1246627.69, 24
March, 1246627.69, 24
-------------------------------------------------

DECLARE @NetSales DECIMAL(18,6)
DECLARE @Cost DECIMAL(18,6)

SELECT
@NetSales = sum(IL.MERCHANDISE+IL.TAX)
,@Cost = sum(IL.COST)
FROM INVOICELINE IL

[Code] .....

View 2 Replies View Related

SQL Server 2008 :: How To Calculate Number Of Days Since Start Of Fiscal Year

Jul 29, 2015

Our fiscal year starts on July 1st. Each month they call a period - so July is period 1, August is period 2, etc.

They are wanting a report that pulls numbers for a given period. There are parameters for them to select the fiscal year and the fiscal period, and then it calculates the numbers for that period. That part works fine.

Now they want me to do some calculations, and one of them is to divide one of the numbers by the # of days since the fiscal year. So if they choose July, it would be 31 days. If they choose August, it would be 61 days, etc. How can I set this up to calculate the number of days when they really aren't entering a start date, it's just a fiscal year and period.

Is there a way to calculate a date field that is 07/01/xxxx where xxxx is the fiscal year they chose? Also a way to calculate a date field that would be the last date of the month for the fiscal period and year they chose?

I suppose I could add 2 other parameters where they enter the start of the current fiscal year, and the last day of the period they're running it for, and use a datediff to calculate that. Just seems kind of redundant.

View 8 Replies View Related

To Display Month Name And Year Instaed Of Complete Date...in SSRS Reports

May 6, 2008

Hi All,
I have a sample data like this..I have added the cases for the particular worker in the table..My question is that when displaying the cases for the particular month...the date should not be displayed..instead of date 2008-04-30 I have to display...like April 2008...if the date is 2008-10-20...it should display as October 2008...can anyone please help me with this...
312 KRISTI WHITE 865400 2008-04-30 2
312 KRISTI WHITE 1000264311 2008-04-30 3
312 KRISTI WHITE 1000430815 2008-04-30 1
312 KRISTI WHITE 1000660614 2008-04-30 1
312 KRISTI WHITE 1002371318 2008-04-30 2
312 KRISTI WHITE 2003722520 2008-04-30 4


Thanks
Chaitanya.

View 10 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

SQL Server 2012 :: Calculate Customers Age And Value By Month And Year?

Sep 22, 2014

What's the best way to calculate a customers age and value by month and year?

I need to be able to calculate customer value by month and year, and then to calculate their age at each month in time. I've found a way of grouping sales by month and year that includes age for a particular contact like this:

select fh.contact_number
, concat(year(fh.transaction_date), '-', month(fh.transaction_date)) as transaction_month_year
, cast(fh.transaction_date as date) as transaction_date
, sum(fh.amount) as ttl_amount_in_month

[Code] .....

It seems to work, but any better way to achieve this?

View 1 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

Reporting Services :: Displaying Sum Of Current Month And Year To Date Without Using Two Separate Datasets?

Jun 4, 2015

I am trying to use one dataset rather than two and was hoping to then filter the data via a table or matrix.

This is my dataset 

SELECT
Practice.ibvStaffCategorisation.StaffId
,SUM(Practice.ibvStaffTotalsCL2Y.ChargeableMinutes) AS Sum_ChargeableMinutes
,SUM(Practice.ibvStaffTotalsCL2Y.NonChargeableMinutes) AS Sum_NonChargeableMinutes
,SUM(Practice.ibvStaffTotalsCL2Y.ChargeableAmount) AS Sum_ChargeableAmount
,SUM(Practice.ibvStaffTotalsCL2Y.NonChargeableAmount) AS Sum_NonChargeableAmount
,Practice.ibvStaffTotalsCL2Y.Period

[code]....

I would like to display two rows of data for each StaffId one representing the current period and the other all periods to date so the table would look something like below.

StaffId | Non Chargeable Time | Chargeable Time

CJJ | 0:20 | 4:20
     | 4:50 | 19:20
JN | 0:05 | 5:30
     | 1:30 | 25:30

The above shows two separate StaffId figures the first line for each shows non chargeable and chargeable for the current period and the second line a total of all periods in that year.

I have managed to get the first row to display only figures from the current period by using a filter however it also applies the same filter to the second row in the group. I have also tried to group the rows but am drawing a blank.

View 6 Replies View Related

Reporting Services :: Show Current Month / Date And Year In Report Header In SSRS

Jun 29, 2015

How to show the CurrentMonthanddateandyear in my report header in ssrs?

1.How to show the currentdateandMonthyear exmple date format like June 29 2015 on my report header.

2.How to change the report rdl name with the same name like EmpUpdatedreportJune 29 2015.rdl ,it is possible to create and change the rdl file name with the current dateandmonth.

View 9 Replies View Related

Create A Table That Shows Month / Day And Year?

Jan 30, 2015

How could I create a table that shows the month, each day in that month and the year and give me the option to set a start date & and end date? This is sql server 2005. I have used this before, but it doesn't allow me to see the days in the month. Essentially I want my output to be

Day Month Year
01-01-2012 January 2012
.......
01-02-2015 January 2015
.....
03-01-2015 March 2015
....
08-02-2020 August 2020

Code:

CREATE TABLE #yourTempTable([MonthName] VARCHAR(9), [Year] INT);
WITH CTE(N) AS (SELECT 1 FROM (SELECT 1 UNION ALL SELECT 1)a(N)),
CTE2(N) AS (SELECT 1 FROM CTE x CROSS JOIN CTE y),
CTE3(N) AS (SELECT 1 FROM CTE2 x CROSS JOIN CTE2 y),
TALLY(N) AS (SELECT 0 UNION ALL
SELECT TOP 11 ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM CTE3),
DATETALLY(N) AS (SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE())+N, 0)
FROM TALLY)
INSERT INTO #yourTempTable
SELECT DATENAME(month,N), YEAR(N)
FROM DATETALLY;
SELECT *
FROM #yourTempTable;

View 4 Replies View Related

Fiscal Year Date Help??? I Need The First Day Of The Year To Be 01/27.

Feb 4, 2008


Greetings All,
I have a SQL question that maybe someone out there can help me with. Our fiscal year starts on 01/27. I want to write a query that I can pass a date to and it will return the week number (DATEPART("ww", someDate)) of the year using the Fiscal Year Start Date as the base. Datepart works great except it figures the first day of the year as 1/1. Does anyone know how I can make it work with a first day of the year equal to the fiscal year date 01/27. Any help would be appreciated.
TIA,
~ck

View 2 Replies View Related

SQL Server 2012 :: Calculate Total On-peak And Off-values For A Month

Nov 25, 2014

SQL query to calculate the total on-peak and off-values for a month as well as the Max/highest on-peak/offPeak hourly value for that month.

On a daily basis i store the hourly values of the meter in a SQL table.

On-Peak

Summer: Apr-Oct hours(7-22) on weekdays (M-F)
Winter: Nov-Mar hours(8-23) on weekdays (M-F)

off-Peak

Summer: Apr-Oct hours(0-6,23,24); Weekends (Saturday & sunday) ; all public holidays during those months as to be considered as off peak

Winter: Nov-Mar hours(0-7,24);Weekends (Saturday & sunday); all public holidays during those months as to be considered as off peak

Here is the DB Table Structure:

Column Name & Data Types
HourId - Uniqueidentifier
CustomerName - nvarchar(50)
Readingdate - datetime
IntegratedHour - TinyInt
Load - decimal(18,4)
Generation - Decimal (18,4)
LastModified - Datetime
ModifiedBy - nvarchar(50)

View 9 Replies View Related

Transact SQL :: Create Columns For Year / Month And Day From Epoch Column

Jul 21, 2015

I'm wanting to create reports in SSDT 2012 which is connected to a 2008R2 database.  I want to have parameters on for my reports where a user is able to select a year such as 2014.  Unfortunate my table containing the data has two columns with a date value.  the first is of the int type and contains an epoch formatted date.  The second is a varchar type and shows the date as 2015-07-01 08:00:00.  I would like to be able to write a query to return the year, monthnumber and daynumber from either of these columns. 

View 5 Replies View Related

SQL Server 2012 :: Create View To Allocate Amount Per Month - Financial Year

Mar 21, 2015

I like to create an SQL view to divide amount 300,000 between 12 month starting from Month July 2014 to June 2015 as shown below

Amount Month Year
25,000 July 2014
25,000 August 2014
25,000 September 2014
25,000 October 2014
25,000 November 2014
25,000 December 2014
25,000 January 2015
25,000 February 2015
.
.
.
.

25,000 June 2015

View 7 Replies View Related

How To Select Fiscal Year?

Mar 13, 2007

strSQL = "SELECT DISTINCT YEAR(Date1) As Year FROM WorkShopReq WHERE Year(Date1) > 1999 ORDER BY 1"oRsYears.Open strSQL, DB_CONNECTION'If no years has been specified select the last oneIf intYear="" then    While not oRsYears.EOF        intYear = oRsYears("Year")        oRsYears.MoveNext()    Wendend if'Move to firstoRsYears.MoveFirst() The above code works and displays the data for the Years from 1999 onwards which get inserted into a drop down box in the web page from where the user selects the year and accordingly the all the data for that year displays on to the web page.  * Field Date1 is smalldatetime  Now I want to select the data between fiscal year.  e.g. 6/1/year(date1) to 5/31/year(date1)+1 So i edit the strSQL statement strSQL = "SELECT DISTINCT YEAR(Date1) As Year FROM WorkShopReq WHERE Date1 BETWEEN 'Year(Date1)/5/31' AND '(Year(Date1)+1)/6/1' ORDER BY 1" I get this errorError Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)[Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting character string to smalldatetime data type./wsr blue/Default.asp, line 24 What am i doing wrong.      

View 2 Replies View Related

Fiscal Year Search

Nov 29, 2006

HiI am trying to perform a search that will return records based on afiscal year search of the bill_Date. The user gives the year then Iwant to search based on the fiscal year (July 1 - June 30) for the yeargiven. The table looks like thisBill Tableid_Num bill_date bill_amount23 7/1/2005 500.0033 12/2/2005 600.0044 3/3/2006 700.00I have triedSelect Bill.id_num, Bill.bill_date, Bill.bill_amountfrom Billwhere Bill.bill_date BETWEEN 7/1/ + @year and 6/30/ + (@year +1)Plus a variety of other fruitless concoctions....but nothing seems towork. Any help would be appreciated.

View 4 Replies View Related

How Can I Add A Fiscal Year Column To A View

Oct 6, 2007

I have a view that shows payment amount, payment date. I need to add a column to the view that shows what fiscal year the payment belongs to.
How can this be done?
I do have a table that has the fiscal start and end in it, tblGlobal with fields FiscalYearStart and FiscalYearEnd.
 

View 16 Replies View Related

Query For Fiscal Year Data

Mar 17, 2014

I am using the query below and it is working great to give me montly averages. I need to find the fiscal year to date averages for the same data. I think all I should need to change is the Select and Group by sections but I am having no sucess.

SELECT Format([CallDate],"yyyy mmm") AS [Month], Count(DailyCallStats.CallDate) AS CountOfCallDate, DailyCallStats.Agent, Avg(DailyCallStats.Inbound) AS AvgOfInbound, Avg(DailyCallStats.Outbound) AS AvgOfOutbound, Avg(DailyCallStats.TotalCalls) AS AvgOfTotalCalls, Avg(DailyCallStats.AvgWrap) AS AvgOfAvgWrap, Avg(DailyCallStats.ParkTime) AS AvgOfParkTime
FROM DailyCallStats
GROUP BY Format([CallDate],"yyyy mmm"), DailyCallStats.Agent, Format([CallDate],"yyyy mm")
ORDER BY Format([CallDate],"yyyy mm");

View 4 Replies View Related

Grouping My Month/year Desending By Month/year

Dec 11, 2006

i have some classes that I want to group by month/year (note:i dont need the day of the month)
how do i wirte my sql so it only gives me the dictinct groups month/year  of the classes I have so that it comes out like so..
11/2006
12/2006
1/2007
3/2007
i try with my sql below but i cant get the groups th come out in order. i dont think it sees it as a date value.
dbo.classgiven.classdate date of the class.thank you all 
SELECT DISTINCT { fn MONTH(dbo.classgiven.classdate) } " + "/"  + "{ fn YEAR(dbo.classgiven.classdate) } AS monthyear,{ fn MONTH(dbo.classgiven.classdate) } AS monthcode   FROM  dbo.classT INNER JOIN dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcode WHERE (dbo.classT.discount = '-1') AND  (dbo.classT.coned IS NOT NULL)", conNorthwind )

View 9 Replies View Related

Converting Fiscal Month

May 15, 2008

Hello,
I'm having a problem with converting calender months to fiscal months.
For April (04) calender month, I have 11 as my fiscal month like below.


[FISCAL CALENDAR].[DAY].&[11]&[2008-04-15T00:00:00]

Here is a parameter 'param' which is a 'datetime' datatype, so i'm basically passing a calender date , so i wrote the query as following:


="[FISCAL CALENDAR].[DAY].&[" + (Month(Parameters!param.Value)+7).ToString + "].&[" + CDate(Parameters!param.Value).ToString("s") + "]"

But it is wrong as I get results like the following:

For April (04), I get 11, which is correct.


[FISCAL CALENDAR].[DAY].&[11].&[2008-04-15T00:00:00]

But for June (06), I get 13 when I'm supposed to get 1.

[FISCAL CALENDAR].[DAY].&[13].&[2008-06-15T00:00:00]

How do I change my query? Please help. I'm struggling with this since yesterday.

View 4 Replies View Related

Reporting Services :: Calculate Sales Percentage Difference Between Selected Year And Previous Year In A Matrix

Mar 27, 2015

I'm trying to generate a report using matrix like this

                                                      Month
Product     PreviousYearSalesAmount    SelectedYearSalesAmount      %SalesDifference

I can populate year sales amount, but i cant calculate the percentage.

Note: Month and Year are passed as parameters.

View 5 Replies View Related

Sql For Financial Reporting Periods This Month, Last Month, This Quarter, Last Quarter, This Year, Last Year

Oct 26, 2006

Does anyone know of a way to use a funtion for returning records based on fiscal reporting periods like Quickbooks uses for example "This Month", "Last Month", "This Quarter", "Last Quarter", "This Year", "Last Year". While I realize that I can create a very long date time parsing routine  for this but it is not very elegant or useful. I thought there might be a way to do this already with an existing function.I have created a stored procedure that I pass a @ViewRange Parameter to and it returns the records that I want but I need this ability in several procedures and wanted to turn it into a stored procedure.IF @ViewRange = 'This Month' SELECT TOP 20 Customer.LastName AS Customer, SUM(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE (MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP)) AND (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY SUM(Sales.AmtCharge) DESC;IF @ViewRange = 'Last Month' SELECT TOP 20 Customer.LastName AS Customer, Sum(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE(MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP) - 1) And (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY Sum(Sales.AmtCharge) DESC; Any ideas? 

View 8 Replies View Related

Help SQL Statement Create Calculate Percent ?

Apr 13, 2006

I have a following table :
ID ------- Answear ------ Vote
1 ------- 1|2|3|4 ------- 3|5|3|2
2 ------- 1|2|3|4 ------- 2|5|3|1
3 ------- 1|2|3|4 ------- 2|5|7|2

So, I need to create : ,

ID ----- Answear ------ Vote ----- Total ----- Percent
1 ----- 1|2|3|4 ------ 3|5|3|2------ 13-----23.08|38.46|23.08|15.38
2 ----- 1|2|3|4 ------ 2|7|8|1------ 18-----.....
3 ----- 1|2|3|4 ------ 2|5|7|2------ 16-----.....

Any one help me ?
Thanh you very much.
PS :(3/13)*100|(5/13)*100|(3/13)*100|(2/13)*100 after calculate : 23.08 % |38.46 %|23.08 %|15.38 %

View 6 Replies View Related

Performing Date Range Queries Based On A Non-Calendar Fiscal Year

Sep 20, 2006

Hi,

Using SQL Server 2000, I need to perform date range type queries that involve my company's Fiscal Year, which is not the same as the calendar year. My company's Fiscal Year if from Sept 1 to Aug 31, where Aug 31st year determines the Fiscal Year. For example, since today's date is 09/20/2006, the current Fiscal Year is 2007.

An example of a typical query requirement:

Find all the sales figures to-date for the current Fiscal Year. So, a WHERE clause will consist of a date range query from 09/01/2006 to 8/31/2007.

Initially, I created a Function to find the current Fiscal Year based on the current date, by calling the GETDATE() function and passing the results to the following function:

CREATE FUNCTION dbo.fnGetFY (@CurrentDatetime datetime)
RETURNS int
AS
BEGIN
DECLARE @FY int
IF (SELECT MONTH(@CurrentDatetime)) > 8
SET @FY = YEAR(@CurrentDatetime) + 1
ELSE
SET @FY = YEAR(@CurrentDatetime)
RETURN(@FY)
END

So, the view queries that involve the Fiscal Year call the above function.

However, these function calls drag down the VIEW query response time to the point where the time is either unacceptable or an ODBC Timeout occurs, even with Query Analyzer.

Is there a way to create a Global server parameter to hold the current Fiscal Year value, so function calls are not necessary? Or set Fiscal Year date ranges for a database or server system parameter?

Does anyone know of a efficent, response timewise, way to handle Fiscal Year date range queries?

Will appreciate the help!!!

View 5 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

To Send The Date Format If The User Has Specified Only Month And Year, Or Only The Year

Aug 30, 2004

I have three web form controls, a ddl that contains the day, another ddl that contains the month and a textbox that contains the current year. To send the date chosen by the user to the database, I join the three web form control values so that the resultant string is ‘day/month/year’ thus:

CmdInsert.Parameters("@Date").Value = day.SelectedItem.Value + "/" + month.SelectedItem.Value + "/" + year.Text()

And the resultant string is: dd/mm/yyyy, for example 30/08/2004.
But the problem is if the user does not select any day or any day and month, then the resultant string is for example; 00/08/2004 or 00/00/2004, but the problem is the database does not accept this format as datetime. How can I do it?

I want the user has the possibility to chose as well only the month and year, and as well only the year. Is it possible to send to the database the datetime format with only the month and year, or only the year?

Thank you,
Cesar

View 4 Replies View Related

SQL Server 2008 :: Return Fiscal Year Based On YYYY-week Format

Nov 6, 2015

I have a table where hours are being loaded in a weekly basis. The YearWeek is populated when the data is loaded. The value format of the Year Week is 2015-39, 2015-41, etc. I need to calculate the total hours per Fiscal Year.For example, week '2015-39' will be return FY15 and week '2015-41' will return FY16, and so on. By extracting the year, I can do a group by and have total hours for each year.

Currently, I have it working by splitting the value into year and week and then looping through each year and week, so I can assign the totals to the corresponding FY.select sum(hours) as total, yearweek from tablename group by yearweek...Then I loop through using C#.I can return the FY using an actual date,how to do it for year-week format for any given year.

select CASE
WHEN CAST(GETDATE() AS DATE) >
SMALLDATETIMEFROMPARTS(DATEPART(YEAR,GETDATE()),09,30,00,000)
THEN
DATEPART(YEAR,GETDATE()) + 1 ELSE DATEPART(YEAR,GETDATE())
END AS FY

View 9 Replies View Related

T-SQL (SS2K8) :: Create Union View To Display Current Values From Table A And All Historical Values From Table B

May 6, 2014

I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.

Q. Can this be done with one joined or conditional select statement?

DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid

View 9 Replies View Related

Select Month/year When Range Spans Year

Feb 25, 2004

I'm using PHP with SQLServer2k to create a page containing monthly counts of episodes at a facility occurring between two user selected month/year combinations. For instance, the user could select 10/2003 and 2/2004 and facility X and get a line for each month showing the count of episodes occuring in that month.

The problem is that the episode date is stored in three integer fields (epiday, epimonth, epiyear) and I'm having a terrible time getting them into a format where I can use them in a between statement.

I've tried evaluating the parts of the episode date seperately like:


where
(epimonth>=10 and epiyear=2003)
or
(epimonth<=2 and epiyear=2004)


and that works, but what happens when someone wants to see from 10/2002 to 2/2004?

Any suggestions on the best way to do this?

View 5 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

Getting Date In Day/month/year Instead Of Month/day/year

Feb 2, 2004

I am trying to get my db to return a date in the format day/month/year but its returning the american version month/day/year.

I'm using a DatePart function that converts my date:


CREATE FUNCTION dbo.DatePart
( @fDate datetime )
RETURNS varchar(10)
AS
BEGIN
RETURN ( CONVERT(varchar(10),@fDate,101) )
END


This returns te american version, can anyone help me to get this to convert the UK way.

Thanks

View 3 Replies View Related

T-SQL (SS2K8) :: Convert Number Of Month In Integer Into How Many Year And Month

Sep 10, 2014

This is my table and data

CVID | WorkExperience
--------------------------------
2838736
68181101
96568122
1135484

I need to convert into this result

CVID | WorkExperience
--------------------------------
283873 years
681818 years 5 months
9656812 years 2 months
1135484 months

View 5 Replies View Related







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