Sunday, May 5, 2013

[SQL Server 2008 issues] sql server query problem

[SQL Server 2008 issues] sql server query problem

sql server query problem

Posted: 03 May 2013 10:38 PM PDT

hi friends i have small doubt in sql server plz tell me how to solve i have 2 tables and data contains like aa id , color , colorid 1 , red , 10 2 , white , 20and 2nd table contains like bb colorid , name 10 , carrote 10 , tammoto 20 , sugar 20 , milkbased on this table i want output like below color name red carrote,tammoto white sugar ,milkactualy i follow query inner join concept .but iam not get exactely this output.plese tell me how to write query to solve this issue in sql server.

SQL FOREIGN KEY conflict help!

Posted: 04 May 2013 08:15 AM PDT

I have two tables and I keep getting an FK conflict error on some of the data being inserted and cannot work out why!Can anyone see the mistake I am missing?ThanksCode is as follows:CREATE TABLE Venue( VenueID INT PRIMARY KEY IDENTITY, VenueName VARCHAR(70), Capacity INT,); CREATE TABLE Match( MatchID INT PRIMARY KEY IDENTITY, VenueID INT,MatchType VARCHAR(70),Date DATE,KickOffTime TIME(0), Team1 VARCHAR(70), Team2 VARCHAR(70), CONSTRAINT fk_VenueID FOREIGN KEY (MatchID) REFERENCES Venue(VenueID)); INSERT INTO Venue VALUES ('Saint Denis','81338');INSERT INTO Venue VALUES ('Marseille','67000');INSERT INTO Venue VALUES ('Lyon','61556');INSERT INTO Venue VALUES ('Paris','51000');INSERT INTO Venue VALUES ('Villeneuve d'Ascq','50186');INSERT INTO Venue VALUES ('Lens','45000');INSERT INTO Venue VALUES ('Bordeaux','43000');INSERT INTO Venue VALUES ('Saint-Etienne','67000');INSERT INTO Venue VALUES ('Toulouse','40000');INSERT INTO Venue VALUES ('Nice','35000');INSERT INTO Match VALUES ('1','Group A Qualifier','03/05/2013','15:00','Czech Republic','Greece');INSERT INTO Match VALUES ('4','Group A Qualifier','03/05/2013','15:00','Russia','Poland');INSERT INTO Match VALUES ('5','Group A Qualifier','07/05/2013','15:00','Greece','Russia');INSERT INTO Match VALUES ('7','Group A Qualifier','07/05/2013','15:00','Czech Republic','Poland');INSERT INTO Match VALUES ('1','Group A Qualifier','10/05/2013','15:00','Czech Republic','Russia');INSERT INTO Match VALUES ('3','Group A Qualifier','10/05/2013','15:00','Greece','Poland');INSERT INTO Match VALUES ('1','Group B Qualifier','04/05/2013','15:00','Germany','Portugal');INSERT INTO Match VALUES ('4','Group B Qualifier','04/05/2013','19:00','Denmark','Netherlands');INSERT INTO Match VALUES ('5','Group B Qualifier','08/05/2013','12:00','Portugal','Denmark');INSERT INTO Match VALUES ('7','Group B Qualifier','08/05/2013','15:00','Germany','Netherlands');INSERT INTO Match VALUES ('1','Group B Qualifier','11/05/2013','15:00','Germany','Denmark');INSERT INTO Match VALUES ('3','Group B Qualifier','11/05/2013','19:00','Portugal','Netherlands');INSERT INTO Match VALUES ('1','Group C Qualifier','05/05/2013','15:00','Spain','Italy');INSERT INTO Match VALUES ('4','Group C Qualifier','05/05/2013','19:00','Croatia','Republic of Ireland');INSERT INTO Match VALUES ('5','Group C Qualifier','09/05/2013','16:00','Italy','Croatia');INSERT INTO Match VALUES ('7','Group C Qualifier','09/05/2013','19:00','Spain','Republic of Ireland');INSERT INTO Match VALUES ('1','Group C Qualifier','12/05/2013','15:00','Spain','Croatia');INSERT INTO Match VALUES ('3','Group C Qualifier','12/05/2013','19:00','Italy','Republic of Ireland');INSERT INTO Match VALUES ('1','Group D Qualifier','06/05/2013','15:00','England','France');INSERT INTO Match VALUES ('4','Group D Qualifier','06/05/2013','19:00','Ukraine','Sweden');INSERT INTO Match VALUES ('5','Group D Qualifier','10/05/2013','16:00','France','Ukraine');INSERT INTO Match VALUES ('7','Group D Qualifier','10/05/2013','19:00','England','Sweden');INSERT INTO Match VALUES ('1','Group D Qualifier','13/05/2013','15:00','England','Ukraine');INSERT INTO Match VALUES ('3','Group D Qualifier','13/05/2013','19:00','France','Sweden');INSERT INTO Match VALUES ('1','Group E Qualifier','07/05/2013','15:00','Scotland','Wales');INSERT INTO Match VALUES ('4','Group E Qualifier','07/05/2013','19:00','San Marino','Luxemberg');INSERT INTO Match VALUES ('5','Group E Qualifier','11/05/2013','16:00','Wales','San Marino');INSERT INTO Match VALUES ('7','Group E Qualifier','11/05/2013','19:00','Scotland','Luxemberg');INSERT INTO Match VALUES ('1','Group E Qualifier','14/05/2013','15:00','Scotland','San Marino');INSERT INTO Match VALUES ('3','Group E Qualifier','14/05/2013','19:00','Wales','Luxemberg');INSERT INTO Match VALUES ('1','Group F Qualifier','08/05/2013','15:00','Cyprus','Israel');INSERT INTO Match VALUES ('4','Group F Qualifier','08/05/2013','19:00','Faroe Islands','Kazakhstan');INSERT INTO Match VALUES ('5','Group F Qualifier','12/05/2013','16:00','Israel','Faroe Islands');INSERT INTO Match VALUES ('7','Group E Qualifier','12/05/2013','19:00','Cyprus','Kazakhstan');INSERT INTO Match VALUES ('1','Group F Qualifier','15/05/2013','15:00','Cyprus','Faroe Islands');INSERT INTO Match VALUES ('3','Group F Qualifier','15/05/2013','19:00','Israel','Kazakhstan');

Archiving

Posted: 04 May 2013 12:17 AM PDT

HiI want to archive the data of a 400G table,what is the best way of archiving?My table has a clustered index on 2 fields (int,datetime).We just do select on last 2 years and insert in this table,and for the old data we do select seldom for examplejust 5 in a month,and no insert.I want to create a table and insert the old data into it and create indexes for both tables,Is it a pointless act?How can I archive the old data ?

Return Distinct Rows

Posted: 04 May 2013 06:27 AM PDT

Hello,I have two tables, one call BoatDetails and the other BoatImages there both linked by the BOATIDwhen i do a select to return all the Boats + the Boat Images associated with that boat i get duplicate rows.This is because one boat could have 1 - 20 images associated with it, Is there a way I can just return the 1 boat + 1 image associated with it?This is my SQL query im not sure how or where i could achieve the above statement?[code="sql"]Select Distinct b.BoatID, b.Title, b.YearOfRegistration as Registration, case b.Condition when 1 then'Excellent' when 2 then 'Average' when 3 then 'Needs some TLC' end as Condition, b.[Length], '£' + CAST(Convert(Decimal(10,2),b.Price) as nvarchar) as Price, bl.[Image]From BoatDetails as b left join BoatImages bl on b.BoatID = bl.BoatIDwhere b.IsArchived = 0[/code]So just to clarify i want to return 1 boat + 1 image not 2 or 3 of the same boat just because there is more then one image, can some one help me

sql server query

Posted: 04 May 2013 04:51 AM PDT

hi friend i have small doubt in sql .plese tell me how to solve table data contains like belowcol10abcjai2021kali23ravi28kumlanbalu31based on this table i want separte numbers and string values output likeCol Col1 Col210 10 Abc . abcJai . jai20 20 .21 21 .Kali . kali23 23 .Ravi . Ravi28 28 .Kum . KumJan . JanLan . Lanbalu . Balu31 31 .plese tell me how to wirte query to solve this issue in sql server.

100% free marketplace to buy and sell things.

Posted: 04 May 2013 02:31 AM PDT

Shoppie.us is a free marketplace that allow individuals and businesses to crate free online stores. You can shop, collect, or sell your items.[url=http://www.shoppie.us/]ebay alternative[/url][url=http://www.shoppie.us/]free online store[/url][url=http://www.shoppie.us/]online shopping marketplace[/url][url=http://www.shoppie.us/]best shopping online[/url]

Operand type clash: int is incompatible with date

Posted: 03 May 2013 10:51 PM PDT

ALTER PROCEDURE [dbo].[bnd_ProjwiseCategory_rblockNODATE] @M_Acc_ID AS INT, @M_ProjVers_ID AS INT, @Category AS INT, @Action AS INT, @From_Date DATE,@To_DATE DATE, @Cate AS INT --ddlcate value AS BEGIN IF @Action=-1 AND @Cate=-3 --ddlaccount and ddlcate ALL BEGIN SELECT TSM.Account, TSM.[Project & Version], TSM.Resources, TSM.Category, SUM(TSM.Utilization)AS Utilization, SUM(TSM.NONBILLABLE) AS NONBILLABLE, ROUND(((SUM(TSM.NONBILLABLE)/(SUM(TSM.NONBILLABLE)+SUM(TSM.Utilization)))*100),2) AS Percentage FROM (SELECT T.Account, T.[Project & Version], T.Resources, T.Category, SUM(T.Utilization) AS Utilization , SUM(T.NONBILLABLE) AS NONBILLABLE FROM (SELECT AC.Acc_Name AS Account, MPV.FullProjName AS [Project & Version], MU.[User_Name] AS Resources , TSC.TaskCategory AS Category , TS.TotalHours AS Utilization, TS.StartTime, TS.EndTime, TS.TSM_Day, 0 AS NONBILLABLE, CONVERT(DATETIME,TS.StartTime, 108) AS'From', CONVERT(DATETIME,TS.EndTime, 108) AS 'To', CONVERT(DATETIME, DATEADD(SS,DATEDIFF(SS ,[StartTime], [EndTime]),0),108) AS TotalHours FROM TimeSheet TS INNER JOIN Accounts AC ON TS.M_Acc_ID=AC.M_Acc_ID INNER JOIN mProjectVersions MPV ON TS.M_ProjVer_ID=MPV.M_ProjVersion_ID INNER JOIN mUser MU ON TS.TaskOwner=MU.M_User_ID INNER JOIN mTimeSheetCategory TSC ON TS.M_TaskCat_ID=TSC.M_TaskCat_ID WHERE TS.M_TaskCat_ID<>1 AND Block=0 AND TS.TSM_Day BETWEEN @From_Date AND @To_Date GROUP BY AC.Acc_Name,MPV.FullProjName,TaskOwner,MU.[User_Name], TSC.TaskCategory,TS.TotalHours,TS.StartTime,TS.EndTime,TS.TSM_Day UNION SELECT AC.Acc_Name AS Account, MPV.FullProjName AS [Project & Version], MU.[User_Name] AS Resources , TSC.TaskCategory AS Category , 0 AS UTilization, TS.TotalHours AS Utilization, TS.StartTime, TS.EndTime, TS.TSM_Day, CONVERT(DATETIME,TS.StartTime, 108) AS'From', CONVERT(DATETIME,TS.EndTime, 108) AS 'To', CONVERT(DATETIME, DATEADD(SS,DATEDIFF(SS ,[StartTime], [EndTime]),0),108) AS TotalHours FROM TimeSheet TS INNER JOIN Accounts AC ON TS.M_Acc_ID=AC.M_Acc_ID INNER JOIN mProjectVersions MPV ON TS.M_ProjVer_ID=MPV.M_ProjVersion_ID INNER JOIN mUser MU ON TS.TaskOwner=MU.M_User_ID INNER JOIN mTimeSheetCategory TSC ON TS.M_TaskCat_ID=TSC.M_TaskCat_ID WHERE TS.M_TaskCat_ID=1 AND Block=0 AND TS.TSM_Day BETWEEN @From_Date AND @To_Date GROUP BY AC.Acc_Name,MPV.FullProjName,TaskOwner,MU.[User_Name], TSC.TaskCategory,TS.TotalHours,TS.StartTime,TS.EndTime,TS.TSM_Day)T GROUP BY T.Account,T.[Project & Version],T.Resources, T.Category,T.NONBILLABLE,T.UTilization,T.StartTime,T.EndTime)TSM GROUP BY TSM.Account,TSM.[Project & Version],TSM.Resources, TSM.Category,TSM.NONBILLABLE,TSM.UTilization END END

Save all DTS pakges to location

Posted: 30 Apr 2013 08:51 AM PDT

Hi I found this script for saving all DTS pkges.Can someone help me in sacving all the DTS t0 H:\DTS location--------------------------------------------------------------if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_SavePackages]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[s_SavePackages]GOCreate procedure s_SavePackages@Path varchar(128)as/**/ set nocount ondeclare @objPackage intdeclare @PackageName varchar(128)declare @rc intdeclare @ServerName varchar(128)declare @FileName varchar(128)declare @FilePath varchar(128)declare @cmd varchar(2000) select @ServerName = @@ServerName , @FilePath = @Path if right(@Path,1) <> '\' begin select @Path = @Path + '\' end -- create output directory - will fail if already exists but ... select @cmd = 'mkdir ' + @FilePath exec master..xp_cmdshell @cmd create table #packages (PackageName varchar(128)) insert #packages (PackageName) select distinct name from msdb..sysdtspackages select @PackageName = '' while @PackageName < (select max(PackageName) from #packages) begin select @PackageName = min(PackageName) from #packages where PackageName > @PackageName select @FileName = @FilePath + @PackageName + '.dts' exec @rc = sp_OACreate 'DTS.Package', @objPackage output if @rc <> 0 begin raiserror('failed to create package rc = %d', 16, -1, @rc) return end exec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null, @ServerName = @ServerName, @Flags = 256, @PackageName = @PackageName if @rc <> 0 begin raiserror('failed to load package rc = %d, package = %s', 16, -1, @rc, @PackageName) return end -- delete old file select @cmd = 'del ' + @FileName exec master..xp_cmdshell @cmd, no_output exec @rc = sp_OAMethod @objPackage, 'SaveToStorageFile', null, @FileName if @rc <> 0 begin raiserror('failed to save package rc = %d, package = %s', 16, -1, @rc, @PackageName) return end exec @rc = sp_OADestroy @objPackage endgo
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog