• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

Multiple subquery "select as" in SQL2005

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
AlamoAutomation
Asked:
AlamoAutomation
1 Solution
 
lcohanDatabase AnalystCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now