Database Discussion Boards

Ismael Oliveira 202119-Jun-21 18:25

Ismael Oliveira 2021

19-Jun-21 18:25

Hi, everyone.
I'm trying to recover data from SQL Server using the pivot operator. My command is:

select isnull([1], 0) as Janeiro,
       isnull([2], 0) as Fevereiro,
	   isnull([3], 0) as Março,
	   isnull([4], 0) as Abril,
	   isnull([5], 0) as Maio,
	   isnull([6], 0) as Junho,
	   isnull([7], 0) as Julho,
	   isnull([8], 0) as Agosto,
	   isnull([9], 0) as Setembro,
	   isnull([10], 0) as Outubro,
	   isnull([11], 0) as Novembro,
	   isnull([12], 0) as Dezembro
from (select month(PP.PAPA_DT_DATAPAGAMENTO) as Mesn, PP.PAPA_RE_VALORPAGAMENTO from PAGAMENTO_PARCELA PP, PAGAMENTO P
     where PP.MOVI_NA_CODIGO = P.MOVI_NA_CODIGO AND P.SUPA_NA_CODIGO = 5) Tab1
pivot (sum(PAPA_RE_VALORPAGAMENTO) for Mesn in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) P1

The way it is above it works fine. Now I need to multiply the sum by a factor, say 0.95. I tried the following:
pivot (0.95 * sum(PAPA_RE_VALORPAGAMENTO) ...
pivot (sum(0.95 * PAPA_RE_VALORPAGAMENTO) ...
0.95 * pivot (sum(PAPA_RE_VALORPAGAMENTO) ...
None of these worked. Can anyone help me? Which is the right way?
Thanks.

Re: Select with pivot

CHill6023-Jun-21 4:50

CHill60

23-Jun-21 4:50

"None of these worked" because the syntax for PIVOT[^] clearly states

Quote:
PIVOT
(
<aggregation function="">(<column being="" aggregated="">)

So just put the results of the PIVOT into a sub-query or a Common Table Expression or temporary table or a table variable, then manipulate that data. E.g.

SQL

;with cte as 
(
	select isnull([1], 0) as Janeiro,
		   isnull([2], 0) as Fevereiro,
		   isnull([3], 0) as Março,
		   isnull([4], 0) as Abril,
		   isnull([5], 0) as Maio,
		   isnull([6], 0) as Junho,
		   isnull([7], 0) as Julho,
		   isnull([8], 0) as Agosto,
		   isnull([9], 0) as Setembro,
		   isnull([10], 0) as Outubro,
		   isnull([11], 0) as Novembro,
		   isnull([12], 0) as Dezembro
	from (select month(PP.PAPA_DT_DATAPAGAMENTO) as Mesn, PP.PAPA_RE_VALORPAGAMENTO from PAGAMENTO_PARCELA PP
	inner join PAGAMENTO P on PP.MOVI_NA_CODIGO = P.MOVI_NA_CODIGO 
	where P.SUPA_NA_CODIGO = 5) Tab1
	pivot (sum(PAPA_RE_VALORPAGAMENTO) for Mesn in  ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) P1
)
select  0.95 * Janeiro,0.95 * Fevereiro,0.95 * Março,0.95 * Abril,
		0.95 * Maio,0.95 * Junho,0.95 * Julho,0.95 * Agosto,
		0.95 * Setembro,0.95 * Outubro,0.95 * Novembro,0.95 * Dezembro
from cte;

Couple of other points to note
- I've changed the JOIN to use an ON clause rather than defining the join using the WHERE clause. Your style is quite old-fashioned and prevents you from using OUTER joins
- It's a lot easier to answer questions like this if you supply the table schemas and some sample data along with your expected results. And always be specific - "None of these worked" is not helpful. "I get an error reported 'Incorrect syntax near '0.95'.' might have got you an answer quicker

Re: Select with pivot !!

User 1407655213-Jul-21 21:34

User 14076552

13-Jul-21 21:34

Hi,

as you act on a column within the select "......" statements,
you must add "group by" and the column involved.

It's from Sql engine,
in a step : it gather the columns,
in a late step : a rollback is made to apply the 0.95* operation on all selected columns.

so the "group by" recall all the 'columns' for sorting the "whole gathered" one by one.

It's really inner mechanicals SqlEngine work.

Remenber : when operation on columns in the 'select' : as "label" / maths .. , string works
add "group by" with the column required.

modified 23-Aug-21 21:01pm.

Re: Select with pivot !!

CHill6014-Jul-21 3:50

CHill60

14-Jul-21 3:50

I'm really not following this at all. Perhaps if you make the amendments you are speaking about and post that code here I might understand it better?
Or if you have a link to the specific documentation that might help?

Re: Select with pivot !!

User 1407655214-Jul-21 3:58

User 14076552

14-Jul-21 3:58

one other error I see :

from -->"tables required"<-- where column-n =<.. ( sub request ).

add as fix the table needed for the first query ( the first part )

I think you have to go on taxes early ,
insert into tax_tab (n_amount, paid) values amount=n_amount , 0.95*n_amount ;;

modified 23-Aug-21 21:01pm.

Re: Select with pivot !!

CHill6014-Jul-21 4:09

CHill60

14-Jul-21 4:09

Sorry, I still do not understand what you are saying. Please post the code here to illustrate your point.
There are no errors reported in my code - or do you think you are replying to the Original Poster?

About MySQL table permission removal failed

Member 1105472311-Jun-21 8:00

Member 11054723

11-Jun-21 8:00

Hello:

My MySQL database version is 5.6.35 and I use InnoDB.

I want to remove the Delete and Drop Table permissions of the user U1 from the table T1 in the db1 database.
The operating instructions are as follows:

<pre lang="SQL">
Revoke delete,drop on db1.T1 from U1;

No error message appeared after execution.

But after execution, the user U1 can still use the DELETE command to delete the data row in the data table T1.
You can also use the DROP TABLE command to delete the data table T1.

Thanks for replying.

Re: About MySQL table permission removal failed

Richard Deeming13-Jun-21 22:09

Richard Deeming

13-Jun-21 22:09

Most likely the user still has permission via their role membership:
MySQL :: MySQL 8.0 Reference Manual :: 6.2.10 Using Roles[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Sandwich Query For Employee Attendance using SQL Server 2005

Mohammad Salmani8-Jun-21 20:40

Mohammad Salmani

8-Jun-21 20:40

Hi,

I am looking a sandwich query for my attendance module, If the employee is absent on Saturday and Monday then sunday will be calculate as Absent the same has to be case of holidays.

The attendance table structure in SQL 2005 as per below.

EmpCode
EmpShift
AttDate
AttStatus
.
.

Please Advice/provide some solution for the above.

Thanks & Kind Regards
Mohammad Salmani

Re: Sandwich Query For Employee Attendance using SQL Server 2005

Richard MacCutchan8-Jun-21 21:55

Richard MacCutchan

8-Jun-21 21:55

Mohammad Salmani wrote:
provide some solution

Sorry, this site does not provide code to order. Google will find you many SQL tutorials that will help.

Re: Sandwich Query For Employee Attendance using SQL Server 2005

SeeSharp29-Jun-21 1:35

SeeSharp2

9-Jun-21 1:35

LEFT JOIN to a table that has all dates and find those that do not have a match.

Re: Sandwich Query For Employee Attendance using SQL Server 2005

Mohammad Salmani9-Jun-21 3:08

Mohammad Salmani

9-Jun-21 3:08

I am having the below data in Attendance Table

EmpCode AttMonth AttYear AttDate ShiftCode AttStatus
1 6 2021 2021-06-01 00:00:00.000 1001 P
1 6 2021 2021-06-02 00:00:00.000 1001 P
1 6 2021 2021-06-03 00:00:00.000 1001 P
1 6 2021 2021-06-04 00:00:00.000 1001 P
1 6 2021 2021-06-05 00:00:00.000 1002 A
1 6 2021 2021-06-06 00:00:00.000 1009 WO
1 6 2021 2021-06-07 00:00:00.000 1001 A
1 6 2021 2021-06-08 00:00:00.000 1001 P
1 6 2021 2021-06-09 00:00:00.000 1001 P

I need to update 'WO' as 'A' while processing attendance

please help for the above.

Re: Sandwich Query For Employee Attendance using SQL Server 2005

SeeSharp29-Jun-21 3:39

SeeSharp2

9-Jun-21 3:39

I can't help because I do not understand what you need help with.

Re: Sandwich Query For Employee Attendance using SQL Server 2005

David Mujica9-Jun-21 4:25

David Mujica

9-Jun-21 4:25

A few SQL functions may be of help for you ..

Take a look at this query

SQL

SELECT *,DATEPART(wk, AttDate) As 'Week Number',DATEPART(weekday, AttDate) As 'Week Day', DATENAME(WEEKDAY,ATTDATE) FROM #TEMP1

Re: Sandwich Query For Employee Attendance using SQL Server 2005

Mycroft Holmes9-Jun-21 12:36

Mycroft Holmes

9-Jun-21 12:36

I would do the as 3 queries:

1 get the first absent date date - store it in date1 variable
2 get the next absent date > date 1 - if it exists store it in date2 variable
If date2 is not null (more than 1 day)
3 set all records between date1 and date2 to absent

This covers multi day holidays.

Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP

Re: Sandwich Query For Employee Attendance using SQL Server 2005

SeeSharp210-Jun-21 1:25

SeeSharp2

10-Jun-21 1:25

But this will only work on 1 record at a time.

Re: Sandwich Query For Employee Attendance using SQL Server 2005

Mycroft Holmes10-Jun-21 12:09

Mycroft Holmes

10-Jun-21 12:09

One Employee at a time, correct. So put it in a loop through all employees.

Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP

Re: Sandwich Query For Employee Attendance using SQL Server 2005

Mohammad Salmani10-Jun-21 21:53

Mohammad Salmani

10-Jun-21 21:53

Hi,

I have implemented as per below query,If Employee is absent on Saturday and Monday the weeklyoff should be counted as Absent. I have passed the process date parameter in place of static date.

SELECT A.EmpCode,A.AttDate As Sundate FROM Attendance_Details A INNER JOIN (SELECT A.Empcode,A.AttDate As SatDate,A.AttStatus As SatStatus
FROM Attendance_Details A INNER JOIN (SELECT EmpCode,AttDate As MonDate,AttStatus As MonStatus FROM Attendance_Details
WHERE AttDate='2021-06-07' AND DATENAME(WEEKDAY, '2021-06-07') = 'Monday' and AttStatus IN ('A','HL'))T ON T.EmpCode=A.EmpCode
WHERE DATENAME(WEEKDAY, DATEADD(day,-2,'2021-06-07')) = 'Saturday' AND AttStatus IN ('A') AND AttDate=DATEADD(day,-2,'2021-06-07'))T1
ON A.EmpCode=T1.EmpCode AND DATENAME(WEEKDAY, DATEADD(day,-1,'2021-06-07')) = 'Sunday' AND AttStatus NOT IN('AL','SL','PL','ML','FL') AND AttDate=DATEADD(day,-1,'2021-06-07')

Please help to improve the above query...

Thanks & Regards
Mohammad Salmani

Re: Sandwich Query For Employee Attendance using SQL Server 2005

SeeSharp211-Jun-21 1:20

SeeSharp2

11-Jun-21 1:20

Mycroft Holmes wrote:
loop

Loops are a last resort in databases. They are built to work on sets.

Load JSON file from network location

Member 144746073-Jun-21 7:36

Member 14474607

3-Jun-21 7:36

Hello,
I am trying to load a json file from network location into a table on sql server 2016. The domain account has access to both the file on network as well as the database.
I keep receiving this message:

Cannot bulk load because the file "\\<networklocation>\loaddata.json" could not be opened. Operating system error code 5(Access is denied.).

In the above error message, does the user not have access to file or to perform the "bulk load" action?

Much appreciated!

Re: Load JSON file from network location

Member 144746073-Jun-21 8:42

Member 14474607

3-Jun-21 8:42

Also, I am using Windows authentication for SQL server, and this same user also has access to the share on network.

Re: Load JSON file from network location

Mycroft Holmes3-Jun-21 12:15

Mycroft Holmes

3-Jun-21 12:15

I think it means that the SQL Server identity (not the user) does not have access to the network drive. I always move the file to the SQL Server server or loaded it via c#.

Loading via c# I would put into a datatable EXACTLY matching the destination table and use BULKCOPY [^] to insert it into the destination

Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP

SQL Server 2010, smalldatetime insert

jkirkerx1-Jun-21 14:52

jkirkerx

1-Jun-21 14:52

I'll swear I have this right
The field is smalldatetime. I'm just picking up the record and inserting it.

On select, I used

CONVERT(varchar(33), ADD_DATE, 121) AS ADD_DATE,

Oninsert, the sql looks like this

'1900-01-01 00:00:00.000'

I read up on sql date times and the different formats. If I pick it raw, and try o insert it, I get an object error.
In SQL Manager, I get

Conversion failed when converting character string to smalldatetime data type.

Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, bool given in C:\App\Dev\PCAD\repository\projectOrder.repository.php on line 1182
Array ( [0] => Array ( [0] => 22007 [SQLSTATE] => 22007 [1] => 295 [code] => 295 [2] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Conversion failed when converting character string to smalldatetime data type. [message] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Conversion failed when converting character string to smalldatetime data type. ) ) addProjectCost 1749 INSERT INTO [proj_cost] ( Proj_ID, Proj_Stage, Vers_ID, Task_ID, Task_Oper, Task_Class, Vend_ID, CO_Proj_Type, CO_Type, CO_Type_D, CO_Value, Cost, ADD_NO, ADD_DATE, ADD_CODE, Pref_Vendor, User_Last_Upd, Delete_Flag ) VALUES ( '2549', 'engineering', '0', 'TK_AT_001', '85', 'OP', '', '39', 'PoolSpa', 'REF_AT_002', '', '25', '175', '0', '1900-01-01 00:00:00.000', '', 'jimk', 0 ) - 1

If it ain't broke don't fix it
Discover my world at jkirkerx.com

Re: SQL Server 2010, smalldatetime insert

Mycroft Holmes1-Jun-21 21:31

Mycroft Holmes

1-Jun-21 21:31

That is why you used parameterized queries instead of strings.

Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP

Re: SQL Server 2010, smalldatetime insert

Victor Nijegorodov1-Jun-21 21:49

Victor Nijegorodov

1-Jun-21 21:49

jkirkerx wrote:
INSERT INTO [proj_cost] ( Proj_ID, Proj_Stage, Vers_ID, Task_ID, Task_Oper, Task_Class, Vend_ID, CO_Proj_Type, CO_Type, CO_Type_D, CO_Value, Cost, ADD_NO, ADD_DATE, ADD_CODE, Pref_Vendor, User_Last_Upd, Delete_Flag ) VALUES ( '2549', 'engineering', '0', 'TK_AT_001', '85', 'OP', '', '39', 'PoolSpa', 'REF_AT_002', '', '25', '175', '0', '1900-01-01 00:00:00.000', '', 'jimk', 0 )

I guess the VALUES list does not correspond the fields list, so the value form ADD_DATE seems to be '0' rather than '1900-01-01 00:00:00.000'.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.