Multiple complex queries with master select statement

Question

So, I've tried to find examples of this query to see if it's elegant or if there is a better way, but I've not found any such examples anywhere. It works, and it runs fairly quickly on a small-medium sized database, but I just don't know if there is a better/faster way to accomplish this query.

Any suggestions on improving this?

select 
(select count(b.bookingnbr) from Customer as cu
inner join Sales as b on b.CugFK=cu.NameID
inner join Gender as g on cu.SexID=g.SexCodeID
inner join SalesOrderHistory as ssh on ssh.SalesFK=b.SalesPK
inner join OrderStatus as ss on ss.OrderStatusID=ssh.OrderStatusFK
where b.SalesDtTm between '01/01/2015 00:00:00' and '12/31/2015 23:59:00'
and ssh.DeletedDtTm is null
and g.sexcode='F'
and ss.IsHold=1
group by g.SexCode) 'Female Hold',

(select count(b.bookingnbr) from Customer as cu
inner join Sales as b on b.cugFK=cu.NameID
inner join Gender as g on cu.SexID=g.SexCodeID
inner join SalesOrderHistory as ssh on ssh.SalesFK=b.SalesPK
inner join OrderStatus as ss on ss.OrderStatusID=ssh.OrderStatusFK
where b.SalesDtTm between '01/01/2015 00:00:00' and '12/31/2015 23:59:00'
and ssh.DeletedDtTm is null
and g.sexcode='M'
and ss.IsHold=1
group by g.SexCode) 'Male Hold',

(select count(b.bookingnbr) from Customer as cu
inner join Sales as b on b.CugFK=cu.NameID
inner join Gender as g on cu.SexID=g.SexCodeID
inner join SalesOrderHistory as ssh on ssh.SalesFK=b.SalesPK
inner join OrderStatus as ss on ss.OrderStatusID=ssh.OrderStatusFK
where b.SalesDtTm between '01/01/2015 00:00:00' and '12/31/2015 23:59:00'
and ssh.DeletedDtTm is null
and g.sexcode='F'
and (ss.IsFinal=1 and ss.code<>'PC')
group by g.SexCode) 'Female Finalized',

(select count(b.bookingnbr) from Customer as cu
inner join Sales as b on b.CugFK=cu.NameID
inner join Gender as g on cu.SexID=g.SexCodeID
inner join SalesOrderHistory as ssh on ssh.SalesFK=b.SalesPK
inner join OrderStatus as ss on ss.OrderStatusID=ssh.OrderStatusFK
where b.SalesDtTm between '01/01/2015 00:00:00' and '12/31/2015 23:59:00'
and ssh.DeletedDtTm is null
and g.sexcode='M'
and (ss.IsFinal=1 and ss.code<>'PC')
group by g.SexCode) 'Male Finalized',

(select count(b.bookingnbr) from Customer as cu
inner join Sales as b on b.CugFK=cu.NameID
inner join Gender as g on cu.SexID=g.SexCodeID
inner join SalesOrderHistory as ssh on ssh.SalesFK=b.SalesPK
inner join OrderStatus as ss on ss.OrderStatusID=ssh.OrderStatusFK
where b.SalesDtTm between '01/01/2015 00:00:00' and '12/31/2015 23:59:00'
and ssh.DeletedDtTm is null
and g.sexcode='F'
and (ss.IsOrdered=1 and ss.code='PC')
group by g.SexCode) 'Female Ordered',

(select count(b.bookingnbr) from Customer as cu
inner join Sales as b on b.CugFK=cu.NameID
inner join Gender as g on cu.SexID=g.SexCodeID
inner join SalesOrderHistory as ssh on ssh.SalesFK=b.SalesPK
inner join OrderStatus as ss on ss.OrderStatusID=ssh.OrderStatusFK
where b.SalesDtTm between '01/01/2015 00:00:00' and '12/31/2015 23:59:00'
and ssh.DeletedDtTm is null
and g.sexcode='M'
and (ss.IsOrdered=1 and ss.code='PC')
group by g.SexCode) 'Male Ordered',

(select count(b.bookingnbr)  from Customer as cu
inner join Sales as b on b.CugFK=cu.NameID
inner join Gender as g on cu.SexID=g.SexCodeID
inner join SalesOrderHistory as ssh on ssh.SalesFK=b.SalesPK
inner join OrderStatus as ss on ss.OrderStatusID=ssh.OrderStatusFK
where b.SalesDtTm between '01/01/2015 00:00:00' and '12/31/2015 23:59:00'
and ssh.DeletedDtTm is null
and g.sexcode='F'
and ss.IsApproved=1
group by g.SexCode) 'Female Approved',

(select count(b.bookingnbr)  from Customer as cu
inner join Sales as b on b.CugFK=cu.NameID
inner join Gender as g on cu.SexID=g.SexCodeID
inner join SalesOrderHistory as ssh on ssh.SalesFK=b.SalesPK
inner join OrderStatus as ss on ss.OrderStatusID=ssh.OrderStatusFK
where b.SalesDtTm between '01/01/2015 00:00:00' and '12/31/2015 23:59:00'
and ssh.DeletedDtTm is null
and g.sexcode='M'
and ss.IsApproved=1
group by g.SexCode) 'Male Approved'

Not in SQL, fairly new to SQL. I'll look into the Case statement, thank you! — Pwncoder, Feb 02 '16 at 18:58
I would recommend using double quotes (or square brackets) for your column aliases. — shawnt00, Feb 02 '16 at 19:15
I've seen people do that, and I'm sure there's a good reason, just I don't know why. EDIT: This says brackets are non-portable: http://stackoverflow.com/questions/19657101/what-is-the-difference-between-square-brackets-and-single-quotes-for-aliasing-in — Pwncoder, Feb 02 '16 at 19:24
Please do not fall into the trap of looking for elegant SQL code. You want SQL code that first gives the correct result and second performs well. Elegant code tends to miss out the second point entirely. The worst looking SQL is often the best choice. — HLGEM, Feb 02 '16 at 19:34
@pwncoder You're right about the square brackets but most people don't want to hear that. Double quotes are preferred for portability. — shawnt00, Feb 03 '16 at 02:48

Horaciux · Accepted Answer · 2016-02-02T19:03:55.463

1

Not full answer, just explaining my comment

Something like this

    select sum(case when g.sexcode='F'and and ss.IsHold=1 then 1 else 0 end ) 'Female Hold',
    sum(case when g.sexcode='M'and ss.IsHold=1 then 1 else 0 end ) 'Male Hold',
   sum(case when g.sexcode='F'and and ss.IsFinal=1 and ss.code<>'PC' then 1 else 0 end ) 'Female Finalized'
--,....
    from Customer as cu
    inner join Sales as b on b.CugFK=cu.NameID
    inner join Gender as g on cu.SexID=g.SexCodeID
    inner join SalesOrderHistory as ssh on ssh.SalesFK=b.SalesPK
    inner join OrderStatus as ss on ss.OrderStatusID=ssh.OrderStatusFK
    where b.SalesDtTm between '01/01/2015 00:00:00' and '12/31/2015 23:59:00'
    and ssh.DeletedDtTm is null

edited Feb 02 '16 at 19:03

answered Feb 02 '16 at 18:57

Horaciux

6,322
2
22
41

does it make sense to you or need further help? I think you could manage from here. Let me know – Horaciux Feb 02 '16 at 18:59
The `group by` should be removed though...along with the trailing paren. – shawnt00 Feb 02 '16 at 19:02
Changed my query modeling your suggestion, and I like it better, as null values are actual '0's. I like this Horaciux! – Pwncoder Feb 02 '16 at 19:55

Multiple complex queries with master select statement

1 Answers1