Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

UDF Table Expand / Collapse
Author
Message
Posted Monday, January 14, 2013 2:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 18, 2013 6:53 AM
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


Post #1406942
Posted Monday, January 14, 2013 3:21 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

This is minimal polite behavior on SQL forums.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it.

>> 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. <<

That is a good spec!

>> 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.inv_date field [sic] there was a sale. <<

No. SQL is declarative not procedural. We use predicates, not functions. Without any DDL, we can only guess. You are writing code that makes sense for punch cards; you punch out a PHYSICAL report but you use a table and not cards!

SQL programmers would create VIEW that is always current. Why did you make it so hard to define a subset with a simple predicate?

CREATE VIEW New_Customer_Billing
AS
SELECT *
FROM Billing
WHERE NOT EXISTS
(SELECT *
FROM Billing
WHERE inv_date >= DATEADD (DAY, -365, CURRENT_TIMESTAMP)



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1406962
Posted Monday, January 14, 2013 3:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 18, 2013 6:53 AM
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.
Post #1406965
Posted Monday, January 14, 2013 3:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 18, 2013 6:53 AM
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
Post #1406969
Posted Tuesday, January 15, 2013 4:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 14,802, Visits: 27,275
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1407162
Posted Tuesday, January 15, 2013 7:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 18, 2013 6:53 AM
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]
Post #1407271
Posted Tuesday, January 15, 2013 6:44 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:03 PM
Points: 3,590, Visits: 5,098
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!
Post #1407541
Posted Tuesday, January 15, 2013 8:38 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:53 PM
Points: 22,492, Visits: 30,195
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 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)
Post #1407557
Posted Tuesday, January 15, 2013 8:44 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:03 PM
Points: 3,590, Visits: 5,098
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!
Post #1407558
Posted Tuesday, January 15, 2013 8:57 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 5:53 PM
Points: 22,492, Visits: 30,195
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





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)
Post #1407563
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse