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)
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?
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
,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?
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......
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
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.
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.
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.
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.
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?
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.
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
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?
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.
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
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.
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
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.
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.
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.
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?
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.
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.
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,
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.
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
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
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
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?
