SQL 2012 :: Calculating Fiscal Week Based On Input Dates
Aug 19, 2014
I need a Query for calculating the fiscal_week based on the input dates (start_date and end_date), though I got a query from this forum, it is not giving me exact result.
the sample is in the excel file with the attachment.
In the excel:
First tab tells you the raw_data what I am using to find the Fiscal_week
Second tab tell you the data where i found the mistake, and how I am expecting the output.
I also have attached the query I have got from this forum, query I have modified for fiscal week.
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
Ok, so I've been struggling with the logic behind this one for a while, so I thought I'd ask you guys for some ideas :)
Basically, I have the following table structure Employee(employee_number, continuous_start_date, ...)
The problem lies in working out a summary of service categories (0-6months, 7-12months, 13-24, 25+). I can work out the length of service in months with the following code
SELECT DateDiff(mm, continuous_start_date, GetDate()) AS 'Service in months' FROM employee
So the first stage is to summarise the length of service into groups as mentioned above.
Then the final stage is working out how many people are in each group!
Hope I have given enough information - and please do not post a full solution - just some hints on how to get the desired result ;)
This function will return working hours between given 2 dates. This function assumes that the break is between 9:45 AM and 10 AM and that Lunch is between 12:30 PM and 1 PM. This function also assumes that the working hours are between 7:30 AM and 4 PM. There is a section for public holidays there. We have a table for that you might not so that piece needs to be fixed.
This function will return working hours between given 2 dates. This function assumes that the break is between 9:45 AM and 10 AM and that Lunch is between 12:30 PM and 1 PM. This function also assumes that the working hours are between 7:30 AM and 4 PM. */ AS BEGIN
--check if one of the dates is null if @StartDate is null or @EndDate is null RETURN 0
I am working with a data set containing several years' of monetary values. I have entries for past dates and the associated values, and I also have entries for future dates. I need to populate the values of the future date records with the values from the same date the previous year. Is there any way this can be done in Power Pivot?
I have a summary report that displayes data on a yearly basis. Currently I am filtering the MDX query with:
SELECT ... FROM
( SELECT ( STRTOSET(@DateShippedYear, CONSTRAINED) ) ON COLUMNS FROM ...)
This works fine if the user wants to see the report by calander year. I am not sure what to do when the client wants to see a report by fiscal year. Each division in the company may have a different fiscal year. I have a dimension which lists division name and fiscal start month. Is there any way massage the data based on another parameter? I do something like that in other reports where I use a parameter to select how many months the user wants to display:
In this case I would have to parce the parameter to get the month start number.
Or would I be better off filtering the data as it is returned to the table or matrix? Here is logically what I'd want to do:
IIf(Parameters!FC.Value = 2 OR Parameters!Division.Value='All'), IIf(Fields!Year.Value=Parameters!Year.Value,"true", "false"), IIf(Fields!Month.Value>= Code.FMonth(Parameters!Division.Value) AND Fields!Year.Value = Parameters!FYears.Value-1 OR Fields!Month.Value < Code.FMonth(Parameters!Division.Value) AND Fields!Year.Value = Parameters!FYears.Value, "true", "false"))
I don't see a place to type in a filter statement like that.......Perhaps a WITH clause in my MDX code which filters each row as "ture" or "false" based on the criteria......
I have to produce a report to calculate no of days based on user input start date and end date.
say for eg: in the tables I have emp name user 'Phani' started work from - EStart 20/11/2014EEnd 10/01/2015 - total days --datediff within his work period he did different roles:
PhaniMarketing (prSt Date) 20/11/2014prE date (28/11/2014) Total 9 days PhaniAdmin (prSt Date) 29/11/2014prE date (20/12/2014) Total 22 days PhaniCRM (prSt Date) 20/12/2014prE date (10/01/2015) Total 22 days Total days 53 Days for this :
[code]...
HOW CAN I USE Case statement to calculate days for given start date and end date. I have to include all three totals, 1 for Job dates, 2, subjobs dates, 3 cal of days for a requested period.
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.
How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?
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?
I'm fairly new to MS SQL, but I'm trying to query the time elapse between two dates. Example below...
Select dbo.res.book_date, dbo.res.arrive_date, Cast (dbo.translog.systemdate as datetime) From dbo.res Where dbo.res.resid > '500'
The third line is where I need help with a statement. I need the elapse time. Ex. if the book date is 20050820100000 and the arrive date is 20050820120000, I would like the third Select statement to return 2:00 hours or something similar. Thanks.
I have a table in my sql server that calculates renewal dates for me, that date is based on the final suit date. The table has the ssn, and the FinalSuitDate its in a one to many relationship with the employeetable.
the Finalsuit table is suppose to calculate the renewal dates(which I'm trying to do in a query) my original expression in access was using dateserial FirstRenewal: DateSerial(Year([FINALSUITDONE])+2,Month([FINALSUITDONE])+1,Day([FINALSUITDONE])=30), but sql does not recognize that.
For Example
If the Finalsuit is 12/01/2000 then the renewal would be 12/31/2002 the renewal are to be done on the last day of the month and two years from the finalsuit date. Problems is I'm having problems doing this in sql servers query?
I have a project where I need to populate a table with a Begin and an end date based on another field with in the table. my example is that the other field will have a value of "monthly" I want then a record to be entered on the table with a begin date of November 1 and the en date as November 30th. I want this record to get populated only on business days. my thinking is a stored procedure that runs daily and only creates a record at a time. i am thinking if the user selects weekly the dates populated would be november 1 = begin date and Novemeber 2nd = end date i want to fill out a current month at a time as if the account closes i will not need the extra cases.
Hello, Can anyone out there tell me if there's a simple way to calculate the number of week days between two dates in TSQL? Need it to calc. average turnaround times, excluding weekends. Can do it v. easily in VB, but gets a little more tricky in TSQL as there's no way to return the number of Sundays and Saturdays between the two dates. Any help much appreciated !
Jon Reade Sql Server DBA NEC Technologies (UK) Ltd.
Hello, Can anyone out there tell me if there's a simple way to calculate the number of week days between two dates in TSQL? Need it to calc. average turnaround times, excluding weekends. Can do it v. easily in VB, but gets a little more tricky in TSQL as there's no way to return the number of Sundays and Saturdays between the two dates. Any help much appreciated !
Jon Reade Sql Server DBA NEC Technologies (UK) Ltd.
I want to automatically populate an alternative date column with hijri (Arabic) dates. So I will have two date columns in my datawarehouse table, one is for gregorian dates, the other for the equivalent in Hijri - a bilingual date system if you like. Now I guess I could do it in the SSI ETL tool and use Julian Dates, but the problem is storing the resulting value. - what data type should I use for hijri dates?
The dates can be input by the user in either format, which means the alternate date has to be populated.
Eventually I hope to make a bilingual system, I don't understand the user-defined types or how to define them, but I suspect they could be used in someway to help. Why am I doing this? - well eventually I hope to populate a cube for analysis and deliver a dual-language-dashboard using the new CTP3 stuff.
Any suggestions on how I can make this dual-date system? - with a view to making a bilingual system eventually.
I have two tables and they have a foreign key relationship. It will be a 1 to Many realationship. Table1- the primary key table has the following columns ContentID - int identity field DateAdded- datetime Table2 - The foreign key table, has the following relevant fields FK - int foreign key version - int When an initial record is added there is a record added to each table. The ContentID in Table1 will match the FK field in Table2. Then a user will be able to "edit" the record in Table2, but instead of writing over the record that is already there, a new record will be added to Table2. I would like to calculate the version field where each time a record is added for the particular ContentID/FK it adds 1 to the last version that was added to that particular ContentID/FK. Does that make sense. I am trying to implement some type of revision tracking for my CMS that I am building and this is the only way I can come up with because my client is worried that somebody will go in and make incorrect changes to their site and they want to be able to roll it back to a previous version. I would appreciate any ideas. Thanks, laura
What I'd like to do is get the percentages for each available status in T1, from T2-- I can do this; however, I'm having trouble in the cases where a particular ID hasn't been in one of the status (in other words, calculating a 0%). What I'd like to get from these two tables is:
I want to pull dates from my database that are between to set dates i have written a query that does this which looks like this: "SELECT OCH_ID, empno, Selected_OCD, Start_Time, End_Time, Selected_OCDay, Selected_DOM, Selected_Month, Selected_Year FROM dbo.ICT_On_Call_Hours WHERE (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) >= CONVERT (datetime, LEFT ('12/02/2007', 2) + '/' + SUBSTRING('12/02/2007', 4, 2) + '/' + RIGHT ('12/02/2007', 4))) AND (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) < CONVERT (datetime, LEFT ('14/02/2007', 2) + '/' + SUBSTRING('14/02/2007', 4, 2) + '/' + RIGHT ('14/02/2007', 4)))" This works when the dates are included in the statement but when i try and use parameters to pull them in like this: "SELECT OCH_ID, empno, Selected_OCD, Start_Time, End_Time, Selected_OCDay, Selected_DOM, Selected_Month, Selected_Year FROM dbo.ICT_On_Call_Hours WHERE (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) >= CONVERT (datetime, LEFT ('@Choice1', 2) + '/' + SUBSTRING('@Choice1', 4, 2) + '/' + RIGHT ('@Choice1', 4))) AND (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) < CONVERT (datetime, LEFT ('@Choice2', 2) + '/' + SUBSTRING('@Choice2', 4, 2) + '/' + RIGHT ('@Choice2', 4)))" I then recieve the following error message why is this? ERROR -------------------> Syntax error converting datetime from character string. Any Help would be greatly appreciated thanks
I'm creating a package that needs to go to an FTP site (FTP Task), download a file, unzip it and then process a series of table loads for the 12 text files that will be unzipped. My problem is that the zip file is a date (yyyymmdd.zip) which is normally the previous day of execution EXCEPT on Mondays when it would be the previous Friday's date. My thought is that IF (magic question) I could determine the day of the week in the SSIS package, I know that Tuesday-Friday is just a formatting exercise of getdate()-1 and Monday would be getdate()-3 but I can't seem to find a way (function?) that will allow me to determine the day of the week?
I have two tables that look like this (below). One tells me the name of my product, the Amazon Category it is in & the amount that I want to sell it for. The other tells me the Category & the fee for that category. So far so good. Though it gets tricky in the sense that some categories have two tiers. So in Electronics, the fee for $0.00 - $100.00 is 15%. But from $100 and up it is 8%.
Since it has two columns & both of the new columns pertain to the fee of my product, I can't figure out how to use both at once. For my $599.99 example it would be ($100 * 0.15) + ($499.99 * 0.08) = $55.00. Would I pivot the data? If not, how would I group it to be considered together?
I need to be able to take the latitude and logitude of two locations and compare then to determine the number of miles between each point. It doesn't need to account for elevation, but assumes a flat plane with lat and long.
Does anyone have any algorithms in T-SQL to do this?
I am trying to create a job that would backup a DB dynamically to a different folder based on the week day. So if it's Saturday, backup to folder Saturday.
I have this
DECLARE @BackupLoc nvarchar (100) DECLARE @DayOfWeek nvarchar (100) set @BackupLoc = N'D:Backup' set @DayOfWeek = (SELECT DATENAME(dw,GETDATE())) set @BackupLoc = @backuploc + @DayOfWeek
[Code] ....
But it's giving me permission errors, although the AGENT and MSSQL service accounts are members of the local admins group.
I have a search page that allows users to type/select values contined within the entry they're looking for. My SELECT statement returns columns in a table that get compared to the user input. So if someone selected Status (Open) then all of the 'Open' Request entries should populate the search page. How do I phrase the SELECT statement to compare values if the user gives them, but ignore the fields where no data was input when it's searching? So a search where no values were entered would return every Request Entry instead of an error; no entry. Thanks!
Let's say I have a table with 3 fields: an ID field (primary key, set as an id field, etc.), a Name field (nvarchar50), and an Age field (int). I have a form that has three elements: DropDownList1: This drop down list contains 3 choices- "=", ">", and "<". Age: This text box is where someone would enter a number. Button1: This is the form's submit button. I want someone to be able to search the database for entries where the Age is either equal to ("="), greater than (">"), or less than ("<") whatever number they enter into TextBox1. The code-behind is shown below. The part I'm confused about is that if I load this page, the query works the -first- time. Then, if I try to change the parameters in the form and submit it, I get the following error: "The variable name '@Age' has already been declared. Variable names must be unique within a query batch or stored procedure." Any help would be appreciated. Here is what I'm using in my code behind: protected void Button1_Click(object sender, EventArgs e) { System.Text.StringBuilder sb = new System.Text.StringBuilder(); sb.Append("SELECT * FROM People WHERE Age "); switch (DropDownList1.SelectedValue) { case "=": sb.Append("= "); break; case ">": sb.Append("> "); break; case "<": sb.Append("< "); break; } sb.Append("@Age"); SqlDataSource1.SelectCommand = sb.ToString(); SqlDataSource1.SelectParameters.Add("Age", TypeCode.Int32, Age.Text); }
I am building a pretty simple intranet application where we need to be able to track changes to tables. The tracking feature do not need to be very advanced, we just need to see who changed something and what it was. Therefore I decided just to use a trigger based solution, but need some input/advice since my SQL skills is somewhat lacking.
Consider the following (mock-up) schema:
-- My content table CREATE TABLE [Content]( [ContentGuid] [uniqueidentifier] NOT NULL PRIMARY KEY DEFAULT (newid()), [Data] [nvarchar](4000) NOT NULL, [ChangedBy] [nchar](10) NOT NULL, [MaybeNull] [int] NULL )
-- My history table CREATE TABLE [History]( [ChangedTable] [nvarchar](50) NOT NULL, [ReferenceGuid] [uniqueidentifier] NOT NULL, [ChangedBy] [nchar](10) NOT NULL, [ChangedOn] [datetime] NOT NULL DEFAULT (getutcdate()), [IsDelete] [bit] NOT NULL DEFAULT ((0)), [Changes] [xml] NOT NULL ) ON [PRIMARY]
-- My insert/update trigger CREATE TRIGGER [RecordChangeOnInsertUpdate] ON [Content] AFTER INSERT,UPDATE AS BEGIN SET NOCOUNT ON;
DECLARE @Changes xml SET @Changes = (select * from [inserted] for xml raw ('Content'), elements xsinil)
INSERT INTO [History] ([ChangedTable] ,[ReferenceGuid] ,[ChangedBy] ,[ChangedOn] ,[IsDelete] ,[Changes]) SELECT 'Content', ContentGuid, ChangedBy, getutcdate(), 0, @Changes FROM [inserted] END
-- My delete trigger CREATE TRIGGER [RecordChangeOnDelete] ON [Content] AFTER DELETE AS BEGIN SET NOCOUNT ON;
DECLARE @Changes xml SET @Changes = (select * from [deleted] for xml raw ('Content'), elements xsinil)
INSERT INTO [History] ([ChangedTable] ,[ReferenceGuid] ,[ChangedBy] ,[ChangedOn] ,[IsDelete] ,[Changes]) SELECT 'Content', ContentGuid, ChangedBy, getutcdate(), 1, @Changes FROM [deleted] END
I have decided to use the "one history table for all table changes" method. The changes for a row is stored in a xml column which obviously limits the total size of columns in a table being tracked, but that is not a problem in my application. In general I like this set up, since I will be able to change the schema continuously without having to change the triggers, and since the application will probably evolve a lot over the coming months this is pretty important to me.
EDIT: I should add that all the tables I will be tracking have a uniqueidentifier column. This makes it possible to related table rows in the different tables being tracked with their history.
A few concerns with the above SQL:
- Can the inserted/deleted table change between "SET @Changes = (select * ..." and the "INSERT INTO ..." statement, such that the data is not valid? If so, how to work around that?
- If I were to (hypothetically) perform a "UPDATE [Content] SET [Data] = 'something'", not only is my update trigger called once for each row updated, but the XML added to the inserted row in the history table ([History].[Changes]) represent all the rows updated in the batch update. How do I get around this?
