January 14, 2013 at 2:10 pm
Hi i have little experience with sql server and i have just been thrown into the fire at my job to learn. The problem I am having is we are trying create a report that shows new customers. If a customer bought something we check and see if that customer has an invoice within 365 days of their most recent invoice. If the customer does not have a sale, then they are considered a new customer.
What I decided to do was create a function that finds the most recent invoice date and put that into a table, and then check within 365 days of that Max Invoice date to see if inside of the billing.invdate field there was a sale. I am brain fried and don't know what i can do with this table. i tried to do a cross apply. to do a join on CustCode.CustName but either i dont know what I am doing, or it doesn't work with Visual Studio 2012. PLEASE HELP!!
Thank you to anyone that replies
ALTER FUNCTION dbo.checkMaxInv
(
@starting Datetime,
@ending Datetime
)
RETURNS @CustMostRecent TABLE (CustCode varchar (12) primary key,
CustName varchar (30),
SalesID varchar (12),
InvDate Datetime)
AS
BEGIN
INSERT INTO @CustMostRecent
SELECT Billing.CustCode, CustCode.CustName, CustCode.SalesID, MAX(Billing.InvDate) AS mostR
FROM Billing INNER JOIN CustCode ON Billing.CustCode = CustCode.CustCode
WHERE (Billing.InvDate BETWEEN @starting AND @ending) AND (Billing.InvoiceTotal > 0)
GROUP BY Billing.CustCode, CustCode.CustName, CustCode.SalesID
RETURN
END
January 14, 2013 at 3:35 pm
Thank you for your reply. I created a view at first, but the problem with that was it needed to accept parameters for a start date and ending date. So the only thing I thought that would help me is to create a udf table that passed in those parameters.
January 14, 2013 at 3:44 pm
For some reason I had a lot of problems with the "where not exists" maybe I mistyped. But with the database we are using there are no primary keys or constraints with the tables I am using. There is a lot of data but I just started working here last week and it looks like they are using an ERP system. Please forgive me for my posts I left work, my internet at home isn't working so I am using my phone
January 15, 2013 at 4:32 am
Nothing like getting thrown into the fire. Welcome. We try to help out here as best we can.
From the sounds of it, no constraints especially, you're going to be dealing with quite a lot. I'd suggest reconfiguring and going from the basics. Instead of trying to use a table valued function, just incorporate this into your existing query (I'm assuming you're trying to add this to a query). Either just join to the tables you're interested in and then filter based on the dates, or use this as a derived table.
From what you typed, it sounds like this is part of a greater query. We could help you more if we knew what the greater query looked like.
Again, welcome to the party. Once the fear and trepidation wear off, it's fun.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 15, 2013 at 7:31 am
Here is the structure of my tables i am using.
CREATE TABLE Billing (
CUSTCODE VARCHAR,
SALESID VARCHAR,
AMTPAIDSOFAR INT,
INVDATE DATETIME,
);
CREATE TABLE CUSTCODE (
CUSTCODE VARCHAR,
CUSTNAME VARCHAR,
SALESID VARCHAR,
);
With my query I wanted to get the customers most recent invoice date and check within 365 days of the most recent date. I declared parameters @starting and @ending that will be databound to an ASP.net form so the manager can put in their own interval. Everything worked to find the most recent invoice date until I added the not exists clause. I am getting an error saying Invalid column name 'MostRecent', but I thought that MostRecent was declared in the beginning?
SELECT Billing.CustCode, CustCode.CustName, CustCode.SalesID, MAX(Billing.InvDate) AS MostRecent
FROM Billing INNER JOIN CustCode ON Billing.CustCode = CustCode.CustCode
WHERE (Billing.InvDate BETWEEN @starting AND @ending) AND (Billing.AmtPaidSoFar > 0) AND (NOT EXISTS
(SELECT CustCode, InvDate, AmtPaidSoFar
FROM Billing AS Billing1
WHERE (InvDate >= DATEADD(DAY, - 365, MostRecent))))
GROUP BY Billing.CustCode, CustCode.CustName, CustCode.SalesID
January 15, 2013 at 6:44 pm
llcooldre75 (1/15/2013)
Here is the structure of my tables i am using.
CREATE TABLE Billing (
CUSTCODE VARCHAR,
SALESID VARCHAR,
AMTPAIDSOFAR INT,
INVDATE DATETIME,
);
CREATE TABLE CUSTCODE (
CUSTCODE VARCHAR,
CUSTNAME VARCHAR,
SALESID VARCHAR,
);
With my query I wanted to get the customers most recent invoice date and check within 365 days of the most recent date. I declared parameters @starting and @ending that will be databound to an ASP.net form so the manager can put in their own interval. Everything worked to find the most recent invoice date until I added the not exists clause. I am getting an error saying Invalid column name 'MostRecent', but I thought that MostRecent was declared in the beginning?
SELECT Billing.CustCode, CustCode.CustName, CustCode.SalesID, MAX(Billing.InvDate) AS MostRecent
FROM Billing INNER JOIN CustCode ON Billing.CustCode = CustCode.CustCode
WHERE (Billing.InvDate BETWEEN @starting AND @ending) AND (Billing.AmtPaidSoFar > 0) AND (NOT EXISTS
(SELECT CustCode, InvDate, AmtPaidSoFar
FROM Billing AS Billing1
WHERE (InvDate >= DATEADD(DAY, - 365, MostRecent))))
GROUP BY Billing.CustCode, CustCode.CustName, CustCode.SalesID
I confess I don't totally understand your requirement, particularly how you are integrating the date range into the query, but I do see a couple of problems with the query you posted.
1. The alias MostRecent created in the outer query will not be accessible within the subquery you are checking for NOT EXISTS - that is why you're getting the error reported.
2. When you check for a date range, BETWEEN is not recommended. You should use >= @StartDate and <= @EndDate.
The below won't solve your problem entirely and I had to make up some sample data that may not be representative, however if you look at what I've done perhaps it will help you. It returns a NULL in the MostRecent column for any customer not having an invoice within 365 days of the start date.
CREATE TABLE #Billing
(CUSTCODE VARCHAR
,SALESID VARCHAR
,AMTPAIDSOFAR INT
,INVDATE DATETIME);
CREATE TABLE #CUSTCODE
(CUSTCODE VARCHAR
,CUSTNAME VARCHAR(20)
,SALESID VARCHAR);
INSERT INTO #CUSTCODE
SELECT '1', 'Dwain', '1' UNION ALL SELECT '2', 'llcooldre', '2' UNION ALL SELECT '3', 'Dr Dobbs', '3'
INSERT INTO #Billing
SELECT '1', '1', 400, '2012-01-01'
UNION ALL SELECT '1', '2', 400, '2012-01-20'
UNION ALL SELECT '2', '1', 400, '2012-01-31'
UNION ALL SELECT '2', '2', 400, '2012-02-28'
UNION ALL SELECT '2', '3', 400, '2013-01-01'
UNION ALL SELECT '3', '1', 400, '2011-01-01'
UNION ALL SELECT '3', '2', 400, '2011-02-01'
UNION ALL SELECT '3', '3', 400, '2011-03-01'
DECLARE @StartDate DATE = '2012-12-01'
,@EndDate DATE = '2012-12-31'
;WITH EligibleInvoices AS (
SELECT CUSTCODE, SALESID, AMTPAIDSOFAR, INVDATE
,MostRecent=ROW_NUMBER() OVER (PARTITION BY CUSTCODE ORDER BY INVDATE DESC)
FROM #Billing)
SELECT a.CUSTCODE, a.CUSTNAME
,MostRecent=CASE WHEN DATEADD(day, 365, INVDATE) > @StartDate THEN INVDATE ELSE NULL END
FROM #CUSTCODE a
LEFT JOIN EligibleInvoices b ON a.CUSTCODE = b.CUSTCODE
WHERE MostRecent = 1 AND AMTPAIDSOFAR > 0
DROP TABLE #Billing
DROP TABLE #CUSTCODE
Good luck!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 15, 2013 at 8:38 pm
dwain.c (1/15/2013)
2. When you check for a date range, BETWEEN is not recommended. You should use >= @StartDate and <= @EndDate.
I'd like to expand on this one a bit. Actually, it should be:
... DateColumn >= @ StartDate and DateColumn < @EndDate
For example, all records rows where the data is in the month of March 2012:
declare @StartDate datetime = '20120301', -- Using datetime as the data is date/time oriented
@EndDate datetime = '20120401'; -- or dateadd(mm,1,@StartDate)
... DateColumn >= @ StartDate and DateColumn < @EndDate
January 15, 2013 at 8:44 pm
Lynn Pettis (1/15/2013)
dwain.c (1/15/2013)
2. When you check for a date range, BETWEEN is not recommended. You should use >= @StartDate and <= @EndDate.I'd like to expand on this one a bit. Actually, it should be:
... DateColumn >= @ StartDate and DateColumn < @EndDate
For example, all
recordsrows where the data is in the month of March 2012:
declare @StartDate datetime = '20120301', -- Using datetime as the data is date/time oriented
@EndDate datetime = '20120401'; -- or dateadd(mm,1,@StartDate)
... DateColumn >= @ StartDate and DateColumn < @EndDate
Lynn - I agree with you. My brain was thinking in terms of a DATE datatype.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 15, 2013 at 8:57 pm
dwain.c (1/15/2013)
Lynn Pettis (1/15/2013)
dwain.c (1/15/2013)
2. When you check for a date range, BETWEEN is not recommended. You should use >= @StartDate and <= @EndDate.I'd like to expand on this one a bit. Actually, it should be:
... DateColumn >= @ StartDate and DateColumn < @EndDate
For example, all
recordsrows where the data is in the month of March 2012:
declare @StartDate datetime = '20120301', -- Using datetime as the data is date/time oriented
@EndDate datetime = '20120401'; -- or dateadd(mm,1,@StartDate)
... DateColumn >= @ StartDate and DateColumn < @EndDate
Lynn - I agree with you. My brain was thinking in terms of a DATE datatype.
In which case, BETWEEN would work just fine, wouldn't it (as long as DateColumn is also defined as a DATE data type)?
DECLARE @StartDate DATE = '20120301',
@EndDate DATE = '20120331';
... DateColumn BETWEEN @StartDate AND @EndDate
... DateColumn >= @StartDate and DateColumn <= @EndDate
January 15, 2013 at 9:00 pm
Yes indeed.
It appears the force is not with me this morning, oh master. 😛
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 15, 2013 at 9:02 pm
dwain.c (1/15/2013)
Yes indeed.It appears the force is not with me this morning, oh master. 😛
Does this mean you have looked at the thread you and I posted on?
January 15, 2013 at 9:07 pm
Lynn Pettis (1/15/2013)
dwain.c (1/15/2013)
Yes indeed.It appears the force is not with me this morning, oh master. 😛
Does this mean you have looked at the thread you and I posted on?
Master Yoda. I know not that of which you speak.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 15, 2013 at 9:08 pm
dwain.c (1/15/2013)
Lynn Pettis (1/15/2013)
dwain.c (1/15/2013)
Yes indeed.It appears the force is not with me this morning, oh master. 😛
Does this mean you have looked at the thread you and I posted on?
Master Yoda. I know not that of which you speak.
The bipolar users.
January 15, 2013 at 9:56 pm
Lynn Pettis (1/15/2013)
dwain.c (1/15/2013)
Lynn Pettis (1/15/2013)
dwain.c (1/15/2013)
Yes indeed.It appears the force is not with me this morning, oh master. 😛
Does this mean you have looked at the thread you and I posted on?
Master Yoda. I know not that of which you speak.
The bipolar users.
I try not to look to closely at the threads I post to... All that information overwhelms me. :hehe::-P
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 15, 2013 at 10:16 pm
dwain.c (1/15/2013)
Lynn Pettis (1/15/2013)
dwain.c (1/15/2013)
Lynn Pettis (1/15/2013)
dwain.c (1/15/2013)
Yes indeed.It appears the force is not with me this morning, oh master. 😛
Does this mean you have looked at the thread you and I posted on?
Master Yoda. I know not that of which you speak.
The bipolar users.
I try not to look to closely at the threads I post to... All that information overwhelms me. :hehe::-P
So you're not intrigued by what I may have posted over there?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply