Friday, April 12, 2013

[SQL Server 2008 issues] Assigning Default Constraint to a column based on another Column...

[SQL Server 2008 issues] Assigning Default Constraint to a column based on another Column...

Assigning Default Constraint to a column based on another Column...

Posted: 11 Apr 2013 07:11 PM PDT

Hi All,I have two Columns in my Table Status A and Status B. I want to create a Default Constraint for column Status B based on the Value from Status A.If Status A = 1 then Status B = 1If Status A = 0 then Status B = 0 ...Both are Bit Data type. Is it possible to Create a Default Constraint to assign Status B with value in Status A.Thanks in Advance...

Pushing Data from T-SQL to Visual Studio While Debugging

Posted: 06 Apr 2013 07:52 PM PDT

I am debugging my T-SQL code in Visual Studio ... is it possible for me to write something in my Transact-SQL code and have it pushed in either the Output Window or Immediate Window of Visual Studio... if yes how do I do it?If I were writing a VB code for example it is simple .. I can just write debug.print "text write" ... and boom the text I want to end up in the Immediate Window gets there ... so how about in T-SQL being debugged in VS, how is it done?I have spent hours in books and google without getting any closer to the answer I am seeking and I am hoping people out there who have more experience would shed light and give me some enlightenment on this.This technique if possible would help me a lot in my debugging work. Thanks so much for all the help.Matt

SSIS: Using a Full Result Set variable in a Data Flow Task

Posted: 05 Feb 2010 01:33 AM PST

I attempting to find a solution for data extraction from a remote Oracle server using SSIS. I have a list of several hundred thousand IDs (Primary Key) in my local SQL Server database. What I would like to do is pull back the matching records in a Oracle database where the ID is shared. I do not have write rights on the remote Oracle database, so I am not able to load the list of records to a table on that server and use it in a join to extract the records I am interested in. What I was was wondering is if there is a way to pass a Full Result Set Variable to a Data Flow Task (essentially passing the list of ID's to be used in a WHERE clause of the statement. I am able to successfully build the Execute SQL Task to write the list to a object variable, but I am struggling with the next step, which is passing the list to the Data Flow Task. Any help would be greatly appreciated or if there is a different method that one has had success with in the past to solve a similiar solution I would definitely be interested in trying that option.Thanks in advance.

Adding to Sales Quantities

Posted: 16 Mar 2013 03:23 AM PDT

Hi I have a table that forecasts our Sales for the upcoming year. It contains the following columns: Year (DATE), Company (VARCHAR), Product (VARCHAR), ProductType (VARCHAR), LastYearsSales (INT), ForecastSales (INT).I would like to increase the ForecastSales for particular ProductTypes in the table. So for example for all the LEISURE products I would like to increase the forecasted sales figures by 1000. I don't want to add a 1000 to each LEISURE product but share the 1000 over all of the LEISURE products ie if there were 10 LEISURE products in the table then increase each of their sales by 100 (1000/10). Thanks in advance.BO

Add Monthly Revenue together - must be an easier way ...

Posted: 11 Apr 2013 02:37 PM PDT

Hi,I have a Cube file with 24 months of data & one field has the Month/Yr & another containing each months Revenue amounts.I want to nominate 3 consecutive months & add those months Revenue data together.Rather than use something like [Month].&[201206],[Revenue] + [Month].&[201207],[Revenue] + [Month].&[201208],[Revenue] is there a better way?Thanks

Please help=SQL 2008 backup script on all the user databases excluding the databases end with SPoint

Posted: 11 Apr 2013 03:23 AM PDT

HI All,Please provide me a script which takes a back up of all user databases in sql 2008 except for few that the db name end with SPoint. Can you please help me out?

Query Help

Posted: 11 Apr 2013 03:05 AM PDT

HelloI need one help to develop logicplease help me to this [code="sql"]create table #Enrollement(StudentID Varchar(10),SchoolID Int,EntryDate int,EntryCode Char(3),WithdrawalDate int,WithdrawalCode char(3),SchoolYear int)insert into #Enrollement values ('0082600',101,20120830,'A',20120128,NULL,2012)insert into #Enrollement values ('0082600',103,20130201,'A',20991231,NULL,2012)create table #Student(StudentKey int,StudentID Varchar(10),CurrentIEPStatus int,FRLunch int,EnrolledSchoolID int,EnrolledSchoolKey int,StartDate int,EndDate int,CurrentFlag int)insert into #Student values (740681,'0082600',0,0,101,6540,20120830,20120930,0)insert into #Student values (740682,'0082600',0,1,101,6540,20120930,20991231,1)[/code]in requirement, based on EndDate in #Student, I need to display StudentKey and EnrolledSchoolKey. I try this [code="sql"]select Distinct E.StudentID, MAX(S.StudentKey) AS StudentKey, --S.EnrolledSchoolID, E.SchoolID, MAX(s.EnrolledSchoolKey) AS SchoolKey, E.EntryDate, E.EntryCode, E.WithDrawalDate, E.WithDrawalCode from #Student sjoin #Enrollement Eon E.StudentID = S.StudentID where S.EndDate <=e.withdrawalDateand s.StudentID = '0082600'group by E.StudentID, E.SchoolID, E.EntryDate, E.EntryCode, E.WithDrawalDate, E.WithDrawalCode, E.SchoolYearorder by StudentKey[/code]and i got output as below[code="plain"]StudentID StudentKey SchoolID SchoolKey EntryDate EntryCode WithDrawalDate WithDrawalCode0082600 740682 103 6540 20130201 A 20991231 NULL[/code]but desired out put is[code="plain"]StudentID StudentKey SchoolID SchoolKey EntryDate EntryCode WithDrawalDate WithDrawalCode0082600 740681 101 6540 20120830 A 20120128 NULL0082600 740682 103 6540 20130201 A 20991231 NULL[/code]Please help me to do this

To Index, or Not to Index

Posted: 11 Apr 2013 01:45 AM PDT

I have a huge table (80 mil) records where I need to append an extra URL column. The query is kind of like:Update a set URL = b.URLfrom ainner join bon a.ID = b.IDwhere a.URL is not nullI'm doing batch updates right now, but the progress is very slow. I'm debating if I should index on a.URL, which is a varchar(500) field and the table already has massive indexes which cannot be dropped because they are used by production. What's your take on this, to index, or not to index on the URL column? Thanks!

Divide by zero error message

Posted: 10 Apr 2013 08:21 PM PDT

Dear All,I'm getting the following error message Msg 8134, Level 16, State 1, Procedure "stored procedure name", Line 149Divide by zero error encountered, and I don't seem to find a way to fix the issue. I've looked at some Forums and one of them suggested that using SET ARITHABORT OFFGOSET ANSI_WARNINGS OFFGOShould resolve the problem, which it does but for some reason after I've altered the Stored Procedure to include it and reopen the SP, SET ARITHABORT OFFGOSET ANSI_WARNINGS OFFGO is no longer there. I'm not sure what I'm missing or are there other ways to resolve the error message?Thank you in advance!

Error when executing dts from BIDS

Posted: 11 Apr 2013 08:08 AM PDT

For some reason I can no longer execute(test) dts packages from BIDS on my Windows 7 laptop.I get the following error after waiting about 1 minute. "Cannot communicate with the debug host process. Failed to obtain child process active object."I have tried repair install, uninstall-reinstall and I've had my laptop reimaged and started from scratch intalling SQL client again.Any idea what might be causing this?

Parallel Data Warehouse for OLTP

Posted: 08 Apr 2013 05:10 PM PDT

Hi,Is Parallel Data Warehouse feature of SQL Server 2008 R2 targeted only for OLAP/BI impementations?Can we use it for hosting OLTP databases as well?Thanks.

Database Query

Posted: 11 Apr 2013 07:45 AM PDT

Hi,I have couple of questions related to sql server, though I googled it, did not get any definite answer. I am sure one of you SSC guru would help me to understand SQL Server in a better way :-).Why the joining between integer columns are faster?How the sql server joins strings internally, does it checks character by character or uses ASCII to compare strings?Does the database engine performs an order by on the group by column first to group the data easily?Thanks in advance.

How to install BIDS for using SSIS for existing Sql Server 2008 r2?

Posted: 11 Apr 2013 02:46 AM PDT

Can someone please tell me how to get BIDS installed for developing SSIS packages to the existing SQL Server 2008 R2 Enterprise edition? and where to find it?In the past I developed and used SSIS packages in Sql Server 2005 but not in Sql Server 2008. I need to start working on a new project that I would like to use SSIS packages to import raw data files into SQL Server 2008 Staging database/tables. please help me how to get BIDS or required tools started to be able to create and use SSIS packages.Thanks!

Convert string into xml and insert Sql Server

Posted: 11 Apr 2013 04:34 AM PDT

We have a sql server 2008 R2 database table with an xml stored in a column as a VARCHAR data type.I now have to fetch some of the elements of the xml. So I want to first convert the xml stored as a VARCHAR data type, to an xml stored as an xml data type.example : Table A Id(int) , ProductXML (varchar(max))Table BId(int), ProductXML(XML)I want to convert the ProductXML from Table A into XML data type and insert into Table B.I tried using the CAST() and CONVERT() function as shown below :insert into TableB (ProductXML)select CAST(ProductXML as XML) from TableA;similarly tried convert but I get an error 'XML Parsing : unable to switch encoding'.Is there any way I can convert the varchar entries in the table into xml entries ?About the *XML* : The XML is huge with many nodes , and its structure changes dynamically. Example : One row can have and XML entry for 1 product and another row can have an xml entry for multiple products.

Data Dictionary in SQL

Posted: 11 Apr 2013 06:22 AM PDT

Anyone know what's equivalent to data dictionary in SQL server? I think it's the same as DMVs?I need to create a login which have read only access to data dictionary in SQL server.I think the requestor means, to create a login and map to master db, with db_datareader permission, this will give the person to select from DMVs. Any suggestions?Thanks,SueTons.

Difference between Index and Table Compression?

Posted: 11 Apr 2013 03:36 AM PDT

If i have 1 Clustered ,4 Non-clustered indices and rebuild all these indices with Page compression enabled would that be equal to Table compression? If not whats the difference?

Unable to view sql error log on Sql Server 2008 r2

Posted: 11 Apr 2013 04:05 AM PDT

Hi,i have sql server 2008 r2 cluster on win 2008 server.We are not able view the sql error log through SSMS and by xp_readerrorlog....Failed to open loopback connection. Please see event log for more information.Msg 22004, Level 16, State 1, Line 0error log location not foundPlease let me know if any one faced same issue.MAny thanks,

Dropping and re-creating full-text catalog

Posted: 11 Apr 2013 04:22 AM PDT

Hi, the user is reporting the following error, i have not done this previously. Any suggestions would be appreciated."Users having an issue with "databaseName" . Users receiving the following error. Full-text catalog 'catalogName' is in an unusable state. Drop and re-create this full-text catalog."Anyone have any script to re-create this, and also what should I make sure before doing this, this is a production change.Thanks,SueTons.

bcp CSV to DB table

Posted: 11 Apr 2013 01:15 AM PDT

To all:I am trying to import a CSV file into a database table using bcp, however I am getting the message "Unexpected EOF encountered in the BCP data-file"I have looked at many forum posts and articles on Google, but just can't seem to find the solution.Here is my code:declare @cmd varchar(8000)select @cmd = 'bcp STH_D_Test.dbo.BCP_Test in "C:\TestFile.csv" -c -T' exec master.dbo.xp_cmdshell @cmdThank you for any help in advance! =)

Restore/Backup minimal permissions

Posted: 02 Nov 2010 11:47 PM PDT

We use Netbackup for our SQL servers to backup and restore databases. I would like the service account used by Netbackup to have as limited permissions as possible. The account should be able to backup and restore a db without being able to read any of the content. Right now the account jobs fail if the service account is not in the sysadmin role. I removed the account from sysadmin and limited it to dbcreator and public but the job fail. Any idea how to setup an account so that people who know the service account password can't log in with that account and read db information?Thanks!

index stats Review

Posted: 11 Apr 2013 03:00 AM PDT

I have a table with the 2 indexes performing as shown below:Index Name Index Type Queries Which Read Queries Which Wrote Reads/WriteIX_index2 NONCLUSTERED 1283151 2 641575PK_index1 CLUSTERED 516435 439378 1Looking at the above stats, would I be better changing the Primary Key to index2 and making index1 the non clustered index ?

SSRS configured through PowerShell (SQLPS)

Posted: 11 Apr 2013 02:42 AM PDT

Hi!I have a question about the SSRS and Powershell (SQLPS)We trying to configure the SSRS through POWERSHELL, we are able to set all settings of SSRS except the Windows Services account and password.Someone have any idea how to do this?Sincerely Eric

Cursor help

Posted: 10 Apr 2013 09:13 PM PDT

Hi,I have made a stored procedure with a cursor:[code="sql"]USE [DB_admin1]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================ALTER PROCEDURE [rept].[suscribedSystems] -- Add the parameters for the stored procedure here @startDate DATETIME, @endDate DATETIMEASBEGIN SET NOCOUNT ON; DECLARE @SubID INT, @sql VARCHAR(8000) DECLARE crSystem CURSOR FOR SELECT [SystemNum] ,[Date] AS SubStart , (SELECT TOP (1) [Date] FROM [DB_admin1].[dbo].[tblRenewals] REN2 WHERE REN2.[Date] > REN.[Date] AND REN.SystemNum = REN2.SystemNum ORDER BY [Date] ASC ) AS SubEnd FROM [DB_admin1].[dbo].[tblRenewals] REN WHERE [Date] <= @startDate AND (SELECT TOP (1) [Date] FROM [DB_admin1].[dbo].[tblRenewals] REN2 WHERE REN2.[Date] > REN.[Date] AND REN.SystemNum = REN2.SystemNum ORDER BY [Date] ASC ) > @endDate AND (SELECT TOP (1) [Date] FROM [DB_admin1].[dbo].[tblRenewals] REN2 WHERE REN2.[Date] > REN.[Date] AND REN.SystemNum = REN2.SystemNum ORDER BY [Date] ASC ) <> '2999-01-01' ORDER BY SubStart OPEN crSystem FETCH NEXT FROM crSystem INTO @SubId WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'INSERT INTO rept.tblSubscribedSystems ([systemNum] ,[dtuStart] ,[dtuEnd] ,[total]) VALUES (@SubID ,@startDate ,@endDate ,(SELECT COUNT (*) FROM rept.tblSubscribedSystems) )' --PRINT @sql EXEC (@sql) FETCH NEXT FROM crSystem INTO @SubId END CLOSE crSystem; DEALLOCATE crSystem; END[/code]When i run the stored procedure--EXEC [rept].[suscribedSystems] '2009-05-01', '2009-06-01'I get this error:Msg 16924, Level 16, State 1, Procedure suscribedSystems, Line 52Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.Thank you in advance for your time and help.

Row count per filegroup

Posted: 11 Apr 2013 12:10 AM PDT

Hi,I have a partitioned table and its spread on filegroups monthly.My question is how do i find out how many records does each individual file have?thanx

Alternative for Excell (Access).

Posted: 10 Apr 2013 10:59 PM PDT

Dear reader,[b]It's not difficult to convince any manager; that storage in SQL-server would be a good idea. But because of lack of interaction software for the manager, most (if not all) managers [u]keep using spreadsheats[/u] to accumulate and spread data/info into the organisation.[/b]SQL-server is a good: 1. For the storage of data.2. For BI (reporting, intergration, analys and dataminging).3. For protection of the data. (Availability, data loss, and constriants on the data).But SQL-server does [b]not[/b] offer a good solution for user interaction with the database.So what is needed is a frontend for SQL-server which is acceptable for managers to work with.The building of a databasemodel and implementing a database is not a problem.The problem lies in the building of a frontend. Where managers expect the solution to be ready before they have thought of it and which has an 'acceptable' userinterface. Specifications are never complete and rarely very clear.The amount of data is always very limited, the number of users is limited. The requirements for safety, security and availability are low.As everybody knows the problem with spreadsheat is: copies, copies and more copies, versions, no good search or retrieval system. But they are still the very favorable method for managers to accumulate, and spread their data.We have a number of set's of data which would be very suitable to be stored in SQL-server. But the lack of frontend's prevents this data to be put in a 'solid' database.Access can be used as a frontend for SQL-server. Allthough this is a solution it is not a brilliant solution.Resources: The SQL-server database is available, all users have a PC with office components. But there are not plenty of resources to build a full blown application for every requirement.Suggestions? Solutions?Please help the managers.Thanks for your time and attention,ben brugman

Need help on Powershell for SQL 2008 R2

Posted: 09 Apr 2013 06:25 AM PDT

Folks ,I created powershell script to be used with SQL 2008 which uses Invoke-sqlcmd . The script runs fine on SQL 2008 environment , however when i try to run the same on other server with SQL 2005 installed on it , i get an error . On troubleshooting further it was found that "Microsoft® Windows PowerShell Extensions for SQL Server® 2008 R2" needs to be installed on the server . Can someone guide if the above installation for Powershell extensions has any prerequisite . The environment on this this needs to be installed is production and hence every positive and negative points needs to be taken into consideration .Kindly help !!!

Copy a table from one db to another including all configurations

Posted: 11 Apr 2013 01:22 AM PDT

How to copy a table from one db to another including all configurations, such as index, primary key...I use select * into newtable from originaltablebut missing indexes.

Unused Indexes

Posted: 10 Apr 2013 11:01 PM PDT

Hello,I have been running the DBTA on a database to get some recommendations, I have the results, nothing has been applied.Afterwards I ran the script below that I got from the web, I first ran it in dev (where DBTA had not been run) to test it, I then ran it in live to compare the output. The script appears to have also listed all the indexes listed in the DBTA results as well. I have checked to make sure these were not applied in live, why have all the dta indexes also been listed? Where are they and how can I prevent them from being listed? I only want to list the indexes currently in use. After the code I have a another question.SET NOCOUNT ON;DECLARE @First [smallint] ,@Last [smallint] ,@IsUnique [smallint] ,@HasNonKeyCols [char](1) ,@TableName [varchar](256) ,@IndexName [varchar](256) ,@IndexType [varchar](13) ,@IndexColumns [varchar](1000) ,@IncludedColumns [varchar](1000) ,@IndexColsOrder [varchar](1000) ,@IncludedColsOrder [varchar](1000) DECLARE @Indexes TABLE ([RowNo] [smallint] IDENTITY(1, 1) ,[TableName] [varchar](256) ,[IndexName] [varchar](256) ,[IsUnique] [smallint] ,[IndexType] [varchar](13))DECLARE @AllIndexes TABLE ([RowNo] [smallint] IDENTITY(1, 1) ,[TableName] [varchar](256) ,[IndexName] [varchar](256) ,[IndexType] [varchar](13) ,[KeyColumns] [varchar](512) ,[NonKeyColumns] [varchar](512) ,[KeyColumnsOrder] [varchar](512) ,[NonKeyColumnsOrder] [varchar](512) ,[IsUnique] [char](1) ,[HasNonKeyColumns] [char](1))IF OBJECT_ID('Tempdb.dbo.#Temp') IS NOT NULL DROP TABLE #TempSELECT o.[object_id] AS [ObjectID] ,OBJECT_NAME(o.[object_id]) AS [TableName] ,i.[index_id] AS [IndexID] ,i.[name] AS [IndexName] ,CASE i.[type] WHEN 0 THEN 'Heap' WHEN 1 THEN 'Clustered' WHEN 2 THEN 'Non-Clustered' WHEN 3 THEN 'XML' ELSE 'Unknown' END AS [IndexType] ,ic.[column_id] AS [ColumnID] ,c.[name] AS [ColumnName] ,ic.[is_included_column] [IncludedColumns] ,i.[is_unique] AS [IsUnique]INTO #TempFROM sys.indexes iINNER JOIN sys.objects o ON i.object_id = o.object_id AND o.type = 'U' AND i.index_id > 0INNER JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_idINNER JOIN sys.columns c ON c.column_id = ic.column_id AND c.object_id = ic.object_idINSERT INTO @IndexesSELECT DISTINCT [TableName] ,[IndexName] ,[IsUnique] ,[IndexType]FROM #TempSELECT @First = MIN([RowNo]) FROM @IndexesSELECT @Last = MAX([RowNo]) FROM @IndexesWHILE @First <= @LastBEGIN SET @IndexColumns = NULL SET @IncludedColumns = NULL SET @IncludedColsOrder = NULL SET @IndexColsOrder = NULL SELECT @TableName = [TableName] ,@IndexName = [IndexName] ,@IsUnique = [IsUnique] ,@IndexType = [IndexType] FROM @Indexes WHERE [RowNo] = @First SELECT @IndexColumns = COALESCE(@IndexColumns + ', ', '') + [ColumnName] FROM #Temp WHERE [TableName] = @TableName AND [IndexName] = @IndexName AND [IncludedColumns] = 0 ORDER BY [IndexName], [ColumnName] SELECT @IncludedColumns = COALESCE(@IncludedColumns+ ', ', '') + [ColumnName] FROM #Temp WHERE [TableName] = @TableName AND [IndexName] = @IndexName AND [IncludedColumns] = 1 ORDER BY [IndexName], [ColumnName] SELECT @IndexColsOrder = COALESCE(@IndexColsOrder + ', ', '') + [ColumnName] FROM #Temp WHERE [TableName] = @TableName AND [IndexName] = @IndexName AND [IncludedColumns] = 0 SELECT @IncludedColsOrder = COALESCE(@IncludedColsOrder + ', ', '') + [ColumnName] FROM #Temp WHERE [TableName] = @TableName AND [IndexName] = @IndexName AND [IncludedColumns] = 1 SET @HasNonKeyCols = 'N' IF @IncludedColumns IS NOT NULL BEGIN SET @HasNonKeyCols = 'Y' END INSERT INTO @AllIndexes ([TableName] ,[IndexName] ,[IndexType] ,[IsUnique] ,[KeyColumns] ,[KeyColumnsOrder] ,[HasNonKeyColumns] ,[NonKeyColumns] ,[NonKeyColumnsOrder] ) SELECT @TableName ,@IndexName ,@IndexType ,CASE @IsUnique WHEN 1 THEN 'Y' WHEN 0 THEN 'N' END ,@IndexColumns ,@IndexColsOrder ,@HasNonKeyCols ,@IncludedColumns ,@IncludedColsOrder SET @First = @First + 1 ENDSELECT 'Listing All Indexes' AS [Comments]SELECT [TableName] ,[IndexName] ,[IndexType] ,[KeyColumns] ,[HasNonKeyColumns] ,[NonKeyColumns] ,[KeyColumnsOrder] ,[NonKeyColumnsOrder] ,[IsUnique]FROM @AllIndexesSELECT 'Listing Duplicate Indexes' AS [Comments]SELECT DISTINCT a1.[TableName] ,a1.[IndexName] ,a1.[IndexType] ,a1.[KeyColumns] ,a1.[HasNonKeyColumns] ,a1.[NonKeyColumns] ,a1.[KeyColumnsOrder] ,a1.[NonKeyColumnsOrder] ,a1.[IsUnique]FROM @AllIndexes a1JOIN @AllIndexes a2ON a1.[TableName] = a2.TableNameAND a1.[IndexName] <> a2.[IndexName]AND a1.[KeyColumns] = a2.[KeyColumns] AND ISNULL(a1.[NonKeyColumns], '') = ISNULL(a2.[NonKeyColumns], '') WHERE a1.[IndexType] <> 'XML'SET NOCOUNT OFF;Is it bad practice to have a non clusted index on top of a clusted index?Thanks for any help.Regards,D.

Initial size for TempDb data and Log file?

Posted: 12 Sep 2011 10:33 AM PDT

Hi,We have installed SQL Server 2008 R2 SP1 instance and it's having Share Point 2010 databases.We have 2 dedicated drives for Tempdb on SAN with 50 GB space. Both tempdb data & log files are created with default size. I would like to presize them. What are the best values to start with?U ->Tempdbdata having tempdb.mdf fileV->Tempdblog having templog.ldf fileThanks

Converting Filestream to varchar/xml

Posted: 11 Apr 2013 01:05 AM PDT

Hello everyone, We have an xml that has been converted into a filestream format and stored into a column of a table. I now have to extract some of the data from the xml So I would like to know if there is a way of converting a fiestream datatype (varbinary(max) Filestream) into a varchar or xml datatype ?

PIVOTing multiple columns?

Posted: 11 Apr 2012 08:30 AM PDT

I'm working on a PIVOT, and I can't see to get it to work. Here is the PIVOT query:[code="sql"]SELECT *FROM( SELECT Listing.Person_ID ,Person.Person_Gender FROM Listing JOIN Person ON Listing.Person_ID = Person.Person_ID WHERE Listing.Program_Version_Code_Listing = 'AR-2016' AND Listing.Entity_Status_Listing IN ('Accept', 'Conditional Accept') GROUP BY Listing.Person_ID ,Person.Person_Gender) AS sPIVOT( COUNT(s.Person_ID) FOR Person_Gender IN (Male, Female, [Not Specified])) as p[/code]Now I need to somehow get this query inside the above query. Obviously I written it as a standalone SELECT statement here, so I know it will need to be modified.[code="sql"]SELECT (AVG(DATEDIFF(DD,Birth_Date_Person,GETDATE()))/365.25) AS 'Avg Age'FROM Listing JOIN Person ON Listing.Person_ID = Person.Person_IDWHERE Listing.Program_Version_Code_Listing = 'AR-2016'AND Listing.Entity_Status_Listing IN ('Accept', 'Conditional Accept')[/code]The final results will need to be something like this:[code="plain"]Male Female Not Specified Avg Age100 89 0 19.8541[/code]I've been wrestling with this all day. Does anyone have any thoughts?

Weekly report through whole year

Posted: 10 Apr 2013 10:51 PM PDT

Hi thereI have 2 tables and sample data:--------------------------------------------------------------------------------------------------------------------------------------------------------CREATE TABLE [dbo].[tblProduct]( [ProductID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NOT NULL, CONSTRAINT [PK_tblProduct] PRIMARY KEY CLUSTERED ( [ProductID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]--------------------------------------------------------------------------------------------------------------------------------------------------------CREATE TABLE [dbo].[tblOrder]( [OrderID] [int] IDENTITY(1,1) NOT NULL, [ProductID] [int] NOT NULL, [OrderDate] [datetime] NOT NULL, CONSTRAINT [PK_tblOrder] PRIMARY KEY CLUSTERED ( [OrderID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]--------------------------------------------------------------------------------------------------------------------------------------------------------INSERT INTO tblProduct (Name) VALUES('Coffee')INSERT INTO tblProduct (Name) VALUES('Tea')INSERT INTO tblProduct (Name) VALUES('Lager')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (1, '2013-01-01 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (1, '2013-01-08 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (2, '2013-01-08 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (3, '2013-01-09 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (1, '2013-01-10 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (2, '2013-01-10 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (3, '2013-01-11 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (1, '2013-01-11 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (2, '2013-01-15 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (1, '2013-01-18 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (1, '2013-01-30 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (2, '2013-05-01 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (3, '2013-07-15 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (1, '2013-09-23 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (1, '2013-10-01 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (1, '2013-10-08 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (2, '2013-10-11 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (1, '2013-11-01 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (3, '2013-11-06 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (1, '2013-11-08 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (3, '2013-12-10 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (1, '2013-12-25 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (2, '2013-12-30 09:00:00')INSERT INTO tblOrder (ProductID, OrderDate) VALUES (1, '2013-12-30 09:00:00')--------------------------------------------------------------------------------------------------------------------------------------------------------I need to generate report which shows number of orders for each ProductID for each week in year 2013. Ideally would be to generate 54 weeks as pivot table so week1, week2 would be columns and 3 rows for 3 products and order count for each product each week.Product
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog