July 30, 2007 at 5:59 am
Hi, all, am back asking questions:
I have the following 8 tables:
CREATE TABLE [dbo].[FOODS] (
[FOOD_ID] [numeric](10, 0) NOT NULL ,
[DES] [varchar] (50)
[TYPE] [int] NOT NULL ,
[UNIT] [numeric](10, 0) NULL ,
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[RECIPES] (
[FID] [numeric](10, 0) NULL ,
[ING_ID] [numeric](18, 0) NULL ,
[QTY] [numeric](15, 3) NULL ,
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PRODUCTS] (
[PROD_ID] [smallint] IDENTITY (1, 1) NOT NULL ,
[PRODUCT] [varchar] (200)
[UNIT] [int] NULL ,
[PRICE] [numeric](18, 0) NULL ,
[BALANCE] [int] NULL ,
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PRODLOC] (
[PROD_ID] [smallint] NULL ,
[LOC_ID] [numeric](8, 0) NULL ,
[QTY] [numeric](8, 2) NULL ,
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[LOCATION] (
[LOC_ID] [numeric](8, 0) IDENTITY (1, 1) NOT NULL ,
[LOCATION] [varchar] (50)
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[INVOICEMASTER] (
[INV_NO] [numeric](18, 0) NOT NULL ,
[DATE] [datetime] NOT NULL ,
[LOC_ID] [int] NOT NULL ,
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[INVOICEDETAILS] (
[INV_NO] [decimal](18, 0) NOT NULL ,
[FOOD_ID] [smallint] NOT NULL ,
[QTY] [numeric](18, 0) NOT NULL ,
[RATE] [money] NOT NULL ,
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[UNIT] (
[UNIT_ID] [tinyint] IDENTITY (1, 1) NOT NULL ,
[UNIT] [varchar] (20)
) ON [PRIMARY]
GO
I want to make a view on these tables to compile a report between two dates showing total consumption of each PRODUCTS.PRODUCT (in UNITS.UNIT) based on the sales volume of each FOODS.FOOD_ID (to be obtained from INVOICEDETAILS.FOODID and RECIPES.PROD_ID ) in UNITS.UNITS (from PRODUCTS.UNIT_ID) for each LOCATIONS.LOC_ID (to be obtained from INVOICEMASTER.LOC_ID).
I need the output to show both detail consumption of each PROD_ID (GROUPed BY FOOD_ID) and summary totals.
No matter how I try to construct my view, I am getting frustrated by SQL Server rejecting my efforts by insisting that I can only group by columns which have summary functions. That means I cannot use SUM(RECIPES.QTY * INVOICEDETAILS.QTY).
I know no SQL problem is insurmountable, and I am sure many must have faced and solved this problem. I hope to get some help too.
July 30, 2007 at 6:50 am
Must you package your report in a view?
You could perhaps write a procedure that produced the output you want.
A proc would give you some more flexibility when adding/summing/grouping etc.....
/Kenneth
July 30, 2007 at 7:00 am
Can you please post some Insert Scripts for reference data in defined tables, then it will be easy to give generate a VIEW.
July 30, 2007 at 7:50 am
July 30, 2007 at 7:59 am
Hi Goodguy
Can you post the query you already have, to show the joins?
I'm sure this is do-able. You will almost certainly find it easier by packaging your tables into two derived tables, e.g. invoicemaster, invoicedetails and location, something like this...
...
INNER JOIN (
SELECT im.DATE, ie.FOOD_ID, ie.QTY, l.LOCATION
FROM INVOICEMASTER im
INNER JOIN INVOICEDETAILS ie ON ie.INV_NO = im.INV_NO
INNER JOIN LOCATION l ON l.LOC_ID = im.LOC_ID) t ON t.FOOD_ID = ...
- which gives you sales volume per product by date and location, preserving the correct rowcount per FOOD_ID per QTY.
Then you could join this to another derived table consisting of the build for each recipe, using FOOD_ID as the JOIN - this is where I need your code to see the joins.
I guess the gotcha here would be this: the rowcount of the derived table shown above is critical for calculating out invoice QTY correctly, and the rowcount of the recipe build section is also critical for calculating the quantity of product going into the recipe - but the rowcounts are different. This is where the derived tables come in, allowing you to introduce all of the tables you need but giving you full control over aggregation. You can do the grouping inside or outside the derived tables - or both.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 30, 2007 at 11:25 pm
To Chris:
This is an extract of the view I am using:
SELECT FOODS.Des, LOCATIONS.Location, RECIPES.Qty As Qty1, INVOICEDETAILS.Qty As Qty2, PRODUCTS.Product, UNITS.Unit
FROM InvoiceMaster INNER JOIN InvoiceDetails
ON InvoiceMaster.Inv_No = InvoiceDetails.Inv_No
INNER JOIN Foods
ON INVOICEDETAILS.ItemNo = Foods.FOOD_ID AND
INNER JOIN RECIPES
ON Foods.Food_ID = RECIPES.Food_ID
INNER JOIN PRODUCTS
ON RECIPES.Ing_No = PRODUCTS .Prod_ID
INNER JOIN UNITS
ON PRODUCTS .Prod_unit = UNITS.Unit_ID
AND FOODS.Unit_ID = UNITS.Unit_ID
INNER JOIN PRODLOC ON
PRODUCTS .Prod_ID = PRODLOC.Prod_ID
INNER JOIN Location ON
PRODLOC.Loc_ID = LOCATIONS.Loc_ID
AND INVOICEMASTER.LOC_ID = LOCATIONS.LOC_ID
WHEREINVOICEMASTER.Date BETWEEN Parameter1 AND Parameter2
--Optional for filtering for a particular POS.
AND INVOICEMASTER.Loc_ID = Parameter3
It is bound to get more complicated once i start implementing advanced filtering functionality etc, but for now i want to get the basic hang of it.
To Bledu:
Crystal Reports 8.
To VishalGupta:
I posted the DDL so as to give members a picture of the structure fromw which it would be easy for an SQL expert to design the query/sproc/view to yield the desired results. I felt that there was no need for INSERT scripts (which I have posted in my other posts here) and to be honest it would just be a big PITA.
To Kenneth:
I am equally amenable to a query or a stored proc. I just need to get a handle on the query structure first.
Thanks to each one of you. I await your replies.
July 31, 2007 at 1:22 am
The reason I suggested a proc, is that a proc lets you divide the logic into smaller 'chunks' if need be.
With a view you have to do it all in 'one go'. Depending on how complex the expression gets, it may be easier to start out to 'piece up' the work you need done, then put it together piece by piece.
When you have sorted out the structure, you'll probably see which format would suit best, a view or a proc.
(maybe even a function, since that is also a 'way of packaging code')
/Kenneth
July 31, 2007 at 1:55 am
As I said I am equally amenable to using an sproc for this case. Although I only discovered their power recently (AFTER joining this site!), I regard stored procedures very highly for efficiency.
All I am looking for now is how to build my 'chunks'.
July 31, 2007 at 2:33 am
Hi Goodguy, apologies for the delay, I'm on UK time - for reference this is posted at 09:35.
The previous post gave you the invoice side of things, now we need to look at the food item build. I reckon the code below is close but there's a question: you've joined table UNITS once to both FOODS and PRODUCTS. I think you will probably need to join UNITS to FOODS as say u1, and also join UNITS to PRODUCTS as say u2, because the units for the food and for the ingredient are unlikely to be the same.
Here's the query, have a play and see how it matches up with your data. Change the SELECT * so that the query returns just the data that you are interested in, including Food_ID.
SELECT * FROM Foods f INNER JOIN RECIPES r ON f.Food_ID = r.Food_ID INNER JOIN PRODUCTS p ON r.Ing_No = p .Prod_ID INNER JOIN UNITS u ON p.Prod_unit = u.Unit_ID
AND f.Unit_ID = u.Unit_ID -- product has units and food has units? -- Might need to join twice: a 'litre' of soup containing a 'head' of garlic
INNER JOIN PRODLOC pl ON pl.Prod_ID = p.Prod_ID INNER JOIN Location lo ON lo.Loc_ID = pl.Loc_ID
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 31, 2007 at 3:25 am
Chris, very kind of you but since I am the one in need here, you or other members are under no obligation to apologise for any delays.
I've looked at your SQL and I appreciate your effort but pls understand that my problem comes when trying to extract aggregate values for SUM(INVOICEDETAILS.QUANTITY) GROUP BY INVOICEDETAILS.ITEMNO) and then multiplying each RECIPES.QUANTITY joined on the RECIPES.PROD_ID.
I need to compile a report which lists all the total quantities of each Food Item sold in the date range (and optionally for the given Location) and give a breakdown of the total quantities of each product that went into preparing each of these foods.
The summary total of product quantities for the date range shall be viewed against another subreport (which I can handle) that shows all the products purchased for the date range and any exceptions will be highlighted here.
It is the aggregate functions that are frustrating me where I need help.
Hope I make sense.
July 31, 2007 at 3:32 am
Hi Good guy, I understand the nature of the problem - please bear with me - we should be able to knock this one on the head very shortly. To recap, this is what I'm trying to do...
<<I need to compile a report which lists all the total quantities of each Food Item sold in the date range (and optionally for the given Location) and give a breakdown of the total quantities of each product that went into preparing each of these foods.>>
If that's ok with you, then I'll hang fire until you've had an opportunity to look at the code I posted earlier. Feel free to switch to personal mail to save on forum space, we can always post the finished code when it's done.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 31, 2007 at 3:49 am
July 31, 2007 at 4:18 am
>If that's ok with you, then I'll hang fire until you've had an opportunity to look at the code I posted earlier. >Feel free to switch to personal mail to save on forum space, we can always post the finished code when it's done.
Please don't.
The whole purpose of a forum is to keep an open discussion.
If you later only show the end station, but keep the journey getting there hidden, there's more lost than gained in that your experiences won't be shared, and you'll also miss out on input from others...
Could you post a few rows of sample data for your DDL that demonstrates the problem you're facing? It would help since it'd save some time and make it more likely that we may come with suggestions that would be accurate to your data.
/Kenneth
July 31, 2007 at 5:21 am
July 31, 2007 at 5:23 am
Thanks for the words of advice Kenneth, I'm a newbie here and still learning the ropes. I'll bear in mind what you've said.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply