Saturday, August 31, 2013

[SQL Server 2008 issues] How to put If Else condition to check one coulmn value is present in another set of column values in sql 2008

[SQL Server 2008 issues] How to put If Else condition to check one coulmn value is present in another set of column values in sql 2008

How to put If Else condition to check one coulmn value is present in another set of column values in sql 2008

Posted: 30 Aug 2013 04:36 PM PDT

[font="Courier New"][size="3"]Hi All,I have main data set which consists of lacks of records and below is my queryDECLARE @USER_DT DATETIME SET @USER_DT = '2013-08-01'DECLARE @RANGE_DATE DATETIMESELECT distinct @RANGE_DATE = RANGE_DATE FROM dbo.TABLE WHERE RANGE_DATE >= (CONVERT(VARCHAR(8), DATEADD (M, -2, GETDATE()), 21)+ '26') AND RANGE_DATE <= (CONVERT(VARCHAR(8), DATEADD (M, -1, GETDATE()), 21)+ '25')IF(@USER_DT = @RANGE_DATE ) BEGINSELECT * FROM MAIN_TABLEWHERE USER_DATE = @USER_DT AND RANGE_DATE >= (CONVERT(VARCHAR(8), DATEADD (M, -2, GETDATE()), 21)+ '26') AND RANGE_DATE <= (CONVERT(VARCHAR(8), DATEADD (M, -1, GETDATE()), 21)+ '25')ENDBEGINSELECT * FROM MAIN_TABLEWHERE USER_DATE = @USER_DT AND RANGE_DATE >= (CONVERT(VARCHAR(8), DATEADD (M, -1, GETDATE()), 21)+ '26') AND RANGE_DATE <= (CONVERT(VARCHAR(8), DATEADD (M, 0, GETDATE()), 21)+ '25')ENDi.e. @USER_DT is for user selection in SSRS Report window. Now, let say he has enter '2013-08-01' now second parameter @RANGE_DT is created for storing distinct dates values now my problem is I want to check that user entered date in @Range_DT and if it is present as shown in the above query i will get desired output. But thing is we can check only single value in If-Else and I wanna check single date value is present or not and displya output accordingly. Is there any way to do this (This later i am going to put in SSRS report).[/size][/font]

I partitioning ever simple?

Posted: 30 Aug 2013 03:12 PM PDT

Hi,*IS partitioning ever simple?I have a web report that I want to retrieve data as quickly as possible. The report is driven by a view of current year transactions - in December it will be about 20 million rows. Instead of taking 5 seconds to get 10k rows, I want it to take 1 second. Instead of 1 second for 200 rows, I want it to be instant. Sorry for the book I've written below. I tried to provide all the information I thought might help understand the situation.The report view has three tables. The main table has an account (~700 of these), cost center (~7,000 of these), several descriptive text fields and a few numeric columns (dollars, quantities). The second and third tables are flat file hierarchies for the cost centers and accounts respectively.On the first table I have a clustered index on cost center, account. On the second and third tables I have primary keys on cost center and account respectively.The account and cost center tables change every month or two and when they do, they are completely overwritten. The main table with texts, quantities and amounts is written from scratch once per day and swapped out instantly (the view points at a synonym). The web report ALWAYS filters on the account sub-category (about 70 of these) and returns all of the descriptive text fields and numbers. It almost always also filters on the cost center or one of the 8 levels of cost center hierarchy.Having heard the word "partition" but knowing nothing about it, I was thinking that my report might likely be faster if I either:a) write the sub-account into my main table and create a clustered index on sub-account, cost center (instead of clustered cost center, account and joining on the account table to get sub-account).orb) write the sub-account into my main table and partition the table on sub-account.orc)change the web report query so that it looks at not only account sub-category (~70 of these) but also account category (8 of these); write out 8 separate tables, one for each account category.Before looking into anything at all, I figured that b) would be the most straightforward as I probably just needed to "add a partition" after each time I drop/recreate my main table and before populating it, the same way I currently add an index. I figured it would be as simple as 'create partition over [account_sub_level] on myTableA and SQL Server would figure it out... But after doing an hour or two of reading up online, it sounds like it is WAY more complicated than I thought and it may require permissions that I don't necessarily have. I might end up trying to figure it out for a couple of days and realizing I can't do it. Maybe given the extent of what I am trying to accomplish I would be better of trying a) and if that doesn't work try c)...What do you think? Thanks for any advice!Tai

SQLSERVER 2008 : Rows into Comma seperated values

Posted: 30 Aug 2013 11:59 AM PDT

[code="sql"]declare @year int = 2013, @week int = 27declare @dte dateselect @dte = dateadd(week, @week - 1, dateadd(year, @year - 1900, 0))SELECT *FROM( SELECT [DATE] = DATEADD(DAY, n, dateadd(day, (datediff(day, '17530107', @dte) / 7) * 7, '17530107')) FROM ( VALUES (0), (1), (2), (3), (4), (5), (6) ) num (n)) dWHERE [DATE] >= dateadd(year, @year - 1900, 0)AND [DATE] <= dateadd(year, @year - 1900 + 1, -1)[/code]my output needs to be as follows[2013-06-30],[2013-07-01],[2013-07-02],[2013-07-03],[2013-07-04],[2013-07-05],[2013-07-06]note : i need the symbol [ ] as well.can any one help me in this

Test and document recovery scenarios

Posted: 30 Aug 2013 11:01 AM PDT

I have to test and document possible recovery scenarios in SQL Server 2008 R2.I can think of the following scenarios. Can you please suggests me more scenarios?1. restore a database that was accidentally deleted2. recover the database to point in time3. recover the database if mdf file is deleted4. recover the database if ldf file is deleted5. restore a table that was accidentally deleted from a databaseThanks

List objects to move

Posted: 30 Aug 2013 06:46 AM PDT

In one database(Order), all my objects (in this db, some objects are not my app) for a app have to move to another server.SQL server administrator want me to list all objects(tables, views, functions and store procedures).Is there a easy to select objects and generate a script for administrator?

Reporting Services scheduling- hourly between 8 and 5 impossible?

Posted: 04 Jun 2012 05:14 AM PDT

Trying to get a report to run daily between certain hours. I can set a start time and an "end date" in Report Manager, but as far as I can tell, I can't say run hourly every day from X to Y. am I missing something? If this isn't doable from RS, can I just find the associated job in the Agent and change the schedule of that job?

how to delete a log file?

Posted: 29 Aug 2013 09:48 PM PDT

Hi All, Have a production database with two log files. The second ldf file is pointing to a wrong drive and we dont want to have a second ldf file. Hence need to delete the second log file.Can any one please help me to achieve this?

SQL only sampled 1 row for 47,000,000 table for a user created statistic

Posted: 29 Aug 2013 10:42 PM PDT

I have an unusual problem which cause as major performance issues with our Application. A user created statistic on our main table in our database hit the 20% modification threshold so SQL refreshed the statistics. It only sampled 1 row.[b]Before:[/b]StatisticName StatisticType NoRecompute LastUpdated Rows RowsSampled UnfilteredRows RowModifications ModificationPercentage Steps_dta_stat_244664415_1_2_15_7_3_8 User Created 0 2013-03-17 16:31:47 39176591 34 39176591 7834425 20 28[b]After:[/b]StatisticName StatisticType NoRecompute LastUpdated Rows RowsSampled UnfilteredRows RowModifications ModificationPercentage Steps_dta_stat_244664415_1_2_15_7_3_8 User Created 0 2013-08-29 17:02:25 46999958 1 46999958 0 0 1Once the issue was identified, an UPDATE STATISTIC WITH FULLSCAN fixed the problem.Does anyone know a valid reason why SQL would have only sampled 1 row ? Personally I think this is a bug in the SQL Engine.

sql 2008 r2 ole db driver

Posted: 29 Aug 2013 11:54 PM PDT

i installed native client for sql 2008 r2 and i have universe which are depend on ole db driver but do not know how to install and where to get it.. anyone can recommend any link..ReagrdsShaun
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog