I need a query to produce permutation combination.
declare @t2 as table (tab varchar(100)) insert into @t2 values ('V') insert into @t2 values ('VL') insert into @t2 values ('1099') insert into @t2 values ('VOI')
declare @t1 as table (tab varchar(100)) insert into @t1 values ('I') insert into @t1 values ('U') from the above I need following output (attached output),
iam having some strange problem. iam searching for records between from date and todate. this query works well except these conditions
a) if i give input say fromdate-2/2/2007 and todate-8/2/2007, the query goes for infinite execution time(pls note the difference between twodates is less than or equal to 6 months)
b)whereas if i give fromdate-2/2/2007 and todate-10/2/2007, the query works fine.(more than 6 months interval) seems strange for me.
pls if anyone came across these kind of errors and solved pls help me. Thanks in advance
I would like to pull all the columns from a table where the date column is within 6 months from the max date (i.e. Jul, Aug, Sep, Oct, Nov, & Dec). In addition to that, I would like to pull another column -the summary column - from the same table where the date = max(date) (Dec only).
I have written 2 queries and they produce the correct data. However, I don't know how to combine them into one resultant table. I tried to do a left join and had difficulties dealing with the different where statements from the 2 queries..
Here is query #1:
select investor, full_date, month_end_summary, category, loan_count from cust_table where datediff(month,full_date,(select max(full_date) from cust_table)) < 6 group by investor, full_date, month_end_summary, category, loan_count order by investor, full_date
Here is query #2:
select investor, full_date, month_end_summary from cust_table where datediff(month,full_date,(select max(full_date) from cust_table)) =0 order by investor, full_date
Can they be combined into one query to produce one result table??
I have to produce a report to calculate no of days based on user input start date and end date.
say for eg: in the tables I have emp name user 'Phani' started work from - EStart 20/11/2014EEnd 10/01/2015 - total days --datediff within his work period he did different roles:
PhaniMarketing (prSt Date) 20/11/2014prE date (28/11/2014) Total 9 days PhaniAdmin (prSt Date) 29/11/2014prE date (20/12/2014) Total 22 days PhaniCRM (prSt Date) 20/12/2014prE date (10/01/2015) Total 22 days Total days 53 Days for this :
[code]...
HOW CAN I USE Case statement to calculate days for given start date and end date. I have to include all three totals, 1 for Job dates, 2, subjobs dates, 3 cal of days for a requested period.
I am creating a report query that returns all unreconciled P/O lines. I am near completion but I am unable to find a way to remove the reconciled records.
I have included a script to produce some sample table, data & query.
The recordset dispalys 6 rows. All reconciled Supplier Invoices are duplicated and have transaction codes 40, 50 and reconcile code of 9 (5024, 921689471).
All unreconciled only appear once and have transaction codes 40 and reconcile code of 0 (4835 & 921978016). These are the only records that I want to show.
I have a column of type Decimal(14, 4) in my SQL server 2005 database. When producing the result set on this column, i need to convert the values to varchar datatype and also i do not want the trailing zeroes to be displayed. For eg: If the value stored is 98.7500, my select query should provide an output of 98.75. Similarly if the value stored is 98.0000, my select query should output 98.
I tried converting the value to "float" and then assigning to varchar data, however doing so i am incurring precision loss in some scenarios See below:
Declare @ele as varchar(25)
Select @ele = Convert(float, 99999.9990)
select @ele
Output I received: 100000 Output I wanted: 99999.999 Are there any in-built functions in SQL to achieve what I need?
I have a SQL 2012 database that has 10 tables. One of the tables is populated by manual import from CSV file. Each time a user calls custom ASP.NET code., records get inserted into a table called forecast_data with incremental increase in FileID. So first import has FileID of 1, second import has FileID of 2 etc.
What I am trying to do is only keep the data that has the highest FileID (MAX(FileID). I would like to write a store procedure that removes all older data once a new import is written into the table.
In QA we have a two-node cluster with four instances of sql. In trying to add a fifth, I was given an IP address already in use so the install hung.
I removed it from the cluster but it is still there in the registry etc on the node I was working on.
I read about using the maintenance tab of the sql server install to "remove a node" but the terminology is confusing. To me a node is a physical server and an instance is an instance of sql server -- not the same at all but they are often referred to as the same thing.
I definitely don't want to remove one of the servers from the cluster.
BEGIN TRANSACTION Copy records from live to archive END TRANSACTION with commit or rollback execute sproc to write audit log with success or fail IF transaction was committed BEGIN TRANSACTION Delete records from live the archive END TRANSACTION with commit or rollback execute sproc to write audit log with success or fail End IF
END TRANSACTION OUTERTXN with commit if both inner transactions were successful or rollback if either failed
If either inner transaction rolled back execute sproc to write audit log saying whole process is rolling back End IfMy problem is that if the outer transaction rolls back then I am losing the two audit records because they are part of the transaction scope. I want these executes to commit even if the master transaction fails.
When you set the results from a query to text, very often you end up with a format like:
job_id name ---------------------------------------------------------------------- ------------------------------------------------------------------------------------------------
Is there a way to get rid of the extra space between two columns? Sometimes it's more than the width of my screen between columns
I have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'
Example Record 1. Elland **REQUIRES BOOKING IN*** Example Record 2. Theale, Nr Reading, Berkshire Example Record 3. Stockport
How do I achieve this in a CASE Statement?
The following two case statements return the correct results, but I some how need to combine them into a single Statement?
,LEFT(Address ,CASE WHEN CHARINDEX(',',Address) =0 THEN LEN(Address ) ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'
,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0 THEN LEN(Address) ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'
I am trying to update records based on the results of a query with a subquery.
The result set being produced shows the record of an item number. This result produces the correct ItemNo which I need to update. The field I am looking to update is an integer named Block.
When I run the update statement all records are updated and not the result set when I run the query by itself.
Below is the code I am using in an attempt to update the block column but it updates all records and not the ones which I need to have the Blocked field set to 1.
Update #items set Blocked = 1 Where Exists ( SELECT ItemNo=MAX(CASE rn WHEN 1 THEN ItemNo END) --,SearchNo --,COUNT(*)
[Code] ...
Why is the update changing each record? How can I change the update to choose the correct records?
CREATE Table #Table1 ( ID INT, Name VARCHAR(50), Class VARCHAR(10) ) INSERT INTO #Table1 Select 1, 'name1', 'a' UNION ALL Select 2, 'name1', 'a' UNION ALL
[Code] ....
Is it possible to have each name and its corresponding class in a single line separated by commas to give a result like the one below in #table2 ?
CREATE Table #Table2 ( ID INT, CommaSeparated VARCHAR(100) ) INSERT INTO #Table2 Select 1, 'name1, a' UNION ALL
[Code] ...
What I have
Select * FROM #Table1
Final Result Select * FROM #Table2
Note that I still want to see all the IDs regardless.
If that is not possible to see all the IDs, I think the results below in #Table3 should suffice.
CREATE Table #Table3 ( CommaSeparated VARCHAR(100) ) INSERT INTO #Table3 Select 'name1, a' UNION ALL Select 'name2, b, c, d' UNION ALL Select 'name3, e, f' Select * FROM #Table3
I wanted to know the best way to achieve the following results. I have a table that I need output sequential range of vouchers in a table. For instance I have the following data in a column called vouchers. The output will consist of a years worth of vouchers, so voucher numbers may contain gaps and so the need to have a sequential range that has a From and To output. The query needs to know the min and max within that numerical range and then output the next min and max range until it gets to the end.
The data looks like: ABCD-001869202 ABCD-001869203 ABCD-001869204 ABCD-001869205 ABCD-001869209 ABCD-0018692010 ABCD-0018692011 ABCD-001869309 ABCD-001869310 ABCD-001869311 ABCD-001869312 ABCD-001869313 ABCD-001869314
Desired out put:
From To ABCD-001869202 ABCD-001869205 ABCD-001869209 ABCD-0018692011 ABCD-001869309 ABCD-001869314
I have tried the following, but it does not quite do what I need it to do, so not sure if I am taking the right approach:
SELECT voucher vouchers,right(voucher, charindex('-', voucher) + 3) voucher INTO #tempVoucher FROM LEDGERJOURNALTRANS where TRANSDATE between '10/1/2013' and '7/31/2014' and VOUCHER like 'APIN%'
I want to write a query that will cycle through the results and if it comes across another record that has a matching Table.ID I want to exclude that row from the result set.
I am not all too familiar with how to use either a Case or If..Else Statement within a Sql statement that would accomplish this.
I am trying to produce a query result that will be using a Case statement to determine values based on scores in a table for each row. The result needs to be exported to be used to upload to a state reporting website. My problem is that the state requires in the CSV file that is uploaded a lot of fields that we do not actually have in the database table we are creating the result set from. After I receive my result set using the Case statement, is there a way to add additional columns that don't actually exist in a table so I can export directly from SQL?
I am unable to update the data using record by record below scenario.
Required output:
patient will able to Admit/Re-admit multiple times in hospital, if a patient readmitted multiple times in hospital after the first visit, first visit record will get Re-admission=0 and Index=1. This visit should cal Index_Admission of that patient. using this index_admission should calculate the 30-day readmission.
Current Output:
Calculation: From index_admission discharge date to next admit_visit date,
1) if the diff is having less than 30 days, readmission=1 and Index=0
else readmission=0 and Index=1 should be update.
For checking this every time should check using the latest index_admission discharge_date.
To get this result i written below logic, but it's updating readmission=0 and Index=1 after 30-day post discharge of using first index admission.
UPDATE Readmission SET Index_AMI = (CASE WHEN DATEDIFF(DD, (SELECT Sub.Max_Index_Dis FROM (SELECT Patient_ID, MAX(Discharge_Date_Time) Max_Index_Dis FROM Readmission WHERE Index_AMI = 1 AND FPR.Patient_ID = Patient_ID GROUP BY Patient_ID) Sub) , FPR.Admit_Date_Time) between 0 and 31 THEN 0 ELSE 1 END),
I want to achieve something like below. It should subtract the '13' row to '6' row and provide another column with the result. the '6' and '13' category code share the same Key.
I am trying to create a SSIS package that will create a csv of a dataset for daily events in the database. However there will be days that there was no activity and thus an empty dataset. The package still runs fine but I want to stop the package if the dataset is empty.
FLOW:
DATA FLOW task: get daily data and put in CSV file
FTP TASK: upload the file to FTP server
MAIL/Copy file task: Move the file and then send a confirmation mail on task completion status.
Pretty simple and it all works great, I do have a few complexities in there. What I would like to add and I am at a loss is at the beginning, if the OLE DB Task resultset is empty then move to Mail Task otherwise process normally. I have tried conditional split, derived columns, the only thing I haven't tried in Script task and am not sure about that yet.
DOC_NO // REV_NO // FILE_NAME ABC123 // A // abc123.pdf ABC123 // B // abc123_2.docx ABC124 // A // abc124.xlsx ABC124 // A // - ABC125 // A // abc125.docx ABC125 // C // abc125.jpg ABC125 // C // abc125.docx ABC125 // C // - ABC126 // 0 // - ABC127 // A1 // abc127.xlsx ABC127 // A1 // abc127.pdf
I'm looking to select all rows where the DOC_NO and REV_NO appear only once.(i.e. the combination of the two values together, not any distinct value in a column)
I have written the sub query to filter the correct results;
SELECT DOC_NO, REV_NO FROM [MYTABLE] GROUP BY DOC_NO, REV_NO HAVING COUNT(*) =1
I now need to strip out the records which have no file (represented as "-" in the FILE_NAME field) and select the other fields (same table - for example, lets just say "ADD1", "ADD2" and "ADD3")
I was looking to put together a query like;
SELECT DOC_NO, REV_NO, FILE_NAME, ADD1, ADD2, ADD3 FROM [MYTABLE] WHERE FILE_NAME NOT LIKE '-' AND DOC_NO IN (SELECT DOC_NO, REV_NO FROM [MYTABLE] GROUP BY DOC_NO, REV_NO HAVING COUNT(*) =1)
But of course, DOC_NO alone being in the subquery select is not sufficient, as (ABC125 /A) is a unique combination, but (ABC125 /C) is not, but these results would be pulled in.
I also cannot simply add an additional "AND" clause on its own to make sure the REV_NO value appears in the subquery, because it is highly repetitive and would have to specifically match the DOC_NO)
What is the easiest way of ensuring that I only pull in the records where both the DOC_NO and REV_NO (combination) are unique, or is there a better way of putting this select together altogether?
I am running backups on SQL Sever express and I take backups via the batch file executing the TSQL as below. This works perfectly fine, I just need to be able to spool out the backup completion log. What TSQL command can I use to do that?
My batch file looks like this:
@echo off sqlcmd -S SERVER01 -E -i H:master_scriptsTOM_FAM_log.sql
Which will the call the TOM_FAM_log.sql :
DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name SET @path = 'H:BackupTOM_FAM'
[Code] ....
Usually if we schedule the backups using the maintenance plan, we can chose reporting options that can spool the result of the backup to a text file. What is the T-SQL for the spool report to a text file.
This option produces a file that writes logs with the details I posted below. I would like to do the same or similar using T-SQL in my code.
Microsoft(R) Server Maintenance Utility (Unicode) Version 11.0.3000 Report was generated on "SERVER01". Maintenance Plan: Backup logs Duration: 00:00:00 Status: Succeeded.
The generated script output is in a order of stored procedure first and then table.
REQUIREMENT: My stored procedure is dependent on table. So, I need the table script first and then stored procedure.
Note: I can generate two separate scripts for table and stored procedure, But in a just curiosity to know, Is there any way, can we re order the Generate Scripts output in SQL Server.
