Friday, March 15, 2013

[SQL Server] Obtaining Before & After Record & Missing Record.

[SQL Server] Obtaining Before & After Record & Missing Record.

Obtaining Before & After Record & Missing Record.

Posted: 15 Mar 2013 11:43 AM PDT

I have a database that has two tables. On table is a date table that has all of the possible dates for a year within it. Within this table is a field ("concatenate") that contains a varchar data type specification that contains the date in the following format 201201010510. This represents the years, months, days, hours and minutes. This table is joined to a data table that contains the same varchar field named "concatenate". How do I query the data table to obtain the missing dates as well as the first prior matched date and the following matched date. Listed below is the format of the data.Record Concatenate1 2012010105102 2012010105113 2012010105144 2012010105155 201201010517The query would report201201010511201201010512201201010513201201010514201201010515201201010516201201010517

rows to columns - creating view for SSRS

Posted: 15 Mar 2013 02:01 AM PDT

I think this issue is complex (at least to me) but will try to explain correctly and put some meaningful info in.I am trying to create a view to access from BIDS.The originating table goes like thiswfId created logType activityName agentSource agentName outcome detailsC7 2013-03-14 310 Technical Fred John NULL Pre-Sales - Check Technical DetailsC7 2013-03-15 316 Technical John Fred Reject Performed By JohnC7 2013-03-15 310 TechReject John Fred NULL Originator - Technical RejectionC7 2013-03-16 316 TechReject Fred John NULL Performed By FredC7 2013-03-16 310 Technical Fred John NULL Pre-Sales - Check Technical DetailsC7 2013-03-16 316 Technical John Fred Checked Performed By JohnC7 2013-03-16 310 SalesDirector John Steve NULL Sales Director - Please Approve ProposalC8 2013-03-14 310 Technical Fred John NULL Pre-Sales - Check Technical DetailsC8 2013-03-15 316 Technical John Fred Checked Performed By JohnC8 2013-03-15 310 SalesDirector John Steve NULL Sales Director - Please Approve ProposalI am trying to return the following in the viewworkflowId TechDt TechAgent TechOut TechOutDt TechDur ReSubDt ReSubAgent ReSubOut ReSubOutDt ResubDur SDAgent SDOut SDDurC7 2013-03-14 John Reject 2013-03-15 1 2013-03-16 John Checked 2013-03-16 0 Steve NULL NULLC8 2013-03-14 John Checked 2013-03-15 1 NULL NULL NULL NULL NULL Steve NULL NULL[i]Where Dt = Date, Out = Outcome, Dur = Duration[/i]For this example, assume there can only be one re-submit and the only outcome for SalesDirector is Accept or Reject with no resubmit n the SalesDirector outcome.In words, I am trying to get the duration of each step of the workflow, in the real database there are many possible legs to a workflow and many possible routes but they all follow the same basic principal. I would like to be able to see where the current WorkflowID is, for example C8 and C7 is currently with the Sales Director.I have tried unions and tried inner joins to get the data onto one line by doing an inner join then using "where" in each join to separate out the different steps and get them into columns, my biggest issue is that the second workflow submission is the same as the original submission and I need to identify it as a different one. The secondary issue is I don't know which method would run fastest. The actual database has around 40 000 records per year and around 8 lines per unique workflowId.If this is not explained well, please tell me and I will attempt to make it more clear

Backup databases depending on the size to multiple files

Posted: 15 Mar 2013 06:36 AM PDT

DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name -- specify database backup directorySET @path = 'C:\Backup\' -- specify filename formatSELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '_' + @fileDate + '.BAK' BACKUP DATABASE @name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor

Month Summarised tabular data output from a table

Posted: 15 Mar 2013 03:52 AM PDT

Using SQL how is it possible to get this data transformed in a tabular summary report? data samplesupplier number selection_date document_type0000001 01/07/2012 AB100190000002 01/07/2012 AB200110000001 01/07/2012 FR100890000031 01/08/2012 AB100840000021 01/08/2012 FR100890000001 01/08/2012 FR100890000005 01/09/2012 FR130190000003 01/09/2012 AB150060000010 01/11/2012 FR100190000012 01/11/2012 FR134490000010 01/11/2012 FR133190000011 01/11/2012 AB14215Report output:[quote]Month Name Count of AB-Types Count of FR-Types July 2 1 Aug 1 2 Sept 1 1 Nov 1 3[/quote]The report should be in order of the month I will filter it by Year 2012/2011. Assume there are only AB and FR type documents with different numberings at end (so use of like operator is required)There will be no Zero count months Thank you.

Using an ORDER BY clause with Multiple IF ELSE statments

Posted: 15 Mar 2013 05:01 AM PDT

Posted - 03/15/2013 : 13:58:23 Hello all,I've created a (probably over complex) query that will eventually allow a user to select a value from 3 different variables. @Status, @AcctName, @RptName. Since I'm allowing the end user to multi-select different values from the variables, my query begins to become a bit more complex... For instance:declare @status varchar(150)select @status = 'ERROR,REVIEW,COMPLETE,DELIVERY PREPARED,ALERT'IF (@status = 'ALL'AND @AcctName <> 'ALL' AND @RptName <> 'ALL') BEGINselect * from vw_document_mainWHERE ','+REPLACE(@AcctName,'','')+',' LIKE '%,'+acct_name+',%'AND ','+REPLACE(@RptName,'','')+',' LIKE '%,'+rpt_title+',%'ENDELSEBEGIN.......Since I'd have to allow for customization I have 9 different IF ELSE statements. What I need help with doing is creating an ORDER BY variable for the entire IF ELSE statement. I.E - @ColName = 'system_Status' then the entire IF ELSE statement would Order by System Status... Any idea? I began writing a query...ORDER BY CASE @ColNameWHEN 'system_Status' THEN system_status WHEN 'document_status_code' THEN document_status_codeHowever, I'm not sure where this ORDER BY clause would go into my IF ELSE statement... would it need to be after each individual SELECT statement? Any clarification would help, thanks!!

Entry Level DBA position - SQL Server experience needed

Posted: 15 Mar 2013 12:14 AM PDT

Hello,I have a job opportunity for an Entry Level DBA located in Malvern, PA and they are looking for someone with an IT-related degree as well as some SQL Server and DBA experience. If you are interested please contact me at dana.murk@insightglobal.net.Thank you!-Dana

SSIS - Data Flow Task With Delete Very Slow

Posted: 14 Mar 2013 08:26 PM PDT

I have a Data Flow Task that reads from a Flat File Source (orders.csv) and then does an OLE DB Command to do a delete from a SQL database table if a match is found. This is the SQL command in the OLE DB Command Task: Delete orders from orders with(index(IX_orderno)) WHERE orderno = ? and ordersuf = ? and oetype in ('r','t','d') and cono = ?(The index IX_orderno is defined as non-clustered and is orderno + ordersuf + oetype + cono. )There are 400,000 records in the orders.csv and the delete is extremely slow (over 1 hour.)We do have 8 indexes on this table, all non-clustered. Would this have any ramifications on the deletion speed?Help would greatly appreciated.
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog