Rolling Up Records Problem (nice Puzzle) :)

Feb 21, 2008

Here's a nice puzzle for all you masochistic programmers LOL.

Actually, it's just something that's been giving me problems and I know this forum has some amazing programmers who will probably find my problem to be easy.

I'm using 2005.

Simply put...
Here's the data:
ID Start End X
123 10 15 a
123 9 10 b
123 11 18 d
123 14 16 z
123 19 21 x
234 16 18 bb

I'd like to roll up overlapping start and end numbers into one set.
So here would be the output:
ID Start End [Ttl X]
123 9 18 4
123 19 21 1
234 16 18 1

The way I'm thinking of attacking this is maybe a combination of RowNumber & a CTE of some sort.

Please no loops or cursors, or extra tables.

Any ideas guys/gals?

Thanks a mil,
Denvas

View 5 Replies


ADVERTISEMENT

Nice Books?

Jun 14, 2006

hi guys,
i m a newbee in SQL. i wanted to know which is the nice book to start with? anyone got ebooks of SQL2005?

View 1 Replies View Related

Very Nice; That Worked.

Sep 29, 2006

Very nice; that worked.

It seems like a lot of code for each date field, but we are up and running now.

View 1 Replies View Related

What Happened To Nice BCP Interface In SQL 6.5

Nov 20, 1998

In the SQL Object manager tools for sql server 4.21 there was a gui interface for running BCP where you could select tables and fields etc.

I haven't beem able to find abnything equivalent in sql 6.5 enterprise manager. Am I missing someting, or was this dropped?

Thanks.
Jerry Dunn
ViaHealth
jerry.dunn@viahealth.org

View 1 Replies View Related

SP3a + Extended Procedures = Not Nice (help!)

Jul 20, 2005

Hi(sorry this has turned into a bit of an epic...thought I'd as theexperts!)SQL 2k, sp3a. Dual xeon 2.4. 2 gig ram. everything on a 3 disk raid 5.Microsoft SQL Server 2000 - 8.00.760 (Intel X86)Enterprise Edition on Windows NT 5.0 (Build 2195: )Problem seems to be some extended procs that seemed to be running fineon a 'vanilla' install on a different server now seem to be failing.Failures occuring c 6 - 10 hours.Sometimes the failure logs as the xp failure with severity 20 that Ikindof expect :s. other times absolutely nothing is logged.There are a variety of error messages - or sometimes nothing at all.http://www.mapinfo.com/common/docs/.../SWMSS48_RN.pdfdescribes one of our issues, which seems to come out on the restartafter a failure:Error event id is 19011, Message "SuperSocket info:ConnectionListen(Shared-Memory (LPC)) : Error 5."That site suggests:[color=blue]>Workaround>Suggested solutions include:>• Obtain a hotfix from Microsoft (http://www.microsoft.com). You will[/color]need to >cite the 94302[color=blue]>reference number.>• Turn off shared memory in the client network configuration utility[/color]to >disable shared[color=blue]>memory.>• Replace the SQL Server SSMSLPCN.DLL with the SSMSLPCN.DLL[/color](8.00.540) from >SQL[color=blue]>Server 2000 Service Pack 2.[/color]- but I cant find any sensible information which describes this tocorrelate! Searching MS for that number (94302) came up with zip.The dll version we are running is 2000.80.760.0, although earlierversions are elsewhere on the server (disk images).Interesting to note that we seem to be able to replicate the 'leakage'of improperly closed handles on this version, but not on the oldserver runningthat dll version 2000.80.194.0. This I understood to have been fixedin sp3.On one failure we caught the following (involving our extended procs):2004-02-15 15:03:31.40 spid58 Error: 0, Severity: 20, State: 02004-02-15 15:03:31.40 spid58 Stored function'xp_Contest_CalculateScores' in the library 'e:sqlxpga_procs.dll'generated an access violation. SQL Server is terminating process 58..2004-02-15 15:04:32.14 spid64 Error: 0, Severity: 20, State: 02004-02-15 15:04:32.14 spid64 Stored function 'xp_XML_GetSubTree'in the library 'e:sqlxpga_procs.dll' generated an access violation.SQL Server is terminating process 64..2004-02-15 15:04:48.48 spid53 Using 'dbghelp.dll' version '4.0.5'*Dump thread - spid = 53, PSS = 0x472d7200, EC = 0x725fc098*Stack Dump being sent to e:sqlMSSQLlogSQLDump0001.txt* ************************************************** ******************************* BEGIN STACK DUMP:* 02/15/04 15:04:48 spid 53This was followed about a minute later by:2004-02-15 15:04:55.75 spid53 Stack Signature for the dump is0x1CC032652004-02-15 15:04:55.75 spid53 SQL Server Assertion: File:<recbase.cpp>, line=1378Failed Assertion = 'm_offBeginVar < m_SizeRec'.2004-02-15 15:04:55.89 spid53 Using 'dbghelp.dll' version '4.0.5'*Stack Dump being sent to e:sqlMSSQLlogSQLDump0002.txt2004-02-15 15:04:56.01 spid53 SqlDumpExceptionHandler: Process 2292generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQLServer is terminating this process.* ************************************************** ******************************* BEGIN STACK DUMP:* 02/15/04 15:04:55 spid 53As a side issue we have a number of 'initialisation of notify failed'notes.Any one have any clue about this?Might the DLL need rolling back?Is sp3a knacked for extended procs?Is there some daft setting I've missed :/Any good events to track in particular?Cheers all!

View 1 Replies View Related

ONE ZERO ZERO -------&&> One Hundred = Nice String Formula !

Feb 21, 2008



Im trying to build myself a report that recieves a values as worded text e.g

zero zero one zero zero

it would be nice if i write some clever bit of code that would convert this to

One Hundred

Has anyone come up against this before, or could point me in the direction of a tutorial, i would appreciate it

thank you in advance

Jonny

View 6 Replies View Related

What Happened To Nice SqlDataAdapter.Fill Method In ASP.net 2.0

Dec 26, 2005

What happened to nice SqlDataAdapter.Fill method in ASP.net 2.0 + How can i now access and traverse trhough table.
ex : sqlDataAdapter.Fill(dataSetname.tblsname);
            dataSet11.rows[][]

View 5 Replies View Related

Nice Problem: How To Combine Different Columns Into A Table

Jan 18, 2004

It's rather easy to combine resultset from the same table structure...we can either insert the entries or union the results.

But let's say you select different columns from different tables and want to combine them to form a new table, how would you do it (assuming you can't join those tables since they are not related), assuming they all return the same number of rows.

select col1 from table1
go
select col2 from table2
go

Now I want to combine them so table3 is made of col1 and col2.

View 4 Replies View Related

SQL Puzzle

Mar 31, 2004

What's the MS SQL Server SQL for making rows

xxx yyy 1 zzz
xxx yyy 2 zzz
xxx yyy 3 zzz

into a row
xxx yyy 1,2,3 zzz

Or is there another way

View 3 Replies View Related

SQL Puzzle

Aug 6, 2001

Hello all, I have a puzzle that needs solving please. I have a Stored Procedure and it works fine and produces what I want to see. I have two servers replicating and when the stored procedure is ran on the other server, it produces nothing at all but the data is verified the same. So I go to query analyzer and copy the following sql statement from the stored procedures and put in the variables it asks for and I get nothing on either server. Here is the statement:


CREATE PROCEDURE sp_Transfer (
@Weekending datetime,
@Transfer datetime)
AS

select jobs.jobnumber, activities.weekending,
activities.material,
case employees.type when '1099' then case left(lineitems.item,4) when '2392' then (lineitems.quantity * lineitems.draft1099) end else
case employees.type when '1099' then case left(lineitems.item,4) when '2391' then (lineitems.quantity * lineitems.draft1099) end else
case employees.type when '1099' then case left(lineitems.item,4) when '3172' then (lineitems.quantity * lineitems.draft1099) end else
case employees.type when '1099' then case left(lineitems.item,5) when 'ECAD2' then (lineitems.quantity * lineitems.draft1099) else
(lineitems.quantity * lineitems.labor1099) end else
case left(lineitems.item,4) when '2392' then (lineitems.quantity * lineitems.draft) else
case left(lineitems.item,4) when '2391' then (lineitems.quantity * lineitems.draft) else
case left(lineitems.item,4) when '3172' then (lineitems.quantity * lineitems.draft) else
case left(lineitems.item,5) when 'ECAD2' then (lineitems.quantity * lineitems.draft) else
(lineitems.quantity * lineitems.labor) end end end end end end end end as labor,
(lineitems.quantity * lineitems.vehicle) as Vehicle,
(activities.meals + activities.lodging) as PerDiem,
(employees.state) as FState, (states.state) as TState
from jobs
inner join activities on jobs.jobid = activities.jobid
left outer join employees on activities.employeeid = employees.employeeid
left outer join lineitems on activities.activityid = lineitems.activityid
inner join states on jobs.billingstateid = states.stateid
where (activities.weekending = @Weekending or activities.transferdate = @Transfer or jobs.transferdate = @Transfer)
and jobs.billingstateid <> employees.stateid and lineitems.quantity <> null
GO


Can anyone help tell me why this happens or has anyone ever experienced this before?

Thank you in advance!!!!!!

View 3 Replies View Related

SQL Puzzle

Dec 11, 2002

Can anyone solve this puzzle with SQL only - no temp tables and no cursors
(sent to me by a friend)?

I would have done it with a cursor on SQL Server but the rules stipulate not using a cursor. Can this be done with just SQL?

You have a table:

First Name Last Name
John Doe
Jeff Smith
etc..

Is it possible to write a query in SQL which assigns serial numbers 1; 2; 3; to the rows(in any sequence)? So the output should look like this:

Number First Name Last Name
1 John Doe
2 Jeff Smith
etc..

Note that your query must work with any data for the input table. You cannot rely on the fact that there are only two rows. Number cannot be a IDENTITY column.

Thanks!

View 9 Replies View Related

SQL Puzzle

Aug 6, 2001

Hello all, I have a puzzle that needs solving please. I have a Stored Procedure and it works fine and produces what I want to see. I have two servers replicating and when the stored procedure is ran on the other server, it produces nothing at all but the data is verified the same. So I go to query analyzer and copy the following sql statement from the stored procedures and put in the variables it asks for and I get nothing on either server. Here is the statement:


CREATE PROCEDURE sp_Transfer (
@Weekending datetime,
@Transfer datetime)
AS

select jobs.jobnumber, activities.weekending,
activities.material,
case employees.type when '1099' then case left(lineitems.item,4) when '2392' then (lineitems.quantity * lineitems.draft1099) end else
case employees.type when '1099' then case left(lineitems.item,4) when '2391' then (lineitems.quantity * lineitems.draft1099) end else
case employees.type when '1099' then case left(lineitems.item,4) when '3172' then (lineitems.quantity * lineitems.draft1099) end else
case employees.type when '1099' then case left(lineitems.item,5) when 'ECAD2' then (lineitems.quantity * lineitems.draft1099) else
(lineitems.quantity * lineitems.labor1099) end else
case left(lineitems.item,4) when '2392' then (lineitems.quantity * lineitems.draft) else
case left(lineitems.item,4) when '2391' then (lineitems.quantity * lineitems.draft) else
case left(lineitems.item,4) when '3172' then (lineitems.quantity * lineitems.draft) else
case left(lineitems.item,5) when 'ECAD2' then (lineitems.quantity * lineitems.draft) else
(lineitems.quantity * lineitems.labor) end end end end end end end end as labor,
(lineitems.quantity * lineitems.vehicle) as Vehicle,
(activities.meals + activities.lodging) as PerDiem,
(employees.state) as FState, (states.state) as TState
from jobs
inner join activities on jobs.jobid = activities.jobid
left outer join employees on activities.employeeid = employees.employeeid
left outer join lineitems on activities.activityid = lineitems.activityid
inner join states on jobs.billingstateid = states.stateid
where (activities.weekending = @Weekending or activities.transferdate = @Transfer or jobs.transferdate = @Transfer)
and jobs.billingstateid <> employees.stateid and lineitems.quantity <> null
GO


Can anyone help tell me why this happens or has anyone ever experienced this before?

Thank you in advance!!!!!!

View 3 Replies View Related

Sql Puzzle

Jul 23, 2005

Hi all,I have a sql puzzle (which I've solved) but seems very unwieldy. Isthere a more efficient way of doing this?It reads like so:SELECT a.*FROM axindex a INNER JOINaxindex b ON a.id4 = b.id4 AND a.id1 = b.id1 ANDa.id5 = b.id5 AND a.conflict_type = b.conflict_type INNER JOINaxindex c ON b.id4 = c.id4 AND b.id1 = c.id1 ANDb.id5 = c.id5 AND b.conflict_type = c.conflict_type INNER JOINaxindex d ON c.id4 = d.id4 AND c.id1 = d.id1 ANDc.id5 = d.id5 AND c.conflict_type = d.conflict_typeWHERE (a.stext = 'land') AND (b.stext = 'reg') AND (c.stext ='fees') AND (a.conflict_type = 5) AND (d.stext = 'hamilton')Many thanks in advance.Sam

View 1 Replies View Related

Dbo Puzzle

Apr 12, 2007

Hi,

I managed to get things broken and I can't solve it:

Scenario:
SQL Server
--->Databases
--->TestDB
---> Users
------> dbo mapped to user1 (user1 = SQL Server user)
------> user1(database user) mapped to nothing (no SQL user)
---->Security
-------> Logins
-------> user1

Problem: I don't want SQL Server user 'user1' to be dbo but I can't remove dbo rights because it owns objects nor can I drop database user 'user1' or delete SQL Server user 'user1' because it's mapped to dbo.

I'm running in circles..
First and last time I use the auto_fix option to fix orphaned users.
At this point, the only solution to me is dropping the database.

Anyone with a better idea?

Thank you!
Best Regards,
Worf


View 2 Replies View Related

Transact SQL Puzzle

Jan 17, 2008

Here's a fun little puzzle that I thought up based on some rather gnarly problems I faced today. It might brighten up your morning. See if you can figure out what it does and how it does it.

How does it work? When will it fail as written (this example does not fail)? Why will it fail? How can you change it to increae its capacity?-- ptp 20080117 SQL puzzle

DECLARE @iINT
, @cVARCHAR(360)

SET @i = 90
SET @c = ''

WHILE 64 < @i
BEGIN
SET @c = Char(@i) + Space(@i - 64) + @c
SET @i = @i - 1
END

SELECT Replace(Replace(Replace(Replace(@c
, ' ', ' '), ' ', ' '), ' ', ' '), ' ', ' ')
UNION SELECT @c-PatP

View 10 Replies View Related

Deadlock Puzzle

Jul 18, 2007

I've been running my simulations against our SQL Server 2005 database all day today and have been noticing a lot of deadlocking. Almost every transaction was deadlocking and retrying.

About an hour ago I started SQL Profiler and enabled trace flags using DBCC TRACEON(3605, 1204). However, even though Performance Monitor was indicating high Deadlocks/sec ratio I could not find any evidence of them in SQL Profiler or in SQL Server error log. I restarted my simulations several times, but nothing changed.

So, then a miracle happened and the last time I started my simulations all deadlocks disappeared. I did not change any queries and I am puzzled as to what could have happened. The only things I did were starting SQL Profiler and executing DBCC TRACEON commands.

Since queries and transactions did not change, could it be that they started executing (and hence releasing locks) faster because of lower network traffic at the end of the day or because the transaction log was written to disk?
Could it be anything that SQL Server 2005 does to optimize its own performance?
What else could have caused such a drastic change?

Thanks.

Alec

View 1 Replies View Related

Job Execute Puzzle

Oct 11, 2007

Hi,

Can I find out the user who has executed the job?

The result of query Q1) gives this information in the message column of the output.

Q1) select * FROMMsdb..sysjobhistory

Do we have any other table or SP from this information can be captured directly?

Regards,
Ashish Johri

View 1 Replies View Related

A Small SQL Puzzle

Sep 26, 2007

Hi,Probable there is a simple solution for this, hopefully someone candirect me in the right direction.I have a table with a persons firstname, lastname, birthdate andaddress. However, I want to select only one person per address, namelythe eldest of all persons living on the same address.Can anyone provide me a solution?Thanks in advance.Duncan

View 2 Replies View Related

Import Puzzle

Jul 20, 2005

Hello -I have three feeds from sources around the world, each coming in at aseparate time.These feeds move into a large table (3GB) that is queried by managers.The feeds are loaded sequentially, and then the previous day's feedrows are deleted from the table (this is done so that the user'sapplication is never without data).The issue is that the import takes a lot of time, as do the deletes.These is hurting performance significantly. I attempted to fix theproblem by creating separate tables for each feed. I then created aview with the original table's name and used UNION ALL's. My intentionwas that as each feed came in, I'd alter the view with the new table'sname, and then truncate the older table. This met both goals ofconcurrency and import/delete speed.Unfortunately, this view seems to ignore the indexes on the underlyingtables, which devastates performance. I can't index the view, sincealtering it makes the index less useful.I'm looking for a different strategy for loading and deleting thedata, all without disruption to the applications. I'd appreciate anysuggestions....

View 3 Replies View Related

Precedence Puzzle

Aug 7, 2006

Hello folks,

I have probably a dumb newbie question but I can't find the answer anywhere.

On my Control Flow design pane I have two objects: a SQL task object and a Data flow task object. The first 'points' to the second. From my digging I believed that by indicating with the arrow from 1 to 2 that 1 would execute to finish before 2 was started.

My SQL taks is to truncate a table to receive the new data coming from the data flow task object. Instead 2 executes first and then 1. You can imagine producing an empty table was not my goal for this package.

Can anybody give me a clue?

Thanks....

Jim

View 9 Replies View Related

Simple And Silly SQL Puzzle

Mar 31, 2004

Hello SQL Gurus,

I want to dynamically add a column to a select statement who must contain the line number.

Example :

Some table has 3 columns (id,field1,field2)


SELECT top 3 *,[lineNumber] as lineOrder FROM someTable

must produce

id | field1 | field 2 | lineOrder
-----------------------------------
1 abc def 1
23 def ghi 2
7 ghi jkl 3

and so on... Possible ?

View 5 Replies View Related

A Logical Puzzle Answer Me, Is It Possible To Get

Nov 30, 2005

I have a view based on few tables okay
I have a query that gives me document details of various revisions
Now i also get the latest rev of those if i use a MAX() in my Query

Now a case where i have say

Docu Numb - Revi - Received Date

Document1 - rev0 - 10/2004
Document1 - rev1 - 11/2004
Document1 - rev2 - 12/2004
Document1 - rev3 - 01/2005
Document1 - rev4 - 02/2005
Document1 - rev5 - 03/2005
Document1 - rev6 - 04/2005
Document1 - rev7 - 05/2005


If i query for latest it gives Document1 - rev7

And if i filter for a month of 12/2004
If i query for latest it should give Document1 - rev2

Now i need to get a filter with latest as rev2,
plus i also need as a indication of actual latest as rev 7
using a query

How do i do that ...

Coz when i use filter for a month and Max then it
does not consider rev7 for that filter and skip the record
and if i try to filter only as per month .. i get a wrong info
as latest rev2 which is not a true info as per database records.


May i possibly get both ... filtered info as per specific month and
also a rev info as what is the latest for a spec document.

Just i need to know is that possible .. in SQl using a query

Any more info needed please mail me on
neeraj.jariwala@gmail.com


Life is beautiful ... When you smile ...

View 1 Replies View Related

Connecting To SSIS Puzzle

Jul 23, 2007

Let me try to put this down in words...

If I log into the SQL server with my credentials and connect to SSIS through SQL Management Studio, I'm able to execute my test SSIS package successfully.

If I go to my machine and try to connect to SSIS "Integration Services..." I get "Access Denied" message.

If I execute the same package via DTEXECUI, the package errors out also.

My account is member of the group "Security_IT_Data", this group has sysadmin privileges on the SQL Server level.

The account used to run the SSIS Service is part of the Local Admin group on the box and also a sysadmin on the SQL Server.

What am I missing?

View 4 Replies View Related

Little Puzzle On Data Selection

Jul 20, 2005

I have the following data (very simplified version)TransactionId Agent_Code------------- ----------191462 95328C205427 000024C205427 75547CAgent Code 75547C is a corporate agent. The others are not. I have alist of corporate codes so I can query against it, BUT what I want todo is...Return a unique TransactionId and max of the AgentCode, but if theAgent is a corporate agent, I need to return max of the corporate agentcodes. We can have multiple agents against the transaction andsometimes have a mix of corporate and none corporate agents. What weneed to do is see the corporate adviser if there is one. I only want 1record per TransactionId.We derive more data (sales hierarchy) from this, so are not interestedin anything other than the maximum, but need to know if it wascorporate which therefore gives me a different hierarchy later.Ideally I want to do this in a view and not use an SP. I can then usethis in my main view. If I have to resort to an SP, then so be it, butI would appreciate any helpful comments (or even better, the answer)ThanksRyan

View 4 Replies View Related

Sp_add_jobschedule Puzzle For Special Weekly Job....

Jul 18, 2003

Hi:
in sp_add_jobschedule
@freq_type = 8 for weekly @freq_interval = 1 for Sunday.

I need to script to create a job which will run every Sunday 6 pm, to backup one prod database to a new backup device. EX. database name is DBTest, and the 7/20/2003 (Sunday) running result should be backup_DBTest_2003_07_20.bak.
and next Sunday's should be backup_DBTest_2003_07_27.bak.
(if this way, the backup device will be created at the begining of the job running...)

this way the backup would not be overwritten. Is there a way to dynamically generate 54 weeks job script from this coming Sunday?

Or to make the backup result as backup_DBTest_2003_week27.bak, backup_DBTest_2003_week28.bak....

thanks
David

View 2 Replies View Related

Sql 2000 Price && License Puzzle...

Nov 21, 2003

SQL 2000 pricing and licensing is quite confusing, and even more pain including the upgrading pricing from sql6.5/sql7.0.

I got very clear Windows2000 pricing and upgrading from one Microsoft web address. But just could not find a ONE Microsoft web sit explicitely describe the $ pricing for each of SQL2000 edition (per processor vs. CAL..., including upgrading from SQL 6.5, SQL 7.0). May be I missed that site?

Thanks
David

View 2 Replies View Related

Identical Server But Different Performance Puzzle

Mar 16, 2007

Guys,

We have MSSQL 2000 Server instance installed and working well on Windows 2003 Server machine [IBM X series-366] with 16GB RAM, 3.67GHZ cpu power, and 400GB hard disk space.

We further created an identical server instance on a new machine. More specifically, on Windows 2003 Server machine [Intel (R) Xeon (TM)] with 16GB RAM, 3.67GHZ cpu power, and 400GB hard disk space, we installed MSSQL 2000 Server and copied over all the dbs, applications ...

We were expecting same or similar performance (since processor speed, ram, hd, server and database configurations are all the same, with same indexes on same tables. However, for some reason, there is a noticeable difference in performance.

More specifically, I ran Profiler for 30 minutes on both servers simultaneously [same trace parameters]. The trace file of the new server is 3 times as large as that of the old one (i.e. It looks like more items are being processed). However, the average duration of the executed stored procedures is much longer on the new server than that of the old server.

Moreover, when I run same queries on 2 servers. The query on the new server always takes longer than that on the old server. And for tables where we don't have indexes, it takes much longer.

Following advice here(http://support.microsoft.com/kb/274750/), we configured our new server (just as was our old one configured) to use 15GB of RAM. I further compared the configurations of 2 servers by executing sp_configure (with advance options). The only difference I saw was that "remote proc trans" is set to off on the new server and on on the old server. I don't think it could affect this issue though.

Furthermore, the new server appears to have many more locks, as compared to the old server. Could it be because it is processing more items?

I cannot figure what is causing the queries to be slower on the new server.

Can anyone suggest anything?


Thanks a lot

View 3 Replies View Related

Concurrency Puzzle (ODBC API Related)

Jul 20, 2005

I hope this is not outside the scope of this newsgroup.have been useing BulkAdd with ms-access with good success.Until now that I;m switching to MSDE which is on a shared server.The problem is that MSDE gives an error:Invalid attribute/option identifier, sql state=HY092which corresponds to:SQL_ATTR_CONCURRENCY statement attribute was set to SQL_CONCUR_READ_ONLY.but my BulkAdd routine explicitly has a call to:SQLSetStmtAttr(stHndl, SQL_ATTR_CONCURRENCY, SQL_CONCUR_LOCK , iRet)I cannot imagine how the concurrency is readonly.Could it be that I'm looking at the wrong place or the wrong attribute???Thanks in adv.

View 6 Replies View Related

A Query Sentence Return A Puzzle Result

Apr 28, 2006

the talbe row like this:
  ID   Name   Scoe 11     Tome    20 12    Jack   30 11     Tome    40 12   Jack   10 13   John    10 
My query command like this:
Select T1.Id,T1.Name,T2.mathfrom st T1right  join(Select Id as Id2,Sum(Math) as Math from St  group by id) T2on T1.id=t2.id2where t1.id = t2.id2
While the reuslt is :
Id      Name   Score
11    Tom        60
11    Tom        60
12    Jake        40
12    Jack        40
13    John        10   
 
I am wonder :the T1 gives a table with six rows, the T2 gives a table with three rows,  and I use RIGHT JOIN to connect the two table,the result should be a table with only three rows.I tried INNER JOIN, the result is same.
but why ? please help me !
 
 

View 1 Replies View Related

Surprising Backup File Growth - Puzzle!!!

Mar 29, 2007

Guys,

I am a bit puzzled. Our database backup grew from the usual size of ~27GB to ~40GB, all of a sudden. Nothing special happened in the last few days - nothing major to cause such increase.

I found out about this, because we suddenly had the backups failing, and when I explored, I saw that this was due to the lack of space on the hard-disk.

I do know that we need additional hard disk space. In the meantime, however, I'd like to be able to identify what exactly could cause such growth.

As far as I understand, for the backup to grow, the database needs to grow in a similar proportion. My only theory is that when the backup failed a few times, each time, somehow, it resulted in the database growth. Does this make any sense?

Another clue is that the backup job, which usually runs ~ 30 minutes has been running for 6 hours already, the file has grown to 40GB, and the backup job is still running ...

What is the best way for me to explore what exactly happened? Are there some system tables containing history of table counts or something - so that I can see who grew when and by how much?

I ran a query to see which objects were added in the past few days - that did not give me any clues - all looks normal.

Any ideas/suggestions?


Thanks a lot

View 5 Replies View Related

A Puzzle Of Data Type In SQL Server 2005

Oct 13, 2007

Recently I began to use SQL 2005. But yesterday I found a puzzle about data type.
I use this script to create a table:

CREATE TABLE [dbo].[InvoiceMaster](
......
[InvoiceMemo] [varchar](max) NULL,
......
)

But when I run this cmd : exec sp_columns InvoiceMaster. I find the type_name of this column changed to "text".
Can anybody tell me why?

Thanks in advance!

View 2 Replies View Related

Puzzle: NULL In Conditional Split Error

Oct 14, 2006

A little puzzle with this Error - Status and PropertyType fields do not allow nulls while other columns do ( there are about 20 columns in original expression but left out for simplicity - these are created the same way ). Any ideas how to solve it?

[Conditional Split Filter Changes [813]] Error: The expression

"LOOK_Status != Status || LOOK_PropertyType != PropertyType
|| (LOOK_OfficeName != OfficeName || ISNULL(LOOK_OfficeName) != ISNULL(OfficeName))
|| (LOOK_OfficeID != OfficeID || ISNULL(LOOK_OfficeID) != ISNULL(OfficeID)) "

on "output "Listing Changed" (885)" evaluated to NULL, but the "component "Conditional Split Filter Changes" (813)" requires a Boolean results.
(My wish for next version is to have better expression editor and be able to see the output for those in dataflow transforms with lets say sample 200 rows)

View 8 Replies View Related

Rolling-back

Mar 27, 2001

If I issue -
update x set y = 'z'

is there a way to undone/roll-back what was done in SQL 7 ?


Thanks very much.

-FB

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved