InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 13:58:48 GMT20http://www.sqlservercentral.com/Forums/Topic1406942-1292-1.aspx[quote][b]dwain.c (1/15/2013)[/b][hr][quote][b]Lynn Pettis (1/15/2013)[/b][hr][quote][b]dwain.c (1/15/2013)[/b][hr][quote][b]Lynn Pettis (1/15/2013)[/b][hr][quote][b]dwain.c (1/15/2013)[/b][hr][quote][b]Lynn Pettis (1/15/2013)[/b][hr][quote][b]dwain.c (1/15/2013)[/b][hr]Yes indeed.It appears the force is not with me this morning, oh master. :-P[/quote]Does this mean you have looked at the thread you and I posted on?[/quote]Master Yoda. I know not that of which you speak.[/quote]The bipolar users.[/quote]I try not to look to closely at the threads I post to... All that information overwhelms me. :hehe::-P[/quote]So you're not intrigued by what I may have posted over there?[/quote]Thought this was the bi-polar thread! Shows how off my game I am.Looking at it now and I see what you mean. I saw what you posted just didn't run it. Interesting result.[/quote]No, I think we just made this one bipolar. :w00t:Tue, 15 Jan 2013 22:58:48 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1406942-1292-1.aspx[quote][b]Lynn Pettis (1/15/2013)[/b][hr][quote][b]dwain.c (1/15/2013)[/b][hr][quote][b]Lynn Pettis (1/15/2013)[/b][hr][quote][b]dwain.c (1/15/2013)[/b][hr][quote][b]Lynn Pettis (1/15/2013)[/b][hr][quote][b]dwain.c (1/15/2013)[/b][hr]Yes indeed.It appears the force is not with me this morning, oh master. :-P[/quote]Does this mean you have looked at the thread you and I posted on?[/quote]Master Yoda. I know not that of which you speak.[/quote]The bipolar users.[/quote]I try not to look to closely at the threads I post to... All that information overwhelms me. :hehe::-P[/quote]So you're not intrigued by what I may have posted over there?[/quote]Thought this was the bi-polar thread! Shows how off my game I am.Looking at it now and I see what you mean. I saw what you posted just didn't run it. Interesting result.Tue, 15 Jan 2013 22:26:22 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1406942-1292-1.aspx[quote][b]dwain.c (1/15/2013)[/b][hr][quote][b]Lynn Pettis (1/15/2013)[/b][hr][quote][b]dwain.c (1/15/2013)[/b][hr][quote][b]Lynn Pettis (1/15/2013)[/b][hr][quote][b]dwain.c (1/15/2013)[/b][hr]Yes indeed.It appears the force is not with me this morning, oh master. :-P[/quote]Does this mean you have looked at the thread you and I posted on?[/quote]Master Yoda. I know not that of which you speak.[/quote]The bipolar users.[/quote]I try not to look to closely at the threads I post to... All that information overwhelms me. :hehe::-P[/quote]So you're not intrigued by what I may have posted over there?Tue, 15 Jan 2013 22:16:21 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1406942-1292-1.aspx[quote][b]Lynn Pettis (1/15/2013)[/b][hr][quote][b]dwain.c (1/15/2013)[/b][hr][quote][b]Lynn Pettis (1/15/2013)[/b][hr][quote][b]dwain.c (1/15/2013)[/b][hr]Yes indeed.It appears the force is not with me this morning, oh master. :-P[/quote]Does this mean you have looked at the thread you and I posted on?[/quote]Master Yoda. I know not that of which you speak.[/quote]The bipolar users.[/quote]I try not to look to closely at the threads I post to... All that information overwhelms me. :hehe::-PTue, 15 Jan 2013 21:56:56 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1406942-1292-1.aspx[quote][b]dwain.c (1/15/2013)[/b][hr][quote][b]Lynn Pettis (1/15/2013)[/b][hr][quote][b]dwain.c (1/15/2013)[/b][hr]Yes indeed.It appears the force is not with me this morning, oh master. :-P[/quote]Does this mean you have looked at the thread you and I posted on?[/quote]Master Yoda. I know not that of which you speak.[/quote]The bipolar users.Tue, 15 Jan 2013 21:08:43 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1406942-1292-1.aspx[quote][b]Lynn Pettis (1/15/2013)[/b][hr][quote][b]dwain.c (1/15/2013)[/b][hr]Yes indeed.It appears the force is not with me this morning, oh master. :-P[/quote]Does this mean you have looked at the thread you and I posted on?[/quote]Master Yoda. I know not that of which you speak.Tue, 15 Jan 2013 21:07:34 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1406942-1292-1.aspx[quote][b]dwain.c (1/15/2013)[/b][hr]Yes indeed.It appears the force is not with me this morning, oh master. :-P[/quote]Does this mean you have looked at the thread you and I posted on?Tue, 15 Jan 2013 21:02:37 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1406942-1292-1.aspxYes indeed.It appears the force is not with me this morning, oh master. :-PTue, 15 Jan 2013 21:00:00 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1406942-1292-1.aspx[quote][b]dwain.c (1/15/2013)[/b][hr][quote][b]Lynn Pettis (1/15/2013)[/b][hr][quote][b]dwain.c (1/15/2013)[/b][hr]2. When you check for a date range, BETWEEN is not recommended. You should use >= @StartDate and <= @EndDate.[/quote]I'd like to expand on this one a bit. Actually, it should be:[code="sql"]... DateColumn >= @ StartDate and DateColumn < @EndDate[/code]For example, all [strike]records[/strike] rows where the data is in the month of March 2012:[code="sql"]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[/code][/quote]Lynn - I agree with you. My brain was thinking in terms of a DATE datatype.[/quote]In which case, BETWEEN would work just fine, wouldn't it (as long as DateColumn is also defined as a DATE data type)?[code="sql"]DECLARE @StartDate DATE = '20120301', @EndDate DATE = '20120331';... DateColumn BETWEEN @StartDate AND @EndDate... DateColumn >= @StartDate and DateColumn <= @EndDate[/code]Tue, 15 Jan 2013 20:57:35 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1406942-1292-1.aspx[quote][b]Lynn Pettis (1/15/2013)[/b][hr][quote][b]dwain.c (1/15/2013)[/b][hr]2. When you check for a date range, BETWEEN is not recommended. You should use >= @StartDate and <= @EndDate.[/quote]I'd like to expand on this one a bit. Actually, it should be:[code="sql"]... DateColumn >= @ StartDate and DateColumn < @EndDate[/code]For example, all [strike]records[/strike] rows where the data is in the month of March 2012:[code="sql"]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[/code][/quote]Lynn - I agree with you. My brain was thinking in terms of a DATE datatype.Tue, 15 Jan 2013 20:44:26 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1406942-1292-1.aspx[quote][b]dwain.c (1/15/2013)[/b][hr]2. When you check for a date range, BETWEEN is not recommended. You should use >= @StartDate and <= @EndDate.[/quote]I'd like to expand on this one a bit. Actually, it should be:[code="sql"]... DateColumn >= @ StartDate and DateColumn < @EndDate[/code]For example, all [strike]records[/strike] rows where the data is in the month of March 2012:[code="sql"]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[/code]Tue, 15 Jan 2013 20:38:47 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1406942-1292-1.aspx[quote][b]llcooldre75 (1/15/2013)[/b][hr]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 MostRecentFROM Billing INNER JOIN CustCode ON Billing.CustCode = CustCode.CustCodeWHERE (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][/quote]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.[code="sql"]CREATE TABLE #Billing (CUSTCODE VARCHAR ,SALESID VARCHAR ,AMTPAIDSOFAR INT ,INVDATE DATETIME);CREATE TABLE #CUSTCODE (CUSTCODE VARCHAR ,CUSTNAME VARCHAR(20) ,SALESID VARCHAR);INSERT INTO #CUSTCODESELECT '1', 'Dwain', '1' UNION ALL SELECT '2', 'llcooldre', '2' UNION ALL SELECT '3', 'Dr Dobbs', '3'INSERT INTO #BillingSELECT '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 ENDFROM #CUSTCODE aLEFT JOIN EligibleInvoices b ON a.CUSTCODE = b.CUSTCODEWHERE MostRecent = 1 AND AMTPAIDSOFAR > 0DROP TABLE #BillingDROP TABLE #CUSTCODE[/code]Good luck!Tue, 15 Jan 2013 18:44:28 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1406942-1292-1.aspxHere 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 MostRecentFROM Billing INNER JOIN CustCode ON Billing.CustCode = CustCode.CustCodeWHERE (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]Tue, 15 Jan 2013 07:31:13 GMTllcooldre75http://www.sqlservercentral.com/Forums/Topic1406942-1292-1.aspxNothing 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.Tue, 15 Jan 2013 04:32:34 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic1406942-1292-1.aspxFor 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 phoneMon, 14 Jan 2013 15:44:36 GMTllcooldre75http://www.sqlservercentral.com/Forums/Topic1406942-1292-1.aspxThank 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.Mon, 14 Jan 2013 15:35:26 GMTllcooldre75http://www.sqlservercentral.com/Forums/Topic1406942-1292-1.aspxPlease 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_BillingASSELECT * FROM Billing WHERE NOT EXISTS (SELECT * FROM Billing WHERE inv_date >= DATEADD (DAY, -365, CURRENT_TIMESTAMP) Mon, 14 Jan 2013 15:21:37 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1406942-1292-1.aspxHi 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[code="sql"] 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 mostRFROM 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[/code]Mon, 14 Jan 2013 14:10:12 GMTllcooldre75