I'm new to SQL (Like Really New) we have had an ERP at work for years and we can obviously pay the company to write our reports but this obviously comes at cost and also if you don't know what to ask then you don't know what you can get . So I though lets have a look and see what its about . So I'm trying to make a simple top 10 of products sold . Which I think I've managed what I don't seem to able to do is then get this into some sort of date range parameter
Hi , the effective date is the date the invoice is raised on , and I would like to be able to filter that , as currently it appears it's looking at all products invoices since time began
I recommend you use GreaterThanOrEqual and LessThan (if you need an endpoint) tests.
If you use "text dates" then stick to 'yyyymmdd' as SQL will treat that as UNambiguous. If you go for '12/31/2016' then you are at the mercy of how SQL chooses to parse that date - and THAT depends on things like the Language setting for the currently connected user - which can obviously change!
For example based on SalesInvoices.EffectiveDate column:
FROM ...
WHERE SalesInvoices.EffectiveDate >= '20170101'
AND SalesInvoices.EffectiveDate < '20180101' -- NOTE: One day PAST the end point
ORDER BY ...
We'd prefer that you post the SQL and the Data (rather than an image) then folk can Cut and Paste to give you answers, and if your question would benefit from testing we can check that our code actually works before posting it - which saves us both time.
```sql
... Your SQL code here
```
and
```text
... Any fixed-spacing text, such as results, here
```
Welcome to the wonderful, and oh-so-frustrating, world of SQL.
A few tips:
Use aliases rather than full table names. SQL allows you to put an "alias"/abbreviation for the table name immediately following the name. This makes the code easier to read (and to change later). I have done that in the query below.
Add the schema/owner before the table name if possible. Almost always for SQL Server it'll be 'dbo'. If the code doesn't work, remove the 'dbo.' in front of all the table names.
When using conditions on joined tables, such as comparing on invoice date, for example, put the condition in the JOIN if possible. I have done that in the code below as well, using "InvoiceDate" from one year back to the first of the current month. Adjust that time frame as needed. I find the spacing used very hard to read and maintain, with soooo much space between clauses and lines, but I have left it to match your original post as well as I could.
SELECT TOP (10)
/*your column list here*/
FROM dbo.SalesOrders SO INNER JOIN
dbo.SalesInvoices SI ON SO.SalesOrder = SI.SalesOrder AND
/* 12 months before the first of the current month */
SI.InvoiceDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, 0) INNER JOIN
dbo.SalesOrderItems SOI ON SO.SalesOrder = SOI.SalesOrderItems INNER JOIN
dbo.Products P ON SOI.Product = P.Product INNER JOIN
dbo.Classifications C ON C.Classification = P.Classification
Can I say a big thank you for your help , the info is greatly appreciated , So i tried the WHERE's as suggested and if i manually change the dates all good . then I skipped along to Parameter dates but its returning no values . Thanks in advance
--DECLARE @StartDate smalldatetime
--DECLARE @EndDate smalldatetime
--SET @StartDate = '01 JUN 2011'
SELECT TOP (10)
SalesInvoices.EffectiveDate, Products.ProductId, Products.ProductDescription, Classifications.ClassificationId, SalesOrders.OrderValue, SalesOrderItems.Quantity
FROM SalesOrders INNER JOIN
SalesInvoices ON SalesOrders.SalesOrder = SalesInvoices.SalesOrder INNER JOIN
SalesOrderItems ON SalesOrders.SalesOrder = SalesOrderItems.SalesOrder INNER JOIN
Classifications INNER JOIN
Products ON Classifications.Classification = Products.Classification ON SalesOrderItems.Product = Products.Product
--WHERE
SalesInvoices.EffectiveDate >= @StartDate
and SalesInvoices.EffectiveDate < @EndDate -- NOTE: One day PAST the end pointSET @EndDate = '30 JUN 2011'
ORDER BY SalesOrderItems.Quantity DESC
Probably the problem is that you haven't set a value for this, so it is NULL and anything compared against NULL (other than a specific test for NULL) is never true.
So you could have:
and (@EndDate IS NULL OR SalesInvoices.EffectiveDate < @EndDate)
which would allow you to have either a specific End Date, or NULL meaning "No end date"
Like I said:
Stick to YYYYMMDD for text/string dates i.e. in your example '20110601'
HI < thanks for the info , I don't have control of the DB so I'm taking the info from working reports and working with what has been done previously . I'm still getting a WHERE error when I run the whole query but not when i isolate . Any recommendations on easy Query Builders? , Many thanks for all your helps
--DECLARE @StartDate smalldatetime
--DECLARE @EndDate smalldatetime
SELECT TOP (25)
(SalesInvoices.EffectiveDate) AS Date
,Products.ProductId AS [Product Id]
,Products.ProductDescription
,Classifications.ClassificationId
,SalesInvoiceItems.Quantity
,SalesInvoiceItems.ItemValue
FROM dbo.CommissionAgents
,dbo.SalesInvoiceItems
INNER JOIN dbo.SalesInvoices
ON SalesInvoiceItems.SalesInvoice = SalesInvoices.SalesInvoice
INNER
JOIN dbo.Products
ON SalesInvoiceItems.Product = Products.Product
INNER JOIN dbo.Classifications
ON Products.Classification = Classifications.Classification
GROUP BY Products.ProductId
,Products.ProductDescription
,Classifications.ClassificationId
,SalesInvoiceItems.Quantity
,SalesInvoiceItems.ItemValue
WHERE SalesInvoices.EffectiveDate >= @StartDate
and (@EndDate IS NULL OR SalesInvoices.EffectiveDate < @EndDate)
ORDER BY SalesInvoiceItems.Quantity DESC
That's a Cartesian Join - it will join every single column in CommissionAgents to every column in SalesInvoiceItems and I doubt that is what you want ...
Try starting with the main table (probably SalesInvoiceItems) and then JOIN everything to that - i.e. find a common column between SalesInvoiceItems and the table you then want to JOIN, or a common column to any table already JOIN'd
You haven;t actually JOINed CommissionAgents to anything, nor used any columns in the Query, so perhaps you could just delete that table from the query for now?
Once you've fixed that this should work:
DECLARE @StartDate smalldatetime
DECLARE @EndDate smalldatetime
SET @StartDate = '20110601'
-- SET @EndDate = '20110701' -- Optional end date
... your query ...
You might already have SQL Server Reporting Services (SSRS) installed as par of your SQL system? Not saying that's "easy", per se!!, but if its already installed that may make getting going easier.
Be interest to hear what other report writers folk here recommend.
Well that got it working , which is fantastic . I appreciate the help her i cant seem to find ONE to ONE training local to me but the more questions I ask the more I begin to understand ( I think)
So if I want to add additional filters to my TOP 10 so I only see results from a certain CommisionClassId or ClassificationId I assume I have to add additional Where Clauses ? I have a demo Of DB forge Studio but its understanding the language i really need to get to grips with
SELECT TOP (10)
MAX(SalesInvoices.EffectiveDate) AS Date
,Products.ProductId AS [Product Id]
,Products.ProductDescription
,Classifications.ClassificationId
,SalesInvoiceItems.Quantity
,SalesInvoiceItems.ItemValue
,CommissionClasses.CommissionClassId
FROM dbo.SalesInvoiceItems
INNER JOIN dbo.SalesInvoices
ON SalesInvoiceItems.SalesInvoice = SalesInvoices.SalesInvoice
INNER JOIN dbo.Products
ON SalesInvoiceItems.Product = Products.Product
INNER JOIN dbo.Classifications
ON Products.Classification = Classifications.Classification
CROSS JOIN dbo.CommissionClasses
INNER JOIN dbo.Customers
ON Customers.CommissionClass = CommissionClasses.CommissionClass
AND SalesInvoices.Customer = Customers.Customer
WHERE SalesInvoices.EffectiveDate >= @startdate
AND SalesInvoices.EffectiveDate < @endate
GROUP BY Products.ProductId
,Products.ProductDescription
,Classifications.ClassificationId
,SalesInvoiceItems.Quantity
,SalesInvoiceItems.ItemValue
,CommissionClasses.CommissionClassId
ORDER BY SalesInvoiceItems.Quantity DESC
If you want a number of optional criteria parameters the easiest way is probably
DECLARE @Param1 somedatatype,
@Param2 somedatatype,
...
SET @ParamX = 'SomeValue'
SELECT ...
FROM ...
WHERE (@Param1 IS NULL OR Col1 = @Param1) -- Exact match
AND (@Param2 IS NULL OR TextCol2 LIKE '%' + @Param2 + '%') -- Wilcard (the query will be slow!)
AND (@Param3Start IS NULL OR DateCol3 >= @Param3Start) -- Start date
AND (@Param3End IS NULL OR DateCol3 < @Param3End) -- End date (set to one day after last required date)
any @Param that is NULL will match "anything". Note that this is not an efficient method of writing a query, so on a large data table it would be better to use a more efficient method, but it will do to get you going, although beware of that if you have users actively using the database that you are querying.
Not sure what that is trying to achieve, but there is risk of cartesian join. Maybe it should be this?
INNER JOIN dbo.Customers
ON SalesInvoices.Customer = Customers.Customer
INNER JOIN dbo.CommissionClasses
ON Customers.CommissionClass = CommissionClasses.CommissionClass
if Customers.CommissionClass can be NULL, or not have a valid value in CommissionClasses (but that seems pretty improbable!) then use LEFT OUTER JOIN dbo.CommissionClasses instead
Well I'm having loads of Fun and begining to understand with the help of you guys and a lot of YOUTUBE , So I have set up the below and all is working but , how do I get the report to show a drop down of the ClassificationID's to choose from rather than having to know them ?
USE Training
GO
ALTER PROC SalesByValue
(
@StartDate AS SMALLDATETIME
, @EndDate AS SMALLDATETIME
, @ClassificationId AS VARCHAR(MAX)
,@CommisionClassId AS VARCHAR(MAX)
)
AS
BEGIN
SELECT
Classifications.ClassificationId
,SalesOrderItems.Quantity
,SalesOrderItems.ItemValue
,Products.ProductId
,Products.ProductDescription
,CommissionClasses.CommissionClassId
,MAX(SalesOrders.EffectiveDate) AS expr1
FROM dbo.SalesOrderItems
INNER JOIN dbo.SalesOrders
ON SalesOrderItems.SalesOrder = SalesOrders.SalesOrder
INNER JOIN dbo.Products
ON SalesOrderItems.Product = Products.Product
INNER JOIN dbo.Classifications
ON Products.Classification = Classifications.Classification
INNER JOIN dbo.Customers
ON SalesOrders.Customer = Customers.Customer
INNER JOIN dbo.CommissionClasses
ON Customers.CommissionClass = CommissionClasses.CommissionClass
WHERE
SalesOrders.EffectiveDate >= @StartDate AND
SalesOrders.EffectiveDate < @EndDate AND
ClassificationId = @ClassificationID AND
CommissionClassId = @CommisionClassId
GROUP BY Classifications.ClassificationId
,SalesOrderItems.Quantity
,SalesOrderItems.ItemValue
,CommissionClasses.CommissionClassId
,Products.ProductDescription
,Products.ProductId
ORDER BY SalesOrderItems.ItemValue DESC
END
Not something I know about, but my suggestion would be: Can you put a SQL SELECT statement behind that Select List (or "Attach some SQL to it" or something like that)
Don't use varchar(MAX) if you don't need to, and ideally size the @Parameter the same as the column or "not too much bigger" (to allow for changes in the APP).
I have read that SQL's allocation of memory for such things is generous, and there is definite benefit in keeping the size of such objects under control.
That apart, the way that varchar(MAX) is handled (perhaps more particularly for columns in tables, and maybe not so relevant to @Paramaters) is dramatically less efficient - i.e. large data being stored out-of-row for tables, which makes it slower to "handle" than if it is in-row.
Varchar(8000) or NVarchar(4000) is the max for "in row"
The SQL for your report requires all 4 parameters to be provided (and match the data) so that does not allow for any "Blank" or "wildcard" type entries. If that's what you want that's fine of course, but you might want to consider "blank" / "wildcard" too
The other reporting requirement is "Any of THESE MULTIPLE Classification IDs that I select" which is often a user requirement, and is a bit more of a pain to program for. If you are going to encounter those you might like to try them out at your Learning Stage, so that you have a Template / proven-method once you come to build real ones.
On that tack you may also want to look at a Template for your stored procedures
I suggest you ought to have SET NOCOUNT ON at the top (so you don't get any information-only messages at the Application end), and some sort of error handling. - e.g. THROW/CATCH
We've developed our templates over decades, and they pre-date THROW/CATCH ... I ought really to find the best-of-breed out there and see whether we should bring ours up to modern standards.
Thanks for the explantation of VARCHAR I have now limited this , I'm really struggling with the Parameter side of things my main purpose for dropping myself into SQL is for Report in purpose and i have 100's of reports that came with the ERP database but every one is of course structured differently , So Its this that I'm trying to get my head around . The parameters In My report need me to manually type the ClassificationId but I know its possible for these to be drop down selection .I just cant work out how . If I try and set the parameter in report builder to the dataset i get this error so i'm assuming its something in the query that is not right for the results i'm trying to achieve
OK, so (from that error screenshot) its looks like you are using SSRS for your reports. I expect someone here will be able to answer your question about drop-down Select List. But in case they've tuned out of this thread you might want to ask it as a brand new question (cut and paste from this one if you like)
