December 10, 2004 at 12:22 pm
I think i found the problem it is with the identity.
I ran your first query
"SELECT identity (int, 0, 6) as salesseq, SeqNumberTemp.ProductNameFull, (CASE WHEN OrderTotal > 0 THEN (SalesTotalTemp.OrderTotal * SeqNumberTemp.margin) ELSE SeqNumberTemp.margin END) AS Total into #SomeTempTable FROM SalesTotalTemp RIGHT OUTER JOIN SeqNumberTemp ON SalesTotalTemp.ProductNameFull2 = SeqNumberTemp.ProductNameFull ORDER BY Total desc"
And then I ran this
"SELECT * FROM #SomeTempTable ORDER BY total desc"
which gives same results as my old query that does not use temporary table but is different from this query
"SELECT * FROM #SomeTempTable ORDER BY salesseq"
I want them to be the same. It seems the identity (int, 0, 6) is not using the order by total desc to assign numbers but just assigning based on when the entry was made or some other logic.
Can you please fix this. I am very close to finishing it if this gets solved.
December 10, 2004 at 12:32 pm
You can see how random these results are if I use this query
SELECT * FROM #SomeTempTable ORDER BY salesseq
salesseq total
0.9221
61.3316
12.9980
181.0758
24-.4010
301.2163
361.5960
421.2350
these results should have maximum total at top and then go down.
SELECT * FROM #SomeTempTable ORDER BY total desc
salesseq total
58323039.61
1680601.34
8736512.16
10638356.37
8346355.66
2568296.95
13824240.37
6432186.40
this should have had saleseq 0 at top and then increase.
December 10, 2004 at 11:15 pm
I think that would solve the problem (just make sure that the derived table dtOrderedSales has the order by you need and you should be fine) :
SELECT identity (int, 0, 6) as salesseq, dtOrderedSales.ProductNameFull, dtOrderedSales.Total
into #SomeTempTable
from (Select TOP 100 PERCENT SeqNumberTemp.ProductNameFull, (CASE WHEN OrderTotal > 0 THEN (SalesTotalTemp.OrderTotal * SeqNumberTemp.margin) ELSE SeqNumberTemp.margin END) AS Total FROM SalesTotalTemp RIGHT OUTER JOIN SeqNumberTemp ON SalesTotalTemp.ProductNameFull2 = SeqNumberTemp.ProductNameFull ORDER BY Total desc) dtOrderedSales
December 11, 2004 at 4:46 pm
This works perfect! It takes 6 seconds compared to 11 minutes using asp where 5000 update statements were being passed.
Thanks a lot! I really appreciate your help.
December 12, 2004 at 2:42 am
Happy to help.
February 25, 2005 at 9:53 am
Hi remi,
remember you had helped me with one of queries before. I again need you help.
I think my aggregate sum function is doing something wrong in the following query
SELECT SUM(Order_details.OrderPrice) AS OrderTotal,
MIN(Products.Manufacturer + ' ' + Products.ProductName) AS ProductNameFull2
FROM Order_details INNER JOIN
Orders ON Order_details.LinkToOrders = Orders.OrderID INNER JOIN
Products ON Order_details.LinkToProducts = Products.SKUCode
GROUP BY Products.Manufacturer + ' ' + Products.ProductName
HAVING (MAX(Orders.OrderDate) > DATEADD(d, - 90, GETDATE()))
When I compare the Oredertotal I get using the above query with the ordertotal I calculate using excel from the following query which does not sum there is a very big difference.
SELECT Products.Manufacturer + ' ' + Products.ProductName AS ProductNameFull2, Orders.OrderID, Orders.OrderDate, Order_details.OrderPrice, Order_details.Quantity,
Orders.Deleted
FROM Order_details INNER JOIN
Orders ON Order_details.LinkToOrders = Orders.OrderID INNER JOIN
Products ON Order_details.LinkToProducts = Products.SKUCode
WHERE (Orders.OrderDate > DATEADD(d, - 90, GETDATE())) AND
(Products.Manufacturer + ' ' + Products.ProductName = N'AERIS ATMOS ELITE AIR/NITROX HOSELESS COMPUTER')
ORDER BY Orders.OrderDate DESC
I cannot understand what am I doing wrong in my first query because I checked the second query is giving correct results.
Thanks.
February 25, 2005 at 10:03 am
I haven't checked the whole thing but this jumped right to my face :
GROUP BY Products.Manufacturer + ' ' + Products.ProductName
HAVING (MAX(Orders.OrderDate) > DATEADD(d, - 90, GETDATE()))
WHERE (Orders.OrderDate > DATEADD(d, - 90, GETDATE())) AND
(Products.Manufacturer + ' ' + Products.ProductName = N'AERIS ATMOS ELITE AIR/NITROX HOSELESS COMPUTER')
Are 2 extremely different things.
(Products.Manufacturer + ' ' + Products.ProductName = N'AERIS ATMOS ELITE AIR/NITROX HOSELESS COMPUTER')
filters out a lot of records that are not filtered in the other query
Orders.OrderDate > DATEADD(d, - 90, GETDATE())
filters only a few lines from the product while
HAVING (MAX(Orders.OrderDate) > DATEADD(d, - 90, GETDATE()))
filters the whole product if the condition is not met.
February 25, 2005 at 10:07 am
Hi,
I just added that where clause in the second query to be able select one product results and copy it in excel to total and compare. I even added that same where clause to the first query and still had the same problem.
HAVING (MAX(Orders.OrderDate) > DATEADD(d, - 90, GETDATE())) AND
(Products.Manufacturer + ' ' + Products.ProductName = N'AERIS ATMOS ELITE AIR/NITROX HOSELESS COMPUTER')
February 25, 2005 at 10:11 am
can you post the DDL and some of the data from the table along with the results from each of the queries? I can't help you further without that.
February 25, 2005 at 10:21 am
The second query gives the following result
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 1475.95
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 1209.95
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 1209.95
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 1404.95
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 1475.95
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 1475.95
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 1404.95
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 1219.95
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 949.95
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 1155.95
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 1354.95
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 1354.95
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 1219.95
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 1425.95
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 1354.95
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 1084.95
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 1354.95
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 1425.95
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 1155.95
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 1425.95
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 1019.95
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 1354.95
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 1019.95
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 1084.95
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 1354.95
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 0
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 0
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 1084.95
DELTA TECHNOLOGY VR3 AIR/NITROX COMPUTER 949.95
Total 34011.65
Comapred to the total from the first query which gives 1630413.85. And the result are different for all products. And its not constant that its more; it can be more or less than the actual result.
February 25, 2005 at 11:26 am
it's still not enough info to help you (no table definition)..
Try running the first query without the sum/group by and find out which lines are missing for a product compared to the second query for the same product. That way you'll be able to adjust your where conditions accordingly.
February 25, 2005 at 11:46 am
Thats what I have written the second query for and the only difference between the 2 queries is I am using having in the first query and where in the second query other than group by and sum.
Look at the following I have removed the extra things from second query
SELECT SUM(Order_details.OrderPrice) AS OrderTotal,
MIN(Products.Manufacturer + ' ' + Products.ProductName) AS ProductNameFull2
FROM Order_details INNER JOIN
Orders ON Order_details.LinkToOrders = Orders.OrderID
INNER JOIN Products ON Order_details.LinkToProducts = Products.SKUCode
GROUP BY Products.Manufacturer + ' ' + Products.ProductName
HAVING (MAX(Orders.OrderDate) > DATEADD(d, - 90, GETDATE())) AND
(Products.Manufacturer + ' ' + Products.ProductName = N'Dell COMPUTER')
SELECT Products.Manufacturer + ' ' + Products.ProductName AS ProductNameFull2
FROM Order_details INNER JOIN Orders ON Order_details.LinkToOrders = Orders.OrderID INNER JOIN
Products ON Order_details.LinkToProducts = Products.SKUCode
WHERE (Orders.OrderDate > DATEADD(d, - 90, GETDATE())) AND
(Products.Manufacturer + ' ' + Products.ProductName = N'Dell COMPUTER')
If this does not help, do u need the table structures for all 3 tables?
February 25, 2005 at 12:09 pm
as I stated earlier :
HAVING (MAX(Orders.OrderDate) > DATEADD(d, - 90, GETDATE()))
IS VERY DIFFERENT than
WHERE (Orders.OrderDate > DATEADD(d, - 90, GETDATE()))
add MAX(Orders.OrderDate) and COUNT(*) from the first select query to see how many lines are returned compared to the 2nd query and also what is returned by the max function... you should be surprised by those results and I think you'll have found your answer.
February 25, 2005 at 12:14 pm
I was thinking that having and where is the same thing.
Then how should I write the query to use where in the first query because it does not allow me to use where in a group by clause. It will be great help if u can modify this query to give the same results as the secong query.
February 25, 2005 at 12:19 pm
WHERE AND HAVING are very similar, but your conditions differ from one another because of the max operation.
Try removing the (MAX(Orders.OrderDate) to only Orders.OrderDate > DATEADD(d, - 90, GETDATE())
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply