Count data then popluated columns based on logic

Transact-SQL
42

the max thing was breaking up into quarters, but if you are just looking for the month, then populate the correct fields, you made it a lot harder than it needed to be

DDL

Summary

Create table #table1 (
[Fund Code] varchar(50)
, Class varchar(50)
, VaudationDate datetime
, FundBaseCurrency varchar(50)
, ClassCurrency varchar(50)
, SharesinIssue decimal(19,6)
, NavperShareinLocalCurrency decimal(19,6)
, NetAssetsinLocalCurrency decimal(19,6)
, Nav decimal(19,6)
, Shares decimal(19,6)
, Net decimal(19,6))

Insert Into #table1 ([Fund Code], Class, VaudationDate, FundBaseCurrency, ClassCurrency, SharesinIssue, NavperShareinLocalCurrency, NetAssetsinLocalCurrency)
Values ('MN-MNEM', 'Class A', '01/31/2019 23:59', 'USD', 'USD', '84471.81', '886.79', '886.79')
, ('MN-MNEM', 'Class A', '02/28/2019 23:59', 'USD', 'USD', '84471.81', '84471.81', '71952276.81')
, ('MN-MNEM', 'Class A', '03/29/2019 23:59', 'USD', 'USD', '84471.81', '861.76', '851.79')
, ('MN-MNEM', 'Class A', '04/30/2019 23:59', 'USD', 'USD', '55599.46', '836.17', '46490465.40')
, ('MN-MNEM', 'Class A', '05/31/2019 23:59', 'USD', 'USD', '49864.89', '823.05', '41041370.93')
, ('MN-EONE', 'Class A', '01/31/2019 23:59', 'USD', 'USD', '25000.00', '1011.70', '25292430.52')
, ('MN-EONE', 'Class A', '02/28/2019 23:59', 'USD', 'USD', '39826.57', '914.86', '36435824.32')
, ('MN-EONE', 'Class A', '03/29/2019 23:59', 'USD', 'USD', '75897.58', '954.71', '72460503.15')
, ('MN-EONE', 'Class A', '04/30/2019 23:59', 'USD', 'USD', '75897.58', '848.24', '64379042.81')
, ('MN-EONE', 'Class A', '05/31/2019 23:59', 'USD', 'USD', '75897.58', '857.11', '65052524.70')
, ('MN-EONE', 'Class A', '06/28/2019 23:59', 'USD', 'USD', '75897.58', '851.28', '64610299.45');

Query

Summary

;with cte as
( Select [Fund Code], Class, Count(1) as N
From #table1
Group By [Fund Code], Class)

update t
set Nav = Case When month(t.VaudationDate) = 1 and c.N = 6 then t.SharesinIssue End
, Shares = Case When month(t.VaudationDate) = 2 and c.N = 6 Then t.NavperShareinLocalCurrency End
, Net = Case When month(t.VaudationDate) = 3 and c.N = 6 Then t.NetAssetsinLocalCurrency End
From cte c
join #table1 t
on c.[Fund Code] = t.[Fund Code]
and c.Class = t.Class

select * from #table1

43

ya that did the trick all right moslty.

any reason if its like this

;with cte as
( Select [Fund Code], Class, Count(1) as N
From #table1
Group By [Fund Code], Class)

update t
set Nav = Case When month(t.VaudationDate) = 1 and c.N = 6 then t.SharesinIssue End
, Shares = Case When month(t.VaudationDate) = 2 and c.N = 6 Then t.NavperShareinLocalCurrency End
, Net = Case When month(t.VaudationDate) = 3 and c.N = 6 Then t.NetAssetsinLocalCurrency End
From cte c
join #table1 t
on c.[Fund Code] = t.[Fund Code]
and c.Class = t.Class
and month(t.ValuationDate)=month(t.ValuationDate)

or even with t.ValuationDate = t.ValuationDate]

just doesnt seem to updated the table in the columns with the correct date

44

should work because when you do it like this

join [dbo].[BNYIRE253Table] t
on c.[FundCode] = t.[FundCode]
and c.Class = t.Class
and month(t.ValuationDate)='1'

it works

45

This is what my query produces. What are you expecting? Your join won't work because you're joining the same column to itself

image
image1195×243 16.7 KB

46

sorry not sure what happened but its working like you. thanks very much for the help.

just one last thing. if i want to delete data from the table where the count wasnt equal to 6 like the mn_mnem fund in your last post as it only has 4 securities with the same fund code and class. how would i do it

47 
;with cte as
( Select [Fund Code], Class, Count(1) as N
From #table1
Group By [Fund Code], Class)


delete t
From cte c
join #table1 t
on c.[Fund Code] = t.[Fund Code]
and c.Class = t.Class
and c.N < 6
48

Simple - the % is the modulo operator and returns the remainder of integer division. In the above we are dividing by 3 and getting the remainder.

1 / 3 = 0 remainder 1, so 1 % 3 = 1
2 / 3 = 0 remainder 2, so 2 % 3 = 2
3 / 3 = 1 remainder 0, so 3 % 3 = 0

Now - if we use that for the other months...

4 / 3 = 1 remainder 1, so 4 % 3 = 1
5 / 3 = 1 remainder 2, so 5 % 3 = 2
6 / 3 = 2 remainder 0, so 6 % 3 = 0
7 / 3 = 2 remainder 1, so 7 % 3 = 1
8 / 3 = 2 remainder 2, so 8 % 3 = 2
9 / 3 = 3 remainder 0, so 9 % 3 = 0
10 / 3 = 3 remainder 1, so 10 % 3 = 1
11 / 3 = 3 remainder 2, so 11 % 3 = 2
12 / 3 = 4 remainder 0, so 12 % 3 = 0

This allows us to bucket the data for each quarter by the month of the quarter - so we get the first month of any quarter as 1, 4, 7, 10 or the second month of the quarter as 2, 5, 8, 11 or the third month of the quarter as 3, 6, 9, 12.

This also allows you to roll up the values by fund/class - so you get a single row for each fund/class and quarter without having to worry about a count of securities.

In your solution - all you are doing is repeating values in other columns but only in the first 3 months and only if the number of securities is exactly 6...which to me doesn't make sense - but it seems to be what you are expecting.

49

thanks very much for your help and for explaining the logic in great detail. really helped

50

Wow,
Thank You, Jef!