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.
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.
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?
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.
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
DECLARE @Date SET @Date = 2012 DECLARE @Year int SET @Year = (SELECT DATEPART(yyyy,@Date)) SELECT @Year AS Year --SELECT * -- FROM [Orders].[dbo].[Orders] od -- WHERE .Orderl_Date < @Date; ----WHERE DATEPART(yyyy,@Date)= @Year ----WHERE DATEPART(yyyy,od.Order_Date)= @ArchiveYear
We were asked to fix a query to get rows from a prior year history table that did not match to rows in the current year to show a variance from one year to the next. Rows must match on [corpnbr],[plincd],[pgrpcd] and [pitmcd]. If the combination has rows in the current and prior year ([hstyr]) then everything is fine. However, if they have rows in the prior year (e.g. [hstyr]='2014') but not in the current year (e.g. [hstyr]='2015') then they do not show in the result. Below is how they designed the table and below that is the stored procedure to pull the records.
CREATE TABLE #TEMP ( TYPE VARCHAR(10), SEQ INT, SUB_TYPE VARCHAR(10))
[Code] ....
Now for each type the seq is very important. Effectively by order of seq the subtype stays the same until another subtype changes it. So for TYPE1 100,110 and 150 are A. 170, 200,220 are B. 230 and 250 are C and so on.
However as you can see the data isnt actually stored in the row. I need a select statement that shows this data.
I have done this:
SELECT t1.*,t3.SUB_TYPE FROM #TEMP t1 CROSS APPLY (SELECT MAX(SEQ) SEQ FROM #TEMP AS t2 WHERE t1.SEQ >= t2.seq AND t2.SUB_TYPE <>'' AND t1.TYPE = t2.TYPE GROUP BY t2.TYPE) t2 INNER JOIN #TEMP t3 ON t3.TYPE = t1.TYPE AND t2.SEQ = t3.SEQ
And it seems to work. Is this the easiest way to do it or am i missing something?
I have a simple 2 column dataset representing Date of session and Client Name (Please download workbook from here - [URL] .... The Financial Year runs from April 1 to March 31. I want to determine the number of sessions conducted in chosen financial year - 2. So if I select FY 2015-16 in a slicer, then, I want view the number of session conducted client wise in FY 2013-14. This is the formula calculated field formula I have written but when I drag this in the Pivot Table, I get an error message. In the calculated field window, when I click on Check for errors, then there are no errors at all.
I have two queries that give me the total sales amount for the current year, and the last year.
SELECT SUM([Sales (LCY)]) FROM [$Cust_ Ledger Entry] cle LEFT OUTER JOIN dw.dim.FiscalDate fd ON fd.CalendarDate = cle.[Posting Date] WHERE [Customer No_] = '10135' AND fd.CalendarYear = '2013'
[Code] ....
I would like to learn how to be able to make this a single query and end up with two columns and their summed up totals. Like it shows on the attached image.
This is my query without the columns I need:
SELECT c.CustomerNumber ,c.Name ,c.ChainName ,c.PaymentTermsCode ,cle.CreditLimit AS 'CreditLimit' ,SUM(cle.Amount) AS 'Amount'
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?
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
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.
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
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.....
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.
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
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
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)
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?
I'm having an issue trying to load some old CVS files created in the year 2005. I'm using SQL server 2008 express edition for now. I've never seen files in this format. For reason, i can't go into, i can't user SSIS and need to load them using the bulkinsert comand. the files contain about 20 fields with a mix of numerics and text. the text fields are delimited by 2 sets of quotes, and a , and the numeric fields by just a , with each line contained in double quotes.
ex """abc"",12,198504,""tom"",""Smith"",49,3322,""mai n st."""
how to define the format file so that i can remove the "" quotes before and after each text field and how to remove the set of quotes that each record is enclosed with.
Hi every one, I have a database table and currently users may retrieve records for a specified date range by providing the start and end dates and then records between those dates provided are retrieved. For example if users wanted to view all records entered in april, they would have to select 04/01/2007 as the start date and then 04/30/2007 as the end date. The records for april would then be displayed in a gridview. How can configure my sql query such that instead the user selectes a month from a dropdownlist of 12 months. I would love a user to just select the desired month from a list instead of selecting start and end dates. Eg if they are intrested in a report for june, then they should just select june from the list instead of specifying the start and stop dates. HOW can i achieve this.
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.
