SQL Server 2008 :: Date Manipulation And CASE Statements
Mar 1, 2015
I have a question on date manipulation functions and CASE statements
My sql is passed the following parameter's and performs a select using a manipulation on these date param's to get a start and end date range depending on the conditions;-
monthColHeader = eg 'Feb 2015'
defaultStartDate and defaultEndDate
filterStartDate and filterEndDate.
These are my conditions;-
if defaultStart and End = filterStart and End use monthColHeader for the date range
if defaultStart and End != filetrStart and End AND the month/year of filterStart and filterEnd match then use the filterStart & End month/Year with the monthColHeader to get the date range
if defaultStart and End != filetrStart and End AND the month/year of filterStart and filterEnd DON't match use filterStart Day and monthColHeader for our start date and monthColHeader for our end date.
When I say use monthColHeader I mean like this;-
(r.dbAddDate >= (CAST('@Request.monthColHeader ~' AS DATETIME)) AND r.dbAddDate < DATEADD(mm,1,'@Request.monthColHeader ~'))
This sql works for converting say 'Feb 2015' to '2015-02-01' & '2015-02-28'....
View 1 Replies
ADVERTISEMENT
Aug 31, 2015
How can I change my T-SQL text editor from text sensitive to text insensitive?
View 2 Replies
View Related
Oct 11, 2007
I'm quite new to SQL Server and I have a pretty naive question.
I have a table called Company that has a field called Renewal date. I have a task that needs to be run on the first of every month to gather all companies that have Renewal Dates coming up in the next 180 days. The Renewal Date is a datetime field in Sql Server. Is there a way I can have this accomplished.
Any help is greatly appreciated.
Thanks,
View 3 Replies
View Related
May 27, 2014
I have a query with huge number of case statements. Basically I need to short this query with getting rid of these hundreds of CASE statements.
Because of the nature of the application I am not allowed to use a function, and just wondering if there is a possible way to rewrite this with COALESCE().
SELECT
CASE WHEN A.[COL_1] LIKE '%cricket%' THEN 'ck' + ',' ELSE '' END +
CASE WHEN A.[COL_1] LIKE '%soccer%' THEN 'sc' + ',' ELSE '' END +
....
CASE WHEN A.[RESIUTIL_DESC] LIKE '%base%ball' THEN 'BB' + ',' ELSE '' END
FROM TableName A
View 9 Replies
View Related
Aug 25, 2015
SELECT
part.num, woitem.qtytarget AS woitemqty,
(SELECT LIST(wo.num, ',')
FROM wo INNER JOIN moitem ON wo.moitemid = moitem.id
WHERE moitem.moid = mo.id) AS wonums, mo."USERID" AS mo_USERID
[Code] ...
View 5 Replies
View Related
Apr 1, 2015
I have a specific requirement. I need to insert the DML statements executed from Management Studio into a SQL table. We have SQL Server 2008 R2 and 2012 instances.
View 8 Replies
View Related
Apr 22, 2015
I am monitoring our production server, and noticed that periodically we have spikes of Memory Paging Rate (pages/sec).
How to find particular queries/stored procedures that causing this?
View 5 Replies
View Related
Jun 11, 2015
I have few complex queries and I want to extract the output of results to all different dateformatted output files.
How to write the queries?
I know BCP is a solution but any other effective way to implement it?
View 2 Replies
View Related
Jul 20, 2007
I have two tables TableA and TableB as shown in the example below. I will have to Update TableA based on the data in TableB.
If a member in TableB has an EmployerId different from the one in TableA where the EffectiveDate in TableB between the EffectiveDate and ExpirationDate in TableA, then it should void the row in TableA and create rows as shown in the example below (Please refer to the 1st record and the last three records).
I would like to get some suggestions on how to do this efficiently. I am not looking for queries, but I need some ideas...
Thanks in advance.
TableA
MemberId EmployerId EffectiveDate ExpirationDate VoidIn
12345 111 1/1/2006 10/31/2006 0
12345 222 11/1/2006 5/31/2007 0
12345 333 6/1/2007 12/31/9999 0
TableB
MemberId EmployerId EffectiveDate
12345 444 4/1/2006
TargetTable (TableA Updated)
MemberId EmployerId EffectiveDate ExpirationDate VoidIn
12345 111 1/1/2006 10/31/2006 1
12345 222 11/1/2006 5/31/2007 0
12345 333 6/1/2007 12/31/9999 0
12345 111 1/1/2006 3/31/2006 0
12345 444 4/1/2006 4/30/2006 0
12345 111 5/1/2006 10/31/2006 0
Thanks
Suresh
View 4 Replies
View Related
Jul 23, 2007
I have a table with one column as a date field in the form of mm/dd/yyyy and I would like to create a new column on a report as 'Days Open' using the column with random dates in the past and subtracting it from the current system date. Can anyone provide any assistance. I'm very new to SQL Server. I know in Oracle u can use 'sysdate'
View 2 Replies
View Related
Jul 17, 2006
I need to to get the result of the function GETDATE and converted to a simpler "mm/dd/yyyy" format in order to compare the results to another date in a table. In ACCESS the function DATE returns the format of 'mm/dd/yyyy' since I need to work with date ranges without a need for this application 'HH:MM:SS'
I have try 'TRANSFORM(GETDATE,'mm/dd/yyyy') but I keep getting errors.
I am not sure what I am doing wrong? Any help is appreciated since I need to work in SQL Server 2000.
Gratefull
Neil
View 3 Replies
View Related
Feb 20, 2008
I have a column(dts) in the database that will retun the date in this format:
2008-02-19 15:10:59:840
I would like it to ve convert it the this format:
Feb -19-08
or
Feb -19-2008
Thanks
View 1 Replies
View Related
Sep 2, 1999
Given any date, how can I change that date to be one of the next 3 year cycle dates.
These are the fixed cycle dates (always on Sep 1 on those set years)
9/1/1997
9/1/2000
9/1/2003
etc.
For example,
if the supplied date was January 4, 1998, it would convert to September 1, 2000
October 31, 2000 -> September 1, 2000
Decemb 12, 2005 -> September 1, 2006
January 1, 1995 -> September 1, 1997
Thanx,
Angel
View 1 Replies
View Related
Apr 4, 2008
I have data from an OLE DB connection in a Dataflow. This data has a datetime column. I am trying to use a derived column object to add new date data to the destination column in the destination table. I would like to change the data in the datetime column, add some time to the value and add as a new column in the destination. The expression I am trying to use is " DATEADD(Hh, 6 , datevalue). " SSIS doesnt like this expression as the datevalue data is a column. How would I accomplish this task using SSIS?
View 2 Replies
View Related
Sep 21, 2015
I'm trying to get a result set without the NULLs. Here is the code I'm using. I'd like the results to look like:
17285204 90471 090471
17285204 90715 090715
17285204 99396 099396
17285204 99420 099420
17285204 90471 090471
17285204 NULL G0444
create table #Test
(
AppNum varchar(10),
CPT varchar(10),
src char(1)
)
insert into #Test(AppNum, CPT, src) values('17285204','090471','b')
[Code] ...
View 5 Replies
View Related
Mar 20, 2015
I am trying to find a beginning date from multiple date ranges, for example:
RowNumberidBegin dtEnd Dt
107933192014-09-022015-06-30
207933192013-09-032014-09-01
307933192012-09-042013-09-02
407933192011-09-062012-09-03
For this id: 0793319, my beginning date is 2011-09-06
108203492014-09-022015-06-30
208203492013-09-032014-09-01
308203492012-09-042013-09-02
408203492011-12-122012-07-03--not a continuous date range
For this id: 0793319, my beginning date is 2012-09-04
108203492014-09-022015-06-30
For this id: 0820349, my beginning date is 2014-09-02
To find continuous date, you look at the beginning date in row 1 and end date in row 2, then if no break in dates, row 2 beginning date to row 3 end date, if no break continue until last date There could multiple dates up to 12 which I have to check for "no break" in dates, if break, display beginning date of last continuous date.
View 9 Replies
View Related
Jul 23, 2005
Greetings All, I was hoping that someone might be able to help me withthe following issue:table ddl:create table exchange(exchangefrom varchar(6),exchangeto varchar(6),exchangecode varchar(6),datemfrom datetime,dateto datetime,exchangerate decimal(28,10))The data in this table under normal conditons will look like:select *from exchangeUSD EURO GL 01/01/2004 01/31/2004 .7523453111USD GBP GL 01/01/2004 01/31/2004 .5384966123USD EURO GL 02/01/2004 02/29/2004 .7523492111USD GBP GL 02/01/2004 02/29/2004 .6004972023My task is to calculate the days delimeted by the start and end date ofthe period which is simple enough:select exchangefrom, exchangeto, exchangecode, datemfrom, dateto,datediff(d, datemfrom, dateto)from exchangeHowever due to circumstances beyond my control the dateto field maycontain a null instead of a valid end date!! YIKES:select *from exchangeUSD EURO GL 01/01/2004 01/31/2004 .7523453111USD GBP GL 01/01/2004 NULL .5384966123USD EURO GL 02/01/2004 02/29/2004 .7523492111USD GBP GL 02/01/2004 02/29/2004 .6004972023My solution to correct the data is to populate the missing end datewith the (start date -1 day) of the next period. However, I am notsure how to do this with SQL? E.g) from the example directly above therow: USD GBP GL 01/01/2004 NULL needs to be updated to:USD GBP GL 01/01/2004 01/31/2004 and this can be done bylooking for the next period (USD GBP GL 02/01/200402/29/2004) that follows and subtracting from its start date 1 day (02/01/2004 - 1 day = 01/31/2004) and that will give me the appropriateend date.If anyone has any insight into solving this problem I would be verythankful.Regards, TFD.
View 7 Replies
View Related
Jul 20, 2015
I have data that I want at multiple granularities, 5,15,30 and 60 minutes. To reduce repetition, I have put them all in the same table, so that there is a column for 5,15,30 and 60 minutes, with a filtered index on each of the columns that removes the nulls. This means that each day will have 288 slots, but only 24 of the slots are filled in for 60 min data, and all of them are filled for 5 minute data.
I have another column that specifies the interval granularity, and my first thought was to access my data through a join, where I can use a CASE statement, and depending on the data granularity necessary, it will look at a different column:
INNER JOIN Data d ON
AND d.settlement_key =
CASE st.interval_granularity
WHEN 5 THEN [5_min_settlement_key]
WHEN 15 THEN [15_min_settlement_key]
WHEN 60 THEN [60_min_settlement_key]
ELSE NULL END
Despite the presence of the indexes on the columns, then the process seems to be quite slow, I think probably due to the fact that any query plan isn't going to know beforehand which of the columns it is going to use for any given dataset, until it actually starts to run, so it may not be optimised.
How I could optimise this based on the given structure? Maybe there are hints to be added to the join, or maybe I can clear the query plan each time the SQL is run? My other option for dealing with the data of different granularity was to use one column and repeat the data multiple times, each at the different granularity, but this makes my data, row and table sizes much higher, as we are adding just a column for each additional granularity. Would this work any better in future versions of SQL server, maybe with column store indexes?
View 5 Replies
View Related
Oct 29, 2015
The below would work if all of the values in A.Value were numbers but they are not. So I need to restrict the view to only look at the following measures but still show all the other row.
WHERE [Measure] IN ('RTT-01','RTT-04','RTT-07')
SELECT
M.[Description]
,A.*
,M.Threshold
,M.[Threshold Direction]
[Code] ....
Is there any way that I can create a select statement in the case when to only look for them measures that I know contain numbers?
View 9 Replies
View Related
Mar 13, 2001
Hello,
I'm trying to use SQL for data warehousing using dates for manipulating data. As SQL doesnt have date, just timedate, and if I want to compare 2 dates to obtain someones age ( ie date - date of birth = age ) I have to use a TIMEDIFF or equivelent. This is REALLY annoying. Is there anyway to use just dates without having to code extra?
Does SQL ignore the time part of a datetime column - ie can I just do a date manipulation without concerning myself with the time ( assuming the time is NOT default at 00:00:00 for every entry ) or am I stuck with treating the field as a complete datetime? It may seem a dumbass question, but as I have to do a lot of date manipulations, it is essential to know.
Many thanks,
Warwick.
View 1 Replies
View Related
Oct 19, 2005
Hey folks, having a problem here...
I have 2 piece of code designed to do the same thing. My problem is, i'm not getting the same results.
Code 1 where the results are correct
Code:
select Count(*) as TotalCount, Sum(DistAmt) as TotalSum
from table1
inner join table2
on table2.id = table1.id
where MailTypeID = 3
AND MailEventID = 2
and table1.IsActive = 1
code 2 - Y is correct, but Z is not, and not only is it not correct, but it is returning a number which equals more then the total rows from the table.
Code:
select
Y = sum(case when mailtypeid = 3 and maileventid = 2 and IsActive = 1 then distamt else 0 end),
Z = count(case when mailtypeid = 3 and maileventid = 2 and IsActive = 1 then 1 else 0 end)
from table1
inner join table2
on table2.id = table1.id
I have no idea what is going on.
Thanks!
Caden
View 2 Replies
View Related
Dec 9, 2006
Hi,
Is there a way to use more criteria in a CASE statement other than CASE WHEN expression THEN value ELSE value END
I need to test if the count is greater than 0. If so, then perform the case statement, else return zeros. Currently there are entries where the values are blank. These blank values are causing errors in the application and unfortunately, I am not able to update these values.
So far I have the following, but I am getting an error stating "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference."
Thanks in advance!
Code:
IF @Qid = 4
SELECT @Exp as Status, COUNT(*) AS Total, @CourseID as CourseID,
(SELECT
question
FROM
tableQuestions
WHERE
qid = @Qid) AS Question,
IF COUNT(*)>0 THEN
1.0 * SUM(CASE WHEN A.Q1 > 1 THEN 1 ELSE 0 END) / COUNT(*) AS Positive,
1.0 * SUM(CASE WHEN A.Q1 = 0 THEN 1 ELSE 0 END) / COUNT(*) AS Neutral,
1.0 * SUM(CASE WHEN A.Q1 < 0 THEN 1 ELSE 0 END) / COUNT(*) AS Negative,
1.0 * SUM(CASE WHEN A.Q1 = 0 THEN 1 ELSE 0 END) / COUNT(*) AS NA
ELSE
0 AS Positive,
0 AS Neutral,
0 AS Negative,
0 AS NA
END IF
FROM
table1 A INNER JOIN table2 B ON A.SessionID = B.SessionID
WHERE
(B.CourseID = @CourseID) AND (A.SubmitDate >= @BeginDate) AND (A.SubmitDate <=@EndDate)
View 2 Replies
View Related
Mar 9, 2015
This works EXCEPT when I add the ELSE line.
How do I accomplish the ELSE?
Does the ELSE have to state "WHEN [BG_STATUS] <> 'Blocked' and [BG_STATUS] <> 'Closed', etc? Do I have to delineate for the ELSE statement everything that BG_STATUS is *not* equal to? Seems there ought to be a way but I can't find it.
SELECT BG_SEVERITY AS 'Severity',
SUM(CASE WHEN [BG_STATUS] = 'Blocked' THEN 1 ELSE 0 END) as 'Blocked',
SUM(CASE WHEN [BG_STATUS] = 'Closed' THEN 1 ELSE 0 END) as 'Closed',
SUM(CASE WHEN [BG_STATUS] = 'Customer Test' THEN 1 ELSE 0 END) as 'Customer Test',
SUM(CASE WHEN [BG_STATUS] = 'Deferred' THEN 1 ELSE 0 END) as 'Deferred',
[Code] ....
View 4 Replies
View Related
Jun 20, 2007
I am creating a stored procedure which receives the following 4 variables. However, VacancySectorID is the only madatory variable.
@VacancySectorID int = NULL, (mandatory)
@VacancyRegionID int = NULL, optional)
@VacancyTypeID int = NULL, (optional)
@VacancyKeywords nvarchar(64) = NULL, (optional)
My objective is to dynamically build the WHERE statement which in turn will return either one or more of the variables and the required data. The following code works for a single if else but it appears that you cannot have more than one if else statement.
Anyones input would be greatly appreciated.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Public_GetVacancyListingBySearchCriteria]
(
@VacancySectorID int = NULL,
@VacancyRegionID int = NULL,
@VacancyTypeID int = NULL,
@VacancyKeywords nvarchar(64) = NULL
)
AS
--SET NOCOUNT ON;
IF @VacancySectorID IS NULL AND @VacancyRegionID Is NULL AND @VacancyTypeID IS NULL AND @VacancyKeywords IS NULL
BEGIN
SELECTviewVacancies.*
FROMviewVacancies
WHEREVacancyActive = 1 AND VacancySectorID = @VacancySectorID
ORDER BYVacancyPosted DESC, VacancyTitle
END
ELSE
BEGIN
SELECTviewVacancies.*
FROMviewVacancies
WHEREVacancyActive = 1
ORDER BYVacancyPosted DESC, VacancyTitle
END
View 4 Replies
View Related
Jun 21, 2007
whats the most efficient way to do this? I have a column that contains three values. A, B, C If colVal = A add one to batch a, if B add one to batch b...ect . Then display all three values
View 3 Replies
View Related
Jul 23, 2007
hi,
I want to know how can i equate the following case statements into
one Condition? Following is my Code
(case when ws.Action_Taken_ID not in(3,17,18) then '-1' else '0' end) as istechfcr,
(case when datediff(day,dbo.Usp_Get_Date(pr.Set_Serial_Number),oh.WO_Record_Date) <= q.Product_Gurantee_Months + 6 then '-1' else '0' end) as istechfcr,
(case when substring(ltrim(rtrim(ws.Symptom)),1,1) not in('X') then '-1' else '0' end) as istechfcr,
(case when ws.Action_Taken_ID not in(8,15,21,25) then '-1' else '0' end) as istechfcr,
Thanks..
View 6 Replies
View Related
Aug 23, 2007
Hi is it possible to have a case statement with 2 or more constraints ?
Like we have
CASE x
WHEN 1 THEN ..
WHEN 2 THEN ..
but what if i want a constraint like :
IF (x= 0 AND y=0)
{
then...
}
can this be done in a CASE statement ?
if not , can I use IF with SQL ?
View 3 Replies
View Related
Jul 20, 2005
Hi all,I have to translate an Access query into sql. The query has thefollowing statement. I know SQL doesn't support iif, so can someone tellme how to use the case statement to get the same result?select field1,IIf(Grand_total-50>0, grand_total-50, 0) AS field2,field3Thanks.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
Jul 20, 2005
I'm trying to make a view that uses organization name from one tableand contact first and last name from another table. In the view Ihave a field that I want to show Organization followed by the maincontact. Problem is if the organization field or name field is NULLthen it doesn't show anything. If one field is empty I still want itto show the other field in the table.Example:Org1--ContactOrg2--ContactOrg3 (Still shows org even without a contact name)Contact (Still shows contact even without an org name)Tried using a CASE statement but didn't work.
View 1 Replies
View Related
Mar 1, 2007
Does anyone know if there is a way to look at a value in a variable and based on that value run different Data Flow Tasks?
For example, let's say I have an SSIS package that contains a variable named Client and 3 separate Data Flow Tasks. I would like to do this: if Client = 1 then run Data Flow 1 else if Client = 2 then run Data Flow 2 else run Data Flow 3.
Is this possible?
Thanks.
Danielle
View 5 Replies
View Related
May 18, 2001
Can anyone show me how to write or post a sample of a nested case statement?
Thanks,
LOC
View 2 Replies
View Related
May 24, 2000
Hi All,
I'am trying to program a nested CASE statements (if this is not possible, does anyone have any alternate suggestions ?) and I'm getting syntax errors.
The statement is:
SELECT @cmdLine =
CASE @BackupType
WHEN 1 THEN Select @tmpStr =
CASE @initFlag
WHEN 1 THEN 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH INIT, NOUNLOAD, NAME = ' + @backupJobName + ' , SKIP , STATS = 10, NOFORMAT'
ELSE 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH NOINIT, NOUNLOAD, NAME = ' + @backupJobName + ' , SKIP , STATS = 10, NOFORMAT'
END
WHEN 2 THEN Select @tmpStr =
CASE @initFlag
WHEN 1 THEN 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH DIFFERENTIAL, INIT , NOUNLOAD, NAME = ' + @backupJobName + ', SKIP, STATS = 10, NOFORMAT'
ELSE 'BACKUP Database ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH DIFFERENTIAL, NOINIT , NOUNLOAD, NAME = ' + @backupJobName + ', SKIP , STATS = 10, NOFORMAT'
END
WHEN 3 THEN Select @tmpStr =
CASE @initFlag
WHEN 1 THEN 'BACKUP Log ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH INIT, NOUNLOAD, NAME = ' + @backupJobName + ', SKIP , STATS = 10, NOFORMAT'
ELSE 'BACKUP LOG ' + @databaseName + 'TO '+ @backupDeviceName + ' WITH NOINIT, NOUNLOAD, NAME = ' + @backupJobName + ', SKIP , STATS = 10, NOFORMAT'
END
ELSE ''
END
TIA,
Romy Stevensen
View 3 Replies
View Related
Dec 13, 2001
I am wondering if there is a way that we could do a nesting case statement in an SQL Query?
This is what I have now... FicaWages = CASE WHEN (UPR00900.FICAMWGS_1 + UPR00900.FICAMWGS_2) >= '84900' AND UPR00400.SBJTSSEC = 1 THEN '0' WHEN UPR00400.SBJTSSEC = 0 THEN '0' ELSE (UPR30100.GRWGPRN - (SELECT SUM(UPR30300.UPRTRXAM) FROM UPR30300 WHERE UPR30300.EMPLOYID = UPR00100.EMPLOYID AND UPR30300.PAYROLCD LIKE '3%' AND AUCTRLCD = 'UPRCC00000007')) END
What I want:
FicaWages = CASE
WHEN
('Sql Statement') = 'GRM'
THEN
CASE
WHEN (UPR00900.FICAMWGS_1+UPR00900.FICAMWGS_2)
>= '84900' AND UPR00400.SBJTSSEC = 1 THEN '0'
WHEN UPR00400.SBJTSSEC = 0 THEN '0'
ELSE (UPR30100.GRWGPRN - ('Sql Statement'))
END
ELSE
CASE
WHEN (UPR00900.FICAMWGS_1+UPR00900.FICAMWGS_2)
>= '84900' AND UPR00400.SBJTSSEC = 1 THEN '0'
WHEN UPR00400.SBJTSSEC = 0 THEN '0'
ELSE (UPR30100.GRWGPRN - ('Sql Statement'))
END
END
I hope this is clear enough.
View 1 Replies
View Related
