Thank you. Excellent article. Everything for start with CLR in one place.

Have a nice day, Matjaz

I have the same problem.

declare @x xml

set @x =

'<Document>

<Data>

<DataFormat>

<MimeType>text/xml</MimeType>

</DataFormat>

<Content>

<EmbeddedData>

<Paket xmlns="http://www.zbs-giz.si/Schemas/2006/ZBSxml/2.2">

<VodilniZapis>

<StevilkaRacunaNalogodajalca>SI56101000000055382</StevilkaRacunaNalogodajalca>

<StevilkaPaketa>000000</StevilkaPaketa>

<DatumPaketa>2011-12-09</DatumPaketa>

<NacinObdelave>SNGL</NacinObdelave>

</VodilniZapis>

<Izpiski>

<Izpisek>

<GlavaIzpiska>

<StevilkaIzpiska>00265</StevilkaIzpiska>

<KodaSWIFT>BAKOSI2XXXX</KodaSWIFT>

<StevilkaRacunaNalogodajalca>SI56101000000055382</StevilkaRacunaNalogodajalca>

<OznakaValute>EUR</OznakaValute>

<ZnesekZacetnoStanje>442111.99</ZnesekZacetnoStanje>

<ZnesekKoncnoStanje>207193.74</ZnesekKoncnoStanje>

<SkupajVBreme>

<Znesek>273214.78</Znesek>

<SteviloTransakcij>000083</SteviloTransakcij>

</SkupajVBreme>

<SkupajVDobro>

<Znesek>38296.53</Znesek>

<SteviloTransakcij>000559</SteviloTransakcij>

</SkupajVDobro>

<DatumZacetkaObdobja>2011-12-09</DatumZacetkaObdobja>

<DatumKoncaObdobja>2011-12-09</DatumKoncaObdobja>

<DatumPredhodnegaIzpiska>2011-12-08</DatumPredhodnegaIzpiska>

</GlavaIzpiska>

<Promet>

<PostavkaPrometa>

<ReferencnaOznakaNalogaNalogodajalca>SI99</ReferencnaOznakaNalogaNalogodajalca>

<ReferencnaOznakaNalogaBanke>1111241111825152</ReferencnaOznakaNalogaBanke>

<PodatkiONalogodajalcu>

<Nalogodajalec>

<Naziv>ROJC IGOR S.P.</Naziv>

<Naslov>KUBED 66A</Naslov>

<Naslov1></Naslov1>

<Drzava>SI</Drzava>

<StevilkaRacuna>SI56101000045915721</StevilkaRacuna>

</Nalogodajalec>

</PodatkiONalogodajalcu>

<PodatkiONakazilu>

<StrukturiranaReferenca>

<Referenca>SI125000113919647</Referenca>

<DodatenOpisNamena>SUPP-Pla?ilo ra?una št. 11391965</DodatenOpisNamena>

</StrukturiranaReferenca>

</PodatkiONakazilu>

<ZnesekPlacila>

<Znesek>57.1</Znesek>

<OznakaValute>EUR</OznakaValute>

</ZnesekPlacila>

<IndikatorKnjizbe>2</IndikatorKnjizbe>

<KodaEksterna>SUPP</KodaEksterna>

<DatumValute>2011-12-09</DatumValute>

<DatumKnjizenja>2011-12-09</DatumKnjizenja>

</PostavkaPrometa>

</Promet>

</Izpisek>

</Izpiski>

</Paket>

</EmbeddedData>

</Content>

</Data>

</Document>'

-- select @x

selectx.Paket.value('StevilkaRacunaNalogodajalca[1]', 'varchar(34)') AS StevilkaRacunaNalogodajalca

,x.Paket.value('DatumPaketa[1]', 'datetime') AS DatumPaketa

,x.Paket.value('StevilkaPaketa[1]', 'smallint') AS StevilkaPaketa

from @x.nodes('//Document/Data/Content/EmbeddedData/Paket/VodilniZapis')...

This should be a simpler solution.

select N2.n as dan, N.n - 1 as Ura, isnull(sum(SaleQTY), 0) as D

from dbo.GetNums(24) as N cross join dbo.GetNums(31) as N2

left join (select datepart(day,...

I would like to take advantage of a chance to join to the discussion with a question which is not directly related to the title but I cannot miss all...

Perhaps is more easily as follows

INSERT dbo.TinyTally default values

GO 254

instead of

INSERT dbo.TinyTally(N) VALUES(IDENT_CURRENT('dbo.TinyTally'));

GO

INSERT dbo.TinyTally(N) VALUES(IDENT_CURRENT('dbo.TinyTally')+1);

GO 254 -- Really cool tidbit I picked up from SQL Saturday #40 (will now...

I'm using extracted data for preview.

I managed with this:

;WITH XMLNAMESPACES('http://www.crea.si/Schemas/2004/Document/ZBSxml/2.0' as cd, DEFAULT 'http://www.zbs-giz.si/Schemas/2006/ZBSxml/2.0')

selectx.Paket.value('StevilkaRacunaNalogodajalca[1]', 'varchar(34)') AS StevilkaRacunaNalogodajalca

,x.Paket.value('DatumPaketa[1]', 'datetime') AS DatumPaketa

,x.Paket.value('StevilkaPaketa[1]', 'smallint') AS StevilkaPaketa

--select x.Paket.query('.')

from @p_XML.nodes('//cd:Document/cd:Data/cd:Content/cd:EmbeddedData/Paket/VodilniZapis') AS x(Paket)

Silly me. I looked at "Last login" date. :blush: Eh, what can I say. Sorry.

I prefer a solution below.

declare @proc_name sysname

if (someCondition)

set @proc_name = 'Subproc1'

if (someotherCondition)

set @proc_name = 'Subproc2'

...

exec @proc_name

I have the same problem. Did you find a solution?

Excellent article.

It's likeThe Einstein Theory of relativity. In a place with big numbers rows behave different, Newton's theory does not correspond exactly.

Thank you. Another CTEs makes query 3 times faster.

Use case statement and sum function.

Example:

...

sum(case addl when 'P' then 1 else 0 end) AS TotalPosition,

sum(case addl when 'E' then 1 else 0 end) AS TotalEmployees,

sum(case addl when...

Kind of.

But also includes split amount to cover debit side with credit side.

I think that runningsum is part of solution.

The goal is to split and cover amount on debit side with amounts on credit side. From my example:

1000 € ( 1.1.2009) = 500€ ( 15.2.2009 ) + 500 €...