Solved - Self Join? - 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

General SQL Server Forums

New to SQL Server Programming

Solved - Self Join?

Author

Topic

ertweety
Starting Member

15 Posts

Posted - 2012-06-11 : 17:20:33

Help!

Here is an example of some data that I have in a table called Dispatches.....

Month/Vendor #/ Category/ Month_Total/ YTD_Total
Jan/ A0055/ Flatbed/ 6/ 10
Jan/ A0055/ Tow/ 4/ 10
Jan/ A0055/ Light/

My question is how to I get the YTD_Total to show 10 for the 'Light' category? I have this happening throughout my table with all the null monthly fields.

shilpash
Posting Yak Master

103 Posts

Posted - 2012-06-11 : 17:46:23

select MONTH,[vendor #],Category,Month_Total/YTD_Total=CASE WHEN category='Light' THEN 10 END FROM Dispatches

shilpash
Posting Yak Master

103 Posts

Posted - 2012-06-11 : 17:47:55

select MONTH,[vendor #],Category,Month_Total,YTD_Total=CASE WHEN category='Light' THEN 10 END FROM Dispatches

ertweety
Starting Member

15 Posts

Posted - 2012-06-11 : 17:53:25

Sorry, I should be more clear. I have multiple blanks for different vendors in the data for YTD_Total. They all wouldn't equal 10. Please see example below.

Month Vendor# Category Month_Total YTD_Total
Jan A0055 Flatbed 6 10
Jan A0055 Tow 4 10
Jan A0055 Light
Jan A1111 Flatbed 20 22
Jan A1111 Tow
Jan A1111 Light 22

I was thinking there must be a way to do a self join on the same table and say where there is a null for YTD_Total take the field without a null where the month and vendor# are equal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-11 : 18:34:25

is that blank ('') or NULL? I think its latter one.

Show your current code

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

ertweety
Starting Member

15 Posts

Posted - 2012-06-11 : 19:51:30

It's definitely null. The code I built is several pages long that's why I haven't shown it. I was just hoping that there was a way to update the YTD amounts when no monthly amount exists. Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-12 : 15:42:17

see scenario 1 here

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2012-06-12 : 22:10:25

this might be a stupid method.

Declare @fun table([Month] varchar(3), Vendor# varchar(5), Category varchar(7), Month_Total int, YTD_Total int)
insert into @fun select
'Jan','A0055','Flatbed', 6, 10 union all select
'Jan','A0055','Tow', 4, 10 union all select
'Jan','A0055','Light', 0, 0 union all select
'Jan','A1111','Flatbed', 20, 22 union all select
'Jan','A1111','Tow', 0, 0 union all select
'Jan','A1111','Light', 22, 0

select *, sum(YTD_Total)over (partition by [Month], Vendor#)/sum(case when YTD_Total <> 0 then 1 else 0 END) over (partition by [Month], Vendor#)
from @fun

lisayling
Starting Member

5 Posts

Posted - 2012-06-13 : 05:39:59

en, i can accept this view, but not the fully
unspammed
lisayling

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-13 : 10:16:24

quote:
Originally posted by lisayling

en, i can accept this view, but not the fully
unspammed
lisayling

why? whats the issue?

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-13 : 10:19:31

Their issue is they're a spammer.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources