Thursday, September 5, 2013

[T-SQL] Need SQL help

[T-SQL] Need SQL help

Need SQL help

Posted: 05 Sep 2013 12:49 AM PDT

I am trying to schedule emails. I have to write my own tables. I am not the best SQL coder, so I may be way off. I am really having a hard time withrecurrance. If the recurrance is 2 and the frequency is 3. It is every 2 months.The code is what I have so far. Recurrance is not added. Sql does not seem to be workingDECLARE @date_to_check datetimeSET @date_to_check = GETDATE()--SET @date_to_check = '2013-09-02'SELECT rsf.ID, te.Name,rsf.Data, rsf.ReportFormat, rsf.ReportTemplateFROM tblReportScheduleFormat AS rsf INNER JOIN tblScheuduleFrequency AS sf ON sf.ID = rsf.FrequencyId INNER JOIN tblTrkEmail AS te ON rsf.EmailId = te.IdWHERE @date_to_check between rsf.StartDate and Coalesce(@date_to_check, NULL) AND (rsf.FrequencyId = 3) AND (rsf.DayNumber = DAY(@date_to_check)) OR (rsf.FrequencyId = 4) AND (rsf.DayNumber = DAY(@date_to_check)) OR (rsf.FrequencyId IN (1, 2)) AND (DATEDIFF(DAY, 0, @date_to_check) = 0) AND (rsf.Monday = 1) OR (rsf.FrequencyId IN (1, 2)) AND (DATEDIFF(DAY, 0, @date_to_check) = 1) AND (rsf.Tuesday = 1) OR (rsf.FrequencyId IN (1, 2)) AND (DATEDIFF(DAY, 0, @date_to_check) = 2) AND (rsf.Wednesday = 1) OR (rsf.FrequencyId IN (1, 2)) AND (DATEDIFF(DAY, 0, @date_to_check) = 3) AND (rsf.Thursday = 1) OR (rsf.FrequencyId IN (1, 2)) AND (DATEDIFF(DAY, 0, @date_to_check) = 4) AND (rsf.Friday = 1) CREATE TABLE [dbo].[tblReportScheduleFormat]( [ID] [int] IDENTITY(1,1) NOT NULL, [EmailId] [int] NOT NULL, [StartDate] [date] NULL, [EndDate] [date] NULL, [Data] [varchar](max) NOT NULL, [Sunday] [bit] NOT NULL, [Monday] [bit] NOT NULL, [Tuesday] [bit] NOT NULL, [Wednesday] [bit] NOT NULL, [Thursday] [bit] NOT NULL, [Friday] [bit] NOT NULL, [Saturday] [bit] NOT NULL, [DayNumber] [int] NULL, [FrequencyId] [int] NOT NULL, [Recurrence] [int] NOT NULL, CONSTRAINT [PK_tblReportScheduleFormat] PRIMARY KEY CLUSTERED 1,1,2013-09-03,NULL,Blaaaa,0,1,0,0,0,0,0,NULL,1,22,1,2013-09-04,NULL,blaaa,0,0,0,0,0,0,0,NULL,2,33,2,2013-09-04,NULL,Blaaa,0,0,0,0,0,0,0,15,3,24,3,2013-09-04,NULL,Blaaa,0,0,0,0,0,0,0,NULL,4,1CREATE TABLE [dbo].[tblScheuduleFrequency]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NOT NULL, CONSTRAINT [PK_tblScheuduleFrequency] PRIMARY KEY CLUSTERED 1,Daily2,Weekly3,Monthly4,Yearly

String splitter function

Posted: 04 Sep 2013 10:21 PM PDT

Hi All, I need help with a function which accepts a string and returns in the column format for SQL server 2000.Eg: '6000, 7000, 600000, 3393043, 274878423, 2837423' Result:6000700060000033930432748784232837423.Thanks in advance!

Trying to bypass via tsql a database when it is offline

Posted: 04 Sep 2013 04:13 AM PDT

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'archer' and state = 0)BEGIN use archer SET NOCOUNT ON DECLARE @objectid int DECLARE @indexid int DECLARE @partitioncount bigint DECLARE @schemaname nvarchar(130) DECLARE @objectname nvarchar(130) DECLARE @indexname nvarchar(130) DECLARE @partitionnum bigint DECLARE @partitions bigint DECLARE @frag float DECLARE @command nvarchar(4000) -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function -- and convert object and index IDs to names. SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag INTO #work_to_do FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0 -- Declare the cursor for the list of partitions to be processed. DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do -- Open the cursor. OPEN partitions -- Loop through the partitions. WHILE (1=1) BEGIN; FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag IF @@FETCH_STATUS < 0 BREAK SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid; SELECT @indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding. IF @frag < 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE' IF @frag >= 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD' IF @partitioncount > 1 SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10)) EXEC (@command) PRINT N'Executed: ' + @command END -- Close and deallocate the cursor. CLOSE partitions; DEALLOCATE partitions -- Drop the temporary table. DROP TABLE #work_to_do; ENDIt still executes even if the state of the archer database is 1. I don't know what is wrong. Can anyone help me? Thanks a bunch.Patti

Query Result in Email?

Posted: 04 Sep 2013 05:09 PM PDT

Hi.I have created database mail profile account with SMTP also email realy working.. Could you help me as below Query Result comes to Email?Select A.MACHINE, A.Instance, B.InstanceA, (B.InstanceA-A.Instance) as Diff from (select MACHINE, COUNT(*) as Instance from PROD.dbo.CM_INSTANCE group by MACHINE) A,(Select COUNT(*)/4 as InstanceA from PROD.dbo.CM_INSTANCE) Bwhere (B.InstanceA-A.Instance) < -3 or (B.InstanceA-A.Instance) > 3Output------Machine InstanceInstanceADIffThanksananda

Find multiple status in one Column from different rows

Posted: 04 Sep 2013 05:06 PM PDT

I need to find the multiple relationship with a Customer in one row. It would be able to search based on given criteria. and return results like : Customer: RelationsAA1: Friend, RelativeAA2: Friend, ColeagueAA3: Studenthere is the provided data code:[code="sql"]CREATE TABLE [dbo].[Customers]( [Id] [int] IDENTITY(1,1) NOT NULL, [CustomerName] [varchar](50) NOT NULL, [Status] [varchar](50) NULL, [AddedDatetime] [datetime] NULL, CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ( [Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[Customers] ADD CONSTRAINT [DF_Customers_AddedDatetime] DEFAULT (getdate()) FOR [AddedDatetime]GOinsert into dbo.Customers (CustomerName, [Status]) values ('AA1', 'Friend')insert into dbo.Customers (CustomerName, [Status]) values ('AA2', 'Friend')insert into dbo.Customers (CustomerName, [Status]) values ('AA1', 'Relative')insert into dbo.Customers (CustomerName, [Status]) values ('AA2', 'Coleague')insert into dbo.Customers (CustomerName, [Status]) values ('AA3', 'Student')select * from Customers [/code]please help.Shamshad Ali

how to validate records

Posted: 04 Sep 2013 01:32 AM PDT

I have Table_A in which data is imported from text file ..Table_AEx:SID State ShortName Description UniqueId Year 10001 VA Nut This is NUT 10010101 200110001 PA Com This is Com A0010101 NULL10001 VA EOW This is Com 10010101 2008 Table_B:BID Shortname SID5 NUT 10001 6 Com 10001 211 Eow 10001 212 Eco 10001 213 land 10001 Table_C (Exclusive table):BmpId ExclusiveBmpId5 2115 2125 2136 2116 2137 2118 130 211 5211 6211 7212 5212 6212 7213 5213 6213 7These are exclusive IDsTABLE_D (TblState)SID Stateid State10001 1 DC10001 2 DE10001 3 MD10001 5 NY10001 6 PA10001 7 VA10001 8 WVI joined Tables B, C and D and placed the results in tempTableZZTempTable_ZZBID ShortName EXID State 5 NUT 211 VA 5 NUT 212 VA5 NUT 213 VA6 Com 211 VA6 Com 212 VA6 Com 213 VA211 Eow 5 VA211 Eow 6 VA211 Eow 7 VA212 Eco 5 VA212 Eco 6 VA212 Eco 7 VA213 land 5 VA213 land 6 VA213 land 7 VAThe validation check against TempTable_ZZ is where I am struggling to create. e.g., If the shortname in Table_A is "NUT" which its' BID which is located in table_B is 5, Then check if the BID is 5 and the EXID is either 211, 212, 213 in the TempTable_ZZ. If in TempTable_ZZ BID is 5 and the the EXID are any of the aforementioned then throw an error. Questions is, How do I do this?

Help with query to get monthly data

Posted: 04 Sep 2013 06:15 AM PDT

I need some assistance with a database size report.I have data pulled into a table that has a ReportDate, Database and Size:[code]2013-02-12 00:00:00.000 METRICS 2150.002013-02-21 00:00:00.000 METRICS 2250.002013-02-26 00:00:00.000 METRICS 2250.002013-03-04 00:00:00.000 METRICS 9050.002013-03-08 00:00:00.000 METRICS 2750.002013-03-24 00:00:00.000 METRICS 8850.002013-04-09 00:00:00.000 METRICS 11250.002013-04-21 00:00:00.000 METRICS 7850.00[/code]I need the sql to return the size of the db for the last day of the date of each month. So the size on 2013-02-26, 2013-03-24 and 2013-04-21.Note that I can't just use the last day of each month because some months don't have it....Also I would like the query to report the month as column headers so it would look like this:[code]DatabaseName Jan Feb Mar Apr METRICS 0 2250 8850 7850[/code]Thank you in advance for any assistance.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog