Which is the Best Method - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Which is the Best Method

Author

Topic

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-04-12 : 12:59:07

Hi Team,

I have a table #Destination which has columns (Year, Jan, Feb, …., Dec)
I have another table #Source which has columns (Item , AchievedDate)

I have a SQL Job that will run first day of each month. It will insert/update records into #Destination table after selecting data from #Source table. The selection criteria (from #Source table) is that the month of AchievedDate is previous month.

What is the best approach to achieve this
1) When readability is the higher priority ?
2) When performance is the higher priority ?

Thanks
Lijo Cheeran Joseph

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-12 : 13:03:07

Perhaps you could supply some sample data and a sample of the outcome you desire.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-12 : 13:12:14

what will be values stored in Jan,feb, etc columns?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-04-12 : 13:13:30

Sample Date (with some difference in schema)

# Source
StudentID, Grade, AcheivedDate
103 New Entrant 1/1/2010
103 Inspired 2/5/2010
107 NewEntrant 1/5/2010
107 Master 3/31/2010

# Destination
Grade Year Jan Feb Mar
New Entrant 2010 2 0 0
Inspired 2010 0 1 0
Master 2010 0 0 1

Thanks
Lijo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-12 : 13:20:57

[code]SELECT Grade,YEAR(AchievedDate),
COUNT(CASE WHEN MONTH(AchievedDate) = 1 THEN 1 ELSE NULL END) AS Jan,
COUNT(CASE WHEN MONTH(AchievedDate) = 2 THEN 1 ELSE NULL END) AS Feb,
COUNT(CASE WHEN MONTH(AchievedDate) = 3 THEN 1 ELSE NULL END) AS Mar,
...
COUNT(CASE WHEN MONTH(AchievedDate) = 12 THEN 1 ELSE NULL END) AS Dec
FROM Table
GROUP BY Grade,YEAR(AchievedDate)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-04-12 : 13:25:14

Hi Vishakh,

The query works if it was a selection. In my case, I have to update the columns Jan,Feb,...

I don't know which column to update/insert. Can I write a CASE for selecting the insert column also? is it the best method?

Thanks
Lijo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-12 : 13:30:35

for update just use like


UPDATE d
SET d.Jan = s.Jan,
d.Feb= s.Feb,
..
FROM #Destination d
JOIN (SELECT Grade,YEAR(AchievedDate) AS Yr,
COUNT(CASE WHEN MONTH(AchievedDate) = 1 THEN 1 ELSE NULL END) AS Jan,
COUNT(CASE WHEN MONTH(AchievedDate) = 2 THEN 1 ELSE NULL END) AS Feb,
COUNT(CASE WHEN MONTH(AchievedDate) = 3 THEN 1 ELSE NULL END) AS Mar,
...
COUNT(CASE WHEN MONTH(AchievedDate) = 12 THEN 1 ELSE NULL END) AS Dec
FROM #Source
GROUP BY Grade,YEAR(AchievedDate))s
ON s.Grade=d.Grade
AND s.Yr=d.[Year]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-04-12 : 13:35:17

Thanks. however there is one more challenge.

Suppose we are running the job on March 1st. The selection will be only for data in the February's achievements. If we use the aboe query, it will overwrite the value of January as zero. Isn't it?

How can we overcome this?

Thanks
Lijo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-12 : 13:40:19

you mean you want update only prev months value at any one time?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-04-12 : 13:43:10

Yes. If I am runnig the Job on March it hast to update only Feb records of that year.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-12 : 13:55:25

[code]UPDATE d
SET d.Jan = CASE WHEN MONTH(DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0))=1 THEN s.Cnt ELSE d.Jan END,
d.Feb= CASE WHEN MONTH(DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0))=2 THEN s.Cnt ELSE d.Feb END,
..
d.Dec=CASE WHEN MONTH(DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0))=12 THEN s.Cnt ELSE d.Dec END
FROM #Destination d
JOIN (SELECT Grade,YEAR(AchievedDate) AS Yr,
COUNT(1) AS Cnt
FROM #Source
WHERE AchievedDate >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0)
AND AchievedDate < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
GROUP BY Grade,YEAR(AchievedDate))s
ON s.Grade=d.Grade
AND s.Yr=d.[Year]
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-04-12 : 14:03:24

Yes, It will work.

I have to document it as a design. Hence I think, it is better to go for a 12 IF ELSE. (I have to toil a lot to document the above approach

)

Also, it will cause an update to 11 coulumns (with existing values )unwantedly. Isn't it?

Anyway, many thanks for your encouraging support.

Thanks
Lijo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-12 : 14:04:43

quote:
Originally posted by Lijo Cheeran Joseph

Yes, It will work.

I have to document it as a design. Hence I think, it is better to go for a 12 IF ELSE. (I have to toil a lot to document the above approach )

Also, it will cause an update to 11 coulumns (with existing values )unwantedly. Isn't it?

Anyway, many thanks for your encouraging support.

Thanks
Lijo

yup. it will check all columns always. but you can dispense with it as your dest table has month values in columns rather than rows

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-04-16 : 08:45:59

Just one appendix question.

Is both the following correct for getting previous month ? Any subtle bug?

SELECT MONTH ( DATEADD(MONTH,-1,GetDate()) )
SELECT MONTH(DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0))

Thanks

Lijo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 04:25:09

quote:
Originally posted by Lijo Cheeran Joseph

Just one appendix question.

Is both the following correct for getting previous month ? Any subtle bug?

SELECT MONTH ( DATEADD(MONTH,-1,GetDate()) )
SELECT MONTH(DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0))

Thanks

Lijo

Both look fine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources