CREATE TABLE [dbo].[multiple](
[emp_code] [varchar](20) NULL,
[swip_in] [nvarchar](255) NULL,
[swipe_in_date] [nvarchar](255) NULL,
[swipe_out] [nvarchar](255) NULL,
[Swipe_out_date] [nvarchar](255) NULL
[Code] ....
I want result like below:
Empcode swip_in1, swip_out1, swip_in2, swip_out2, swip_in3, swip_out3
Condition :
1. 24 hrs who are multiple time punched
I want to get the employee hierarchy like tree structure and reportes.
View 7 Replies View Related<g class="gr_ gr_54 gr-alert gr_gramm Grammar multiReplace" data-gr-id="54" id="54">I want</g> to get row number of last inserted into employee table?
How can i get it @@identity function returns null.
is there any way to do it?
I'm working on a project where I need to retrieve employees data and then combine the data into single row per employee.
Sample Data:
WITH SampleData (PERSON, [DATA], [FIELD]) AS
(
SELECT 1234,'04/02/2014','Date'
UNION ALL SELECT 1234,'123','Department'
UNION ALL SELECT 1234,80.0,'Rate'
)
SELECT *
FROM SampleData;
The results from the above are as follows:
PERSONDATA FIELD
123404/02/2014Date
1234123 Department
123480.0 Rate
The desired results would be:
PERSONDate Department Rate
123404/02/2014 123 80.0
Hello Everyone,
I am bit confused.
I am using vwd2005 express,c# and sql express.
I have a webform that registers new employee(title,name,age,address,phone,email) and inserts those data into the sqldatabase.
The problem is there might exists 'N' number or email ids and phone nos for a single user.
for eg: user A might have 3 email address where has user B might have 5 email address.
so in that case its not appropriate to create 5 textboxes in the webform and create 5 column like email1,email2,email3,email4,email5 in the database for both users..
i hope your getting the point.
I tried to create a separate table for emails and phone.
But i am new to relational database.
So if i need to use Relational database then could anyone help me here to :-
1.create a table structure here.(what would be the structure of tbl_employee, tbl_email and tbl_phone)
2.How to write insert query if i am using RD here?
plz help explaining the concept with a simple running example.
Thanks in advance.
Jack.
Hi, I have been struggling trying to design a query that will alow be to select the most recent date in a table
and I'm obviously not having much luck
This is basically the table layout, note each employee can have multiple rows with different dates
Employee_ID
Last_Name
First_Name
Evaluation_Date
Evaluation_Score
1
Jones
Tom
01/04/07
40
1
Jones
Tom
01/.12/07
50
1
Jones
Tom
04/01/08
60
2
Smith
Ed
02/14/05
70
2
Smith
Ed
03/18/06
80
3
Brown
John
06/23/04
80
3
Brown
John
12/23/04
79
3
Brown
John
01/07/06
50
3
Brown
John
10/22/08
69
What I'd like to do would be to write some thing that would return the following, just the last date of the evaluation & whatever relevant data is in the table
Employee_ID
Last_Name
First_Name
Evaluation_Date
Evaluation_Score
1
Jones
Tom
04/01/08
60
2
Smith
Ed
03/18/06
80
3
Brown
John
10/22/08
69
I've looked at select distinct and the date operatives with out any success.
Thanks Much
Vince
I run into a problem when asking to show a query of employee vacation days.
table 1:
column1 is dates
e.g.
2015-01-01
2015-01-02
2015-01-03
.
.
.
2015-12-31
table2:
employeeID
vacation_date
Tom
2015-01-03
Tom
2015-01-04
David
2015-01-04
John
2015-01-08
Mary
2015-01-012
My query output need to be:
2015-01-01
2015-01-02
2015-1-03
Tom
2015-01-04
Tom
2015-01-04
David
2015-01-05
2015-01-06
2015-01-07
2015-01-08
John
2015-01-09
2015-01-10
2015-01-11
2015-11-12
Mary
... etc... all the way to 2015-12-31
when i use left outer join, i only record one employee per date.
I need formulating a view through which I can create a an output like below image -
Monthly Table
NUM STATUS ACTIVITYCODE
HAX603 Completed 0x45845a
HAX317 Completed 0x112z44
HAX465 Completed 0x1155x4
HAX523 Completed 0x124c69
Season Table
NUM STARTMONTH STARTDAY ENDMONTH EMDDAY
HAX603 JULY 1 OCTOBER 31
HAX317 DECEMBER 1 DECEMBER 31
HAX317 MARCH 1 MARCH 31
HAX317 July 1 July 28
[Code] ...
Final Output
NUM STATUS ACTIVITYCODE <SEASONS>
HAX603 Completed 0x45845a 1 JULY - 31 OCTOBER, 1 DECEMBER - 31 DECEMBER
HAX317 Completed 0x112z44 1 DECEMBER - 31 DECEMBER, 1 MARCH - 31 MARCH, 1 July - 30 July
HAX465 Completed 0x1155x4 1 MARCH - 31 MARCH, 1 July - 28 July, 1 August - 30 August
HAX523 Completed 0x124c69 1 November - 30 November
I have written a query to join the values of multiple field, but lacking in as how will I formulate a view which will check for the duplicate values of Num fields and merge there values in a single field like season.
select num, (CAST(startday AS VARCHAR(3)) + ' ' + startmonth + ' - ' + CAST(endday AS VARCHAR(3)) + ' ' + endmonth)AS Season from seasons;
I need to return a computed value and have no idea how to do it.
I have a table of dates:
Column DataType
RowID int
Person nvarchar(20)
Percent real
Date smalldatetime
What I need is the Annual Fiscal Year Average. The fiscal year starts and ends in June. I need it to add all Percent values between the months of June 2001 - June 2002, June 2002 - June 2003, and so on and on....
A result set like this...
Person - FiscalYear - FiscalYearResult
John D - 2001 - .58
John D - 2002 - .52
John D - 2003 - .50
Jane D - 2001 - .58
Jane D - 2002 - .52
Jane D - 2003 - .50
so on and so on...
How do I write this in Transact-SQL to get this result set...
Thanks,
Roger
I have a table which table has :Identity Column (Identity), Schema name and Table_Name.
So I would like to write a cursor For each Table Count(*) from Table--@Cnt Int , Schemaname and Tablename need to store another table.
Implement a USP, using a cursor that scan the table, generate select count statement from configuration table and fire the select count statement and record the result of the query in the log table :
how can I write a cursor and Import Those results into to Another table.
Is it possible to write query from different database engines?
One database A is in local server database engine and another database B is in another server located in different system.
Now , I need to write a query in database A based on table A and table B (another database engine).
We are not allowed to write delete statement in a function. It will give the below error.
"Invalid use of a side-effecting operator 'DELETE' within a function."
We will get the same error for truncate/drop statements.
What is the reason behind this error?
I'm trying to insert records into "holding" table and write back identity column value (Entry_Key) to the original table. So my setup is I have two tables; tblEWPBulk and tbleFormsUploadEWP. Users will enter records into tblEWPBulk and use BatchID to group records, once batch entry has been completed (usually less than 30 records) user will click on UploadAll button and insert records (not all fields) into tbleFormsUploadEWP. One record in tblEWPBulk can be sent multiple times to the holding table but tblEWPBulk will need to have latest Entry_Key captured. Records are sent from holding table to DB2 z/VSE using SQL stored procedure and based on certain logic records are marked uploaded or certain error capture... that part works fine.
So for example I want to send
BatchID, AccountNumber, Period, ReceiveDate, AccountType, ReturnType, NetProfitOrLoss, TaxCredit FROM tblEWPBulk to the holding table and write back Entry_Key (identity column) back to the record in tblEWPBulk (field called UploadEntryKey). As I said one record could be sent to the holding table multiple times until uploaded or deleted and UploadEntryKey always needs to be updated so that when results are processed response from the DB2 can be inserted into table and presented to the user.
No foreign key relationship exists since records in the holding table get sent to the archive table and table is truncated and entry_key starting value reset back to 2000... just some DB2 restrictions.
I am having table on which i want to fire a query on.
table structure is as follows :
Table1
Table1ID bigint PK
City nvarchar(10)
FirstName nvarchar(10)
LastName nvarchar(10)
MiddleName nvarchar(10)
State nvarchar(10)
FirstName nvarchar(10)
LastName nvarchar(10)
LLDCode nvarchar(20)
MMDCode nvarchar(20)
LastModified datetime
CreatedDateTime datetime
LastUpdatedDateTime datetime
SSN# nvarchar(20)
I have to write a parameterised stored procedure where multiple values will be pass as input to SP.
E:g: 1) SSN# if no record found by SSN# then 2) Find by LLDCode OR MMDCode
This logic can be implemented using UNION to join output of 2 queries and selecting by LastUpdatedDateTime this query will return the Table1ID.
Now we will fire the query on table2 where this Table1ID as FK in Table2 and we will return the filed SSXML from Table2.How to do this ?
Table2 structure.
Table2ID PK
Table1ID FK
Name
Department
SSXML
I have a dataset which is like:
Month, Day, Location, TotalSales
Jan 1 A 100
Jan 1 B 200
Jan 14 A 120
Feb 2 A 130
Mar 5 B 150
I want to transform the dataset using sql query into the following format:
Month, Day, LocationATotalSales, LocationBTotalSales, TotalSales
Jan 1 100 200 300
Jan 14 120 0 120
Feb 2 130 0 130
Mar 5 0 150 150
I have a table with single row like below
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0 | Value1 | Value2 | Value3 | Value4 |
Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below
_ _ _ _ _ _ _ _
Column0 | Value0
_ _ _ _ _ _ _ _
Column1 | Value1
_ _ _ _ _ _ _ _
Column2 | Value2
_ _ _ _ _ _ _ _
Column3 | Value3
_ _ _ _ _ _ _ _
Column4 | Value4
_ _ _ _ _ _ _ _
I want to use one stored procedure to delete rows in three different tables - how do I write this for Sql Server 2000 please?
PROCEDURE dbo.DeleteSubmission @C_ID intASDELETE *FROM tblCompaniesWHERE C_ID = @C_ID
DELETE *FROM tblStoresWHERE CS_ID = @C_ID
DELETE *FROM tblDistributorsWHERE CD_ID = @C_ID
RETURN
so, I am trying to write an sql that requires information across two
different database. It's under the same sql server. However, the
location of the tables are from different database.
Any links I can read about that can show me how to write the queries?
Thanks in advance, and merry X'mas.
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary',
@profile_name =
'SQL SMTP',
[code]....
I have a table called employee_punch_record that we use to store employee time clock punches.
The columns are:
employeeid,
punch_timestamp,
punch_type (In / Out),
closed (bit used as status for open or closed pay periods),
ident
Here are some examples of a record:
bkingery62015-10-06 16:59:04.000In0
bkingery72015-10-06 16:59:09.000Out0
bkingery82015-10-06 16:59:13.000In0
bkingery92015-10-06 18:22:44.000Out0
bkingery102015-10-06 18:22:46.000In0
bkingery112015-10-06 18:22:48.000Out0
bkingery122015-10-06 18:22:51.000In0
tfeller52015-10-05 17:00:05.000In0
We are using SQL Server 2008 as our database and use Access as a GUI. I am looking to create a form in Access where employees can access their time card and request changes from management. I want to use the format from the attached screen shot for the form. I pretty much know how to do it all, the only point of complication is trying to figure out the easiest way to get the transaction punch record data on employee_punch_record into a format where I can easily populate the form in the horizontal format you see in the screen shot.
I am not super strong in SQL, but figure I can do it using a formatting table of some sort. quick and easy way to move transaction records into a more horizontally oriented record?
I have the following database structure
Stock Depth41 Depth12 Depth34
AAA 1 2 1
BBB 2 2 4
How can I show Each Depth column as seperate row
AAA 1
AAA 2
AAA 1 as follows
I am trying to use FOR XML under SQL Server 2014 to write out a large XML data set. I want it to look like
<CVS_Member_Add_Change>
<RecordType>3</RecordType>
<Carrier>1266</Carrier>
<MultiBirthCode>0000000</MultiBirthCode>
<MemberType></MemberType>
[Code] ....
That's how it looks when you click on the results of a small subset of the query. Just what I want. Unfortunately when you try to right click and save it you get
<dataroot><CVS_Member_Add_Change><RecordType>3</RecordType><Carrier>1266</Carrier<MultiBirthCode>0000000</MultiBirthCode><MemberType></MemberType<LanguageCode>1</LanguageCode><DURFlag></DURFlag><DURKey></DURKey><SocialSecurityNumber>000000000</SocialSecurityNumber</CVS_Member_Add_Change>
Everything being on one line blows up the translator application that reads the data.
The FOR XML statement copied out of the query is below.
FOR XML RAW ('CVS_Member_Add_Change'), ROOT('dataroot'), ELEMENTS
GO
Is there a way in the T-SQL to force it to break lines neatly?
Is there a way to force it to a specific file name or directory?
I'm trying to code a query in Access that finds rows w/ duplicate "ContactKeys" then finds duplicate "AddressLine1s" out of the list of duplicate "ContactKeys." (I tried subqueries but it was really slow)
I am trying to create a new table with only duplicate ContactKey rows, and then I wanted to use that table to pick out the duplicate AddressLine1 rows.
Code:
SELECT *
INTO dupContactKeys
FROM Contacts
WHERE ContactKey IN (
SELECT ContactKey
FROM Contacts
GROUP BY ContactKey
HAVING COUNT(*) > 1)
SELECT *
FROM dupContactKeys
WHERE ContactKey IN (
SELECT AddressLine1, Zip
FROM Contacts
GROUP BY AddressLine1, Zip
HAVING COUNT(*) > 1)
ORDER BY ContactKey, TypeKey;
drop table dupContactKeys
This of course doesn't work. Please help, as I am going slightly mad!
I have to do following scenario,
if 1st query Then 2nd Query
Else 'Msg'
How Can i do this using Case Statement??how can do this by Other way??
I have several database files, all in the same directory. I want to write SQL statements that access two databases at once. For example, if only one database would be involved, I would write:
SELECT Test_Source_ID_column, Data_column
INTO Test_Destination_table
FROM Test_Sources_table
The above query copies two columns (Test_Source_ID_column and Data_column) from Test_Sources_table into a brand new table called Test_Destination_table.
Now the above query is fine for both the old and new tables being in the same database. But what if Test_Sources_table is in file Old.dbf and I want the new table Test_Destination_table to be in New.dbf?
Both Old.dbf and New.dbf are in the same directory. Therefore, there is no security issue about using ......etcpasswords. I should therefore be able to use relative paths. So, I also want to know how I can do that select on two databases in the same directory without typing long, long paths.
Is there a way to do queries that access multiple database files (all database files being in the exact same directory) AND use relative paths or no paths (and certainly not type a full path)?
NOTE 1: I'm using SQLEXPRESS, therefore do not have the SQL Server 2005 tools. I have Visual Studio 2005 Developer Edition with its tools.
NOTE 2: One table is accessed through ODBC. The other table is accessed through SQLEXPRESS.
The scenario is as follows: I have a source with many rows. Each row has a column called max_qty_value. I need to perform a calculation using another column called qty. This calculation is something similar to dividing qty/(ceiling) max_qty_value. Once I have that number I need to write an additional duplicate row for each value from the prior calculation performed. For example, 15/4 = 4. I need to write 4 rows to the same target table as in line information for a purchase order.
The multicast transform appears to only support fixed and/or predetermined outputs. How do I design this logic in SSIS to write out dynamic number of rows to a target table.
Any ideas would be greatly appreciated.
thanks
John
I am testing out a blank database created over two physical files on two separate disks with one table called data which has one column called values nvarchar(max).
I filled the table up with a whole load of data and ran a select * against it. If I run Permon at the same time I can see that the read load has been spread over multiple disks as each of these disks is getting read from in parallel. If I create the same database on a single file and run the same select * again it takes much longer, proving that the read load has been distributed across multiple disks.
Now moving onto writes, this is where the confusion lies. I understand that SQL server fills files evenly until they need growing, after which it will then fill files individually until they are full in a round robin fashion unless you have trace 1117 turned on. What I don't understand is why the writes aren't distributed out whilst it is filling these file groups.
I ran an continual insert into my table with go 1000000 to monitor how the files are being filled up. I monitored where SQL server was physically placing the files as they were being inserted by running the following query:
;WITH CTE AS
(SELECT
sys.fn_PhysLocFormatter (%%physloc%%) col1,
RIGHT(LEFT(sys.fn_PhysLocFormatter (%%physloc%%),2),1) AS [Physical RID],
DATAID
[Code] ....
I could see that it would a thousand or so records into file 1, then a thousand or so into file 2, then a thousand or so into file 1 etc etc. In another words it would hit one disk, then another disk, then back to disk one to fill the file evenly. Is there any way to make SQL Server distribute the writes out in parallel so that both disks are writing in tandem?
By the looks of it, multiple disks only scale reads, as with writes only one disk is ever written to at once which is annoying. Any way to harness the write power of multiple disks?
i attached adventure works in sql server 2008 and it showing as read only ,make it read write or remove read only tag from database.
View 11 Replies View Relatedhow to write a query to get current date or end of month date if we pass year and month as input
Eg: if today date is 2015-09-29
if we pass year =2015 and month=09 then we have to get 2015-09-29
if we pass year =2015 and month=08 then we have to get 2015-08-31(for previous months we have to get EOMonth date & for current month we have to get current date).
I have database with three tables Accounts, Results, and ClosedOrders. All are connected through AccountID PK/FK.
I got a wonderful select statement that gives me the latest Results for each Account.
WITH cte AS
(
SELECT
Accounts.AccountID,
Accounts.AccountName,
Results.ResultTime AS LastUpdated,
[Code] ....
I've been struggling to extend this with two more columns from the ClosedOrders table. How to add columns to the this view? Basically what I need is this:
SELECT SUM([Lots]) AS Longs
FROM [DEV].[dbo].[ClosedOrders]
WHERE OrderTypeID = 0;
SELECT SUM([Lots]) AS Shorts
FROM [DEV].[dbo].[ClosedOrders]
WHERE OrderTypeID = 1;
But it has to "join" the CTE somehow so that I get the correct answer for each Account row.
I'm working on a data analysis involving a table with a large number of records (close to 2 million). I'm using only three of the columns in the table and basically am grouping results based on different criteria. The three columns are PWSID, Installation and AccountType. I have to Provide the PWSID column with a count of the total number of installations per PWSID, also a count of AccountTypes per PWSID. I have the following query, but the numbers aren't adding up and I'm not sure why. I'm falling short in the total count by around 60k records.
CREATE TABLE [dbo].[CATASTRO_PSWID_SHPMUNINEW](
[Installation] [numeric](38, 8) NULL,
[AccountType] [nvarchar](50) NULL,
[PWSID] [smallint] NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
[code]....
SELECT FirstSet.Country,FirstSet.[Month]
,ABC.ABC1
,DEF.DEF1
FROM (
SELECT [Answer Text]'Country',interview_start 'Month',[ID respondent],
[Code] ....
I didn't find whats problem with this code. Actually I try to create a select statement with with cte select statement. In cte clause my output ok but when I try to receive that output from write another select statement then its show error.
Msg 102, Level 15, State 1, Line 276
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Line 315
Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Line 351
Incorrect syntax near the keyword 'as'.
I have a table with the following format
I am looking for a way to get the PRODUCT of all columns and group by M_DOMA, [FROM] - Basically multiply all columns on the row that are not M_DOMA or [FROM].
Is this possible using T-SQL 2012?