ID Order Date .....
1 Bla 2015-01-13 12:00:45.2713558 .
2 Mio 2015-01-13 12:05:45.2713558 .
3 Tala 2015-01-13 14:00:45.2713558 .
4 Bla 2015-01-13 15:00:45.2713558 .
5 Mio 2015-01-13 20:00:45.2713558 .
6 Bla 2015-01-15 17:00:45.2713558 .
7 Bla 2015-01-15 19:00:45.2713558 .
[code]...
I tryed several things with CASE, IF, EXISTS,... but I had no success.
Query: SELECT m.MemberID, vw.Category, vw.Type, FROM dbo.TestVW vw JOIN dbo.TestMember m ON vw.MemberKey = m.MemberKey WHERE vw.Type = 'test' GROUP BY m.MemberID,
[Code] ...
but cannot seem to be able to return one record with its corresponding value criteria.
I need to make a selection on join datasets with 2 conditions and populate the results in another dataset(Report).It is working with the fist condition "AccountingTypeCharacteristicCodeId = 3"...
I have a non-partitioned table (TableToPartition) and I want to apply an existing partition scheme (PartSch) to it using a query. I didn't find any option so I used the StorageCreate Partition wizard to generate the script.why this clustering magic needed if it is dropped at the end? Isn't there another way without indexing to partition a table, say something with ALTER TABLE? (SQL Server 2012)
BEGIN TRANSACTION CREATE CLUSTERED INDEX [ClusteredIndex_on_PartSch_635694324610495157] ON [dbo].[TableToPartition] ( [ID] )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PartSch]([ID]) DROP INDEX [ClusteredIndex_on_PartSch_635694324610495157] ON [dbo].[TableToPartition] COMMIT TRANSACTION
I have a table called USERS, some of its records are marked as hiddenRcord, I want to load those records in a custom page in my asp.net webpage with paging enabled, each page contains 10 records.
I use the statement "SELECT Top 10 tableID,userName FROM USERS WHERE (hiddenRecord=0 AND tableID>@tableID)"
The pagination has 5 links (First,2,3,4,Last), I can of course put the last tableID in link number 2, but I don't know how to do it for the links (3,4,Last).
I'm trying to find it difficult to use recursice CTEs or better solution for a special request below. There are two tables 1) @sizes serves as a lookup or reference for right drive and 2) @test is sample data with different sizes. Assume that I want to evenly distribute the drive letters from table1 to table2 by checking the size available.
E.g.: for the first record in @test; id = 1 where the size is 50 and it fits in Y: drive -- left over space in Y: = 50
id=2, size 2.5, space available from left over = 50 - 2.5 = 47.5 which again fits into Y: id = 3, size 51, cannot in fit in Y: drive as there is enough space in Y: to allocate (51 > 47.5)
so pick the next drive check on availability again
DECLARE @sizes TABLE (id TINYINT, size DECIMAL(5,2), drive VARCHAR(3)) INSERT INTO @Sizes SELECT 1,100.00,'Y:' UNION ALL SELECT 2,80.85,'Z:'
[Code] ....
-- My output should look like
col1 , val , path A 50 Y: B 2.5 Y: C 51 Z: D 2.6 Y: E 52 Z: F 2.7 Y:
So how to use these three values in the where condition when i use the where clause i have put in condition it will give me the subquery returns more than one value,so how should i use this scenario to accomplish this output?
I am using stored procedure to load gridview,i want to show row specific values in coloumns , as i an working on daily timetable of college and There are three tables Week_Day,Daily_Timetable & Subject.Daily_Timetable has data which has week_day,class_id,Subject_id,Period_No.
Each day has 6 periods and each period is mapped with subject in daily timetable.From below sql i am getting 6 rows of monday.
But i want to show in a row weekname,period1_subject_id(Period_No=1),period2_subject_id(Period_No=2),period3_subject_id.......upto period6_subject_id.
Please see my query below:-
SELECT Week_Day.Week_Day_name, Subject.Subject_Code, Daily_Timetable.Period_No FROM Week_Day LEFT JOIN Daily_Timetable ON Week_Day.Week_Day_Id = Daily_Timetable.Week_Day_Id and Daily_Timetable.Class_Id=6 LEFT JOIN Subject ON Daily_Timetable.Subject_Id = Subject.Subject_Id order by Week_Day.Week_Day_Id ,Daily_Timetable.Period_No
I have the following report I need to create with 2 parameters. An equal OR not equal. I need the report to have a drop down that has equal to '1024' or a drop down option that IS NOT equal to '1024'. I also need the WHERE clause to return the equal or not equal based on the user selection inside of SSRS.
SELECT user1 AS [Company], reference AS [PAI_REF], statenumber, LEFT(user4, 7) AS [Supplier Code], user4 AS [Company Information], user8 AS [Transaction Type], user2 AS[Invoice Number], --CONVERT(VARCHAR,CONVERT(Date, user3, 103),101) AS [Invoice Date], [routeName] AS [Route], username AS [User Name]
I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :
declare @id varchar(50) set @id= '3,4,6,7' set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype select *from ehsservice where id in(@id)
But this query throws following error message:
Conversion failed when converting the varchar value '3,4,6,7' to data type int.
I developed the following T-SQL query that runs successfully, but I was looking for a more efficient and concise way to do this. Is there a CTE that can replace all of these case statements? I've updated my query as below. Although this sample query works, it's not working for my real data. Instead, I get an error. At the bottom is the error part of my real query.I copied all of the tables from the first query block below. But when I wrote the bottom query block, it underlined in red the words "answer" and "question." It says "Invalid column name".
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#hard_values') ) DROP TABLE #hard_values; if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#dummy_data') ) DROP TABLE #dummy_data; if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#temp')
I have SQL 2000 server machine on our network. I can connect to our existing SQL database and display data from tables etc, but when I try and edit the data using the detailsView it wont let me. No error msg, it just does not update the field. However if I create a new record using the detailsView, I can edit it. Its just older records that were created previously that it wont let me edit. Is this a bug?, A compatibility issue?, Any Ideas? O_F
I have been asked to create PK on many tables using a query on all tables where we do not have clustered indexes. Some of the tables contains PK but non-clustered. If in a table there are no PK, then how to decide on which column PK can be created? can we do it with the query without data loss and without human intervention?
below is some code I use to identify where a patient has attended the ED department whilst also admitted as an Inpatient. This report works fine. However while most of the results are recording issues to be corrected some of them are real and as so can be excluded from the report.
Is there a way I can build in an exclusion table which would include:
SELECT IP_ADMISSION.HeyNo AS HEYNo ,IP_ADMISSION.NHSNo2 AS NHSNo ,IP_ADMISSION.Forename AS Forename ,IP_ADMISSION.Surname AS Surname ,IP_ADMISSION.SourceAdmNatCode AS SourceAdm
I'm trying to rename a table with date suffix at the end of the table name, and drop the date suffix table which is greater than 7 days. for that I have the below sql, I have not included the drop syntax in this.
I'm not able to rename with the date suffix in the below sql, syntax error at '+'
DECLARE @TPartitionDate date IF EXISTS (SELECT * FROM sysobjects WHERE Name = 'IIS_4') BEGIN SELECT @TPartitionDate = MAX(PartitionDate) FROM PartitionLog (NOLOCK) EXEC sp_rename 'IIS_4','IIS_4_'+ @TPartitionDate END
I have the following table (Table does not have unique key id )
Last Name First Name DATE Total-Chrg
Jaime KRiSH 5/1/2015 -4150.66 Jaime KRiSH 5/1/2015 1043.66 Jaime KRiSH 5/1/2015 1043.66 Jaime KRiSH 5/1/2015 4150.66 Jaime KRiSH 5/3/2015 4150.66 Peter Jason 5/1/2015 321.02 Peter Jason 5/1/2015 321.02 Peter Jason 5/23/2015 123.02
I want the results to be in following way
Uniq ID Last Name First Name DATE Total-Chrg
1 Jaime KRiSH 5/1/2015 -4150.66 2 Jaime KRiSH 5/1/2015 1043.66 2 Jaime KRiSH 5/1/2015 1043.66 3 Jaime KRiSH 5/1/2015 4150.66 4 Jaime KRiSH 5/3/2015 4150.66 5 Peter Jason 5/1/2015 321.02 6 Peter Jason 5/1/2015 321.02 7 Peter Jason 5/23/2015 123.02
May be we may do by dense_rank or Row_Number, but I couldn't get the exact query to produce based on the above table values. There are some duplicates in the table(which are not duplicates as per the Business). For those duplicated Unique ID should be same(Marked in Orange Color which are duplicates).
Currently I have a column with multiple postcodes in one value which are split with the “/” character along with the corresponding location data. What I need to do is split these postcode values into separate rows while keeping their corresponding location data.
For example PostCode Latitude Longitude 66000/66100 42.696595 2.899370 20251/20270 42.196471 9.404951
Would become PostCode Latitude Longitude 66000 42.696595 2.899370 66100 42.696595 2.899370 20251 42.196471 9.404951 20270 42.196471 9.404951
I am trying to audit data quality based on some defined data quality rules. The rules are stored in tables and processed using stored procedures. I am facing a problem while generating audits. Let's say I am trying to audit data in OrderDetail table. The table design is mentioned below...I inserted some sample data into the table using RedGate data generator.The audit table output I am expecting is as mentioned in the screenshot below
Its the PrimaryKeyAttributeValues column I am facing problems with. I am using STUFF function within a dynamic SQL query to get the primary key's as a list of comma separated values.
Basically it's converting certain row values to new column. Every PloicyNum will have 1001 to 1006 Fixed InsCode values as a group.
Rule-1: InsCode value 1001 should always mapped to 1005 InsCode value 1002 should always mapped to 1006 InsCode value 1003 should always mapped to 1004
Rule-2: For a policyNum, If any Inscode value is missed from the group values 1001 to 1006, still need to mapped with corresponding values as shown in Rule-1
In the above sample data..
for PolicyNum - 1ABC20 , group values 1003,1006 are missing for PolicyNum - 1ABC25 , group values 1002,1003,1004,1005,1006 are missing
Create Table sampleDate (PolicyNum varchar(10) not null, InsCode Varchar(4) not null) Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1001')
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1002') Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1003')
I am new to use MERGE statement. The MERGE cannot find any match Cardnumber in the target table. It inserts row into an existing row on the target table causing SQL rejected with duplicate key not allowed. The CardNumber is defined as a primary key on the target table with no duplicate allowed. Below snippet stop when MERGE insert a row exists on the target. The source table contains multiple rows with the same Cardnumber because it is a transactional table with multiple redemptions.
If MERGE cannot handle many (source) to one (target) relationship, what other method that I can change to in order to update the target GiftCard table which keeps track of gift card balance?
Below is the error message:
Msg 2627, Level 14, State 1, Line 5 Violation of PRIMARY KEY constraint 'PK_GiftCard'. Cannot insert duplicate key in object 'dbo.GiftCard'. The duplicate key value is (63027768).
I'm trying to add random dates to date column in existing table, but these need to be week days (Mon-Fri).I'm a beginner in TSQL, worked with MS Access many years - in Access I used to do something a bit different:
DateAdd("d",(Int((5*Rnd([ID]))+1)),#31/08/2015#) Table had ID, I gave a date it would start from (31/08/2015) and then range of ID to apply new date: UPDATE table1 SET table1 .date = DateAdd("d",(Int((5*Rnd([ID]))+1)),#31/08/2015#) WHERE (((table1 .ID) Between 1 And 5456));
This was applying random dates in range of 31/08/2015 + 5 days, so I could give a starting date of Sunday to get random dates populated over given IDs from Monday to Friday.Now, how can I do it in TSQL?I have a table with ID and dates column. I would like to apply new random dates from some range, but making sure they will be week days.
Combination of Student_Id, Subject_Id and Quarter columns is the primary key. One student can take one subject in a quarter. Now the new requirement is a student can take multiple subjects in a quarter. So need to add another table like below:
NEW table name: Student_Subject and column are below: Student_id Subject_Id Quarter1
All the above three columns combination is primary key.
After the new table Student_Subject created, remove Subject_Id column from Student table.
When the user clicks on a button after selecting multiple subjects and provide col1 and col2 data then one row gets inserted into Student table and multiple rows gets inserted into Student_Subject table.
Is there any other table design that satisfies one student can take multiple subjects in a quarter?
I have some transactions with the same card number that needs to add value amount to its existing balance. For example:
Card Number Balance Amount Issue Date Issue Branch. 4000111122223333 $100.00 10/1/2015 123 <= This is an existing row in Card Number SQL table.
Now, the same card number with additional $50 dollars that I want to add to this card number to make the total to become $150. This additional $50 is from another transaction table. On the contrary, I will have -$20 from the same card number in different transaction that I will need to deduce $150-$20 to become $130. How can I update the card number table with debit/credit transactions to keep the outstanding balance?
In y sql server table has millions of records available. I don't want to drop the tables.
My requirement is I want to change the column order of an existing table. some tables I am able to saving on design window Like Below image.
Even I had generate a script and using that script trying to execute on Management Studio but unable to saving the new column orders. I am getting the Timeout expired error after couple of minutes. How can we save the orders without dropping the table !
We are planning to convert or change all existing Traces to Extended Events in SQL server 2012. What is the procedure to convert custom traces. We have already created some below custom traces: like this we are planning to convert for all servers.
The objective is to identify orders where an order fee has been applied incorrectly. I have multiple orders per customer, my table contains an orderID and a customerID. Currently if the customer places additional orders before the previous orders have been closed/cancelled, then additional fees are being applied.
Let's say I'm comparing order #1 to order #2. I need to identify these rows where the following is true:-
The CustID is the same.
Order #2 has a more recent order date.
Order #2 has a FeeDate Before the CancelledDate of Order #1 (or Order #1 has no cancellation date).
So in the table the orderID:2835692 of CustID: 24643 has a valid order fee. But all the subsequently placed orders have fees which were applied before the first order was cancelled and so I want to update the FeeInvalid column with a 'Y'. The first fee will always be valid.
I think I understand why the code I am trying doesn't achieve the result I want but I can't figure out how to write it correctly. Below is one example of code I've tried and also code to create the table and insert some test data.
update t1 SET FeeInvalid = 'Y' FROM MockData t1 Join MockData t2 on t1.CustID = t2.CustID WHERE t1.CustID = t2.CustID AND t2.OrderDate > t1.OrderDate AND t2.FeeDate > t1.CancelledDate CREATE TABLE [dbo].[MockData]( [OrderID] [float] NULL,
I have a stored procedure that selects the unique Name of an item from one table.
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
