T-SQL (SS2K8) :: Making A Record Set Horizontal
May 14, 2014
I have a client with a fairly simple table as illustrated in my sample code. Their interesting requirement is that the records would be listed horizontally, end-to-end as I hope you can visualize based on what I'm providing here. Two other characteristics/specs of note, 1) there could be as few as two records end-to-end in a row or as many as 100 and 2) of course the column headings would have to be unique.
create table #tmpEndToEnd
(
ID int identity,
ClientID char(10),
Agency varchar(20),
Clinician varchar(20),
Goal varchar(50)
[code]....
View 3 Replies
ADVERTISEMENT
May 14, 2014
create table #temp1
(
col1 float,
col2 float,
col3 float,
clo4 float)
insert into #temp1 values (1.5, 1.6,1.7,1.8)
insert into #temp1 values (1.9, 1.0,1.2,1.8)
o/p should display as below is there a way we can do this with cte or some other option
col1 1.5 1.9
col2 1.6 1.0
col3 1.7 1.2
col4 1.8 1.8
View 1 Replies
View Related
Apr 21, 2015
I have 10 records seperated by ID. I m using List in SSRS to show data on report. I need to add horizontal line after first record, then after second record till 10 th record. How do we add this line?
View 4 Replies
View Related
Oct 13, 2014
I have a scenario where the second SELECT statement in my query has to be removed such that the logic needs to be included in the CTE select statement itself.
How this can be done? The columns in the final SELECT statement is having bit complexity.
My query is below:
WITH Local_EOL_Dioxin_Statistic_Import_New
AS
(
SELECT
CASE
WHEN ISNUMERIC(vr.stringValue) = 1 THEN vr.stringValue
[Code] .........
View 6 Replies
View Related
Apr 10, 2014
Observe below records of table Bakery . When cake is order it flows from
BaseMaking----------> Decoration------------> Delivered/Reject .
If cake is Rejected , again process has to start from BaseMaking ..
I need assign BatchID when it flows from BaseMaking to Delivered/Reject And For Analysis I need to exclude BatchID which are rejected..
Expected OutPut..
SQL Script
Create table dbo.Bakery ( CakeID int,EmployeeID Int ,ActionName varchar(20) ,LogTime datetime )
Insert into dbo.Bakery( CakeId, EmployeeID,ActionName,LogTime)Values
(1,1,'BaseMaking','2014-04-10 09:30:00.000' )
, (1,1,'Decoration','2014-04-10 10:30:00.000' )
, (2,2,'BaseMaking','2014-04-10 09:40:00.000' )
, (2,1,'Decoration','2014-04-10 10:43:00.000' )
, (2,3,'Reject','2014-04-10 10:50:00.000' )
, (2,2,'BaseMaking','2014-04-10 11:00:00.000' )
,( 2,1,'Decoration','2014-04-10 12:00:00.000' )
,( 2,3,'Delivered','2014-04-10 12:30:00.000' )
View 9 Replies
View Related
May 12, 2014
,I am creating a database where each record is required to have a twin record in the database.These is a type a value and a type b value and both must be present for the record to be valid.
Customer_ID, Order_Type, Product_Code
54, a, 00345
54, b, 00356
Is this something that would have to be done programmatically, or is it possible to create a constraint of some sort to ensure this?
View 8 Replies
View Related
Aug 5, 2014
I need to write SP which will call from front End to populate dropdown, But in dropdown uppermost value should be frequently used.
For example, My SP Select CountryID,Country Columns where i am displaying Country in dropdown. Now first time it will sort by ascending, next time if user selects US then US should be top and rest things after that.
Now If user selects Canada 3 times then result should sort as Canada,US, and rest countries.
if again US Selects 7 times then result should display as US,Canada,........
In short need to sort depending used previously. i.e. most popular first and so on......
View 6 Replies
View Related
Mar 6, 2014
I have a snapshot table of about 15 million records in the form of:
InvoiceIDLineItemIDSnapshotDateAmount
1 1 20140101 12
1 2 20140102 14
1 3 20140103 17
2 1 20140101 10
2 2 20140102 5
1 2 20140105 15
1 3 20140105 20
I want to create an additional column called Current as shown below:
InvoiceIDLineItemIDSnapshotDateAmount Current
1 1 20140101 12 1
1 2 20140102 14 0
1 3 20140103 17 0
2 1 20140101 10 1
2 2 20140102 5 1
1 2 20140105 15 1
1 3 20140105 20 1
How can we write a query to achieve this while keeping in mind:
- We do not want to do unnecessary record lookups and Updates
- We only update records that corresponds to new entries. For example, we should not touch the record for InvoiceID = 2 in the above example
View 6 Replies
View Related
Mar 14, 2014
I am having table which is having 5 columns say A,B,C,D and E. There are chances to change in C,D,E columns. I want to identify if any change happened in the above column and show to user prev value + new value.
i.e. i want to prepare query by using calculated extra 6 column where i want to put change happned in C,D,E columns.
how to do that?
View 5 Replies
View Related
May 14, 2014
The situation is that we have resources (trucks) that perform shifts. Shifts consists of actions. A resource can perform multiple shifts.
For every resource we want to find the record that:
- Is 'younger' than the last realized action.
- Has actionkind pickup, deliver or clean
I have constructed a solution with CTE and row_number but I was curious if there would be other alternatives. The fact that I'm joining a CTE onto itself and subject the outcome to a partition makes me think there are sharper ways.
Note that the action id in the data below is also sorted but in practice this need not be the case. The sorting key is prevalent.
output of the query is
id_action id_resource actionKindCode
4665 4 clean
34540 96 pickup
24000 901 clean
declare @mytable table (
id_action int,
id_shift int,
[code]....
View 6 Replies
View Related
Jul 4, 2014
How to calculate Average sal foe below scenario.
I am having tables with 12 columns as jan,feb,.......dec.
Now I want to calculate average salary for each record, but condition is that if any month salary is zero then that column also exclude from average calculation.
For example : if jan and feb column values are zero then i want to calculate (mar+apr+...+dec)/10.
View 5 Replies
View Related
Oct 6, 2014
I am simply inserting records in a table, But if record already present then i dont want add that record and display resultset as -1.If record is not present then i want to add that record and commit transaction. I am doing like below : below is sample, I am considering only two columns.
CREATE PROCEDURE [dbo].[Sproc__Save_Teacher_Details]
-- Add the parameters for the stored procedure here
@FacilitiDetailID int
,@SpecialityType varchar(50)
AS
BEGIN
[code]....
above code is working fine for new recordset but if record exists then it is giving two resultsets -1 and 1, but i want to display only -1.
View 4 Replies
View Related
Oct 15, 2014
I am having below two tables:
1) TableA : Which contains 5 columns(Column1,..........Column5)
2)TableB : Which contains 10 columns(Column1,..........Column10)
TableB contains millions of data.Now I want select all 5 columns from tableA but combination of Column1,Column2,Column3 if present in tableB, then i want exclude that records.I am doing as below:
select * from TableA a join TableB b a.column1!=b.column1 and a.column2!=b.column2 and a.column3!=b.column3 )
But query is taking almost 5 minutes. Is there is another approach?
View 4 Replies
View Related
Aug 3, 2015
I would like to display all the products with maximum SeqNo from the table below:
TABLE
ProductIDSeqNoBalance
111215
11135
111420
111510
12115
1212100
121325
121445
OUTPUT
ProductIDSeqNoBalance
111510
121445
View 3 Replies
View Related
Aug 27, 2014
I have created the following SQL snippet that is a very simple mock-up illustrating the problem (I hope!) that I am facing:
-- create table
if object_id('tempdb..#tmpdelnotes') is not null
drop table #tmpdelnotes
create table #tmpdelnotes(
DelNote int identity (1,1) ,
DelDate date not null,
Item int not null,
Customer int not null)
[code]...
What I need to retrieve is a unique list of item numbers with information about the latest (DelDate) delivery note. The "Clumsy workaround" works, but is not very pretty when doing multiple table joins. Is it really necessary to use a derived table for this kind of query? Window functions can only exist in the SELECT and ORDER BY clauses, which is understandable since the calculations take place (I would guess) after the aggregations in the HAVING clause.
View 2 Replies
View Related
Dec 10, 2014
This my table named myData
CREATE TABLE [dbo].[myData](
[idx] [int] NULL,
[paymentMethod] [nvarchar](200) NULL,
[daerahKutipan] [int] NULL,
[payer] [int] NULL,
[code]....
I want to calculate the number of payer Group By paymentMethod. The number of payer must be divided by daerahKutipan. So far, my query as follow
select paymentMethod,
COUNT(CASE WHEN daerahKutipan = 1 THEN payer ELSE 0 END) figure_Seremban,
COUNT(CASE WHEN daerahKutipan = 3 THEN payer ELSE 0 END) figure_KualaPilah,
COUNT(CASE WHEN daerahKutipan = 4 THEN payer ELSE 0 END) figure_PortDickson,
COUNT(CASE WHEN daerahKutipan = 5 THEN payer ELSE 0 END) figure_Jelebu,
COUNT(CASE WHEN daerahKutipan = 6 THEN payer ELSE 0 END) figure_Tampin,
COUNT(CASE WHEN daerahKutipan = 7 THEN payer ELSE 0 END) figure_Rembau,
[code]....
View 1 Replies
View Related
Feb 17, 2015
Better method to detect records with one or more 0's, other than hard coding all of the potential values. For example, I need to detect account duplicates where the duplicate contains multiple/all 0's in the "MasterAccount" column:
DECLARE @Account TABLE(AccountNo varchar(3), MasterAccount varchar(10) )
INSERT INTO @Account
SELECT 'ABC','0000000ABC' UNION
SELECT 'DEF','0000000DEF' UNION
SELECT 'ABC','0000000000' UNION ---- unwanted duplicate
SELECT 'DEF','00000' UNION ---- unwanted duplicate
[Code] ....
Is there a better method than this?
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
Apr 24, 2015
We are getting data feed from Oracle database in our project. Everyday we will need to track if any rows got inserted/updated/deleted in the source and get that update right into our data warehouse.
Currently we are taking a dump of the required table (as it is) to our staging DB and comparing it with previous day data to track the changes (column by column comparison). This approach is working currently but has performance bottleneck. There is no tracking column (eg. last modified date or time) in source that will give us any idea of what got changed. Also there is no identity key or primary key in the source data.
Is there a way in SQL Server to get that inserted/updated records only instead of comparing column by column to track the changes?
View 1 Replies
View Related
Sep 4, 2015
I have a scenario where ID has three flags.
For example
ID flag1 flag2 flag3
1 0 1 0
2 1 0 0
1 1 0 0
1 0 0 1
2 0 1 0
3 0 1 0
Now I want the records having flag2=1 only.. I.e ID=3 has flag2=1 where as ID = 1 and 2 has flag1 and flag3 =1 along with flag2=1. I don't want ID=1 and 2.
I can't make ID unique or primary. I tried with case when statements but it I am somehow missing the basic logic.
View 5 Replies
View Related
Sep 18, 2015
I need to build a CTE query to find for the same Cabstamp (document) where i have different Origin.
I know that i can build this with a correlated subquery, but i´am curious about using CTE.
I post sample create Script:
create table #temp (Cabstamp varchar(10), account varchar(10),document varchar(15), origin varchar(2), debit numeric(10,2), credit numeric(10,2), datalc datetime)
insert into #temp (Cabstamp,account,Document, origin, debit, credit, datalc)
select 'ADM12345',111,'CMP-01','FO',1000,0, '20150110'
union
select 'ADM12345',112,'CMP-01','FO', 500, 0,'20150110'
union
select 'ADM12345',6811,'CMP-01','DO',0,1500,'20150110'
union
[code]....
View 5 Replies
View Related
Aug 26, 2014
I have been asked to give the users the ability to copy a set of records within the database. The current setup is
Master Table: JSA1
JSAID: (PK, int, not null)
JOBTITLE(nvarchar(200), null)
PlantNumber(int, not null)
Detail Table: tblSteps
STEPID (PK, int, not null)
JSAID (FK, int, not null)
StepNo (int, null)
BasicSteps (nvarchar(200), null)
DBPhoto(varbinary(max), null)
The plant number field is a location based field that the application uses to filter/select data on for the end users. What they want to be able to do is to select a record, select another location from a dropdown list and then click a button that duplicates the master record and the detail records to the new location.
I am thinking that a stored procedure passing the JSAID and new Location number to do it, I am just not sure how to get the new ID when I go to copy the detail records.
View 4 Replies
View Related
Mar 27, 2014
I'm got a "folder" structure application which we'll be using as an in-house directory viewer. (In case you're wondering, it doesn't relate to any "real" folders, so using xp_cmdshell is out! )
Each folder and file record can have its own permissions, however these are assumed to inherit from the parent folder if no specific access rules have been set, basically in the same way file systems work. Each file record can only have one parent, and a folder can either have a parent or be at the root level.
Right now I'm having an issue with the inheritance of permissions. Say if I want to grant access to "Folder 1" to "Group A", then "Group B" shouldn't be able to see it. However, if I grant access to "File 1" in "Folder 1" to "Group B", then "Group B" should be able to see "Folder 1", but only see "File 1" and not the rest of the contents.
I thought I could do this with a CTE, but I'm having a bit of difficulty..
Here's the code:
CREATE TABLE #FileSystem (
FSIDINTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY
,ParentFSIDINTEGER NULL
,NameVARCHAR(100)
,RecordTypeVARCHAR(1)-- (F)older, or Fi(L)e
[Code] ....
View 1 Replies
View Related
Apr 16, 2014
I'm using a Merge statement to update/insert values into a table. The Source is not a table, but the parameters from a Powershell script. I am not using the Primary Key to match on, but rather the Computer Name (FullComputerName).
I am looking on how-to return the Primary Key (ComputerPKID) of an updated record as "chained" scripts will require a Primary Key, new or used.As an aside: the code below does return the newly generated Primary Key of an Inserted record.
CREATE PROCEDURE [dbo].[usp_ComputerInformation_UPSERT](
@FullComputerName varChar(50) = NULL
,@ComputerDescription varChar(255) = NULL
,@ComputerSystemType varChar(128) = NULL
,@ComputerManufacturer varChar(128) = NULL
[code]....
View 4 Replies
View Related
Sep 15, 2014
I am looking to create a script that will go through a table a pick out the necessary columns to create a unique record. Some of the tables that I am working with have 200 plus columns and I am not sure if I would have to list every column name in the script or if they could be dynamically referenced. I am working with a SQL server that has little next to no documentation and everytime I type to mere some tables, I get too many rows back.
View 4 Replies
View Related
Jun 9, 2015
Here is a CTE query
With mstrTable(ItemNo, Sales)
as (
Query1
Union All
Query2
)
Select Row_Number () Over(Partition by ItemNo order by ItemNo)as RowNo, ItemNo, Sales
From mstrTable
order by ItemNo
The results from Query1 and Query2 overlap sometimes.
The result set looks like:
1 Item1 10000
2 Item1 10000
1 Item2 20000
1 Item3 30000
I only want the first occurrence of each item. The desired result set is:
1 Item1 10000
1 Item2 20000
1 Item3 30000
I am not able to add a "Where RowNo = 1" to the query. SQL returns an "invalid field name".How would I obtain a record set that returns just the first occurrence of the ItemNo field?
View 7 Replies
View Related
Aug 17, 2015
I have this View and want to also see Clubs that do not have current memberships.I have the IS NULL but not seeing the Clubs that do NOT have memberships. attribute.PersonMembership is a SQL table that has membership information.
SELECT dbo.v060ClubOfficersPresOrNot.ClubNo, dbo.v060ClubOfficersPresOrNot.SortName, dbo.v060ClubOfficersPresOrNot.ClubName,
dbo.v060ClubOfficersPresOrNot.BSProgram, dbo.v060ClubOfficersPresOrNot.ClubSection, dbo.v060ClubOfficersPresOrNot.Code,
RTRIM(attribute.PersonMembership.InvoiceNumber) AS InvNo, dbo.v060ClubOfficersPresOrNot.President, dbo.v060ClubOfficersPresOrNot.Email,
[Code]...
The "PersonMembership" has all the membership records from 2015 through 2019 of membertypeid 1-4 for the sampling.Since the syntax used in Access do not carry over without modifications to SQL, SQL syntax to make it work in SQL.And if you know the proper SQL syntax for "Between Year(Date())+IIf(Month(Date())>=7,1,0) And Year(Date())+IIf(Month(Date())>=7,1,0)+4" instead of what I currently have in SQL, that would be wonderful.
View 9 Replies
View Related
Jan 14, 2015
My goal is to update the "PriorInsert" field with the "DateInserted" from the previously inserted record where the WorkOrder, MachineNo, and Operator are all in the same group.
While trying to get to the correct previous record, I wrote the query below.
P.S. The attached .txt file includes a create and insert tbl_tmp sampling.
select top 1
a.ID,
a.WorkOrder,
a.MachineNo,
a.Operator,
a.PriorInsert,
[code]...
View 2 Replies
View Related
Aug 27, 2014
I would like to have records in my Absences table split up into multiple records in my query based on a start and end date.
A random record in my Absences table shows (as an example):
resource: 1
startdate: 2014-08-20 09:00:00.000
enddate: 2014-08-23 13:00:00.000
hours: 28 (= 8 + 8 + 8 + 4)
I would like to have 4 lines in my query:
resource date hours
1 2014-08-20 8
1 2014-08-21 8
1 2014-08-22 8
1 2014-08-23 4
Generating the 4 lines is not the issue; I call 3 functions to do that together with cross apply.One function to get all dates between the start and end date (dbo.AllDays returning a table with only a datevalue column); one function to have these dates evaluated against a work schedule (dbo.HRCapacityHours) and one function to get the absence records (dbo.HRAbsenceHours) What I can't get fixed is having the correct hours per line.
What I now get is:
resource date hours
...
1 2014-08-19 NULL
1 2014-08-20 28
1 2014-08-21 28
1 2014-08-22 28
1 2014-08-23 28
1 2014-08-24 NULL
...
... instead of the correct hours per date (8, 8, 8, 4).
A very simplified extract of my code is:
DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
SET @startdate = '2014-01-01'
SET @enddate = '2014-08-31'
SELECTh.res_id AS Resource,
t.datevalue,
(SELECT ROUND([dbo].[HRCapacityHours] (h.res_id, t.datevalue, t.datevalue), 2)) AS Capacity,
(SELECT [dbo].[HRAbsenceHours] (9538, h.res_id, t.datevalue, t.datevalue + 1) AS AbsenceHours
FROMResources h (NOLOCK)
CROSS APPLY (SELECT * FROM [dbo].[AllDays] (@startdate, @enddate)) t
p.s.The 9538 value in the HRAbsenceHours function refers to the absences-workflowID.I can't get this solved.
View 1 Replies
View Related
Oct 1, 2014
I am calling stored procedure called GetCommonItemCount within another stored procedure called CheckBoxAvailability, the first stored procedure should return a count to second stored procedure and based on that some logic will be executed.
I have 2 problems in that
1. The result is not coming from first stored so the variable called @Cnt is always 0 although it should be 18
2. At the end i need to see in the output the result from second stored procedure only while now i am seeing multiple results of record sets coming.
I have attached the scripts also, the line i described in step1 is
View 9 Replies
View Related
Apr 22, 2015
following table global_usage
ID varchar (contains alphanumeric values,not unique)
Territory (combined with ID unique)
Total_Used int can be null
Date_ date (date of the import of the data)
ID Territory Total_Used Date_
ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01
[Code] .....
Now the problem,per month I need the most recent value so I'm expecting
ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01
ACASC CAL071288 2014-11-01
ACASC CAL071190 2014-12-14
ACASC CAL071286 2015-01-22
ACASC CAL071165 2015-02-01
ACASC CAL071164 2015-03-01
I've tried a few thing like group,having even row_number() but I keep getting wrong results
View 6 Replies
View Related
Feb 3, 2005
If I have the raw data dumped into a big table as following:
Date P R M E Date P R M E Date P R M E Date P R M E
1/1/90 1 2 3 4 1/1/90 2 3 4 5 1/1/90 3 4 5 6 1/1/90 4 5 6 7
...
1/1/05 1 2 3 4 1/1/05 2 3 4 5 1/1/05 3 4 5 6 1/1/05 4 5 6 7
And this table has a repeating block [D, P, R, M,E] 300 times. Is it possible to write a loop query/stored procedures/triggers (or whatever it is) to read each repeating block and stack them on top of each other to insert into another table which has the same structure as following?
Look like this?
Date P R M E
1/1/90 1 2 3 4
...
1/1/05 1 2 3 4
1/1/90 2 3 4 5
...
1/1/05 2 3 4 5
1/1/90 3 4 5 6
...
1/1/05 3 4 5 6
If there is a solution would you please elaborate, example?
Thank you for the help.
shiparsons
View 14 Replies
View Related
Jul 20, 2005
Hello all,Thinking about building a new database in the enterprise addition ofsql server and using some horizontal parititioning techniques in orderto accomaodat what will eventually be a monster huge database.Can you share some hard earned experience, gotchas, etc...with me? Wewill be setting up this server on a SAN array that will be made up ofjust one or two huge virtual RAID10 volumes and I am also wonderingabout the wisdom of this? Its simple and should work, but is thatconfig relevant in any way specifically to our plan to partition?
View 2 Replies
View Related
