Thursday, May 9, 2013

[T-SQL] Major Challenge - Too Hard to Summarize

[T-SQL] Major Challenge - Too Hard to Summarize

Major Challenge - Too Hard to Summarize

Posted: 08 May 2013 06:34 AM PDT

I have two tables. Table #1CREATE TABLE [XXX].[SLHIH]([WorkDate] [date] NOT NULL,[BusinessUnit] [varchar](5) NOT NULL,[ContactType] [varchar](5) NULL,[TotalContacts] [int] NULL,[SIH] [bigint] NULL,[SLEligible] [int] NULL,[InServiceLevel] [int] NULL,CONSTRAINT [PK_SLHIH] PRIMARY KEY CLUSTERED ([WorkDate] ASC,[BusinessUnit] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY]Table #2CREATE TABLE [XXX].[HoursOfOperation]([CalendarDate] [date] NOT NULL,[BusinessUnit] [varchar](5) NOT NULL,[StartTime] [time](0) NOT NULL,[EndTime] [time](0) NOT NULL,[GMTDifference] [int] NULL,[WorkingDate] [bit] NULL,CONSTRAINT [PK_HoursOfOperation] PRIMARY KEY CLUSTERED ([CalendarDate] ASC,[BusinessUnit] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY]Data is added to SLHIH daily via scheduled process.For the report, I need to find the dates on SLHIH that are WorkingDate = FALSE for each BusinessUnit and replace their data fields (TotalContacts, SIH, SLEligible, InServiceLevel) with the contents of the most recent previous date for that same BusinessUnit that WAS a WorkingDate (WorkingDate = TRUE). For example, if Sunday is not a WorkingDate for BusinessUnit A, and Saturday was, I need Saturday's data to replace Sunday.I would prefer to do this "on the fly" rather than actually replace the contents on the table.How in the heck?PK

Last 6 quarter and current quarter

Posted: 08 May 2013 11:47 PM PDT

Hi,We have fiscal quarters like FY13-Q1,FY13-Q2..Need to display last 6 quarters (FY12-Q1 to FY13-Q2) and Current quarter (FY13-Q3) in a report and this should be dynamic.How can we achieve this in Netezza database.Thanks!

Join creating two records

Posted: 08 May 2013 01:59 AM PDT

I have a join that is doing something that I can not explain.I have two tables.. linking on Delivery No and Item No..Temp table #Delivery has one record with Delivery No = 00835308 and the second table does not have that Delivery at all. But I get two records running...select *FROM #Delivery Left Outer JOIN details_2 ON cast(#Delivery.Delivery as int) = cast(details_2.Delivery_Doc as int) AND cast(#Delivery.Delivery_Item as int) = cast(details_2.Delivery_Item as int)Where cast(delivery as int) = 83535308Like gives only one record.. but when I run without the WHERE it never completes the INSERT.

SQL Server system tables

Posted: 08 May 2013 08:54 PM PDT

Hi,I am familiar with SQL and have created queries.With TSQL the problem I'm having is I'm not sure which SQL Server system tables to look in for the data I need (there are a lot of tables to browse through....)Is there a reference guide on the internet which could guide me to look at certain system tables for certain tasks. For example for backups look in msdb.dbo.backupset table, for monitoring replication look in msdb.dbo.sysreplicationalerts and msdb.dbo.sysalerts tables etc. etc.I know how to query but when given a new task I'm a bit lost as to where to find my data, from which SQL server system tables.Thanks.

Differnet Join Statement

Posted: 08 May 2013 09:30 PM PDT

Hi guru's Thanks for your support this is my following situation [code="sql"]DECLARE @A as TABLE(Eid VARCHAR(5),DocNo VARCHAR(5))DECLARE @B as TABLE(Cid VARCHAR(5),CDocNo VARCHAR(5))INSERT INTO @AVALUES ('a','1'), ('a','2'),('a','4'), ('a','6'), ('c','3'), ('b','4'), ('e','5'), ('b','1'), ('b','3'), ('b','6') INSERT INTO @BVALUES ('a','1'), ('a','2'), ('b','4')[/code]Now i want the result as followsEid cDocNoa 1a 2a NULLa NULLb NULLb NULLb 4b NULLhow did i bring this result if have tried this one but not successfull[code]SELECT a.Eid, b.cDocNo FROM @A a LEFT OUTER JOIN @B b ON A.Eid = b.cid AND a.DocNo=b.CDocNoORDER BY a.Eid[/code]but result include with the c NULLe NULLany other idea how to get that result

Query Help

Posted: 08 May 2013 08:55 PM PDT

Hi,I required help in creating this type queryCreate table #temp (ID int,VoucherNo varchar(10), Status varchar(10), VoucherType int)Insert into #temp Values (1,'VVB00001','New',1),(2,'VVB00002','New',1),(3,'VVB00003','Active',1),(4,'VVB00004','Active',1), (5,'VVB00005','New',1),(6,'VVB00006','New',1) Required output:FirstVoucherNo LastVoucher Status VoucherTypeVVB00001 VVB00002 New 1VVB00003 VVB00004 Active 1VVB00005 VVB00006 New 1

how to concatenate o as perfix in case statment ?

Posted: 08 May 2013 08:09 PM PDT

Hi All,select Case when MONTH(GETDATE())=1then 12 when LEN(MONTH(GETDATE()))=2 then MONTH(GETDATE())[b]else right('0'+ convert(varchar(2), MONTH(GETDATE())),4) end[/b]In the above query else statment will come it as to come with zero(0) as prefix. AM expecting 05 as result . but am getting only 5 . zero is not considering as varchar.Please let me know the solution for this ?::(Regards,Ravi@sql

Query Improvement

Posted: 08 May 2013 06:14 PM PDT

When I executet his query it takes a lot of time...Can anyone plz help in this?SELECT id, english_nameFROM ys_movementsWHERE is_private =0AND idIN (SELECT object_idFROM profiles_useritemsWHERE content_type_id = ( SELECT idFROM django_content_typeWHERE name ="movements"AND app_label ="yoga") AND user_idIN (SELECT target_user_idFROM follow_followWHERE user_id =8AND target_user_id NOT IN (SELECT idFROM auth_userWHERE is_staff =1)))

how to merge one column values with same primary key

Posted: 08 May 2013 07:49 AM PDT

Hello, Please help me here with below Query SELECT AcctNo, PlanCount = COUNT(Model_no), PlanName = Case COUNT(Model_no) When 1 then a.Model_no when 0 then NULL else 'Multi Model' end FROM dbo.cpDetails D LEFT JOIN TBACTDetails A ON D.AcctNo = A.Social WHERE model_NAME IS NULL GROUP BY AcctNo --, Case when COUNT(Model_no)= 1 then a.Model_no else 'Multi Model' end ORDER BY 2 DESC,1 i got below error ---Column 'TBACTDetails.Model_no' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. then i placed max(a.model) it is worked, i do get results but the problem First Question is is is it correct way of doing or any otherway without use Max() function ? Second Question is along with abouve results, if COUNT(model_no) > 1 then (i.e., this model has more than one model name values in model name columns) how can i show both values in one column (seperated by ; ) for example Model_No, Model_Name 100, Zirrard 100, Modached in the results i would like to get as 100,2,'Multi Model','Zirrard; ModaChed' Please assist me how to get this new column ('Zirrard; ModaChed') where count(*) > 1 Thanks in advance MIlan

CROSS APPLY Question

Posted: 08 May 2013 04:40 AM PDT

Hello All,I had a request to concatenate the various entries of a specific column into one row per server. In other words, a server can have multiple entries of a specific column resulting in multiple rows for a single server:Server Name MOTS EntryServer A catServer B catServer C catServer C dogServer C mouseThe user requested to have all of the MOTS Entries for Server C in one row:Server Name MOTS EntryServer A catServer B catServer C cat, dog, mouseThe query I wrote using a CROSS APPLY works except it takes almost 12 minutes to complete which is not good. There are over 24k rows in the result set. Hardware is not an issue because it's running on server class hardware - XEON processors (8), 16GB RAM, etc. Is that normal for CROSS APPLY type queries to take that long to render a result set? Is there a better approach to accomplish the same results?I've included the actual query being used for this.Any assistance of how to improve the run time would be greatly appreciated!Thanks in advanceRonnieselect DISTINCT [WITT DB ID] ,[Server Name] ,SUBSTRING(D.[MOTS Acronym],1,LEN(D.[MOTS Acronym]) -1) ,[Server Status] ,[Support Org] ,[AIA Certification] ,[App Contact] ,[Supervisor ID] ,[VP SBCUID] ,[VP Last Name]from v_MOTS_RELATED_CONTACT_DATA_N p1CROSS APPLY(SELECT [MOTS Acronym] + ', ' FROM v_MOTS_RELATED_CONTACT_DATA_N p2 WHERE p2.[WITT DB ID] = p1.[WITT DB ID] ORDER BY [MOTS Acronym] FOR XML PATH('') ) D ( [MOTS Acronym] )ORDER BY [Server Name]
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog