Again select statement...

RinoDM · January 20th, 2009, 06:10 PM

hi to all...I have these 2 select statements...One is grouped data and the other one has individual data...i use the grouped data for my intranet so the data is returned faster...i have to mark sure the data is equal...is there a way i can join or union these two select that would return me the dates that are not equal...Select Sum(Qty),Sum(Price),Sum(Qty*DGCost) from GroupedDetails where Date between '12/01/08' and 12/01/08'Select sum(abs(Qty)*PosNegFlag),sum(abs(Price)*PosNegFlag ),Sum(abs(Qty)*PosNegFlag*DGCost)from TransactionDetails where Date between '12/01/08' and 12/01/08'thanking you inadvance...Rino...

Old Pedant · January 20th, 2009, 07:19 PM

...but this FEELS right.

Yes, a JOIN looks like the right answer. Try this:

Code:

SELECT G.Date, G.sQTY, G.sPRICE, G.sQCOST, T.sQTY, T.sPRICE, T.sQCOST
FROM 
(
    SELECT Date. SUM(Qty) AS sQTY, SUM(Price) AS sPRICE, SUM(Qty*DGCost) AS sQCOST
    FROM GroupedDetails 
    GROUP BY Date
    WHERE Date between '12/01/2008' AND '12/31/2008'
) AS G,
(
    SELECT Date, SUM(ABS(Qty)*PosNegFlag) AS sQTY, SUM(ABS(Price)*PosNegFlag) AS sPRICE, 
                 SUM(ABS(Qty)*PosNegFlag*DGCost) AS sQCOST
    FROM TransactionDetails 
    GROUP BY Date
    WHERE Date between '12/01/2008' AND '12/31/2008'
) AS T
WHERE G.Date = T.Date
  AND ( G.sQTY<>T.sQTY OR G.sPRICE<>T.sPRICE OR G.sQCOST<>T.sQCOST )
ORDER BY G.Date

NOTE: Use full years in your dates, always. Two digit years *CAN* be misinterpreted.

NOTE: The parentheses in the final WHERE clause are *NEEDED* as shown.

rstelma · January 21st, 2009, 02:08 PM

Old Pedant,

If you have a couple of minutes could you please explain this query to me. I'm working with some very difficult date based data queries and I think some of the concepts in this query would really help me out a lot if I just understood them.

In particular I don't understand the FROM [query]. Does that just do a select on the data set in the query as if you were creating a table? And conceptually, how does that help in this case?

Thank you for your time.

Richard

Old Pedant · January 21st, 2009, 04:51 PM

Short answer: Yes.

Longer answer: For all intents and purposes, when you do

Code:

SELECT ...
FROM ( SELECT x,y,z FROM sometable ) AS someAlias
WHERE ...

that sub-select (or inner select) is treated as if it's another table. Or perhaps you'd prefer to think of it as a VIEW (if you are using SQL Server) or STORED QUERY (if you are using Access).

In other words, you *COULD* achieve the same results (using SQL Server for this example) by doing:

Code:

CREATE VIEW someAlias
AS 
SELECT x,y,z FROM sometable

and then

 SELECT ...
FROM someAlias
WHERE ...

But the problem with using a VIEW is that you can't add a "dynamic condition" to it. So in your case, you certainly *COULD* create the VIEW:

Code:

CREATE VEIW G
AS
SELECT Date. SUM(Qty) AS sQTY, SUM(Price) AS sPRICE, SUM(Qty*DGCost) AS sQCOST
FROM GroupedDetails 
GROUP BY Date
WHERE Date between '12/01/2008' AND '12/31/2008'

But what happens next month, when you want to change the date range to, say, '1/1/2009' AND '1/31/2009' ???

There are actually two answers to that.
(1) Don't put the date range into the VIEW:

Code:

CREATE VEIW G
AS
SELECT Date. SUM(Qty) AS sQTY, SUM(Price) AS sPRICE, SUM(Qty*DGCost) AS sQCOST
FROM GroupedDetails 
GROUP BY Date

And then, when you use the VIEW, limit it:

Code:

SELECT ...
FROM G WHERE G.Date BETWEEN '1/1/2009' AND '1/32/2009'

You might think that the disadvantage in doing this is that the VIEW has to process all the records in GroupedDetails before it can then be used in the outer SELECT and limited to only certain dates. But SQL Server is smart enough that it will optimize your *entire* query. And this is why VIEWs are, often, better than #TEMP tables.

(2) But the other solution is to do as I did in my answer: Don't bother creating stored VIEWs but just create the equivalents "in line" in the overall query.

And, truly, the results in terms of both data and performance should be identical (or damned near so...maybe minor differences in compile time...dunno).

So now, to get more particular:

Quote:

In particular I don't understand the FROM [query]. Does that just do a select on the data set in the query as if you were creating a table? And conceptually, how does that help in this case?

There's no real logical difference between a table and a VIEW. And, as I've just said, there's no real logical difference between a VIEW and an inner SELECT, such as I used.

Yes, we *could* have accomplished the same thing by doing:

Code:

SELECT Date. SUM(Qty) AS sQTY, SUM(Price) AS sPRICE, SUM(Qty*DGCost) AS sQCOST
INTO #TEMPG
FROM GroupedDetails 
GROUP BY Date
WHERE Date between '12/01/2008' AND '12/31/2008'

and then

SELECT Date, SUM(ABS(Qty)*PosNegFlag) AS sQTY, SUM(ABS(Price)*PosNegFlag) AS sPRICE, 
                 SUM(ABS(Qty)*PosNegFlag*DGCost) AS sQCOST
INTO #TEMPT
FROM TransactionDetails 
GROUP BY Date
WHERE Date between '12/01/2008' AND '12/31/2008'

and finally

SELECT G.Date, G.sQTY, G.sPRICE, G.sQCOST, T.sQTY, T.sPRICE, T.sQCOST
FROM AS #TEMPG AS G, #TEMPT AS T
WHERE G.Date = T.Date
  AND ( G.sQTY<>T.sQTY OR G.sPRICE<>T.sPRICE OR G.sQCOST<>T.sQCOST )
ORDER BY G.Date

And who knows? Maybe SQL Server would even be smart enough to make this version as efficient as the others (though I tend to doubt it).

So... For *MANY* practical purposes you can treat a VIEW much the same as a temporary table. And you can treat an inner SELECT much the same as a VIEW.

There are things you can do with temporary tables that you can't do with VIEWs (primarily, you can build them up "in pieces" instead of all in one step). And there are things you can do with VIEWs that you can't do with inner SELECTs (primarily, you can create them ahead of time and even limit clients to using only STORED PROCs and VIEWs and disallowing ad hoc queries). But for a query such as this one (which really isn't very complicated, as SQL Server queries go), there's little to recommend one route over the other. Perhaps the primary benefit of the version I show is that it *CAN* be all done as an ad hoc query, with no need to "save" something in the DB (that is, even VIEWs must be created and saved in the DB). Many SQL purists would argue that this perceived "benefit" is actually the biggest flaw, just because it *does* use an ad hoc query. Me, I'm pragmatic: Use what works. When it's time to worry about security, *then* encapsulate the whole thing in a very protective STORED PROCEDURE.

Does that help, at all?

Old Pedant · January 21st, 2009, 04:54 PM

N.B.: In all the above, I have assumed that what you *really* want to do is replace all occurrences of

Code:

WHERE Date between '12/01/2008' AND '12/31/2008'

with something like

Code:

WHERE Date BETWEEN @startDate AND @endDate

Which is what you can't do if you use VIEWs, as I said, except by then limiting the data range *outside* the VIEW. (Or, I suppose, re-creating the VIEWs each month. Blech.)

rstelma · January 22nd, 2009, 08:35 PM

Old Pedant,

Wow! Thank you. It makes sense. I was left with an inventory report query from a former employee that uses that form 'SELECT FROM ([Query])'. It's not pulling the correct numbers. And since I really didn't understand that syntax I had no understanding of how to go about trying to fix it. I don't know that I will but I've got a lot better chance now. I could explain but I don't want to completely hijack this post. If I continue to have trouble with it I'll write a new post.

Thanks again for your time and such a great explanation. I've seen many of your posts and I really appreciate them.

Richard

January 30th, 2009, 06:51 AM

Thanks for the nice reply Old Pedant