Wednesday, July 3, 2013

[SQL Server] SQL to combine rows based on dates and other common factors

[SQL Server] SQL to combine rows based on dates and other common factors

SQL to combine rows based on dates and other common factors

Posted: 03 Jul 2013 04:40 AM PDT

Hi All,We have a database for employees, jobs and work allocation. I am trying to find an efficient way to combine rows based on date and work load but excluding vacation allocations.Here is an example of the current data set:[b]ID Employee Job StartDate EndDate Workload[/b]1 John Doe HSBC 01/01/2013 31/12/2013 1002 John Doe Vacation 17/06/2013 21/06/2013 1003 John Doe HSBC 01/01/2014 31/12/2014 1004 John Doe Vacation 19/08/2013 23/08/2013 1005 John Doe Barclays 01/01/2014 31/01/2014 506 John Doe Barclays 01/01/2015 31/01/2015 507 John Doe Santander 06/01/2014 25/01/2014 508 John Doe Santander 05/03/2014 17/07/2014 809 John Doe Santander 05/03/2014 17/07/2014 5010 John Doe Vacation 13/01/2014 17/01/2014 100The final result should be as follows (bold indicates the changed rows excluding header). Note that the IDs are not important at this point and also the work allocation to the job 'Santander' are separate rows as one is at 50% workload and the other is 80. The rows should only be combined if the employee, job and workload is the same. 'Vacations' are not combined at all.[b]ID Employee Job StartDate EndDate Workload[/b][b]1 John Doe HSBC 01/01/2013 31/12/2014 100[/b]2 John Doe Vacation 17/06/2013 21/06/2013 1004 John Doe Vacation 19/08/2013 23/08/2013 100[b]5 John Doe Barclays 01/01/2014 31/01/2015 50[/b][b]7 John Doe Santander 06/01/2014 17/07/2014 50[/b]8 John Doe Santander 05/03/2014 17/07/2014 8010 John Doe Vacation 13/01/2014 17/01/2014 100Here is the DDL which another forumite kindly provided for an earlier query. Any suggestions would be greatly appreciated.[code="sql"]set dateformat dmyif OBJECT_ID('tempdb..#Something') is not null drop table #Something create table #Something( ID int, Employee varchar(20), Job varchar(20), StartDate datetime, EndDate datetime, Workload int)insert #Somethingselect *from (Values(1, 'John Doe', 'HSBC', '01/01/2013', '31/12/2013', 100),(2, 'John Doe', 'Vacation', '17/06/2013', '21/06/2013', 100),(3, 'John Doe', 'HSBC', '01/01/2014', '31/12/2014', 100),(4, 'John Doe', 'Vacation', '19/08/2013', '23/08/2013', 100),(5, 'John Doe', 'Barclays', '01/01/2014', '31/01/2014', 50),(6, 'John Doe', 'Barclays', '01/01/2015', '31/01/2015', 50),(7, 'John Doe', 'Santander', '06/01/2014', '25/01/2014', 50),(8, 'John Doe', 'Santander', '05/03/2014', '17/07/2014', 80),(9, 'John Doe', 'Santander', '05/03/2014', '17/07/2014', 50),(10, 'John Doe', 'Vacation', '13/01/2014', '17/01/2014', 100)) x(a,b,c,d,e,f)select * from #Something[/code]

ODBC Connection Fail

Posted: 03 Jul 2013 07:22 AM PDT

Hello,I am having a problem with an Access application that we use to show and print checks. I believe the application was created with Access 2003 connecting to SQL Server 2005. It ran ok, but not always perfectly, on my old machine (Windows XP) using Access 2007. When we recently upgraded to SQL Server 2008, it continued to run ok. Now we've upgraded from XP to 2007 and I get an 'ODBC -- connection to 'SQL Native ClientDbaseName' failed'.I can create a new access database and link to the tables that I want to, so I believe that means I have the correct drivers? But maybe the checks database is still connecting with an old string? Although, I can update the tables successfully and still get the error.I am hoping to not have to rewrite the Access checks program, but if I do - do I use a file source as my .dsn instead of a machine source? It seems like we've had problems getting this program onto other machines, and I'm thinking it might be because it was created with a machine source?Any articles on recreating ODBC connections when moving to a new system?Thanks!Mary

Difference between Cluster and Index

Posted: 03 Jul 2013 03:53 AM PDT

Hi, I am really confused about difference between `Cluster` and `Index`.I think they are same.Please help me to find a correct answer for my mistakes.1. What is Cluster?I think cluster create indexes on a column, so you can reach row faster.2. What is Index?I think indexing on a column helps to reach row faster. (So what is difference between this and cluster?)3.[code="sql"]CREATE CLUSTERED INDEX myIndexON tableName (Column1, Column2, Column3)[/code]What the...? :blink:Does it mean if two rows have same value for Column1 then lower value for Index choose by Column2?4.[code="sql"]CREATE NONCLUSTERED INDEX myIndexON tableName (columnName)[/code]What? :blink:Nonclustred index? How is it possible to an Index be nonclustred?Im really confused, thank you for help.

SQL to split row by date (split into multiple rows)

Posted: 11 Jun 2013 08:42 PM PDT

I am looking for help with splitting a row into multiple rows based on dates overlapping.As an example, I have a table with the following data:[b]Row ID, Employee, Job, Start Date, End Date, Workload[/b]1, John Doe, HSBC, 01/01/2013, 31/12/2013, 1002, John Doe, Vacation, 17/06/2013, 21/06/2013, 1003, John Doe, Vacation, 19/08/2013, 23/08/2013, 1004, John Doe, Barclays, 01/01/2014, 31/01/2014, 505, John Doe, Santander, 06/01/2014, 25/01/2014, 506, John Doe, Vacation, 13/01/2014, 17/01/2014, 100I am looking to split the banking rows where they overlap with a vacation. So for example, the final result should be:[b]Row ID, Employee, Job, Start Date, End Date, Workload[/b]1, John Doe, HSBC, 01/01/2013, 16/06/2013, 1002, John Doe, Vacation, 17/06/2013, 21/06/2013, 100[b]3, John Doe, HSBC, 22/06/2013, 18/08/2013, 100[/b]4, John Doe, Vacation, 19/08/2013, 23/08/2013, 100[b]5, John Doe, HSBC, 24/08/2013, 31/12/2013, 100[/b]6, John Doe, Barclays, 01/01/2014, 12/01/2014, 50[b]7, John Doe, Barclays, 18/01/2014, 31/01/2014, 50[/b]8, John Doe, Santander, 06/01/2014, 12/01/2014, 50[b]9, John Doe, Santander, 18/01/2014, 25/01/2014, 50[/b]10, John Doe, Vacation, 13/01/2014, 17/01/2014, 100New rows after split are in bold. The Row ID should be unique although it doesn't need to be sequential. Any help or guidance would be appreciated.

Writing script

Posted: 03 Jul 2013 02:00 AM PDT

I need to add a script so that my system will pull a word document using a program I have. Can I write the script in notepad and save it as "xxx.sql" and then just save it the the fold that houses the doc? I already have the script written out.

DTS To SSIS Conversion

Posted: 03 Jul 2013 01:21 AM PDT

I am tying to convert a n DTS package to SSIS. I have used pragmatic works to convert so far but it does not convert Visual Basic.The code looks like[code="vb"]Function Main() If Left(DTSSource("Col001"),6) = "STOCK" Or Left(DTSSource("Col001"), 6) = "PROJCT" Then DTSDestination("Column_Out") = Mid(DTSSource("Col001", 7, 25) DTSDestination("Column_Out_2") = Mid(DTSSource("Col001", 60, 25) Main = DTSTransformationStat_OK Else Main = DTSTransformationStat_SkipRow End ifEnd Function[/code]With a little bit of research I was able to do this[code="vb"]Public Overrides Sub NightlyInput_ProcessInputRow(ByVal Row As NightlyInputBuffer) Dim strRow As String strRow = Row.toString If (Left(strRow, 6) = "STOCK") Or (Left(strRow, 6) = "PROJCT") Then NightlyOutBuffer.ColumnOut = Mid(strRow, 7, 25) NightlyOutBuffer.ColumnOut2 = Mid(strRow, 60, 25) end ifEnd Sub[/code]I don't seem to be getting any output from the code.Thanks for you help.

a query like factorial

Posted: 18 Mar 2013 05:00 AM PDT

Hi.I ask you for help because its more than 2 days im thinking about this question.Imagine we have a table like this:ID | Value---------1 | 192 | 903 | 204 | 85 | 9I want my query make this output:ID | Value---------1 | 192 | 19 + 903 | 19 + 90 +204 | 19 + 90 +20 + 85 | 19 + 90 +20 + 8 + 9I already tried so many queries, the last query I wrote are this but it dont make correct output:[code="sql"] WITH CTE (rowNum,Value,Level) AS( SELECT ROW_NUMBER() OVER (ORDER BY ID DESC) as rowNum, Value 0 as Level FROM Mytable WHERE ID = (SELECT MIN(ID) FROM Mytable) UNION ALL SELECT ROW_NUMBER() OVER (ORDER BY ID DESC) as rowNum, Value Level + 1 FROM Mytable a INNER JOIN CTE b ON b.rowNum <= a.rowNum + 1 --a.rowNum are not allowed here )SELECT rowNum, SUM(Value)FROM CTEGROUP BY LEVEL, rowNum[/code]Thank you for help
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog