July 31, 2007 at 7:04 am
I have tinkered around and come up with these three disparate queries. The question is how to integrate them.
SELECT DISTINCT P.PRODUCT, ID.ITEMNO, F.NAME
INTO #DETAILS
FROM INVOICEMASTER IM
INNER JOIN INVOICEDETAILS ID
ON IM.INVNO = ID.INVNO
INNER JOIN FOODS F
ON ID.ITEMNO = F.FOODID
WHERE IM.LOC_ID = Para1
AND IM.DATE BETWEEN Para2 AND Para3
GO
SELECT ID.ITEMNO, SUM(ID.QTY) AS QTYSOLD
INTO #QTYSOLD
FROM INVOICEDETAILS ID
INNER JOIN INVOICEMASTER IM
ON IM.INVNO = ID.INVNO
GROUP BY ID.ITEMNO
GO
SELECT R.PROD_ID, (R.QTY * Q.QTYSOLD) AS QTYUSED, P.PRODUCT
INTO #QTYUSED
FROM RECIPES R
INNER JOIN PRODUCTS P
ON R.PROD_ID = P.PROD_ID
INNER JOIN #QTYSOLD Q
ON R.FOOD_ID = X.ITEMNO
GO
------------------------------------------------------------
For those who wanted sample data, I've placed some below. Please excuse me from formatting it into INSERT statements, I just find it too tedious.
------------------------------------------------------------
FOODS
FOOD_ID....DES..............TYPE......UNIT
1..........HAMBURGER........MAIN......1
2..........PIZZA............MAIN......1
3..........TEA..............BEVERAGE..2
4..........CHICKEN WINGS....STARTER...2
PRODUCTS
PROD_ID..PRODUCT....UNIT..PRICE....BALANCE
1........BUNS.......1.....2........20
2........PATTIES....1.....2.5......25
3........MILK.......6.....3........10
4........TEABAGS....1.....2........50
5........CRUSTs.....1.....2........15
6........TOPPINGs...1.....3........15
7........CH/WINGS...5.....10.......20
RECIPES
FOOD_ID....ING_ID..QTY
1..........1.......1
1..........2.......2
2..........3.......1
2..........4.......2
3..........5.......1
3..........6.......0.2
4..........7.......0.3
LOCATIONS
LOC_ID....LOCATION
1.........POOL BAR
2.........RESTAURANT
3.........NIGHTCLUB
INVOICEMASTER
INV_NO....DATE......LOC_ID
1.........010207....1
2.........010207....2
3.........010207....1
4.........020207....3
5.........020207....1
6.........020207....2
7.........030207....1
8.........030207....3
9.........030207....1
10........030207....2
INVOICEDETAILS
INV_NO....FOOD_ID..QTY
1.........1........2
1.........3........3
2.........2........1
3.........1........2
3.........2........1
4.........2........1
4.........4........3
4.........3........1
5.........3........2
5.........4........1
6.........1........4
7.........3........1
7.........2........2
UNITS
UNIT_ID....UNIT....
1..........PIECE
2..........PLATE
3..........BOWL
4..........BOTTLE
5..........KG
6..........LITRE
July 31, 2007 at 8:13 am
Hi Goodguy, please can you QC the following? I've taken a couple of liberties e.g. introduced 'CUP' as a unit for 'TEA'.
Cheers
ChrisM
DROP TABLE #FOODS
CREATE TABLE #FOODS (FOOD_ID int, DES varchar(20), TYPE varchar(15), UNIT int)
INSERT INTO #FOODS
SELECT 1, 'HAMBURGER', 'MAIN', 1 UNION ALL
SELECT 2, 'PIZZA', 'MAIN', 1 UNION ALL
SELECT 3, 'TEA', 'BEVERAGE', 6 UNION ALL
SELECT 4, 'CHICKEN WINGS', 'STARTER', 2
--SELECT * FROM #FOODS
DROP TABLE #PRODUCTS
CREATE TABLE #PRODUCTS (PROD_ID int, PRODUCT varchar(15), UNIT int, PRICE money, BALANCE int)
INSERT INTO #PRODUCTS
SELECT 1, 'BUNS', 1, 2, 20 UNION ALL
SELECT 2, 'PATTIES', 1, 2.5, 25 UNION ALL
SELECT 3, 'MILK', 6, 3, 10 UNION ALL
SELECT 4, 'TEABAGS', 1, 2, 50 UNION ALL
SELECT 5, 'CRUSTs', 1, 2, 15 UNION ALL
SELECT 6, 'TOPPINGs', 1, 3, 15 UNION ALL
SELECT 7, 'CH/WINGS', 5, 10, 20
--SELECT * FROM #PRODUCTS
DROP TABLE #RECIPES
CREATE TABLE #RECIPES (FOOD_ID int, ING_ID int, QTY numeric (5,2))
INSERT INTO #RECIPES
SELECT 1, 1, 1 UNION ALL
SELECT 1, 2, 2 UNION ALL
SELECT 2, 3, 1 UNION ALL
SELECT 2, 4, 2 UNION ALL
SELECT 3, 5, 1 UNION ALL
SELECT 3, 6, 0.2 UNION ALL
SELECT 4, 7, 0.3
--SELECT * FROM #RECIPES
DROP TABLE #LOCATIONS
CREATE TABLE #LOCATIONS (LOC_ID int, LOCATION varchar(10))
INSERT INTO #LOCATIONS
SELECT 1, 'POOL BAR' UNION ALL
SELECT 2, 'RESTAURANT' UNION ALL
SELECT 3, 'NIGHTCLUB'
--SELECT * FROM #LOCATIONS
DROP TABLE #INVOICEMASTER
CREATE TABLE #INVOICEMASTER (INV_NO int, DATE char(6), LOC_ID int)
INSERT INTO #INVOICEMASTER
SELECT 1, '010207', 1 UNION ALL
SELECT 2, '010207', 2 UNION ALL
SELECT 3, '010207', 1 UNION ALL
SELECT 4, '020207', 3 UNION ALL
SELECT 5, '020207', 1 UNION ALL
SELECT 6, '020207', 2 UNION ALL
SELECT 7, '030207', 1 UNION ALL
SELECT 8, '030207', 3 UNION ALL
SELECT 9, '030207', 1 UNION ALL
SELECT 10, '030207', 2
--SELECT * FROM #INVOICEMASTER
DROP TABLE #INVOICEDETAILS
CREATE TABLE #INVOICEDETAILS (INV_NO int, FOOD_ID int, QTY int)
INSERT INTO #INVOICEDETAILS
SELECT 1, 1, 2 UNION ALL
SELECT 1, 3, 3 UNION ALL
SELECT 2, 2, 1 UNION ALL
SELECT 3, 1, 2 UNION ALL
SELECT 3, 2, 1 UNION ALL
SELECT 4, 2, 1 UNION ALL
SELECT 4, 4, 3 UNION ALL
SELECT 4, 3, 1 UNION ALL
SELECT 5, 3, 2 UNION ALL
SELECT 5, 4, 1 UNION ALL
SELECT 6, 1, 4 UNION ALL
SELECT 7, 3, 1 UNION ALL
SELECT 7, 2, 2
--SELECT * FROM #INVOICEDETAILS
DROP TABLE #UNITS
CREATE TABLE #UNITS (UNIT_ID int, UNIT varchar(6))
INSERT INTO #UNITS
SELECT 1, 'PIECE' UNION ALL
SELECT 2, 'PLATE' UNION ALL
SELECT 3, 'BOWL' UNION ALL
SELECT 4, 'BOTTLE' UNION ALL
SELECT 5, 'KG' UNION ALL
SELECT 6, 'CUP' UNION ALL
SELECT 7, 'LITRE'
--SELECT * FROM #UNITS
--------------------------------------------------------------------
-- For quantity of food items invoiced / date / location
SELECT im.DATE, ie.FOOD_ID, f.DES, u.UNIT, l.LOCATION, SUM(ie.QTY) AS SUMQTY, COUNT(*) AS INVOICELINES
FROM #INVOICEMASTER im
INNER JOIN #INVOICEDETAILS ie ON ie.INV_NO = im.INV_NO
INNER JOIN #LOCATIONS l ON l.LOC_ID = im.LOC_ID
INNER JOIN #FOODS f ON f.FOOD_ID = ie.FOOD_ID
INNER JOIN #UNITS u ON u.UNIT_ID = f.UNIT
GROUP BY im.DATE, ie.FOOD_ID, f.DES, u.UNIT, l.LOCATION
-- For food item breakdown...
SELECT f.DES, r.QTY AS RECIPEQTY, p.PRODUCT, u.UNIT AS PRODUCTUNIT
FROM #FOODS f
INNER JOIN #RECIPES r ON r.FOOD_ID = f.FOOD_ID
INNER JOIN #PRODUCTS p ON p.PROD_ID = r.ING_ID
INNER JOIN #UNITS u ON u.UNIT_ID = p.UNIT
WHERE r.FOOD_ID = 1 -- 'HAMBURGER'
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 8:49 am
Thanks, Chris, for taking the effort. Muchos appreciados. And your liberties are welcome, no use being rigid with data values, is there?
I checked your last two queries, and I see you too have been forced to include all non-aggregate columns in GROUP BY in the first one. The grouping is to be only by FOOD_ID. INVOICE.DATE is to be checked in WHERE clause, not output.
In teh second one, we are supposed to extract the multiple of RECIPES.QTY and INVOICE.QTY to get us the stock usage.
Hope I've made sense.
-- For quantity of food items invoiced / date / location
SELECT im.DATE, ie.FOOD_ID, f.DES, u.UNIT, l.LOCATION, SUM(ie.QTY) AS SUMQTY, COUNT(*) AS INVOICELINES
FROM #INVOICEMASTER im
INNER JOIN #INVOICEDETAILS ie ON ie.INV_NO = im.INV_NO
INNER JOIN #LOCATIONS l ON l.LOC_ID = im.LOC_ID
INNER JOIN #FOODS f ON f.FOOD_ID = ie.FOOD_ID
INNER JOIN #UNITS u ON u.UNIT_ID = f.UNIT
GROUP BY im.DATE, ie.FOOD_ID, f.DES, u.UNIT, l.LOCATION
-- For food item breakdown...
SELECT f.DES, r.QTY AS RECIPEQTY, p.PRODUCT, u.UNIT AS PRODUCTUNIT
FROM #FOODS f
INNER JOIN #RECIPES r ON r.FOOD_ID = f.FOOD_ID
INNER JOIN #PRODUCTS p ON p.PROD_ID = r.ING_ID
INNER JOIN #UNITS u ON u.UNIT_ID = p.UNIT
WHERE r.FOOD_ID = 1 -- 'HAMBURGER'
July 31, 2007 at 9:11 am
Hi Goodguy, of course, you're making perfect sense, thank you for the excellent explanation.
Check out this query, have a look at the result set, I'm sure you will find it interesting (and hopefully helpful too).
Cheers
ChrisM
SELECT t1.*, t2.* FROM ( -- For quantity of food items invoiced / date / location SELECT im.DATE, ie.FOOD_ID, f.DES, u.UNIT, l.LOCATION, SUM(ie.QTY) AS SUMQTY, COUNT(*) AS INVOICELINES FROM #INVOICEMASTER im INNER JOIN #INVOICEDETAILS ie ON ie.INV_NO = im.INV_NO INNER JOIN #LOCATIONS l ON l.LOC_ID = im.LOC_ID INNER JOIN #FOODS f ON f.FOOD_ID = ie.FOOD_ID INNER JOIN #UNITS u ON u.UNIT_ID = f.UNIT GROUP BY im.DATE, ie.FOOD_ID, f.DES, u.UNIT, l.LOCATION ) t1 INNER JOIN ( -- For food item breakdown... SELECT f.FOOD_ID, f.DES, r.QTY AS RECIPEQTY, p.PRODUCT, u.UNIT AS PRODUCTUNIT FROM #FOODS f INNER JOIN #RECIPES r ON r.FOOD_ID = f.FOOD_ID INNER JOIN #PRODUCTS p ON p.PROD_ID = r.ING_ID INNER JOIN #UNITS u ON u.UNIT_ID = p.UNIT --WHERE r.FOOD_ID = 1 -- 'HAMBURGER' ) t2 ON t2.FOOD_ID = t1.FOOD_ID WHERE t1.DATE = t1.DATE AND t1.LOCATION = t1.LOCATION
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
August 1, 2007 at 4:16 am
Hi, all, and thanks for all your help.
Chris, thanks again. I tried your query, the end result is a detailed breakdown of all the columns once again. I played around with the code and came up with this sproc:
------------------------------------------------------------------------
CREATE PROCEDURE FOODSTOCKS @FROM DATETIME, @TODATE DATETIME, AS
DECLARE @SALES TABLE (FDESC VARCHAR(50), FQTY INT)
INSERT into @SALES (FDESC, FQTY)
SELECT ID.DESCRIPTION, SUM(ID.QUANTITY)
FROM INVOICEDETAILS ID
INNER JOIN INVOICEMASTER IM
ON ID.INVNO = IM.INVNO
WHERE (IM.TYPENAME = 'BAR'
OR IM.TYPENAME = 'RESTAURANT') -- Filter only Food Sales
AND IM.LOC_ID = PARA1 -- Filter by Location Optionally
AND IM.DATE BETWEEN @FROMDATE AND @TODATE
GROUP BY ID.DESCRIPTION
DECLARE @INGREDS TABLE (FDES VARCHAR(40), FQTY INT, FID INT, ING INT,
PROD VARCHAR(40) NULL, RQTY NUMERIC(5,2), QTY NUMERIC (6,2))
INSERT INTO @INGREDS (FDES, FQTY, FID, ING, PROD, RQTY, QTY)
SELECT F.FDESC, F.FQTY, F1.FID, R.ING_NO,
P.PROD_DESC, R.QTY, (F.FQTY * R.QTY)
FROM @SALES F
INNER JOIN FOODS F1
ON F.FDESC = F1.SDES
INNER JOIN RECIPES R
ON F1.FID = R.FID
INNER JOIN PRODUCTS P
ON R.ING_NO = P.PROD_ID
ORDER BY P.PROD_ID
DECLARE @PRODVOLS TABLE (PROD VARCHAR(40), QTY NUMERIC(6,2))
INSERT INTO @PRODVOLS(PROD, QTY)
SELECT PROD, SUM(QTY) FROM @INGREDS GROUP BY PROD
SELECT PROD, QTY FROM #PRODVOLS
-------------------------------------------------
However, as you can see, I have an uphill task to retrieve related records for Food Items Sold (compares with the @SALES Table above) and the respective Units (for both Food Items and Stock Items) and display them on the same report.
I would also like to know if I add a @LOC_ID parameter to the above sproc, how can I make it optional and then tell if it was not passed and how to omit the filter.
All help is welcome.
And if there any Crystal wizards among the members, I'd like help in organising disparate recordsets into one report (how to exploit subreporting capability).
August 1, 2007 at 5:35 am
Hi Goodguy
It's an uphill task because the sproc doesn't fit the sample data or vice versa. Please can you amend one or the other to fit your requirements? I suspect it will be the data...
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
August 1, 2007 at 6:09 am
No, I am afraid it's neither - it's just the SQL requirements which make life tuff. Nonetheless, I managed to modify my last SELECT statement as below:
SELECT V.PROD, V.QTY, U.UNIT
FROM #PRODVOLS V
INNER JOIN PRODUCTS P
ON V.PROD = P.PRODUCT
INNER JOIN UNITS U
ON P.UNIT_ID = U.UNIT_ID
GROUP BY V.PROD, U.UNIT
to include the UNIT alongside each PRODUCT.
My SQL jobs are now (almost) done, I now remain with these tasks:
-- passing an optional LOCATION parameter to the sproc (and skipping the filter if it is not passed)
-- presenting the SALES data (in FOODS terms) for the same date-range (which I can obtain in SQL) in one Crystal Report with the above STOCKS data. (I know this is not relevant here, but who knows - I just might find an expert in CR here).
Thanks a tonne.
August 1, 2007 at 7:00 am
Cool, you're well on the way.
You can use ISNULL in the WHERE clause as follows:
CREATE TABLE #LOCATIONS (LOC_ID int, LOCATION varchar(10))
INSERT INTO #LOCATIONS
SELECT 1, 'POOL BAR' UNION ALL
SELECT 2, 'RESTAURANT' UNION ALL
SELECT 3, 'NIGHTCLUB'
DECLARE @LOCATION VARCHAR(10)
SET @LOCATION = 'POOL BAR' -- or null
SELECT * FROM #LOCATIONS l
WHERE l.LOCATION = ISNULL(@LOCATION, l.LOCATION)
Subreports in Crystal, using different data sources, are straightforward - insert the subreport then set the datasource for it. The report I'm looking at now has six subreports (in report footer a...report footer f) each presenting detail from a different view in the database. There's no detail band for the main report, all of the detail bands are in subreports. The hardest part of setting this up was managing the layout within each subreport, and placing the subreports consistently within the main report, so that borders and columns are consistent.
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
August 1, 2007 at 7:45 am
Yes, Chris, have made much progress, thank you very much.
ISNULL won't help me because I want to bypass the filter if the argument is not passed in, as below:
INSERT INTO @SALES (FDESC, FQTY)
SELECT ID.DESCRIPTION, SUM(ID.QUANTITY)
FROM INVOICEDETAILS ID
INNER JOIN INVOICEMASTER IM
ON ID.TAXINV_NO = IM.TAXINV_NO
WHERE (IM.TYPENAME = 'BAR'
OR IM.TYPENAME = 'RESTAURANT')
AND IM.TAX_DATE BETWEEN @FDT AND @tdt
-- I want to be able to omit the filter for LOCATION here
-- if @LOCID = 0, like we do in VB
-- eg IF @LOCID > 0 THEN 'AND IM.LOCID = @LOCID'
GROUP BY ID.DESCRIPTION
How is it possible?
As for subreports, I do use them but once CR fails to establish any direct relationship between the two, it clams up.
August 1, 2007 at 7:59 am
If the default value for @LOCID will be non-null, then you can do essentially the same substitution with a CASE instead:
WHERE IM.LOCID = CASE WHEN @LOCID > 0 THEN @LOCID ELSE IM.LOCID END
I haven't found the same problem with Crystal but could be different versions, ours is 11.0.0.2269. The six subreports in this month-end accounts report are all fed by different views in the same db, and of course they have different columns returned, between 4 and 12.
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
August 1, 2007 at 8:30 am
Chris, I think I have not been very lucid, sorry:
What I want is:
WHERE (IM.TYPENAME = 'BAR'
OR IM.TYPENAME = 'RESTAURANT')
AND IM.TAX_DATE BETWEEN @FDT AND @tdt
-- I want to be able to omit the filter for LOCATION here
-- if @LOCID = 0, like we do in VB
-- eg IF @LOCID > 0 THEN
-- Then the WHERE clause is extended here
AND IM.LOCID = @LOCID
-- else it is skipped directly to
GROUP BY ID.DESCRIPTION
i.e. There shouldbe NO FILTER when argument is missing.
Thanks and hope I've made more sense.
August 1, 2007 at 8:51 am
Hey Goodguy, I'm not explaining this well, sorry.
WHERE ...
AND IM.LOCID = CASE WHEN @LOCID > 0 THEN @LOCID ELSE IM.LOCID END
This means "if @LOCID is greater than zero then use it as a filter, otherwise don't filter". So if @LOCID isn't greater than zero, the expression equates to WHERE IM.LOCID = IM.LOCID.
WHERE ...
AND IM.LOCID = ISNULL(@LOCID, IM.LOCID)
This means "if @LOCID is non-null then use it as a filter, otherwise don't filter"
Try it and you will see.
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
August 1, 2007 at 11:53 pm
August 2, 2007 at 5:03 am
No worries mate, good luck.
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 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply