dynamic sub qry help | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

dynamic sub qry help

I am having a query which does generate dynamic subqueries from my asp coding for my report. the actual report may be like this.. shippername |jan2006|Feb2006| Mar2006….|nov 2007|… the months and date is dynamic from asp input
each record will have shipper name and the total teus they done. for a particular shipper and particular month (say jan2006) the query is here –starts here
quote:select
–c.id,teus
count(c.id)*sum(teus)as t–,blcontacts.companyid
from containers c inner join blcargo blc on blc.cntrid=c.id inner join bl on bl.id=blc.blid
inner join voyage on voyage.id=bl.voyageid inner join blcontacts on blcontacts.blid=blc.blid
inner join tblcntrtypes on tblcntrtypes.id=c.typeid
where ishousebl=0 and isimport=0 and month(voyage.dtdeparture)=1 and year(voyage.dtdeparture)=2006
and blcontacts.companytypeid=1 and blcontacts.companyid=11

–ends here I am getting the shippers id is here (suppose if i selected jan2006 to feb2006) –starts here
quote:select b2.companyid
from blcargo blc
inner join bl on blc.blid=bl.id inner join voyage on voyage.id=bl.voyageid
inner join blcontacts b2 on b2.blid=blc.blid– inner join company on company.id=blcontacts.companyid
where ishousebl=0 and isimport=0 and ((month(voyage.dtdeparture)>= 1) and year(voyage.dtdeparture)>=2006)
and ((month(voyage.dtdeparture)<= 2) and year(voyage.dtdeparture)<=2006)
and b2.companytypeid=1
group by b2.companyid order by (select name from company where id=b2.companyid)

–ends here I can get the output by the following query but if the months increased (say jan 2006 to mar 2007) number of subqueries also increased… –starts here
quote:select b2.companyid, (select name from company where id=b2.companyid),(
select count(c.id)*sum(teus)as t from containers c inner join blcargo blc on blc.cntrid=c.id inner join bl on bl.id=blc.blid
inner join voyage on voyage.id=bl.voyageid inner join blcontacts on blcontacts.blid=blc.blid
inner join tblcntrtypes on tblcntrtypes.id=c.typeid
where ishousebl=0 and isimport=0 and month(voyage.dtdeparture)=1 and year(voyage.dtdeparture)=2006
and blcontacts.companytypeid=1
and blcontacts.companyid =b2.companyid) jan2006,(
select count(c.id)*sum(teus)as t from containers c inner join blcargo blc on blc.cntrid=c.id inner join bl on bl.id=blc.blid
inner join voyage on voyage.id=bl.voyageid inner join blcontacts on blcontacts.blid=blc.blid
inner join tblcntrtypes on tblcntrtypes.id=c.typeid
where ishousebl=0 and isimport=0 and month(voyage.dtdeparture)=2 and year(voyage.dtdeparture)=2006
and blcontacts.companytypeid=1
and blcontacts.companyid =b2.companyid) Feb2006
from blcargo blc
inner join bl on blc.blid=bl.id inner join voyage on voyage.id=bl.voyageid
inner join blcontacts b2 on b2.blid=blc.blid where ishousebl=0 and isimport=0 and ((month(voyage.dtdeparture)>= 1) and year(voyage.dtdeparture)>=2006)
and ((month(voyage.dtdeparture)<= 2) and year(voyage.dtdeparture)<=2006)
and b2.companytypeid=1
group by b2.companyid order by (select name from company where id=b2.companyid)

–ends hers any suggestions?
Your queries seem a little overdone, with all those subqueries everywhere. You know how to use JOINs, so why not do the lookups for full names in JOINs as well? You need to start with the FROM clause with the JOINs – from which tables am I reading data, and how do they connect. Then the WHERE, finally the SELECT. You can also use a much simpler syntax for date ranges:
WHERE voyage.dtdeparture BETWEEN ‘2006-01-01’ AND ‘2007-03-31’ Start with a simple query, then start adding complexity.
If you can Construct your query somthing like this..
select b2.companyid, (select name from company where id=b2.companyid),
Sum(Case When month(voyage.dtdeparture)=1 and year(voyage.dtdeparture)=2006 Then teus Else 0) *
Sum(Case When month(voyage.dtdeparture)=1 and year(voyage.dtdeparture)=2006 Then 1 Else 0) jan2006,
Sum(Case When month(voyage.dtdeparture)=2 and year(voyage.dtdeparture)=2006 Then teus Else 0) *
Sum(Case When month(voyage.dtdeparture)=2 and year(voyage.dtdeparture)=2006 Then 1 Else 0) Feb2006,
from blcargo blc
inner join bl on blc.blid=bl.id inner join voyage on voyage.id=bl.voyageid
inner join blcontacts b2 on b2.blid=blc.blid where ishousebl=0 and isimport=0 and ((month(voyage.dtdeparture)>= 1) and year(voyage.dtdeparture)>=2006)
and ((month(voyage.dtdeparture)<= 2) and year(voyage.dtdeparture)<=2006)
and b2.companytypeid=1
group by b2.companyid
Chirag
THank you Adrian and Chirag, Chirag query is working fine with little modification as per my needs. Here is the code select com.id,com.name,
Sum(Case When month(voyage.dtdeparture)=1 and year(voyage.dtdeparture)=2006 Then tc.teus Else 0 end) *
count(c.id) jan2006,
Sum(Case When month(voyage.dtdeparture)=2 and year(voyage.dtdeparture)=2006 Then tc.teus Else 0 end) *
count(c.id) feb2006,
Sum(Case When month(voyage.dtdeparture)=3 and year(voyage.dtdeparture)=2006 Then tc.teus Else 0 end) *
count(c.id) mar2006
from blcargo blc
inner join bl on blc.blid=bl.id
inner join voyage on voyage.id=bl.voyageid
inner join blcontacts b2 on b2.blid=blc.blid
inner join company com on com.id=b2.companyid
inner join containers c on c.id=blc.cntrid
inner join tblcntrtypes tc on c.typeid=tc.id
where ishousebl=0 and isimport=0 and
((month(voyage.dtdeparture)between 1 and 3) and year(voyage.dtdeparture)=2006)and
b2.companytypeid=1
group by c.id, com.id,com.name
After running this query i gets the result like compid compname jan2006 feb2006 mar20006
12 xyz 0 0 2
12 xyz 0 2 2
12 xyz 1 0 2
12 xyz 1 0 2
25 abc 2 0 1
25 abc 2 0 1
25 abc 2 0 1 The actual result should be combined like below compid compname jan2006 feb2006 mar20006
12 xyz 2 2 8
25 abc 6 0 3
any help is appreciated.

if the output is like this:
compid compname jan2006 feb2006 mar20006
12 xyz 0 0 2
12 xyz 0 2 2
12 xyz 1 0 2
12 xyz 1 0 2
25 abc 2 0 1
25 abc 2 0 1
25 abc 2 0 1 you can use sum with group by.
like:
SELECT id,name,sum(jan2006) as jan2006,sum(feb2006) as feb2006,sum(mar2006) as mar2006
FROM(
select com.id,com.name,
Sum(Case When month(voyage.dtdeparture)=1 and year(voyage.dtdeparture)=2006 Then tc.teus Else 0 end) *
count(c.id) jan2006,
Sum(Case When month(voyage.dtdeparture)=2 and year(voyage.dtdeparture)=2006 Then tc.teus Else 0 end) *
count(c.id) feb2006,
Sum(Case When month(voyage.dtdeparture)=3 and year(voyage.dtdeparture)=2006 Then tc.teus Else 0 end) *
count(c.id) mar2006
from blcargo blc
inner join bl on blc.blid=bl.id
inner join voyage on voyage.id=bl.voyageid
inner join blcontacts b2 on b2.blid=blc.blid
inner join company com on com.id=b2.companyid
inner join containers c on c.id=blc.cntrid
inner join tblcntrtypes tc on c.typeid=tc.id
where ishousebl=0 and isimport=0 and
((month(voyage.dtdeparture)between 1 and 3) and year(voyage.dtdeparture)=2006)and
b2.companytypeid=1
group by c.id, com.id,com.name
) a group by id,name
Thank you all.. Its really a live forum. Everyone’s help helped me to do and learn. For others I have added the final query along with the modified dtdeparture where clause as guided by adrian. (finding last day of a month by nextmonth’s first date minus one) // SELECT id,name,sum(jan2006) as jan2006,sum(feb2006) as feb2006,sum(mar2006) as mar2006
FROM(
select com.id,com.name,
Sum(Case When month(voyage.dtdeparture)=1 and year(voyage.dtdeparture)=2006 Then tc.teus Else 0 end) *
count(c.id) jan2006,
Sum(Case When month(voyage.dtdeparture)=2 and year(voyage.dtdeparture)=2006 Then tc.teus Else 0 end) *
count(c.id) feb2006,
Sum(Case When month(voyage.dtdeparture)=3 and year(voyage.dtdeparture)=2006 Then tc.teus Else 0 end) *
count(c.id) mar2006
from blcargo blc
inner join bl on blc.blid=bl.id
inner join voyage on voyage.id=bl.voyageid
inner join blcontacts b2 on b2.blid=blc.blid
inner join company com on com.id=b2.companyid
inner join containers c on c.id=blc.cntrid
inner join tblcntrtypes tc on c.typeid=tc.id
where ishousebl=0 and isimport=0 and
voyage.dtdeparture between ‘1-1-2006′ and (select dateadd(d,-1,’3-1-2007’)) and
b2.companytypeid=1
group by c.id, com.id,com.name
) a group by id,name
]]>