SQLTeam.com | Weblogs | Forums

Sql Subqueries


#1

I and trying to recreate a query with few subqueries a already have in an access DB on Sql. So far my code looks like this:
Select ParentCD.[ID],ParentCD.ParentCDDesc, BudgetCD.BdgCD, Annual_Budget, SUM([Items].[Qty][Items].[Price])AS Total, Year(PurchaseOrders.[PO Date]) AS [Year],SUM([CAItems].[Qty][CAItems].[Price]) as [CAItemsTotals]
,SUM ([MiscellaneousItems].Subtotal) as MiscSubTotals
from ParentCD
inner join

	BudgetCD on BudgetCD.ParentCD = ParentCD.ID

left join

	Annual_Budget_TB on Annual_Budget_TB.BdgCd  = BudgetCD.BdgCD	

left join 

	Items inner join PurchaseOrders on PurchaseOrders.PurchaseOrdersNo = Items.PurchaseOrdersNo
	on BudgetCD.BdgCD = [dbo].[Items].BudgetCD
						  

Left join (dbo.CAItems inner join CAPurchaseOrders on CAItems.PurchaseOrdersNo = CAPurchaseOrders.PurchaseOrdersNo) on [BudgetCD].BdgCD = [dbo].[CAItems].CABudgetCD 
						   
						   
Left join ([dbo].[Miscellaneous] inner join [dbo].[MiscellaneousItems] on [dbo].[Miscellaneous].ID = [dbo].[MiscellaneousItems].[ID]) on BudgetCD.[BdgCD] = [dbo].[Miscellaneous].[MisBdgCD]  

group by ParentCD.[ID],ParentCD.ParentCDDesc, BudgetCD.[BdgCD], [Annual_Budget],Year(PurchaseOrders.[PO Date]),Year(CAPurchaseOrders.[PO Date]),Year(MiscellaneousItems.[PO Date]),YEAR([Annual_Budget_TB].Bdg_Year])
having
Year(PurchaseOrders.[PO Date]) = 2015 and YEAR(CAPurchaseOrders.[PO Date]) = 2015 and YEAR(MiscellaneousItems.[PO Date]) = 2015 and YEAR([Annual_Budget_TB].[Bdg_Year]) = 2015
order by ParentCD.ID


The main Query should pull the the ParentCD , Parent CDDesc, from ParentCD table, the BdgtCD from the BudgetCD table from which a created the first subquery (from ParentCD inner join BudgetCD on BudgetCD.ParentCD = ParentCD.ID). I also want the query to include the Annual_Budget from the Annual_Budget_TB from the second subquery (Left join Annual_Budget_TB on annual_Budget_TB.BdgCd = BudgetCD. BdgCd) . So far I am joining three different tables and the query returns what Im expecting, however the problem starts when I am joining that query to the next set of subqueries.

I want the overall query to show me what I have so far but also to include the totals (Sum(Items.qty* items.Price) from the items table which at the same time is inner joined with the Purchase orders table. The result is not completely wrong but now the result of the previous two joins show me only parentCD, ParentCDDesc, BudgetCD and Annual_Budget only if there matching results in the third subquery (Items inner join PurchaseOrders…..) and I would like the whole query to show me everything from the first two subqueries even if the third subquery (Items inner join PurchaseOrders) has null values. In other words the overall query should pull 42 records but since they are null values in the third subquery (Items inner join PurchaseOrders…) the query is only showing me 26.
And the same is basically happening with the next two subqueries (Left join CAItems inner join CAPurchaseOrders... and Left join Miscellaneous inner join MiscellaneousItems), as I keep adding more subqueries the results show less records since these two other tables have even more null values.

I am pretty sure the problem relies on the joins weather the should be inner, right or left joins but no matter how much I keep playing with the joins the results are still the same.

I know this query might look too basic as I am a novice in Sql and I am pretty sure there must be better ways to obtain the results I want so if anybody have a better suggestion I would really appreciate it.


#2

See if this gets you closer to a solution:

select ParentCD.ID
      ,ParentCD.ParentCDDesc
      ,BudgetCD.BdgCD
      ,Annual_Budget
      ,sum(Items.Qty*Items.Price) as Total
      ,year(PurchaseOrders.[PO Date]) as [Year]
      ,sum(CAItems.Qty*CAItems.Price) as CAItemsTotals
      ,sum(MiscellaneousItems.Subtotal) as MiscSubTotals
  from ParentCD
       inner join BudgetCD
               on BudgetCD.ParentCD=ParentCD.ID
       left outer join Annual_Budget_TB
                    on Annual_Budget_TB.BdgCd=BudgetCD.BdgCD
                   and year(Annual_Budget_TB.Bdg_Year)=2015
       left outer join Items
                    on Items.BudgetCD=BudgetCD.BdgCD
       left outer join PurchaseOrders
                    on PurchaseOrders.PurchaseOrdersNo=Items.PurchaseOrdersNo
                   and year(PurchaseOrders.[PO Date])=2015
       left outer join CAItems
                    on CAItems.CABudgetCD=BudgetCD.BdgCD
       left outer join CAPurchaseOrders
                    on CAPurchaseOrders.PurchaseOrdersNo=CAItems.PurchaseOrdersNo
                   and year(CAPurchaseOrders.[PO Date])=2015
       left outer join Miscellaneous
                    on Miscellaneous.MisBdgCD=BudgetCD.BdgCD
       left outer join MiscellaneousItems
                    on MiscellaneousItems.ID=Miscellaneous.ID
                   and year(MiscellaneousItems.[PO Date])=2015
 group by ParentCD.ID
         ,ParentCD.ParentCDDesc
         ,BudgetCD.BdgCD
         ,Annual_Budget
         ,year(PurchaseOrders.[PO Date])
 order by ParentCD.ID
;

#3

Thank you so much for your reply

I gets me very close actually I can pull something very similar with my own query, however the numbers are not adding correctly when they are grouped by BdgCD
Here is the copy of the result I am getting right now in sql

01 Communication 01.001 81000 NULL NULL NULL 27667.55
01 Communication 01.002 71000 15330.00 2015 NULL 69585.93
01 Communication 01.003 308000 NULL NULL NULL 110301.72
010 Training 010.001 125000 6690.00 NULL NULL 50082.50
010 Training 010.001 125000 37715.00 2015 NULL 225371.25
010 Training 010.002 25000 NULL NULL NULL NULL
02 Consulting 02.001 120000 165200.00 NULL NULL 20899.85
02 Consulting 02.001 120000 147200.00 2015 NULL 62699.55
02 Consulting 02.002 212000 NULL NULL NULL 37267.57
02 Consulting 02.003 68000 38500.00 2015 NULL 10290.00
03 Data Center Services 03.001 96000 NULL NULL NULL 25200.00
03 Data Center Services 03.002 50000 NULL NULL NULL NULL
03 Data Center Services 03.003 45000 NULL NULL NULL NULL
03 Data Center Services 03.004 4000 4276.00 NULL NULL 2503.80
03 Data Center Services 03.004 4000 2948.00 2015 NULL 2503.80
03 Data Center Services 03.005 20000 37791.25 2015 NULL 28577.00
03 Data Center Services 03.006 12000 31344.00 NULL NULL 12600.00
03 Data Center Services 03.006 12000 15123.15 2015 NULL 22400.00
04 Hardware 04.001 101000 101087.00 2015 NULL NULL
04 Hardware 04.002 32000 12634.34 NULL NULL NULL
04 Hardware 04.002 32000 31899.62 2015 NULL NULL
04 Hardware 04.003 46000 832.84 NULL NULL NULL
04 Hardware 04.003 46000 45823.00 2015 NULL NULL
04 Hardware 04.004 110000 1602683.82 NULL 1681060.23 NULL
04 Hardware 04.004 110000 905745.24 2015 985079.88 NULL
04 Hardware 04.005 32000 31899.62 2015 NULL NULL
04 Hardware 04.006 46000 29048.20 2015 NULL NULL
04 Hardware 04.007 45000 45000.00 2015 NULL NULL
04 Hardware 04.008 38000 18872.00 NULL NULL 389.98
04 Hardware 04.008 38000 46068.86 2015 NULL 4679.76
05 Operation Expenses 05.001 40000 NULL NULL NULL 3819.11
05 Operation Expenses 05.002 8000 NULL NULL NULL NULL
05 Operation Expenses 05.003 12000 NULL NULL NULL 1768.74
06 Salaries and Compensations 06.001 832000 NULL NULL NULL NULL
06 Salaries and Compensations 06.002 18000 NULL NULL NULL NULL
07 Software 07.001 23000 31228.00 2015 NULL NULL
07 Software 07.002 9000 6124.36 NULL NULL NULL
07 Software 07.002 9000 10602.99 2015 NULL NULL
07 Software 07.003 36000 25036.00 NULL NULL NULL
07 Software 07.003 36000 35606.86 2015 NULL NULL
07 Software 07.004 24000 375.00 NULL NULL NULL
07 Software 07.004 24000 23674.50 2015 NULL NULL
07 Software 07.005 124000 259218.00 NULL NULL 65160.00
07 Software 07.005 124000 240773.65 2015 NULL 43440.00
07 Software 07.006 54000 7368.00 NULL NULL 8242.35
07 Software 07.006 54000 90124.50 2015 NULL 30221.95
07 Software 07.007 30000 18297.38 NULL NULL 899.10
07 Software 07.007 30000 33529.99 2015 NULL 1198.80
08 Supplies 08.001 80000 3564.00 NULL NULL 19305.31
08 Supplies 08.001 80000 80310.84 2015 NULL 115831.86
09 System Support Services 09.001 80000 NULL NULL NULL 135192.32
09 System Support Services 09.002 135000 1944750.00 NULL 1142889.90 NULL
09 System Support Services 09.002 135000 1749757.95 2015 1217833.50 NULL
09 System Support Services 09.003 39000 49612.65 NULL NULL NULL
09 System Support Services 09.003 39000 9560.00 2015 NULL NULL
09 System Support Services 09.004 30000 2695.00 NULL NULL NULL
09 System Support Services 09.005 59000 NULL NULL NULL NULL
09 System Support Services 09.006 48000 7160.00 NULL NULL 15845.15
09 System Support Services 09.007 62000 118059.27 NULL NULL NULL
09 System Support Services 09.007 62000 43209.364 2015 NULL NULL
09 System Support Services 9.009 NULL NULL NULL NULL NULL

Here is the same results in Access 2010
BudgetCDSubtotals(P2)
ParentCD ParentCDDesc BdgCD CD_Description Annual_Budget Totals CATotals MisTotals Spent Balance Year
01 Communication 01.001 CERVALIS COMMS $81,000.00 $27,667.55 $27,667.55 $53,332.45 2015
01 Communication 01.002 MTVL Communication $71,000.00 $2,190.00 $69,585.93 $71,775.93 ($775.93) 2015
01 Communication 01.003 NY Communication $308,000.00 $110,301.72 $110,301.72 $197,698.28 2015
010 Training 010.001 IT Training Courses $125,000.00 $37,715.00 $25,041.25 $62,756.25 $62,243.75 2015
010 Training 010.002 IT staff training travel and lodging expenses $25,000.00 $0.00 $25,000.00 2015
02 Consulting 02.001 Systems Professional Services $120,000.00 $36,800.00 $20,899.85 $57,699.85 $62,300.15 2015
02 Consulting 02.002 Business Software Architecture and Development $212,000.00 $37,267.57 $37,267.57 $174,732.43 2015
02 Consulting 02.003 Systems Software Architecture and Development $68,000.00 $19,250.00 $10,290.00 $29,540.00 $38,460.00 2015
03 Data Center Services 03.001 CERVALIS Data Center Annual Rental $96,000.00 $25,200.00 $25,200.00 $70,800.00 2015
03 Data Center Services 03.002 CERVALIS Data Center SETUP $50,000.00 $0.00 $50,000.00 2015
03 Data Center Services 03.003 MTVL Data Center Annual Rental $45,000.00 $0.00 $45,000.00 2015
03 Data Center Services 03.004 MTVL Data Center Generator Maintenance $4,000.00 $2,948.00 $1,251.90 $4,199.90 ($199.90) 2015
03 Data Center Services 03.005 Supporting components $20,000.00 $7,558.25 $7,144.25 $14,702.50 $5,297.50 2015
03 Data Center Services 03.006 Technician Services $12,000.00 $15,123.15 $1,400.00 $16,523.15 ($4,523.15) 2015
04 Hardware 04.001 CISCO ROUTERS, SWITCHES, FIREWALLS, KVM $101,000.00 $101,087.00 $101,087.00 ($87.00) 2015
04 Hardware 04.002 EMC 2500 DATA DOMAIN $32,000.00 $31,899.62 $31,899.62 $100.38 2015
04 Hardware 04.003 VMWARE SERVERS FARM EXPANSION $46,000.00 $45,823.00 $45,823.00 $177.00 2015
04 Hardware 04.004 DESKTOP EQUIPMENT $110,000.00 $50,319.18 $3,946.39 $54,265.57 $55,734.43 2015
04 Hardware 04.005 EMC 2500 DATA DOMAIN $32,000.00 $31,899.62 $31,899.62 $100.38 2015
04 Hardware 04.006 CISCO SERVERS RACK MOUNTABLE $46,000.00 $29,048.20 $29,048.20 $16,951.80 2015
04 Hardware 04.007 VMWARE SERVERS FARM EXPANSION $45,000.00 $45,000.00 $45,000.00 $0.00 2015
04 Hardware 04.008 CISCO ROUTERS, SWITCHES, FIREWALLS, KVM $38,000.00 $46,068.86 $389.98 $46,458.84 ($8,458.84) 2015
05 Operation Expenses 05.001 IT staff travel expenses to Data Centers $40,000.00 $3,819.11 $3,819.11 $36,180.89 2015
05 Operation Expenses 05.002 IT staff travel expenses to CA $8,000.00 $0.00 $8,000.00 2015
05 Operation Expenses 05.003 Other IT staff related operations expenses $12,000.00 $1,768.74 $1,768.74 $10,231.26 2015
06 Salaries and Compensations 06.001 Monthly compensation $832,000.00 $0.00 $832,000.00 2015
06 Salaries and Compensations 06.002 Special bonus $18,000.00 $0.00 $18,000.00 2015
07 Software 07.001 VEEAM Backup Solution Software $23,000.00 $31,228.00 $31,228.00 ($8,228.00) 2015
07 Software 07.002 EMC DATA DOMAIN 2500 Software $9,000.00 $10,602.99 $10,602.99 ($1,602.99) 2015
07 Software 07.003 Core Switch NEXUS 7K Software $36,000.00 $35,606.86 $35,606.86 $393.14 2015
07 Software 07.004 Cisco UCS License for FC ports $24,000.00 $23,674.50 $23,674.50 $325.50 2015
07 Software 07.005 Business software applications $124,000.00 $48,154.73 $5,430.00 $53,584.73 $70,415.27 2015
07 Software 07.006 Desktop computing licenses $54,000.00 $45,062.25 $2,245.00 $47,307.25 $6,692.75 2015
07 Software 07.007 Other licences $30,000.00 $33,529.99 $602.35 $34,132.34 ($4,132.34) 2015
08 Supplies 08.001 DESKTOP Support accessories and consumables $80,000.00 $13,385.14 $19,305.31 $32,690.45 $47,309.55 2015
09 System Support Services 09.001 IBM Maintenance $80,000.00 $135,192.32 $135,192.32 ($55,192.32) 2015
09 System Support Services 09.002 Cisco SmartNet $135,000.00 $116,650.53 $8,808.08 $125,458.61 $9,541.39 2015
09 System Support Services 09.003 VEEAM Backup Project Support $39,000.00 $9,560.00 $9,560.00 $29,440.00 2015
09 System Support Services 09.004 Network Security and Domain renewals $30,000.00 $0.00 $30,000.00 2015
09 System Support Services 09.005 Microsoft office 365 $59,000.00 $4,968.00 $4,968.00 $54,032.00 2015
09 System Support Services 09.006 Security Related Renewals $48,000.00 $15,845.15 $15,845.15 $32,154.85 2015
09 System Support Services 09.007 Vendor Support Services $62,000.00 $43,209.36 $43,209.36 $18,790.64 2015

Please let me know what else I can do


#4

It looks like the sum of each BdgCD are not grouping by year and instead is adding every year together not only 2015


#5

Try using isnull on the calculation inside sum:

select ...
      ,sum(isnull(Items.Qty*Items.Price,0)) as Total
      ,year(PurchaseOrders.[PO Date]) as [Year]
      ,sum(isnull(CAItems.Qty*CAItems.Price,0)) as CAItemsTotals
      ,sum(isnull(MiscellaneousItems.Subtotal,0)) as MiscSubTotals
      ...

If this doesn't work, please provide:

  • table descriptions in the form of create statements
  • sample data in the form of insert statements
  • expected output from the sample data you provide

#6

PCD ParentCDDesc BdgCD CD_Description Annual_Budget Totals CATotals MisTotals Spent Balance Year
01 Communication 01.001 CERVALIS COMMS $81,000.00 $27,667.55 $27,667.55 $53,332.45 2015
01 Communication 01.002 MTVL Communication $71,000.00 $2,190.00 $69,585.93 $71,775.93 ($775.93) 2015
01 Communication 01.003 NY Communication $308,000.00 $110,301.72 $110,301.72 $197,698.28 2015
010 Training 010.001 IT Training Courses $125,000.00 $37,715.00 $25,041.25 $62,756.25 $62,243.75 2015
010 Training 010.002 IT staff training travel $25,000.00 $0.00 $25,000.00 2015
02 Consulting 02.001 Systems Professional $120,000.00 $36,800.00 $20,899.85 $57,699.85 $62,300.15 2015
02 Consulting 02.002 Business Software $212,000.00 $37,267.57 $37,267.57 $174,732.43 2015
02 Consulting 02.003 Systems Software $68,000.00 $19,250.00 $10,290.00 $29,540.00 $38,460.00 2015
03 Data Center Ser 03.001 CERVALIS Data $96,000.00 $25,200.00 $25,200.00 $70,800.00 2015
03 Data Center Ser 03.002 CERVALIS Data $50,000.00 $0.00 $50,000.00 2015
03 Data Center Ser 03.


#7

Sorry for the mess I am trying to upload a little sample in another format


#8

This is a sample of the desirable output in access. The output should have 42 records and each row should list the different totals with at the end should add across to calculate the spent which should be subtracted from the Annnual Budget to show the balance, However, I think my problem is on the sorting by year statement ex........ and year(PurchaseOrders.[PO Date])=2015 because the SQL version is returning not only the numbers from 2015 but also from other years which instead of showing one line for each of the BdgCD Code is showing me more than one depending if other years has values.

PCD PCDDesc BdgCD CD_Description Annual_Budget Totals CATotals MisTotals Spent Balance Year
1 Communication 1.001 CERVALIS COMMS $81,000.00 $27,667.55 $27,667.55 $53,332.45 2015
1 Communication 1.002 MTVL Communication $71,000.00 $2,190.00 $69,585.93 $71,775.93 ($775.93) 2015
1 Communication 1.003 NY Communication $308,000.00 $110,301.72 $110,301.72 $197,698.28 2015


#9

here is a sample of the tables in their data

create table ParentCD
(ID nvarchar(255) not null,
[ParentCDDesc] nvarchar (255) null.....)

insert Into ParentCD
(ID, [ParentCDDesc])
Values(01,'Communications')...

CREATE TABLE BudgetCD
(BdgCd nvarchar(255) not null,
CD_Description nvarchar(255)not null,
BdgCDDesc nvarchar(255) null....)

insert into BudgetCD
(BdgCD, ParentCD, CD_Description)
Values(01.0001, 01, 'Cervalis Communications'....)

create table [Annual_Budget_TB]
(BdgCD nvarchar(255) null,
[Bdg_Year] date null,
[Annual_Budget] int null...)

Insert into [Annual_Budget_TB]
(BdgCd, [Bdg_Year], [Annual_Budget])
values(1.001, 2015, 81000...)

create table items
(PurchaseOrdersNo int null,
Qty int null,
Price int null,
BudgetCD nvarchar(255) null...)

insert into Items
(PurchaseOrdersNo, Qty, Price, BudgetCD)
values(2421, 5, 50, 1.001....)

create table PurchaseOrders
(PurchaseOrdersNo int not null,
[PO Date] date null...)

Insert into PurchaseOrders
(PurchaseOrdersNo, [PO Date])
values( 2421, 01/01/2015....)

Create table CAPurchaseOrders
(CAPurchaseOrdersNo int not null,
[PO Date] date null...)

Insert into CAPurchaseOrders
(PurchaseOrdersNo, [PO Date])
values( 150, 02/01/2015....)

create table CAItems
(PurchaseOrdersNo int null,
Qty int null,
Price int null,
CABudgetCD nvarchar(255) null...)

insert into CAItems
(PurchaseOrdersNo, Qty, Price, CABudgetCD)
values(150, 2, 250, 1.001....)

create table Miscellaneous
(MisBdgCD nvarchar (255) null...)

insert into Miscellaneous
(MisBdgCD)
values(1.001...)

Create table MiscellaneousItems
([Po Date] date null,
subtotal int null.....)

insert into MiscellaneousItems
([PO Date], subtotal)
values(01/01/2015, 3900...)


#10

How about this:

select p.id
      ,p.parentcddesc
      ,b.bgdcd
      ,ab.annual_budget
      ,po.total
      ,year(po.[po date]) as [year]
      ,ca.caitemstotals
      ,m.miscsubtotals
  from parentcd as p
       inner join budgetcd as b
               on b.parentcd=p.parentcd
       left outer join annual_budget_tb as ab
                    on ab.bgdcd=b.bgdcd
                   and ab.bgd_year>=cast('2015-01-01' as date)
                   and ab.bgd_year< cast('2016-01-01' as date)
       left outer join (select i.budgetcd
                              ,sum(i.qty*i-price) as total
                          from purchaseorders as p
                               inner join items as i
                                       on i.purchaseordersno=p.purchaseordersno
                         where p.[po date]>=cast('2015-01-01' as date)
                           and p.[po date]< cast('2016-01-01' as date)
                         group by i.budgetcd
                       ) as po
                    on po.budgetcd=b.bgdcd
       left outer join (select i.cabudgetcd
                              ,sum(i.qty*i-price) as caitemstotals
                          from capurchaseorders as p
                               inner join caitems as i
                                       on i.purchaseordersno=p.purchaseordersno
                         where p.[po date]>=cast('2015-01-01' as date)
                           and p.[po date]< cast('2016-01-01' as date)
                         group by i.cabudgetcd
                       ) as ca
                    on ca.cabudgetcd=b.bgdcd
       left outer join (select m.misbdgcd
                              ,sum(i.subtotal) as miscsubtotals
                          from miscellaneous as m
                               inner join miscellaneousitems as i
                                       on i.id=m.id
                                      and i.[po date]>=cast('2015-01-01' as date)
                                      and i.[po date]< cast('2016-01-01' as date)
                         group by m.misbdgcd
                       ) as m
                    on m.misbdgcd=b.bgdcd
;

#11

waoo thank you sooo much it finally works perfectly!!!