Hi,
Need to write a query which would select data based on weekly wise based on the column Date.And week starts from Monday.
Hence would reqiure a function or a query which would select only those records which would fall under respective Weeks.
Help!!!
Regards,
Navneet
I have a query that will generate records monthly based on the number of months that i calculate between two date feilds for a given requestid. How can i use the same query to generate records for weekly and bi weekly based on the receiveddate field that i use in the subtraction for calculating the number of months.
Also when inserting i have been adding a month for every record as i was generating monthly and now i would have to add week and 2 weeks to the receiveddate
SET NOCOUNT ON
GO
declare @num_of_times int
declare @count int
declare @frequency varchar(10)
declare @num_of_times1 int
[Code] ....
My Table(collection) Structure consist of below:
TypeDate ItemAmount
REVENUE2007-04-01serv002***
REVENUE2007-04-01Serv004***
REVENUE2007-04-01serv005***
REVENUE2007-04-01AMC-***
ROYALTY2007-04-01serv002***
ROYALTY2007-04-01Serv004***
ROYALTY2007-04-01serv005***
ROYALTY2007-04-01AMC***
O/p should come as:
====================
Revenue:
MONTH AMC serv002 SER004 serv005
2007-04-01 *** **** *** ****
Royalty:
MONTH AMC serv002 SER004 serv005
2007-04-01 *** **** *** ****
Hi All
Can any one solve my problem?
Requirements:
We have Our Local DataBase(SQL Server) Guess eg. DBLocal
We have a requirements to update this DBLocal Table Data with Other Database DBRemote(SQL Server) table data.
This Task is Schedule in a Week. Means We needs to update this DBLocal Table Every Week from DBRemote Data.
Both Side we have only one table. Means Source is One Table and Destination is Table One.
Right Now I have a connection string for DBRemote(SQL Server) and SQL Statement for getting Data from DBRemote(SQL Server).
can any one tell me what I need to do for achive this requirement?
please provide me link also; from there i can get enough information for my requirements.
Thanks in Advance
Regards
Hi,
I have a requirement where I need to display the items category wise
I have 3 tables,
one stores category_id, parent_id and category_name
second table stored item_id,item_name and proce
3rd table stores category_id and item_id
I need to display the values like this
Category name, Item name
with first parent category ans ites items should be displayed and next sub category and its item
By category table with contain 2 levels of sub category
i.e Category1
Category11
Category111
any help with the query would be much appreciated, I wrote the query something like this....
select c.category_name,parent_id,item_name from category c,items i,Category_item ci where
ci.item_id=i.item_id and c.category_id=ci.category_id order by parent_id, category_nameBut it will display all parent category items then the sub category items I want to display like thiscategory1 items1category11 item11category1 item12category11 item111category11 item112 category2 item2
Thanks
I have query on grouping data on weekly basis..
1. Week should start from Monday to Sunday
2. It should not consider current week data(suppose user clicks on report on Tuesday, it should display the data for last week).
3. I want output like below
Week1,week2,week3..... week12,AverageofWeek
12 , 10 ,0.........0 12
I have get 3 columns in a select query. how can show these 3 columns in row wise.
View 2 Replies View RelatedI have the data in the below format.
Month(YYYYMM) | Department | TotalCount | LeftCount
201401 xxxxxx 30 0
201402 xxxxxx 28 2
201406 xxxxxx 27 1
In the above data, no record exist for 201403,201404,201405, query I wrote will give only the data for which there LeftCount exists, but I am looking for a query which get the data in the below format.
Month(YYYYMM) | Department | TotalCount | LeftCount
201401 xxxxxx 30 0
201402 xxxxxx 28 2
201403 xxxxxx 28 0
201404 xxxxxx 28 0
201405 xxxxxx 28 0
201406 xxxxxx 27 1
i want to show data Party Name and Time interval wise. here is my table from where i will fetch data. so pasting table data here.
Call start Call duration Ring duration Direction Is_Internal Continuation Party1Name Park_Time
------------------------- ---------------- ------------- --------- ----------- ------------ --------------- -----------
2015/06/08 08:06:08 00:02:28 2 I 0 0 Emily 0
2015/06/08 08:16:38 00:00:21 0 I 0 1 Line 2.0 0
2015/06/08 08:16:38 00:04:13 5 I 0 0 Jen 0
[code]...
now i am not being able to cross join this CTE with my table to get data party name wise and time interval wise. say for if no data exist for a specific time interval then it will show 0 but each party name should repeat for time interval 9:00:00 - 9:30:00 upto 17:30:00. i like to add what filter need to apply to get data for incoming, outgoing, call transfer and miss call.
For Incoming data calculation
where direction='I' and
Is_Internal=0 and continuation=0 and
RIGHT(convert(varchar,[call duration]),8)<> '00:00:00'
For outgoing data calculation
[code]...
I am using Sql Server 2012.
This is how I calculate the ratio of failures in an order:
31 Days Table 1 query
sum(CASE
WHEN (datediff(dd,serDATE,'2015-01-21')) >= 31 THEN 31
WHEN (datediff(dd,serDATE,'2015-01-21')) < 0 THEN 0
ELSE (datediff(dd,serDATE,'2015-01-21'))END) as 31days1 .
How do i loop and pass dates dynamically in the Datediff?
31 Failures Table 2 query
SUM(Case when sometable.FAILUREDATE BETWEEN dateadd(DAY,-31,CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102))
AND CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102)Then 1 Else 0 END) As Failures31,31 Day Cal(Formula) combining both Table 1 and Table 2
((365*(Convert(decimal (8,1),T2.Failures31)/T1.31day))) [31dayCal]This works fine when done for a specific order.
I want a similar kind of calculation done for day wise and month wise.
2. what approach should I be using to achieve day wise and month wise calculation?
I do also have a table called Calender with the list of dates that i can use.
I am using Bulk Copy command for Exporting data table wise from database to csv files and it was working fine. Since last 3-4 days when exporting for some tables data in csv file is coming junk.
View 1 Replies View Relatedhai iam new to ssrs, please help me.
i have student billbale information assume what ever data it. i need to to dispaly total amount for the student at
Bottom Of Report By Daily, Weekly, Monthly, SYTD . take any example, i want to know formula.
thanks to advanced
Jacks v
Hi!
I have an original database that I want to copy once to another database. Then I want to update the data weekly with the data of the original database. I don€™t change any tables or columns in that part of the database. Just some tables more in the €˜new€™ database (than the tables from the original database) with some references to the tables that have to be updated weekly.
How can I do this? (if you know what I mean... it's a sort of a datawarehouse where different sources come together.. that part that represents the original database is just a part of the datawarehouse. That part is exactly the same structure as the original database.)
Thanks,
Sandra
We do a 4:00AM weekly reboot of every SQL 7.0 server on NT4 scheduled as a SQL Agent job using the os command "shutdown servername /l /c /y /r". The problem is some of the SQL servers recognize the NT shutdown command and gracefully exit out of SQL first with the SQL log entry "SQL Server terminating because of system shutdown", then closes all databases files. Most of the other SQL server do not list any SQL log messages and sometimes list startup error messages indicating a dirty shutdown.
Does anyone else reboot their SQL servers with a scheduled job? If so, how do you perform this task cleanly?
Thanks,
D
Been trying to send this all week...
-----Original Message-----
From: Driggers, John
To: 'SQL Discussions'
Sent: 10/27/99 9:10 AM
Subject: FW: Weekly server hang
I also see the one below prior to another crash....going through tech
net now...but not seeing anything that reflects the messages below. The
results from searching on "Exception_Access_Violation" I'm not sure
apply in my case...also looks like I have at least two causes of crashes
(how can one interpret the below statements???)
Thanks, John
--------------------
99/10/24 10:38:00.06 spid10 EXCEPTION_ACCESS_VIOLATION raised,
attempting to create symptom dump
99/10/24 10:38:00.06 spid10 Initializing symptom dump and stack dump
facilities
99/10/24 10:38:02.61 spid10 ***BEGIN STACK TRACE***
99/10/24 10:38:02.61 spid10 0x00404CD9 in SQLSERVR.EXE,
rm_ods_handler() + 0x0329
99/10/24 10:38:02.64 spid10 0x00405571 in SQLSERVR.EXE, st_do_enlist()
+ 0x00C1
99/10/24 10:38:02.64 spid10 0x004071CA in SQLSERVR.EXE,
CDTCState::init() + 0x033A
99/10/24 10:38:02.65 spid10 0x005A70A3 in SQLSERVR.EXE,
lddb_fixdbosuid() + 0x0423
99/10/24 10:38:02.68 spid10 0x005A6CC2 in SQLSERVR.EXE,
lddb_fixdbosuid() + 0x0042
99/10/24 10:38:02.68 spid10 0x005963CB in SQLSERVR.EXE, textalloc() +
0x04CB
99/10/24 10:38:02.71 spid10 0x00463F4B in SQLSERVR.EXE, agghaving() +
0x004B
99/10/24 10:38:02.71 spid10 0x00409829 in SQLSERVR.EXE, opencheck() +
0x0089
99/10/24 10:38:02.71 spid10 0x00427B09 in SQLSERVR.EXE,
tbswritecheck() + 0x0969
99/10/24 10:38:02.71 spid10 0x00250FED in opends60.dll
99/10/24 10:38:02.71 spid10 0x0025055B in opends60.dll
99/10/24 10:38:02.71 spid10 0x002414D1 in opends60.dll
99/10/24 10:38:02.71 spid10 0x00241384 in opends60.dll
99/10/24 10:38:02.71 spid10 0x10219D84 in MSVCRT40.dll
99/10/24 10:38:02.71 spid10 0x77F04F3E in KERNEL32.dll
99/10/24 10:38:02.71 spid10 ***END STACK TRACE***
************************************************** **********************
*************
Cindy, nothing in the NT logs but found this in the SQL logs:
99/10/25 09:25:15.45 spid71 EXCEPTION_ACCESS_VIOLATION raised,
attempting to create symptom dump
99/10/25 09:25:15.45 spid71 Initializing symptom dump and stack dump
facilities
99/10/25 09:25:20.45 spid71 ***BEGIN STACK TRACE***
99/10/25 09:25:20.46 spid71 0x00404CD9 in SQLSERVR.EXE,
rm_ods_handler() + 0x0329
99/10/25 09:25:20.52 spid71 0x005725C1 in SQLSERVR.EXE, stuff() +
0x0241
99/10/25 09:25:20.54 spid71 0x0056D35F in SQLSERVR.EXE, ncrid_update()
+ 0x057F
99/10/25 09:25:20.57 spid71 0x0051DD35 in SQLSERVR.EXE, prRESOURCE() +
0x0055
99/10/25 09:25:20.57 spid71 0x00464C65 in SQLSERVR.EXE, genbuiltin() +
0x0445
99/10/25 09:25:20.59 spid71 0x00427B09 in SQLSERVR.EXE,
tbswritecheck() + 0x0969
99/10/25 09:25:20.62 spid71 0x00250FED in opends60.dll
99/10/25 09:25:20.62 spid71 0x0025055B in opends60.dll
99/10/25 09:25:20.62 spid71 0x002414D1 in opends60.dll
99/10/25 09:25:20.62 spid71 0x00241384 in opends60.dll
99/10/25 09:25:20.62 spid71 0x10219D84 in MSVCRT40.dll
99/10/25 09:25:20.62 spid71 0x77F04F3E in KERNEL32.dll
99/10/25 09:25:20.62 spid71 ***END STACK TRACE***
This proceeds my 'crashes', which it looks scary enough to do the trick!
Any idea what could be causing this exception?
Thanks, John
ps. someone else mentioned backup software - we use BackupExec and I
have a sched. task that dumps one of the databases to a network drive 2x
day. But these are running throughout the week...looking over the logs I
really don't see a correlation...(ie. these same processes run on days
that no crash occurs and successfully later in the day that the crashes
do occur (some hours earlier).
-----Original Message-----
From: Gross, Cindy [mailto:CindyGross@hmhs.com]
Sent: Monday, October 25, 1999 2:14 PM
To: SQL 6.5 Discussions
Subject: RE: Weekly server hang
Did you check the SQL Server errorlog (sometimes things are written here
that don't go to the event viewer) and the NT event viewer (application
and
system)?
You could try turning on SQL Trace to see if you can capture a "bad"
query
but depending on how SQL goes down it may not be captured.
If you are auditing successful logons you could take a look to see if
there
is any pattern in who logs in just before SQL restarts.
Any chance someone is actually stopping it on purpose? Or maybe a
program
that is stopping it (maybe a backup system trying to backup the device
files
instead of the dumps)?
Cindy Gross
SQL Server MCP
Texas Health Resources
http://members.tripod.com/cindygross/sqlsrvr.htm
> -----Original Message-----
> From:Driggers, John [SMTP:John_Driggers@spspay.com]
> Sent:Monday, October 25, 1999 12:26 PM
> To: SQL 6.5 Discussions
> Subject: Weekly server hang
>
> List-Unsubscribe: <mailto:leave-mssql-13928C@ls.swynk.com>
> List-Software: Lyris Server version 3.0
> List-Subscribe: <mailto:subscribe-mssql@ls.swynk.com>
> List-Owner: <mailto:owner-mssql@ls.swynk.com>
> X-URL: <http://www.swynk.com/sysapps/sql.asp>
> X-List-Host: swynk.com discussion lists <http://www.swynk.com>
> Reply-To: "SQL 6.5 Discussions" <mssql@ls.swynk.com>
> X-Message-Id: <06F417B00B8CD1119BA400008322DF6D03515E78@spsgex01>
> Sender: bounce-mssql-14964@ls.swynk.com
> Precedence: bulk
>
> This one is bugging the tar out of me. Running SQL 6.5 sp5a, NT4 sp4
on
> the
> server. Either on the weekend or Mon. mornings (happened all 3 days
this
> past week) the SQL service stops on the server. This is during low
usage
> times. The box is a DELL 4300 dual 450 w 512 RAM (250 dedicated to
SQL).
> All
> other services on the server are ok, except for SQL.
>
> I'm thinking maybe a bad query hitting the server (I've seen this
happen
> before) but the programmers claim there is nothing special about these
> time
> periods that something "unique" would be happening. After I restart
the
> server it may not happen until the next week (this past weekend being
an
> exception). I thought maybe I had a memory leak but running perf.
monitor
> before a crash once revealed 99+% data cache, available proced. crash,
low
> CPU usage, low swapping....anything else I could check?
>
> Maybe reinstalling the sp5a? Any suggestions on things to try would be
> most
> appreciated...
>
> Thanks, John
>
>
> ------
> FAQ: http://www.swynk.com/faq/sql/sqlserverfaq.asp
> Please post SQL Server 7.0 questions to the SQL 7 list
> http://ls.swynk.com for list server signup/maint options
> You are subscribed as cindygross@hmhs.com
> Archives: http://www.swynk.com/sitesearch/search.asp
> To unsubscribe send a blank email to leave-mssql-13928C@ls.swynk.com
------
FAQ: http://www.swynk.com/faq/sql/sqlserverfaq.asp
Please post SQL Server 7.0 questions to the SQL 7 list
http://ls.swynk.com for list server signup/maint options
You are subscribed as John_Driggers@spspay.com
Archives: http://www.swynk.com/sitesearch/search.asp
To unsubscribe send a blank email to leave-mssql-13928C@ls.swynk.com
Hi.
I need some help. Does anyone know how to create a select statetement that will generate a list of records that have been timestamped (datetime) on the week of the system date (getdate())? Assuming that Monday is the start of the week and Sunday is the lastday of the week.
Thanks in advance
Hi. I want to be able to calculate weekly averages of our report data. Here is a sample from our EmplyeeTrends table:
EmployeeID ReportDate TotalCases
2 1/1/2007 77
2 1/2/2007 63
3 1/1/2007 56All the report data is produced daily. However, my boss wants to be able to see weekly/monthly averages for each employee. Is this possible? Thanks so much for your help!
Hi All,
I am Maran. Am facing the problem to retrieve the following format of output using the sql query. Is it possible 2 solve this.. I tried this, but i am unable to.
Input values:
Start Date: 2/17/2008
End Date : 5/8/2008
Output Format:
2/17/08 - 2/29/08 (Partial Month) 12
3/1/08 - 3/31/08 (Full month) 0
4/1/08 - 4/30/08 (Full month) 22
5/1/08 - 5/8/08 (Full month) 10
I want the above format of the monthly report. I really could use some help on this. thanks.
~ Maran
Manimaran.Ramaraj
Software Engineer
Aspire Systems
Chennai - 600 028
Hi All
I am using following code for my application
SELECT
convert(varchar(10),acr._eventTime,120) [Date],
sum(case r.Severity WHEN 'Warning' THEN 1 end)[Warning],
isnull(sum(case r.Severity WHEN 'OK' THEN 1 end),0)[OK],
FROM ActiveCheckIssueResult r
GROUP BY convert(varchar(10),acr._eventTime,120)
it gives me Result as,
Date warning OK
--------- --------- -----
1/1/1008 1 1
2/1/2008 0 2
i want count on weekly basis
Could any one help me?
What daily/weekly checks do you guys currently perform on your servers and databases?
I recently ran across with an article from SQLServerCentral that listed a couple of daily checks that I'm thinking about implementing on my environment, and some of them are:
DB Missing Recent Backup - Report
DB Missing Recent Log Backup - Report
Drives Low on Disk Space - Report
Error Log Messages Report - Report
Instance Recently Restarted - Report
Job Failures - Report
Large Databases Log File - Report
I already have in place:
Verify is SQL Agent Service is running
Check Disk Space Available
Since I'm going to spend some time on this, I was wondering if there's anything else that you guys have in place or any other 'nice to have' that you guys also might have, so I don't leave anything behind...
Thanks!
---
http://www.ssisdude.blogspot.com/
I have a table with columns: Date (SmallDateTime), Sales (Decimal)
Is there a SELECT way to compute weekly totals, similar to the GROUP BY clause?
Or is using a WHILE loop a practical way?
Thank you.
I'm not sure this is the place for this question, but not sure where else to go. I've written asp.net code to read from a sql server 2005 db and send out customized emails based on user info.Currently the process gets rolling by clicking a button in a web page.The client doesn't want to click a button, they want to run the email sender on a timer.How can I set up my function to run on a timer either in asp.net or more likely called from sql server?
View 6 Replies View RelatedHi:
in sp_add_jobschedule
@freq_type = 8 for weekly @freq_interval = 1 for Sunday.
I need to script to create a job which will run every Sunday 6 pm, to backup one prod database to a new backup device. EX. database name is DBTest, and the 7/20/2003 (Sunday) running result should be backup_DBTest_2003_07_20.bak.
and next Sunday's should be backup_DBTest_2003_07_27.bak.
(if this way, the backup device will be created at the begining of the job running...)
this way the backup would not be overwritten. Is there a way to dynamically generate 54 weeks job script from this coming Sunday?
Or to make the backup result as backup_DBTest_2003_week27.bak, backup_DBTest_2003_week28.bak....
thanks
David
hello friends!
I want to display the reports in weekly format suppose
today is sept 27 2006, so i know from datepart(weekday,..) its value is 4 and end of this week is sept 30 2006 and again next week will start like that....also search should be monthly...
my report looks like
Weekly Report (09/27 - 10/12)
Week====09/27-09/30======10/01-10/07======10/08-10/12
Sales======50===============100===============80
like that my output looks like
T.I.A
Dear Forum Community,
I am new to this forum (actually to forums in general). I apologize for the long post, but I feel that someone must have done all this before and perhaps there are better approaches, so I felt I had to explain my objectives.
I have written a Daily(date) report for all the pertinent data in our production database. We want this data to persist for two years and be easily accessible. We also want other reports to use this data as a datasource rather than the production data because the production database is periodically purged of old data that may be of interest to these reports. So I am using SSRS not only as a reporting tool but also a sort of historical database.
To persist the daily report I added a Yesterday report that includes the Daily(date) as a subreport. The Yesterday report uses yesterday's date and has no paramters so it can be set up to run as a snapshot and be stored in the history. Daily(date) is set to use the cache which expires after 7 reports. So far so good.
I have now created a Weekly(weeknumber) report that includes 7 Daily(date) subreports and a LastWeek report that is schedule to run weekly as a snapshot and is stored in the history. I am hoping that because the Daily(date) reports have already been run by the Yesterday report, their datasets will still be in the cache and this will not cause a refresh from the production database. Am I right?
Basically, I want to keep access to the production database to a minimum, not store (much) duplicate data in the Report Server database and yet still have quick and easy access to the data going back 2 years even though it has long since been purged from the production database.
I considered to use the 7 daily reports as a datasources for the weekly report so that I could aggregate the data each week and expire the daily reports (and again for months, quarters and years). However, I was unable to figure out how to use a report as a datasource.
My questions come down to these...
Will the approach I am pursuing work?
Is it the best approach?
If a report includes subreports that have already been run individually with the same parameters, will the cached subreport be used.
How do you use an existing reports as datacsources for an aggregated report?
I look forward to hearing your suggestions.
Dave
Hello all,
I hope someone can help me with a big problem... I'm using Citrix Resource Management Services with a SQL 2000 database. Their are 15 citrix servers which are all reporting to the SQL database.
The database is expanding very quickly and is becoming slower and slower.
My question is: I want to schedule a purge of old records on a friday afternoon, like this:
WEEK 1 - MON / FRI
WEEK 2 - MON / FRI (Friday's purge records week1)
WEEK 3 - MON / FRI (Friday's purge records week2)
etc...
Is this possible? if yes how do i do this !??!
Thank you very much for any info!!
Daan
Hi,
I need help in looking at sample script to perform automatic creation for weekly partitioned tables and also update the partitioned view accordingly.
Any helps are very much appreciated!
Thanks,
G
I have a weekly Maintenance Plan Reindex job that has failed because of a deadlock. My question seems simple enough and I'm ashamed to say I ought to know this answer, but here goes: Does the rest of a given job continue after such failures (this one was maybe 3/4 through the log) occur?
View 6 Replies View Related
Hi Guys,
I am generating Transaction Activity report,which should get data by weekly.Report shold look like this.
W1
W2
W3
W4
W5
W6
OKC
79
38
50
76
35
47
NFL
0
0
45
43
33
28
LA
5
12
10
0
0
10
Total
79
38
95
119
68
75
Iam passing 3 parameters @startdate,@enddate,@Market. when i select one Market(OKC/NFL/LA), report generating properly, but when i passing 3 markets values(OKC,NFL,LA), iam getting wrong report,report format is not correct.I used Cross tab for generating this report.Result lam getting like this.
W37
W38
W39
W40
W41
W42
W43
W44
W45
OKC
80
OKC
38
OKC
95
OKC
119
OKC
68
OKC
75
OKC
74
OKC
70
OKC
59
OKC
OKC
LA
And i wrote query like this,
select m.Market_name as Market,'W'+datename(ww,ut.creation_date) as Week,count(ut.transaction_id) as Count
from POS.DSC_TRANSACTION_STATUS_VL ts inner join POS.DSC_USER_TRANSACTION ut
on ts.transaction_status=ut.transaction_status inner join POS.RETAIL_LOCATION rl
on ut.rl_number=rl.rl_number inner join POS.BILLING_MARKETS bm
on rl.bm_code=bm.bm_code inner join dbo.Market m
on bm.market_id=m.market_id
where (ut.creation_date between @startdate and @enddate) and m.market_name IN(@Market)
group by m.Market_name,ut.creation_date
order by m.Market_name desc
Could you please some one help me to get this correctly.
Thanks in advance
Thanks
San
I am currently creating calculations for weekly and fortnightly ranges by day.
Is it better to use LastPeriods or Tail? For example, the last 14 days could be:
LastPeriods(14,Â
StrToMember
(
"[Date].[Year Week Day].[Date].&[" + FORMAT(Now(),"yyyy-MM-ddT00:00:00")
+"]"
) Â Â
))
Tail(
Filter(
{[Date].[Year Week Day].[Day]},Â
([Date].[Year Week Day].CURRENTMEMBER, [Measures].[Test Measure]) > 0
), 14
)
What is the advantages or disadvantages?
Guys,
I've been asked to properly schedule some jobs on our system for a database following a full recovery model. I realized that the order for some of the jobs might matter and came up with the solution, which is described below.
Please let me know if this ordering makes sense and/or if you have any general comments/suggestions:
1. Backup all dbs [in case defragmentation messes something up]
2. Defragmentation [this will grow the transaction log]
3. Backup transaction log [backs up trans log and truncates inactive portion of it]
4. Shrink log file
Also, I've been previously suggested not to shrink the log file, unless absolutely necessary, as it is resource intensive. In my experience, however, this operation does not seem to be resource intensive at all. It took me 1 second to shrink the transaction log file from 25GB to 2GB.
Is it regrowing of the log file that will affect performance?
Also, if I do shrink the transaction log file, is there a suggested value to shrink it to - possibly a function of the database file size?
Thanks a lot
I have a table having 3 coulmns.
amnt1 amnt2 amnt3
100 200 230
200 170 120
290 188 299
800 170 120
i need to get max amount row wise like
amnt
230
200
299
800
Hi,
I have the following three tables :
Account (Id int, AccountName nvarchar(25))
Role (id int, Rights int)
AccountRole (AccountID, RoleID)
In Role table - Rights Column is a bit map where in each bit would refer to access to a method.
One account can be associated with multiple roles - AccountRole table is used for representing the N:N relation.
I want to develop a store procedure - which would return all AccountName and their Consolidated Rights.
Basically I want to do a BitWise OR operation for all the Rights in the Aggregation instead of the SUM as shown in the following statement.
SELECT Account.Name, SUM(Role.Rights) FROM Account WITH (NOLOCK)
JOIN RoleAccount ON RoleAccount.AccountID = Account.Id
JOIN Role ON RoleAccount.RoleId = Role.Id
GROUP BY Account.Name
Thanks,
Loonysan