Wednesday, May 29, 2013

[T-SQL] calcualte Days between 2 dates but exclude weekend - Code not working need help please urgent

[T-SQL] calcualte Days between 2 dates but exclude weekend - Code not working need help please urgent

calcualte Days between 2 dates but exclude weekend - Code not working need help please urgent

Posted: 28 May 2013 10:50 PM PDT

Dear friends,I have requirement to Calculate Days between 2 Dates but the code is not working-My current code is---CAST ((datediff(dd,convert(int,Job Posting Create Date_JP]),convert(int,Report Run Date_BYR)+1))- (datediff(WK,convert(int,Job Posting Create Date_JPconvert(int,Report Run Date_BYR))*2)- (CASE when DATENAME(DW,convert(int,Report Run Date_BYR)) = 'Sunday' then 1 else 0 END)- (CASE when DATENAME(DW,convert(int,Job Posting Create Date_JP)) = 'Saturday' then 1 else 0 END) AS VARCHAR(255))-- AS Age (Working Days) ---Note- I have to do varachar since where the avlue comes blank I have to display as 'N/A'the date formats values we have are -,[WorkForce_JobPosting].[Job Posting Create Date_JP]) =WorkForce_JobPosting].[Report Run Date_BYR]) = Report Run Date_BYR2013-05-28 09:04:51.2272013-05-28 09:04:51.2272013-05-28 09:04:51.2272013-05-28 09:04:51.227Job Posting Create Date_JP2013-05-17 14:49:25.000Job Posting Create Date_JP2013-05-19 22:38:49.000Job Posting Create Date_JP2013-05-20 02:44:49.000Job Posting Create Date_JP2013-05-21 03:28:28.000Expected values are-I'm getting output as Age (Working Days) -6,7,7,6 Where as the User says it should be-7,6,6,5 Would be much obliged if any help to fix this ASAP critical issue.thanksDhnanajay

Delete failing in distributed transaction on linked server

Posted: 29 May 2013 12:54 AM PDT

I am looking to "transfer" records from a table on Server A to a table on Server B. And then delete those records from table on Server A. So this way, table on Server B acts as a space to store archive records.My process is a SQL Server Agent job running a stored procedure which accomplishes the above stated scenario.My code is Insert into Server B table and subsequent delete from Server A table:BEGIN DISTRIBUTED TRAN INSERT INTO Archive table on Server B SELECT * FROM Server A table WITH (NOLOCK) WHERE CREATED < @RUNDATE; DELETE FROM Server A table WHERE CREATED < @RUNDATE; COMMIT TRANGOI get this error:OLE DB provider "SQLNCLI10" for linked server "xyz" returned message "No transaction is active.".Msg 0, Level 11, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded.Upon removing:BEGIN DISTRIBUTED TRAN COMMIT TRANGOfrom the code - the job worksBut I want to place these pieces of code to treat this process as one transaction.I have tried several resolutions like:Ping both servers: YesLinked Server Connection properties = TRUE: YESEnable DTC over network: YESCheck registry to make sure MSDTC security turned off: YESIs firewall blocking port 135 or higher level - checked - all openPlease advisethank you,

Linked server queries

Posted: 28 May 2013 04:31 AM PDT

I have a view that is built on OPENQUERY function calls to linked server (Oracle db). When I run queries against that view, I sometimes get the following error:OLE DB provider "OraOLEDB.Oracle" for linked server "VTRACK" returned message "ORA-01403: no data found".Msg 7346, Level 16, State 2, Line 1Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "VTRACK".This doesn't happen all of the time, but comes up frequently enough to be a problem. This error has been thrown by multiple queries, so I'm thinking the problem does not lie with the query itself. Any idea what causes this error?

STDEVP help

Posted: 28 May 2013 07:39 AM PDT

If you have a record, where there are 12 columns, one for each of the last 12 months, and you want to use STDEVP, how would you go about it? Since the data is in different columns? Create a CTE and load the data?David

OPENXML query problem

Posted: 28 May 2013 07:58 AM PDT

Hi,I need help with querying an xml file with openxml.My xml file looks like that (it is longer, but the problem appears here as well)DECLARE @doc xmldeclare @docno intSET @doc ='<camt:Document xmlns:camt="urn:iso:std:iso:20022:tech:xsd:camt.053.001.02"> <camt:BkToCstmrStmt> <camt:Stmt> <camt:Id>1222145695_2013-05-10</camt:Id> <camt:Acct> <camt:Id> <camt:Othr> <camt:Id>1179411821542672</camt:Id> </camt:Othr> </camt:Id> <camt:Ccy>HUF</camt:Ccy> <camt:Nm>XXX HUNGARY ZRT.</camt:Nm> <camt:Ownr> <camt:Nm>XXX HUNGARY ZRT.</camt:Nm> </camt:Ownr> <camt:Svcr> <camt:FinInstnId> <camt:Nm>OTP Bank Nyrt.</camt:Nm> <camt:PstlAdr> <camt:Ctry>HU</camt:Ctry> </camt:PstlAdr> </camt:FinInstnId> </camt:Svcr> </camt:Acct> </camt:Stmt> </camt:BkToCstmrStmt></camt:Document>'EXEC sp_xml_preparedocument @docno OUTPUT, @docselect * from openxml(@docno,'/Document/BkToCstmrStmt/Stmt',2) with (ID varchar(100)) I expected this query returns: 1222145695_2013-05-10, but no result is returned.select * from openxml(@docno,'/Document/BkToCstmrStmt/Stmt/Acct/ID/Othr',2) with (ID varchar(100)) And this one should return 1179411821542672, but again nothing.I tried several things without success. Could anyone help me?ThanksMárton

Need some query help.

Posted: 22 May 2013 06:57 AM PDT

I need to figure out how to do a group by on this query by number of days between start and end date. Can someone point me in the right directions. Thanks!select plantfilenumber,(select min(cc.lasteventdate) from tbltitlevault_history c inner join tbltitlevault cc on c.tvid = cc.tvid where c.tvid = a.tvid and c.eventid = '1') as startDate,(select max(dd.lasteventdate) from tbltitlevault_history d inner join tbltitlevault dd on d.tvid = dd.tvid where d.tvid = a.tvid and d.eventid = '6') as endDatefrom tbltitlevault a inner join tbltitlevault_history b on a.tvid = b.tvid

add a new column to a table with conditions

Posted: 28 May 2013 02:15 AM PDT

hi friendsi want to add a column to a table with conditions below :1- it has a default value=02- this column can get null value3- no constraint should be createdis it possible ?thanks

Dropping select tables across a database.

Posted: 28 May 2013 01:29 AM PDT

HiI have a situation whereby I have hundreds of tables across my instances that need dropping. The tables are prefixed with a name of dataload.x_TableName_dateThe date part is in the format of 20130508201455337 if this helps.The situation has happened because whenever we need to do a datacleanse the Dev's take a backup of the table and then give it the new name, so i am left with hundreds of tables as mentioned above that are no longer needed.Initially I only want to drop tables that are older than 1 month and then I might gradually decrease this amount.Can anybody advise a method of doing this task - maybe via some kind of script as i dont fancy dropping each one manually !Thanks
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog