August 10, 2010 at 9:45 am
I am working on a SQL query that will be used in a crystal report. The problem I am tasked with is writing a report to summarize sales of certain products by the purchaser’s job code. I also need to be able to input certain job codes in the report and ensure that it show’s 0 if a sum of the purchases of a particular item for that job code are 0.
The db I’m working with has a table for all accounts, which also stores the job code of the individual, a table for the order, a table for the line items of the order, and a separate table for the job codes and descriptions.
The table for the job codes and descriptions has no linkable columns to the other tables needed for the query. The query below has all of the information that I will need as far as relevant orders goes. Essentially what I am looking to do is merge the query below with and get a count of each purchase of an item by job code. If no one of that job code purchased that particular item, I still need it to display zero. I’ve read that a left outer join would normally do this but with the tables having no linkable columns I’m not sure how to do that.
Let me know if there is anything else I can provide and thanks in advance for the help.
Query shows relevant purchases of an item and bottom query shows the query necessary for job codes and descriptions.
SELECT
am.name,
am.jobcode,
jobcode.jobdesc,
order.orderdate,
orderdtl.itemcode,
FROM
order INNER JOIN am ON
order.acctcode = am.acctcode
INNER JOIN jobcode ON
jobcode.jobcode = am.jobcode
INNER JOIN orderdtl ON
order.ordernbr = orderdtl.ordernbr
WHERE
order.ordertype = 'SO' AND
order.orderstatus = 'C' AND
orderdtl.dept IN ('CDROM', 'VIDEO', 'CDR', 'EBOOKS','AUDCDS') AND
order.orderdate BETWEEN '2009-08-01' and '2010-07-31' AND
orderdtl.itemcode = 'asdf123' AND
oderdtl.completestatus = 'Y'
SELECT jobcode.jobcode,
jobcode.DESC
FROMjobcode
August 10, 2010 at 10:35 am
not really sure what you are trying to get but here is a guess....
SELECT
am.name,
am.jobcode,
jobcode.jobdesc,
order.orderdate,
orderdtl.itemcode,
count(order.orderdate) as NumSold
from
.
.
where
.
.
group by am.name,
am.jobcode,
jobcode.jobdesc,
order.orderdate,
orderdtl.itemcode
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 10, 2010 at 10:53 am
I'm essentially looking to get a query that shows all of the job codes regardless of any purchases. I'll give it a shot but I don't think that one will quite get me what I'm looking for.
In essence there are 122 job codes in our database. I need all 122 listed on a query no matter what. Then in a purchase count column I need to know how many of that job code purchased an item etc.
As far as having to have it actually retrieve the info listed above all I really need it to display is job code, job description(preferably not necessary) and purchase count(for each job code). I can get the other info via a different query in the report. However in order to get accurate purchase records those tables need to be linked.
Does that clarify at all?
August 10, 2010 at 11:01 am
try this
select j.jobcode, j.jobdesc, count(order.orderdate) as NumSold
from jobcode j
left join am on am.jobcode = j.jobcode
left join order on order.acctcode = am.acctcode
where [whatever stuff here]
group by j.jobcode, j.jobdesc
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 10, 2010 at 11:22 am
This is close, however I had to join in the order detail table as well, since orders can have multiple items. Also it only shows job codes that actually purchased something, it doesn't show them if no one with that job code purchased anything.
I believe the group by all function would work, however I can't do that in crystal there for i don't think that's an option for me.
Thanks again for your help.
select j.jobcode, j.jobdesc, count(order.orderdate) as NumSold
from jobcode j
left join am on am.jobcode = j.jobcode
left join order on order.acctcode = am.acctcode
left join orderdtl on order.ordernbr = orderdtl=ordernbr
where [whatever stuff here]
group by j.jobcode, j.jobdesc
August 10, 2010 at 11:27 am
Oh yeah :hehe:.
You need to add an isnull to the order like (although you should count a field from order detail). That should just about get you there.
count(isnull(order.orderdate, 0)) as NumSold
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 10, 2010 at 12:34 pm
Well we're getting there, unfortunately isnull only replaces it if it returns NULL, which this just doesn't return a row. I do however think I can use a view for this report in which case I should be able to use the group by all function. Ill let you know what I came up with.
Thanks again.
August 10, 2010 at 12:41 pm
bolson32 (8/10/2010)
Well we're getting there, unfortunately isnull only replaces it if it returns NULL, which this just doesn't return a row. I do however think I can use a view for this report in which case I should be able to use the group by all function. Ill let you know what I came up with.Thanks again.
If the field is from a table that is joined with a left join and there are no records present for it, ISNULL(theField,0) will return 0.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply