SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


UDF Table


UDF Table

Author
Message
llcooldre75
llcooldre75
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 12
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



llcooldre75
llcooldre75
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 12
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.
llcooldre75
llcooldre75
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 12
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
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95227 Visits: 33013
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
llcooldre75
llcooldre75
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 12
Here is the structure of my tables i am using.

[code= "sql"]

CREATE TABLE Billing (

CUSTCODE VARCHAR,
SALESID VARCHAR,
AMTPAIDSOFAR INT,
INVDATE DATETIME,


);



CREATE TABLE CUSTCODE (

CUSTCODE VARCHAR,
CUSTNAME VARCHAR,
SALESID VARCHAR,

);

[/code]

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?

[code = "sql"]

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

[/code]
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17107 Visits: 6431
llcooldre75 (1/15/2013)
Here is the structure of my tables i am using.

[code= "sql"]

CREATE TABLE Billing (

CUSTCODE VARCHAR,
SALESID VARCHAR,
AMTPAIDSOFAR INT,
INVDATE DATETIME,


);



CREATE TABLE CUSTCODE (

CUSTCODE VARCHAR,
CUSTNAME VARCHAR,
SALESID VARCHAR,

);

[/code]

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?

[code = "sql"]

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

[/code]


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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91524 Visits: 38951
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




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17107 Visits: 6431
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 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




Lynn - I agree with you. My brain was thinking in terms of a DATE datatype.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91524 Visits: 38951
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 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




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




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search