Max Query: columns

Showing posts with label columns. Show all posts

Wednesday, March 28, 2012

Help with SQL

I have a table named "policy_details" having four columns called policy_details(varchar),effectdate(datetime),Historyid(int) and policy_status(varchar).

I want to keep only the maximum historyid records in "policy_details" where policy_details and effectdate should be equal and policy_status should be '30240084' . I want to delete the remaining records where policy_details and effectdate should be equal and policy_status should be '30240084'.

Cheers

Praveen

So if you order by policy_details ASC, affectdate ASC, historyid DESC then you only want to keep the first row from each (policy_details, affectdate) group.

I would use the ROW_NUMBER() function and delete where it's not 1

DELETE
FROM policy_details
WHERE ROW_NUMBER() OVER ( PARTITION BY policy_details, affectdate
ORDER BY historyid DESC
) > 1
AND policy_status = '30240084'

|||

Adam,

Thanks for your query.

I am getting an error "The ranking function "ROW_NUMBER" must have an ORDER BY clause."

One more thing is i want same effectdate with only date but not time part.

DELETE
FROM policy_details
WHERE ROW_NUMBER() OVER ( PARTITION BY policy_details,CONVERT(CHAR(10),effectdate,103)
ORDER BY historyid DESC
) > 1
AND policy_status = '30240084'

I did not understand "The ranking function "ROW_NUMBER" must have an ORDER BY clause" error though it is having order by clause. Any ideas?

Cheers

Praveen

|||

Use A CTE, it will work:

WITH myCTE

(SELECT ROW_NUMBER() OVER ( PARTITION BY policy_details,CONVERT(CHAR(10),effectdate,103) ORDER BY historyid DESC) as num

FROM policy_details

WHERE policy_status = '30240084')

DELETE FROM myCTE WHERE num>1

|||

Interesting, when I write the following query, which is almost exactly like yours:

begin transaction

DELETE
FROM person.address
WHERE ROW_NUMBER() OVER ( PARTITION BY addressId
ORDER BY addressLine1 DESC
) > 1
and addressLine1 = 'fred'
rollback transaction

The error I get is:

Msg 4108, Level 15, State 1, Line 3
Windowed functions can only appear in the SELECT or ORDER BY clauses.

(which is more clear).

|||Hello,
After some research, I tested out this syntax which works:
DELETE c
FROM (SELECT * FROM (SELECT RANK() OVER (PARTITION BY policy_details,CONVERT(CHAR(10),effectdate,103) ORDER BY historyid DESC) as num
FROM policy_details WHERE policy_status = '30240084' ) AS t
WHERE t.num>1) c|||

Hi Limno,

Thanks for your help with the SQL which works fine with my data.

I have written a small strored proc which deletes some data based on certain criteria which works fine but it takes approximately 30 minutes for one million records. Is there any way to write this stored proc logic into a single SQl query or sub queries?

The SP is :

-- ***********************************************************************

declare @.policy_details_id uniqueidentifier

declare @.Prepolicy_details_id uniqueidentifier

declare @.EffectDate datetime

declare @.CloseDate datetime

declare @.historyid int

DECLARE DATECur CURSoR FOR

select distinct(policy_details_id),effectdate,closedate,historyid from SM_Cust_policy_Details

where closedate = '2079-06-06 00:00:00.000' and derivative = 0

order by policy_details_id,effectdate

OPEN DATECur

FETCH NEXT FROM DATECur INTO @.policy_details_id, @.effectdate,@.CloseDate,@.historyid

WHILE @.@.FETCH_STATUS = 0

BEGIN

DELETE SM_Cust_policy_details where policy_details_id=@.policy_details_id

and historyid <> @.historyid

and closedate <> '2079-06-06 00:00:00.000'

and CONVERT(DATETIME,CONVERT(CHAR(10),effectdate,103)) >= CONVERT(DATETIME,CONVERT(CHAR(10),@.effectdate,103))

and policy_status_id = 30240084

and derivative = 0

FETCH NEXT FROM DATECur INTO @.policy_details_id, @.effectdate,@.CloseDate,@.historyid

END

CLOSE DATECur

DEALLOCATE DATECur

-- ***********************************************************************

Once again thanks for your help..

Cheers

Praveen

|||Can you explain what the query is trying to do in english rather than trying to decode your criteria. The potential query may differ depending the nature of the data in your table e.g. uniqueness and nullability of the columns.|||

I need to delete the table data based on the following criteria.

1) "policy_details_id" should be same

2) "effectdate" is greater than or equal to effectdate

3) "HistoryId" should not be same

4) "closedate" is not equal to ' 2079-06-06 00:00:00.000 '

5) "policy_status_id" should be 30240084

6) "Derivative" should be 0

Cheers

Praveen

|||

I can read your code. I got that much from the stored proc you posted. I'm trying to understand what the stored procedure is trying to achieve.

Should just one row per policy_details_id remain after the delete? Why are you doing distinct of the policy_details_id in you cursor query? why delete where effectdate is greater than @.effectdate? Wouldn't that delete more recent rows.

Like I said, I'm trying to understand what this query is doing "in english" and not "in SQL". An explanation similar to your original posting is what I'm after.

|||

Hi Adam,

Thanks for your reply and here are the answers for your queries.

******** Should just one row per policy_details_id remain after the delete?

Yes, I want to keep only one row per policy_details_id after the deletion.

****** Why are you doing distinct of the policy_details_id in you cursor query?

I used distinct caluse in order to reduce the cursor result set in stored proc. Anyway you can avoid distinct caluse in the sql query.

******** why delete where effectdate is greater than @.effectdate? Wouldn't that delete more recent rows

Yes, it is going to delete more recent rows.

Cheers

Praveen

|||

Hello:

Please check this query :

DELETE c

FROM (SELECT * FROM (SELECT *, RANK() OVER (PARTITION BY policy_details_id ORDER BY historyid DESC, effectdate) as num

FROM policy_details WHERE (closedate<> '2079-06-06 00:00:00.000' OR closedate is NULL) AND Derivative=0 AND policy_status_id = 30240084 ) AS t

WHERE t.num>1) c

|||

Hi Limno,

Needs to change the query.

First i need to do the following query..

1) select policy_details_id,effectdate,closedate,historyid from SM_Cust_policy_Details where closedate = '2079-06-06 00:00:00.000' and derivative = 0

Based on the result set of this query i need to do the following delete query.

2) DELETE SM_Cust_policy_details where policy_details_id = resultset_policy_details_id and historyid <> resultset_historyid and closedate <> '2079-06-06 00:00:00.000' and CONVERT(DATETIME,CONVERT(CHAR(10),effectdate,103))>=CONVERT(DATETIME,CONVERT(CHAR(10),resultset_effectdate,103)) and policy_status_id=30240084 and derivative = 0.

Note: resultset_policy_details_id,resultset_historyid,resultset_effectdate are the result set query values of policy_details_id,historyid,effectdate in query number 1.

How can i integrate both of the above queries and make a single query?

Cheers

Praveen

|||

Could you post a set of your sample data in your table and the expected result? Thanks.

|||

The data consists like this..

policy_details_id policy_status_id historyid effectdate closedate

70C36E97-9564-A048-0000-9665018B81FF 30240084 9 2004-09-11 12:00:00.000 2004-10-11 11:34:00.000
70C36E97-9564-A048-0000-9665018B81FF 30240084 10 2004-10-11 11:34:00.000 2005-09-11 11:47:00.000
70C36E97-9564-A048-0000-9665018B81FF 30240084 11 2005-09-11 12:00:00.000 2005-09-11 12:00:00.000
70C36E97-9564-A048-0000-9665018B81FF 14075352 12 2005-09-11 11:47:00.000 2079-06-06 00:00:00.000

First i need to consider the effectdate where closedate is '2079-06-06 00:00:00.000'.In this case it is '2005-09-11 11:47:00.000'.

I need to delete the records where effectdate(only datepart) is equal or greater than '2005-09-11' and policy_status_id should be 30240084.

In the above case the third record i.e historyid = 11 is going to be deleted.

Let me know if you have any problems to understand.

Cheers

Praveen

Friday, March 23, 2012

Help with Select statement

I have 2 columns with data in different sequences in one table referencing a single column in different table.

I'm trying to learn SQL using SQLserver 2000.
I need some help Please!!

I'm having trouble creating a view that will give me the information that i need correctly.
I listed all the tables and the view that I tried but it's not working I dont think i have the view right.

Here is some info to help you understand what I'm trying to get:

Examples of the data that I'm having trouble with
only conscerns two of the tables

CREATE TABLE TDrivers
(
intDriverID INTEGER NOT NULL, <---
strFirstName VARCHAR(25) NOT NULL,
strMiddleName VARCHAR(25) NOT NULL,
strLastName VARCHAR(25) NOT NULL,
strAddress VARCHAR(25) NOT NULL,
strCity VARCHAR(25) NOT NULL,
strState VARCHAR(25) NOT NULL,
strZipCode VARCHAR(10) NOT NULL,
strPhoneNumber VARCHAR(14) NOT NULL,
CONSTRAINT TDriveres_PK PRIMARY KEY (intDriverID)
)
CREATE TABLE TScheduledRoutes
(
intRouteID INTEGER NOT NULL,
intScheduleTimeID INTEGER NOT NULL,
intBusID INTEGER NOT NULL,
intDriverID INTEGER NOT NULL, <Both ref above table
intAlternateDriverID INTEGER NOT NULL, <Both ref above table
CONSTRAINT TScheduleRoutes_PK PRIMARY KEY (intRouteID,intScheduleTimeID)
)

TDrivers Table has
intDriverID 1, 2, 3, 4, 5 and each id is associated with a name

1 = john
2 = mike
3 = sam
4 = jim
5 = tony

TScheduledRoutes Table
has column
intDriverID
and data is 1, 2, 3, 4, 5 that references TDrivers.intDriverID

and has column
intAlternateDriverID
and data is 5, 3, 1, 2, 4 that references TDrivers.intDriverID also

NOTICE the two have different sequence.

I need to get a select statement that would give me a list of
TScheduledRoutes.intDriverID full name
and their assciated alternate driver
TScheduledRoutes.intAlternateDriverID

output would give this as example

(intdriverID 1) john would have alt driverId 5 tony

I can't create a select statement that will give me both names at the same time.

Below is a list of the actual code and the view I cant get to do what I want it to and still keep the database in 3rd normal form.

Any suggestions would be greatly appreciated.

CREATE TABLE TRoutes
(
intRouteID INTEGER NOT NULL,
strRoute VARCHAR(30) NOT NULL,
strRouteDescription VARCHAR(50) NOT NULL,
CONSTRAINT TRoutes_PK PRIMARY KEY (intRouteID)
)

CREATE TABLE TBuses
(
intBusID INTEGER NOT NULL,
strBus VARCHAR(25) NOT NULL,
intCapacity INTEGER NOT NULL,
CONSTRAINT TBuses_PK PRIMARY KEY (intBusID)
)

CREATE TABLE TDrivers
(
intDriverID INTEGER NOT NULL,
strFirstName VARCHAR(25) NOT NULL,
strMiddleName VARCHAR(25) NOT NULL,
strLastName VARCHAR(25) NOT NULL,
strAddress VARCHAR(25) NOT NULL,
strCity VARCHAR(25) NOT NULL,
strState VARCHAR(25) NOT NULL,
strZipCode VARCHAR(10) NOT NULL,
strPhoneNumber VARCHAR(14) NOT NULL,
CONSTRAINT TDriveres_PK PRIMARY KEY (intDriverID)
)

CREATE TABLE TScheduleTimes
(
intScheduleTimeID INTEGER NOT NULL,
strScheduleTime DATETIME NOT NULL,
CONSTRAINT TScheduleTimes_PK PRIMARY KEY (intScheduleTimeID)
)

every column below is a foreign key to other tables

CREATE TABLE TScheduledRoutes
(
intRouteID INTEGER NOT NULL,
intScheduleTimeID INTEGER NOT NULL,
intBusID INTEGER NOT NULL,
intDriverID INTEGER NOT NULL,
intAlternateDriverID INTEGER NOT NULL,
CONSTRAINT TScheduleRoutes_PK PRIMARY KEY (intRouteID,intScheduleTimeID)
)

CREATE NONCLUSTERED INDEX TRoutes_NI ON TRoutes(strRoute)
CREATE NONCLUSTERED INDEX TBuses_NI ON TBuses(strBus)
CREATE NONCLUSTERED INDEX TDrivers_NI ON TDrivers (strLastName,strFirstName)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TBuses_TScheduledRoutes_FK
FOREIGN KEY (intBusID)REFERENCES TBuses(intBusID)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TRoutes_TScheduledRoutes_FK
FOREIGN KEY (intRouteID)REFERENCES TRoutes(intRouteID)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TDrivers_TScheduledRoutes_FK
FOREIGN KEY (intDriverID)REFERENCES TDrivers(intDriverID)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TADrivers_TScheduledRoutes_FK
FOREIGN KEY (intAlternateDriverID)REFERENCES TAltDrivers(intAltDriverID)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TScheduleTimes_TScheduledRoutes_FK
FOREIGN KEY (intScheduleTimeID)REFERENCES TScheduleTimes(intScheduleTimeID)

Here is what I tried but its not working. Is there better way to get the information I need and
keep the database in 3rd Normal form

CREATE VIEW V_SchedualedRoutes AS

SELECT TRoutes.strRoute,
TBuses.strBus,
(TDrivers.strLastName + ', '+ TDrivers.strFirstName)
AS strDriverFullName,
(SELECT TDrivers.strLastName + ', '
+ TDrivers.strFirstName)
FROM TDrivers
INNER JOIN TScheduledRoutes
ON TDrivers.intDriverID =
TScheduledRoutes.intDriverID
WHERE TScheduledRoutes.intAlternateDriverID=
TDrivers.intDriverI)
AS strAltDriFullName, TScheduleTimes.strScheduleTime
FROM TBuses
INNER JOIN TScheduledRoutes
ON TBuses.intBusID = TScheduledRoutes.intBusID
INNER JOIN TScheduleTimes
ON TScheduledRoutes.intScheduleTimeID =
TScheduleTimes.intScheduleTimeID
INNER JOIN TDrivers
ON TScheduledRoutes.intDriverID = TDrivers.intDriverID
AND TScheduledRoutes.intAlternateDriverID =
TDrivers.intDriverID
INNER JOIN TRoutes
ON TScheduledRoutes.intRouteID = TRoutes.intRouteIDI am having trouble following your query, but in essence it appears that the trouble you are having is knowing how to access the same table twice in a query - once for the main driver and once for the alternate driver. The solution is to use table aliases:

SELECT d1.strLastName MainDriver,
d1.strLastName AlternateDriver
FROM TScheduledRoutes sr
INNER JOIN TDrivers d1 ON sr.intDriverID = d1.intDriverID
INNER JOIN TDrivers d2 ON sr.intAlternateDriverID = d2.intDriverID

In your query, this bit looks to me like a syntax error:

(SELECT TDrivers.strLastName + ', ' + TDrivers.strFirstName) FROM TDrivers

... unless SQL Server has a very different SQL syntax that the one I know.|||Sorry about the confusion but thats it. Thank yousql

Help with sample code for ssis surrogate key transform

I am trying to write a ssis surrogate key data transform, my problem is I can't find an example how to add a column to the incoming columns and add some data to it. If anyone has a sample, can you please post it. I found a script option that works but I would like an actual transform.

Thanks

Basically - here is a surrogate key Transform Script - to generate image numbers for products
Input is ProdNum column - output ImgNo colum.
The idea is to get the result like this:
ProdNum ImgNo
1 1
1 2
2 1
3 1
3 2

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Dim imgno As Short, incr As Short, prevProdNum As String

Public Sub New()
imgno = 0
incr = 1
prevProdNum = ""
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Row.ProdNum <> prevProdNum Then
imgno = incr
Else
imgno += incr
End If
Row.ImgNo = imgno
prevProdNum = Row.ProdNum
End Sub
End Class|||Also - you can check out this article "SSIS Generating Surrogate Keys"

Help with RS Report Viewer in .Net 1.1 (Sortable Columns)

Currently, we are using .net 1.1 and SQL 2005. To integrate RS without having to open new browser windows or launch the report manager, we are using the ReporService.asmx service to render the reports. One thing that we are having a difficult time with is allowing columns to be sortable. In report manager, we have sortable columns sorting without a problem, but once the page is rendered within our product using the service, it is rendered without the sortable columns.

While going through the different flags within the service I found that there is a flag for Javascript (which is what I'm assuming I need) but it doesn't change the report when I enable it. I am wondering if there is a combonation of flags that need to be set to allow the sorting to work or if it just not possible to do sorting when rendering the HTML sent from the service. Hopefully this is enough infomation to lead to some answers with this. Thanks.

bump

|||

I don't think it's necessarily a javascript issue -- although it might be if you are using ajaxy stuff since there are apparently issues there.

What's your doctype in your page?

>L<

|||

The 2000 version of the SSRS Web service (ReportService.asmx) doesn't support interactive features. The Report Manager uses URL access for report rendering which supports interactive features. The 2005 web service supports interactive features but you need to pass in the identifier of the sortable item wchich you don't know at design time. To make the long story short, I don't know of a hack to sort via the web service. My recommendation will be to upgrade to ASP.NET 2.0 and use the ASP.NET report viewer which supports all interactive features.

Wednesday, March 21, 2012

Help with RS Report Viewer in .Net 1.1 (Sortable Columns)

bump

|||

I don't think it's necessarily a javascript issue -- although it might be if you are using ajaxy stuff since there are apparently issues there.

What's your doctype in your page?

>L<

|||

The 2000 version of the SSRS Web service (ReportService.asmx) doesn't support interactive features. The Report Manager uses URL access for report rendering which supports interactive features. The 2005 web service supports interactive features but you need to pass in the identifier of the sortable item wchich you don't know at design time. To make the long story short, I don't know of a hack to sort via the web service. My recommendation will be to upgrade to ASP.NET 2.0 and use the ASP.NET report viewer which supports all interactive features.

sql

Monday, March 12, 2012

Help with Query

I have two columns I want to compare, both varchar, and
would be in this format
Subject | Instructor
ACCT101-nnnnn | jcdoe
ACCT101-nnnnn | jcdoe
ACCT102-nnnnn | jcdoe
ACCT102-nnnnn | jcsmith
The subject is made up of (Table.Subject + '-' +
CourseNumber)
What I want to do, is query for those cases where the
Table.Subject(ie ACCT101)has the same instructor for each
instance. So if one teacher was teaching the same
section of the course my query would be like this, from
the above example:
Subject | Instructor
ACCT101 | jcdoe
Does anyone know how I could do this?
Thanks.
You could try this:
select
substring(Subject, charindex('-', Subject)) as Subject,
min(Instructor) as Instructor
from yourTable
group by substring(Subject, charindex('-', Subject))
having min(Instructor) = max(Instructor
or
select distinct
substring(Subject, charindex('-', Subject)) as Subject,
Instructor
from yourTable
where not exists (
select * from yourTable Tcopy
where substring(Tcopy.Subject, charindex('-', Tcopy.Subject)) =
substring(yourTable.Subject, charindex('-', yourTable.Subject))
and Tcopy.Instructor <> yourTable.Instructor
)
Just a suggestion. If the two pieces of the [Subject] column have
independent meanings in your table, you might consider keeping them in
separate columns to avoid having to use SUBSTRING to get the information
out.
Steve Kass
Drew University
spacejunk wrote:

>I have two columns I want to compare, both varchar, and
>would be in this format
>Subject | Instructor
>--
>ACCT101-nnnnn | jcdoe
>ACCT101-nnnnn | jcdoe
>ACCT102-nnnnn | jcdoe
>ACCT102-nnnnn | jcsmith
>--
>The subject is made up of (Table.Subject + '-' +
>CourseNumber)
>What I want to do, is query for those cases where the
>Table.Subject(ie ACCT101)has the same instructor for each
>instance. So if one teacher was teaching the same
>section of the course my query would be like this, from
>the above example:
>Subject | Instructor
>--
>ACCT101 | jcdoe
>--
>Does anyone know how I could do this?
>Thanks.
>
>

Wednesday, March 7, 2012

Help with outer join

I have a table Financial_Values that has the following columns:
Year(pk),
Month (pk),
Account_No (pk),
Amount

The combination year, month & account no varies for each year & month.

I need to create sp or function that creates a result set that has the following columns:

Account_No (pk),
Current Amount,
Prior_Year_Amount
Current YTD_Amount,
Prior_Year_YTD

Because the rows in the Financial_Values (number and values of the Account No) can be

different for the current and prior years, I believe I have to do the following steps

1. Create table #Current_Amount
Year(pk),
Month (pk),
Account_No (pk),
Current_Amount

Insert #Current_Amount
Select Year, Month, Account_No, Amount as Current_Amount
From Financial_Values
Where Financial_Value.Year = @.Current_Year

And Financial_Value.Month = @.Current_Month

2. Create table #Current_YTD_Amount
Year(pk),
Month (pk),
Account_No (pk),
Current_YTD_Amount

Insert #Current_Amount
Select Year, Month, Account_No, Amount as Current_Amount
From Financial_Values
Where Financial_Value.Year = @.Current_Year

And (Financial_Value.Month >= 1 and <= @.Current_Month)

3. Create table #Current_Values
Year(pk),
Month (pk),
Account_No (pk),
Current_Amount,
Current_YTD_Amount

Insert #Current_Values
Select #Current_Amount.Year,
#Current_Amount.Month,
#Current_Amount.Account_No,
#Current_Amount.Current_Amount,
#Current_YTD_Amount.Current_YTD_Amount
From #Current_Amount INNER JOIN #Current_YTD_Amount
On #Current_Amount.Year = #Current_YTD_Amount.Year
And #Current_Amount.Month = #Current_YTD_Amount.Month
And #Current_Amount.Account_No = #Current_YTD_Amount.Account_No

4. Create table #Prior_Year_Amount
Year(pk),
Month (pk),
Account_No (pk),
Prior_Year_Amount

Insert #Prior_Year_Amount
Select Year, Month, Account_No, Amount as Prior_Year_Amount
From Financial_Values
Where Financial_Value.Year = @.Current_Year

And Financial_Value.Month = @.Current_Month

5. Create table #Prior_Year_YTD_Amount
Year(pk),
Month (pk),
Account_No (pk),
Prior_Year_YTD_Amount

Insert #Prior_Year_YTD_Amount
Select Year, Month, Account_No, Amount as Prior_Year_YTD_Amount
From Financial_Values
Where Financial_Value.Year = @.Current_Year

And (Financial_Value.Month >= 1 and <= @.Current_Month)

6. Create table #Prior_Year_Values
Year(pk),
Month (pk),
Account_No (pk),
Prior_Year_Amount,
Prior_Year_YTD_Amount

Insert #Prior_Year_Values
Select #Prior_Year_Amount.Year,
#Prior_Year_Amount.Month,
#Prior_Year_Amount.Account_No,
#Prior_Year.Current_Amount,
#Prior_Year_YTD_Amount.Current_YTD_Amount
From #Prior_Year_Amount INNER JOIN #Prior_Year_YTD_Amount
On #Prior_Year_Amount.Year = #Prior_Year_YTD_Amount.Year
And #Prior_Year_Amount.Month = #Prior_Year_YTD_Amount.Month
And #Prior_Year_Amount.Account_No = #Prior_Year_YTD_Amount.Account_No

7. Create table #Current_and_Prior_Year_Values
Account_No (pk),
Current_Amount,
Current_YTD_Amount,
Prior_Year_Amount,
Prior_Year_YTD_Amount

Select @.Current_Values_Count = Count(Account_No)

From dbo.tblPFW_Current_Values

Select @.Prior_Year_Values_Count = Count(Account_No)

From dbo.tblPFW_Prior_Year_Values

If @.Current_Values_Count > @.Prior_Year_Values_Count

Insert #Current_and_Prior_Year_Values

Select #Current_Values.Account_No,
#Current_Amount.Current_Amount,
#Current_YTD_Amount.Current_YTD_Amount
#Prior_Year_Values.Prior_Year_Amount,
#Prior_Year_YTD_Amount.Prior_Year_YTD_Amount

From #Current_Values RIGHT OUTER JOIN #Prior_Year_Values
On #Current_Values.Year = #Prior_Year_Values.Year
And #Current_Values.Month = #Prior_Year_Values.Month
And #Current_Values.Account_No = #Prior_Year_Values.Account_No

Else

Insert #Current_and_Prior_Year_Values

Select #Prior_Year_Values.Account_No,
#Current_Amount.Current_Amount,
#Current_YTD_Amount.Current_YTD_Amount
#Prior_Year_Values.Prior_Year_Amount,
#Prior_Year_YTD_Amount.Prior_Year_YTD_Amount

From #Prior_Year_Values RIGHT OUTER JOIN #Current_Values
On #Prior_Year_Values.Year = #Current_Values.Year
And #Prior_Year_Values.Month = #Current_Values.Month
And #Prior_Year_Values.Account_No = #Current_Values.Account_No

Steps 1 thru 6 are working fine, however when I get to Step 7, my stored procedure fails with

trying to insert into #Current_and_Prior_Year_Values a null value the primary key Account_No.

If I create all the tables not as temporary tables it still fails the same way, however

if I don't run step seven and then run views like the select statements in Step 7

I get the correct results from the views.

Also if a perform an inner join in step seven vs an right outer join, the step does not fail with

the null insert, however I don't the right number of rows (account no)

I quess my question is why would the right outer joins in step 7, run as part of a sp, return

any null Account No values?

Or could anyone suggest a different way to get the result set I need?

Big O,

Have you considered using a DateTime field in your table instead of separating Year and Month like this? This would make the date functions, e.g. datepart(), dateadd(), datediff(), more accessible to you.

If I add a field to your table -- let's call it AccountDate -- I can get the results your after using these functions:

Code Snippet

select

a.Account_No,

b.CurrentAmount,

c.YTDAmount,

d.PriorYTDAmount,

e.PriorAmount

from (

select distinct

Account_No

from FinancialValues

) a

left outer join (

select -- CURRENT AMOUNT BY ACCOUNT

Account_No,

Amount as CurrentAmount

from FinancialValues

where AccountDate = dateadd( dd, -1 * datepart(dd,getdate()) + 1, getdate())

) b

on a.Account_No=b.Account_No

left outer join (

select -- YEAR TO DATE AMOUNT BY ACCOUNT

Account_No,

SUM(Amount) as YTDAmount

from FinancialValues

where year(AccountDate) = year(getdate())

group by Account_No

) c

on a.Account_No=c.Account_No

left outer join (

select -- PRIOR YEAR TO DATE AMOUNT BY ACCOUNT

Account_No,

SUM(Amount) as PriorYTDAmount

from FinancialValues

where year(AccountDate) = year(getdate()) - 1 AND

AccountDate <= dateadd( yy, -1, getdate())

group by Account_No

) d

on a.Account_No=d.Account_No

left outer join (

select -- PRIOR YEAR AMOUNT BY ACCOUNT

Account_No,

Amount as PriorAmount

from FinancialValues

where AccountDate = dateadd(yy, - 1, dateadd( dd, -1 * datepart(dd,getdate()) + 1, getdate()))

) e

on a.Account_No=e.Account_No

It's not the prettiest thing, but it's relatively straightforward. Each value is calculated in a nested subquery. A list of all accounts is generated in the first subquery and these sets of calculated values are joined to it.

Since this is a beginner's forum, I'd generally recommend that you avoid heavy use of temporary tables. If you find yourself creating these to store intermediate data sets, challenge yourself to use nested queries. The performance will be better (up to a point) and this will help you become comfortable with more and more complex SQL problems.

Bryan

PS The code above has not been properly tested. You may need to tweak a few things to make this work exactly as you need.