Nested select (do I get the data I want?)

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

General SQL Server Forums

New to SQL Server Programming

Nested select (do I get the data I want?)

Author

Topic

stefanh
Starting Member

4 Posts

Posted - 2011-01-26 : 06:04:23

Hi,
I have 2 tables I want to display some statistics from. It's about orders and how long time after their planned ship date they've actually been shipped. I think I have solved it, but I'm a newbie when it comes to SQL and I'm a bit worried that I might have missed something... Code is probably also ugly.

The result I want is: For a given period (entered by me), a list of ordertypes (ordtype), total amount of sub-orders (TableB.nopordl) for eachy order type, how many percent of the total order rows was shipped (ordstat = '9') 0, 1, 2, 3 and 4 and days after the scheduled shipping date (oppdate) for the given period. Something like this:
<ordtype> <Total amount sub-orders> <% shipped after 0 days> <% shipped after 1 day> ...

I get all data from TableB, except for the planned ship date which I get from TableA. I use a temporary table to get orders and total sum of sub-order rows per shipping day with some help of the dateDiff function, which I then use to calculate and display the final results. Something tells me that this temporary table could be wrong, but... it seems to work.

Here's the code:
DECLARE @startDate DATETIME, @endDate DATETIME

SET @startDate ='2011-01-01'
SET @endDate ='2011-01-24'

SELECT ordtype, ROUND(SUM (_Total), 1) AS "_Total amount of sub-orders",
ROUND(SUM(_0)/SUM(_Total)*100, 1) AS "_% shipped after 0 days",
ROUND(SUM(_1)/SUM(_Total)*100, 1) AS "_% shipped after 1 day",
ROUND(SUM(_2)/SUM(_Total)*100, 1) AS "_% shipped after 2 days",
ROUND(SUM(_3)/SUM(_Total)*100, 1) AS "_% shipped after 3 days",
ROUND(SUM( _4)/SUM(_Total)*100, 1) AS "_% shipped after 4 days"
FROM
(
SELECT ordtype, convert(float,SUM (TableB.nopordl)) AS "_Total",
(SELECT convert(float, SUM (TableB.nopordl)) WHERE DateDiff(d, oppdate, TableB.statdate) <= 0 AND ordstat = '9') AS "_0",
(SELECT convert(float, SUM (TableB.nopordl)) WHERE DateDiff(d, oppdate, TableB.statdate) = 1 AND ordstat = '9') AS "_1",
(SELECT convert(float, SUM (TableB.nopordl)) WHERE DateDiff(d, oppdate, TableB.statdate) = 2 AND ordstat = '9') AS "_2",
(SELECT convert(float, SUM (TableB.nopordl)) WHERE DateDiff(d, oppdate, TableB.statdate) = 3 AND ordstat = '9') AS "_3",
(SELECT convert(float, SUM (TableB.nopordl)) WHERE DateDiff(d, oppdate, TableB.statdate) = 4 AND ordstat = '9') AS "_4"
FROM TableB, TableA
WHERE TableA.shortKey = TableB.shortKey
AND oppdate >= @startDate AND oppdate <= @endDate
AND ordtype >= 11 AND ordtype <= 13
GROUP BY ordtype, oppdate, TableB.statdate, ordstat
) temp

GROUP BY ordtype

My question is if I really get the data I want in the temp table. The 2 first columns should contain values from the "where TableA.shortr08 = TableB.shortr08 ..." part. Then I want the rest of the columns to display values when the conditions are the same, but only when the dateDiff and ordstat conditions match.

Does this looks reasonable?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-26 : 06:25:14

have you tried to execute the above query and has it given you the desired results?

stefanh
Starting Member

4 Posts

Posted - 2011-01-26 : 07:58:13

quote:
Originally posted by MIK_2008

have you tried to execute the above query and has it given you the desired results?

Thanks for the reply. Yes, I have. It seems like it does, but the amount of data is rather huge and I'm not 100% fit with how the these selects work. I suppose my question could be re-phrased to something more general:

-------------------------
SELECT columnA1, columnB, (select sum(columnA2) WHERE condition1)
FROM
TableA, TableB
WHERE TableA.ID = TableB.ID AND condition2
-------------------------

The first 2 columns, will they only contain results from condition2? Will the third column contain results from condition2 and condition1 (in that order)?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-26 : 11:22:01

the condition2 will get applied to entire query so all three columns will get returned only as per condition2. may be what you want is this


SELECT columnA1, columnB, 
sum(CASE WHEN condition1 THEN columnA2 ELSE 0 END)
FROM
TableA, TableB
WHERE TableA.ID = TableB.ID AND condition2

this will ensure third column alone has condition1 applicable and all others just follows condition2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stefanh
Starting Member

4 Posts

Posted - 2011-01-26 : 15:52:32

quote:
Originally posted by visakh16

the condition2 will get applied to entire query so all three columns will get returned only as per condition2. may be what you want is this

SELECT columnA1, columnB,
sum(CASE WHEN condition1 THEN columnA2 ELSE 0 END)
FROM
TableA, TableB
WHERE TableA.ID = TableB.ID AND condition2

this will ensure third column alone has condition1 applicable and all others just follows condition2

Thanks for the reply. That looks like a perfect suggestion :-) One question: If I read "sum(CASE WHEN condition1 THEN columnA2 ELSE 0 END)" correct, this also means that if condition1 is true, condition2 will also apply to the third column? Because I think I need both conditions apply to the third column (but as you pointed out, only when condition1 applies)

Let me just give an example of why I think I need both conditions to apply to the third column. ColumnA1 would be ordertypes, ColumnB would be total sum of planned sub-order rows of the ColumnA1 order type. These 2 columns return the results from condition2 (ordertype > 10 AND ordertype < 14, planning date range). Now, ColumnA2 should then be the total sum of the shipped sub-order rows of the ColumnA order type. To find the shipped orders I need condition1 (status=shipped), but I would also need condition2, otherwise I think I will get back all order types and all planning dates.

Does that makes sense (sorry if I'm unclear)?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-27 : 12:08:03

yep. you're right. since the condition2 is in where it applies to all columns by default. In addition because of CASE you will also ensure condition1 also to be applied for column3 alone. All other columns will still be based only on default condition in where.

so this will perfectly suit your requirement here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stefanh
Starting Member

4 Posts

Posted - 2011-01-28 : 08:51:38

quote:
Originally posted by visakh16

yep. you're right. since the condition2 is in where it applies to all columns by default. In addition because of CASE you will also ensure condition1 also to be applied for column3 alone. All other columns will still be based only on default condition in where.

so this will perfectly suit your requirement here

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-29 : 11:09:29

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources