SQL Querry Help Grouping
SQL Querry Help Grouping
I have a table called
ICITEM and a table called ICILOC.
ICITEM: Is a Product item setup table that contains The Item Number, Description , ItemCategory Ect ect.....
ICILOC: Is the table that holds the stock info QTY On hand, QTY Shipped ect ect.
So im linking ICITEM and ICILOC on the Unique [ITEMNUMBER] field with an inner join.
When i return the resaults i get the following.
ItemNUMBER LOCATION QTYONHAND
ITEM001 GOOD 1
ITEM001 EXPORT 2
ITEM002 GOOD 1
So the item number is repeated in the ICILOC rows cus each location wil generate a line.
So i would like a querry that will group by item and sum by item.
So it gives me a result of.
ITEMNUMBER QTY ON HAND
ITEM001 3
ITEM002 1.
Is this possible ?
ICITEM and a table called ICILOC.
ICITEM: Is a Product item setup table that contains The Item Number, Description , ItemCategory Ect ect.....
ICILOC: Is the table that holds the stock info QTY On hand, QTY Shipped ect ect.
So im linking ICITEM and ICILOC on the Unique [ITEMNUMBER] field with an inner join.
When i return the resaults i get the following.
ItemNUMBER LOCATION QTYONHAND
ITEM001 GOOD 1
ITEM001 EXPORT 2
ITEM002 GOOD 1
So the item number is repeated in the ICILOC rows cus each location wil generate a line.
So i would like a querry that will group by item and sum by item.
So it gives me a result of.
ITEMNUMBER QTY ON HAND
ITEM001 3
ITEM002 1.
Is this possible ?
- rustypup
- Registered User
- Posts: 8872
- Joined: 13 Dec 2004, 02:00
- Location: nullus pixius demonica
- Contact:
Re: SQL Querry Help Grouping
yes
Code: Select all
SELECT
ICITEM.ITEMNMBR,
ICITEM.CATEGORY,
SUM(ICILOC.QTYOnHand) AS ONHAND,
SUM(ICILOC.QTYShipped) AS SHIPPED
FROM
MyCat.dbo.ICITEM LEFT JOIN
MyCat.dbo.ICILOC ON
ICILOC.ITEMNMBR=ICITEM.ITEMNMBR
WHERE
<somefilter>
GROUP BY
ICITEM.ITEMNMBR,
ICITEM.CATEGORY
ORDER BY
ICITEM.CATEGORY,
ICITEM.ITEMNMBR
Most people would sooner die than think; in fact, they do so - Bertrand Russel
Re: SQL Querry Help Grouping
Thanks.
i got it to work with your help
i got it to work with your help
Last edited by SBSP on 06 Jul 2009, 14:30, edited 1 time in total.
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: SQL Querry Help Grouping
You won't get it more efficient as well. That is a very good solution there from old rustypup. You might just want to add a nolock in there somewhere if you are only going to read data and don't want to lock the table
Code: Select all
SELECT
ICITEM.ITEMNMBR,
ICITEM.CATEGORY,
SUM(ICILOC.QTYOnHand) AS ONHAND,
SUM(ICILOC.QTYShipped) AS SHIPPED
FROM
MyCat.dbo.ICITEM WITH (NOLOCK) LEFT JOIN
MyCat.dbo.ICILOC WITH (NOLOCK) ON
ICILOC.ITEMNMBR=ICITEM.ITEMNMBR
WHERE
<somefilter>
GROUP BY
ICITEM.ITEMNMBR,
ICITEM.CATEGORY
ORDER BY
ICITEM.CATEGORY,
ICITEM.ITEMNMBR
- Spoiler (show)
- rustypup
- Registered User
- Posts: 8872
- Joined: 13 Dec 2004, 02:00
- Location: nullus pixius demonica
- Contact:
Re: SQL Querry Help Grouping
excellent... i accept payment in whiskey...
@RuadRauFlessa: actually, the one niggle i'd have is where the sum results are returning nulls... but case statements add so much sodding overhead in MS SQL...
@RuadRauFlessa: actually, the one niggle i'd have is where the sum results are returning nulls... but case statements add so much sodding overhead in MS SQL...
Most people would sooner die than think; in fact, they do so - Bertrand Russel
Re: SQL Querry Help Grouping
Thanks , cus latley we have loads of database locks dont wnat to be adding more.
the reason for query is.
I have to build a report for someone that does forecasting on an item database of 27K items.
the reason for query is.
I have to build a report for someone that does forecasting on an item database of 27K items.
Last edited by SBSP on 06 Jul 2009, 14:50, edited 3 times in total.
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: SQL Querry Help Grouping
well he could userustypup wrote:excellent... i accept payment in whiskey...
@RuadRauFlessa: actually, the one niggle i'd have is where the sum results are returning nulls... but case statements add so much sodding overhead in MS SQL...
coalesce to return a default value for when the sum evaluates to a null. The only time he would get a null back from that is when there is nothing to sum. so
Code: Select all
SELECT
ICITEM.ITEMNMBR,
ICITEM.CATEGORY,
COALESCE(SUM(ICILOC.QTYOnHand),0) AS ONHAND,
COALESCE(SUM(ICILOC.QTYShipped),0) AS SHIPPED
FROM
MyCat.dbo.ICITEM WITH (NOLOCK) LEFT JOIN
MyCat.dbo.ICILOC WITH (NOLOCK) ON
ICILOC.ITEMNMBR=ICITEM.ITEMNMBR
WHERE
<somefilter>
GROUP BY
ICITEM.ITEMNMBR,
ICITEM.CATEGORY
ORDER BY
ICITEM.CATEGORY,
ICITEM.ITEMNMBR
- Spoiler (show)
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: SQL Querry Help Grouping
Only problem with nolock is that it will return uncomitted results if the record is locked for writing. It will return the value before the lock started.SBSP wrote:Thanks , cus latley we have loads of database locks dont wnat to be adding more.
The other option you have is to use WITH (READOVER). Not sure if it is correct but I have read about the two options. The readover option skipps something if it is locked for writing.
- Spoiler (show)
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: SQL Querry Help Grouping
- Spoiler (show)
Re: SQL Querry Help Grouping
The guy needs to see
[Item number] [Description] [QTY Ordered] [Qty Backorderd] and [QTY Sold] in between a period.
By reading the line is seems simple. but its actually not possible, you cant create such a report in Crystal Reports.
The problem is the guy needs to see sales on all items whether it was sold or not.
So if the item was not sold in the specified date range then it wont show the item number cus there is no such transaction.
It comes down to the linking and no matter what join type you select when linking tables it doesnt show accurate data.
and the other problem is with the ICILOC and ICITEM table.
The iciloc table causes crystal reports to duplicate items because of the way the items are stored. (Item,Location,Qty)
So i'm trying to figure out a way to do this.
So i'm using VB to dump the data into a listview box then with another query get the Qty sold in between a date range. into a different listbox
Then problematically scan the first lisviewbox and second listviewbox for items and fill where they match and if not found enter a 0 figure. once don export to CSV.
Ive tried this before a while back then gave ,now i got a consultant to do this and the guy is also not getting it right in
crystal.
His words to me and the guy who the report is for was "Give me 30mins and i will have it ready, the guy who the report is for
Looked at me strange and said," maybe you should look at taking some lessons from this Guy."
I just said "We'll see 30mins" its now more like 3 days!
[Item number] [Description] [QTY Ordered] [Qty Backorderd] and [QTY Sold] in between a period.
By reading the line is seems simple. but its actually not possible, you cant create such a report in Crystal Reports.
The problem is the guy needs to see sales on all items whether it was sold or not.
So if the item was not sold in the specified date range then it wont show the item number cus there is no such transaction.
It comes down to the linking and no matter what join type you select when linking tables it doesnt show accurate data.
and the other problem is with the ICILOC and ICITEM table.
The iciloc table causes crystal reports to duplicate items because of the way the items are stored. (Item,Location,Qty)
So i'm trying to figure out a way to do this.
So i'm using VB to dump the data into a listview box then with another query get the Qty sold in between a date range. into a different listbox
Then problematically scan the first lisviewbox and second listviewbox for items and fill where they match and if not found enter a 0 figure. once don export to CSV.
Ive tried this before a while back then gave ,now i got a consultant to do this and the guy is also not getting it right in
crystal.
His words to me and the guy who the report is for was "Give me 30mins and i will have it ready, the guy who the report is for
Looked at me strange and said," maybe you should look at taking some lessons from this Guy."
I just said "We'll see 30mins" its now more like 3 days!
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: SQL Querry Help Grouping
Wahahahaha.... Easy problem to solve
say you have
the you should start with your main object or item which in this case is the item in ICITEM.
Then add what you need. note that for your problem you don't need a join
I think you can figure out the rest as from here it should take about 5 to 10 minutes to adapt it to your tables
say you have
Code: Select all
ICITEM
{
ITEMNMBR int,
CATEGORY varchar(50)
}
ICILOC
{
ID int,
ITEMNMBR int,
LOCATION varcher(50),
QTY int,
WHEN DATETIME
}
Code: Select all
SELECT
ITEMNMBR,
CATEGORY
FROM
ICITEM WITH (NOLOCK)
Code: Select all
SELECT
ITEMNMBR,
CATEGORY,
COALESCE(SELECT SUM(ICILOC.QTY) FROM ICILOC WITH (NOLOCK) WHERE ICILOC.ITEMNMBR = ICITEM.ITEMNMBR AND ICILOC.WHEN BETWEEN '2009-01-01' and '2009-01-31')
FROM
ICITEM WITH (NOLOCK)
- Spoiler (show)
Re: SQL Querry Help Grouping
Yes but i must join the ICILIC to ICITEM cus ICITEM contains the description field
and ICITEM dont contain the QTYonhand field
and ICITEM dont contain the QTYonhand field
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: SQL Querry Help Grouping
No need for a joinSBSP wrote:Yes but i must join the ICILIC to ICITEM cus ICITEM contains the description field
and ICITEM dont contain the QTYonhand field
The above will actually iliminate the need for a join. Inner Queries FTW. They work.
Just try running that and see what you get. Or else post the full create script of both tables and I will write the complete query for you.
- Spoiler (show)
- rustypup
- Registered User
- Posts: 8872
- Joined: 13 Dec 2004, 02:00
- Location: nullus pixius demonica
- Contact:
Re: SQL Querry Help Grouping
CR does a darn fine job of grouping....SBSP wrote:The iciloc table causes crystal reports to duplicate items because of the way the items are stored. (Item,Location,Qty)
by using the item master table, ICITEM, we'll assume that the ITEMNMBR is not going to repeat... so the dupe issue is solved on that score..
and, just for the hell of it:
Code: Select all
SELECT
ICITEM.ITEMNMBR,
ICITEM.DESCRIPTION,
ICITEM.CATEGORY,
SUM(ICILOC.QTYOnHand) AS ONHAND,
SUM(SFACTL.QTYSold) AS SOLD
FROM
((MyCat.dbo.ICITEM LEFT JOIN
MyCat.dbo.ICILOC ON
ICILOC.ITEMNMBR=ICITEM.ITEMNMBR) LEFT JOIN
MyCat.dbo.SALESFACTLINE SFACTL WITH(NOLOCK) ON
ICITEM.ITEMNMBR=SFACTLINE.ITEMNMBR) LEFT JOIN
MyCat.dbo.SALESFACTHEADER SFACTH WITH(NOCLOCK) ON
(SALESFACTHEADER.TYPE='INV') AND
(SALESFACTHEADER.VOID=0) AND
(SALESFACTHEADER.DOCNUMBR=SFACTLINE.DOCNUMBR)
WHERE
YEAR(SALESFACTHEADER.DOCDATE) = <someyear> AND
MONTH(SALESFACTHEADER.DOCDATE) = <someperiod> AND
ICITEM.CATEGORY NOT IN ('NONSTOCK','HAIRYAPES','USEDTEABAG')
GROUP BY
ICITEM.ITEMNMBR,
ICITEM.DESCRIPTION,
ICITEM.CATEGORY
ORDER BY
ICITEM.CATEGORY,
ICITEM.ITEMNMBR
Most people would sooner die than think; in fact, they do so - Bertrand Russel
Re: SQL Querry Help Grouping
Rustypup when Cr does a "Crystal report group" its more like a SQL Order by.
Once you have the Stock on hand report going and linking on ICILOC joined to ICITEM (Wil try what RuadRauFlessa said)
You have to sum the resaults in the group header. When when you then hide the the details section it will appear like a SQL group by.
I dont care much about the lock with regards to not updated items, anyway Once you post and order and everything ships
IC dont get updated anyway only when you run a day end it gets updated we run about 6 dayends a day
So those items will not be far out also this is mostly done at month end when the fiscal period is still locked.
Once you have the Stock on hand report going and linking on ICILOC joined to ICITEM (Wil try what RuadRauFlessa said)
You have to sum the resaults in the group header. When when you then hide the the details section it will appear like a SQL group by.
I dont care much about the lock with regards to not updated items, anyway Once you post and order and everything ships
IC dont get updated anyway only when you run a day end it gets updated we run about 6 dayends a day
So those items will not be far out also this is mostly done at month end when the fiscal period is still locked.
- rustypup
- Registered User
- Posts: 8872
- Joined: 13 Dec 2004, 02:00
- Location: nullus pixius demonica
- Contact:
Re: SQL Querry Help Grouping
are you suppressing the group's line detail?...SBSP wrote:Rustypup when Cr does a "Crystal report group" its more like a SQL Order by.
Most people would sooner die than think; in fact, they do so - Bertrand Russel
Re: SQL Querry Help Grouping
Not suppressed but hidden (in CR language)rustypup wrote:are you suppressing the group's line detail?...SBSP wrote:Rustypup when Cr does a "Crystal report group" its more like a SQL Order by.
I dont have to but thats the only way i know of to get the totals in line with the item number to be exported to excel
Re: SQL Querry Help Grouping
can i not rather create 2 views one that gets on hand and the other one that get on BO then
incrystal link both to ICITEM then put in a sub report for sales ?
incrystal link both to ICITEM then put in a sub report for sales ?
- rustypup
- Registered User
- Posts: 8872
- Joined: 13 Dec 2004, 02:00
- Location: nullus pixius demonica
- Contact:
Re: SQL Querry Help Grouping
ahh... :lightdawns:...SBSP wrote:only way i know of to get the totals in line with the item number to be exported to excel
are you running MS SQL?... if so, have you glanced at reporting services yet?
and how would you go about declaring the view date bounds? (i suppose you could do this using GETDATE()....)SBSP wrote:can i not rather create 2 views one that gets on hand and the other one that get on BO
if you can pull the BO numbers into the same query, why not?... or is the BO inclusion proving tricky?
Most people would sooner die than think; in fact, they do so - Bertrand Russel
Re: SQL Querry Help Grouping
I'm unsure how to approach this so apologies if i'm was being unclear.
Yes its SQL2005 i'm not sure what Reporting services are, is it the MS version of Crystal ?
If so no i cant cus everything here is build round crystal reports already and the company wont buy another reporting application as crystal Pro was already 5K per license
Our ERP clients also includes crystal runtimes. So its best if i stick with it and i dont think having a new reporting program will justify 1 report
I have SQL2005 Stored procs, Views , tables and Crystal reports at my disposal to make this report.
And to your last post.
I could try and pull in BO orders but BO's is listed per transaction in order entry OEORDH and ORORDD which is the header and detail. i only need the detail. And i sum the detail field QTYBCKRD where its > 0
But ive now already created 2 views one for BO and one for QTY on hand and if i link the 2 to the ICITEM table on ITMENO using a right outer join.
I get the 27K items we have when i do a sum so it works perfectly.
On the items where there is no Back order, its just blank which is fine cus you can always do a find and replace in Excel to make the spaces 0 (Not ideal but i should be fine)
I just need to somehow include the sales side now, I'm thinking of using a Sub Report which joins to ICITEM's ITEMNO field also using a right outer join. I dont think crystal can pass date parameters to a view.
But i think i'm on track now.
RuadRauFlessa i got you PM, thanks its appreciated will try what i have here if it doesnt work i will let you know.
Yes its SQL2005 i'm not sure what Reporting services are, is it the MS version of Crystal ?
If so no i cant cus everything here is build round crystal reports already and the company wont buy another reporting application as crystal Pro was already 5K per license
Our ERP clients also includes crystal runtimes. So its best if i stick with it and i dont think having a new reporting program will justify 1 report
I have SQL2005 Stored procs, Views , tables and Crystal reports at my disposal to make this report.
And to your last post.
I could try and pull in BO orders but BO's is listed per transaction in order entry OEORDH and ORORDD which is the header and detail. i only need the detail. And i sum the detail field QTYBCKRD where its > 0
But ive now already created 2 views one for BO and one for QTY on hand and if i link the 2 to the ICITEM table on ITMENO using a right outer join.
I get the 27K items we have when i do a sum so it works perfectly.
On the items where there is no Back order, its just blank which is fine cus you can always do a find and replace in Excel to make the spaces 0 (Not ideal but i should be fine)
I just need to somehow include the sales side now, I'm thinking of using a Sub Report which joins to ICITEM's ITEMNO field also using a right outer join. I dont think crystal can pass date parameters to a view.
But i think i'm on track now.
RuadRauFlessa i got you PM, thanks its appreciated will try what i have here if it doesnt work i will let you know.
- rustypup
- Registered User
- Posts: 8872
- Joined: 13 Dec 2004, 02:00
- Location: nullus pixius demonica
- Contact:
Re: SQL Querry Help Grouping
sort of... but it allows you to schedule report runs, which is a blessing around month end... simply schedule and forget...SBSP wrote:is it the MS version of Crystal ?
i stand to be corrected, but SQL 2005 includes RS for free... depending on version...SBSP wrote:the company wont buy another reporting application as crystal Pro was already 5K per license
does the detail table include a date field to bind to?... if not, i'm sure the header table does... this means you don't end up selecting all historical data, which is what a standard view would do...SBSP wrote:i only need the detail. And i sum the detail field QTYBCKRD where its > 0
if the sales document number is present in all tables, including the sales tables, is there any reason why you're breaking the data up?
if you're going to go with the view, at least restrain the records ..I dont think crystal can pass date parameters to a view.
Code: Select all
SELECT yadayada
FROM yadayada
WHERE YEAR(SOME_DATE)=YEAR(GETDATE()) AND MONTH(SOME_DATE) = (MONTH(GETDATE())
Most people would sooner die than think; in fact, they do so - Bertrand Russel
Re: SQL Querry Help Grouping
Done!
You guys Rock!
I created the 2 views like so.
and
Right outer joined the 2 views item fields to the ICITEM's item field using a Right outer join.
Then in CR built the report to show item number item description , Qty on hand, qty on Bo.
Then added the OESHDT table which is the sales history table (Detail). also right outer join to ICITEM item field
Put in a parameter , created 1 formula that says.
Grouped the report by item hide the details section put in a sumary on QTY sold and moved it to be in the same line
as the group header then also, moved the qty bo, qty on hand and desc to be inline with the group header (Item group)
Run export into excel and figures match up 100%
Would it be fair for me to ask our money back that we spent on the consultant ?
I dont mind the call out but the consultant's fees is already at 8 grand ?
You guys Rock!
I created the 2 views like so.
Code: Select all
USE [COMPANY]
GO
/****** Object: View [dbo].[ForecastQTyONHAND] Script Date: 07/07/2009 09:07:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[ForecastQTyONHAND]
AS
SELECT ICITEM.ITEMNO, SUM(ICILOC.QTYONHAND) AS QTYONHAND
FROM dbo.ICITEM AS ICITEM INNER JOIN
dbo.ICILOC AS ICILOC ON ICITEM.ITEMNO = ICILOC.ITEMNO
GROUP BY ICITEM.ITEMNO
Code: Select all
USE [COMPANY]
GO
/****** Object: View [dbo].[ForeCastQtyonBO] Script Date: 07/07/2009 09:07:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[ForeCastQtyonBO]
AS
SELECT ITEM, SUM(QTYBACKORD) AS SUMQTYBO
FROM dbo.OEORDD AS OEORDD
WHERE (QTYBACKORD > 0)
GROUP BY ITEM
Then in CR built the report to show item number item description , Qty on hand, qty on Bo.
Then added the OESHDT table which is the sales history table (Detail). also right outer join to ICITEM item field
Put in a parameter , created 1 formula that says.
Code: Select all
if {OESHDT.TRANDATE} > {?Date From} then
if {OESHDT.TRANDATE} < {?Date To} then
{OESHDT.QTYSOLD}
else
0
as the group header then also, moved the qty bo, qty on hand and desc to be inline with the group header (Item group)
Run export into excel and figures match up 100%
Would it be fair for me to ask our money back that we spent on the consultant ?
I dont mind the call out but the consultant's fees is already at 8 grand ?
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: SQL Querry Help Grouping
SBSP, he didn't solve your problem. So just throw it into management's faces that they said something bad about your work and they will handle the rest. Just do it subtly.
- Spoiler (show)
Re: SQL Querry Help Grouping
Yip think i'm going to do that.
There is one thing that really get's to me, thats when
Someone ignorantly ask you to do something and they dont really how much work is involved or really cares
and they make it out to be simple.
And when you explain to them you cant cus of this and that, they look at you strange when you get technical.
I mean how do i explain database table names and linking them together
This user couldive just run 3 different reports and used a vlookup in excel i'm sure
to get the results.
But at least its done. I'm not too happy using SQL Views cus when the next ERP upgrade comes its just another
nag that has to be upgraded. Cus of some table change.
There is one thing that really get's to me, thats when
Someone ignorantly ask you to do something and they dont really how much work is involved or really cares
and they make it out to be simple.
And when you explain to them you cant cus of this and that, they look at you strange when you get technical.
I mean how do i explain database table names and linking them together
This user couldive just run 3 different reports and used a vlookup in excel i'm sure
to get the results.
But at least its done. I'm not too happy using SQL Views cus when the next ERP upgrade comes its just another
nag that has to be upgraded. Cus of some table change.