T-SQL (SS2K8) :: Create Function For Dynamically Update Every Year?
May 27, 2015
UPDATE Report
SET MSYear= casewhen MSDate > '2011/06/30' and MSDate < '2012/07/01' THEN '2012'
when MSDate > '2012/06/30' and MSDate < '2013/07/01' THEN '2013'
when MSDate > '2013/06/30' and MSDate < '2014/07/01' THEN '2014'
when MSDate > '2014/06/30' and MSDate < '2015/07/01' THEN '2015'
when MSDate > '2015/06/30' and MSDate < '2016/07/01' THEN '2016'
when MSDate > '2016/06/30' and MSDate < '2017/07/01' THEN '2017'
End
Actually our business year starts from 1st july and for this code I need function which is dynamically updates the every year for example 2014-07-01 to 2015-06-30 this is called as a 2015 year like this I need function which will dynamically update a year.
View 4 Replies
ADVERTISEMENT
Sep 5, 2014
I am having SP which gives, two result sets. The columns which are coming from result sets are also dynamic.
i.e. some time 5 columns and some time 10 columns.
Now I want to load this output into 2 different tables on daily basis. This would be truncate/delete table and load again.
Now my problem is that as I am not sure about columns, Is it possible to create table(Physical Table) depends on output of SP, and after load data into it.
During each load we can drop table, No issue and we can handle this through SSIS Package.
View 2 Replies
View Related
May 27, 2015
I want to change Set clause of Update Statement dynamically based on some condition.
Basically i have 2 Update statments having same FROM clause and same JOIN clause.
Only diff is SET clause and 1 Where condition.
So i am trying to combine 2 Update statements into 1 and trying to avoid visit to same table twice.
Update t
Set CASE **WHEN Isnull(td.IsPosted, 0) = 0
THEN t.AODYD = td.ODYD**
*ELSE t.DAODYD = td.ODYD*
END
From #ReportData As t
Join @CIR AS tmp On t.RowId = tmp.Max_RowId
[Code] ....
But CASE statement is not working...
View 7 Replies
View Related
Oct 20, 2014
I would like to create a table valued function using the following data:
create table #WeightedAVG
(
Segment varchar(20),
orders decimal,
calls int
);
insert into #WeightedAVG
[code].....
I would like to create a function from this where I can input columns, and two numbers to get an average to output in a table ie,
CREATE FUNCTION WeightedAVG(@divisor int, @dividend int, @table varchar, @columns varchar)
returns @Result table
(
col1 varchar(25),
WeightedAVG float
[Code] .....
View 4 Replies
View Related
Jun 28, 2015
I have a requirement to display the count of project data by their status (On Hold, In Progress, Pre-concept and Closed) for the current FY and Last FY based on column 'Financial Year' and 'Project Status'.
Financial Year column has a value in the following format (FY2011-12, FY2012-13, FY2013-14,FY2014-15, FY2015-16,FY2019-20 ,FY2020-21,FY2021-22)
I need to set the financial year value dynamically in above format for the last and current year (FY2013-14, FY2014-15) based on today's date and Financial Year is from July to Jun. For example,
When today's date is 28-06-2015 then it should set the current FY as FY2014-15 and Last Financial Year as FY2013-14When today's date is 28-06-2021 then it should set current FY as FY2020-21 and Last Financial Year as FY2019-20
View 3 Replies
View Related
Jun 5, 2014
I'm using sp_OACreate in a scalar function to create a folder if it doesn't exist, and it works fine if you're asking it to create a single folder. For instance: C:Newfolder
It creates "Newfolder"
However, if I try to ask it to create C:NewfolderNewsubfolder
It doesn't work
Here's the code:
DECLARE @Exists int, @ObjFile int, @ObjFileSystem int, @Folder nvarchar(500) = 'C:',
@Action tinyint = 1 --(0 to check if folder exists, 1 to actually create it)
EXEC dbo.sp_OACreate 'Scripting.FileSystemObject', @ObjFileSystem OUT
EXEC dbo.sp_OAMethod @ObjFileSystem, 'FolderExists', @Exists OUT, @Folder
[Code] .....
View 1 Replies
View Related
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
View Related
Feb 22, 2015
I m creating P&L(profit and Loss ) Reports of accounts its consists of 2 levels
in level2:
my procedure display the output of (Actuals in lakhs)
RESPONSIBILITY DEPT CATEGORY CURRENT YTD ACTUALS
SALES Sales Net Sales 444.65
Sales Sales LESS TRD 22.55
SALES NET RETURNS NET RETURNS 422.10 (net sales - TRD)
Finance LESS ED LESS ED 40
Sales Totals Sales 382.10(RETURNS - ED)
(only calculation for above dept only remaining dept values display sum of relvenat accounts ,and if i click the category relvent account codes shown here)
Materials .... ... ..
production ..... ............ ........
i made a procedure for above
create procedure Pl_level2
@fmdate datetime,
@todate datetime,
@category varchar(200)
as
begin
create table #temp1
[code]....
like i m inserted so many accounts in temp tables .
IF (@category IS NULL or @catagory=' ' )
begin
select
responsibility,
dept,
category,
round(Max(Actuals)/100000,1,2) as Actuals from #temp
[code]....
here i can display only current YTD only how to display previous year (13-14) YTD in level1
How to do?
View 1 Replies
View Related
Jun 26, 2014
A user is wanting to run a query against the current 3 active databases. These change every month on the 1st e.g. Test-06-14. Therefore I want to set up a dynamic query which will always use the current database. I believe I am almost there but I cannot set the USE @DatabaseName dynamically yet.
DECLARE @DB_Name varchar(100)
DECLARE @DatabaseName varchar(100)
DECLARE @Command nvarchar(200)
DECLARE @Command2 nvarchar(200)
DECLARE database_cursor CURSOR FOR
[Code] ....
View 9 Replies
View Related
Oct 15, 2014
This statement adds two additional months to which is fine :
DATENAME(MM,dd.date)+ ' ' + DATENAME(D,dd.date) + ', ' + DATENAME(YY,dd.date)
but if my month is November and two months is added, the year does not change, it stays the same. how do I make the year change when two months are added toward the end of the year.
View 7 Replies
View Related
Mar 25, 2014
I'm trying to write some T-SQL to return the previous even numbered month and appropriate year from given date.
Examples given:
03-25-2014 should return 02-xx-2014
01-01-2014 should return 12-xx-2013
View 2 Replies
View Related
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
Jun 4, 2014
I am trying to pivot data based on columns value in year column... but results are not showing up correctly. I want to see all columns after pivot.I want to Pivot based on year shown in the data but it can be dynamic as year can go for last 3 years
I am also using an inner join as i have two amount columns in my code and i want to show both amount columns for all displayed year.I am able to pivot but I need in output all the columns like this Id,MainDate, Year1,Year2,Year3(if any), AMT1 for YR1, AMT2 for Yr1, , AMT1 for YR2, AMT2 for Yr2, AMT1 for YR3, AMT2 for Yr3,
Here is some data:
-- CREATE TABLE [dbo].[TEMP](
--[FileType] [varchar](19) NOT NULL,
--[dType] [char](2) NOT NULL,
--[dVersion] [char](2) NOT NULL,
--[Id] [char](25) NOT NULL,
--[MainDate] [char](40) NULL,
[code]....
View 5 Replies
View Related
May 11, 2015
I have table in which month & year are stored, Like this
Month Year
10 2014
11 2014
12 2014
1 2015
2 2015
3 2015
4 2015
I wanted a query in which it should return the value in a status field which has latest year & month.
View 9 Replies
View Related
Aug 28, 2014
I have an SSIS package that puts together a series of queries to check all text fields in all tables of a database for a set of "invalid" characters. I know the root query works as we have been running it manually for quite a while. However, now that I have changed it to build dynamically, I cannot figure out why it does not work.
Here is the code. The EXEC (@sSQL) returns 3 records. It just so happens that these 3 records are the only ones in the database with a '?' in the field. The very bottom statement returns 0 records. I cannot figure out what the difference is. When I do PRINT (@sSQL), it looks fine to me, except that @Pattern is fully printed.
DECLARE @Pattern NVARCHAR(MAX)
DECLARE @sSQL NVARCHAR(MAX)
DECLARE @1 nvarchar(max)
DECLARE @2 nvarchar(max)
DECLARE @loop int
[code].....
View 9 Replies
View Related
Sep 2, 2014
I have table of standardized data that I'm merging into from an Import table.
This import table may have the [ContactName]. I one row in the import table has the contact name then every row would have the contact name.
What I'd like to be able to do is something like this in the UPDATE portion of the Merge (I don't care about the insert phase, since I can insert a null [ContactName] since I'm not overwritting an existing [ContactName]
MERGE INTO [dbo].[Standardized] AS [target]
USING [dbo].[ImportDestination]
AS [Source]
ON [Source].[Key] = [Target].[Key]
WHEN MATCHED
THEN UPDATE
SET[ContactName] = CASE WHEN source.[ContactName] IS NOT NULL THEN source.[ContactName] else target.[ContactName];
Is anything like that possible and if the code above works it would only work on a row by row bases. Is it possible to figure out if any [ContactName] has data and if so possibly overwrite an old [ContactName] with a null?
View 1 Replies
View Related
Nov 9, 2014
I have created dynamic sql to declare variables based on columns from the table and i set values to those variable now here is the issue . i want to check the variable values how do i do that dynamically
drop table test
create table test
(
id varchar(10) not null,
col1 varchar(10) ,
col2 varchar(10)
[Code] .....
Now my next step is verify if the variable is blank or not how do i do that ?
How do i verify all of the columns one after the other .
I am after the statement like this dynamically
-- IF NOT (@col1 = '') THEN set @SQL = @SQL + '[col1] = ' + @col1 + ' '
--IF NOT (@col2 = '') THEN set @SQL = @SQL + '[col2] = ' + @col2 + ' '
I need to check if the columns are blank or not dynamically as i do not want to hard code the column names there.
View 4 Replies
View Related
May 21, 2015
I have two tables:
tblServer
tblInstance
tblServer represents a server, tblInstance represents any SQL Server instances present on the server. They're related thru srvID.
I'd like to write a query that gives me servers with all instances, on one row. This dataset will populate a List in an SSRS report.
I have this but it's clunky and does not provide for more than 2 instances on the server. I'd like this to account for any number of instances all on one row without having to hard code multiple joins:
with serverInfo as
(
select
srv.srvType as Type
,srv.srvName as ServerName
,srv.srvIP as ServerIP
,ins.instNetName as InstanceNetName
[Code] .....
View 3 Replies
View Related
Jun 23, 2008
I'm looking for a function that will return an INTEGER that computes the number of days between two dates based on a 360-calendar year instead of using just the datediff function which is actual days.
If anybody knows where I could find something like this, I would be greatly appreciated.
Does this site have a library of user-defined sql functions? Or, does somebody have a site that you know of where there are a bunch?
Thanks,
South Side Rob
View 14 Replies
View Related
Jun 19, 2014
select empid,orderdate from [Sales].[Orders] WHERE custid = 71
group by empid,orderdate
sample data resultset:
empid orderdate
1 2006-12-25
1 2007-12-24
1 2007-12-23
1 2008-12-23
1 2008-04-24
select empid,year(orderdate) from [Sales].[Orders] WHERE custid = 71
group by empid,year(orderdate)
sample data resultset:
empid orderdate
1 2006
1 2007
1 2008
why is the year() gets only one year when worked with groupby. when empid =1 the year 2007 has 2 records and 2008 has 2 records in first select and when I used year(orderdate) in secong select only one year for each is selected.
View 3 Replies
View Related
Jan 18, 2006
This function returns the ISO 8601 week of the year for the date passed. The first week of each year starts on the first Monday on or before January 4 of that year, so that the year begins from December 28 of the prior year through January 4 of the current year.
This code creates the function and demos it for the first day of each ISO week/year from 1990 to 2030.
drop function dbo.F_ISO_WEEK_OF_YEAR
go
create function dbo.F_ISO_WEEK_OF_YEAR
(
@Datedatetime
)
returnsint
as
/*
Function F_ISO_WEEK_OF_YEAR returns the
ISO 8601 week of the year for the date passed.
*/
begin
declare @WeekOfYearint
select
-- Compute week of year as (days since start of year/7)+1
-- Division by 7 gives whole weeks since start of year.
-- Adding 1 starts week number at 1, instead of zero.
@WeekOfYear =
(datediff(dd,
-- Case finds start of year
case
whenNextYrStart <= @date
thenNextYrStart
whenCurrYrStart <= @date
thenCurrYrStart
elsePriorYrStart
end,@date)/7)+1
from
(
select
-- First day of first week of prior year
PriorYrStart =
dateadd(dd,(datediff(dd,-53690,dateadd(yy,-1,aa.Jan4))/7)*7,-53690),
-- First day of first week of current year
CurrYrStart =
dateadd(dd,(datediff(dd,-53690,aa.Jan4)/7)*7,-53690),
-- First day of first week of next year
NextYrStart =
dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aa.Jan4))/7)*7,-53690)
from
(
select
--Find Jan 4 for the year of the input date
Jan4=
dateadd(dd,3,dateadd(yy,datediff(yy,0,@date),0))
) aa
) a
return @WeekOfYear
end
go
-- Execute function on first day of first week of year from 1990 to 2030
select
DT,
ISO_WEEK_OF_YEAR =
dbo.F_ISO_WEEK_OF_YEAR(a.DT)
from
(
select DT = getdate()union all
select DT = convert(datetime,'1990/01/01') union all
select DT = convert(datetime,'1990/12/31') union all
select DT = convert(datetime,'1991/12/30') union all
select DT = convert(datetime,'1993/01/04') union all
select DT = convert(datetime,'1994/01/03') union all
select DT = convert(datetime,'1995/01/02') union all
select DT = convert(datetime,'1996/01/01') union all
select DT = convert(datetime,'1996/12/30') union all
select DT = convert(datetime,'1997/12/29') union all
select DT = convert(datetime,'1999/01/04') union all
select DT = convert(datetime,'2000/01/03') union all
select DT = convert(datetime,'2001/01/01') union all
select DT = convert(datetime,'2001/12/31') union all
select DT = convert(datetime,'2002/12/30') union all
select DT = convert(datetime,'2003/12/29') union all
select DT = convert(datetime,'2005/01/03') union all
select DT = convert(datetime,'2006/01/02') union all
select DT = convert(datetime,'2007/01/01') union all
select DT = convert(datetime,'2007/12/31') union all
select DT = convert(datetime,'2008/12/29') union all
select DT = convert(datetime,'2010/01/04') union all
select DT = convert(datetime,'2011/01/03') union all
select DT = convert(datetime,'2012/01/02') union all
select DT = convert(datetime,'2012/12/31') union all
select DT = convert(datetime,'2013/12/30') union all
select DT = convert(datetime,'2014/12/29') union all
select DT = convert(datetime,'2016/01/04') union all
select DT = convert(datetime,'2017/01/02') union all
select DT = convert(datetime,'2018/01/01') union all
select DT = convert(datetime,'2018/12/31') union all
select DT = convert(datetime,'2019/12/30') union all
select DT = convert(datetime,'2021/01/04') union all
select DT = convert(datetime,'2022/01/03') union all
select DT = convert(datetime,'2023/01/02') union all
select DT = convert(datetime,'2024/01/01') union all
select DT = convert(datetime,'2024/12/30') union all
select DT = convert(datetime,'2025/12/29') union all
select DT = convert(datetime,'2027/01/04') union all
select DT = convert(datetime,'2028/01/03') union all
select DT = convert(datetime,'2029/01/01') union all
select DT = convert(datetime,'2029/12/31') union all
select DT = convert(datetime,'2030/12/30')
) a
CODO ERGO SUM
View 12 Replies
View Related
Feb 16, 2006
I have been spoiled by some report writing tools that have intrinsicfunctions like Last Year Month-to-date. I'm looking for a way to emulatethis in SQL Server now with my fields that are date/time.I'm thinking I need to develop a user defined function to accept a dateinput parameter, but I don't know where to start.Help/Examples appreciated.Thanks,Frank*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies
View Related
Aug 29, 2007
Dear , all
I have a problem in fucntion year in sql mobile , Use select Date_in from Test Where Year(Date_in) = 8
Brg
Tingnong
View 1 Replies
View Related
May 19, 2015
DELETE FROM Report_temp2
WHERE MSSalesID in
( Select Report_temp.MSSalesID FROM Report_temp)
DELETE FROM Report_temp
WHEREMSDate < '2015-07-01'
Actually the year stating form july.
Q1 is july,aug,sep.
Q2 is oct nov,dec.
Q3 is jan,feb,mar.
Q4 is april, may,june.
So what I need is dynamically I want to delete the data every year prior to current year.
View 4 Replies
View Related
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
Jan 2, 2008
Hi ,
Year function in derived column gives error if the incoming date is less than 1/1/1753. Is this Issue or required behaviour
Thanks
Dharmbir
View 11 Replies
View Related
Jan 12, 2006
Hi guys,
Thanks for the recent help.
Now, how can I update the YEAR?
Sample Data
11-23-1903
11-20-1903
01-04-1903
Thank you.
View 6 Replies
View Related
May 21, 2008
hay all, i'm trying to pass parameters to a stored procedure ..and i keep getting this error "Procedure or function 'selectFromView' expects parameter '@Year', which was not supplied." this is the procedure implementation : ALTER PROCEDURE dbo.selectFromView
@Year as varchar(10),
@Country as varchar(10),
@Family as varchar(10),
@Manu as varchar(10),
@Status as varchar(10),
@Type as varchar(10),
@Operator as varchar(10)
AS
SELECT * FROM ViewofAll
WHERE
ProductionYear = @Year and
CountryName = @Country and
Family = @Family and
Manufacturer = @Manu and
Status = @Status and
Type = @Type and
OperatorName = @Operator
RETURN and below how i call the procedure : Dim connection As SqlConnection
connection = New SqlConnection()
connection.ConnectionString = "Data Source=BLACKIRIS3SQLEXPRESS;Initial Catalog=Aircrafts;Integrated Security=True"
connection.Open()
Dim command As SqlCommand
command = New SqlCommand("selectFromView", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add(New SqlParameter("@Year", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Add(New SqlParameter("@Country", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Add(New SqlParameter("@Family", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Add(New SqlParameter("@Manu", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input 'Stored prcedure parameters
command.Parameters.Add(New SqlParameter("@Status", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Add(New SqlParameter("@Type", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Add(New SqlParameter("@Operator", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Item("@Year").Value = myArray(0)
command.Parameters.Item("@Country").Value = myArray(1)
command.Parameters.Item("@Family").Value = myArray(2)
command.Parameters.Item("@Manu").Value = myArray(3)
command.Parameters.Item("@Status").Value = myArray(4)
command.Parameters.Item("@Type").Value = myArray(5)
command.Parameters.Item("@Operator").Value = myArray(5)
DSGrid.SelectCommand = command.CommandText With DSGrid
.DataBind()
End With i'm not really familiar with stored procedures ...any one can help me plz ? thanx in advance .
View 3 Replies
View Related
Sep 2, 2005
Hello,
I am trying to create a sp dynamically. However each time, I run my sp, which creates the dynamic SQL for the sp, then it automatically changes:
CREATE PROCEDURE .....
to
Alter PROCEDURE ....
Then I am obvoiusly getting the error msg:
Invalid object name.
How can I get rid of that?
View 14 Replies
View Related
Apr 28, 2015
I've written sql code which takes a date and finds the Last Day of the Month one year ago. For example, it takes the date '2015-04-17' and returns the date '2014-04-30'. The code works fine in a query. Now I'm trying to turn this into a function. However, when I try to create the function I get the error:
Operand type clash: date is incompatible with int
Why is this error being returned?
Here is my function:
CREATE FUNCTION dbo.zEOM_LY_D(@Input Date)
RETURNS date
AS
BEGIN;
DECLARE @Result date;
SET @Result = convert(DATE, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dateadd(m, -11, @Input)+1),0)),101)
RETURN @Result;
END;
View 11 Replies
View Related
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
Sep 12, 2007
Hi! I am using VS 2003 (v 1.1)
I need to generate tables with the values from the Backend (SQLServer 2000) database in C#.Net.
How can i create the tables, tablerows, cells, etc. from the codebehind page of C#. I am having a very little knowledge about dynamic generation.
Give me the complete code with can example (if possible)
Thanks & Regrads
Jai Shankar
View 1 Replies
View Related
Apr 14, 2008
Hi,
I have N1 table where columns name(id,Field). Base on the fields of this table I want to create N2 table from SP where data from N1 will be columns in N2.
id Field
-- ------
1 ID
2 First
3 Last
Create table N2(ID,First,Last)
regards,
Mark
View 6 Replies
View Related
