Help with limiting data 2

[dscoiho]

I have the following query I am working on.

SELECT
FM.C_NAME,
(CASE WHEN O.C_ADR_1 IS NULL THEN 'No Address on File.' ELSE O.C_ADR_1 END) AS 'Office Address',
O.C_CITY AS CITY,O.C_STATE AS STATE,
C.CONTACT_ID,TH.FUND,TH.TRADE_CLASS,
ISNULL(TH.GROSS_AMOUNT,0)AS 'GROSS AMOUNT',
O.OFFICE_ID

FROM dbo.CONTACT C (nolock)
LEFT OUTER JOIN dbFFICE O (nolock)
ON C.OFFICE_ID = O.OFFICE_ID
LEFT OUTER JOIN dbo.FIRM FM (nolock)
ON C.FIRM_ID = FM.FIRM_ID
LEFT OUTER JOIN dbo.TRANSACTION_HISTORY TH (nolock)
ON C.CONTACT_ID = TH.CONTACT_ID
LEFT OUTER JOIN dbo.FUNDS F
ON TH.FUND=F.FUND
LEFT OUTER JOIN dbo.REP_PROFILE RP (nolock)
ON C.CONTACT_ID = RP.CONTACT_ID

WHERE RP.TERR1 IN ('I01')
AND (C.C_PRIMEFLG= 'N'
OR C.C_PRIMEFLG IS NULL)

I am trying to get all individual information and only Sales for a trailing 12 month period.
ex...
Salesman A address etc $1,000 <-- within the last 12 months
Salesman B address etc $0 <-- within the last 12 months (may have had some prior)

Any help as always would be appreciated.

 

[gmmastros]

You are niot giving enough information for us to help you.

1. How do you know what sales occured within the last 12 months?
2. What is your definition for the last 12 months?

12 months could mean 1 year, and that you want to see any sales that occurred within the last year. Ex: Mar 12, 2006 to Mar 12, 2007.

12 months could mean 'prior 11 calendar months and current month'. Ex: April 2006 to current.

12 months could mean 'prior 12 calendar months and NOT anything from current month'. Ex: March 1, 2006 to Feb 28, 2007.

Without knowing the answer to these questions, you may not get a satisfactory answer.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[dscoiho]

I appreciate your reply to my question. The date range would be March 2006 to February 2007. Trade_class = s would be my sales. Please let me know if you need further explanation.

 

[gmmastros]

Should I assume that TRANSACTION_HISTORY has a DateTime column that indicates when the sale occurred?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Try this...

[COLOR=blue]SELECT[/color] 
FM.C_NAME,
([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] O.C_ADR_1 [COLOR=blue]IS[/color] NULL [COLOR=blue]THEN[/color] [COLOR=red]'No Address on File.'[/color] [COLOR=blue]ELSE[/color] O.C_ADR_1 [COLOR=blue]END[/color]) [COLOR=blue]AS[/color] [COLOR=red]'Office Address'[/color],
O.C_CITY [COLOR=blue]AS[/color] CITY,O.C_STATE [COLOR=blue]AS[/color] STATE,
C.CONTACT_ID,TH.FUND,TH.TRADE_CLASS,
[COLOR=#FF00FF]ISNULL[/color](TH.GROSS_AMOUNT,0)[COLOR=blue]AS[/color] [COLOR=red]'GROSS AMOUNT'[/color],
O.OFFICE_ID

[COLOR=blue]FROM[/color] dbo.CONTACT C (nolock)
[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]OUTER[/color] [COLOR=blue]JOIN[/color] dbo.OFFICE O (nolock)
[COLOR=blue]ON[/color] C.OFFICE_ID = O.OFFICE_ID
[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]OUTER[/color] [COLOR=blue]JOIN[/color] dbo.FIRM FM (nolock)
[COLOR=blue]ON[/color] C.FIRM_ID = FM.FIRM_ID
[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]OUTER[/color] [COLOR=blue]JOIN[/color] dbo.TRANSACTION_HISTORY TH (nolock)
[COLOR=blue]ON[/color] C.CONTACT_ID = TH.CONTACT_ID
[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]OUTER[/color] [COLOR=blue]JOIN[/color] dbo.FUNDS F
[COLOR=blue]ON[/color] TH.FUND=F.FUND
[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]OUTER[/color] [COLOR=blue]JOIN[/color] dbo.REP_PROFILE RP (nolock)
[COLOR=blue]ON[/color] C.CONTACT_ID = RP.CONTACT_ID

[COLOR=blue]WHERE[/color] RP.TERR1 IN ([COLOR=red]'I01'[/color])
[highlight]And TH.Trade_Class = [COLOR=red]'s'[/color]
And TH.DateColumn >= [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Month[/color], [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Month[/color], 0, [COLOR=#FF00FF]GetDate[/color]()) -12, 0)
And TH.DateColumn < [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Month[/color], [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Month[/color], 0, [COLOR=#FF00FF]GetDate[/color]()), 0)[/highlight]
AND (C.C_PRIMEFLG= [COLOR=red]'N'[/color]
OR C.C_PRIMEFLG [COLOR=blue]IS[/color] NULL)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[dscoiho]

gmmastros thanks for the quick response.
Your solution is the problem I am having with this query. The "where statement" is limiting the number of record selection over all. Say I have 2000 Salespeople I need to show all 2000 salespeople and any sales they do or do not have for the 12 months.

I can sum of the sales for on the front end to get results ok, just trying to limit amount of overall data that the query is pulling.

 

[SQLDenis]

Change this

WHERE RP.TERR1 IN ('I01')
And TH.Trade_Class = 's'
And TH.DateColumn >= DateAdd(Month, DateDiff(Month, 0, GetDate()) -12, 0)
And TH.DateColumn < DateAdd(Month, DateDiff(Month, 0, GetDate()), 0)
AND (C.C_PRIMEFLG= 'N'
OR C.C_PRIMEFLG IS NULL)

to

AND RP.TERR1 IN ([COLOR=red]'I01'[/color])
And TH.Trade_Class = [COLOR=red]'s'[/color]
And TH.DateColumn >= [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Month[/color], [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Month[/color], 0, [COLOR=#FF00FF]GetDate[/color]()) -12, 0)
And TH.DateColumn < [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Month[/color], [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Month[/color], 0, [COLOR=#FF00FF]GetDate[/color]()), 0)
[COLOR=blue]WHERE[/color] (C.C_PRIMEFLG= [COLOR=red]'N'[/color]
OR C.C_PRIMEFLG [COLOR=blue]IS[/color] NULL)

because the first code is basically an inner join, you have to put the WHERE clause only on the Contact table

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions