Database

Re: Sandwich Query For Employee Attendance using SQL Server 2005

9-Jun-21 1:35

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

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

Re: Sandwich Query For Employee Attendance using SQL Server 2005

9-Jun-21 3:39

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

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

Re: Sandwich Query For Employee Attendance using SQL Server 2005

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

SeeSharp210-Jun-21 1:25

Re: Sandwich Query For Employee Attendance using SQL Server 2005

10-Jun-21 1:25

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

Mycroft Holmes10-Jun-21 12:09

Re: Sandwich Query For Employee Attendance using SQL Server 2005

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

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

Load JSON file from network location

11-Jun-21 1:20

Mycroft Holmes wrote:
loop

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

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

SQL Server 2010, smalldatetime insert

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

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

Re: SQL Server 2010, smalldatetime insert

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

Victor Nijegorodov1-Jun-21 21:49

Re: SQL Server 2010, smalldatetime insert

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

jkirkerx2-Jun-21 8:47

jkirkerx

2-Jun-21 8:47

I thought about what you both said, and would like to say thanks and show gratitude as well.

I went back and keep the Select statement raw, took the convert out.
The dumped my CRUD function to add the record, and converted the calling function to SQL.
Then used PHP to convert the raw date object I picked up to a string, and declared it in SQL.

I suppose I could go back to my add CRUD function and do the conversion there instead

$result2 = sqlsrv_query($conn, $query2) or die(" setDbProjectCost " . LINE . " - " . $query2 . " - " . print_r(sqlsrv_errors()));
if (sqlsrv_has_rows($result2)) {
    $row2 = sqlsrv_fetch_array($result2);
    $addDateString = $row2[13]->format('Y-m-d H:i:s') . '.000';

    $query3 = "
    DECLARE @addDate AS VARCHAR(33) = '$addDateString';
    INSERT INTO [proj_cost] 
    VALUES ... @addDate

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

Get columns name

_Flaviu27-May-21 23:55

27-May-21 23:55

If I run a SQL statement, like this:

SQL

SELECT * FROM my_table

is there any SQL statement / trick to find the name of every column from '*' ?

Because if I write:

SQL

SELECT id, name, age FROM my_table

yes, I could extract the columns name, by text analyze. But how about '*' case ?

Victor Nijegorodov28-May-21 0:22

How can I get column names from a table in SQL Server? - Stack Overflow
sql - How can I get column names from a table in Oracle? - Stack Overflow
information schema - How to find all the tables in MySQL with specific column names in them? - Stack Overflow
postgresql - List all columns for a specified table - Database Administrators Stack Exchange

28-May-21 0:22

_Flaviu3-Jun-21 5:57

3-Jun-21 5:57

Yes, I know that, but the question is, how can I know the columns name for the following SELECT (programmatically):

SQL

SELECT * FROM table1, table 2 WHERE table1.id = table2.id;

modified 3-Jun-21 12:17pm.

Victor Nijegorodov3-Jun-21 20:13

3-Jun-21 20:13

I'd just do it the same way as for a single table:
- obtain column names for table1;
- obtain column names for table2;
- ...

_Flaviu3-Jun-21 20:37

3-Jun-21 20:37

So, you are saying that I need to do a static analyze on SELECT text and see what columns I have, if I enumerate the columns in the SELECT statement, then I have it, if I don't (I have '*') then I have to query the tables names / columns name and will find them. I thought there is a SQL trick to find the columns name in any circumstances.

Victor Nijegorodov3-Jun-21 21:48

3-Jun-21 21:48

I'd implement such a "trick" in a stored procedure that gets a query as an argument, then creates the temporary View, uses

SQL

SELECT [TABLE_NAME]
      ,[COLUMN_NAME]
  FROM [INFORMATION_SCHEMA].[COLUMNS]
  where TABLE_NAME = <your_temp_view>

to return the recordset with the tables/columns names,
then delete the temp view.

PS: this should work with SQL Server 2008 and above.

_Flaviu3-Jun-21 23:33

3-Jun-21 23:33

Sound good ! Because this solution should be cross server platform, it is available on the other SQL servers, like Oracle, Informix, MySQL, and so on ?

Victor Nijegorodov4-Jun-21 0:55

4-Jun-21 0:55

You have to test it yourself! Just check out the links I gave you here