ChrisM@Work (9/14/2012)

---

SELECT i.*

FROM tbItems i

CROSS APPLY (

SELECT [active inventory] = CASE

WHEN Solddate >= CONVERT(DATETIME,'01/11/2011',103)

AND Solddate < CONVERT(DATETIME,'01/12/2011',103) THEN 1

WHEN Purchasedate >= CONVERT(DATETIME,'01/11/2011',103)

AND Purchasedate < CONVERT(DATETIME,'01/12/2011',103)

AND Solddate...

1-All sold items on Nov 2011 means were active

2-All items Purchasedate on Nov 2011 and solddate is null were active

3-All items Purchasedate on Nov 2011 and solddate sold after Nov...

Difficult to help without test data and a more detailed explanation of what you are trying to achieve but do you mean something like this?

CREATE TABLE #TEMP

(

[ID] [int] NOT NULL,

[itemno]...

Hi

If you only have a week end date and no other joins you can make to other tables to identify when the sales were made this doesn't sound possible I'm...

Your Welcome

Hi

You could try this:

WITH

CTE

AS

(

SELECT format(WEEK_DATE, "YYYYMM") as f1, Count(TBL_AML_ERRORS.Q6_BRANCH) AS CountOfQ6_BRANCH

FROM TBL_AML_ERRORS

GROUP BY format(WEEK_DATE, "YYYYMM")

UNION

SELECT format(WEEK_DATE, "YYYYMM") as f1, Count(TBL_AML_ERRORS_ARCHIVE.Q6_BRANCH) AS CountOfQ6_BRANCH

FROM TBL_AML_ERRORS_ARCHIVE

GROUP BY format(WEEK_DATE, "YYYYMM")

)

SELECT

f1

,SUM(CountOfQ6_BRANCH)

FROM

CTE

GROUP BY

f1

Andy

davdam8 (9/11/2012)

---

Here's the result I get:

CAT NAME RESOURCES GROUPS

My Resource ...

Without any data or DDL's can't really help as don't know your structure. You could try UNIONing these in a sub query but that's just a shot in the dark..

Without knowing the structure of all your tables and data its difficult to help, this may point you in the right direction (untested as no consumable data):

SELECT

Qry.platenr

, 'invoicefm'...

No Worries

The code I posted above for you will run for all business units as it is, there is no restriction

Thanks 🙂

that's a great example, thanks, i wanted to add that the the select statement is for just one business_unit and one parent_item_no, there are multiple business_units and parent_item_no's, so...

I'll take a shot in the dark, from what I can see in the data you have posted there are no levels, 235021 is the only parent so all levels...

Hi

Take a look at this link:

http://www.sqlservercentral.com/articles/Tally+Table/72993/

Andy

Hi Jeff

Good catch I think that you may be correct on that thinking about it (although I haven't tested either)

I suppose this would be a work around for that:

IF @DisplayYesterday...