Complex Query Undesired Results...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Complex Query Undesired Results...

Author

Topic

jforward5
Starting Member

19 Posts

Posted - 2009-01-07 : 17:54:54

I wrote a complex query and want to get only the DISTINCT values. But it appears that the query is running the math functions I have in the query twice for each record pulled, and to top it off the results are not identical they are different for the same row.

This is my query:

SELECT DISTINCT Recaps.SMonth, Recaps.SDay, Recaps.SYear, Recaps.SDate, Recaps.PTD_PROFIT, Recaps.PTD_SALES, BILLING_DAYS.BDaysMonth,
ROW_NUMBER() OVER(ORDER BY Recaps.SYear) AS BDay,
dbo.Percentages(ROW_NUMBER() OVER(ORDER BY Recaps.SLM_NUMBER), BILLING_DAYS.BDaysMonth) as PercentageBilled,
BILLING_DAYS.BDaysMonth, dbo.Percentages(Recaps.PTD_PROFIT, Recaps.PTD_SALES) as ProfitPercent,
dbo.Percentages(Recaps.PTD_PROFIT, SalesGoals.MonthlyProfitGoal) as PercentGoalReached

FROM Recaps INNER JOIN
BILLING_DAYS ON Recaps.SYear = BILLING_DAYS.DYear AND Recaps.SMonth = BILLING_DAYS.DMonth INNER JOIN
SalesGoals ON Recaps.SLM_NUMBER = SalesGoals.SLM_NUMBER

WHERE (Recaps.SLM_NUMBER = @SLM_NUMBER) AND (Recaps.SMonth = @SMonth) AND (Recaps.SYear = @SYear)

Here are my results:

1 5 2009 2009-01-05 04:00:03.100 0.0001 0.0001 22 1 0.0454545454545455 22 1 2.11640211640212E-09
1 5 2009 2009-01-05 04:00:03.100 0.0001 0.0001 22 3 0.136363636363636 22 1 1
1 6 2009 2009-01-06 04:00:02.947 811.03 2359.02 22 2 0.0909090909090909 22 0.34379954387839 0.0171646560846561
1 6 2009 2009-01-06 04:00:02.947 811.03 2359.02 22 4 0.181818181818182 22 0.34379954387839 8110300

Can anyone help me with this query? My results should only have 2 records and they should be unique.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-01-08 : 06:53:06

Your rows are clearly distinct with no duplicates. What do you expect?
In any case, selecting row_number will never give you duplicate rows will it?

jforward5
Starting Member

19 Posts

Posted - 2009-01-08 : 09:08:41

They are not distinct. There should only be 2 rows returned. There are only 2 rows the query can pull. My problem is that is has pulled those 2 records and for each one run my functions twice on them and return 2 completely different results using the same numbers and the same mathmatical function.

jforward5
Starting Member

19 Posts

Posted - 2009-01-08 : 09:12:26

What is worse is if I remove my functions from the query including the row_number system function it gives me the 2 rows I expect to see with the math and row_number functions in place.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 09:49:39

as you can cleraly see values of function results are different for two rows (like 0.0454545454545455 & 0.136363636363636), so they are distinct. what's result that you're expecting?

jforward5
Starting Member

19 Posts

Posted - 2009-01-08 : 09:56:23

I am expecting 2 records. There are only 2 records that the Query can pull from, only 2 that match the criteria. It is taking those 2 and running the function twice on each of them for some reason. Even the row_number system function is adding 2 records to the results. My function does division and once I display the results I am formating them to percent.

jforward5
Starting Member

19 Posts

Posted - 2009-01-08 : 09:59:40

Could mu math function be the problem?

ALTER FUNCTION [dbo].[Percentages]
(
-- Add the parameters for the function here
@exp1 sql_variant,
@exp2 sql_variant
)
RETURNS float
AS
BEGIN

RETURN (cast(@exp1 as float)/cast(@exp2 as float))

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 10:04:40

quote:
Originally posted by jforward5

I am expecting 2 records. There are only 2 records that the Query can pull from, only 2 that match the criteria. It is taking those 2 and running the function twice on each of them for some reason. Even the row_number system function is adding 2 records to the results. My function does division and once I display the results I am formating them to percent.

post what result you expect out of above 4 rows.

jforward5
Starting Member

19 Posts

Posted - 2009-01-08 : 10:42:23

quote:
Originally posted by visakh16

quote:
Originally posted by jforward5

I am expecting 2 records. There are only 2 records that the Query can pull from, only 2 that match the criteria. It is taking those 2 and running the function twice on each of them for some reason. Even the row_number system function is adding 2 records to the results. My function does division and once I display the results I am formating them to percent.

post what result you expect out of above 4 rows.

I expect these 2 Results:

1 5 2009 2009-01-05 04:00:03.100 0.0001 0.0001 22 1 0.0454545454545455 22 1 2.11640211640212E-09
1 6 2009 2009-01-06 04:00:02.947 811.03 2359.02 22 2 0.0909090909090909 22 0.34379954387839 0.0171646560846561

I do not know why the other results are generated. If I could figure that out I would be able to fix the query...Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 11:20:51

whats the purpose of below?

bo.Percentages(ROW_NUMBER() OVER(ORDER BY Recaps.SLM_NUMBER), BILLING_DAYS.BDaysMonth) as PercentageBilled

jforward5
Starting Member

19 Posts

Posted - 2009-01-08 : 11:42:40

quote:
Originally posted by visakh16

whats the purpose of below?

bo.Percentages(ROW_NUMBER() OVER(ORDER BY Recaps.SLM_NUMBER), BILLING_DAYS.BDaysMonth) as PercentageBilled

It divides the number of records (billing days) by the number of days in the month.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 11:47:42

why passing generated sequence number as one of parameter? this will obviously return distinct value for each record and will cause returning duplicate rows with same value for other fields (which is what you observes)

jforward5
Starting Member

19 Posts

Posted - 2009-01-08 : 12:00:12

quote:
Originally posted by visakh16

why passing generated sequence number as one of parameter? this will obviously return distinct value for each record and will cause returning duplicate rows with same value for other fields (which is what you observes)

Even when I remove the lines with the auto generated fields it still duplicates and creates distinct results. I need the auto generated line for display purposes.

jforward5
Starting Member

19 Posts

Posted - 2009-01-08 : 14:48:00

Here is the results with only the row_number system function. This is the query getting these results:

SELECT DISTINCT Recaps.SMonth, Recaps.SDay, Recaps.SYear, Recaps.SDate, Recaps.PTD_PROFIT, Recaps.PTD_SALES, BILLING_DAYS.BDaysMonth,
ROW_NUMBER() OVER(ORDER BY Recaps.SYear) AS BDay

FROM Recaps INNER JOIN
BILLING_DAYS ON Recaps.SYear = BILLING_DAYS.DYear AND Recaps.SMonth = BILLING_DAYS.DMonth INNER JOIN
SalesGoals ON Recaps.SLM_NUMBER = SalesGoals.SLM_NUMBER

WHERE (Recaps.SLM_NUMBER = @SLM_NUMBER) AND (Recaps.SMonth = @SMonth) AND (Recaps.SYear = @SYear)

Here are the results:

1 5 2009 2009-01-05 04:00:03.100 0.0001 0.0001 22 1
1 5 2009 2009-01-05 04:00:03.100 0.0001 0.0001 22 2
1 6 2009 2009-01-06 04:00:02.947 811.03 2359.02 22 3
1 6 2009 2009-01-06 04:00:02.947 811.03 2359.02 22 4
1 7 2009 2009-01-07 04:00:02.113 978.81 2887.79 22 5
1 7 2009 2009-01-07 04:00:02.113 978.81 2887.79 22 6

Now with only the basic query. Here is the Query first:

SELECT DISTINCT Recaps.SMonth, Recaps.SDay, Recaps.SYear, Recaps.SDate, Recaps.PTD_PROFIT, Recaps.PTD_SALES, BILLING_DAYS.BDaysMonth

FROM Recaps INNER JOIN
BILLING_DAYS ON Recaps.SYear = BILLING_DAYS.DYear AND Recaps.SMonth = BILLING_DAYS.DMonth INNER JOIN
SalesGoals ON Recaps.SLM_NUMBER = SalesGoals.SLM_NUMBER

WHERE (Recaps.SLM_NUMBER = @SLM_NUMBER) AND (Recaps.SMonth = @SMonth) AND (Recaps.SYear = @SYear)

Here is the results:

1 5 2009 2009-01-05 04:00:03.100 0.0001 0.0001 22
1 6 2009 2009-01-06 04:00:02.947 811.03 2359.02 22
1 7 2009 2009-01-07 04:00:02.113 978.81 2887.79 22

There are now 3 possible results in the db since I have a SSIS package that runs every night to compile data from our backend system. Yesterday there was 2 possible results today 3 and tomorrow 4 and so on.

Some one has to see what ever it is I am missing...I can't seem to find the problem.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-01-08 : 22:10:33

Nobody can see what you are missing because you still have not defined the problem, only stated that what you have does not work.
What in that data makes you think that only 2 results should be returned. From what I am seeing it is 100% correct.
The source data would help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 12:10:18

quote:
Originally posted by jforward5

Here is the results with only the row_number system function. This is the query getting these results:

SELECT DISTINCT Recaps.SMonth, Recaps.SDay, Recaps.SYear, Recaps.SDate, Recaps.PTD_PROFIT, Recaps.PTD_SALES, BILLING_DAYS.BDaysMonth,
ROW_NUMBER() OVER(ORDER BY Recaps.SYear) AS BDay

FROM Recaps INNER JOIN
BILLING_DAYS ON Recaps.SYear = BILLING_DAYS.DYear AND Recaps.SMonth = BILLING_DAYS.DMonth INNER JOIN
SalesGoals ON Recaps.SLM_NUMBER = SalesGoals.SLM_NUMBER

WHERE (Recaps.SLM_NUMBER = @SLM_NUMBER) AND (Recaps.SMonth = @SMonth) AND (Recaps.SYear = @SYear)

Here are the results:

1 5 2009 2009-01-05 04:00:03.100 0.0001 0.0001 22 1
1 5 2009 2009-01-05 04:00:03.100 0.0001 0.0001 22 2
1 6 2009 2009-01-06 04:00:02.947 811.03 2359.02 22 3
1 6 2009 2009-01-06 04:00:02.947 811.03 2359.02 22 4
1 7 2009 2009-01-07 04:00:02.113 978.81 2887.79 22 5
1 7 2009 2009-01-07 04:00:02.113 978.81 2887.79 22 6

Now with only the basic query. Here is the Query first:

SELECT DISTINCT Recaps.SMonth, Recaps.SDay, Recaps.SYear, Recaps.SDate, Recaps.PTD_PROFIT, Recaps.PTD_SALES, BILLING_DAYS.BDaysMonth

FROM Recaps INNER JOIN
BILLING_DAYS ON Recaps.SYear = BILLING_DAYS.DYear AND Recaps.SMonth = BILLING_DAYS.DMonth INNER JOIN
SalesGoals ON Recaps.SLM_NUMBER = SalesGoals.SLM_NUMBER

WHERE (Recaps.SLM_NUMBER = @SLM_NUMBER) AND (Recaps.SMonth = @SMonth) AND (Recaps.SYear = @SYear)

Here is the results:

1 5 2009 2009-01-05 04:00:03.100 0.0001 0.0001 22
1 6 2009 2009-01-06 04:00:02.947 811.03 2359.02 22
1 7 2009 2009-01-07 04:00:02.113 978.81 2887.79 22

There are now 3 possible results in the db since I have a SSIS package that runs every night to compile data from our backend system. Yesterday there was 2 possible results today 3 and tomorrow 4 and so on.

Some one has to see what ever it is I am missing...I can't seem to find the problem.

1st one will obviously bring all rows since ROW_NUMBER() generates the sequence which is always distinct (1,2,3...) so all records will be returned.
Second i think is what you're expecting. if thats the case, why are you adding row_number()
perhaps you can explain us what you're tryinng to do with some sample data

jforward5
Starting Member

19 Posts

Posted - 2009-01-12 : 16:33:28

I am trying to take the rows in the database and return them one at a time and do math on each one getting the percentage for each row. I need to number them because I need the number to represent the number of the billing day.

Let me make this more clear.

You have day 1: user number 117 has made 1200.00 profit and had to sell 2400.00 to make that amount. There are 21 billable days in the month and 255 billable days in the year. The user has a goal to reach each year of 1,000,000.00 and each month a goal of 83,333.33 and then to break it down even more a daily goal of 3,968.25.

All days that follow would follow the same rules. The information stored in my database is the user's number, the users monthly and yearly goal, the users profit month to date, and sales month to date. The information not stored is the daily goal, the billing day number, the percentage of the month billed, the percentage of the month goal reached, and the profit percentage for the day. When I get the query to work right it will present me with the information not stored in the database.

The problem right now is that it is returning information for the rows twice. So if I have 2 rows in the database it is returning 4, if I have 3 it returns 6, each distinct and with different results using the same data. I do not understand where it is getting the second set of information for the same row. my out put should look something like this:

smonth--sday--sdate--------profit-----sales-----billingdaysmonth---billingday--percentbilled--profitpercent--percentreached

1-------5-----2009-1-05----1200.00----2400.00---21-----------------1-----------0.045----------1--------------0.60

Instead I will get this:

smonth--sday--sdate--------profit-----sales-----billingdaysmonth---billingday--percentbilled--profitpercent--percentreached

1-------5-----2009-1-05----1200.00----2400.00---21-----------------1-----------0.045----------1--------------0.60

1-------5-----2009-1-05----1200.00----2400.00---21-----------------2-----------0.090----------1--------------1

And Im not sure why I get this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources