Thursday, September 12, 2013

[SQL Server] Group By with Union

[SQL Server] Group By with Union

Group By with Union

Posted: 12 Sep 2013 01:49 PM PDT

Hello,I am trying to group by the aggregate results by combining multiple tables, below is what I have, but it is not aggregating as it should. Appreciate the help.Here is what I have:Select *from(select [MS], sum ([Vis]) as 'TV',sum(Round([VIS]*[GR],1)) as '# of C's'from [dbo].[Table1] group by [MS]unionselect [MS], sum ([Vis]) as 'TV',sum(Round([VIS]*[GR],1)) as '# of C's'from [dbo].[Table2] group by [MS]) as G1

problem with subtracting one millisecond

Posted: 11 Sep 2013 07:09 PM PDT

Hi,what I am doing seems to be very simple. I just want to subtract one millisecond from midnight to receive as an output 23:59:59.999. Just before this step I am just calculating the last day of the week. Unfortunately when subtracting 1 millisecond then nothing happens but when subtracting 2 milliseconds the ms sql is subtracting 3. Am I doing something wrong?1 millisecond:[code="sql"]DECLARE @TempDate AS DATETIMESET @TempDate = '2013-09-12'SELECT Convert(DateTime, DATEADD(millisecond, -1, DATEADD(ww, DATEDIFF(ww,0,@TempDate) + 1, 0)))2013-09-16 00:00:00.000[/code]2 milliseconds:[code="sql"]DECLARE @TempDate AS DATETIMESET @TempDate = '2013-09-12'SELECT Convert(DateTime, DATEADD(millisecond, -2, DATEADD(ww, DATEDIFF(ww,0,@TempDate) + 1, 0)))2013-09-15 23:59:59.997[/code]

Count all months with "0's" starting today

Posted: 12 Sep 2013 07:17 AM PDT

we are tracking safety events and would like to count the # of consecutive months of 0 events starting from today backward. Maybe I'm overthinking it...butFor example:Today is September and we had 0 events for Falls, it would be 1,Today is September and we had 2 events for Falls in September and 0 in August, it would be 0Today is September and we had 0 events for Falls in September and 0 in August, it would be 2Select SafetyIndicator, count(safetyevent)From SafetyEventTableGroup by SafetyIndicatorWould like to have it displayed as:Indicator, CountSafetyEvent1, 4SafetyEvent2, 3SafetyEvent3, 0

Creating Database: Error Message.

Posted: 12 Sep 2013 01:45 AM PDT

[code="sql"] CREATE DATABASE ApressFinancial ON PRIMARY( NAME = N'ApressFinancial',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ApressFinancial.mdf' , Size = 3072KB, Maxsize = Unlimited, FILEGROWTH = 1024KB )LOG ON(Name = N'ApressFinancial_log',FileName= N' C:\Program files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ApressFinancial_log.ldf', Size = 1024KB, Maxsize = 2048GB, FILEGROWTH = 10%)Collate SQL_Latin1_General_CP1_CI_ASgo [/code]Getting an error message that says: Msg 5133, Level 16, State 1, Line 1Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ApressFinancial.mdf" failed with the operating system error 3(The system cannot find the path specified.).Msg 1802, Level 16, State 1, Line 1CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

Selecting records from an associated table

Posted: 12 Sep 2013 06:25 AM PDT

CREATE TABLE [dbo].[A]( [ID] [smallint] NOT NULL, Name nvarchar(10), [AssocTableName] [nvarchar](50) NULL,data: 1 TableType AssociatedTable1CREATE TABLE [dbo].[AssociatedTable1]( [ID] [smallint] NOT NULL, [Desc] [nvarchar](80) NULL, data: 1 Supp 2 Sup2Is there any way i can join these two tables?

making records unique

Posted: 11 Sep 2013 10:51 PM PDT

Hi everyone, I've a table with 15 fields with 70,000 rows of data. I now realise I need to make each record unique. I could have gone down the road of using if exists ... statement in my Store Procedure to stop duplicates, but I'd have to test each field to see if they all make a duplicate row. I did think about making all the fields a primary key. The problem with that is I've allowed Nulls in some of the fields, I did think of updating the Null fields with 'N/A' but I can't remember the SQL statment that does it. Can someone please help me resolve this with a suggestion.

restore database bakup

Posted: 11 Sep 2013 09:49 PM PDT

how to restore a database backup in sql server 2008 on particular time scheduled

Add a record to a replicated table.....

Posted: 20 Aug 2013 09:59 PM PDT

HiI am quite new to replication but i did my research and have setup transactional replication between two servers and it is has been working well....until now.I am trying to add a record to a particular table that is marked for replication on the publisher side and i receive the following error.[b]Error message : 'Could not find stored procedure dbo.sp_MSgetreplnick' [/b]Any ideas on this? Im assuming this is a system SP of some kind and it has something to do with replicationAny advice would be much appreciated.Thanks

SSIS Assigning a value to a package variable

Posted: 23 Jul 2012 07:31 AM PDT

Hi AllHave a foreach loop that iterates through files. If an error it writes log and I simple want to keep a count of how many files failed. I created a package variable (package scope int32, value 0)In my error handler I have this (VB):Dts.Variables("User::ERR_ErrorCount").Value = Dts.Variables("User::ERR_ErrorCount").Value + 1I've checked the spelling and case several times and it's correct. (tried with and without "User::")Here's the errorError: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there. ---> System.Runtime.InteropServices.COMException (0xC0010009): The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.I've looke din BOL and online and the syntax seems to be correctIt's driving me mad. Gotta be something real basicAny suggestions?RegardsMark
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog