Count data then popluated columns based on logic

Transact-SQL
1

Hi

I have the following table

|Fund Code|Class |ValuationDate|SharesinIssue|NavperShareinLocalCurrency|
|MN-MNEM|Class A|01/31/2019 23:59|84,471.81|886.7921|
|MN-MNEM|Class A|02/28/2019 23:59|84,471.81|851.7904|
|MN-MNEM|Class A|03/29/2019 23:59|84,471.81|861.7579|
|MN-MNEM|Class A|04/30/2019 23:59|55,599.46|836.1676|
|MN-MNEM|Class A|05/31/2019 23:59|49,864.89|823.0515|
|MN-MNEM|Class A|06/28/2019 23:59|38,517.32|837.131|
|MN-MNEM|Class A|07/31/2019 23:59|32,479.80|832.2994|
|MN-MNEM|Class A|08/30/2019 23:59|32,479.80|843.2283|
|MN-MNEM|Class A|09/30/2019 23:59|32,479.80|824.7786|
|MN-MNEM|Class A|10/31/2019 23:59|31,262.91|816.5636|
|MN-MNEM|Class A|11/29/2019 23:59|31,030.23|785.6692|
|MN-MNEM|Class A|12/31/2019 23:59|29,437.19|767.3134|
|MN-EONE|Class A|06/30/2019 23:59|25,000.00|1,011.70|
|MN-EONE|Class A|07/31/2019 23:59|39,826.57|914.8622|
|MN-EONE|Class A|08/31/2019 23:59|75,897.58|954.7143|
|MN-EONE|Class A|09/30/2019 23:59|75,897.58|848.2358|
|MN-EONE|Class A|10/31/2019 23:59|75,897.58|857.1094|
|MN-EONE|Class A|11/30/2019 23:59|75,897.58|851.2828|
|MN-EONE|Class A|12/31/2019 23:59|75,897.58|1,015.00|

I need to first of all count the number of records based on fundcode and class. then if there is 6 records the same when you count them populate the SharesinIssue from table1 to columnA in table 2 where valuation date begings with 01. if it has 02 in the date populate the SharesinIssue from table1a to columnB in table 2. if it has 03 in the date populate SharesinIssue from table 1 to columnC in table 2.

is this possible and if so any idea how i would even go about doing it

2

its really easy to do what you are saying .. using CASE statements .. maybe tally table

please explain what you are saying .. like below .. so that its easy to understand ..

image

3

sample
sample1152×686 207 KB

So you count he fundcode and class name. if its 6 and valuation date begins with 01 populate sharesissue from table into the nav in table 2. if valuation date begins with 02 populate navpershares from table into the shares in table 2 etc. I can run a sort to delete duplicates if needs be.

hopefully thats a bit clearer

4

can i still use case for this? if so how would i go about it

5

hi

looking at your data .. dont know what needs to be done ..

it looks very easy to do ..

But not able to understand ..

-- how to make it very very easy for others to understand .. so that they can help you ..is a PHD subject by itself ..

6

please provide this sample data in following format

declare @table1 table([Fund Code] varchar(50), 
Class  varchar(50), VaudationDate datetime, ) ---etc listing out all of the columns

insert into @table1
select 'MN-MNEM', 'MN-MNEM'  --etc with all of the data.

help us help you.

7

hey. not sure how else to explain it to be honest. here is the data as requested.

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)

) ---etc listing out all of the columns

insert into @table1

select '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'

8

Your example shows row 3 for fund MN-EONE value from NetAssetsInLocalCurrency. Are you looking to pivot the data for each fund?

It isn't clear why you would include the first fund - as that only has 1 item for MN-MNEM/MN-MNEM and 5 rows for MN-MNEM/Class A. But you stated you want to only include it if the count is 6 - and not include the next 3 months.

This seems to just be a cross-tab/pivot query where you pivot the data based on the month. This would mean the first month has the beginning SharesInIssue for the quarter - Shares has the value from the second month - and net has the value from the final month in that quarter.

9

yes this it it
. This would mean the first month has the beginning SharesInIssue for the quarter - Shares has the value from the second month - and net has the value from the final month in that quarter.
If count(classes that are the same) =6

If Valuation date begins with 01
Populate nav with SharesinIssue

If Valuation date begins with 02
Populate shares with NavperShareinLocalCurrency

If Valuation date begins with 03
Populate net with NetAssetsinLocalCurrency

10

Okay - what about months 4, 5 and 6? How do you want those displayed...or are you just ignoring them?

I am assuming you want the combination of [Fund Code] and [Class] and that your original example identified an incorrect class (your DDL does not have that problem).

I am confused by the requirement for the count to be 6 - how does that figure into the logic? It really isn't necessary unless you are assuming that a count of 6 includes 2 quarters - but then it doesn't follow if you decide to pull data for 3 quarters (or more).

Wouldn't it be better to show the values for quarter based on the valuation date?

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');

 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);

This breaks it out for each quarter - It should get you started on the rest of your requirements.

11

sorry only getting back to this now.

The count isnt about date. its about the amount of securities with the same fundcode and class name.

12

your requirements state exactly results based on date

" if it has 02 in the date populate the SharesinIssue from table1a to columnB in table 2. if it has 03 in the date populate SharesinIssue from table 1 to columnC in table 2."

13

yes there is 2 parts to it. i was talking about the count part and has nothing to do wioth date.

If count =6 ( 6 securites with the same fund code and class name)

If Valuation date begins with 01
Populate nav with SharesinIssue

If Valuation date begins with 02
Populate shares with NavperShareinLocalCurrency

If Valuation date begins with 03
Populate net with NetAssetsinLocalCurrency

etc

14

so in your excel image example the count is 4 for
MN-MNEM Class A where do you get 6?

what happens if it is 4? or count is 2 etc

15

i must not havew copied them all up or missed some but thought i counted 6 for the examples.

There will be similar logic for 2 and 9 and they will very similar logic and probably end up populating some of the same fields as count = 6. but for now just want to get the count for 6 working and writing to the new table

16

I think you need to take a step back and review your logic - you seem to be stuck on this idea of a count of 6 for some reason, but don't seem to be able to explain how or why you need a count of 6 - nor what to count to get to that value.

Let's assume that count does mean something...what happens if you have 6 rows in the results all with the same fund code and class and all with dates in the month of January ('01/31/2019 23:59')?

If that cannot happen - what happens if you have 6 rows for the same fund - but the first 3 are for one class and the next 3 are for a different class - and the first 3 are for months 1 through 3 and the next 3 are for months 4 through 6?

Or - let's say you only have 5 rows for a specific fund and class? Months 1 through 5 only (as shown in your sample data)? Do we just exclude it from the results because it doesn't have a count of 6?

Or how about 6 rows - where you have months 1 through 6 - same fund code but a different class for each month? Do we drop that from the results?

It also seems that you only care about displaying the results for the first 3 months...but require all 6 months in the results. That doesn't make sense...why include the rows if you don't need any data from those rows?

2 Likes
17

the count does mean something. its the number of securities with the same fund code and class name

the same dates cant happen.

if you dont have 6 securities with the same fund code and class name you do nothing.

this is only a snipped of what i want to do. i didnt want to be adding to much. i will be taking every month into account probably but i just wanted to get some working and go from there.

18

can you please expound on what you mean with "you do nothing" for those with less than 6 what do you want to see in table 2

19

So will all dates be the end of the month - in consecutive order from Jan through June? Or - could they start in Feb and end in July? Or can they all be in Jan - or all be in Jan through Mar (with a total of 6 or more)?

What do you want to do with rows 4 through 6? You keep saying you must have 6 rows...but no idea what you want to do with those extra rows...you seem to only want the data from the first 3 rows.

I have provided a start...what is wrong with that solution?

20

the solution does work apart from the count bit. How can i get that into is as well.