September 4, 2016 at 12:22 am
I am new to SQL and I wanted to create a view the problem I have is the "Group by". Currently my data is showing:
Customer Invoice Date Amount
AJAX 5/1/2016 $500
What I really want to do is enter a parameter from 1/1/2016 to 8/31/2016 and show the Top 5 customer sales I don't need to show the invoice date only for inputting date all I want is the following:
Customer Amount
AJAX $10,000
ACME 8,000
ACE 5,000
Code
SELECT CUSMS_1.CMCSNM AS Customer, CAST(CAST(HSHED.OAIPDT AS CHAR(8)) AS DATE) AS [Invoice Date], SUM(HSDET.OBLNAM)
AS Amount
FROM HSDET INNER JOIN
HSHED ON HSDET.OBCONO = HSHED.OACONO AND HSDET.OBCSNO = HSHED.OACSNO AND HSDET.OBORNO = HSHED.OAORNO INNER JOIN
CUSMS ON HSHED.OACONO = CUSMS.CMCONO AND HSHED.OACSNO = CUSMS.CMCSNO INNER JOIN
ITMST ON HSDET.OBITNO = ITMST.IMITNO INNER JOIN
CUSMS AS CUSMS_1 ON HSHED.OAARCN = CUSMS_1.CMCSNO
WHERE (CAST(CAST(HSHED.OAIPDT AS CHAR(8)) AS DATE) BETWEEN P1 AND P2)
GROUP BY CAST(CAST(HSHED.OAIPDT AS CHAR(8)) AS DATE), CUSMS_1.CMCSNM
Any help will be appreciated. Thank you in advance
September 4, 2016 at 5:49 am
Can you please post the DDL (create table) scripts, sample data as an insert statement and the expected results?
😎
September 4, 2016 at 8:52 am
If you are looking for the top five total amounts, the code below should work. If you are looking for the top five individual sales, there is no need for a GROUP BY. But Eirikur is right. Please read this article before submitting another question:
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
WITH CTE AS
(
SELECT CUSMS_1.CMCSNM AS Customer, CAST(CAST(HSHED.OAIPDT AS CHAR(8)) AS DATE) AS [Invoice Date], SUM(HSDET.OBLNAM)
AS Amount
FROM HSDET INNER JOIN
HSHED ON HSDET.OBCONO = HSHED.OACONO AND HSDET.OBCSNO = HSHED.OACSNO AND HSDET.OBORNO = HSHED.OAORNO INNER JOIN
CUSMS ON HSHED.OACONO = CUSMS.CMCONO AND HSHED.OACSNO = CUSMS.CMCSNO INNER JOIN
ITMST ON HSDET.OBITNO = ITMST.IMITNO INNER JOIN
CUSMS AS CUSMS_1 ON HSHED.OAARCN = CUSMS_1.CMCSNO
WHERE (CAST(CAST(HSHED.OAIPDT AS CHAR(8)) AS DATE) BETWEEN P1 AND P2)
GROUP BY CAST(CAST(HSHED.OAIPDT AS CHAR(8)) AS DATE), CUSMS_1.CMCSNM)
SELECT TOP(5) *
FROM CTE
ORDER BY Amount DESC
By the way, if you want to use parameters, consider using an inline table valued function instead of a view. ITVFs behave like views but accept parameters.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 5, 2016 at 9:58 am
Thanks The Dixie Flatline, but I am getting the invoice date, which I don't need and your right I don't need to group them.
September 5, 2016 at 11:34 am
I hope I'm understanding you correctly. The code below will show you the top 5 amounts in descending order.
There should be no need to cast OAIPDT as char(8) then RE-cast it as a date column. If it is a DATETIME column, just cast it as DATE.
I presume your parameters are going to be DATE datatypes. If OAIPDT is a DATETIME column your WHERE clause should look the change I made in the code below.
Again, I am having to guess at what you want. Please read the article I referred to you previously. If you posted supporting data and expected results for your problem in the manner it suggests, you would already have a coded and tested answer. We all volunteer our time and expertise to help others out. Help us help you by setting up the problem so we can communicate better. Thanks.
SELECT TOP (5) CUSMS_1.CMCSNM AS Customer, CAST(HSHED.OAIPDT AS AS DATE) AS [Invoice Date], HSDET.OBLNAM
AS Amount
FROM HSDET INNER JOIN
HSHED ON HSDET.OBCONO = HSHED.OACONO AND HSDET.OBCSNO = HSHED.OACSNO AND HSDET.OBORNO = HSHED.OAORNO INNER JOIN
CUSMS ON HSHED.OACONO = CUSMS.CMCONO AND HSHED.OACSNO = CUSMS.CMCSNO INNER JOIN
ITMST ON HSDET.OBITNO = ITMST.IMITNO INNER JOIN
CUSMS AS CUSMS_1 ON HSHED.OAARCN = CUSMS_1.CMCSNO
WHERE HSHED.OAIPDT >= @P1 AND HSHED.OAIPDT < DATEADD(DAY,1@P2)
ORDER BY OBLNAM DESC
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 6, 2016 at 6:43 am
I suspect the invoice date is formatted as YYYYMMDD but stored as an integer. I've inherited a number of tables like that. Another important reason for giving us the table structures and sample data. If so it could be faster to cast the start/end limits to the same integer format, rather than casting all the table values.
I'm also not clear on what the query results should be. Does the user want the 5 invoices with the highest amount during the period (in which case ACME might show up more than once)? Or does he want the 5 customers with the highest total invoices during the time period?
September 6, 2016 at 7:20 am
gvoshol 73146,
Thanks, yes I've inherited the date field is actually decimal with yyyymmdd. What I was really looking for is 5 customers with the highest total invoices during the time period? I was able to figure it out. I was able to figure it out thanks to The Dixie Flatline. I want to thank all for helping.
September 6, 2016 at 7:21 am
The Dixie Flatline, yes you understood correctly and I was able to get what I needed. Thank you for your help. Regards
September 6, 2016 at 9:53 am
You're quite welcome, but once again I urge you to read the article about how to best post questions. We all would have gotten to the correct answer much faster that way. 😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply