I need to use a temp table to do the following

Question

I need to use a temp table to do the following

bluetea.55z

Newbie

Points: 6
More actions
April 11, 2013 at 9:09 pm

#276440

I know there are probably other ways to do this, but temp tables must be used.
The tables I have in the DB are:
Authors
Books
Categories
BookCategories
Customers
Orders
OrderLine
OrderStatus
I guess you can assume what kind of fields fill these tables. Thanks in advance!

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

bluetea.55z (4/11/2013)
I need to do the following:
Write a query to display a daily report of all books sold this week including 0 sold, use temp tables.
Columns in the result should be:
Date
Category
Author’s State
Number of Books Sold
I know there are probably other ways to do this, but temp tables must be used.
The tables I have in the DB are:
Authors
Books
Categories
BookCategories
Customers
Orders
OrderLine
OrderStatus
I guess you can assume what kind of fields fill these tables. Thanks in advance!

This looks like homework. How about you show us what you have tried to solve the problem and we will try and show you where you are going wrong. Doesn't do us or you any good if we just give you the answer to the problem.

And no, we aren't going to guess at what columns may be defined in your tables.

bluetea.55z Newbie Points: 6 More actions · Answer 2

DECLARE @begindate date = getdate()

DECLARE @enddate date = getdate()

SELECT DATENAME(weekday, Orders.DateCreated) as [Weekday], Orders.DateCreated as [Date], Categories.CategoryName as Category, Authors.State, isnull(SUM(OrderDetails.OrderID),0) as [NumberSold]

INTO #booksales

FROM Books

Left Outer join OrderDetails on Books.BookID = OrderDetails.BookID

Join Orders on Orders.OrderID=OrderDetails.OrderID

Join Authors on Books.AuthorID = Authors.AuthorID

Join Categories on BookCategories.CategoryID=Categories.CategoryID

WHERE Orders.DateCreated BETWEEN DATEPART(dw,Orders.DateCreated) AND DATEPART(dw,Orders.DateCreated)

GROUP BY DATENAME(weekday, Orders.DateCreated), Orders.DateCreated, Categories.CategoryName, Authors.State

SELECT * FROM #booksales

Drop Table #booksales

Sean Lange SSC Guru Points: 286609 More actions · Answer 3

So what is the question here??? We see some table names and a couple queries. There is no explanation of what you need help with. We can help but we don't know what the question is.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

There's absolutely no need to use temp tables in that, doing so just adds complexity and reduces the performance.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

GilaMonster (4/12/2013)
There's absolutely no need to use temp tables in that, doing so just adds complexity and reduces the performance.

True, Gail, but apparently it is required that a temp table be used. I think this is a homework assignment.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

Lynn Pettis (4/12/2013)
GilaMonster (4/12/2013)
There's absolutely no need to use temp tables in that, doing so just adds complexity and reduces the performance.
True, Gail, but apparently it is required that a temp table be used. I think this is a homework assignment.

Yes, I did read the question. Doesn't change the fact that the query does not in any way need a temp table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass