Discussion:
subquery question
(too old to reply)
Uri Dimant
2009-06-18 09:17:24 UTC
Permalink
Mike
Wow, how do you think we can test it? Please post sample data+ epxected
result
I am struggling with some complex SQL. The part 'Last Cumulative
Outputs Difference' below needs a further where clause wrapped around it
and I am not sure how to do this as it is already a subquery within the
main query. Can anybody help?
[code]SELECT targ.new_programmemoidname AS 'Programme',
targ.new_programcontracouttidname AS 'Programme Contract',
targ.new_monthlyoutputsidname AS 'Output',
targ.new_monthnumber AS 'Month',
targ.new_monthlyprofileoutidname as 'Month Name',
targ.new_enddateutc as 'End Date',
CASE WHEN MAX(CASE WHEN targ.new_enddateutc < CURRENT_TIMESTAMP
THEN targ.new_enddateutc
END) OVER() = targ.new_enddateutc
THEN 'Yes'
ELSE 'No'
END as 'Is Last Occuring',
(select max(targ4.new_monthnumber)
FROM FilteredNew_monthlyoutput AS targ4
inner join dbo.FilteredNew_outputlookup l4
on l4.new_outputlookupid = targ4.new_monthlyoutputsid
WHERE targ4.new_programmemoidname = targ.new_programmemoidname
AND targ4.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ4.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
and targ4.new_enddateutc < CURRENT_TIMESTAMP) as 'Last Occuring',
targ.new_numberofoutputs AS 'Target Outputs',
COUNT(act.new_outputevmonthnumber) AS 'Actual Outputs',
case when targ.new_numberofoutputs -
COUNT(act.new_outputevmonthnumber) < 0 then 0 else
COUNT(act.new_outputevmonthnumber) - targ.new_numberofoutputs end AS '
Outputs Difference',
(select sum(targ3.new_numberofoutputs)
from FilteredNew_monthlyoutput targ3
inner join FilteredNew_outputlookup l
on targ3.new_monthlyoutputsid = l.new_outputlookupid
WHERE targ3.new_programmemoidname = targ.new_programmemoidname
AND targ3.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ3.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ3.new_monthnumber <= targ.new_monthnumber
and targ3.statecode = 0 and l.statecode = 0) AS 'Cumulative Target
Outputs',
(SELECT COUNT(*)
FROM FilteredNew_evidence AS act2
JOIN FilteredNew_monthlyoutput AS targ2
ON targ2.new_monthlyprofileoutid = act2.new_claimperiodid
AND targ2.new_monthlyoutputsid = act2.new_evidenceforoutputid
WHERE targ2.new_programmemoidname = targ.new_programmemoidname
AND targ2.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ2.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ2.new_monthnumber <= targ.new_monthnumber
and act2.statecode = 0 and targ2.statecode = 0) AS 'Cumulative
Actual Outputs',
(SELECT COUNT(*)
FROM FilteredNew_evidence AS act4
JOIN FilteredNew_monthlyoutput AS targ4
ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid
AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid
WHERE targ4.new_programmemoidname = targ.new_programmemoidname
AND targ4.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ4.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ4.new_monthnumber <= targ.new_monthnumber
and act4.statecode = 0 and targ4.statecode = 0) -
(select sum(targ5.new_numberofoutputs)
from FilteredNew_monthlyoutput targ5
inner join FilteredNew_outputlookup l2
on targ5.new_monthlyoutputsid = l2.new_outputlookupid
WHERE targ5.new_programmemoidname = targ.new_programmemoidname
AND targ5.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ5.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ5.new_monthnumber <= targ.new_monthnumber
and targ5.statecode = 0 and l2.statecode = 0) as 'Last
Cumulative Outputs Difference',
(SELECT COUNT(*)
FROM FilteredNew_evidence AS act4
JOIN FilteredNew_monthlyoutput AS targ4
ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid
AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid
WHERE targ4.new_programmemoidname = targ.new_programmemoidname
AND targ4.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ4.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ4.new_monthnumber <= targ.new_monthnumber
and act4.statecode = 0 and targ4.statecode = 0) -
(select sum(targ5.new_numberofoutputs)
from FilteredNew_monthlyoutput targ5
inner join FilteredNew_outputlookup l2
on targ5.new_monthlyoutputsid = l2.new_outputlookupid
WHERE targ5.new_programmemoidname = targ.new_programmemoidname
AND targ5.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ5.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ5.new_monthnumber <= targ.new_monthnumber
and targ5.statecode = 0 and l2.statecode = 0) as 'Cumulative
Outputs Difference',
CASE WHEN targ.new_numberofoutputs = 0 THEN 0 ELSE
round(cast(count(act.new_outputevmonthnumber) as
float)/targ.new_numberofoutputs * 100, 0) END as '% Achieved',
l.new_unitrate_base as 'Unit Rate',
l.new_unitrate_base * targ.new_numberofoutputs as 'Target Income',
l.new_unitrate_base * COUNT(act.new_outputevmonthnumber) AS
'Actual Income',
(l.new_unitrate_base * COUNT(act.new_outputevmonthnumber)) -
(l.new_unitrate_base * targ.new_numberofoutputs) as 'Income Difference',
(SELECT l.new_unitrate_base * COUNT(act4.new_outputevmonthnumber)
FROM FilteredNew_evidence AS act4
JOIN FilteredNew_monthlyoutput AS targ4
ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid
AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid
WHERE targ4.new_programmemoidname = targ.new_programmemoidname
AND targ4.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ4.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ4.new_monthnumber <= targ.new_monthnumber
and act4.statecode = 0 and targ4.statecode = 0) -
(select sum(l.new_unitrate_base * targ3.new_numberofoutputs)
from FilteredNew_monthlyoutput targ3
inner join FilteredNew_outputlookup l
on targ3.new_monthlyoutputsid = l.new_outputlookupid
WHERE targ3.new_programmemoidname = targ.new_programmemoidname
AND targ3.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ3.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ3.new_monthnumber <= targ.new_monthnumber
and targ3.statecode = 0 and l.statecode = 0) as 'Cumulative Income
Difference'
FROM FilteredNew_monthlyoutput AS targ
inner join dbo.FilteredNew_outputlookup l
on l.new_outputlookupid = targ.new_monthlyoutputsid
LEFT JOIN FilteredNew_evidence AS act
ON targ.new_monthlyprofileoutid = act.new_claimperiodid
AND targ.new_monthlyoutputsid = act.new_evidenceforoutputid
where
targ.statecode = 0 and
l.statecode = 0
and targ.new_programmemoidname like 'MCC RSL%' and
targ.new_programcontracouttidname = 'No Programme Contract' and
targ.new_monthlyoutputsidname = 'Residents Engaged'
GROUP BY targ.new_programmemoidname,
targ.new_programcontracouttidname,
targ.new_monthlyoutputsidname,
targ.new_monthnumber,
targ.new_enddateutc,
targ.new_numberofoutputs,
l.new_unitrate_base,
targ.new_monthlyprofileoutidname
ORDER BY targ.new_programmemoidname,
targ.new_programcontracouttidname,
targ.new_monthlyoutputsidname,
targ.new_monthnumber[/code]
*** Sent via Developersdex http://www.developersdex.com ***
Mike P
2009-06-18 10:10:48 UTC
Permalink
Uri,

The problem is that the subquery references a table (targ)further down
the main query, so I would have to make your second example more like
this :

SELECT *

FROM

(

(SELECT COUNT(*)
FROM FilteredNew_evidence AS act4
JOIN FilteredNew_monthlyoutput AS targ4
ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid
AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid
WHERE targ4.new_programmemoidname = targ.new_programmemoidname
AND targ4.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ4.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ4.new_monthnumber <= targ.new_monthnumber
and act4.statecode = 0 and targ4.statecode = 0) -

(select sum(targ5.new_numberofoutputs)
from FilteredNew_monthlyoutput targ5
inner join FilteredNew_outputlookup l2
on targ5.new_monthlyoutputsid = l2.new_outputlookupid
WHERE targ5.new_programmemoidname = targ.new_programmemoidname
AND targ5.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ5.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ5.new_monthnumber <= targ.new_monthnumber
and targ5.statecode = 0 and l2.statecode = 0) as 'Last
Cumulative Outputs Difference'

FROM FilteredNew_monthlyoutput AS targ
inner join dbo.FilteredNew_outputlookup l
on l.new_outputlookupid = targ.new_monthlyoutputsid
LEFT JOIN FilteredNew_evidence AS act
ON targ.new_monthlyprofileoutid = act.new_claimperiodid
AND targ.new_monthlyoutputsid = act.new_evidenceforoutputid

where
targ.statecode = 0 and
l.statecode = 0

GROUP BY targ.new_programmemoidname,
targ.new_programcontracouttidname,
targ.new_monthlyoutputsidname,
targ.new_monthnumber,
targ.new_enddateutc,
targ.new_numberofoutputs,
l.new_unitrate_base,
targ.new_monthlyprofileoutidname
ORDER BY targ.new_programmemoidname,
targ.new_programcontracouttidname,
targ.new_monthlyoutputsidname,
targ.new_monthnumber
) AS Der

But this gives me errors :

Incorrect syntax near '-'.
Incorrect syntax near the keyword 'as'.

Any ideas why?



*** Sent via Developersdex http://www.developersdex.com ***
Uri Dimant
2009-06-18 10:36:10 UTC
Permalink
Mike
1) If ou are using SQL Server 2005 , I suggest using CTE (common table
expression) for readabilty.
Row_Number () over (ORDER BY colum) is a new function introduced in SQL
Server 2005 and thus I wanted to get a key (numerator) to calculate the data
from both CTE
2)
I tested the below on Northwind database to mimic your query, see if that
helps you
select * from

(

select(select count(*) from orders)

+(select sum(orderid) from orders) as c

) as d
Post by Mike P
Uri,
The problem is that the subquery references a table (targ)further down
the main query, so I would have to make your second example more like
SELECT *
FROM
(
(SELECT COUNT(*)
FROM FilteredNew_evidence AS act4
JOIN FilteredNew_monthlyoutput AS targ4
ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid
AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid
WHERE targ4.new_programmemoidname = targ.new_programmemoidname
AND targ4.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ4.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ4.new_monthnumber <= targ.new_monthnumber
and act4.statecode = 0 and targ4.statecode = 0) -
(select sum(targ5.new_numberofoutputs)
from FilteredNew_monthlyoutput targ5
inner join FilteredNew_outputlookup l2
on targ5.new_monthlyoutputsid = l2.new_outputlookupid
WHERE targ5.new_programmemoidname = targ.new_programmemoidname
AND targ5.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ5.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ5.new_monthnumber <= targ.new_monthnumber
and targ5.statecode = 0 and l2.statecode = 0) as 'Last
Cumulative Outputs Difference'
FROM FilteredNew_monthlyoutput AS targ
inner join dbo.FilteredNew_outputlookup l
on l.new_outputlookupid = targ.new_monthlyoutputsid
LEFT JOIN FilteredNew_evidence AS act
ON targ.new_monthlyprofileoutid = act.new_claimperiodid
AND targ.new_monthlyoutputsid = act.new_evidenceforoutputid
where
targ.statecode = 0 and
l.statecode = 0
GROUP BY targ.new_programmemoidname,
targ.new_programcontracouttidname,
targ.new_monthlyoutputsidname,
targ.new_monthnumber,
targ.new_enddateutc,
targ.new_numberofoutputs,
l.new_unitrate_base,
targ.new_monthlyprofileoutidname
ORDER BY targ.new_programmemoidname,
targ.new_programcontracouttidname,
targ.new_monthlyoutputsidname,
targ.new_monthnumber
) AS Der
Incorrect syntax near '-'.
Incorrect syntax near the keyword 'as'.
Any ideas why?
*** Sent via Developersdex http://www.developersdex.com ***
Uri Dimant
2009-06-18 09:55:13 UTC
Permalink
Last, ROW_NUMBER should not have asteric.
Sorry,should be
with count_cte
as
(
SELECT COUNT(*) as cnt,row_number*() over (....) as id
FROM FilteredNew_evidence AS act4
JOIN FilteredNew_monthlyoutput AS targ4
ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid
AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid
WHERE targ4.new_programmemoidname = targ.new_programmemoidname
AND targ4.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ4.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ4.new_monthnumber <= targ.new_monthnumber
and act4.statecode = 0 and targ4.statecode = 0
),sum_count
as
(
select sum(targ5.new_numberofoutputs)as sum_col,
row_number*() over (....) as id
from FilteredNew_monthlyoutput targ5
inner join FilteredNew_outputlookup l2
on targ5.new_monthlyoutputsid = l2.new_outputlookupid
WHERE targ5.new_programmemoidname = targ.new_programmemoidname
AND targ5.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ5.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ5.new_monthnumber <= targ.new_monthnumber
and targ5.statecode = 0 and l2.statecode = 0
) select cnt-sum_col from count_cte join sum_count on
count_cte.id=count_cte.id
...... where
Mike
with count_cte
as
(
SELECT COUNT(*) as cnt,row_number*() over (....) as id
FROM FilteredNew_evidence AS act4
JOIN FilteredNew_monthlyoutput AS targ4
ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid
AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid
WHERE targ4.new_programmemoidname = targ.new_programmemoidname
AND targ4.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ4.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ4.new_monthnumber <= targ.new_monthnumber
and act4.statecode = 0 and targ4.statecode = 0
),sum_count
as
(
select sum(targ5.new_numberofoutputs)as sum_col,
row_number*() over (....) as id
from FilteredNew_monthlyoutput targ5
inner join FilteredNew_outputlookup l2
on targ5.new_monthlyoutputsid = l2.new_outputlookupid
WHERE targ5.new_programmemoidname = targ.new_programmemoidname
AND targ5.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ5.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ5.new_monthnumber <= targ.new_monthnumber
and targ5.statecode = 0 and l2.statecode = 0
),cte_final
as
(
select cnt-sum_col from count_cte join sum_count on
count_cte.id=count_cte.id
...... where
)
or (untested)
SELECT *
FROM
(
(SELECT COUNT(*)
FROM FilteredNew_evidence AS act4
JOIN FilteredNew_monthlyoutput AS targ4
ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid
AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid
WHERE targ4.new_programmemoidname = targ.new_programmemoidname
AND targ4.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ4.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ4.new_monthnumber <= targ.new_monthnumber
and act4.statecode = 0 and targ4.statecode = 0) -
(select sum(targ5.new_numberofoutputs)
from FilteredNew_monthlyoutput targ5
inner join FilteredNew_outputlookup l2
on targ5.new_monthlyoutputsid = l2.new_outputlookupid
WHERE targ5.new_programmemoidname = targ.new_programmemoidname
AND targ5.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ5.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ5.new_monthnumber <= targ.new_monthnumber
and targ5.statecode = 0 and l2.statecode = 0) as 'Last
Cumulative Outputs Difference'
) AS Der WHERE ...........................................
Uri,
I am not expecting you to test the SQL, all I want to know is what
syntax I would need to wrap a where clause around the subquery 'Last
Cumulative Outputs Difference'.
(SELECT COUNT(*)
FROM FilteredNew_evidence AS act4
JOIN FilteredNew_monthlyoutput AS targ4
ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid
AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid
WHERE targ4.new_programmemoidname = targ.new_programmemoidname
AND targ4.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ4.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ4.new_monthnumber <= targ.new_monthnumber
and act4.statecode = 0 and targ4.statecode = 0) -
(select sum(targ5.new_numberofoutputs)
from FilteredNew_monthlyoutput targ5
inner join FilteredNew_outputlookup l2
on targ5.new_monthlyoutputsid = l2.new_outputlookupid
WHERE targ5.new_programmemoidname = targ.new_programmemoidname
AND targ5.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ5.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ5.new_monthnumber <= targ.new_monthnumber
and targ5.statecode = 0 and l2.statecode = 0) as 'Last
Cumulative Outputs Difference'
*** Sent via Developersdex http://www.developersdex.com ***
Mike P
2009-06-18 10:02:44 UTC
Permalink
Uri,

I am not familiar with the Over keyword...what does the (...) represent
where you have put over (...)?



*** Sent via Developersdex http://www.developersdex.com ***
Uri Dimant
2009-06-18 09:41:59 UTC
Permalink
Mike
with count_cte

as

(

SELECT COUNT(*) as cnt,row_number*() over (....) as id

FROM FilteredNew_evidence AS act4

JOIN FilteredNew_monthlyoutput AS targ4

ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid

AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid

WHERE targ4.new_programmemoidname = targ.new_programmemoidname

AND targ4.new_programcontracouttidname =

targ.new_programcontracouttidname

AND targ4.new_monthlyoutputsidname =

targ.new_monthlyoutputsidname

AND targ4.new_monthnumber <= targ.new_monthnumber

and act4.statecode = 0 and targ4.statecode = 0

),sum_count

as

(

select sum(targ5.new_numberofoutputs)as sum_col,

row_number*() over (....) as id

from FilteredNew_monthlyoutput targ5

inner join FilteredNew_outputlookup l2

on targ5.new_monthlyoutputsid = l2.new_outputlookupid

WHERE targ5.new_programmemoidname = targ.new_programmemoidname

AND targ5.new_programcontracouttidname =

targ.new_programcontracouttidname

AND targ5.new_monthlyoutputsidname =

targ.new_monthlyoutputsidname

AND targ5.new_monthnumber <= targ.new_monthnumber

and targ5.statecode = 0 and l2.statecode = 0

),cte_final

as

(

select cnt-sum_col from count_cte join sum_count on
count_cte.id=count_cte.id

...... where

)

or (untested)

SELECT *

FROM

(

(SELECT COUNT(*)
FROM FilteredNew_evidence AS act4
JOIN FilteredNew_monthlyoutput AS targ4
ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid
AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid
WHERE targ4.new_programmemoidname = targ.new_programmemoidname
AND targ4.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ4.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ4.new_monthnumber <= targ.new_monthnumber
and act4.statecode = 0 and targ4.statecode = 0) -

(select sum(targ5.new_numberofoutputs)
from FilteredNew_monthlyoutput targ5
inner join FilteredNew_outputlookup l2
on targ5.new_monthlyoutputsid = l2.new_outputlookupid
WHERE targ5.new_programmemoidname = targ.new_programmemoidname
AND targ5.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ5.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ5.new_monthnumber <= targ.new_monthnumber
and targ5.statecode = 0 and l2.statecode = 0) as 'Last
Cumulative Outputs Difference'
) AS Der WHERE ...........................................
Uri,
I am not expecting you to test the SQL, all I want to know is what
syntax I would need to wrap a where clause around the subquery 'Last
Cumulative Outputs Difference'.
(SELECT COUNT(*)
FROM FilteredNew_evidence AS act4
JOIN FilteredNew_monthlyoutput AS targ4
ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid
AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid
WHERE targ4.new_programmemoidname = targ.new_programmemoidname
AND targ4.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ4.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ4.new_monthnumber <= targ.new_monthnumber
and act4.statecode = 0 and targ4.statecode = 0) -
(select sum(targ5.new_numberofoutputs)
from FilteredNew_monthlyoutput targ5
inner join FilteredNew_outputlookup l2
on targ5.new_monthlyoutputsid = l2.new_outputlookupid
WHERE targ5.new_programmemoidname = targ.new_programmemoidname
AND targ5.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ5.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ5.new_monthnumber <= targ.new_monthnumber
and targ5.statecode = 0 and l2.statecode = 0) as 'Last
Cumulative Outputs Difference'
*** Sent via Developersdex http://www.developersdex.com ***
Uri Dimant
2009-06-18 09:46:15 UTC
Permalink
Sorry,should be
with count_cte

as

(

SELECT COUNT(*) as cnt,row_number*() over (....) as id

FROM FilteredNew_evidence AS act4

JOIN FilteredNew_monthlyoutput AS targ4

ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid

AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid

WHERE targ4.new_programmemoidname = targ.new_programmemoidname

AND targ4.new_programcontracouttidname =

targ.new_programcontracouttidname

AND targ4.new_monthlyoutputsidname =

targ.new_monthlyoutputsidname

AND targ4.new_monthnumber <= targ.new_monthnumber

and act4.statecode = 0 and targ4.statecode = 0

),sum_count

as

(

select sum(targ5.new_numberofoutputs)as sum_col,

row_number*() over (....) as id

from FilteredNew_monthlyoutput targ5

inner join FilteredNew_outputlookup l2

on targ5.new_monthlyoutputsid = l2.new_outputlookupid

WHERE targ5.new_programmemoidname = targ.new_programmemoidname

AND targ5.new_programcontracouttidname =

targ.new_programcontracouttidname

AND targ5.new_monthlyoutputsidname =

targ.new_monthlyoutputsidname

AND targ5.new_monthnumber <= targ.new_monthnumber

and targ5.statecode = 0 and l2.statecode = 0

) select cnt-sum_col from count_cte join sum_count on
count_cte.id=count_cte.id

...... where
Mike
with count_cte
as
(
SELECT COUNT(*) as cnt,row_number*() over (....) as id
FROM FilteredNew_evidence AS act4
JOIN FilteredNew_monthlyoutput AS targ4
ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid
AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid
WHERE targ4.new_programmemoidname = targ.new_programmemoidname
AND targ4.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ4.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ4.new_monthnumber <= targ.new_monthnumber
and act4.statecode = 0 and targ4.statecode = 0
),sum_count
as
(
select sum(targ5.new_numberofoutputs)as sum_col,
row_number*() over (....) as id
from FilteredNew_monthlyoutput targ5
inner join FilteredNew_outputlookup l2
on targ5.new_monthlyoutputsid = l2.new_outputlookupid
WHERE targ5.new_programmemoidname = targ.new_programmemoidname
AND targ5.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ5.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ5.new_monthnumber <= targ.new_monthnumber
and targ5.statecode = 0 and l2.statecode = 0
),cte_final
as
(
select cnt-sum_col from count_cte join sum_count on
count_cte.id=count_cte.id
...... where
)
or (untested)
SELECT *
FROM
(
(SELECT COUNT(*)
FROM FilteredNew_evidence AS act4
JOIN FilteredNew_monthlyoutput AS targ4
ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid
AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid
WHERE targ4.new_programmemoidname = targ.new_programmemoidname
AND targ4.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ4.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ4.new_monthnumber <= targ.new_monthnumber
and act4.statecode = 0 and targ4.statecode = 0) -
(select sum(targ5.new_numberofoutputs)
from FilteredNew_monthlyoutput targ5
inner join FilteredNew_outputlookup l2
on targ5.new_monthlyoutputsid = l2.new_outputlookupid
WHERE targ5.new_programmemoidname = targ.new_programmemoidname
AND targ5.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ5.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ5.new_monthnumber <= targ.new_monthnumber
and targ5.statecode = 0 and l2.statecode = 0) as 'Last
Cumulative Outputs Difference'
) AS Der WHERE ...........................................
Uri,
I am not expecting you to test the SQL, all I want to know is what
syntax I would need to wrap a where clause around the subquery 'Last
Cumulative Outputs Difference'.
(SELECT COUNT(*)
FROM FilteredNew_evidence AS act4
JOIN FilteredNew_monthlyoutput AS targ4
ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid
AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid
WHERE targ4.new_programmemoidname = targ.new_programmemoidname
AND targ4.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ4.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ4.new_monthnumber <= targ.new_monthnumber
and act4.statecode = 0 and targ4.statecode = 0) -
(select sum(targ5.new_numberofoutputs)
from FilteredNew_monthlyoutput targ5
inner join FilteredNew_outputlookup l2
on targ5.new_monthlyoutputsid = l2.new_outputlookupid
WHERE targ5.new_programmemoidname = targ.new_programmemoidname
AND targ5.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ5.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ5.new_monthnumber <= targ.new_monthnumber
and targ5.statecode = 0 and l2.statecode = 0) as 'Last
Cumulative Outputs Difference'
*** Sent via Developersdex http://www.developersdex.com ***
Mike P
2009-06-18 09:25:32 UTC
Permalink
Uri,

I am not expecting you to test the SQL, all I want to know is what
syntax I would need to wrap a where clause around the subquery 'Last
Cumulative Outputs Difference'.

(SELECT COUNT(*)
FROM FilteredNew_evidence AS act4
JOIN FilteredNew_monthlyoutput AS targ4
ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid
AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid
WHERE targ4.new_programmemoidname = targ.new_programmemoidname
AND targ4.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ4.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ4.new_monthnumber <= targ.new_monthnumber
and act4.statecode = 0 and targ4.statecode = 0) -

(select sum(targ5.new_numberofoutputs)
from FilteredNew_monthlyoutput targ5
inner join FilteredNew_outputlookup l2
on targ5.new_monthlyoutputsid = l2.new_outputlookupid
WHERE targ5.new_programmemoidname = targ.new_programmemoidname
AND targ5.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ5.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ5.new_monthnumber <= targ.new_monthnumber
and targ5.statecode = 0 and l2.statecode = 0) as 'Last
Cumulative Outputs Difference'




*** Sent via Developersdex http://www.developersdex.com ***
Mike P
2009-06-18 11:10:33 UTC
Permalink
Uri,

I am making progress with this thanks to your help.

The problem is that the subquery references a table (targ)further down
the main query. So this is what I have at the moment :

with count_cte

as

(

SELECT COUNT(*) as cnt,row_number() over (order by
targ4.new_programmemoidname) as id

FROM FilteredNew_evidence AS act4

JOIN FilteredNew_monthlyoutput AS targ4

ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid

AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid

WHERE targ4.new_programmemoidname = targ.new_programmemoidname

AND targ4.new_programcontracouttidname =

targ.new_programcontracouttidname

AND targ4.new_monthlyoutputsidname =

targ.new_monthlyoutputsidname

AND targ4.new_monthnumber <= targ.new_monthnumber

and act4.statecode = 0 and targ4.statecode = 0

),sum_count


as

(

select sum(targ5.new_numberofoutputs)as sum_col,

row_number() over (order by targ5.new_programmemoidname) as id

from FilteredNew_monthlyoutput targ5

inner join FilteredNew_outputlookup l2

on targ5.new_monthlyoutputsid = l2.new_outputlookupid

WHERE targ5.new_programmemoidname = targ.new_programmemoidname

AND targ5.new_programcontracouttidname =

targ.new_programcontracouttidname

AND targ5.new_monthlyoutputsidname =

targ.new_monthlyoutputsidname

AND targ5.new_monthnumber <= targ.new_monthnumber

and targ5.statecode = 0 and l2.statecode = 0

) select cnt-sum_col from count_cte join sum_count on
count_cte.id=count_cte.id



And I need to add the following from the main query of the original SQL
I posted :

FROM FilteredNew_monthlyoutput AS targ
inner join dbo.FilteredNew_outputlookup l
on l.new_outputlookupid = targ.new_monthlyoutputsid
LEFT JOIN FilteredNew_evidence AS act
ON targ.new_monthlyprofileoutid = act.new_claimperiodid
AND targ.new_monthlyoutputsid = act.new_evidenceforoutputid


where
targ.statecode = 0 and
l.statecode = 0

and targ.new_programmemoidname like 'MCC RSL%' and
targ.new_programcontracouttidname = 'No Programme Contract' and
targ.new_monthlyoutputsidname = 'Residents Engaged'

GROUP BY targ.new_programmemoidname,
targ.new_programcontracouttidname,
targ.new_monthlyoutputsidname,
targ.new_monthnumber,
targ.new_enddateutc,
targ.new_numberofoutputs,
l.new_unitrate_base,
targ.new_monthlyprofileoutidname
ORDER BY targ.new_programmemoidname,
targ.new_programcontracouttidname,
targ.new_monthlyoutputsidname,
targ.new_monthnumber




*** Sent via Developersdex http://www.developersdex.com ***
Mike P
2009-06-18 11:15:36 UTC
Permalink
This is what I have tried, but it gives syntax errors :

with count_cte

as

(

(SELECT COUNT(*) as cnt,row_number() over (order by
targ4.new_programmemoidname) as id

FROM FilteredNew_evidence AS act4

JOIN FilteredNew_monthlyoutput AS targ4

ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid

AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid

WHERE targ4.new_programmemoidname = targ.new_programmemoidname

AND targ4.new_programcontracouttidname =

targ.new_programcontracouttidname

AND targ4.new_monthlyoutputsidname =

targ.new_monthlyoutputsidname

AND targ4.new_monthnumber <= targ.new_monthnumber

and act4.statecode = 0 and targ4.statecode = 0)

FROM FilteredNew_monthlyoutput AS targ
inner join dbo.FilteredNew_outputlookup l
on l.new_outputlookupid = targ.new_monthlyoutputsid
LEFT JOIN FilteredNew_evidence AS act
ON targ.new_monthlyprofileoutid = act.new_claimperiodid
AND targ.new_monthlyoutputsid = act.new_evidenceforoutputid


where
targ.statecode = 0 and
l.statecode = 0

and targ.new_programmemoidname like 'MCC RSL%' and
targ.new_programcontracouttidname = 'No Programme Contract' and
targ.new_monthlyoutputsidname = 'Residents Engaged'

GROUP BY targ.new_programmemoidname,
targ.new_programcontracouttidname,
targ.new_monthlyoutputsidname,
targ.new_monthnumber,
targ.new_enddateutc,
targ.new_numberofoutputs,
l.new_unitrate_base,
targ.new_monthlyprofileoutidname
ORDER BY targ.new_programmemoidname,
targ.new_programcontracouttidname,
targ.new_monthlyoutputsidname,
targ.new_monthnumber

),sum_count


as

(

(select sum(targ5.new_numberofoutputs)as sum_col,

row_number() over (order by targ5.new_programmemoidname) as id

from FilteredNew_monthlyoutput targ5

inner join FilteredNew_outputlookup l2

on targ5.new_monthlyoutputsid = l2.new_outputlookupid

WHERE targ5.new_programmemoidname = targ.new_programmemoidname

AND targ5.new_programcontracouttidname =

targ.new_programcontracouttidname

AND targ5.new_monthlyoutputsidname =

targ.new_monthlyoutputsidname

AND targ5.new_monthnumber <= targ.new_monthnumber

and targ5.statecode = 0 and l2.statecode = 0)

FROM FilteredNew_monthlyoutput AS targ
inner join dbo.FilteredNew_outputlookup l
on l.new_outputlookupid = targ.new_monthlyoutputsid
LEFT JOIN FilteredNew_evidence AS act
ON targ.new_monthlyprofileoutid = act.new_claimperiodid
AND targ.new_monthlyoutputsid = act.new_evidenceforoutputid


where
targ.statecode = 0 and
l.statecode = 0

and targ.new_programmemoidname like 'MCC RSL%' and
targ.new_programcontracouttidname = 'No Programme Contract' and
targ.new_monthlyoutputsidname = 'Residents Engaged'

GROUP BY targ.new_programmemoidname,
targ.new_programcontracouttidname,
targ.new_monthlyoutputsidname,
targ.new_monthnumber,
targ.new_enddateutc,
targ.new_numberofoutputs,
l.new_unitrate_base,
targ.new_monthlyprofileoutidname
ORDER BY targ.new_programmemoidname,
targ.new_programcontracouttidname,
targ.new_monthlyoutputsidname,
targ.new_monthnumber

) select cnt-sum_col from count_cte join sum_count on
count_cte.id=count_cte.id




*** Sent via Developersdex http://www.developersdex.com ***
Uri Dimant
2009-06-18 11:28:58 UTC
Permalink
Mike
I'm almost lost here:-)) No need ROW_NUMBER , join on new_programmemoidname
and perhaps onther columns, check it out


with count_cte

as

(

SELECT COUNT(*) as cnt,targ.new_programmemoidname,

targ.new_programcontracouttidname,

targ.new_monthlyoutputsidname,

targ.new_monthnumber,

targ.new_enddateutc,

targ.new_numberofoutputs,

l.new_unitrate_base,

targ.new_monthlyprofileoutidname

FROM FilteredNew_evidence AS act4

JOIN FilteredNew_monthlyoutput AS targ4

ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid

AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid

WHERE targ4.new_programmemoidname = targ.new_programmemoidname

AND targ4.new_programcontracouttidname =

targ.new_programcontracouttidname

AND targ4.new_monthlyoutputsidname =

targ.new_monthlyoutputsidname

AND targ4.new_monthnumber <= targ.new_monthnumber

and act4.statecode = 0 and targ4.statecode = 0

FROM FilteredNew_monthlyoutput AS targ

inner join dbo.FilteredNew_outputlookup l

on l.new_outputlookupid = targ.new_monthlyoutputsid

LEFT JOIN FilteredNew_evidence AS act

ON targ.new_monthlyprofileoutid = act.new_claimperiodid

AND targ.new_monthlyoutputsid = act.new_evidenceforoutputid



where

targ.statecode = 0 and

l.statecode = 0

and targ.new_programmemoidname like 'MCC RSL%' and

targ.new_programcontracouttidname = 'No Programme Contract' and

targ.new_monthlyoutputsidname = 'Residents Engaged'

GROUP BY targ.new_programmemoidname,

targ.new_programcontracouttidname,

targ.new_monthlyoutputsidname,

targ.new_monthnumber,

targ.new_enddateutc,

targ.new_numberofoutputs,

l.new_unitrate_base,

targ.new_monthlyprofileoutidname



),sum_count



as

(

select sum(targ5.new_numberofoutputs)as sum_col,

targ.new_programmemoidname,

targ.new_programcontracouttidname,

targ.new_monthlyoutputsidname,

targ.new_monthnumber,

targ.new_enddateutc,

targ.new_numberofoutputs,

l.new_unitrate_base,

targ.new_monthlyprofileoutidname

from FilteredNew_monthlyoutput targ5

inner join FilteredNew_outputlookup l2

on targ5.new_monthlyoutputsid = l2.new_outputlookupid

WHERE targ5.new_programmemoidname = targ.new_programmemoidname

AND targ5.new_programcontracouttidname =

targ.new_programcontracouttidname

AND targ5.new_monthlyoutputsidname =

targ.new_monthlyoutputsidname

AND targ5.new_monthnumber <= targ.new_monthnumber

and targ5.statecode = 0 and l2.statecode = 0

FROM FilteredNew_monthlyoutput AS targ

inner join dbo.FilteredNew_outputlookup l

on l.new_outputlookupid = targ.new_monthlyoutputsid

LEFT JOIN FilteredNew_evidence AS act

ON targ.new_monthlyprofileoutid = act.new_claimperiodid

AND targ.new_monthlyoutputsid = act.new_evidenceforoutputid



where

targ.statecode = 0 and

l.statecode = 0

and targ.new_programmemoidname like 'MCC RSL%' and

targ.new_programcontracouttidname = 'No Programme Contract' and

targ.new_monthlyoutputsidname = 'Residents Engaged'

GROUP BY targ.new_programmemoidname,

targ.new_programcontracouttidname,

targ.new_monthlyoutputsidname,

targ.new_monthnumber,

targ.new_enddateutc,

targ.new_numberofoutputs,

l.new_unitrate_base,

targ.new_monthlyprofileoutidname



) select cnt-sum_col from count_cte join sum_count on

count_cte.new_programmemoidname,=count_cte.new_programmemoidname

ORDER BY

targ.new_programmemoidname,

targ.new_programcontracouttidname,

targ.new_monthlyoutputsidname,

targ.new_monthnumber,

targ.new_enddateutc,

targ.new_numberofoutputs,

l.new_unitrate_base,

targ.new_monthlyprofileoutidname
with count_cte
as
(
(SELECT COUNT(*) as cnt,row_number() over (order by
targ4.new_programmemoidname) as id
FROM FilteredNew_evidence AS act4
JOIN FilteredNew_monthlyoutput AS targ4
ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid
AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid
WHERE targ4.new_programmemoidname = targ.new_programmemoidname
AND targ4.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ4.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ4.new_monthnumber <= targ.new_monthnumber
and act4.statecode = 0 and targ4.statecode = 0)
FROM FilteredNew_monthlyoutput AS targ
inner join dbo.FilteredNew_outputlookup l
on l.new_outputlookupid = targ.new_monthlyoutputsid
LEFT JOIN FilteredNew_evidence AS act
ON targ.new_monthlyprofileoutid = act.new_claimperiodid
AND targ.new_monthlyoutputsid = act.new_evidenceforoutputid
where
targ.statecode = 0 and
l.statecode = 0
and targ.new_programmemoidname like 'MCC RSL%' and
targ.new_programcontracouttidname = 'No Programme Contract' and
targ.new_monthlyoutputsidname = 'Residents Engaged'
GROUP BY targ.new_programmemoidname,
targ.new_programcontracouttidname,
targ.new_monthlyoutputsidname,
targ.new_monthnumber,
targ.new_enddateutc,
targ.new_numberofoutputs,
l.new_unitrate_base,
targ.new_monthlyprofileoutidname
ORDER BY targ.new_programmemoidname,
targ.new_programcontracouttidname,
targ.new_monthlyoutputsidname,
targ.new_monthnumber
),sum_count
as
(
(select sum(targ5.new_numberofoutputs)as sum_col,
row_number() over (order by targ5.new_programmemoidname) as id
from FilteredNew_monthlyoutput targ5
inner join FilteredNew_outputlookup l2
on targ5.new_monthlyoutputsid = l2.new_outputlookupid
WHERE targ5.new_programmemoidname = targ.new_programmemoidname
AND targ5.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ5.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ5.new_monthnumber <= targ.new_monthnumber
and targ5.statecode = 0 and l2.statecode = 0)
FROM FilteredNew_monthlyoutput AS targ
inner join dbo.FilteredNew_outputlookup l
on l.new_outputlookupid = targ.new_monthlyoutputsid
LEFT JOIN FilteredNew_evidence AS act
ON targ.new_monthlyprofileoutid = act.new_claimperiodid
AND targ.new_monthlyoutputsid = act.new_evidenceforoutputid
where
targ.statecode = 0 and
l.statecode = 0
and targ.new_programmemoidname like 'MCC RSL%' and
targ.new_programcontracouttidname = 'No Programme Contract' and
targ.new_monthlyoutputsidname = 'Residents Engaged'
GROUP BY targ.new_programmemoidname,
targ.new_programcontracouttidname,
targ.new_monthlyoutputsidname,
targ.new_monthnumber,
targ.new_enddateutc,
targ.new_numberofoutputs,
l.new_unitrate_base,
targ.new_monthlyprofileoutidname
ORDER BY targ.new_programmemoidname,
targ.new_programcontracouttidname,
targ.new_monthlyoutputsidname,
targ.new_monthnumber
) select cnt-sum_col from count_cte join sum_count on
count_cte.id=count_cte.id
*** Sent via Developersdex http://www.developersdex.com ***
Uri Dimant
2009-06-18 11:22:02 UTC
Permalink
Mike
See if that works
SELECT * FROM

(

SELECT (SELECT COUNT(*) as cnt

FROM FilteredNew_evidence AS act4

JOIN FilteredNew_monthlyoutput AS targ4

ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid

AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid

WHERE targ4.new_programmemoidname = targ.new_programmemoidname

AND targ4.new_programcontracouttidname =

targ.new_programcontracouttidname

AND targ4.new_monthlyoutputsidname =

targ.new_monthlyoutputsidname

AND targ4.new_monthnumber <= targ.new_monthnumber

and act4.statecode = 0 and targ4.statecode = 0)

-

(select sum(targ5.new_numberofoutputs)as sum_col,



from FilteredNew_monthlyoutput targ5

inner join FilteredNew_outputlookup l2

on targ5.new_monthlyoutputsid = l2.new_outputlookupid

WHERE targ5.new_programmemoidname = targ.new_programmemoidname

AND targ5.new_programcontracouttidname =

targ.new_programcontracouttidname

AND targ5.new_monthlyoutputsidname =

targ.new_monthlyoutputsidname

AND targ5.new_monthnumber <= targ.new_monthnumber

and targ5.statecode = 0 and l2.statecode = 0

) AS calc

FROM FilteredNew_monthlyoutput AS targ

inner join dbo.FilteredNew_outputlookup l

on l.new_outputlookupid = targ.new_monthlyoutputsid

LEFT JOIN FilteredNew_evidence AS act

ON targ.new_monthlyprofileoutid = act.new_claimperiodid

AND targ.new_monthlyoutputsid = act.new_evidenceforoutputid



where

targ.statecode = 0 and

l.statecode = 0

and targ.new_programmemoidname like 'MCC RSL%' and

targ.new_programcontracouttidname = 'No Programme Contract' and

targ.new_monthlyoutputsidname = 'Residents Engaged'

GROUP BY targ.new_programmemoidname,

targ.new_programcontracouttidname,

targ.new_monthlyoutputsidname,

targ.new_monthnumber,

targ.new_enddateutc,

targ.new_numberofoutputs,

l.new_unitrate_base,

targ.new_monthlyprofileoutidname

ORDER BY targ.new_programmemoidname,

targ.new_programcontracouttidname,

targ.new_monthlyoutputsidname,

targ.new_monthnumber

) AS Der
Post by Mike P
Uri,
I am making progress with this thanks to your help.
The problem is that the subquery references a table (targ)further down
with count_cte
as
(
SELECT COUNT(*) as cnt,row_number() over (order by
targ4.new_programmemoidname) as id
FROM FilteredNew_evidence AS act4
JOIN FilteredNew_monthlyoutput AS targ4
ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid
AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid
WHERE targ4.new_programmemoidname = targ.new_programmemoidname
AND targ4.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ4.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ4.new_monthnumber <= targ.new_monthnumber
and act4.statecode = 0 and targ4.statecode = 0
),sum_count
as
(
select sum(targ5.new_numberofoutputs)as sum_col,
row_number() over (order by targ5.new_programmemoidname) as id
from FilteredNew_monthlyoutput targ5
inner join FilteredNew_outputlookup l2
on targ5.new_monthlyoutputsid = l2.new_outputlookupid
WHERE targ5.new_programmemoidname = targ.new_programmemoidname
AND targ5.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ5.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ5.new_monthnumber <= targ.new_monthnumber
and targ5.statecode = 0 and l2.statecode = 0
) select cnt-sum_col from count_cte join sum_count on
count_cte.id=count_cte.id
And I need to add the following from the main query of the original SQL
FROM FilteredNew_monthlyoutput AS targ
inner join dbo.FilteredNew_outputlookup l
on l.new_outputlookupid = targ.new_monthlyoutputsid
LEFT JOIN FilteredNew_evidence AS act
ON targ.new_monthlyprofileoutid = act.new_claimperiodid
AND targ.new_monthlyoutputsid = act.new_evidenceforoutputid
where
targ.statecode = 0 and
l.statecode = 0
and targ.new_programmemoidname like 'MCC RSL%' and
targ.new_programcontracouttidname = 'No Programme Contract' and
targ.new_monthlyoutputsidname = 'Residents Engaged'
GROUP BY targ.new_programmemoidname,
targ.new_programcontracouttidname,
targ.new_monthlyoutputsidname,
targ.new_monthnumber,
targ.new_enddateutc,
targ.new_numberofoutputs,
l.new_unitrate_base,
targ.new_monthlyprofileoutidname
ORDER BY targ.new_programmemoidname,
targ.new_programcontracouttidname,
targ.new_monthlyoutputsidname,
targ.new_monthnumber
*** Sent via Developersdex http://www.developersdex.com ***
Uri Dimant
2009-06-18 11:22:02 UTC
Permalink
Mike
See if that works
SELECT * FROM

(

SELECT (SELECT COUNT(*) as cnt

FROM FilteredNew_evidence AS act4

JOIN FilteredNew_monthlyoutput AS targ4

ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid

AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid

WHERE targ4.new_programmemoidname = targ.new_programmemoidname

AND targ4.new_programcontracouttidname =

targ.new_programcontracouttidname

AND targ4.new_monthlyoutputsidname =

targ.new_monthlyoutputsidname

AND targ4.new_monthnumber <= targ.new_monthnumber

and act4.statecode = 0 and targ4.statecode = 0)

-

(select sum(targ5.new_numberofoutputs)as sum_col,



from FilteredNew_monthlyoutput targ5

inner join FilteredNew_outputlookup l2

on targ5.new_monthlyoutputsid = l2.new_outputlookupid

WHERE targ5.new_programmemoidname = targ.new_programmemoidname

AND targ5.new_programcontracouttidname =

targ.new_programcontracouttidname

AND targ5.new_monthlyoutputsidname =

targ.new_monthlyoutputsidname

AND targ5.new_monthnumber <= targ.new_monthnumber

and targ5.statecode = 0 and l2.statecode = 0

) AS calc

FROM FilteredNew_monthlyoutput AS targ

inner join dbo.FilteredNew_outputlookup l

on l.new_outputlookupid = targ.new_monthlyoutputsid

LEFT JOIN FilteredNew_evidence AS act

ON targ.new_monthlyprofileoutid = act.new_claimperiodid

AND targ.new_monthlyoutputsid = act.new_evidenceforoutputid



where

targ.statecode = 0 and

l.statecode = 0

and targ.new_programmemoidname like 'MCC RSL%' and

targ.new_programcontracouttidname = 'No Programme Contract' and

targ.new_monthlyoutputsidname = 'Residents Engaged'

GROUP BY targ.new_programmemoidname,

targ.new_programcontracouttidname,

targ.new_monthlyoutputsidname,

targ.new_monthnumber,

targ.new_enddateutc,

targ.new_numberofoutputs,

l.new_unitrate_base,

targ.new_monthlyprofileoutidname

ORDER BY targ.new_programmemoidname,

targ.new_programcontracouttidname,

targ.new_monthlyoutputsidname,

targ.new_monthnumber

) AS Der
Post by Mike P
Uri,
I am making progress with this thanks to your help.
The problem is that the subquery references a table (targ)further down
with count_cte
as
(
SELECT COUNT(*) as cnt,row_number() over (order by
targ4.new_programmemoidname) as id
FROM FilteredNew_evidence AS act4
JOIN FilteredNew_monthlyoutput AS targ4
ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid
AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid
WHERE targ4.new_programmemoidname = targ.new_programmemoidname
AND targ4.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ4.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ4.new_monthnumber <= targ.new_monthnumber
and act4.statecode = 0 and targ4.statecode = 0
),sum_count
as
(
select sum(targ5.new_numberofoutputs)as sum_col,
row_number() over (order by targ5.new_programmemoidname) as id
from FilteredNew_monthlyoutput targ5
inner join FilteredNew_outputlookup l2
on targ5.new_monthlyoutputsid = l2.new_outputlookupid
WHERE targ5.new_programmemoidname = targ.new_programmemoidname
AND targ5.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ5.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ5.new_monthnumber <= targ.new_monthnumber
and targ5.statecode = 0 and l2.statecode = 0
) select cnt-sum_col from count_cte join sum_count on
count_cte.id=count_cte.id
And I need to add the following from the main query of the original SQL
FROM FilteredNew_monthlyoutput AS targ
inner join dbo.FilteredNew_outputlookup l
on l.new_outputlookupid = targ.new_monthlyoutputsid
LEFT JOIN FilteredNew_evidence AS act
ON targ.new_monthlyprofileoutid = act.new_claimperiodid
AND targ.new_monthlyoutputsid = act.new_evidenceforoutputid
where
targ.statecode = 0 and
l.statecode = 0
and targ.new_programmemoidname like 'MCC RSL%' and
targ.new_programcontracouttidname = 'No Programme Contract' and
targ.new_monthlyoutputsidname = 'Residents Engaged'
GROUP BY targ.new_programmemoidname,
targ.new_programcontracouttidname,
targ.new_monthlyoutputsidname,
targ.new_monthnumber,
targ.new_enddateutc,
targ.new_numberofoutputs,
l.new_unitrate_base,
targ.new_monthlyprofileoutidname
ORDER BY targ.new_programmemoidname,
targ.new_programcontracouttidname,
targ.new_monthlyoutputsidname,
targ.new_monthnumber
*** Sent via Developersdex http://www.developersdex.com ***
Mike P
2009-06-18 11:15:36 UTC
Permalink
This is what I have tried, but it gives syntax errors :

with count_cte

as

(

(SELECT COUNT(*) as cnt,row_number() over (order by
targ4.new_programmemoidname) as id

FROM FilteredNew_evidence AS act4

JOIN FilteredNew_monthlyoutput AS targ4

ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid

AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid

WHERE targ4.new_programmemoidname = targ.new_programmemoidname

AND targ4.new_programcontracouttidname =

targ.new_programcontracouttidname

AND targ4.new_monthlyoutputsidname =

targ.new_monthlyoutputsidname

AND targ4.new_monthnumber <= targ.new_monthnumber

and act4.statecode = 0 and targ4.statecode = 0)

FROM FilteredNew_monthlyoutput AS targ
inner join dbo.FilteredNew_outputlookup l
on l.new_outputlookupid = targ.new_monthlyoutputsid
LEFT JOIN FilteredNew_evidence AS act
ON targ.new_monthlyprofileoutid = act.new_claimperiodid
AND targ.new_monthlyoutputsid = act.new_evidenceforoutputid


where
targ.statecode = 0 and
l.statecode = 0

and targ.new_programmemoidname like 'MCC RSL%' and
targ.new_programcontracouttidname = 'No Programme Contract' and
targ.new_monthlyoutputsidname = 'Residents Engaged'

GROUP BY targ.new_programmemoidname,
targ.new_programcontracouttidname,
targ.new_monthlyoutputsidname,
targ.new_monthnumber,
targ.new_enddateutc,
targ.new_numberofoutputs,
l.new_unitrate_base,
targ.new_monthlyprofileoutidname
ORDER BY targ.new_programmemoidname,
targ.new_programcontracouttidname,
targ.new_monthlyoutputsidname,
targ.new_monthnumber

),sum_count


as

(

(select sum(targ5.new_numberofoutputs)as sum_col,

row_number() over (order by targ5.new_programmemoidname) as id

from FilteredNew_monthlyoutput targ5

inner join FilteredNew_outputlookup l2

on targ5.new_monthlyoutputsid = l2.new_outputlookupid

WHERE targ5.new_programmemoidname = targ.new_programmemoidname

AND targ5.new_programcontracouttidname =

targ.new_programcontracouttidname

AND targ5.new_monthlyoutputsidname =

targ.new_monthlyoutputsidname

AND targ5.new_monthnumber <= targ.new_monthnumber

and targ5.statecode = 0 and l2.statecode = 0)

FROM FilteredNew_monthlyoutput AS targ
inner join dbo.FilteredNew_outputlookup l
on l.new_outputlookupid = targ.new_monthlyoutputsid
LEFT JOIN FilteredNew_evidence AS act
ON targ.new_monthlyprofileoutid = act.new_claimperiodid
AND targ.new_monthlyoutputsid = act.new_evidenceforoutputid


where
targ.statecode = 0 and
l.statecode = 0

and targ.new_programmemoidname like 'MCC RSL%' and
targ.new_programcontracouttidname = 'No Programme Contract' and
targ.new_monthlyoutputsidname = 'Residents Engaged'

GROUP BY targ.new_programmemoidname,
targ.new_programcontracouttidname,
targ.new_monthlyoutputsidname,
targ.new_monthnumber,
targ.new_enddateutc,
targ.new_numberofoutputs,
l.new_unitrate_base,
targ.new_monthlyprofileoutidname
ORDER BY targ.new_programmemoidname,
targ.new_programcontracouttidname,
targ.new_monthlyoutputsidname,
targ.new_monthnumber

) select cnt-sum_col from count_cte join sum_count on
count_cte.id=count_cte.id




*** Sent via Developersdex http://www.developersdex.com ***

Plamen Ratchev
2009-06-18 12:04:43 UTC
Permalink
You can always nest the subquery one more level:

SELECT ...
(SELECT Last_Cumulative_Outputs_Difference
FROM (SELECT ...
FROM ...) AS T(Last_Cumulative_Outputs_Difference)
WHERE ...) AS Last_Cumulative_Outputs_Difference
...
FROM ...
--
Plamen Ratchev
http://www.SQLStudio.com
Mike P
2009-06-18 12:00:34 UTC
Permalink
Uri,

I think the problem lies with the fact that I am not only trying to use
CTE for the first time, but I am also trying to incorporate some pretty
complicated SQL into it. I am going to try to start with something
simpler and come back to this later, but thanks for your help anyway.



*** Sent via Developersdex http://www.developersdex.com ***
Mariano Gomez
2009-06-19 00:02:07 UTC
Permalink
You could also use common table expressions to simplify your query syntax

WITH someCTE (col1,.., coln) AS (
SELECT col1,...,coln FROM fooTable1
), someOtherCTE (col1,...,coln) AS (
SELECT col1,...,coln FROM fooTable2
)
SELECT a.*, b.* FROM someCTE a
LEFT/RIGHT/INNER JOIN someOtherCTE on (a.col1 = b.col2)
WHERE a.colX = someCondition

Best regards,
--
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com
I am struggling with some complex SQL. The part 'Last Cumulative
Outputs Difference' below needs a further where clause wrapped around it
and I am not sure how to do this as it is already a subquery within the
main query. Can anybody help?
[code]SELECT targ.new_programmemoidname AS 'Programme',
targ.new_programcontracouttidname AS 'Programme Contract',
targ.new_monthlyoutputsidname AS 'Output',
targ.new_monthnumber AS 'Month',
targ.new_monthlyprofileoutidname as 'Month Name',
targ.new_enddateutc as 'End Date',
CASE WHEN MAX(CASE WHEN targ.new_enddateutc < CURRENT_TIMESTAMP
THEN targ.new_enddateutc
END) OVER() = targ.new_enddateutc
THEN 'Yes'
ELSE 'No'
END as 'Is Last Occuring',
(select max(targ4.new_monthnumber)
FROM FilteredNew_monthlyoutput AS targ4
inner join dbo.FilteredNew_outputlookup l4
on l4.new_outputlookupid = targ4.new_monthlyoutputsid
WHERE targ4.new_programmemoidname = targ.new_programmemoidname
AND targ4.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ4.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
and targ4.new_enddateutc < CURRENT_TIMESTAMP) as 'Last Occuring',
targ.new_numberofoutputs AS 'Target Outputs',
COUNT(act.new_outputevmonthnumber) AS 'Actual Outputs',
case when targ.new_numberofoutputs -
COUNT(act.new_outputevmonthnumber) < 0 then 0 else
COUNT(act.new_outputevmonthnumber) - targ.new_numberofoutputs end AS '
Outputs Difference',
(select sum(targ3.new_numberofoutputs)
from FilteredNew_monthlyoutput targ3
inner join FilteredNew_outputlookup l
on targ3.new_monthlyoutputsid = l.new_outputlookupid
WHERE targ3.new_programmemoidname = targ.new_programmemoidname
AND targ3.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ3.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ3.new_monthnumber <= targ.new_monthnumber
and targ3.statecode = 0 and l.statecode = 0) AS 'Cumulative Target
Outputs',
(SELECT COUNT(*)
FROM FilteredNew_evidence AS act2
JOIN FilteredNew_monthlyoutput AS targ2
ON targ2.new_monthlyprofileoutid = act2.new_claimperiodid
AND targ2.new_monthlyoutputsid = act2.new_evidenceforoutputid
WHERE targ2.new_programmemoidname = targ.new_programmemoidname
AND targ2.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ2.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ2.new_monthnumber <= targ.new_monthnumber
and act2.statecode = 0 and targ2.statecode = 0) AS 'Cumulative
Actual Outputs',
(SELECT COUNT(*)
FROM FilteredNew_evidence AS act4
JOIN FilteredNew_monthlyoutput AS targ4
ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid
AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid
WHERE targ4.new_programmemoidname = targ.new_programmemoidname
AND targ4.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ4.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ4.new_monthnumber <= targ.new_monthnumber
and act4.statecode = 0 and targ4.statecode = 0) -
(select sum(targ5.new_numberofoutputs)
from FilteredNew_monthlyoutput targ5
inner join FilteredNew_outputlookup l2
on targ5.new_monthlyoutputsid = l2.new_outputlookupid
WHERE targ5.new_programmemoidname = targ.new_programmemoidname
AND targ5.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ5.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ5.new_monthnumber <= targ.new_monthnumber
and targ5.statecode = 0 and l2.statecode = 0) as 'Last
Cumulative Outputs Difference',
(SELECT COUNT(*)
FROM FilteredNew_evidence AS act4
JOIN FilteredNew_monthlyoutput AS targ4
ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid
AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid
WHERE targ4.new_programmemoidname = targ.new_programmemoidname
AND targ4.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ4.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ4.new_monthnumber <= targ.new_monthnumber
and act4.statecode = 0 and targ4.statecode = 0) -
(select sum(targ5.new_numberofoutputs)
from FilteredNew_monthlyoutput targ5
inner join FilteredNew_outputlookup l2
on targ5.new_monthlyoutputsid = l2.new_outputlookupid
WHERE targ5.new_programmemoidname = targ.new_programmemoidname
AND targ5.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ5.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ5.new_monthnumber <= targ.new_monthnumber
and targ5.statecode = 0 and l2.statecode = 0) as 'Cumulative
Outputs Difference',
CASE WHEN targ.new_numberofoutputs = 0 THEN 0 ELSE
round(cast(count(act.new_outputevmonthnumber) as
float)/targ.new_numberofoutputs * 100, 0) END as '% Achieved',
l.new_unitrate_base as 'Unit Rate',
l.new_unitrate_base * targ.new_numberofoutputs as 'Target Income',
l.new_unitrate_base * COUNT(act.new_outputevmonthnumber) AS
'Actual Income',
(l.new_unitrate_base * COUNT(act.new_outputevmonthnumber)) -
(l.new_unitrate_base * targ.new_numberofoutputs) as 'Income Difference',
(SELECT l.new_unitrate_base * COUNT(act4.new_outputevmonthnumber)
FROM FilteredNew_evidence AS act4
JOIN FilteredNew_monthlyoutput AS targ4
ON targ4.new_monthlyprofileoutid = act4.new_claimperiodid
AND targ4.new_monthlyoutputsid = act4.new_evidenceforoutputid
WHERE targ4.new_programmemoidname = targ.new_programmemoidname
AND targ4.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ4.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ4.new_monthnumber <= targ.new_monthnumber
and act4.statecode = 0 and targ4.statecode = 0) -
(select sum(l.new_unitrate_base * targ3.new_numberofoutputs)
from FilteredNew_monthlyoutput targ3
inner join FilteredNew_outputlookup l
on targ3.new_monthlyoutputsid = l.new_outputlookupid
WHERE targ3.new_programmemoidname = targ.new_programmemoidname
AND targ3.new_programcontracouttidname =
targ.new_programcontracouttidname
AND targ3.new_monthlyoutputsidname =
targ.new_monthlyoutputsidname
AND targ3.new_monthnumber <= targ.new_monthnumber
and targ3.statecode = 0 and l.statecode = 0) as 'Cumulative Income
Difference'
FROM FilteredNew_monthlyoutput AS targ
inner join dbo.FilteredNew_outputlookup l
on l.new_outputlookupid = targ.new_monthlyoutputsid
LEFT JOIN FilteredNew_evidence AS act
ON targ.new_monthlyprofileoutid = act.new_claimperiodid
AND targ.new_monthlyoutputsid = act.new_evidenceforoutputid
where
targ.statecode = 0 and
l.statecode = 0
and targ.new_programmemoidname like 'MCC RSL%' and
targ.new_programcontracouttidname = 'No Programme Contract' and
targ.new_monthlyoutputsidname = 'Residents Engaged'
GROUP BY targ.new_programmemoidname,
targ.new_programcontracouttidname,
targ.new_monthlyoutputsidname,
targ.new_monthnumber,
targ.new_enddateutc,
targ.new_numberofoutputs,
l.new_unitrate_base,
targ.new_monthlyprofileoutidname
ORDER BY targ.new_programmemoidname,
targ.new_programcontracouttidname,
targ.new_monthlyoutputsidname,
targ.new_monthnumber[/code]
*** Sent via Developersdex http://www.developersdex.com ***
Continue reading on narkive:
Loading...