How to get date until current month march and fill gap between monthes ?

Question

I work on sql server 2017 i face issue i need to make select statment get Partid from last month until current month

based on last date exist per partid

and on same time if there are any gaps between dates then file it based on last date

so

first case if i found partid with last date 2022-01-08 then i will add 08-02-2022 and 08-03-2022 as partid 6070

second case if partid with date on month 7 and month 10 and no date per part id on month 8 and 9 then it must display this gap

according to last month as partid 1234 have gap

both cases must applied for all data based on partid

date used below on formate yyyy-mm-dd

create table Parts  
(  
  
PartId int,  
CreatedDate date  
)  
insert into Parts(PartId,CreatedDate)  
values  
(1234,'2021-07-03'),  
(1234,'2021-10-05'),  
(1234,'2021-11-05'),  
(5981,'2021-11-15'),  
(5981,'2021-12-03'),  
(6070,'2021-12-12'),  
(6070,'2022-01-08')  

i need to make select statment display parts as expected result

green rows only for more clear that these parts must added

Expected result

Answer 1

Hi Sir,
To create or change any sql in master data services only model admin are allowed.
Here I share some informations, hope its can help you.

To perform this procedure:

You must have permission to access the System Administration functional area.

You must be a model administrator. For more information, see Administrators (Master Data Services).

You must have an entity to create the attribute for. For more information, see Create an Entity (Master Data Services).

To create a date attribute

In Master Data Manager, click System Administration.

On the Manage Model page, select a model from the grid and then click Entities.

On the Manage Entity page, select the row for the entity that you want to create an attribute for.

Click Attributes.

On the Manage Attributes page, do one of the following and then click Add.

If the attribute is for leaf members, select Leaf from the Member Types list box.

If the attribute is for consolidated members, select Consolidated from the Member Types list box.

If the attribute is for collections, select Collection from the Member Types list box.

In the Name box, type a name for the attribute. For a list of words that should not be used as attribute names, see Reserved Words (Master Data Services).

Optionally, type a display name, and type a description for the attribute in the Description box.

In the Display pixel width box, type the width of the attribute column to be displayed in the Explorer grid.

From the Attribute type list, select Free-form.

From the Data type list, select DateTime.

From the Input mask list, select a format for dates.

Optionally, select Enable change tracking to track changes to groups of attributes. For more information, see Add Attributes to a Change Tracking Group (Master Data Services).

Click Save.

Format:-

DateTime thisDate1 = new DateTime(2011, 6, 10);
Console.WriteLine("Today is " + thisDate1.ToString("MMMM dd, yyyy") + ".");

DateTimeOffset thisDate2 = new DateTimeOffset(2011, 6, 10, 15, 24, 16,
TimeSpan.Zero);
Console.WriteLine("The current date and time: {0:MM/dd/yy H:mm:ss zzz}",
thisDate2);
// The example displays the following output:
// Today is June 10, 2011.
// The current date and time: 06/10/11 15:24:16 +00:00

To set month

DateTime date1 = new DateTime(2008, 8, 29, 19, 27, 15);

Console.WriteLine(date1.ToString("dddd dd MMMM",
CultureInfo.CreateSpecificCulture("en-US")));
// Displays Friday 29 August
Console.WriteLine(date1.ToString("dddd dd MMMM",
CultureInfo.CreateSpecificCulture("it-IT")));
// Displays venerdì 29 agosto

To set day

DateTime date1 = new DateTime(2008, 8, 29, 19, 27, 15);

Console.WriteLine(date1.ToString("dddd dd MMMM",
CultureInfo.CreateSpecificCulture("en-US")));
// Displays Friday 29 August
Console.WriteLine(date1.ToString("dddd dd MMMM",
CultureInfo.CreateSpecificCulture("it-IT")));
// Displays venerdì 29 agosto

Have a nice day.