February 21, 2011 at 4:36 am
Hi, how to retrieve the below said result using sub query.
Tables
Plan_hdr
Sys_id (PK)
Plan_det
Sys_Id (FK)
Item_ID (FK)
MDPDate
Qty
sales_hdr
Sys_id (PK)
inv_date
sales_det
Item_ID (FK)
Qty
Item_Master
Item_ID (PK)
Item_Desc
Results should be
Item_ID Day Plan Qty Sale Qty
01 01-02-2011 5000 2500
01 02-02-2011 2500 3000
02 02-02-2011 10000 9000
February 21, 2011 at 6:20 am
You're not saying where those results come from.
Take a look at the article linked in my signature line. You will see how to improve your question and get a quicker answer.
-- Gianluca Sartori
February 21, 2011 at 6:37 am
this is my query. but the result is not retrieve correctly.
SELECT IM.Item_ID, datepart(dd,SH.Inv_date), sum(SD.Qty) S.PlanQty FROM
(SELECT PD.Item_ID, datepart(dd,PD.MDPDate), SUM(PD.Qty) FROM plan_det PD) AS S
WHERE S.Item_ID = IM.Item_ID AND IM.Item_ID = IM.Item_ID
February 21, 2011 at 7:53 am
Your query can't execute, it is missing some GROUP BY clauses.
Can you post some sample data from the tables involved?
-- Gianluca Sartori
February 22, 2011 at 5:50 am
You are also using the DATEPART function to just get the day, but in your example data you want the date. If you want to strip off the time use CONVERT.
The other issue with the query is you are referencing fields from IM (I'm assuming Item Master) but you don't every actually use the table.
You are close, but you do need the GROUP BY as indicated by Gianluca, get all of your tables in the SQL and change the datepart to CONVERT.
Example of CONVERT:
CONVERT(DATETIME,CONVERT(VARCHAR(12),[DATEFIELD],101),101)
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
February 22, 2011 at 5:53 am
You also have a join using the same field on both sides of the equation (Cartesian):
IM.Item_ID = IM.Item_ID
That's gonna hurt ya.
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply