January 27, 2017 at 2:48 am
Hi, I have created a view here is the code ..............
USE [Quartz]
GO
/****** Object: View [dbo].[QUARTZREPORT] Script Date: 27/01/2017 09:26:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[QUARTZREPORT]
AS
SELECT dbo.Sales.SALESDESCRIPTION,
dbo.Groups.GROUPDESCRIPTION,
dbo.Account.ACCOUNTNUMBER,
dbo.Account.ACCOUNTNAME,
dbo.Orders.ORDERDATEANDTIME,
DATENAME(MONTH,ORDERDATEANDTIME) AS MONTH_NAME,
(datepart(mm,ORDERDATEANDTIME)) AS MONTH_NUMBER,(DATEPART(yyyy,ORDERDATEANDTIME)) as YEAR,
CASE WHEN ORDERS.TYPE = 0 THEN 'SP' WHEN TYPE = 1 THEN 'WO' WHEN TYPE = 3 THEN 'NP8' WHEN TYPE = 4 THEN 'SO' WHEN TYPE = 6 THEN 'PI' WHEN TYPE = 7 THEN 'GEN' WHEN TYPE = 8 THEN 'OTC' ELSE 'XX' END AS Type,
dbo.Products.SPECIALCODE,
dbo.Products.SPECIALDESCRIPTION, dbo.Products.WHOLESALE, dbo.Products.PRODUCTID
FROM dbo.Products INNER JOIN
dbo.Orders ON dbo.Products.PRODUCTID = dbo.Orders.PRODUCTID INNER JOIN
dbo.Account ON dbo.Orders.ACCOUNTID = dbo.Account.ACCOUNTID INNER JOIN
dbo.Groups ON dbo.Account.GROUPID = dbo.Groups.GROUPID CROSS JOIN
dbo.Sales
--order by dbo.Orders.ORDERDATEANDTIME
GO
What I need to do is add another 4 columns to the view. Column names 'NP8', 'SO','WO' and Total. In the columns I need to add up the number of 'NP8's, the number of SO's and the number of WO's for (SALESDESCRIPTION, GROUPDECRIPTION AND ACCOUNTNUMBER) those 3 fields grouped together and then the total column will be the NP's the SO's and WO's added up.
A bit of background. SALESDECRIPTION is just the sales rep really, GROUPDESCRIPTION is the company that makes the product and ACCOUNTNUMBER is the customer account number.
So if Joe Bloggs (SALESDESCRIPTION), ACME Ltd.(GROUPDESCRIPTION), ACM001 (ACCOUNTNUMBER) has 8 SO's appearing and 5 NP's appearing how do I go about counting them up?
Thanks in advance
Paul.
January 27, 2017 at 6:51 am
paul 69259 - Friday, January 27, 2017 2:48 AMHi, I have created a view here is the code ..............
USE [Quartz]
GO/****** Object: View [dbo].[QUARTZREPORT] Script Date: 27/01/2017 09:26:17 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE VIEW [dbo].[QUARTZREPORT]
AS
SELECT dbo.Sales.SALESDESCRIPTION,
dbo.Groups.GROUPDESCRIPTION,
dbo.Account.ACCOUNTNUMBER,
dbo.Account.ACCOUNTNAME,
dbo.Orders.ORDERDATEANDTIME,
DATENAME(MONTH,ORDERDATEANDTIME) AS MONTH_NAME,
(datepart(mm,ORDERDATEANDTIME)) AS MONTH_NUMBER,(DATEPART(yyyy,ORDERDATEANDTIME)) as YEAR,
CASE WHEN ORDERS.TYPE = 0 THEN 'SP' WHEN TYPE = 1 THEN 'WO' WHEN TYPE = 3 THEN 'NP8' WHEN TYPE = 4 THEN 'SO' WHEN TYPE = 6 THEN 'PI' WHEN TYPE = 7 THEN 'GEN' WHEN TYPE = 8 THEN 'OTC' ELSE 'XX' END AS Type,dbo.Products.SPECIALCODE,
dbo.Products.SPECIALDESCRIPTION, dbo.Products.WHOLESALE, dbo.Products.PRODUCTID
FROM dbo.Products INNER JOIN
dbo.Orders ON dbo.Products.PRODUCTID = dbo.Orders.PRODUCTID INNER JOIN
dbo.Account ON dbo.Orders.ACCOUNTID = dbo.Account.ACCOUNTID INNER JOIN
dbo.Groups ON dbo.Account.GROUPID = dbo.Groups.GROUPID CROSS JOIN
dbo.Sales
--order by dbo.Orders.ORDERDATEANDTIME
GO
What I need to do is add another 4 columns to the view. Column names 'NP8', 'SO','WO' and Total. In the columns I need to add up the number of 'NP8's, the number of SO's and the number of WO's for (SALESDESCRIPTION, GROUPDECRIPTION AND ACCOUNTNUMBER) those 3 fields grouped together and then the total column will be the NP's the SO's and WO's added up.
A bit of background. SALESDECRIPTION is just the sales rep really, GROUPDESCRIPTION is the company that makes the product and ACCOUNTNUMBER is the customer account number.
So if Joe Bloggs (SALESDESCRIPTION), ACME Ltd.(GROUPDESCRIPTION), ACM001 (ACCOUNTNUMBER) has 8 SO's appearing and 5 NP's appearing how do I go about counting them up?
Thanks in advance
Paul.
Hi Paul
Here's your code reformatted and with table names aliased for clarity. Can you please check that it works?
Also, your Sales table is cross-joined. Are you absolutely sure that this is correct? No join criteria?
CREATE VIEW [dbo].[QUARTZREPORT]
AS
SELECT
s.SALESDESCRIPTION,
g.GROUPDESCRIPTION,
a.ACCOUNTNUMBER,
a.ACCOUNTNAME,
o.ORDERDATEANDTIME,
DATENAME(MONTH,o.ORDERDATEANDTIME) AS MONTH_NAME,
(datepart(mm,o.ORDERDATEANDTIME)) AS MONTH_NUMBER,
(DATEPART(yyyy,o.ORDERDATEANDTIME)) as YEAR,
CASE o.[TYPE]
WHEN 0 THEN 'SP'
WHEN 1 THEN 'WO'
WHEN 3 THEN 'NP8'
WHEN 4 THEN 'SO'
WHEN 6 THEN 'PI'
WHEN 7 THEN 'GEN'
WHEN 8 THEN 'OTC'
ELSE 'XX' END AS [Type],
p.SPECIALCODE,
p.SPECIALDESCRIPTION,
p.WHOLESALE,
p.PRODUCTID
FROM dbo.Products p
INNER JOIN dbo.Orders o
ON p.PRODUCTID = o.PRODUCTID
INNER JOIN dbo.Account a
ON o.ACCOUNTID = a.ACCOUNTID
INNER JOIN dbo.Groups g
ON a.GROUPID = g.GROUPID
CROSS JOIN dbo.Sales s
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
January 27, 2017 at 7:24 am
ChrisM@Work - Friday, January 27, 2017 6:51 AMpaul 69259 - Friday, January 27, 2017 2:48 AMHi, I have created a view here is the code ..............
USE [Quartz]
GO/****** Object: View [dbo].[QUARTZREPORT] Script Date: 27/01/2017 09:26:17 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE VIEW [dbo].[QUARTZREPORT]
AS
SELECT dbo.Sales.SALESDESCRIPTION,
dbo.Groups.GROUPDESCRIPTION,
dbo.Account.ACCOUNTNUMBER,
dbo.Account.ACCOUNTNAME,
dbo.Orders.ORDERDATEANDTIME,
DATENAME(MONTH,ORDERDATEANDTIME) AS MONTH_NAME,
(datepart(mm,ORDERDATEANDTIME)) AS MONTH_NUMBER,(DATEPART(yyyy,ORDERDATEANDTIME)) as YEAR,
CASE WHEN ORDERS.TYPE = 0 THEN 'SP' WHEN TYPE = 1 THEN 'WO' WHEN TYPE = 3 THEN 'NP8' WHEN TYPE = 4 THEN 'SO' WHEN TYPE = 6 THEN 'PI' WHEN TYPE = 7 THEN 'GEN' WHEN TYPE = 8 THEN 'OTC' ELSE 'XX' END AS Type,dbo.Products.SPECIALCODE,
dbo.Products.SPECIALDESCRIPTION, dbo.Products.WHOLESALE, dbo.Products.PRODUCTID
FROM dbo.Products INNER JOIN
dbo.Orders ON dbo.Products.PRODUCTID = dbo.Orders.PRODUCTID INNER JOIN
dbo.Account ON dbo.Orders.ACCOUNTID = dbo.Account.ACCOUNTID INNER JOIN
dbo.Groups ON dbo.Account.GROUPID = dbo.Groups.GROUPID CROSS JOIN
dbo.Sales
--order by dbo.Orders.ORDERDATEANDTIME
GO
What I need to do is add another 4 columns to the view. Column names 'NP8', 'SO','WO' and Total. In the columns I need to add up the number of 'NP8's, the number of SO's and the number of WO's for (SALESDESCRIPTION, GROUPDECRIPTION AND ACCOUNTNUMBER) those 3 fields grouped together and then the total column will be the NP's the SO's and WO's added up.
A bit of background. SALESDECRIPTION is just the sales rep really, GROUPDESCRIPTION is the company that makes the product and ACCOUNTNUMBER is the customer account number.
So if Joe Bloggs (SALESDESCRIPTION), ACME Ltd.(GROUPDESCRIPTION), ACM001 (ACCOUNTNUMBER) has 8 SO's appearing and 5 NP's appearing how do I go about counting them up?
Thanks in advance
Paul.
Hi Paul
Here's your code reformatted and with table names aliased for clarity. Can you please check that it works?
Also, your Sales table is cross-joined. Are you absolutely sure that this is correct? No join criteria?
CREATE VIEW [dbo].[QUARTZREPORT]
AS
SELECT
s.SALESDESCRIPTION,
g.GROUPDESCRIPTION,
a.ACCOUNTNUMBER,
a.ACCOUNTNAME,
o.ORDERDATEANDTIME,
DATENAME(MONTH,o.ORDERDATEANDTIME) AS MONTH_NAME,
(datepart(mm,o.ORDERDATEANDTIME)) AS MONTH_NUMBER,
(DATEPART(yyyy,o.ORDERDATEANDTIME)) as YEAR,
CASE o.[TYPE]
WHEN 0 THEN 'SP'
WHEN 1 THEN 'WO'
WHEN 3 THEN 'NP8'
WHEN 4 THEN 'SO'
WHEN 6 THEN 'PI'
WHEN 7 THEN 'GEN'
WHEN 8 THEN 'OTC'
ELSE 'XX' END AS [Type],
p.SPECIALCODE,
p.SPECIALDESCRIPTION,
p.WHOLESALE,
p.PRODUCTID
FROM dbo.Products p
INNER JOIN dbo.Orders o
ON p.PRODUCTID = o.PRODUCTID
INNER JOIN dbo.Account a
ON o.ACCOUNTID = a.ACCOUNTID
INNER JOIN dbo.Groups g
ON a.GROUPID = g.GROUPID
CROSS JOIN dbo.Sales s
Hi Chris,
Yes, that works thanks.
The only link to any table from the sales table is that the salesID is a foreign key in the Account table.
I need to somehow group the report by salesdescription (or sales rep) and then have all the groupdescription, accountnumber,account name and type to each sales rep. then count up the number of individual types for each. It's a bit complicated.
Thank you
Paul
January 27, 2017 at 7:35 am
Focus first on ensuring that your existing query is correct. Comment out
s.SALESDESCRIPTION,
and
CROSS
JOIN dbo.Sales s
then examine the results. If a ton of duplicates have been eliminated, then you know that you have to join the Sales table.
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
January 27, 2017 at 7:46 am
ChrisM@Work - Friday, January 27, 2017 7:35 AMFocus first on ensuring that your existing query is correct. Comment out
s.SALESDESCRIPTION,
andCROSS
JOIN dbo.Sales sthen examine the results. If a ton of duplicates have been eliminated, then you know that you have to join the Sales table.
Yes, before commenting out I get 264852 rows of data when selecting all from the view but after commenting out I get 29428 rows of data
January 27, 2017 at 7:52 am
paul 69259 - Friday, January 27, 2017 7:46 AMChrisM@Work - Friday, January 27, 2017 7:35 AMFocus first on ensuring that your existing query is correct. Comment out
s.SALESDESCRIPTION,
andCROSS
JOIN dbo.Sales sthen examine the results. If a ton of duplicates have been eliminated, then you know that you have to join the Sales table.
Yes, before commenting out I get 264852 rows of data when selecting all from the view but after commenting out I get 29428 rows of data
It's probably a bit quicker too 🙂
You need to determine what the join criteria are for that table. Try your best guess first, you might want to change that CROSS JOIN to a LEFT JOIN for convenience.
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
January 27, 2017 at 7:59 am
ChrisM@Work - Friday, January 27, 2017 7:52 AMpaul 69259 - Friday, January 27, 2017 7:46 AMChrisM@Work - Friday, January 27, 2017 7:35 AMFocus first on ensuring that your existing query is correct. Comment out
s.SALESDESCRIPTION,
andCROSS
JOIN dbo.Sales sthen examine the results. If a ton of duplicates have been eliminated, then you know that you have to join the Sales table.
Yes, before commenting out I get 264852 rows of data when selecting all from the view but after commenting out I get 29428 rows of data
It's probably a bit quicker too 🙂
You need to determine what the join criteria are for that table. Try your best guess first, you might want to change that CROSS JOIN to a LEFT JOIN for convenience.
It doesn't like a left join, inner or outer join it gives and error.
January 27, 2017 at 8:07 am
paul 69259 - Friday, January 27, 2017 7:59 AMChrisM@Work - Friday, January 27, 2017 7:52 AMpaul 69259 - Friday, January 27, 2017 7:46 AMChrisM@Work - Friday, January 27, 2017 7:35 AMFocus first on ensuring that your existing query is correct. Comment out
s.SALESDESCRIPTION,
andCROSS
JOIN dbo.Sales sthen examine the results. If a ton of duplicates have been eliminated, then you know that you have to join the Sales table.
Yes, before commenting out I get 264852 rows of data when selecting all from the view but after commenting out I get 29428 rows of data
It's probably a bit quicker too 🙂
You need to determine what the join criteria are for that table. Try your best guess first, you might want to change that CROSS JOIN to a LEFT JOIN for convenience.It doesn't like a left join, inner or outer join it gives and error.
None of the folks who lurk here are mindreaders, Paul 😉 At a guess, you're missing the ON clause.
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
January 27, 2017 at 8:47 am
ChrisM@Work - Friday, January 27, 2017 8:07 AMpaul 69259 - Friday, January 27, 2017 7:59 AMChrisM@Work - Friday, January 27, 2017 7:52 AMpaul 69259 - Friday, January 27, 2017 7:46 AMChrisM@Work - Friday, January 27, 2017 7:35 AMFocus first on ensuring that your existing query is correct. Comment out
s.SALESDESCRIPTION,
andCROSS
JOIN dbo.Sales sthen examine the results. If a ton of duplicates have been eliminated, then you know that you have to join the Sales table.
Yes, before commenting out I get 264852 rows of data when selecting all from the view but after commenting out I get 29428 rows of data
It's probably a bit quicker too 🙂
You need to determine what the join criteria are for that table. Try your best guess first, you might want to change that CROSS JOIN to a LEFT JOIN for convenience.It doesn't like a left join, inner or outer join it gives and error.
None of the folks who lurk here are mindreaders, Paul 😉 At a guess, you're missing the ON clause.
Ahh yes, thanks,
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply