No idea - sorry, but it isn't clear what you need from the count or how it applies.
Ok thanks for help. I can't explain the count any other way than I have. The count is to count the number of securities with same fund code and class . then do the rest of the logic that u provided
this bit of logic seems to work on the count side of things.
SELECT [Fundcode], class,
max(connectionCount) as ConnectionCount
from
(
select [Fundcode], class,
count(*) as ConnectionCount
from [dbo].[Table]
group by [Fundcode], class
) t
group by [Fundcode], class
how would i then get it to work with the other logic for example
if ConnectionCount = 6
then do this
Select t.[Fund Code]
, t.Class
, Qtr = datepart(quarter, t.VaudationDate)
, Nav = max(Case When month(t.VaudationDate) % 3 = 1 Then t.SharesinIssue End)
, Shares = max(Case When month(t.VaudationDate) % 3 = 2 Then t.NavperShareinLocalCurrency End)
, Net = max(Case When month(t.VaudationDate) % 3 = 0 Then t.NetAssetsinLocalCurrency End)
From @table1 t
Group By
t.[Fund Code]
, t.Class
, datepart(quarter, t.VaudationDate);
I really have no idea why you insist on using 'count' for anything - I have asked several questions which you have not answered...which could help identify a solution, but you keep coming back to count.
Again - you are only reporting on 3 months (if I read this correctly) and yet need 6 months of data? Why?
What happens if you have 9 months of data?
What happens if you only have 3 months of data?
What happens if you only have 5 months of data for the fund/class?
What happens if you have 7 months of data for the fund/class? Ignore this one?
Until you can answer those questions - there is no reason to even bother with using count for anything.
I think you're looking for any records that have a count of 6 regardless of date, then only update those. Just a guess, but this only updates the one class
DDL
Summary
Declare @table1 Table(
[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))
Insert Into @table1
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');
Code
Summary
;with cte as
( Select [Fund Code], Class, Count(1) as N
From @table1
Group By
[Fund Code]
, Class
)
Select t.[Fund Code]
, t.Class
, Qtr = datepart(quarter, t.VaudationDate)
, Nav = max(Case When month(t.VaudationDate) % 3 = 1 and c.N = 6 then t.SharesinIssue End)
, Shares = max(Case When month(t.VaudationDate) % 3 = 2 and c.N = 6 Then t.NavperShareinLocalCurrency End)
, Net = max(Case When month(t.VaudationDate) % 3 = 0 and c.N = 6 Then t.NetAssetsinLocalCurrency End)
From cte c
join @table1 t
on c.[Fund Code] = t.[Fund Code]
Group By
t.[Fund Code]
, t.Class
, datepart(quarter, t.VaudationDate);
Yes that correct mike. Thanks for been able to figure out what i was trying to get at. thought i was explaining it well enough but sometimes im not so good at it.
that logic work as expect as well so thanks for that. if i wanted to updated the data it pulls back into columns in the same table based with the same column name as in the query like nav, share and net on a match up on the fund code class and datepart(quarter, t.VaudationDate) how would i go about getting the select part logic into the update.
are those columns in the same table?? Can you provide the table layout for the table that needs to be updated?
Declare @table1 Table(
[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))
Thats will be the table layout and when you do the update and it finds the match up on fund code class and valuation date. the value you get for nav ,shares and net from the select part in the query you sent will update the nav shares or net column in the table.
you can change to an update
;with cte as
( Select [Fund Code], Class, Count(1) as N
From @table1
Group By
[Fund Code]
, Class
)
update t
set Nav = x.Nav
, Shares = x.Shares
, Net = x.Net
from #Table1 t
join (
Select t.[Fund Code]
, t.Class
, Qtr = datepart(quarter, t.VaudationDate)
, Nav = max(Case When month(t.VaudationDate) % 3 = 1 and c.N = 6 then t.SharesinIssue End)
, Shares = max(Case When month(t.VaudationDate) % 3 = 2 and c.N = 6 Then t.NavperShareinLocalCurrency End)
, Net = max(Case When month(t.VaudationDate) % 3 = 0 and c.N = 6 Then t.NetAssetsinLocalCurrency End)
From cte c
join #table1 t
on c.[Fund Code] = t.[Fund Code]
Group By
t.[Fund Code]
, t.Class
, datepart(quarter, t.VaudationDate)) x
on t.[Fund Code] = x.[Fund Code]
and t.Class = x.Classthanks for that it works as expect. if i wanted to update the last part with the qtr results.. something like this
on t.[FundCode] = x.[FundCode]
and t.Class = x.Class
and t.qtr = x.qtr
j
actually do need the help. i used the VaudationDate like this after adding it into the logic. but columns as been poplauted where the fundcode class and VaudationDate are not the same
on t.[FundCode] = x.[FundCode]
and t.Class = x.Class
and t.VaudationDate = x.VaudationDate
j
or is there anyway i can add the qtr values to a field in my table.
can you provide a sample of the expected output?
actually that probably wont work either. i just need the update to update the table where fund code class and valuation date or the qtr value match up.
at the moment its populating all the data with the same fund code and class name no matter what date is there.
i haave added in valuation date to query but it doesnt seem to be working
On that update statement above, did you try adding the date ? Something like this:
;with cte as
( Select [Fund Code], Class, Count(1) as N
From @table1
Group By
[Fund Code]
, Class
)
update t
set Nav = x.Nav
, Shares = x.Shares
, Net = x.Net
from #Table1 t
join (
Select t.[Fund Code]
, t.Class
, Qtr = datepart(quarter, t.VaudationDate)
, Nav = max(Case When month(t.VaudationDate) % 3 = 1 and c.N = 6 then t.SharesinIssue End)
, Shares = max(Case When month(t.VaudationDate) % 3 = 2 and c.N = 6 Then t.NavperShareinLocalCurrency End)
, Net = max(Case When month(t.VaudationDate) % 3 = 0 and c.N = 6 Then t.NetAssetsinLocalCurrency End)
From cte c
join #table1 t
on c.[Fund Code] = t.[Fund Code]
Group By
t.[Fund Code]
, t.Class
, datepart(quarter, t.VaudationDate)) x
on t.[Fund Code] = x.[Fund Code]
and t.Class = x.Class
and datepart(quarter, t.VaudationDate) = x.qtr
just tired it there and its still populating all the column with the same fundcode and class name instead of populating just the data with the 01 date or 02 date
any other ideas that might work. tried most thing i think
Can you post what you are expecting the results to be? also, can you explain about if month starts with 01 or 02 or 03? What about 04, 05, 06? Are you looking at current month and then looking out the next 2 months ??
i just 2 row of an example might be easier to get
so if this is the raw data for just the 01 and 02 date
| [Fund Code] | Class | VaudationDate | Currency | Currency1 | SharesinIssue | NavperShareinLocalCurrency | |
|---|---|---|---|---|---|---|---|
| MN-MNEM | Class A 01/31/2019 23:59 | USD | USD | 8447181 | 88679 | ||
| MN-MNEM | Class A 02/28/2019 23:59 | USD | USD | 8447181 | 7195227681 |
[Fund Code] Class VaudationDate SharesinIssue NavperShareinLocalCurrency Nav Shares
MN-MNEM Class A 01/31/2019 23:59 84471.81 886.79 84471.81
MN-MNEM Class A 02/31/2019 23:59 8447181 7195227681 7195227681
so based on the update logic i just what the SharesinIssue to popluate the nav column for just the 01/31/2019. and NavperShareinLocalCurrency would populate the shares column where valaution date is just |02/31/2019 23:59
based on this part of the update sql script
, Nav = max(Case When month(t.VaudationDate) % 3 = 1 Then t.SharesinIssue End)
, Shares = max(Case When month(t.VaudationDate) % 3 = 2 Then t.NavperShareinLocalCurrency End)
i will end up looking at every month from 01 to 12. im just trying to get a few working first then build the rest of the logic up
can someone explain the follwoing to me
max(Case When month(t.VaudationDate) % 3 = 1 when date is 01
max(Case When month(t.VaudationDate) % 3 = 2 when date is 02
max(Case When month(t.VaudationDate) % 3 = 0 hen date is 03
i dont get how the % 3 = 0 would be month with 03 in it should it not be ax(Case When month(t.VaudationDate) % 3 = 3?
if not how would i get date is 04, 05 06 etc