SQL server – Reformatting date with pivot or unlocking

I'm having trouble converting the format of a table with the following fields:

Customer | Fiscal year | Amount of September | Amount of October | Amount of November | …

at:

Customer | Fiscal year | Month | Amount

I tried pivot and unpivot but I did not succeed.

Thank you!

Carrying over a row in BigQuery SQL

I have data this way

Column1 Column2  Date
d1        b1    1/1/2020
d1        b2    1/1/2020
d1        b3    1/1/2020
d2        b2    1/2/2020
d1        b3    1/2/2020
d1        b4    1/2/2020
d1        b1    1/3/2020
d2        b3    1/3/2020
d2        b3    1/4/2020

The grain of the above data is Col1, Col2
For all that grain, if the grain level data doesn't come the next day, I have to repeat the day before.
Which means that if I execute the toady request and today is 1/4/2020, the expected output is as follows

Column1 Column2  Date     Status
d1        b1    1/1/2020  FromInput
d1        b1    1/2/2020  Repeated
d1        b1    1/3/2020  FromInput
d1        b1    1/4/2020  FromInput

d1        b2    1/1/2020  FromInput
d1        b2    1/2/2020  Repeated
d1        b2    1/3/2020  Repeated
d1        b2    1/4/2020  Repeated

d1        b3    1/1/2020  FromInput
d1        b3    1/2/2020  FromInput
d1        b3    1/3/2020  Repeated
d1        b3    1/4/2020  Repeated

d1        b4    1/2/2020  FromInput
d1        b4    1/3/2020  Repeated
d1        b4    1/4/2020  Repeated

d2        b2    1/2/2020  FromInput
d2        b2    1/3/2020  Repeated 
d2        b2    1/4/2020  Repeated

d2        b3    1/3/2020  FromInput
d2        b3    1/4/2020  FromInput

SQL – Galera with MariaDB master-master to support writing when disconnected

I am trying a distributed application using the Galera replication topology which must support writes if the nodes are disconnected from the cluster. The idea was to have an additional MariaDB that is connected to each Galera node via master-master replication:

dbc0 <--> db0 <--> app
 |
dbc1 <--> db1 <--> app
 .
 .
 .
dbcn <--> dbn <--> app

When vertical replication between databases is Galera and needs to support hundreds of nodes and WAN. Horizontal replication is MariaDB master-master within the same linux server but between different lxc containers. The idea is that the application would only communicate with non-Galera databases, and the writes would have keys containing the node #, so they would be guaranteed unique. Synchronization between nodes can be slow, but the application (in another container) must interact quickly with its local database.

After setting it up with server_id & # 39; s identical on dbc and unique on db, and log_slave_updates = 1 on dbc, it almost works: creating a new table on any which dbc creates the tables in all the dbc and db. Adding a line in any dbc also appears everywhere.

Adding a row to a database replicates successfully across all databases, but does not reach other databases. However, this topology allows writes to a database when its database is deactivated and synchronizes correctly when the database is reactivated on all other databases (but not the databases). I hope this makes sense.

Is there a way to rewrite an entry in a db node via the dbc nodes of the Galera cluster and their corresponding MariaDB master-master replicated databases? Or any other way to accomplish this?

SQL Server 2012 – Common Table Expression (CTE) returning more rows than it should

I have a life table with 121 rows and 3 columns (age, male and female). I have to go get the 121 lines taking gender into account. If the sex is "Male", I get the "Male" column from the life table as qx and the "Female" column as qy and vice versa.

However, I need to calculate a fourth column, vx, whose value starts with 1 and the following values ​​are calculated as follows, using the previous value of vx itself:

vx = (vx / (1 - @interestRate)^age)

The age always goes from 0 to 120, incremented by one at each step.

I do not know CTE well, therefore, I take baby steps to accomplish my tasks with this new method for me. and I need help.

When I try to calculate vx, the query returns 7381 instead of 121.

Here is my CTE:

DECLARE @gender VARCHAR(10) = 'MALE'
DECLARE @interestRate NUMERIC(7, 5) = 0.0354

;WITH cte AS (
    SELECT
        lt.Age,
        CASE
            WHEN @gender = 'MALE' THEN
                lt.Male
            ELSE
                lt.Female
        END AS qx,
        CASE
            WHEN @gender = 'MALE' THEN
                lt.Female
            ELSE
                lt.Male
        END AS qy,
        CAST(1.0 AS NUMERIC(7, 5)) AS vx
    FROM LifeTable AS lt

    UNION ALL

    SELECT
        cte.Age + 1,
        cte.qx,
        cte.qy,
        CAST((vx / (1 + @interestRate)) AS NUMERIC(7, 5)) AS vx
    FROM cte

    WHERE cte.Age < 120

) SELECT COUNT(*) AS NumberOfRows FROM cte OPTION (MAXRECURSION 0);

I am a little tired because I spent my afternoon trying to solve this problem, it is already dark, so my question can be considered a reduced effort, but I really need your help to see where I'm making mistakes.

sql server – Quick question about Scalar UDF

I am trying to browse online but I can't get enough information on how to measure the performance of scalar UDFs in SQL Server 2014.

Newer version yes I see plans we can get the same and more recent dmv

But I have on my SQL 2014 where a bunch of scalar uDFs exist, including one that includes a function involving computation and functions calling basic or user tables.

I see blocking and SP performance and seem to have few calls using these scalar functions. But couldn't find a way to prove how bad these functions could be when calling from so many affected SPs

please help

SQL server – Drop DB via SQSH

I want to delete a remote SQL Server database using SQSH. I tried two ways;

the first is to take the database offline since automatic change is disabled.

sqsh -h -S databaseHost -D master -U USER -P PASS -C "ALTER DATABASE ${TARGETDB} set OFFLINE WITH ROLLBACK IMMEDIATE; DROP DATABASE ${TARGETDB}"

The result

Error: 615, Severity: 21, State: 1. Could not find database ID 9356, name '9356'

The second way

sqsh -h -S databaseHost -D master -U USER -P PASS -C  "ALTER DATABASE ${TARGETDB} set SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE ; DROP DATABASE ${TARGETDB}"

It works well, but if no one else connects / tries to connect to the database, if it does, it results.

The database is in single-user mode, and a user is currently connected to it. 

The second way should work fine because I have defined it on a single user and dropped it in the same connection, or it's supposed to be the same connection, an idea of ​​how to fix this problem?

SQL Server – SQLServer Format Date to YYYYMMDD

Please your help.

I have the following table DATE_REG SqlServer.

ID | DATED
1 | 20180424020549
2 | 20180424020500
3 | 20180424080010

SELECT TRXSF.iD, TRXSF.DATE FROM [DIAGRAM]. [Dbo]. [DATE_REG] AS TRXSF;

Expected result:

ID | DATED
1 | 20180424
2 | 20180424
3 | 20180424

I have to delete the hour, minute and second.

SQL server – SQL Always On – database is not accessible after failover

Always On configuration with 2 Azure SQL and Cloud Witness virtual machines.

I connect to the SQL instance via Listener and initiate the failover to the second server.

After the switchover, I cannot access the database:

enter description of image here

The dashboard shows:
enter description of image here

SELECT *from sys.dm_hadr_availability_replica_states in high school
enter description of image here

The primary server does not see the secondary operational status:
enter description of image here

I tried to run on both servers without any luck

GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITYSYSTEM];
GRANT CONNECT SQL TO [NT AUTHORITYSYSTEM];
GRANT VIEW SERVER STATE TO [NT AUTHORITYSYSTEM];

No significant errors in the event logs and everything seems green in the cluster, what might not be the case?

Return records based on percentage on SQL server

Good night
I have to make a selection that only returns records that have been paid more than 60% of the installments.

I have created a table with the following structure:

CREATE TABLE PARCELAS(
IDPARCELAS INT IDENTITY(1,1) PRIMARY KEY,
IDFINANCIAMENTO INT FOREIGN KEY REFERENCES FINANCIAMENTO(IDFINANCIAMENTO),
NUMERO INT NOT NULL,
VALOR_PARCELA DECIMAL(18,2) NOT NULL,
DATA_VENCIMENTO DATE NOT NULL,
DATA_PAGAMENTO DATE 
);

Select the result:

1 213 2 1 2333.33 2019-07-25 2019-07-25
2 214 2 2 2333.33 2019-08-25 2019-08-25
3 215 2 3 2333.33 2019-09-25 2019-09-25
4 216 2 4 2333.33 2019-10-25 2019-10-25
5 217 2 5 2333.33 2019-11-25 2019-11-25
6 218 2 6 2333.33 2019-12-25 2019-12-25
7 219 2 7 2333.33 2020-01-25 2020-01-25
8 220 2 8 2333.33 2020-02-25 2020-02-25
9 221 2 9 2333.33 2020-03-25 2020-03-25
10 222 2 10 2333.33 2020-04-25 2020-04-25
11 223 2 11 2333.33 2020-05-25 2020-05-25
12 224 2 12 2333.33 2020-06-25 2020-06-25
13 225 2 13 2333.33 2020-07-25 2020-07-25
14 226 2 14 2333.33 2020-08-25 2020-08-25
15 227 2 15 2333.33 2020-09-25 2020-09-25
16 228 3 1 3000.00 2019-07-25 NULL
17 229 3 2 3000.00 2019-08-25 NULL
18 230 3 3 3000.00 2019-09-25 NULL
19 231 3 4 3000.00 2019-10-25 NULL
20 232 3 5 3000.00 2019-11-25 NULL
21 233 3 6 3000.00 2019-12-25 NULL
22 234 3 7 3000.00 2020-01-25 NULL
23 235 3 8 3000.00 2020-02-25 NULL
24 236 3 9 3000.00 2020-03-25 NULL
25 237 3 10 3000.00 2020-04-25 NULL
26 238 3 11 3000.00 2020-05-25 NULL
27 239 3 12 3000.00 2020-06-25 NULL
28 240 3 13 3000.00 2020-07-25 NULL
29 241 3 14 3000.00 2020-08-25 NULL
30 242 3 15 3000.00 2020-09-25 NULL
31 243 4 1 2200.00 2020-04-25 1900-01-01
32 244 4 2 2200.00 2020-05-25 1900-01-01
33 245 4 3 2200.00 2020-06-25 1900-01-01
34 246 4 4 2200.00 2020-07-25 1900-01-01
35 247 4 5 2200.00 2020-08-25 1900-01-01
36 248 4 6 2200.00 2020-09-25 1900-01-01
37 249 4 7 2200.00 2020-10-25 1900-01-01
38 250 4 8 2200.00 2020-11-25 1900-01-01
39 251 4 9 2200.00 2020-12-25 1900-01-01
40 252 4 10 2200.00 2021-01-25 1900-01-01
41 253 4 11 2200.00 2021-02-25 1900-01-01
42 254 4 12 2200.00 2021-03-25 1900-01-01
43 255 4 13 2200.00 2021-04-25 1900-01-01
44 256 4 14 2200.00 2021-05-25 1900-01-01
45 257 4 15 2200.00 2021-06-25 1900-01-01
46 258 5 1 1266.66 2020-01-25 2020-01-25
47 259 5 2 1266.66 2020-02-25 2020-02-25
48 260 5 3 1266.66 2020-03-25 2020-03-25
49 261 5 4 1266.66 2020-04-25 NULL
50 262 5 5 1266.66 2020-05-25 NULL
51 263 5 6 1266.66 2020-06-25 NULL
52 264 5 7 1266.66 2020-07-25 NULL
53 265 5 8 1266.66 2020-08-25 NULL
54 266 5 9 1266.66 2020-09-25 NULL
55 267 5 10 1266.66 2019-07-25 NULL
56 268 5 11 1266.66 2019-08-25 NULL
57 269 5 12 1266.66 2019-09-25 NULL
58 270 5 13 1266.66 2019-10-25 NULL
59 271 5 14 1266.66 2019-11-25 NULL
60 272 5 15 1266.66 2019-12-25 NULL

Where in this table there are 60 records. I only need to bring the records where the 3rd column is 2 and 4, as more than 60% of the installments have been paid on them. A part that has not been paid on the NULL payment date, how can I do this with select without using procedures.

Thanks in advance.

SQL Server 2017 – Analysis of column types of columns used in joins

Is it possible to do a deep type analysis based on source code, views and stored procedures in order to identify column joins that have no compatible types?

I inherited a database which was not consistent in the use of varchar vs nvarchar columns. And sometimes numeric columns are joined to text fields that contain numbers. Now there are changes that bring more tables to the database with millions of records and any difference in the joined types can have huge performance impacts. These tables have varchar fields and it is not possible to change them to nvarchar (they are populated by a third-party product which does not support varchar).

The options I have:

  1. Convert all varchar columns to nvarchar. Unfortunately, this is not possible. It would have been my favorite option.
    1.b Convert all nvarchar columns to varchar. Hmmm … I think it's a rabbit I probably don't want to get into, although all of the textual data is supposed to be in English.
  2. Use the MS Transact SQL analyzer (DacFx) to analyze the queries, however, I think it will take a significant programming effort. I would need a full list of all the SQL Server functions and the types they return.

Any other ideas?

Thank you