Print days of the month in the table

Asked

Viewed 57 times

1

I am trying to create a report based on some tables of my bank, in this report, I need to pass during the month, on which day the operation was executed, so I need to print out every day of the month, and compare them with my field of dataExecutada.

this is my current table

Tabela incompleta

This is the expected report:

Relatório esperado

Of the fields in this report I just don’t have the dates of the month

All that’s left is to spend the days of the month and compare it to mine initDate.

How can I get this kind of information in my query? Follow my current code:

SELECT DISTINCT vapacasset,
                awp.workprocedure,
                wp.workprocedurename,
                awp.intervaltype,
                assetwp.initdate,
                CONVERT(VARCHAR, assetwp.dateinterval) + CONVERT(VARCHAR(1), CASE
                                                                                          WHEN assetwp.intervaltype = '2' THEN 'M'
                                                                                          ELSE 'D'
                                                                                        END) AS PERIOD
  FROM asset a
 INNER JOIN assetworkprocedure awp
    ON a.asset = awp.asset)
 INNER JOIN workprocedure wp
    ON awp.workprocedure = wp.workprocedure)
 INNER JOIN v_assetparentassetcl vapac
    ON awp.asset = vapacasset
 WHERE vapacparentasset = 'F.SP.SPO.IP'
   AND awp.recordstate = 'OP'

[2]: https://i.stack.imgur.com/rdaYM.png

sql sql-server

  • Related: Split date ranges into weeks.

  • Related: How to allocate transactions or group by day?.

  • I need to pass the same table is in the second photo, select every day of the month (this will come as my backend parameter), and compare the day that is compatible with initDate of Asset, which is already being selected in the current query

  • @Sorack did the requested edits

  • Has any response helped solve the problem and can address similar questions from other users? If so, make sure to mark the answer as accepted. To do this just click on the left side of it (below the indicator of up and down votes).

1 answer

1

The best way to do what you want would be directly in the programming language that is generating the report. But in the bank you can create a function that generates a record for each day of the interval you inform and return it in a column of your query:

IF OBJECT_ID('imprimir_intervalo', 'FN') IS NULL
BEGIN
  EXEC('CREATE FUNCTION imprimir_intervalo() RETURNS INT AS BEGIN RETURN 1 END');
END;
GO

ALTER FUNCTION imprimir_intervalo(@inicial DATE,
                                  @dias    INT)
RETURNS VARCHAR(MAX)
AS
BEGIN
  DECLARE @texto VARCHAR(MAX);

  WITH dias AS (
      SELECT @inicial AS dia,
           1 AS quantidade
       UNION ALL
      SELECT DATEADD(DAY, 1, dia),
           quantidade + 1 AS quantidade
          FROM dias
       WHERE quantidade < @dias
    )
  SELECT @texto = ISNULL(@texto + ', ', '') + CAST(DATEPART(DAY, d.dia) AS VARCHAR)
    FROM dias d;

  RETURN @texto;
END;
GO

An example execution:

SELECT dbo.imprimir_intervalo(getdate(), 2)

Which results in:

13, 14

Apply in your business rule as you need.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.