Solved

Multiple subquery "select as" in SQL2005

Posted on 2011-03-02
1
409 Views
Last Modified: 2012-05-11
Is there a more efficiant way to code the following query? (sorry I'm very new to SQL) I'm trying to build an asp page for inventory management. Here is a sample of the stored procedure to return the data. I have 50 products and have to keep joining tables becuase I'm exceeding the 256 subquery select limit in SQL2005.
tbl_inventoryreconcile has one entry per month, others have one entry per day.

Thanks
ALTER proc [dbo].[usp_GetInventoryMTDSelectedDate]
(
	@enddate datetime
)
as
select
	/*** product1 VALUES ***/
	(select product1_TOT from tbl_inventoryreconcile where datediff(m,@enddate,[datetime]) = -1) as 'product1_PREV_MONTH',
	(select product1_PROD from uv_InventoryData where datediff(d,[datetime],@enddate) = 0) as 'product1_PROD',
	(select sum(product1_PROD) from uv_InventoryData where datediff(m,[datetime],@enddate) = 0 and datepart(yyyy,[datetime]) = datepart(yyyy,@enddate) and datepart(d,[datetime]) <= datepart(d,@enddate)) as 'product1_PROD_MTD',
	(select product1_CONS from uv_InventoryData where datediff(d,[datetime],@enddate) = 0) as 'product1_CONS',
	(select sum(product1_CONS) from uv_InventoryData where datediff(m,[datetime],@enddate) = 0 and datepart(yyyy,[datetime]) = datepart(yyyy,@enddate) and datepart(d,[datetime]) <= datepart(d,@enddate)) as 'product1_CONS_MTD',
	(select product1_TOT from tbl_inventoryreconcile where datediff(m,@enddate,[datetime]) = -1) + sum(product1_PROD) - sum(product1_CONS) as 'product1_CALC',
	case
		when (select product1_TOT from tbl_inventoryreconcile where datepart(mm,[datetime]) = datepart(mm,@enddate) and datepart(yyyy,[datetime]) = datepart(yyyy,@enddate)) is null then -9999
		else (select product1_TOT from tbl_inventoryreconcile where datepart(mm,[datetime]) = datepart(mm,@enddate) and datepart(yyyy,[datetime]) = datepart(yyyy,@enddate))
	end as 'product1_TOT',
	case
		when (select product1_TOT from tbl_inventoryreconcile where datepart(mm,[datetime]) = datepart(mm,@enddate) and datepart(yyyy,[datetime]) = datepart(yyyy,@enddate)) - (select product1_TOT from tbl_inventoryreconcile where datediff(m,@enddate,[datetime]) = -1) + sum(product1_PROD) - sum(product1_CONS) is null then 0
		else (select product1_TOT from tbl_inventoryreconcile where datepart(mm,[datetime]) = datepart(mm,@enddate) and datepart(yyyy,[datetime]) = datepart(yyyy,@enddate)) - (select product1_TOT from tbl_inventoryreconcile where datediff(m,@enddate,[datetime]) = -1) + sum(product1_PROD) - sum(product1_CONS)
	end as 'product1_ADJ'
	
into #table1
from
	dbo.uv_InventoryData
	where
	datepart(dd,[datetime])= datepart(dd,@enddate)
	and datepart(mm,[datetime]) = datepart(mm,@enddate)
	and datepart(yyyy,[datetime]) = datepart(yyyy,@enddate)
group by
	cast(cast(datepart(mm,[DATETIME]) as varchar) + '/1/' + cast(datepart(yyyy,[DATETIME]) as varchar) as datetime)
order by
	cast(cast(datepart(mm,[DATETIME]) as varchar) + '/1/' + cast(datepart(yyyy,[DATETIME]) as varchar) as datetime)

Open in new window

0
Comment
Question by:AlamoAutomation
1 Comment
 
LVL 39

Accepted Solution

by:
lcohan earned 250 total points
ID: 35020198
1. In the stored procedure you don't need to SELECT.....INTO #table1 if you dont' need to use that table as it's a waste.

2. This is what I use to "drop time" from a date time function and still make use of an existing index on the date column - just replace dateentered with getdate() to see the results:

select CONVERT(DATETIME,(CAST(MONTH(dateentered) AS VARCHAR(2))+'/'+CAST(DAY(dateentered) AS VARCHAR(2))+'/'+CAST(YEAR(dateentered) AS VARCHAR(4))),101)

3. I would add a WITH (NOLOCK) after each table in the FROM or JOIN to avoid locking/blocking

4. Make sure you have indexes on the WHERE and JOINed tables/columns
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now