tableA tableB IdPrice price id tax IdPrice ---------------------- ------------------------------------ 4 100 1 20 4 ------------------------ ------------------ ------------------ 5 150 2 10 6 ------------------------ ------------------ ------------------ 6 270 ------------------------ result = price id tax ---- --- ---- 100 1 20 150 2 10 270 null null my Query SELECT price,id,tax FROM tableB INNER JOIN tableA ON tableA.IdPrice= tableB.IdPrice but this result price id tax ---- --- ---- 100 1 20 150 2 10
jeudi 31 mars 2016
how to select all value from two table
mercredi 30 mars 2016
An Error Occurred Attempting To Delete Row 6 - SSMS 2014
No Rows were deleted.
A problem occurred attempting to delete row 6. Error Source: Microsoft.sqlServer.Management.Data.Tools. Error Message: The updated rows has changed or been deleted since data was last retrieved.
Correct the errors and attempt to delete the row again or press ESC to cancel the changes(s).
Even my table consist the Primary Key and I don't know why this error occurs?
Taken the below SQL script by right clicking the table>>Script Table as>>Create To>>New Query for getting the structure of my table.
CREATE TABLE [dbo].[FO_Entry](
[Rec_Num] [int] IDENTITY(1,1) NOT NULL,
[E_No] [nvarchar](50) NULL,
[CA_Name] [nvarchar](50) NULL,
[Created_On] AS (getdate()),
[App_1] [int] NULL CONSTRAINT [DF_FO_Entry_App_1] DEFAULT ((0)),
[App_2] [int] NULL CONSTRAINT [DF_FO_Entry_App_2] DEFAULT ((0)),
[App_3] [int] NULL CONSTRAINT [DF_FO_Entry_App_3] DEFAULT ((0)),
[App_4] [int] NULL CONSTRAINT [DF_FO_Entry_App_4] DEFAULT ((0)),
[Tot] AS ((([App_1]+[App_2])+[App_3])+[App_4]) PERSISTED,
CONSTRAINT [PK_FO_Entry] PRIMARY KEY CLUSTERED
(
[Rec_Num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
How to replace instance of string in SQL Server when followed by another pattern?
I have a [message] column which can contain hostname information - which I need to replace and remove parts of.
The statement I wrote to do this was:
update table1
set message = replace(replace(message ,'RL','NN'),'.COMPANY.COM','')
where message is not NULL
So a hostname that appears as - RL12345.COMPANY.COM will return as NN12345.
The issue is that if 'RL' appears anywhere else in the message column, it will be erroneously replaced. Is there a way to conditionally replace using a regex? I could verify that the number of numerical characters between RL and .COMPANY.COM was always between 7-9 for example.
To clarify, although RL will always be the beginning of the hostname string, it may not (and probably will not) be the beginning of the entire string in the message column.
e.g.:
"Tried to access RL12345.COMPANY.COM with no success"
There may also be multiple hostname instances in the one cell, all instances must be tansformed.
mardi 29 mars 2016
MSarticles table keeping ghost rows
I have SQL Server 2005 running on a Windows 7 machine that is replicating data being logged into SQL Server 2005 on a Windows XP machine. I'm having a problem that when subscriptions are dropped, about 1% of the time (works out to about once a month) the corresponding row doesn't get removed from the MSarticles table, leaving a 'ghost' subscription.
A lot of the table structure was set up by the software manufacturer, and I'm not able to change it without breaking the software's job of logging all our sensor records every 10 seconds.
To keep the replication on the currently active set of tables (why the software people set up rotating tables is beyond me) I have a trigger that, when the software moves to the next table in the rotation (which it does every two days):
- Removes the clustered index created by the software
- Adds a primary key (since replication can't be done on a table without one)
- Adds a subscription to the newly active table with
sp_addarticleandsp_addsubscription(this won't start replicating until the snapshot agent is run) - Drops the replication on the table being rotated out with
sp_dropsubscriptionandsp_droparticle - Removes the primary key from the older table
- Adds the clustered index to the older table (so the software will interact with it correctly when it comes around again in the rotation)
- Checks a value in a one-row table; if the value is less than 7 (the number of active tables; they are all moved in the rotation at the same time) it increments the value; if equal to 7 it resets the value to 1 and runs the snapshot agent
The ghost rows in the MSarticles table cause an error when removing the primary key, which causes the entire trigger to be rolled back, and then the replication is not backing up everything we need it to be backing up. I've been manually deleting the ghost rows as I notice them and updating all the affected tables that got missed in the replication, which is not ideal.
Two questions:
- Any ideas on why the ghost rows are getting left in the MSarticles table and how to prevent that?
- Would it be an OK workaround to add a step in my trigger to run a
DELETE... WHERE destination_object = 'MyTableName'statement on the MSarticles table to delete any potential ghost rows? (This would be after runningsp_droparticleand before removing the primary key; I haven't tried this yet because I'm a little hesitant to set up something that messes with system tables on a recurring and unattended schedule. And also I'm not sure how to reference the MSarticles table on the distributor - the Win7 machine - from the publisher where the trigger is running - the WinXP machine; I can probably figure it out but would rather try to prevent the ghost rows before tackling this workaround.)
lundi 28 mars 2016
Generate month and year from date and store automatically in table
Im working on sql server 2005, i have a column 'Date' of datatype datetime and want to extract month and year separately and store it in two separate columns in the same table as 'Date'. How do i do this and make it happen automatically everytime a new entry is entered where i dont have to manually input month and date.
samedi 26 mars 2016
.trn vs .bak backups of a database, both needed?
I've just started working at a company where the guy who left told me they use transaction backup (I'm quite new to SQL Server, never heard of this before) and full backup for the 6 servers they have.
The servers use replication, one is a publisher and all others are suscribers.
In every server are jobs that run at night that create a .bak file (11GB) and around 25 .trn each night.
Are these .trn really necessary? The guy told me that if the trn backups are not done the server starts failing because "it fills" which I don't know what it means now that I think about it.
Do we really need a backup on every server? or should we only backup in the publisher?
Also, during the backup process the replication starts failing and I have to manually hit the "Restart" option every morning.
jeudi 24 mars 2016
calculate vehicle trip
I want to calculate the vehicle trip but facing problem. No Groupby is working and also facing to create logic.
select z.ZoneId, o.ObjectId,o.Number,o.Comment,z.Name StartTrip,zs.TimeFirst,zs.Inside,z.ZoneGroupId
from dbo.Object o join
dbo.GroupObject gobj on o.ObjectId = gobj.ObjectId join
dbo.[Group] g on gobj.GroupId=g.GroupId join
dbo.[ZoneState] zs on o.ObjectId=zs.ObjectId join
dbo.[Zone] z on zs.ZoneId=z.ZoneId
where (z.ZoneGroupId=1096 OR z.ZoneGroupId=1095) and o.ObjectId=3414 and (convert(date,zs.TimeFirst)>='2016/2/15') and (convert(date,zs.TimeFirst)<='2016/3/24')
ORDER BY o.Number
Current Result
ZoneId Objectid Number commants StartTrip TimeFirst Inside
540 3414 VT-0678T VOLVO Oil Fd 2016-03-17 17:10:31 1
540 3414 VT-0678T VOLVO Oil Fd 2016-03-18 14:15:19 0
543 3414 VT-0678T VOLVO Khour 2016-03-19 09:38:30 1
543 3414 VT-0678T VOLVO Khour 2016-03-19 11:55:04 0
540 3414 VT-0678T VOLVO Oil Fd 2016-03-21 07:32:58 1
540 3414 VT-0678T VOLVO Oil Fd 2016-03-21 13:34:25 0
543 3414 VT-0678T VOLVO Khour 2016-03-22 07:29:56 1
543 3414 VT-0678T VOLVO Khour 2016-03-22 10:09:58 0
540 3414 VT-0678T VOLVO Oil Fd 2016-03-22 15:22:41 1
540 3414 VT-0678T VOLVO Oil Fd 2016-03-22 16:45:02 0
543 3414 VT-0678T VOLVO Khour 2016-03-24 07:41:27 1
Required Result
ObjId Number StartTrip EndTrip TimeFirst TimeLast
3414 VT-0678T Oil Fd Khour 2016-03-18 14:15:19 2016-03-21 07:32:58
3414 VT-0678T Oil Fd Khour 2016-03-21 13:34:25 2016-03-22 15:22:41
3414 VT-0678T Oil Fd Khour 2016-03-22 16:45:02 cont
mercredi 23 mars 2016
Store a generated SQL String into a table via Stored Procedure
I have an application where a user fills out a form which in turn generates a pretty lengthy Query. Well as part of a usage statistics program, I track and log everything our users do in one spot in the database. I have a stored procedure that inserts the appropriate values into this database, however the issue I'm running into is inserting the actual Query that took place.
Stored Procedure
USE [Worktool]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Insert_Usage_Statistics]
-- Add the parameters for the stored procedure here
@Query_VC AS VARCHAR(MAX) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO Usage_Statistics_T
(
Query_VC
)
VALUES
(
@Query_VC
)
END
Example of it being called
exec Worktool.dbo.Insert_Usage_Statistics
@Query_VC = 'SELECT col1 FROM tbl1 INNER JOIN tbl2 ON tbl1.id = tbl2.id WHERE tbl1.id IN ('1','2')'
The error I'm receiving is around the IN ('1','2') syntax.
sql server agent job fails while execute
i am trying to execute ssis package in sql server agent job . while start the job am facing the sql job fail. below i attached the screenshot of the error. NOTE : it is running fine , if i execute ssis package.
mardi 22 mars 2016
How to get number of false in every column of a table?
I have a table say T_myTable it has 5 columns and all have some values either true or false.
--------------------------------
col1 | col2 | col3 | col4 | col5
--------------------------------
true | false|false|true |false
false| true |false|false|false
true | false|false|true |false
false| false|false|true |false
I want to get result as:-
col1 | col2 | col3 | col4 | col5
--------------------------------
2 | 3 |4 |1 |4
Where these numbers here are numbers of false.
lundi 21 mars 2016
SQL Server 2005 - Incorrect syntax near the keyword 'SET'
I generated in SQL Server Management Studio 2014 this automatic update script (after visual table editing):
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Rilevamenti ADD
DataScadenza datetime NULL
GO
DECLARE @v sql_variant
SET @v = N'Data di scadenza rilevata.'
EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'Rilevamenti', N'COLUMN', N'DataScadenza'
GO
ALTER TABLE dbo.Rilevamenti SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
select Has_Perms_By_Name(N'dbo.Rilevamenti', 'Object', 'ALTER') as ALT_Per, Has_Perms_By_Name(N'dbo.Rilevamenti', 'Object', 'VIEW DEFINITION') as View_def_Per, Has_Perms_By_Name(N'dbo.Rilevamenti', 'Object', 'CONTROL') as Contr_Per
Now I want to run the script on another database running on a SQL Server 2005 instance but when I run it I get the error:
Incorrect syntax near the keyword 'SET'
Where is the syntax error?
No-break space not recognized as white-space in SQL Server
is there any settings in SQL server 2005, so that no-break space will be recognized as white space in SQL Server
vendredi 18 mars 2016
SQL: True/False/Null SUM of a Bit field
I have the following table on MS-SQL Server 2005.
CREATE TABLE tblData ( ID int NOT NULL PRIMARY KEY, FOOBAR1 bit, LOCATION varchar(50) ) GO INSERT INTO tblData (ID, FOOBAR1, LOCATION) VALUES (1,'True','Paris') GO INSERT INTO tblData (ID, FOOBAR1, LOCATION) VALUES (2,'True','New York') GO INSERT INTO tblData (ID, FOOBAR1, LOCATION) VALUES (3,'False','Paris') GO INSERT INTO tblData (ID, FOOBAR1, LOCATION) VALUES (4,'False',NULL) GO INSERT INTO tblData (ID, FOOBAR1, LOCATION) VALUES (5,NULL,'Paris') GO
tblCities looks like this:
CREATE TABLE tblCities ( cityName varchar(50) ) GO INSERT INTO tblCities (cityName) VALUES ('Paris') GO INSERT INTO tblCities (cityName) VALUES ('New York') GO INSERT INTO tblCities (cityName) VALUES ('London')
I want to have aggregates for the bit FOOBAR1 (True, False and NULL) So this is the SELECT statement that I've tried:
SELECT UPPER(tblCities.cityName) AS City ,SUM (CASE WHEN tblData.FOOBAR1 = 1 THEN 1 ELSE 0 END) AS Yes ,SUM (CASE WHEN tblData.FOOBAR1 = 0 THEN 1 ELSE 0 END) AS [No] ,SUM (CASE WHEN tblData.FOOBAR1 IS NULL THEN 1 ELSE 0 END) AS NoData FROM tblCities LEFT JOIN tblData ON tblCities.cityName = tblData.LOCATION GROUP BY tblCities.cityName
Results are the following:
City | Yes | No | NoData | LONDON | 0 | 0 | 1 | NEW YORK | 1 | 0 | 0 | PARIS | 1 | 1 | 1 |
Ideally, I want all five records in tblData to be accounted for in the SELECT statement:
City | Yes | No | NoData | LONDON | 0 | 0 | 0 | NEW YORK | 1 | 0 | 0 | PARIS | 1 | 1 | 1 | NO DATA | 0 | 1 | 0 |
Thoughts?
migrating sql server 2005 to 2014- general questions
My boss tasked me with doing research on migrating sql server 2005 to 2014. my first question is, is it really as easy as restoring into an old backup? No thing needs to be changed in terms of processes that load to the databases or components that look at the databases?
I'm completely new to this, obviously. I use SQL server management studio almost every day, yet I am still not familiar with anything else besides running a few simple queries. I've looked at this site here that finds all permissions/access for all users in a database. I've also run sp_who2 and: SELECT * FROM Master..sysprocesses order by spid
just to get an idea of all the processes going on. How would I see the websites that are pulling from the databases? I know we have a DB loader, how can I see that process? I can't quite get a grasp of the scope of this project.
Detect if Stored Proc is being called within a transaction
Ok - so the answer is probably 'everything is within a transaction in SQLServer'. But here's my problem.
I have a stored procedure which returns data based on one input parameter. This is being called from an external service which I believe is Java-based.
I have a log-table to see how long each hit to this proc is taking. Kind of like this (simplified)...
TABLE log (ID INT PRIMARY KEY IDENTITY(1,1), Param VARCHAR(10), In DATETIME, Out DATETIME)
PROC troublesome
(@param VARCHAR(10))
BEGIN
--log the 'in' time and keep the ID
DECLARE @logid INT
INSERT log (In,Param) VALUES (GET_DATE(),@param)
SET @logid = SCOPE_IDENTITY()
SELECT <some stuff from another table based on @param>
--set the 'out' time
UPDATE log SET Out = GET_DATE() WHERE ID = @logid
END
So far so easily-criticised by SQL fascists.
Anyway - When I call this eg troublesome 'SOMEPARAM' - I get the result of the SELECT back and in the log table is a nice new row with the SOMEPARAM and the in and out timestamps.
Now - I watch this table, and even though no rows are going in - if I am to generate a row, I will see that the ID has skipped many places. I guess this is being caused by the external client code hitting it. They are getting the result of the SELECT - but I am not getting their log data.
This suggests to me they are wrapping their client call in a TRAN and rolling it back. Which is one thing that would cause this behaviour. I want to know...
- If there is a way I can FORCE the write of the log even if it is contained within a transaction over which I have no control and which is subsequently rolled back (seems unlikely)
- If there is a way I can determine from within this proc if it is executing within a transaction (and perhaps raise an error)
- If there are other possible explanations for the ID skipping (and it's skipping alot like 1000 places in an hour. So I'm sure it's caused by the client code - as they are reporting successfully retrieving the results of the
SELECTalso.)
Thanks!
jeudi 17 mars 2016
Confused with the syntax of procedure
I have created a Stored procedure in which I am confused with the syntax whether properly closed or not.
For ex: for BEGIN/ END
Below is the procedure.
ALTER PROCEDURE [dbo].[Final_Leave_Allocate_CuttOff]
@Type nvarchar(10),
@Month int,
@Year int
AS
BEGIN
declare @actualMonth int
declare @actualYear int
declare @actuallastdate varchar(20)
declare @DAMonth int
declare @DAYear int
Declare @Final_LOP as numeric(5,2)
Declare @Final_LOPDays as numeric(5,2)
declare @checkRecord int
declare @NoOfDaysForDelayApproval numeric(5,2)
declare @Leave_Default numeric(5,2)
declare @status varchar(10)
declare @resig_date datetime
declare @dt_of_leave datetime
declare @emp_name varchar(20)
declare @dt_of_join datetime
declare @emp_card_no numeric(9)
declare @pl_days numeric(5,2)
declare @pl_days_opening numeric(5,2)
declare @Month_Diff int
declare @month1 numeric(5,2)
declare @month2 numeric(5,2)
declare @month3 numeric(5,2)
declare @month4 numeric(5,2)
declare @month5 numeric(5,2)
declare @month6 numeric(5,2)
declare @month7 numeric(5,2)
declare @month8 numeric(5,2)
declare @month9 numeric(5,2)
declare @month10 numeric(5,2)
declare @month11 numeric(5,2)
declare @month12 numeric(5,2)
declare @pl_sum numeric(5,2)
declare @Total_days numeric(5,2)
declare @Days_worked numeric(5,2)
declare @actualleavedays numeric(5,2)
declare @Final_PaidDayLop numeric(5,2)
declare @Opg_bal numeric(5,2)
declare @remaingPL as numeric(5,2)
declare @DelayedMonth int
declare @DelayedYear int
declare @LOP_value as numeric(5,2)
declare @emp_type varchar(2)
declare @emp_mkey int
declare @PL_Opening_2015 numeric(10,2)
declare @TOTAL_PL_Allocation_2015 numeric(10,2)
declare @PL_Alloc_2015_Month1 numeric(10,2)
declare @PL_Alloc_2015_Month2 numeric(10,2)
declare @PL_Alloc_2015_Month3 numeric(10,2)
declare @PL_Alloc_2015_Month4 numeric(10,2)
declare @PL_Alloc_2015_Month5 numeric(10,2)
declare @PL_Alloc_2015_Month6 numeric(10,2)
declare @PL_Alloc_2015_Month7 numeric(10,2)
declare @PL_Alloc_2015_Month8 numeric(10,2)
declare @PL_Alloc_2015_Month9 numeric(10,2)
declare @PL_Alloc_2015_Month10 numeric(10,2)
declare @PL_Alloc_2015_Month11 numeric(10,2)
declare @PL_Alloc_2015_Month12 numeric(10,2)
declare @TOTAL_OL_Alloc_2015 numeric(10,2)
declare @OL_Alloc_2015_MONTH1 numeric(10,2)
declare @OL_Alloc_2015_MONTH2 numeric(10,2)
declare @OL_Alloc_2015_MONTH3 numeric(10,2)
declare @OL_Alloc_2015_MONTH4 numeric(10,2)
declare @OL_Alloc_2015_MONTH5 numeric(10,2)
declare @OL_Alloc_2015_MONTH6 numeric(10,2)
declare @OL_Alloc_2015_MONTH7 numeric(10,2)
declare @OL_Alloc_2015_MONTH8 numeric(10,2)
declare @OL_Alloc_2015_MONTH9 numeric(10,2)
declare @OL_Alloc_2015_MONTH10 numeric(10,2)
declare @OL_Alloc_2015_MONTH11 numeric(10,2)
declare @OL_Alloc_2015_MONTH12 numeric(10,2)
declare @Mkey int
declare @Entry_Sr_no int
declare @dcount int
declare @TotalRecord int
declare @date_of_joining DATETIME
declare @setCount INT
declare @OnDate DATETIME
SET NOCOUNT ON;
set @Leave_Default=1.75
IF (@Month = 1)
BEGIN
set @actualYear = @Year - 1
set @actualMonth = 12
set @DelayedMonth = 11
set @DelayedYear = @Year - 1
END
ELSE
BEGIN
set @actualYear = @Year
set @actualMonth = @Month - 1
IF (@Month = 2)
BEGIN
set @DelayedMonth = 12
set @DelayedYear = @Year - 1
END
Else
BEGIN
set @DelayedMonth = @actualMonth - 1
set @DelayedYear = @Year
END
END
Print 'Actual Year ' + cast(@actualYear as VARCHAR)
Print 'Actual Month ' + Cast(@actualMonth as varchar)
Print 'Delayed Year ' + Cast(@DelayedYear as varchar)
Print 'Delayed Month' + Cast(@DelayedMonth as varchar)
DECLARE @DATE DATETIME;
SET @DATE = CAST(CAST(@actualYear AS VARCHAR)+'-'+CAST(@DelayedMonth AS VARCHAR)+'-'+ Cast(Day(DATEADD(DAY,-1,DATEADD(month,@DelayedMonth,DATEADD(year,@actualYear-1900,0)))) AS VARCHAR) AS DATETIME);
PRINT Convert(varchar(11),@DATE,103)
set @actuallastDate = convert(varchar(10),'1/'+Convert(Varchar,@actualMonth)+'/'+Convert(Varchar,@actualYear),103)
print @actuallastDate
IF(@Type = 'M')
BEGIN
Print 'Yes I am in the Monthly process'
SELECT @checkRecord = COUNT(Year) FROM p_leave_allocation where Year = @Year
IF(@checkRecord = 0)
BEGIN
Print 'Kindly Run Annual process first for creation of Blank Records for New Year.'
RETURN
END
declare monthly_Allocate cursor for
Select e.status,e.resig_date, dt_of_leave, e.emp_name,e.date_of_joining, e.emp_card_no,
a.pl_days,pl_days_opening,
a.month1,a.month2,a.month3,a.month4,a.month5,a.month6,a.month7,a.month8,a.month9,a.month10,a.month11,a.month12,--actual_pl,
a.month1+a.month2+a.month3+a.month4+a.month5+a.month6+a.month7+a.month8+a.month9++a.month10+a.month11+a.month12 pl_sum
from p_leave_allocation a join emp_mst e on a.emp_card_no=e.emp_card_no
where a.year=@actualYear
and (datediff(month,e.date_of_joining,convert(datetime,@actuallastDate,103)) >= 6
and datediff(month,e.date_of_joining,convert(datetime,@actuallastDate,103)) <= 36)
and (e.resig_date is null or (e.dt_of_leave is not null and e.dt_of_leave >= convert(datetime,@actuallastDate,103)))
and e.status in ('A','S')
and e.comp_mkey in (Select distinct m.comp_mkey from emp_mst e, company_mst c, P_Monthly_Must_Para_Hdr m where c.mkey = e.comp_mkey
and m.comp_mkey = e.comp_mkey and (m.process_date is not null
and (convert(varchar,(getDate())-5,103)) = convert(varchar,m.process_date, 103)))
order by 7,5
OPEN monthly_Allocate fetch next
FROM monthly_Allocate into
@status,@resig_date,@dt_of_leave,@emp_name, @dt_of_join,@emp_card_no,@pl_days,@pl_days_opening,
@month1,@month2,@month3,@month4,@month5,@month6,@month7,@month8,@month9,@month10,@month11,@month12,@pl_sum
WHILE @@FETCH_STATUS = 0
BEGIN
set @actualleavedays = 0
set @Final_PaidDayLop = 0
Print 'Faiz K.' + cast(@emp_card_no as varchar(10))
select @Total_days = Sum(total_day), @Days_worked = Sum(days_worked)
from emp_mon_day a
where a.emp_mkey = @emp_card_no
and a.month = @actualMonth and Year = @actualYear
group by emp_mkey
print @emp_card_no
print @Total_days
print @Days_worked
if(@Days_worked > 0)
BEGIN
Set @actualleavedays = (cast(@Leave_Default as numeric(18,2))/cast(@Total_days as numeric(18,2)))* cast(@Days_worked as numeric(18,2))
END
print @actualleavedays
DECLARE @sql NVARCHAR(MAX)
PRINT @sql
EXEC SP_EXECUTESQL @sql;
BEGIN
declare @totaldaysofmonth decimal(9,2)
set @totaldaysofmonth = DAY(DATEADD(ms,-2,DATEADD(MONTH, DATEDIFF(MONTH,0,@DATE)+1,0)))
select @NoOfDaysForDelayApproval=isnull(sum(Total_Days),0.00)
from XXACL_EMP_DELAY_APPROVAL_V
where DYear = @DelayedYear and DMonth = @DelayedMonth
and CardNo = @emp_card_no
print @NoOfDaysForDelayApproval
declare @DelayleaveAllocate decimal(9,2)
set @DelayleaveAllocate = ((1.75 / @totaldaysofmonth) * @NoOfDaysForDelayApproval)
print @DelayleaveAllocate
SET @sql = '';
print 'Yes 01'
print @DelayedMonth
declare @strmon varchar(100)
set @strmon= 'cast(month'+ CAST(@DelayedMonth AS VARCHAR(100)) + ' as numeric(10,2)) + cast(' + cast(@DelayleaveAllocate as varchar(50)) +' as numeric(10,2))'
print @DelayleaveAllocate
PRINT 'EMPLOYEE DATA ' + cast(@emp_name as char)
END
FETCH NEXT
FROM monthly_Allocate into
@status,@resig_date,@dt_of_leave,@emp_name, @dt_of_join,@emp_card_no,@pl_days,@pl_days_opening,
@month1,@month2,@month3,@month4,@month5,@month6,@month7,@month8,@month9,@month10,@month11,@month12,@pl_sum
END
close monthly_Allocate
deallocate monthly_Allocate
END -- END
ELSE IF(@Type = 'C')
BEGIN
Print 'Yes I am in the Current process';
set @OnDate = '2016-03-14';
DECLARE daily_Allocate CURSOR FOR
Select distinct c.emp_card_no, c.emp_name, c.Date_Of_Joining from emp_mst c
join emp_mon_day d
on c.emp_card_no=d.emp_mkey
WHERE Date_Of_Joining = CAST(FLOOR(CAST( DATEADD(month, -6, @OnDate) AS FLOAT ))AS DATETIME)
OPEN daily_Allocate
FETCH NEXT FROM daily_Allocate INTO
@emp_card_no, @emp_name, @Date_Of_Joining
WHILE @@FETCH_STATUS = 0
BEGIN
select @Total_days = Sum(total_day),@Days_worked = Sum(days_worked)
from emp_mon_day a
where a.emp_mkey = @emp_card_no
group by emp_mkey
PRINT 'Employee Card no = ' + cast(@emp_card_no as char)
PRINT 'Total days = ' + cast(@Total_days as char)
PRINT 'Days Worked = ' + cast(@Days_worked as char)
set @Final_PaidDayLop = 0;
set @TotalRecord = 0;
Select @Final_PaidDayLop =
isnull(sum(days),0)
from P_Emp_Del_App_Hdr c join P_Emp_Del_App_trl d on c.mkey=d.mkey
where c.delete_flag='N' and app_flag='Y' and c.year = @Year
and c.emp_mkey = @emp_card_no
set @setCount = 0;
Select @setCount = count(*) from emp_mon_day
where emp_mkey =@emp_card_no
Select @TotalRecord = ((1.75 * @setCount) / @Total_days) * (@Days_worked + @Final_PaidDayLop)
from emp_mon_day a where a.emp_mkey = @emp_card_no group by a.emp_mkey
PRINT 'Final Paid LOP ' + cast(coalesce(@Final_PaidDayLop,0) as char)
PRINT 'Total Record ' + cast(coalesce(@TotalRecord,0) as char)
PRINT 'Set Count ' + cast(coalesce(@setCount,0) as char)
FETCH NEXT FROM daily_Allocate INTO @emp_card_no, @emp_name, @Date_Of_Joining
END
CLOSE daily_Allocate
DEALLOCATE daily_Allocate
END END
Kindly let me know if there is mistake in Opening and closing of BEGIN/ END OR ELSE/IF
default sort order for a select query in SQL 2005 and SQL 2012
Is there a difference between default sort order for a select query in SQL 2005 and SQL 2012? I have a table variable with no primary key. When I give a select query on the table variable in SQL 2005, the records are selected and displayed in Alphabetical order as per one of the columns. In SQL 2012, the records are displayed in the same order as in the parent table.
connection failed issue while login the application
I am using jboss 4.0.5 and ms sql 2005 in my application, actually i can login with 'sa' user but after 20 or 30 mins if i login with same 'sa' user am getting this connection fail issue.Below is my log details...Please help
2016-03-17 07:56:52,775 DEBUG [org.jboss.resource.connectionmanager.IdleRemover] run: IdleRemover notifying pools, interval: 450000 2016-03-17 08:04:22,784 DEBUG [org.jboss.resource.connectionmanager.IdleRemover] run: IdleRemover notifying pools, interval: 450000 2016-03-17 08:11:52,793 DEBUG [org.jboss.resource.connectionmanager.IdleRemover] run: IdleRemover notifying pools, interval: 450000 2016-03-17 08:19:16,708 WARN [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Throwable while attempting to get a new connection: null org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'sa'.) at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.createManagedConnection(LocalManagedConnectionFactory.java:179) at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.createConnectionEventListener(InternalManagedConnectionPool.java:565) at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(InternalManagedConnectionPool.java:250) at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.getConnection(JBossManagedConnectionPool.java:491) at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnectionManager2.java:341)
how to assign the variable to the argument in execute process task?
i tried to pass the variable in argument (execute process task) . i read msdn article in that they told to pass like @variablename in argument.
i tried @variablename,@[User::variablename] but couldn't achieve. any suggestions ?
No output from SQL Server 2005 T-SQL and PDO when using SET
I am connecting to a SQL Server 2005 database using PDO and don't get output when running certain types of queries using PHP, but I do when running the queries directly in SQL Server Management Studio.
When I run this:
$conn = new PDO(...);
$statement = $conn->prepare("
DECLARE @testvar VARCHAR(10)
SELECT 'hello world'
");
$result = $statement->execute();
echo $result ? "Success" : "Failure";
echo "<br>";
print_r($conn->errorInfo());
echo "<br>";
die(print_r($statement->fetchAll()));
I get output.
Success
Array ( [0] => 00000 [1] => 0 [2] => (null) [0] (severity 0) [] [3] => 0 [4] => 0 )
Array ( [0] => Array ( [] => hello world ) ) 1
However, if I change the query to set @testvar to something, either inline:
DECLARE @testvar VARCHAR(10) = 'test'
SELECT 'hello world'
Or by using SET:
DECLARE @testvar VARCHAR(10)
SET @testvar = 'test'
SELECT 'hello world'
I no longer get "hello world" when running $statement->fetchAll(), and there does not seem to be an error:
Success
Array ( [0] => 00000 [1] => 0 [2] => (null) [0] (severity 0) [] [3] => 0 [4] => 0 )
Array ( ) 1
When I try all three of the queries above directly in SQL Server Management Studio, all three of them work and return "hello world" correctly.
How can i select rows that do not have any matching foreign keys
SELECT *
FROM dbo.MainTable mt
LEFT JOIN Lookup_Items msn ON mt.Item = msn.Item_ID
WHERE msn.Item_ID is NULL
I have the above query that i want to use to find all rows in "MainTable" with out a matching foreign key.
Column Item in "MainTable" is of nvarchar(200) datatype but column Item_ID in table Lookup_Items is an int.
The problem i have is that if a row in MainTable does NOT have a matching foreign key, the Item column will contain string data and the above query will fail with error message
Conversion failed when converting the nvarchar value 'TestValue' to data type int.
Is there away i can work a round this so the query can execute successfully and still give me all rows with no matching foreign key?
mercredi 16 mars 2016
I have single row should convert into columns
Please tell how to display them like
|NumberOfQuotesGenerated | 11
| TotalAmountOfQuotes | 78100
| NumberOfInvoiceGenerated | 9
| TotalAmountOfInvoice | 8222
Thank you in advance
How to use GROUPING function in SQL common table expression - CTE
I have the below T-SQL CTE code where i'm trying to do some row grouping on four columns i.e Product, ItemClassification, Name & Number.
;WITH CTE_FieldData
AS (
SELECT
CASE(GROUPING(M.CodeName))
WHEN 0 THEN M.CodeName
WHEN 1 THEN 'Total'
END AS Product,
CASE(GROUPING(KK.ItemClassification))
WHEN 0 THEN KK.[ItemClassification]
WHEN 1 THEN 'N/A'
END AS [ItemClassification],
CASE(GROUPING(C.[Name]))
WHEN 0 THEN ''
WHEN 1 THEN 'Category - '+ '('+ItemClassification+')'
END AS [Name],
CASE(GROUPING(PYO.Number))
WHEN 0 THEN PYO.Number
WHEN 1 THEN '0'
END AS [Number],
ISNULL(C.[Name],'') AS ItemCode,
MAX(ISNULL(PYO.Unit, '')) AS Unit,
MAX(ISNULL(BT.TypeName, '')) AS [Water Type],
MAX(ISNULL(PYO.OrderTime, '')) AS OrderTime,
MAX(ISNULL(BUA.Event, '')) AS Event,
MAX(ISNULL(PYO.Remarks, '')) AS Remarks,
GROUPING(M.CodeName) AS ProductGrouping,
GROUPING(KK.ItemClassification) AS CategoryGrouping,
GROUPING(C.[Name]) AS ItemGrouping
FROM CTable C INNER JOIN CTableProducts CM ON C.Id = CM.Id
INNER JOIN MyData R ON R.PId = CM.PId
INNER JOIN MyDataDetails PYO ON PYO.CId = C.CId AND PYO.ReportId = R.ReportId
INNER JOIN ItemCategory KK ON C.KId = KK.KId
INNER JOIN Product M ON R.ProductId = M.ProductId
INNER JOIN WaterType BT ON PYO.WId = BT.WId
INNER JOIN WaterUnit BUA ON PYO.WUId = BUA.WUId
WHERE R.ReportId = 4360
GROUP BY M.CodeName, KK.ItemClassification, C.Name, PYO.Number
WITH ROLLUP
)
SELECT
Product,
[Name] AS Category,
Number,
Unit as ItemCode,
[Water Type],
OrderTime,
[Event],
[Comment]
FROM CTE_FieldData
Below are the issues/problems with the data being returned by the script above and they are the ones i'm trying to fix.
-
At the end of each ItemClassification grouping, i extra record is being added yet it does not exist in the table. (See line number 4 & 10 in the sample query results screenshot attached).
-
I want the ItemClassification grouping in column 2 to be at the beginning of the group not at the end of the group. That way, ItemClassification "Category- (One)" would be at line 1 not the current line 5. Also ItemClassification "Category- (Two)" would be at line 5 not the current line 11
-
Where the "ItemClassification" is displaying i would like to have columns (Number, ItemCode, [Water Type], [OrderTime], [Event], [Comment]) display null. In the attached sample query results screenshot, those would be rows 11 & 5
-
The last row (13) is also unwanted.
I'm trying to understand SQL CTE and the GROUPING function but i'm not getting things right.
mardi 15 mars 2016
SQL Remove string between two characters
I have a string such as this: a|b^c|d|e^f|g
and I want to maintain the pipe delimiting, but remove the carrot sub-delimiting, only retaining the first value of that sub-delimiter.
The output result would be: a|b|d|e|g
Is there a way I can do this with a simple SQL function?
Insert stored procedure data into temp table not working
I have created a stored procedure which takes 3 parameters. See below
ALTER PROCEDURE [dbo].[Leave_Allocation_Mar_2016]
@Type nvarchar(10),
@Month int,
@Year int
AS
BEGIN
declare @emp_card_no numeric(9)
declare @emp_name varchar(20)
declare @dt_of_join datetime
declare @Total_days numeric(5,2)
declare @Days_worked numeric(5,2)
declare @Final_PaidDayLop numeric(5,2)
declare @TotalRecord int
declare @actualMonth int
declare @actualYear int
declare @actuallastdate varchar(20)
IF(@Type = 'C')
BEGIN
Print 'Yes I am in the Current process';
DECLARE daily_Allocate CURSOR FOR
Select distinct c.emp_card_no, c.emp_name, c.Dt_Of_Join from emp_mst c
join emp_mon_day d
on c.emp_card_no=d.emp_mkey
WHERE Dt_Of_Join = CAST(FLOOR(CAST( DATEADD(month, -6, GETDATE()) AS FLOAT ))AS DATETIME)
OPEN daily_Allocate
FETCH NEXT FROM daily_Allocate INTO
@emp_card_no, @emp_name, @Dt_Of_Join
WHILE @@FETCH_STATUS = 0
BEGIN
select @Total_days = Sum(total_day),@Days_worked = Sum(days_worked)
from emp_mon_day a
where a.emp_mkey = 2519
group by emp_mkey
PRINT 'Employee Card no = ' + cast(@emp_card_no as char)
PRINT 'Total days = ' + cast(@Total_days as char)
PRINT 'Days Worked = ' + cast(@Days_worked as char)
set @Final_PaidDayLop = 0;
set @TotalRecord = 0;
Select @Final_PaidDayLop =
isnull(sum(days),0)
from P_Emp_Del_App_Hdr c join P_Emp_Del_App_trl d on c.mkey=d.mkey
where c.delete_flag='N' and app_flag='Y' and c.year = @actualYear
and c.emp_mkey = @emp_card_no
Select @TotalRecord = ((1.75 * 6) / @Total_days) * (@Days_worked + @Final_PaidDayLop)
from emp_mon_day a where a.emp_mkey = @emp_card_no group by a.emp_mkey
PRINT 'Final Paid LOP ' + cast(coalesce(@Final_PaidDayLop,0) as char)
PRINT 'Total Record ' + cast(coalesce(@TotalRecord,0) as char)
FETCH NEXT FROM daily_Allocate INTO
@emp_card_no, @emp_name, @Dt_Of_Join
END
CLOSE daily_Allocate
DEALLOCATE daily_Allocate
END END
which gives me output as below
Now what I want is, I want to insert the above data into the temp table.
I tried like below
CREATE TABLE #tmp1111
(
Sr_no int identity(1,1),
Current_Status nvarchar(255),
Emp_card_no int,
Total_days int,
days_worked int,
final_lop_paid int,
total_record int
)
First I created a temp table and tried to insert the data as below
INSERT INTO #tmp1111 exec Leave_Allocation_Mar_2016 'C', '2', '2016'
It executed succesfully but when I ran the select statement there were no records in the temp table.
I am using SQL server 2005
Full Backup of MSSQL 2005 Database (with script or visual studio?)
I'm not much into MSSQL, but I need to do a full backup of a remote MSSQL-2005 Database. I have a computer with Visual Studio 2013 Prof. installed. I can use the Server-Explorer of Visual Studio to look into the database, but there seem to be no way to backup it there.
I found C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.2\SqlPubWiz.exe installed on my computer. But starting it with doubleclick, it just crashes.
I tried starting it via console with:
"C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.2\sqlpubwiz.exe" script -d mydatabase -S myserver -U myuser -P mypass g:\mybackup.sql
but it just says:
Microsoft (R) SQL Server Datenbankveröffentlichungs-Assistent
Version (10.0.0.0)
Copyright (c) Microsoft Corporation. Alle Rechte vorbehalten.
Done.
Without outputting anything to g:\mybackup.sql
Any ideas?
Retrieve value for Temporary column based on the value of a column in sub query
I have a SQL Query as below :
SELECT SUM(Z.MaxMarks) AS TotMaxMarks,
SUM(Z.MinMarks) AS TotMinMarks,
SUM(Z.SecuredMarks) AS TotSecuredMarks,
Z.IsPass
FROM (
SELECT Y.*
FROM (
SELECT X.MaxMarks,
X.SecuredMarks,
(X.MaxMarks * X.PassPercentage / 100) AS MinMarks,
CASE WHEN ((X.MaxMarks * X.PassPercentage / 100) < X.SecuredMarks) THEN 'True' ELSE 'False' END AS IsPass,
row_number() OVER (
PARTITION BY ChapterID ORDER BY UpdatedDate DESC
) AS SequencNo
FROM (
SELECT C.ChapterID,
T.MinimumMarks AS PassPercentage,
(
SELECT Sum(Q.MaximumMarks)
FROM MstQuestion Q,
TransStudentAnswer SA
WHERE Q.QuestionID = SA.QuestionID AND SA.TestAllotmentID = TA.TestAllotmentID
) AS MaxMarks,
(
SELECT Sum(Score)
FROM TransStudentAnswer
WHERE TestAllotmentID = TA.TestAllotmentID
) AS SecuredMarks,
TA.UpdatedDate
FROM TransTestAllotment TA,
MstStudent S,
MstBatchDetails B,
MstTest T,
MstChapter C
WHERE TA.StudentID = 47 AND TA.BatchID = 10 AND T.TestID = TA.TestID AND S.StudentID = TA.StudentID AND B.BatchID = TA.BatchID AND T.ChapterID = C.ChapterID AND TA.IsAttempted = 'True' AND TA.IsEvaluated = 'True'
) X
) Y
WHERE SequencNo = 1
) Z
GROUP BY Z.IsPass
It is returning results as below :
+-------------+-------------+-----------------+--------+
| TotMaxMarks | TotMinMarks | TotSecuredMarks | IsPass |
+-------------+-------------+-----------------+--------+
| 20 | 10 | 3 | False |
| 30 | 15 | 28 | True |
+-------------+-------------+-----------------+--------+
Is it possible to modify this query such that it returns one record with SUM(Z.MaxMarks),SUM(Z.MinMarks),SUM(Z.SecuredMarks) and TempIsPass column whose value will be true if value of the column Z.Ispass is true in all records of Z, else it should be false.
lundi 14 mars 2016
how to SUM two different columns in two different table between two date
this may quere:
select sum(tableA.value)+sum(tableB.value1) ) from tableA,tableB where tableA.data between '2016-01-21' and '2016-03-09' and tableB.date2 between '2016-01-21' and '2016-03-09'
but result false where number row different , that's to say whenever tableA select 2 row and tableB select 3 result is false.. if table ay value = 2 and tableB value =3,5 result =12 this false.. result =10
SQL Managing Database copies at change of year
Microsoft SQL Server 2005.
I have quite a lot of experience with day to day writing of SQL scripts, but am less familiar with SQL Admin tasks.
I have inherited a couple of 3rd party systems that require databases to be copied at the end of financial year. These databases are named "data_1415","data_1516" etc. These all exist on the same server.
This all works fine. However at year end I will need to copy / rename the database and then alter all the views and stored procedures in this new version to access the new database name.
So .. is there a more efficient method of updating multiple views/stored procedures?
I also wondered if using synonyms might help, but cannot get these to work unless they point to a specific table, which defeats the object.
In an ideal world I would simply name the databases "This Year" and "Last Year" and avoid doing any changes, but the users want to retain the current naming as it avoids any confusion in selecting the correct database in the 3rd party software.
Get data from SQL Server 2005 database and display in html table using php?
I've searched over the net for solutions, but yet nothing worked. I have a database (SQL Server 2005). What i want to do is make a php file that connects to the database, get data (e.g SELECT LoginID, Password, IP FROM DBName) and display it in html table e.g:
<th>LoginID</th><th>Password</th><th>IP</th>
I have already installed xampp and sql server driver for php. Here's the phpinfo output:
Thanks.
Configuration of XA dataSources on Jboss eap 6.4
I have configured the XA-datasources on sql server by the following ways:
enter code here
<datasources>
<xa-datasource jndi-name="java:/jdbc/MyDataSource" pool-name="SQLServerPool">
<driver>sqlserver</driver>
<xa-datasource-property name="ServerName">localhost</xa-datasource-property>
<xa-datasource-property name="DatabaseName">mydb</xa-datasource-property>
<xa-datasource-property name="SelectMethod">cursor</xa-datasource-property>
<security>
<user-name>admin</user-name>
<password>admin</password>
</security>
<xa-pool>
<is-same-rm-override>false</is-same-rm-override>
</xa-pool>
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"></valid-connection-checker>
</validation>
</xa-datasource>
<drivers>
<driver name="sqlserver" module="com.microsoft">
<xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>
</driver>
</drivers>
</datasources>
And enabled XA transaction database side by following
But the Database I have been using for my project is not a master database .Since XA-transactions are enabled on master, by default data is taking from master db and not from my database. Is it that I need to configure XA-transactions on "my database" for the problem to be resolved? How to give the server name under inorder to fetch from "my database "
My loginUserName is the owner of both master schema and "mydatabase" schema and have created Role SqlJDBCXA user both in master and "mydatabase".
Referred the following but couldnot help Exception with MS-SQL Server,JDBC and XA Transactions
Can anyone Suggest? ThankYou
dimanche 13 mars 2016
Retrieve data from changed rows records
I'm working in Microsoft SQL Server 2005 Management Studio. I'm new to T-SQL and i have to make the following report:
I have a table (H2O) that contains a records of timestamp, pumpstatus, tankVolume.
The data is inserted into H2O every 1 minute.
I have to make a report (based on timestamp between date1 and date2) on the emptying tank.
The indicator when the emptying process is started is the pumpstatus chages from 2 to 3
The indicator when the emptying process is finished is the pumpstatus chages from 3 to 2
Thanks for any help!
Asp.net data table returns row count as 1 even though data table is null(in case of aggregate functions only)
I have been trying to execute a particular function if data table rows count >0 but in my code it returns 1 even though data table is null . my code is..
c.cmd.CommandText = "select sum(qty) from product where seller_id=" +Convert.ToInt64( Session["seller_id"]) + "";
adp.SelectCommand = c.cmd;
dt1.Clear();
adp.Fill(dt1);
MessageBox.Show(dt1.Rows.Count.ToString());
someone help please...
vendredi 11 mars 2016
How to update column of table having composite key in rails?
id: [1003, "group"], user_id: 1, society_id: 2, category: "group", latitude: -33.817, longitude: 151.2008, country_name: "Australia", city: "Naremburn", country_code: "AU", region_code: "NSW", region_name: "New South Wales", zip_code: "2065", time_zone: "Australia/Sydney", metro_code: "0", created_at: Fri, 11 Mar 2016 05:56:54 UTC +00:00, updated_at: Fri, 11 Mar 2016 05:56:54 UTC +00:00, street: "Quiamong Road", group_id: 203>
I have try like as: Address.where(:id => address_id).update_all(category: address_params.category)
How to properly concatenate and escape strings in T-SQL
I'm trying to create a stored procedure from an sql string that i'm creating by concatenating multiple strings.
Getting the string escaping is giving me some trouble and i thought some one might sport the places where i'm getting it wrong.
When i try to execute the script i get the following errors
Msg 102, Level 15, State 1, Procedure TEST_01, Line 48 Incorrect syntax near '@StartDate'.
Msg 102, Level 15, State 1, Procedure TEST_01, Line 50 Incorrect syntax near '@MenuIds'.
Msg 102, Level 15, State 1, Procedure TEST_01, Line 53 Incorrect syntax near '@MeansIds'.
Msg 102, Level 15, State 1, Procedure TEST_01, Line 56 Incorrect syntax near '@TypeIds'.
Msg 102, Level 15, State 1, Procedure TEST_01, Line 59 Incorrect syntax near '@CondIds'.
Msg 102, Level 15, State 1, Procedure TEST_01, Line 64 Incorrect syntax near '+MeansIdentifier+'.
i have tried to play a round the string escaping but the more i try the more i mess it up.
Below is my script.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DECLARE @CreateSP varchar(MAX)
SET @CreateSP = '
CREATE PROCEDURE [dbo].[TEST_01]
(
@StartDate DATETIME,
@EndDate DATETIME,
@MenuIds NVARCHAR(MAX),
@MeansIds NVARCHAR(MAX),
@TypeIds NVARCHAR(MAX),
@CondIds NVARCHAR(MAX),
@CatIds NVARCHAR(MAX),
@Blocks NVARCHAR(MAX)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql AS NVARCHAR(MAX);
SET @sql = '';WITH CTE_ActionDetails
AS (
SELECT
CASE(GROUPING(M.CodeName))
WHEN 0 THEN [CodeName]
WHEN 1 THEN ''''Total''''
END AS [Menu],
CASE(GROUPING(D.[Name]))
WHEN 0 THEN D.[Name]
WHEN 1 THEN ''''ET-Total ''''+ ''''(''''+CC.Name+'''')''''
END AS [Name],
max(C.MeansIdentifier) AS MeansIdentifier,
MAX(D.Name) as [Condition],
SUM(ISNULL(Master.Youth, 0)) AS Youth,
SUM(ISNULL(Master.MiddleAged, 0)) AS MiddleAged,
SUM(ISNULL(Master.Elderly, 0)) AS Elderly,
ISNULL(max(Master.Comment), '''') AS Comment,
GROUPING(M.CodeName) AS MenuGrouping,
GROUPING(C.MeansIdentifier) AS MeansGrouping,
GROUPING(CC.Name) AS TypeGrouping,
GROUPING(D.[Name]) AS ItemGrouping
FROM Condition D
INNER JOIN MasterData Master ON Master.ConditionId = D.ConditionId
INNER JOIN Report R ON R.ReportId = Master.ReportId
INNER JOIN Menu M ON R.MenuId = M.MenuId
INNER JOIN Means C ON C.MeansId = Master.MeansId
INNER JOIN Type CC ON CC.TypeId = Master.TypeId
WHERE (1=1)''
SET @sql = @sql + '' AND (R.ReportDate >= ''@StartDate'' AND R.ReportDate <=''@EndDate'')''
SET @sql = @sql + '' AND (R.MenuId IN IN (SELECT DATA FROM SPLIT(''@MenuIds,'','''')))''
IF @MeansIds is not null
SET @sql = @sql + '' AND (Master.MeansId IN (SELECT DATA FROM SPLIT(''@MeansIds,'','''')))''
IF @TypeIds is not null
SET @sql = @sql + '' AND (Master.TypeId IN (SELECT DATA FROM SPLIT(''@TypeIds,'','''')))''
IF @CondIds is not null
SET @sql = @sql + '' AND (Master.ConditionId IN (SELECT DATA FROM SPLIT(''@CondIds,'','''')))''
SET @sql = @sql + '' GROUP BY M.CodeName,C.MeansIdentifier,CC.Name,D.Name WITH ROLLUP ''
SET @sql = @sql + '' SELECT Menu,
(CASE WHEN [Name] IS NULL THEN ''''Total '''' + ''(''+MeansIdentifier+'')''
WHEN [Name] IS NOT NULL THEN ''''
END) AS MeansIdentifier,
[Name] AS [Condition],
Youth,
Elderly,
MiddleAged,
(Youth + Elderly + MiddleAged) AS Total,
Comment
FROM CTE_ActionDetails
END'''
EXEC(@CreateSP)
jeudi 10 mars 2016
how to solution query for SqlServer where tableA is not data select all row tableB
I Can table A is not data(full Row null) Select all row tableB . now where TableA is not data, the query is not select data
select b.price from tableA a inner join tableB b on b.idprice = a.idprice group by a.idprice, b.price having sum(tax) < b.price;
How to convert different string value to respective 'YYYY' int format?
I'm inserting values into table from xml as:
DECLARE @XMLRESULT XML
DECLARE @tbl_XMLResult TABLE
(
RowID INT IDENTITY(1,1),
Status VARCHAR(50),
Address VARCHAR(250),
ListPrice INT,
SoldPrice INT,
YearBuilt INT
)
EXEC usp_GetReportResult @query = @query, @ReportName = @ReportName, @XMLResult = @XMLRESULT OUTPUT
INSERT INTO @tbl_XMLResult
(
Status,
Address,
ListPrice,
SoldPrice,
YearBuilt
)
SELECT
L.value('(ListingStatus/text())[1]','VARCHAR(50)') AS Status,
L.value('(PMCReportData/Listing_StreetAddress/text())[1]','VARCHAR(250)') AS Address,
L.value('(ListPrice/text())[1]','INT') AS ListPrice,
L.value('(ClosePrice/text())[1]','INT')AS SoldPrice,
L.value('(YearBuilt/text())[1]','INT')AS YearBuilt
FROM @XMLRESULT.nodes('/Results/Report/Listings/Listing')AS Result(L)
But as shown above for YearBuilt column the datatype is int in which we are storing just year on which property is built. And accordingly subsequent calculations are done. But some of the time the values for YearBuilt comes from xml as in format
- "New"
- "0-5 Yrs"
- "70+"
But when above type of result for YearBuilt comes in xml we are getting conversion error. Need to get values from above result as if YearBuilt as:
- "New" - Here we want consider current year and store result as 2016 current year.
- "0-5 Yrs" - 0-5Yrs. Here consider upper limit, i.e. 5. So store result as 2011 as(2016 - 5 = 2011)
- "70+" - consider 70 as => 2016 - 70 Year = 1946
How we can do this to store appropriate result into @tbl_xmlResult
SQL Join taking too much time to run
This query shown below is taking almost 2 hrs to run and I want to reduce the execution time of this query. Any help would be really helpful for me.
Currently:
If Exists (Select 1
From PRODUCTS prd
Join STORE_RANGE_GRP_MATCH srg On prd.Store_Range_Grp_Id = srg.Orig_Store_Range_Grp_ID
And srg.Match_Flag = 'Y'
And prd.Range_Event_Id = srg.LAR_Range_Event_Id
Where srg.Range_Event_Id Not IN (Select distinct Range_Event_Id
From Last_Authorised_Range)
)
I have tried replacing the Not IN clause by Not Exists and Left join but no luck in runtime execution.
What I have used:
If Exists( Select top 1 *
From PRODUCTS prd
Join STORE srg
On prd.Store_Range_Grp_Id = srg.Orig_Store_Range_Grp_ID
And srg.Match_Flag = 'Y'
And prd.Range_Event_Id = srg.LAR_Range_Event_Id
and srg.Range_Event_Id ='45655'
Where NOT EXISTS (Select top 1 *
From Last_Authorised_Range where Range_Event_Id=srg.Range_Event_Id)
)
Product table has 432837 records and the Store table also has almost the same number of records. This table I am creating in the stored procedure itself and then dropping it in the end in the stored procedure.
Create Table PRODUCTS
(
Range_Event_Id int,
Store_Range_Grp_Id int,
Ranging_Prod_No nvarchar(14) collate database_default,
Space_Break_Code nchar(1) collate database_default
)
Create Clustered Index Idx_tmpLAR_PRODUCTS
ON PRODUCTS (Range_Event_Id, Ranging_Prod_No, Store_Range_Grp_Id, Space_Break_Code)
Should I use non clustered index on this table or what all can I do to lessen the execution time? Thanks in advance
mercredi 9 mars 2016
Need max(id) by location but always getting the same value for each location
I am running a query in sqlserver where i need the latest id by location the location is in a different table but i can join on the id. The problem is the values are always the same and not for each location. Here is my query:
SELECT DISTINCT
(select max(id) from t
where p.partname = 'ibiza')locid,
p.partitionkey,
t.partitionkey,
p.partdesc,
p.partname
FROM
t1 t1,
t t
WHERE
t.partitionkey = t1.partitionkey
GROUP BY
t.id,
p.partitionkey,
p.partname,
p.partdesc,
t.partitionkey;
mardi 8 mars 2016
Is there someway I could insert data in MSSQL using PHP?
Is there someway I could insert data in MSSQL using PHP ?
Here is the code for mysql , what changes could be done to insert data in MSSQL instead .. Thank You
<?php
$where_form_is="http://".$_SERVER['SERVER_NAME'].strrev(strstr(strrev($_SERVER['PHP_SELF']),"/"));
include("config.inc.php");
$link = mysql_connect($db_host,$db_user,$db_pass);
if(!$link) die ('Could not connect to database: '.mysql_error());
mysql_select_db($db_name,$link);
$query = "INSERT into `".$db_table."` (field_1,field_2,field_3,field_4) VALUES ('" . $_POST['field_1'] . "','" . $_POST['field_2'] . "','" . $_POST['field_3'] . "','" . $_POST['field_4'] . "')";
mysql_query($query);
mysql_close($link);
include("confirm.html");
?>
lundi 7 mars 2016
SQL While Loop in 2005
Anyone have a sample of how to do a WHILE Loop in 2005 SQL without using a cursor? I'd like to loop through based on a counter
SQL query to get row wise total
Im working with SQL Server 2008 I want to get the row wise total for the table and want to merge the 2 string column names for the total column. I have below table data
<HTML>
<Table>
<tr>
<Td>Region</Td>
<Td>Test </Td>
<Td>taken </Td>
<Td>Nottaken</Td>
</tr>
<tr>
<td>India</td>
<td>AA</td>
<td>1</td>
<td>0</td>
</tr>
<tr>
<td>UK</td>
<td>AS</td>
<td>3</td>
<td>4</td>
</tr>
<tr>
<td>US</td>
<td>AF</td>
<td>1</td>
<td>1</td>
</tr>
</Table>
</HTML>
I want the result set like below
<HTML>
<Table>
<tr>
<Td>Region</Td>
<Td>Test </Td>
<Td>taken </Td>
<Td>Nottaken</Td>
<td>Total</td>
</tr>
<tr>
<td>India</td>
<td>AA</td>
<td>1</td>
<td>0</td>
<td>1</td>
</tr>
<tr>
<td>UK</td>
<td>AS</td>
<td>3</td>
<td>4</td>
<td>7</td>
</tr>
<tr>
<td>US</td>
<td>AF</td>
<td>1</td>
<td>1</td>
<td>2</td>
</tr>
<tr>
<td>region</td>
<td>total</td>
<td>5</td>
<td>5</td>
<td>10</td>
</tr>
</Table>
</HTML>
how to write query?
dimanche 6 mars 2016
How to Select columns from tow different table(A,B) where sum(column tableA) less then columntableB
I can select price where sum(tax) less than price, I am the Query but error select b.price from tableA a inner join tableB b on b.idprice = a.idprice where sum(tax)
samedi 5 mars 2016
how to design query for select columns
how to design query for select id price where sum(tax) less than price plz help me
jeudi 3 mars 2016
Transfering a SQL Database on AWS
We have a SQL Server 2005 database on our local server.
Here it is (ofc i've to repeat the proceder for the other databases):
I've to transfer it to our SQL server 2012 instance on Amazon RDS.
I right clicked the database and selected Generate Scripts - All tables - Copy Schema and Data and saved everything as a sql file
At this point i attempted to use the SQL Azure MW v5.15 (in a question here i saw that it works with AWS too, way to go Microsoft!) to transfer the database on AWS. However it crashes. No problem, i try to use SQL Management studio to import the file but as soon the RAM consumed by the program reaches 1gb (as you can see that DB is 3,4gb) BOOM - out of memory error! What should i do now?
mercredi 2 mars 2016
Search MS SQL Database and get part of of text matching with the kewords
I am using simple search sql to search the table for matching search keyword or string. For example i have a News table and i need to get 10 words around the matched keyword.
NewsID Title Detail
1 xxxx <p> Hello How are you! is this the word you are looking for.</p>
2 yyyy <p> this is the detail of second news of today. </p>
right now i use simple select statement to search
Select * FROM News WHERE Detail Like '%word%'
This matched with first row.
Now i need to get the two or three words before & after the matching keyword like
Is this the word you are.
Not sure how this can be don't using MS SQL function or CTE query.
We can do this using C# & Regex but this will be too heavy as search has to go through thousands of records and Details field of news column can have up to 1000 words or more for each news item.
Just want to mention i don't have FreeText search option on the server
Appreciate pointer to resolve this in best & optimized way
Automatically log SQL query, if it throws some exception in SQL Server 2005
I have a huge WinForms application having SQL Server 2005 as database server. The application deals in financial stock markets, and during market hours, SQL requests can range from 2500-3000 per minute approximately. Many times on production we face an issue when SQL query fails, and we get errors. It becomes really difficult of reproduce that issue for debugging purposes. Is it possible to automatically log that particular SQL query which failed to execute successfully. Logging each query would be costly, therefore, only if it fails, then only it should be logged.
How to allow other computers to connect to my database
I have been working for a few months on an application for a client and it is in testing phase and I need to allow another computer on the same network to connect to my database.
I have enabled the TCP/UDP in sql server configuration manager I allowed for windows firewall to accept udp connections based off of port and service.
My connection string in the app specifies this machine and an instance of the database I am able to connect on my development PC but not the other pc.
Min and max date between rows - grouping row
I have a table "A" as in the image and I need to create a view to group data as table "B". Any suggestions? I'll appreciate every input to improve my skills Thanks
is it possible to do this in SQL?
MEMBERS TABLE:
ID Resident Gender Location
1 Steve M S-55
2 Roger M S-42
3 Martha F R-20
4 Samantha F CC
5 Tom M S-12
Trying to code an SQL Statement that returns a single numerical value.
Something along the lines of...
SELECT COUNT(Gender)
FROM Members
WHERE GENDER = M
AND Location CONTAINS 'S%'
The AND LOCATION obviously doesn't really exists in SQL in that syntax, but I'm hopping to get the count of how many members that are MALE and reside in a location that has an S in it.
Is this possible?
Fastest way to get total count of employees , total count of employees who are absent in each department?
Department
+-------+------------+
| EmpId | DepId|
+-------+------------+
| 1 | IT |
| 2 | admin |
| 3 | IT |
| 4 | IT |
| 5 | admin |
+-------+------------+
Attendance
+-------+--------+
| EmpId | Status |
+-------+--------+
| 1 | P |
| 2 | P |
| 3 | P |
| 4 | A |
| 5 | P |
+-------+--------+
Desired Output :
+-------+------------+--------------+
| DepId | TotalCount | PresentCount |
+-------+------------+--------------+
| Admin | 2 | 2 |
| IT | 3 | 2 |
+-------+------------+--------------+
My Query:
SELECT DepId,
COUNT(att.empid) total,
sum(CASE WHEN status = 'P' THEN 1 ELSE 0 END) presetCount
FROM attendance att
INNER JOIN departments dep ON att.empid=dep.empid
GROUP BY DepId
Can this be optimized further? if yes how?
concatenate 2 columns into 1 and update it in the table using sql query
I have 3 columns in a table paxname,fname,lname...now i want to concate fname and lname and update that in paxname...so i ran the following query but it does not work...can anyone please help me with this
update pax set paxname=lname || " " || fname
I tried with CONCAT function but it didnt work either.
mardi 1 mars 2016
Need suggestion for SQL server 2005 to 2012 migration steps [on hold]
I have to create a plan of implementation for SQL server 2005 to 2012 migration. My senior told me that i am missing important points so please check the steps below and suggest me what i am doing wrong.
Migration
1 Run upgrade adviser on the databases for the instance that i want to upgrade.
2 If any issues found, Report dev team/ Vendor for fixing the same.
3 Take the backup of all the databases and validate them.
4 Backup of sql server level setting by sp_configure.
5 Scripting out all the jobs and take it in a file.
6 Scripting the linked server
7 Restore the backup on the destination server and fix the orphan users issue
8 Change the compatibility level and run dbcc checkdb on all the databases.
9 Run rebuild indexes on all the databases.
10 restore server level setting on the destination server
11 run job scripts on the destination sql server
12 Change job owner if any problem exists.
Thanks in advance
Inscription à :
Commentaires (Atom)