Nested Select

Question

Nested Select

aliasghar.09

SSC Rookie

Points: 46
More actions
January 9, 2015 at 1:45 am

#397853

Hi Experts,
A newbie here. So please be kind 🙂
I have 2 Tables namely Item & Item Entries. relation is Item.No = ItemEntries.No. In Item Entries Table I have Columns as Qty,Entry type,Purchase Amount,Sales Amount
I like to have a report which Shows as Below,
Item No. Opening Quantity Purcahse Amount Sales Amount
To calculate Opening Inventory I summed up the quantity field and the result is as expected. No problem in that. Now From that dataset i like to run a sub query which Calcualtes/Sum the Purchase amount for an Item that i sa part of first dataset and similarly for Sales Amount.
e.g. Select(Item No.,Sum(IE.Quantity) As Quantity, Select(......Purchase Amount),Select(....Sales Amount)
I hope i was able to clear my doubts to you guys.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Table definitions please (as CREATE TABLE statements), sample data (as INSERT statements) and expected results please.

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

aliasghar.09 SSC Rookie Points: 46 More actions · Answer 2

Hi,

I do not have any Create Table or Insert Table statements as i am reading the Data from an ERP database.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I'm not asking you to create tables. I'm asking for the definitions of the tables the data is in so that we can figure out a query for you.

We need:

- definitions of the tables that your data is in, as CREATE TABLE statements please

- some sample data to test with, as INSERT statements

- your expected results, with the sample data you provided.

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

Lempster SSCoach Points: 15651 More actions · Answer 4

aliasghar.09 (1/9/2015)
Hi Experts,
A newbie here. So please be kind 🙂
I have 2 Tables namely Item & Item Entries. relation is Item.No = ItemEntries.No. In Item Entries Table I have Columns as Qty,Entry type,Purchase Amount,Sales Amount

Ok, being kind, I assume you have Management Studio (SSMS) available to you, yes?

If so then right-click on each table (Item and Item Entries) in turn and select 'Script Table as' > 'CREATE TO' > 'Clipboard' (or New Query Window) and then paste the results into a new post on this thread. That would be a good start.

Regards

Lempster

aliasghar.09 SSC Rookie Points: 46 More actions · Answer 5

SELECT Item.No_, SUM(VE.[Item Ledger Entry Quantity]) AS OpeningQty, Item.Description, Item.[Tariff No_], SUM(VE.[Cost Amount (Actual) (ACY)]) AS OpeningInvActual,

SUM(VE.[Cost Amount (Expected) (ACY)]) AS OpeningInvExpected

FROM [Company$Item] AS Item INNER JOIN

[Company$Value Entry] AS VE ON Item.No_ = VE.[Item No_]

WHERE (VE.[Posting Date] < @StartDate)

GROUP BY Item.No_, Item.Description, Item.[Tariff No_]

In the same Set of Values, I like to Have the SUM(VE.Purchase_Amount) & SUM(VE.Sales_Amount) but in date filter @StartDate & @EndDate.

In Other Words, for My Opening Inventory, I have calculated all the Items where the transaction Date is less than my Start Date of the report(A user defined Filter) but to calculate the Purchase amount between Start Date and End Date i have issue.

I will post the table structure shortly as requested by you guys.