SQL and Database: Unable to drop temp table?

useful info
1

I’m trying to get a running total for a count, and I decided that the best way to do this would be to dump some counts into a temp table, then do a sum.

I have:

    DECLARE     
@startDate datetime = '3/3/2014 09:00',
@endDate datetime = '4/4/2014 23:00'

CREATE TABLE #SC
(Spec_Date DATETIME, Spec_Count INT)

SELECT CONVERT(VARCHAR , Sn_Date , 101) + ' ' + DATENAME(weekday , Sn_Date)+ ' ' + LEFT (CONVERT(VARCHAR , Sn_Date , 108), 5) 'Date/Time'
    ,COUNT (Sn_ID)
    INTO #SC
    FROM <table_name>
    WHERE Sn_Date BETWEEN @startDate AND @endDate)

SELECT #SC.[Date/Time]
,SUM(#SC.Spec_Count)
FROM #SC

DROP TABLE #SC

I get an error saying "Cannot drop the table ‘#SC’, because it does not exist or you do not have permission. If I try to run the code, it tells me that “There is already an object name ‘#SC’ in the database.”

I’ve tried exiting the application and opening it again, hoping that the temp table would go away when I ended the session, but it didn’t. I don’t see #SC in the table list…

2

If I’m reading your query right, you can do it w/o the temp table - use nested queries instead.

SELECT [Date/Time]
	,sum(spec_count)
FROM (
	SELECT CONVERT(VARCHAR, Sn_Date, 101) + ' ' + DATENAME(weekday, Sn_Date) + ' ' + LEFT(CONVERT(VARCHAR, Sn_Date, 108), 5) AS 'Date/Time'
		,COUNT(Sn_ID) AS spec_count
	FROM < table_name >
	WHERE Sn_Date BETWEEN @startDate
			AND @endDate
	GROUP BY CONVERT(VARCHAR, Sn_Date, 101) + ' ' + DATENAME(weekday, Sn_Date) + ' ' + LEFT(CONVERT(VARCHAR, Sn_Date, 108), 5)
	) Counts
GROUP BY [Date/Time];

I don’t have a dataset handy that I can test this with easily to make sure it’s getting the sums you’re after.

3

The table dropped when I was playing with the code. :shrug: Not sure why it works now. I’ll play with the nested queries, @dakboy. Thanks!

Grrr… I’m trying to get a running total, but it doesn’t seem to be working. SUM is getting the same result as COUNT.

4

For a running total, you want to use a Window Function if possible. See http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver/10309947#10309947 if you’re running SQL Server 2012

If you’re on an older release, a recursive CTE I guess is the way to go. http://stackoverflow.com/a/13744550/1324345

5

Go with what Dak said. It’ll be better.

The reason you were getting a failure before is because your SQL was creating #SC and then you were doing a “SELECT… into #SC”. That in itself will create #SC. Before the whole thing gets run, SQL server makes sure everything is copacetic, except the SELECT is going to try to create a table that you’ve already created. As a result, it didn’t do anything except return a somewhat misleading error message.

Instead of “SELECT … INTO”, you need to do a “INSERT #SC … SELECT”. Either that or don’t create #SC, just let the SELECT do it for you.