Difference between two queries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Difference between two queries

Author

Topic

dosteroid
Starting Member

29 Posts

Posted - 2012-03-09 : 12:02:16

Hi

Can anyone tell me why the following two queries don't return the same result?
The result in the first query is the correct one, but since I'm doing a lot of calculations on the first query afterwards and not only for december 2011, I need to remove the date filter from the query...

1.
with a as
(
select SDKCOO OrderCompany,dbo.JDEJulian(F4211.SDIVD) InvoiceDate
,case when row_number() over (partition by SDDOCO,SDKCOO order by SDDOCO,SDKCOO) = 1 then 1 else 0 end CountOrders
from F4211
where MONTH(dbo.JDEJulian(F4211.SDIVD)) = '12' and YEAR(dbo.JDEJulian(F4211.SDIVD)) = '2011'
)
select SUM(CountOrders) DOC_COUNT
from a

RESULT: DOC_COUNT = 341

2.
with a as
(
select SDKCOO OrderCompany,dbo.JDEJulian(F4211.SDIVD) InvoiceDate
,case when row_number() over (partition by SDDOCO,SDKCOO order by SDDOCO,SDKCOO) = 1 then 1 else 0 end CountOrders
from F4211
)
select SUM(CountOrders) DOC_COUNT
from a
where month(InvoiceDate) = '12' and year(InvoiceDate) = '2011'

RESULT: DOC_COUNT = 297

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-09 : 12:17:03

thats because the first case it applies rowNumber only over subset whereas second case it applies rownumber over entire set and hence number of records with 1 will vary regardless of year value.so i dont think its equivalent to first query.
between what does below return?


with a as
(
select SDKCOO OrderCompany,dbo.JDEJulian(F4211.SDIVD) InvoiceDate
,case when row_number() over (partition by SDDOCO,SDKCOO order by SDDOCO,SDKCOO) = 1 and month(dbo.JDEJulian(F4211.SDIVD)) = '12' and year(dbo.JDEJulian(F4211.SDIVD)) = '2011'
then 1 else 0 end CountOrders
from F4211
)
select SUM(CountOrders) DOC_COUNT
from a
where month(InvoiceDate) = '12' and year(InvoiceDate) = '2011'

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

dosteroid
Starting Member

29 Posts

Posted - 2012-03-09 : 12:20:33

Returns 297 aswell :(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-09 : 12:48:36

i think i understood issue row_number() over (partition by SDDOCO,SDKCOO order by SDDOCO,SDKCOO) will simply group data based on SDDOCO,SDKCOO values and number them 1,2,3 etc without considering the month(InvoiceDate) and year(InvoiceDate) values. in first case you first do filter and then apply grouping over subset which gives you 341. but in second case you apply grouping first so there's no guarantee that records within group with month(InvoiceDate) and year(InvoiceDate) value will get 1 thats why you're missing some groups.

i think this should fix it up


with a as
(
select SDKCOO OrderCompany,dbo.JDEJulian(F4211.SDIVD) InvoiceDate
,case when row_number() over (partition by SDDOCO,SDKCOO order by CASE WHEN month(InvoiceDate) = '12' and year(InvoiceDate) = '2011' then 1 else 2 end) = 1 then 1 else 0 end CountOrders
from F4211
)
select SUM(CountOrders) DOC_COUNT
from a
where month(InvoiceDate) = '12' and year(InvoiceDate) = '2011'

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

dosteroid
Starting Member

29 Posts

Posted - 2012-03-09 : 13:17:28

It was indeed correct...

But that doesn't tell me how I can get:

with a as
(
select SDKCOO OrderCompany,dbo.JDEJulian(F4211.SDIVD) InvoiceDate
,case when row_number() over (partition by SDDOCO,SDKCOO order by SDDOCO,SDKCOO) = 1 then 1 else 0 end CountOrders
from F4211
)
select SUM(CountOrders) DOC_COUNT
from a
where month(InvoiceDate) = '12' and year(InvoiceDate) = '2011'

to give the same result as:

with a as
(
select SDKCOO OrderCompany,dbo.JDEJulian(F4211.SDIVD) InvoiceDate
,case when row_number() over (partition by SDDOCO,SDKCOO order by SDDOCO,SDKCOO) = 1 then 1 else 0 end CountOrders
from F4211
where MONTH(dbo.JDEJulian(F4211.SDIVD)) = '12' and YEAR(dbo.JDEJulian(F4211.SDIVD)) = '2011'
)
select SUM(CountOrders) DOC_COUNT
from a

How do I modify the first query so that the result is DOC_COUNT = 341 (as the result is in the second query)

dosteroid
Starting Member

29 Posts

Posted - 2012-03-09 : 15:23:26

Anyone?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-09 : 21:45:17

quote:
Originally posted by dosteroid

It was indeed correct...

But that doesn't tell me how I can get:

with a as
(
select SDKCOO OrderCompany,dbo.JDEJulian(F4211.SDIVD) InvoiceDate
,case when row_number() over (partition by SDDOCO,SDKCOO order by SDDOCO,SDKCOO) = 1 then 1 else 0 end CountOrders
from F4211
)
select SUM(CountOrders) DOC_COUNT
from a
where month(InvoiceDate) = '12' and year(InvoiceDate) = '2011'

to give the same result as:

with a as
(
select SDKCOO OrderCompany,dbo.JDEJulian(F4211.SDIVD) InvoiceDate
,case when row_number() over (partition by SDDOCO,SDKCOO order by SDDOCO,SDKCOO) = 1 then 1 else 0 end CountOrders
from F4211
where MONTH(dbo.JDEJulian(F4211.SDIVD)) = '12' and YEAR(dbo.JDEJulian(F4211.SDIVD)) = '2011'
)
select SUM(CountOrders) DOC_COUNT
from a

How do I modify the first query so that the result is DOC_COUNT = 341 (as the result is in the second query)

you need to modify like my last suggestion. As shown before your above two queries are not equivalent but mine is

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

dosteroid
Starting Member

29 Posts

Posted - 2012-03-12 : 04:31:55

Thanks for the reply :) I must admit I still don't get the results I get... :/

The entire fact_table looks like this and this is the table Im doing alot of calculations on where one of them is DOC_Count:
select SDKCOO OrderCompany,(select CCNAME from F0010 where SDKCOO = CCCO) OrderCompanyDsc,SDCO Company,CCNAME CompanyDsc,SDMCU BranchPlant
,dbo.JDEJulian(SDDRQJ) RequestDate,dbo.JDEJulian(SDTRDJ) OrderDate,dbo.JDEJulian(SDOPDJ) Org_Prom_DeliveryDate,dbo.JDEJulian(SDRSDJ) Promised_DeliveryDate,dbo.JDEJulian(SDPPDJ) Promised_ShipDate
,dbo.JDEJulian(SDPDDJ) Schedule_PickDate,dbo.JDEJulian(F4211.SDIVD) InvoiceDate,SDDCTO OrderType,SDDOCO [OrderNo],SDAN8 BillTo_Cust,a.ABALPH BillTo_CustName,SHHOLD HoldCode
,SDUORG/100000 Ship_Quantity,SDUOM Ship_UoM
,case when UMCONV is null then SDUORG/100000 else SDUORG/100000 * UMCONV/10000000
end PlanQty
,SDUOM4 PlanUoM,SDLITM ItemNo,SDDSC1 ItemDsc1,SDDSC2 ItemDsc2,IBSRP7 MF,(SELECT DRDL01 FROM F0005 WHERE DRKY=IBSRP7 and DRSY='41' AND DRRT='07') MFDsc,SDCRCD SAlesCurrency
,case when SDFEA = 0 then SDAEXP/100 else SDFEA/100
end Sales_Amount
,case when SDCRCD = 'USD' then case when SDFEA = 0 then SDAEXP/100 else SDFEA/100 end else case when SDFEA = 0 then
case when (select CXCRR from F0015 where CXCRCD = CCCRCD and CXCRDC = 'USX' and Month(dbo.JDEJulian(F0015.CXEFT)) = MONTH(dbo.JDEJulian(F4211.SDIVD))
and YEAR(dbo.JDEJulian(F0015.CXEFT)) = YEAR(dbo.JDEJulian(F4211.SDIVD))) IS null then (SDAEXP/100*(select top 1 CXCRR from F0015 where CXCRCD = CCCRCD and CXCRDC = 'USX'
and YEAR(dbo.JDEJulian(F0015.CXEFT)) = YEAR(dbo.JDEJulian(F4211.SDIVD)) order by CXEFT DESC)) else SDAEXP/100*(select CXCRR from F0015 where CXCRCD = CCCRCD and CXCRDC = 'USX'
and Month(dbo.JDEJulian(F0015.CXEFT)) = MONTH(dbo.JDEJulian(F4211.SDIVD)) and YEAR(dbo.JDEJulian(F0015.CXEFT)) = YEAR(dbo.JDEJulian(F4211.SDIVD)))
END else
case when (select CXCRR from F0015 where CXCRCD = CCCRCD and CXCRDC = 'USX' and Month(dbo.JDEJulian(F0015.CXEFT)) = MONTH(dbo.JDEJulian(F4211.SDIVD))
and YEAR(dbo.JDEJulian(F0015.CXEFT)) = YEAR(dbo.JDEJulian(F4211.SDIVD))) IS null then (SDAEXP/100*(select top 1 CXCRR from F0015 where CXCRCD = CCCRCD and CXCRDC = 'USX'
and YEAR(dbo.JDEJulian(F0015.CXEFT)) = YEAR(dbo.JDEJulian(F4211.SDIVD)) order by CXEFT DESC)) else SDAEXP/100*(select CXCRR from F0015 where CXCRCD = CCCRCD and CXCRDC = 'USX'
and Month(dbo.JDEJulian(F0015.CXEFT)) = MONTH(dbo.JDEJulian(F4211.SDIVD)) and YEAR(dbo.JDEJulian(F0015.CXEFT)) = YEAR(dbo.JDEJulian(F4211.SDIVD)))
end end
end Sales_AmountUSD
,SDNXTR NextStatus,SDLTTR LastStatus,isnull(F03012.AICMC1,0) SalesRep,isnull((select WWMLNM from f0111 where F03012.aicmc1 = F0111.WWAN8 and F0111.WWIDLN='0'),'N/A') as SalesRepName
,isnull(AIAC03,0) AreaCode,isnull((SELECT DRDL01 FROM F0005 WHERE DRKY=AIAC03 and DRSY='01' AND DRRT='03'),'N/A') SalesArea,DATEDIFF(day,dbo.JDEJulian(SDPPDJ),dbo.JDEJulian(SDDRQJ)) [Diff Ship - Req]
,DATEDIFF(day,dbo.JDEJulian(SDPPDJ),dbo.JDEJulian(SDRSDJ)) [Diff Delivery - Ship],DATEDIFF(day,dbo.JDEJulian(SDPDDJ),dbo.JDEJulian(SDPPDJ)) [Diff Ship - Pick]
,case when YEAR(dbo.JDEJulian(F4211.SDIVD)) <> '1899' then DATEDIFF(day,dbo.JDEJulian(SDIVD),dbo.JDEJulian(SDDRQJ)) else 0 end [Diff Inv - Req]
,case when YEAR(dbo.JDEJulian(F4211.SDIVD)) <> '1899' then DATEDIFF(day,dbo.JDEJulian(SDIVD),dbo.JDEJulian(SDRSDJ)) else 0 end [Diff Inv - Del]
,DATEDIFF(day,dbo.JDEJulian(SDRSDJ),dbo.JDEJulian(SDDRQJ)) [Diff Del - Req],isnull(T3KY,0) Reason_Code,isnull((select DRKY from F0005 where DRKY=T3KY and DRSY='55' and DRRT='PD'),'N/A') ReasonCode
,isnull((select DRDL01 from F0005 where DRKY=T3KY and DRSY='55' and DRRT='PD'),'N/A') ReasonCodeDsc,isnull(T3RMK,'N/A') Remark1,isnull(T3RMK2,'N/A') Remark2
,IMSRP7 IM_MF,(select DRDL01 from F0005 where DRKY=IMSRP7 and DRSY='41' and DRRT='07') IM_DF_Dsc
,case when row_number() over (partition by SDDOCO,SDKCOO order by SDDOCO,SDKCOO) = 1 then 1 else 0 end CountOrders
from F4211
left join F0010 on F4211.SDCO = F0010.CCCO
left join F0101 a on F4211.SDAN8 = a.ABAN8
left join F0101 b on F4211.SDSHAN = b.ABAN8
left join F4201 on F4211.SDKCOO = F4201.SHKCOO and F4211.SDDOCO = F4201.SHDOCO and F4211.SDDCTO = F4201.SHDCTO
left join F41002 on F4211.SDITM = F41002.UMITM and F4211.SDUOM1 = F41002.UMUM and F4211.SDUOM4 = F41002.UMRUM
left join F4102 on F4211.SDLITM = F4102.IBLITM and F4211.SDMCU = ltrim(rtrim(F4102.IBMCU))
left join F03012 on F4211.SDCO = F03012.AICO and F4211.SDPA8 = F03012.AIAN8
left join F00092 on F4211.SDKCOO = F00092.T3KCO and F4211.SDDOCO = F00092.T3SBN1 and F4211.SDDCTO = F00092.T3SDB and F4211.SDLNID = F00092.T3SBN2 and F00092.T3TYDT = 'PD'
left join F4101 on F4211.SDITM = F4101.IMITM
where SDDCTO in ('SO','SM','SI','SK','SD') and SDCO in ('10430','10440','10710','10740','14440','10240') and SDLNTY <> 'N'
and SDDRQJ >= '109001' and (SDIVD = '0' or SDIVD >= '110001')

Now say I wan't Doc_count for dec 2011 I would do as shown earlier:

with a as
(
Fact_Table Statement
)
select SUM(CountOrders) DOC_COUNT
from a
where month(InvoiceDate) = '12' and year(InvoiceDate) = '2011'

This would give me the result: DOC_COUNT = 242

Now if I then take the fact_table and modify the where clause to contain:
where SDDCTO in ('SO','SM','SI','SK','SD') and SDCO in ('10430','10440','10710','10740','14440','10240') and SDLNTY <> 'N'
and SDDRQJ >= '109001' and (SDIVD = '0' or SDIVD >= '110001') and month(dbo.JDEJulian(F4211.SDIVD)) = '12' and year(dbo.JDEJulian(F4211.SDIVD)) = '2011'

and then copies the column CountOrders into an excel sheet and do a =SUM on the column Im getting: DOC_COUNT = 265

why is that and what do I need to modify in order to get the same results... what could be the reason...?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-12 : 10:45:05

sorry very difficult for me to analyse the query without any data. As suggested before see the results and understand how you can apply the row_number based on correct condition using case when expression inside

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

dosteroid
Starting Member

29 Posts

Posted - 2012-03-12 : 11:31:42

I know... :)

I analysed the query myself and found that the reason for the differences were because I hadn't defined the row_number() probably just like you said... So I changed it to use rank () instead like below:

Where I rank the lines on SDKCOO, SDDOCO,SDIVD_YM (which is Invoicedate converted to yearMonth) and order by a SDLNID which is lineID (didn't knew I had that before)

,case when rank() over (partition by SDKCOO,SDDOCO,SDIVD_YM order by SDLNID) = 1 then 1 else 0 end CountOrders

So it all works now thanks :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-12 : 11:34:53

ok...great that you sorted it out

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources