Something like this should list out any of the products that fail your audit.

WITH ValidationTables (n, )

SELECT 1, [Key] FROM A

UNION ALL...

Steven - Your posting gave me the idea for this (your Tally FUNCTION):

CREATE FUNCTION [dbo].[itvfTally]

(

@pMin BIGINT

,@pMax BIGINT

...

Although the syntax is not the same, you could DECLARE a TYPE that is a TABLE and pass that into the FUNCTION.

You then just assign each value to a row...

Here are some additional links:

Understanding and Using APPLY (Part 1)[/url] by Paul White

Understanding and Using APPLY (Part 2)[/url] by Paul White

The Cascading (CROSS) APPLY[/url] by Chris Morris

And don't miss the...

Here's another way. Just uncomment your code and comment out the sample data.

WITH CTE (NMDOS, ETOTALDEB, tpdesc, dataobra, NOME, OBRANO, nmdoc, FNO, etiliquido)

AS (

--SELECT distinct BO.NMDOS,bo.ETOTALDEB, bo.tpdesc ,bo.dataobra,BO.NOME ,BO.OBRANO,ft.nmdoc,FT.FNO,CASE...

Here's one way. Not sure it's the best way and it doesn't use OPENXML though:

DECLARE @Books XML = '

<bookstore>

<book>

...

Jeff Moden (7/24/2013)

---

savycara (7/24/2013)

---

DECLARE @d varchar(6) = '20114'

SELECT try_convert(date, substring(@d, 1, 4) +

CASE WHEN len(@d) = 5 THEN '0' ELSE '' END +

substring(@d, 5, 2) + '01')

This worked but...

Why use a CASE at all?

WITH Rates (rate) AS (

SELECT -100 UNION ALL SELECT NULL UNION ALL SELECT 0

UNION ALL SELECT...

Ignoring the try_convert (which is probably a good idea), I think this may be another way:

SELECT CONVERT(VARCHAR(10), CAST(STUFF('201311',5,0,'-0')+'-01' AS DATETIME), 101)

,CONVERT(VARCHAR(10), CAST(STUFF('20131',5,0,'-0')+'-01' AS DATETIME), 101);

HanShi (7/24/2013)

---

Because additional digits are required to get a valid date notation, the conversion fails. You need to add additional prefixing to get a valid numeric value of at least...

ColdCoffee (7/24/2013)

---

Glad that it worked. I hope you understand the power of providing ready-to-use sample data. It makes the life of forum members a lot easier to work on the...

SELECT a.Col1, a.Col2, a.Col3, a.Col4

FROM #TableA a

LEFT JOIN #TableB b

ON a.Col1 = b.Col1 AND a.Col2 = b.Col2 AND

...

Just for a little variety, I think this is another way:

SELECT a.ID, Cat -- , [Date], [Type]

,[FirstDate]=MIN([Date])

,[FirstType]=MAX(CASE WHEN a=[Date] THEN [Type] END)

...

I guess that would depend on exactly what date you think 20114 represents.

SELECT '20114', CAST('20114'+0 AS DATETIME)

I'd say that if you want this

SELECT DATEADD(week,DATEDIFF(week,7,GETDATE()),0)

To always return the Mon of the prior week, it works pretty well.

SET DATEFIRST 1

SELECT DATEADD(week,DATEDIFF(week,7,GETDATE()),0)

SET DATEFIRST 7

SELECT DATEADD(week,DATEDIFF(week,7,GETDATE()),0)

SELECT DATEADD(week,DATEDIFF(week,7,'2014-01-15'),0)

SELECT DATEADD(week,DATEDIFF(week,7,'2012-03-15'),0)