T-SQL (SS2K8) :: Ranking Rows On Basis Of SUM Of Records
Jul 17, 2014
Following is my table structure
IDRowCount PagID
1448
2267
3297
4216
5405
6254
[Code] ....
PageId is currently set to 0
I have a user input, @IntNoOfRowsPerPage = 800 Means 800 rows per page. So following is the output I require.
AIDRowCountPageId
1448 1
2267 1
3297 2
4216 2
5405 3
6254 3
[Code] ....
The values of PageID are such that summation of RowCount for PageID is <= @IntNoOfRowsPerPage (i.e, 800)
If NTILE function can be used in such scenarios.
View 5 Replies
ADVERTISEMENT
Nov 13, 2014
I have 5 columns in my database. 1 column is coming like a dynamic.
I want to convert records from rows to columns. Currently I have a data like this.
Race AgeRange Amount
W 17-20 500
W 21-30 400
W 31-40 200
A 17-20 100
H 41-50 250
H 51-60 290
So age range is not fixed and it can be any and I have one separate relational table for age range where it's coming from. Now I want to convert it into columns like
Race 17-20 21-30 31-40 41-50 51-60
W 500 400 200 0 0
A 100 0 0 0 0
H 0 0 0 250 290
View 3 Replies
View Related
Apr 14, 2015
I have around 3 tables having around 20 to 30gb of data. My table A related to table B by a FK and same way table B related to table C by FK. I would like to delete all rows satisfying certain condition from table A and all corresponding related records from table B and C. I have created a query to delete the grandchild first, followed by child table and finally parent. I have used inner join in my delete query. As you all know, inner join delete operations, are going to be extremely resource Intensive especially on bigger tables.
What is the best approach to delete all these rows? There are many constraints, triggers on these tables. Also, there might be some FK relations to other tables as well.
View 3 Replies
View Related
Jan 9, 2015
Split function. I have records of multiple users, the last value of every record is a contact number (10 Digits- Numeric), I want a split function which can take the whole text and split the records on the basis of contact number.
In order words i want SQL to locate the contact number and move to the next record after that and so on till the end of the text.
create table
tbl_1
(txt varchar (max))
insert into tbl_1 values ('john asfasdf 535 summit ave franklin lks nj 15521 510_644_1079 na na 5,8/12 executive,
finance finance and planning far 5537 21133 8.25 126 ronald d hensor jr. 5575621596
[Code] .....
Output
john jimenez 535 summit ave franklin lks nj 15521 510_644_1079 na na 5,8/12 executive,finance finance and planning far 5537 21133 8.25 126 ronald d hensor jr. 5575621596
jeffrey galione 57 allen dr wayne nj 15810 562_434_0710 na na 5,8/12 executive, technical sales and support good 8137 91630 8.25 126 eileen oneal 8258364083
[Code] ....
View 6 Replies
View Related
Jun 8, 2015
I'm in the process of trying to identify duplicate contacts. I doing this for millions of contacts and have gotten stuck and could use some elegant solutions!
The business rule is this:
Any contact that has the same name, phone and email address are the same contact
Any contact that has the same name, and email address are the same contact
Any contact that has the same name, email address, but different phone are a different contact.
Any contact that has the same name, email address, and a blank phone can be the same contact as one that has the same name, email address, and has an email address
Rank by the DataSource_fk. 1 being the highest
Put another way:
If 3 contacts have the same name, 2 have phone '1112223344' and all three have the email address 'johndoe@gmail.com' they are the same contact and the lowest DataSource_fk should be ranked the highest.
I've used the Row_number over (Partition by) in the past, but am unsure how to deal with the blanks in email and phone.
DROP TABLE [dbo].[TestBusinessContact];
GO
CREATE TABLE [dbo].[TestBusinessContact]
(
[TestBusinessContact_pk] INT IDENTITY(1,1)NOT NULL,
[Business_fk]INT NOT NULL CONSTRAINT DF_TestBusinessContact_Business_fk DEFAULT(0),
[Code] ......
View 7 Replies
View Related
Jul 28, 2006
A few weeks ago i posted this and got it to work perfectly. Now im trying to retreive the '3' closest stores rather than just the '1' closest store. How would I go about doing this? I tried using 'TOP 3' in the subquery but you can only retrieve one value.
Here is the origonal post:
"I have a table containing 100 stores, 5000 customers, and the distance between each store and each customer. I am trying to build a query that simply lists each customer and which store is closest and its distance.
Right now it looks like this:
Customer1|store1|33
Customer1|store2|15
Customer1|store3|28
Customer1|store4|35
Customer2|store1|35
Customer2|store2|95
Customer2|store3|28
Customer2|store4|76
I want to get it like this:
Customer1|store2|15
Customer2|store3|28"
Here is the solution:
Code:
SELECT CustomerPost, StorePost, StoreName, Distance
FROM Customer_Store_Distance2 as T
WHERE (Distance =
(SELECT MIN(Distance)
FROM Customer_Store_Distance2
WHERE CustomerPost = T.CustomerPost))
View 10 Replies
View Related
Jan 21, 2015
I have a table with score info for each group, and the table also contains historical data, I need to get the ranking for the current week and previous week, here is what I did and the result is apparently wrong:
select CurRank = row_number() OVER (ORDER BY cr.CurScore desc) , cr.group_name,cr.CurScore
, lastWeek.PreRank, lastWeek.group_name,lastWeek.PreScore
from
(select group_name,
Avg(case when datediff(day, asAtDate, getdate()) <= 7 then sumscore else 0 end) as CurScore
[Code] ....
The query consists two parts: from current week and previous week respectively. Each part returns correct result, the final merged result is wrong.
View 3 Replies
View Related
May 16, 2007
Hi,
When I execute the following statement:
Select * from table1;
On what basis, SQL server decides the sequence of these rows?
I need to fetch data from a table with very large number of rows. Because of the datasize I need to do this in chunks. I am thinking of passing row counter and fetch N rows at a time. I want to know if there is a need for sorting a table before I apply the above logic or I can rely on default sorting.
Thanks veyr much.
Regards,
Tim
View 5 Replies
View Related
Mar 6, 2015
i have a table containing the top 5 teams points for the current season using the rank function, and as well as their points for last season.the only problem is that i want to add a sequential ranking to the table so that if there are 2 or more teams with the same score this season, they are ranked based on their points last season;
League -- Team - Season -- Points
League 1 AAA 2013-14 90
League 1 BBB 2013-14 80
League 1 CCC 2013-14 75
League 1 DDD 2013-14 70
League 1 EEE 2013-14 65
League 1 AAA 2014-15 90
League 1 BBB 2014-15 80
League 1 CCC 2014-15 80
League 1 DDD 2014-15 70
League 1 EEE 2014-15 65
View 3 Replies
View Related
Dec 3, 2014
I have a table with about half a million records, each representing a patient in my county.
Each record has a field (RRank) which basically sorts the patients as to how "unwell" they are according to a previously-applied algorithm. The most unwell patient has an RRank of 1, the next-most unwell has RRank=2 etc.
I have just deleted several hundred records (which relate to patients now deceased) from the table, thereby leaving gaps in the RRank sequence. I want to renumber the remaining recs to get rid of the gaps.
I can see what I want to accomplish by using ROW_NUMBER, thus:
SELECT ROW_NUMBER() Over (ORDER BY RRank) as RecNumber, RRank
FROM RPL
ORDER BY RRank
I see the numbers in the RecNumber column falling behind the RRank as I scan down the results
My question is: How to convert this into an UPDATE statement? I had hoped that I could do something like:
UPDATE RISC_PatientList_TEMP
SET RRank = ROW_NUMBER() Over (ORDER BY RRank);
but the system informs that window functions will only work on SELECT (which UPDATE isn't) or ORDER BY (which I can't legally add).
View 5 Replies
View Related
Aug 5, 2014
I managed to transpose rows into columns.
;WITH
ctePreAgg AS
(
select top 500 act_reference "ActivityRef",
row_number() over (partition by act_reference order by act_reference) as rowno,
t3.s_initials "Initials"
from mytablestuff
order by act_reference
[code]...
But what I would love to do next is take each of the above rows - and return the initials either in one column with all the nulls and duplicate values removed, separated by a comma ..
ref, initials
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
OR the above but using variable number of columns based on the maximum number of different initials for each row.this is not strictly required, but maybe neater for further work on the view
ref, init1,init2
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
View 6 Replies
View Related
Oct 28, 2014
I have 3 tables...
JobRequirements (A)
JobID int
QualificationTypeID int
EmployeeQualifications (B)
EmployeeID int
QualificationTypeID int
Employee (C)
EmployeeID int
EmployeeName int
I need to return a list of all employees fit for a specific job ... The criteria is that only employees who have all the JobRequirements are returned. So if a job had 3 requirements and the employee had just 2 of those qualifications, they would not be returned. Likewise, the employee might have more qualifications than the job requires, but unless the employee has all the specific qualifications the job requires they are not included. If an employee has all the job qualifications plus they have extra qualifications then they should be returned...
How to only return those records where all the child records are present in the other table..
View 5 Replies
View Related
Mar 3, 2014
There are four tables
1. Matter
MID, CID, RType
001, a, m
002, a, m
003, b, m
004, c, m
2. Category
CID. RType
a, T
b, T
c, T
3. Security assignmnet
RID, RType, GID
001, m, g01
002, m, g01
002, m, g02
002, m, g03
003, m, g01
003, m, g03
a, T, g01
a, T, g02
a, T, g03
b, T, g02
b, T, g03
b, T, g04
4. Group
GID
g01
g02
g03
g04
I'd like to find the record in table #1 "Matter" which has exact record of "GID" in table #3 "Security Assignment" compare with table #2 "Category"
In this case, it is record of "002" bacause "002" in table#1 "Matter" and the record "a" in table #2 "category" both has exact GID records(g01, g02, g03) in table #3, "Security Assignment"
How can I create qury to find all the possible record in the table #2?
View 9 Replies
View Related
Mar 6, 2014
I have a table that has multiple records as illustrated in the simple list below. The real list is much longer.
MachineA 1/1/2008
MachineA 1/3/2008
MachineB 1/7/2008
MachineB 1/8/2009
MachineB 5/5/2010
MachineA 5/7/2011
MachineA 4/2/2013
I need to query to return a result for each unique machine with the latest date. The example result below would be returned because they have the latest date.
MachineA 5/7/2011
MachineB 5/5/2010
Select Distinct would almost do it, but I need each unique machine that has the latest date.
View 9 Replies
View Related
Apr 14, 2014
Suppose I have 2 table
1)Main
2)History
Main table maintain all the records having columns MAIN_SKU,DEDUCTIBLE_AMT,model_id,catagory,ModifiedDate
IF DEDUCTIBLE_AMT is changes it will place entry in history table ,columns are same with history_id
i want to display distinct main_sku from history table(all columns) with last DEDUCTIBLE_AMT changed from history table
table structure
main table
MAIN_SKUDEDUCTIBLE_amtmodel_idcatagory
1100100phone
2150101phone
3200109smartphone
4100202smartphone
History table
History_idMAIN_SKUDEDUCTIBLE_amtmodel_idcatagoryModifiedDate
11150100phone4/14/2014
21200101phone4/13/2014
34109202smartphone4/14/2014
44101202smartphone4/13/2014
52200101phone4/13/2014
63100109smartphone4/12/2014
View 3 Replies
View Related
Apr 24, 2014
I have a table called TBLCataloghi
I have multiple records with colunms codpro and codcat equal
They differ only by a date called catalog.datfin
I'd like to select all rows but with the same codpro,codcat, obtaining ONLY the row with MIN () field datfin
Field datfin is a date..
Ex. codpro = 'PIPPO'
codcat = 'MK'
DATFIN = 01/01/2010
codpro = 'PIPPO'
codcat = 'MK'
DATFIN = 10/07/2014
I'd like to read both records but in SELECT obtain only the record with datfin MIN (01-10-2010)
I did the query but i was not able to do nothing of good. I obtain all times both records...
SELECT catalog.codpro AS CodProdotto,
catalog.codcat AS CodiceCatalogo,
MIN(catalog.datfin)
FROM pub.catalog
WHERE catalog.codcat = 'MK'
GROUP BY catalog.codpro,catalog.codcat ,catalog.datfin
View 2 Replies
View Related
Jul 10, 2014
Here is my setup: I have the following tables -
tblPerson - holds basic person data.
tblPersonHistorical - holds a dated snapshot of the fkPersonId, fkInstitutionId, and fkDepartmentId
tblWebUsers - holds login data specific to a web account, but not every person will have a web account
I want to allow my admins to search for users (persons) with web accounts. They need to be able to search by tblPerson.FirstName, tblPerson.LastName, tblInstitutions.Institution, and tblDepartments.Department. The only way a Person record is joined an Institution or Department record is through many -> many junction table tblPersonHistorical.
People place orders and make decisions in our system. Because people can change institutions and departments, we need an historical snapshot of where they worked at the time they placed an order or made a decision. Of course that means some folks will have multiple historical records. That all works fine.
So when an admin user wants to search for webusers, I only want to return data, if possible, from he most recent/current historical records. This is where I am getting bogged down. When I search for a specific webuser I simply do a TOP 1 and ORDER BY DateCreated DESC. That returns only the current historical record for that person/webuser.
But what if I want to return many different webusers, and only want the TOP 1 historical for each returned?
Straight TOP by itself won't do it.
GROUP BY by itself won't do it.
View 9 Replies
View Related
Sep 3, 2014
I have a SP that manipulates data for picking products and puts them into a temp table "#PickList" which is used for the basis of printing a picking note report.
I have also added code at the end of the SP to take the "#PickList" data and insert into a permanent table called "BWT_Lift_Transaction" and then use the bcp command to query it out to a text file. All this works fine, until the bcp command runs. Although the records are in the table, bcp returns nothing. Here is the code:
DECLARE @strLocation VARCHAR(50)
DECLARE @TransNum VARCHAR(50)
DECLARE @strFileLocation VARCHAR(1000)
DECLARE @strFileName VARCHAR(1000)
DECLARE @bcpCommand VARCHAR(8000)
[code].....
The earlier parts of the code create the filename for the text file and location to store it. If I insert a SELECT on the dbo.BWT_Lift_Transaction directly after the insert, I can see the data in there, but although the bcp command creates the file correctly, it returns no data:
output
NULL
Starting copy...
NULL
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
NULL
If I remove the delete statement at the end and run the code twice, it will insert the data into the table twice. On the first run, nothing is returned by bcp. On the second run, the first set is returned by bcp, not both sets.
I don't understand why this is, but I guess it's something to do with transaction commitment and the way bcp works.
View 1 Replies
View Related
Oct 8, 2014
I have the below requirement:
1. Group records according to docno column.
2. Records will sort in desc order. (According to date1 column)
3. In date1 column if more than one date is same than we ll consider the date2 column.
EX: 2008-04-30 00:00:00is same here so sorting will happen based on Date2 column. So internal sorting should happen instead assigning random values.
4. Number column is the expected output column.
docnodate1 date2 Number
d1 2008-08-25 00:00:00 2009-09-08 11:23:41 1
d1 2008-04-30 00:00:00 2008-09-08 14:40:53 2
d1 2008-04-30 00:00:00 2008-09-08 14:29:43 3
d1 2008-04-30 00:00:00 2008-09-08 13:30:04 4
[Code] ....
View 5 Replies
View Related
Oct 20, 2014
I am having a table which contains data of students like:
StudentID,StudentName,Term,RESult.
Sample data :
StudentID,StudentName,Term,RESult.
1,ABC,Term1,Pass
1,ABC,Term2,Fail
1,ABC,Term3,Pass
1,ABC,Term4,Pass
1,ABC,Term5,Pass
Now i want to compare Result and dislay prevterm where student fail:
Now my output would be as: Now I want to compare latest term i.e. Term5 with prev Terms and if found Mismatch in result then i want to display as below:
studentID PrevFailTerm, CurrentTerm
1,Term2,Term5
View 1 Replies
View Related
Feb 14, 2015
Below is my sample data and query
declare @Sample table (ID int, message varchar(1000))
insert into @Sample(ID,message)
select 1,'Testing 1' union all
select 1,'Testing 2' union all
select 1,'Testing 3' union all
[Code] ....
I need to get top three values has to be comma separated. for example id 1 has 5 rows message which comma separated. Instead i need to consider top three message group by Id
expected result :
IDmessage
1Testing 1, Testing 2, Testing 3
2Testing 6, Testing 7, Testing 8
3Testing 11, Testing 12, Testing 13
View 3 Replies
View Related
Mar 20, 2014
In our concern, we have a table with lots of redundant rows, to avoid the redundant, we create some structure and denormalize the table now we need to migrate the old data into the new structure.
Here is my following table structure
DECLARE @TestTable AS TABLE(id INT, DAta1 VARCHAR(500), Data2 VARCHAR(500), Data3 VARCHAR(3))
INSERT INTO @TestTable
VALUES
(1, 'Name', 8, 1),
(1, 'possible Shifts', 30, 7),
(1, 'First shift', 22, 8),
(1, 'Second Shift', 24, 9),
[Code] ....
This is just a sample data we have a lot amount of data like this, any changes in Data1 or Data2 might came under a new version, any new insert or delete in a ID column based Set might consider as a new version. So I need to migrate this into the following structure of output
DECLARE @tbl AS TABLE (ID INT, DAta1 VARCHAR(200), DAta2 VARCHAR(200), Dversion INT)
I need to import the data as set with out redundant please do not consider the column data3, and the output might be like this
SELECT * FROM
(
VALUES
(1,'Name','8',1),
(1,'possible Shifts','30',1),
(1,'First shift','22',1),
[Code] ....
This is for single time import only...
View 9 Replies
View Related
Dec 29, 2014
Came across one scenario not able to find out how to do it..
Below is the table data
ColA
1
-1
2
-2
Need output in below way
ColA ColB
1 -1
2 -2
View 5 Replies
View Related
Feb 4, 2015
What I want is to divide the row value for 'OB Dial Attempts' by the row value for '# Ready To Work Inventory', both in the same table.The code below will work, however I think there is a flaw in my logic. The actual table only has one row per category (MatrixCat) and will always have only one row per category.
CREATE TABLE #NumVals (MatrixCat VARCHAR(100), MatrixVal VARCHAR(100));
INSERT INTO #NumVals (MatrixCat, MatrixVal) VALUES
('# Ready To Work Inventory','606'),
('OB Dial Attempts','255');
[code]....
View 8 Replies
View Related
Dec 30, 2011
I need to get the replacement records between the 2 tables. I have table A and table B with same structure. I have 5 fields. Table A has 50,000 records and table B has 20,000 records. I have fields id , name, address,meter_flag,end_Date.
Some of the records in Table B are just replacement records of table A. I mean for example I have records like this in Table A
id name address meter_flag end_date
23 john 1201 salt lake dr no 2011-12-28
24 tom 1222 gibson ln yes 2011-12-16
25 alex 1334 qayak dr no 2011-12-17
In Table B
23 john 1344 mc kinney st yes 2011-12-18
24 tom 1222 gibson ln yes 2011-12-16
56 gary 1335 pruitt rd no 2011-12-18
25 alex 1334 qayak dr no 2011-12-17
So here in Table B i have an update for john with id 23 in table A in address field and meter_flag has changed to yes. There is new record with id 25 in table b but that is not in table A. so I need to find all these difference records by querying these 2 table
View 9 Replies
View Related
Mar 18, 2014
I want to compare ONLY 1 Column values from 2 tables having more than 4.9 million records. There is a difference of 4000 rows between the 2 tables.
SELECT ID From TABLE1 where ID not in (SELECT DISTINCT ID From TABLE2)
My above query took nearly 4.5 hours to run and I had to cancel it. Is there a better way to write the query . I just want to compare the ID - column values which are missing in TABLE2
View 7 Replies
View Related
Apr 21, 2014
We have a data warehouse staging database in which we capture change history for hundreds of tables from a source system. In the source system, records are updated in place, but in our data warehouse we capture these changes by "terminating" the existing record and adding a new record reflecting the changes. In the data warehouse we add two columns to every table -- effective_date and expiration_date -- which indicate the dates the record was in effect in the source system. By convention, an expiration_date of 6/6/2079 means the record is currently still active in the source system. Each day we simply compare yesterday's version of the record (in the data warehouse) against today's version (in the source system). If differences are found in any of the columns, we terminate the record and add a new one, setting those dates appropriately.
In this example, the employee_id column is the natural key in the source system. We add the effective_date and expiration_date in the data warehouse, so those three columns together make up the key in the data warehouse. The employee_name, employee_dept, and last_login_date columns all come from the source system as well.
drop table mytbl
create table mytbl (
effective_date smalldatetime,
expiration_date smalldatetime,
employee_id int,
employee_name varchar(30),
[code]....
In the select output, you can follow the trail of changes for each of these three employees. Bob moved from dept 7 to 8 at some point; Frank didn't change departments at all; Cheryl moved from dept 6 to 9 and later back to 6. However, the last_login_date was updated frequently for all these employees.
We've tracked hundreds of tables this way for years, some with hundreds of columns. For optimization purposes, I'm now interested in trimming the fat a bit. That is, we track changes in many columns that we don't really need in our data warehouse. Some of these columns are rapidly-changing, causing all sorts of unnecessary terminate/inserts in the data warehouse. My goal is to remove these columns, reclaim the disk space and increase the ETL speed. So in this example, let's get rid of the last_login_date column.
alter table mytbl
drop column last_login_date
select *
from mytbl
order by employee_id, effective_date
Now in the select output, you can see we have many "effective duplicate" records. For example, nothing changed for Bob between 1/1/2014 and 1/31/2014 -- those really should be one record, not three. Here's the challenge: I'm looking for an efficient way to merge these "effective duplicates" together, through set-based sql updates/deletes/inserts (hoping to avoid any RBAR operations). Here's what the table ultimately should look like (cheating to get there):
create table mytbl2 (
effective_date smalldatetime,
expiration_date smalldatetime,
employee_id int,
employee_name varchar(30),
employee_dept int
[code]...
Note that Bob only has two records (he changed department), Frank only has one record (no changes), and Cheryl has three records (two department changes).
My inclination would be to drop the unwanted columns, then GROUP BY all the remaining columns from the source system, and taking the MIN effective_date and MAX expiration_date. However, this doesn't work for cases like Cheryl's -- she moved to another department, then back again, so that change history needs to be retained.
As I mentioned, we have hundreds of tables, and I'd like to strip out dozens (maybe hundreds) of unused columns, so ultimately there will be millions of these pseudo-duplicates that need to be merged together. These are huge tables, so I really need to find an efficient set-based approach to this.
View 2 Replies
View Related
May 2, 2014
using below query
DELETE FROM Table_name
WHERE Date_column < GETDATE() - 30
am able to delete old records morethan 30 days, but i want to results to be saved in file.
before deleting i want to a craete a file and save the to be deleted records.
View 7 Replies
View Related
Jun 5, 2014
I'm working on a report where my table is as follows:
WITH SampleData (ID,NAME,[VALUE]) AS
(
SELECT 170983,'DateToday','6/04/2014'
UNION ALL SELECT 170983,'DateToday','6/04/2014'
UNION ALL SELECT 170983,'employee','1010'
UNION ALL SELECT 170983,'employee','1010'
[Code] .....
Here is my query against the table above:
SELECT
ID
,MAX(CASE WHEN NAME = 'employee' THEN VALUE END) AS PERSON
,MAX(CASE WHEN NAME = 'DateToday' THEN VALUE END) AS REQUEST_DATE
,MAX(CASE WHEN NAME = 'LeaveStartDate' THEN VALUE END) AS REQUEST_START_DATE
,MAX(CASE WHEN NAME = 'LeaveEndDate' THEN VALUE END) AS REQUEST_END_DATE
,MAX(CASE WHEN NAME = 'HoursPerDay' THEN VALUE END) AS REQUESTED_HOURS
,MAX(CASE WHEN NAME = 'LeaveType' THEN VALUE END) AS REQUEST_TYPE
FROM SampleData
Here is the result from the above query, I'm not sure how to get the desired results (listed at the end):
IDPERSONREQUEST_DATEREQUEST_START_DATE REQUEST_END_DATE REQUESTED_HOURS REQUEST_TYPE
170983NULL6/04/2014NULL NULL NULL NULL
1709831010NULL NULL NULL NULL NULL
170983NULLNULL NULL NULL 8:00 NULL
170983NULLNULL NULL 6/16/2014 NULL NULL
[Code] .....
My Desired results are as follows:
IDPERSONREQUEST_DATEREQUEST_START_DATE REQUEST_END_DATE REQUESTED_HOURS REQUEST_TYPE
17098310106/04/20146/16/2014 6/16/2014 8:00 Personal
17102416/04/20146/17/2014 6/17/2014 8:00 Bereavement
View 2 Replies
View Related
Jun 5, 2014
I need to export the records of a table in xml format.
create table ##prova
( Valuta varchar(2),
Misura float
)
insert into ##prova values ('EU',1000)
insert into ##prova values ('$',2000)
The final result must be something like this:
<root>
<obs id=”0”>
<dim name=”Valuta” value=”EU” />
<dim name=”Misura” value=”1000” />
</obs>
<obs id=”0”>
<dim name=”Valuta” value=”$” />
<dim name=”Misura” value=”2000” />
</obs>
</root>
View 2 Replies
View Related
Jun 25, 2014
I have a pretty complex query that returns three records. For simplicity sake, the results can be simulated with this query:
Select 5 AS InternalAuditTeamEmployeeID, 1 as InternalAuditTeamID
UNION ALL
Select 11, 2
UNION ALL
Select 14, 3;
I want to take this result and update the Flag field to true in my table tblInternalAuditTeamEmployee (CREATE statement below) for any InternalAuditTeamEmployeeID that is less than or equal to the ones in the results above, but by group. My results would look something like this using the data below and the results above.
InternalAuditTeamEmployeeIDInternalAuditTeamIDEmployeeIDFlag
1 1 619 1
218581
316041
425181
517161
639661
711910
819400
92391
1012340
1129541
1228910
1329500
143321
1539450
I was thinking I could somehow use ROW_NUMBER(PARTITION BY InternalAuditTeamID ORDER BY InternalAuditTeamEmployeeID DESC), but not sure how to get the results of "WHERE <= InternalAuditTeamEmployeeID For each particular group".
CREATE TABLE STATEMENT:
CREATE TABLE [tblInternalAuditTeamEmployee](
[InternalAuditTeamEmployeeID] [int] IDENTITY(1,1) NOT NULL,
[InternalAuditTeamID] [int] NOT NULL,
[EmployeeID] [int] NOT NULL,
[Code] ......
View 3 Replies
View Related
Mar 10, 2015
I am creating a report on an asp.net webpage to show how many times a coupon has been used from a set list of items.There are two tables I am using for this query.Table 1 has the list of items or coupons. Table 2 has a column for the item id from Table 1 and a value.
Example:
Table 1
id Name
1 Coupon1
2 Coupon2
3 Coupon3
[code]...
My current query will only show coupon1 and coupon2 and the number and value. It will not show coupon2 since it hasn't been used. I would still like to show coupon2 with a number of zero and a value of zero.
Current query is:
SELECT Table1.Name, COUNT(Table2.ID) AS CNum, SUM(Table2.Value) AS CValue FROM Table2 JOIN Table1 ON Table2.ID = Table1.ID GROUP BY Table1.Name
View 5 Replies
View Related
Apr 14, 2015
i have a query i.e.
select project, description from table
with results
chocolate | white
chocolate | black
chocolate | brown
sugar | black
vinegar | yellow
i would like to get a result like:
chocolate | white, blakc, brown
sugar | black
vinegar | yellow
so I would like to get all values of one project in one records included with separators/a space in it
View 8 Replies
View Related
