T-SQL (SS2K8) :: Get Rows Based On Current Quarter
Sep 3, 2014
I am working on a report and the data source is Teradata. now I have situation where I want to get order id details based on the current quarter and year I am posting this same data. For TD related queries I do not where to post.
ACCT_ID ACCT_NMORD_NBRORD_DT ORD_AMT_USD
595709114ASDASD444447/28/2014 546
2224809440ASDASD444445/2/2012 546
1724031572ASDASD444446/22/2011 546
1702887651ASDASD444447/3/2014 546
1724020508ASDASD444447/16/2012 546
1148151895ASDASD444449/18/2013 546
2125154824ASDASD444449/2/2014 546
1503552723ASDASD4444412/20/2011 546
2224689808ASDASD4444410/4/2010 546
931387698ASDASD4444412/31/2010 546
View 4 Replies
ADVERTISEMENT
Feb 12, 2006
Hi there everbody,
i am trying to get the last day of the Q + 1 month
the first part i have succeeded (dateadd(ms,-3,DATEADD(qq, DATEDIFF(qq,0,getdate() )+1, 0))) ,but how do i add 1 more month ?
would appreciate any suggestion
regards
Yoav
View 2 Replies
View Related
Jun 22, 2014
Currently I have the below style data:
Name, StartPostion, EndPosition, Height
Person1, 10, 15, 5
Person2, 14,14,0
Person3, 20,21,1
What I am looking to do is, run through my table of data and create a record for each Name for each Position it takes up. For example
For Person1 the data will look like,
Name, StartPosition, EndPosition, Heigh, Position
Person1, 10,15,5, 10
Person1, 10,15,5, 11
Person1, 10,15,5, 12
Person1, 10,15,5, 13
Person1, 10,15,5, 14
Person1, 10,15,5, 15
View 8 Replies
View Related
Oct 2, 2014
I am trying to always get the start of the week of the current quarter in my criteria
This is the statement for the current quarter
Dateadd(qq, Datediff(qq,0,GetDate()), 0)
This is the statement for the current week
DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)
How to calculate from the start of the week of the current quarter...
View 1 Replies
View Related
Mar 6, 2014
I have two columns which needs to repeat based on ID and number of distinct rows in that ID.
ID Date Created
1 1/1/2012 Sudheer
1 1/2/2013 Sudheer
1 3/3/2013 Sudheer
2 1/2/2014 Veera
2 2/5/2015 Veera
Results
ID Date Created Date Created Date Created
1 1/1/2012 Sudh 1/2/2013 Sudh 3/3/2013 Sudh
2 1/2/2014 Veera 2/5/2015 Veera
View 3 Replies
View Related
Apr 18, 2014
--------------------------------------------------
SalesOrder-ItemName-Price-Category
-------------------------------------------------
01-Camera-100-Electronic
01-Memory-4GB-10-Memory
01-Battery-5-Battery
02-Keyboad-10-Accessories
02-Mouse-5-Accessories
03-CPU-300-Hardware
03-Motherboad-400-Hardware
From above rows i would like to select rows based on one distinct column SalesOrder, i want output like below.
-----------------------------------
SalesOrder-ItemName-Price-Category
-----------------------------------
01-Camera-100-Electronic
02-Keyboad-10-Accessories
03-CPU-300-Hardware
CREATE TABLE Table1 (SalesOrder varchar(10), ItemName VARCHAR(100), Price INT, Category VARCHAR(100))
[Code] ......
View 2 Replies
View Related
Aug 22, 2014
How do I get first day of last month of previous quarter from today's date? I know my question is little confusing. I need to get 06/01/2014 using t-sql.
View 2 Replies
View Related
Jun 3, 2015
I have a simple following table which is having only one date column.
CREATE TABLE TEST_DATE
(
InputDate DATE
)
GO
INSERT INTO TEST_DATE VALUES('01-01-2015')
INSERT INTO TEST_DATE VALUES('06-25-2015')
INSERT INTO TEST_DATE VALUES('11-23-2014')
GO
SELECT * FROM TEST_DATE;
And the expected out put would be as follows:
I want to derive a Four Quarter End Date based on Date selected.
For Example if i select 01-01-2015 then
First Quarter End Date would be Previous Quarter End Date
Second Quarter End Date would be Current Quarter End Date
Third Quarter End Date would be Next Quarter End Date
Fourth Quarter End Date would be Next +1 Quarter End Date Like that
View 9 Replies
View Related
Mar 20, 2015
We had a requirement that need to sum the data based on quater we will be having 12 months data in the system for an year suppose we have 12 records for 2014 year. jan month sales data should be same when we were in feb month it should sum jan+feb sales and should show in sales column whereas we were in march month it should sum jan+feb+mar sales, then same for next quater also apr month it wil be same value in may it should be apr+may in may sales value etc ....
We will be having date column values as 201401,201402,.....
How can we implement in sql sever performance should be good.
View 1 Replies
View Related
Jun 5, 2014
I have a process to rollover prior quarter data to new quarter in a table.
For example, i have a table with (col1, col2, year, qtr) with data like ( Note: col1 is identity(1,1) )
1,'today',2014,1
2,'tomorrow,2014,1
3,'friday',2014,1
Now when i run my process, above 3 records will be rolled over new quarter 2014 Q2 and the table will be like
1,'today',2014,1
2,'tomorrow,2014,1
3,'friday',2014,1
4,'today',2014,2
5,'tomorrow,2014,2
6,'friday',2014,2
Row 1 with identity 1 has rolled over to new quarter row 4 with identity 4 ( qtr fields are changed )
Row 2 with identity 2 has rolled over to new quarter row 5 with identity 5. Same with last row as well.
Here, i have another table called "ident_map" with columns like (old identity, new identity ) and during rollover i am supposed to load ident_map table with old and new identity. So after rollover is complete, ident_map table should look like
1,4
2,5
3,6
I know using output clause I can capture the new identity values. 4,5,6 in this case. But is there any way to capture both old identity and new identity during rollover so that i can load the ident_map table with old and new identity.
View 9 Replies
View Related
Jun 8, 2008
I've this query
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)
Let's say, current date is 8 AUG 2005 and current time is 2045
So, i will get
ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
206 | 8/1/2005| 1950 | 88
...
...
207 | 8/7/2005| 1845 | 77
208 | 8/7/2005| 2255 | 77
209 | 8/7/2005| 2140 | 77
Can someone can show me to filter data between
t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND TIME>=CurrentTime
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101) AND TIME<=CurrentTime
If current date is 8 AUG 2005 and current time is 2045, so the result shown as follow
ID | Date (this is datetime) | Time (this is integer) | Value
--------------------------------------------------
204 | 8/1/2005| 2359 | 90
205 | 8/1/2005| 2250 | 99
...
...
207 | 8/7/2005| 1845 | 77
I only have this query,
SELECT
t1.ID, t1.Date, t1.Time,
t1.VALUE
FROM RESULT WHERE t1.Date>=CONVERT(VARCHAR(10),DATEADD(d,-7,GETDATE()),101) AND
t1.Date<CONVERT(VARCHAR(10), GETDATE(), 101)
lack of idea to put the TIME condition.
Plz help me..
View 14 Replies
View Related
Mar 6, 2014
I have a snapshot table of about 15 million records in the form of:
InvoiceIDLineItemIDSnapshotDateAmount
1 1 20140101 12
1 2 20140102 14
1 3 20140103 17
2 1 20140101 10
2 2 20140102 5
1 2 20140105 15
1 3 20140105 20
I want to create an additional column called Current as shown below:
InvoiceIDLineItemIDSnapshotDateAmount Current
1 1 20140101 12 1
1 2 20140102 14 0
1 3 20140103 17 0
2 1 20140101 10 1
2 2 20140102 5 1
1 2 20140105 15 1
1 3 20140105 20 1
How can we write a query to achieve this while keeping in mind:
- We do not want to do unnecessary record lookups and Updates
- We only update records that corresponds to new entries. For example, we should not touch the record for InvoiceID = 2 in the above example
View 6 Replies
View Related
Jul 2, 2014
I am novice to intermediate writer of T-SQL. Here is my current Query:
SELECT [FISCALYEAR],
[ACCTPERIOD],
SUM([ACTIVITYDEBIT]) AS TrialBalanceDebit,
[POSTINGTYPE]
FROM [dbo].[TB_Lookup]
WHERE [POSTINGTYPE]='Profit & Loss'
GROUP BY [FISCALYEAR],[ACCTPERIOD], [POSTINGTYPE]
ORDER BY acctperiod ASCand this is what is produces.
FISCALYEARACCTPERIODTrialBalanceDebitPOSTINGTYPE
2014 201401 282361372.13000 Profit & Loss
2014 201402 227246272.86000 Profit & Loss
2014 201403 315489534.33000 Profit & Loss
2014 201404 287423793.76150 Profit & Loss
2014 201405 256521290.76000 Profit & Loss
2014 201406 65582951.30000 Profit & Loss
Now I need a way to add another field that takes the TrialBalanceDebit from current ACCTPERIOD and adds it to the Previous ACCTPERIOD TrialBalanceDebit.
View 9 Replies
View Related
Jan 14, 2015
I have a query that will go into an ssis package (eventually). The package will run every night at 3am. I need to capture the last 24 hours of by using something like:
SELECT worktype, changedate, woclass
where siteid = 'GTM' and woclass = 'WORKORDER' and istask = 0
[highlight=#ffff11]and changedate between '2015-01-13 03:00:00' and '2015-01-14 03:00:00'[/highlight]
I know I am not doing the between correctly to get the changedate between the last 24 hours. Is there a way to correct this so that I am only getting the change date that is between 3am today and 3am yesterday on any given day I happen to run this?
View 7 Replies
View Related
Feb 25, 2015
I've tried all sorts of code i.e. cross apply, running totals, etc. Cannot get this to work. I am trying to add a previous row value but only doing it for each group.
Source records
DECLARE @tbl table (Item int, Sequence int, StartTime datetime, Duration int)
INSERT INTO @tbl (Item,Sequence,StartTime, Duration) VALUES (1,1,'2/25/2015 12:00 am',10),(1,2,null,20),(1,3, null,22),(2,1,'2/25/2015 1:00 am',15),(2,2,null,30),(2,3, null,45),(2,4, null,5)
select * from @tbl
ItemSequenceStartTimeDuration
1102/25/15 0:0010
12null 20
13null 22
2102/25/15 1:0015
22null 30
23null 45
2 4 null 5
I would like to set the start time of the next row to be equal to the previous row time + duration. I know the start time of each group of 'Items' when the 'Sequence' number = 1. The last 'duration' value in the group would be ignored.
My expected output would be:
ItemSequenceStartTimeDuration
1102/25/15 0:0010
1202/25/15 0:1020
1302/25/15 0:3022
2102/25/15 1:0015
2202/25/15 1:1530
2302/25/15 1:4545
2402/25/15 2:305
View 7 Replies
View Related
May 6, 2014
I am working on some payroll related code which currently has the following hard-coded CASE statement (I won't include the entire thing, it is lengthy):
AND b.TCDateTime BETWEEN '2013-01-01 0:00' and '2013-12-31 23:59'
I want to get rid of the hard coding, and have this use current year dates. So for 2014, it should reference rows where the TCDateTime is >='2014-01-01 0:00' AND <'2015-01-01 0:00'..I think the following would accomplish this, but would like confirmation that this is the 'best' way (and that it will work!)
SELECT CONVERT(DATETIME, CONVERT(CHAR(4), DATEPART(yyyy, GETDATE())) + '0101') AS curryrbegin
--output should be yyyy-01-01 00:00:00.0 where yyyy is current year
SELECT CONVERT(DATETIME, CONVERT(CHAR(4), DATEPART(yyyy + 1, GETDATE()))
+ '0101') AS nextyrbegin
--output s/b nextyear-01-01-00:00:00.0
Then, change the CASE statement to read:
AND (b.TCDateTime >= curryrbegin AND < nextyrbegin)
View 8 Replies
View Related
Jan 21, 2015
I have to download the files from SFTP server, for which i am using WINSCP and i am able to successfully automate the process to download the files. But it is downloading all the files instead of only current day's files. Ihave searched for WINSCP documentation for time query parameter to pass to the get command. But its not working.
My source file name contains Datefield as "Filenameexample_150120_N001.txt".
Here 150120 mean Jan 20 2015.
Winscp has no functionality to query the files to parse using datefield. how to look for files which are from today's date.
Currently i am downloading files which contain *.* (meaning all files).
View 6 Replies
View Related
Aug 5, 2014
I managed to transpose rows into columns.
;WITH
ctePreAgg AS
(
select top 500 act_reference "ActivityRef",
row_number() over (partition by act_reference order by act_reference) as rowno,
t3.s_initials "Initials"
from mytablestuff
order by act_reference
[code]...
But what I would love to do next is take each of the above rows - and return the initials either in one column with all the nulls and duplicate values removed, separated by a comma ..
ref, initials
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
OR the above but using variable number of columns based on the maximum number of different initials for each row.this is not strictly required, but maybe neater for further work on the view
ref, init1,init2
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
View 6 Replies
View Related
May 13, 2015
I have data similar to the below
CREATE TABLE #TEMP
(
TYPE VARCHAR(10),
SEQ INT,
SUB_TYPE VARCHAR(10))
[Code] ....
Now for each type the seq is very important. Effectively by order of seq the subtype stays the same until another subtype changes it. So for TYPE1 100,110 and 150 are A. 170, 200,220 are B. 230 and 250 are C and so on.
However as you can see the data isnt actually stored in the row. I need a select statement that shows this data.
I have done this:
SELECT t1.*,t3.SUB_TYPE FROM #TEMP t1
CROSS APPLY
(SELECT MAX(SEQ) SEQ FROM #TEMP AS t2 WHERE t1.SEQ >= t2.seq AND t2.SUB_TYPE <>'' AND t1.TYPE = t2.TYPE
GROUP BY t2.TYPE) t2
INNER JOIN
#TEMP t3
ON t3.TYPE = t1.TYPE AND t2.SEQ = t3.SEQ
And it seems to work. Is this the easiest way to do it or am i missing something?
View 3 Replies
View Related
Oct 23, 2007
Hi All,
His there any way to build a connection string to sql server based on the current user credential?
I mean, intead of using
user id=Adminpassword=adminPass
Is it possible to do something like
user id= Context.User.IDpass=???
I'm asking cause, iI don't want to use impersonisation in my code.So that I can be sure logged user only sees what they can
(I'm a newbie, so this whole thing may makes no point(thanks for clarifying
View 4 Replies
View Related
Sep 28, 2006
Hi all--I am writing a DTS package looking to back up all databases in sequence. I am calling CDOSYS in a stored procedure from support.microsoft.com to send email upon failure. I have two types of tasks I am writing into the DTS package:
1. backup database <database> to disk = '<drive>:<path>.bak' with init
2. Upon failure, send the following message:
declare @srvname varchar(128),
@db_name varchar(128),
@msg nvarchar(4000)
select @srvname=srvname from master.dbo.sysservers
print @srvname
select @db_name=name from master.dbo.sysdatabases
print @db_name
set @msg='Hi--check your database backups. The ' + @db_name + ' database backup on ' + @srvname + ' has failed.
Thanks--SQL Server';
Upon success, go to the next database to back up, execute through these steps for the next database. Here's my problem: The @db_name variable captures the proper value for the current SQL Server, but I want to turn both @srvname and @db_name into global variables. I would like to pass in @db_name as the current database being backed up (and failing), and @srvname as the current SQL Server instance being backed up.
The above statement "select @db_name=name from master.dbo.sysdatabases" definitely does not capture the current database name value. Any suggestions as to how to pass this in, if not as a global variable?
Thanks in advance,
Jonathan
View 2 Replies
View Related
May 8, 2008
I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.
VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).
The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.
I also would like to add two derived variables.
1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.
2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.
My report should look like:
VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState
1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE
1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE
Can someone please help me here?
Thanks,
Romakanta
View 1 Replies
View Related
Jun 3, 2014
I am using MSSQL Server 2008R2 and I am interested in returning rows from a 'financial' table that fall within the current year (each row contains a 'Entered Date'). I am located in Australia so my financial year consists of all entries between the date 01/07/xx to the 30/06/yy.
Perhaps using the datediff() function, or other functions as required to achieve what I need?
View 3 Replies
View Related
Dec 1, 2014
I need to list the current DB Backup Set up list in PIVOT STYLE.
I need following way:
Database_NameFULL - DDIFF - ILOG - L
DB1DL
DB2D
modelDL
DB3DIL
msdbD
View 2 Replies
View Related
Feb 5, 2008
I am trying to match records that are >= the current date. I have tried using:
SELECT DISTINCT name
FROM table
WHERE datefield >= DATEPART(month, GETDATE()) AND datefield >= DATEPART(day, GETDATE()) AND datefield >= DATEPART(year, GETDATE())
ORDER BY name
but this is not giving me the result that I am looking for. What is the best way to match a DateTime field type using the current date without the time?
View 5 Replies
View Related
Jul 28, 2014
I need to get previous month data in the table based on current date.
In case of execution of each month, the data for previous month should come with date as between
create table TestDate
(Sno Int,
Name varchar(100),
DateofJoin datetime)
insert into TestDate values (1,'Raj', '2/21/2014')
insert into TestDate values (1,'Britto', '6/12/2014')
insert into TestDate values (1,'Kumar', '5/14/2014')
insert into TestDate values (1,'Selva', '6/27/2014')
insert into TestDate values (1,'Ravi', '5/2/2014')
insert into TestDate values (1,'Gopu', '6/2/2014')
/*
if I execute in month July ( ie: today)
select * from TestDate where dateofjoin between 1-june-2014 and 30-june-2014
Result
5 Ravi 2014-05-02 00:00:00.000
3 Kumar 2014-05-14 00:00:00.000
if I execute in month June
select * from TestDate where dateofjoin between 1-may-2014 and 30-may-2014
Result
6Gopu2014-06-02 00:00:00.000
2Britto2014-06-12 00:00:00.000
4Selva2014-06-27 00:00:00.000
/*
View 1 Replies
View Related
Jan 21, 2015
I have a table with score info for each group, and the table also contains historical data, I need to get the ranking for the current week and previous week, here is what I did and the result is apparently wrong:
select CurRank = row_number() OVER (ORDER BY cr.CurScore desc) , cr.group_name,cr.CurScore
, lastWeek.PreRank, lastWeek.group_name,lastWeek.PreScore
from
(select group_name,
Avg(case when datediff(day, asAtDate, getdate()) <= 7 then sumscore else 0 end) as CurScore
[Code] ....
The query consists two parts: from current week and previous week respectively. Each part returns correct result, the final merged result is wrong.
View 3 Replies
View Related
Oct 28, 2014
I have 3 tables...
JobRequirements (A)
JobID int
QualificationTypeID int
EmployeeQualifications (B)
EmployeeID int
QualificationTypeID int
Employee (C)
EmployeeID int
EmployeeName int
I need to return a list of all employees fit for a specific job ... The criteria is that only employees who have all the JobRequirements are returned. So if a job had 3 requirements and the employee had just 2 of those qualifications, they would not be returned. Likewise, the employee might have more qualifications than the job requires, but unless the employee has all the specific qualifications the job requires they are not included. If an employee has all the job qualifications plus they have extra qualifications then they should be returned...
How to only return those records where all the child records are present in the other table..
View 5 Replies
View Related
Mar 25, 2002
I'm stuck. I have a table that I want to pull some info from that I don''t know how to.
There are two colomuns, one is the call_id column which is not unique and the other is the call_status column which again is not unique. The call_status column can have several values, they are ('1 NEW','3 3RD RESPONDED','7 3RD RESOLVED','6 PENDING','3 SEC RESPONDED','7 SEC RESOLVED').
i.e example, this is the existing data.
Call_id Call_Status
555555 3 3RD RESPONDED
235252 7 SEC RESOLVED
555555 7 3RD RESOLVED
325252 6 PENDING
555555 6 PENDING
325235 3 SEC RESPONDED
555555 1 NEW
This is the data I want...
Call_id Call_Status
555555 3 3RD RESPONDED
555555 6 PENDING
555555 7 3RD RESOLVED
The call_id could be any number, I only want the 6 PENDING rows where there are other rows for that call_id which have either 3 3RD RESPONDED or 7 3RD RESOLVED. If someone knows how it would be a great help.
Cheers,
Chris
View 1 Replies
View Related
Sep 30, 2015
I need to create a set so that when a user selects a month in filter (say 201506) then it should give me a list of months from 201406 to 201506. Any appropriate MDX query.
View 7 Replies
View Related
May 19, 2012
I am trying to create a stored procedure where i would like to fetch the next previous and current rows from a particular pagename which is stored in a table with sorting of dateadded
like my products table structure
id
iQty
strProductTitle
strPageName
DtAdded
DtApproved
my dummy records are like
1 13 'PCHDD' 'PCHDD' '2009-12-03 04:32:30.363' '2009-12-04 04:32:30.363'
2 30 'SDRAM' 'SD_Ram' '2009-12-03 04:32:30.363' '2009-12-06 04:32:30.363'
3 12 'Pen Drive' 'Pendrive' '2009-12-03 04:32:30.363' '2009-12-05 04:32:30.363'
4 3 'Note Book' 'NoteBook' '2009-12-03 04:32:30.363' '2009-12-08 04:32:30.363'
5 15 'VIO' 'VIO' '2009-12-03 04:32:30.363' '2009-12-06 04:32:30.363'
6 19 'PS2' 'PS_2' '2009-12-03 04:32:30.363' '2009-12-09 04:32:30.363'
7 31 'PS3' 'PS_3' '2009-12-03 04:32:30.363' '2009-12-02 04:32:30.363'
8 23 'WII' 'WII' '2009-12-03 04:32:30.363' '2009-12-10 04:32:30.363'
9 22 'Speakers' 'Speakers' '2009-12-03 04:32:30.363' '2009-12-16 04:32:30.363'
10 12 'Iphone' 'I_phone' '2009-12-03 04:32:30.363' '2009-12-15 04:32:30.363'
now if i query for a record like
I_phone
i should get
Prv Detail I_pad row
11 23 'Ipad' 'I_pad' '2009-12-03 04:32:30.363' '2009-12-11 04:32:30.363'
Current Detail I_phone
10 12 'Iphone' 'I_phone' '2009-12-03 04:32:30.363' '2009-12-15 04:32:30.363'
Next Record Speakers
9 22 'Speakers' 'Speakers' '2009-12-03 04:32:30.363' '2009-12-16 04:32:30.363'
View 2 Replies
View Related
Jul 19, 2006
Hello,I want to select the names and number of rows of all tables in thecurrent database, whose name starts with 'sys'my query is:select o.[name], sum(i.[rows]) as numbersfrom sysobjects oinner join sysindexes ion (o.[id] = i.[id])where o.[name] like 'sys%'and i.indid=1group by o.[name]the result of the query is (depending of the database) :name numberssysaltfiles 14syscharsets 114syscolumns 4934syscomments 2035sysconfigures 38sysdatabases 7sysdepends 5524sysdevices 6sysfilegroups 1sysfulltextcatalogs 0sysfulltextnotify 0sysindexes 98syslanguages 33sysmessages 3795sysobjects 1285syspermissions 806sysproperties 0sysreferences 0sysservers 1systypes 26sysusers 14sysxlogins 3But when I count the number of rows of "sysobjects", I getnumbers=1298, which is different from the result displayed above andsame goes for "syscolumns".I also tried "dbcc updateusage [0]" to update the SQL Server but Ididnt help me.Can anyone please advice me on this behaviour?
View 2 Replies
View Related
Oct 26, 2006
Does anyone know of a way to use a funtion for returning records based on fiscal reporting periods like Quickbooks uses for example "This Month", "Last Month", "This Quarter", "Last Quarter", "This Year", "Last Year". While I realize that I can create a very long date time parsing routine for this but it is not very elegant or useful. I thought there might be a way to do this already with an existing function.I have created a stored procedure that I pass a @ViewRange Parameter to and it returns the records that I want but I need this ability in several procedures and wanted to turn it into a stored procedure.IF @ViewRange = 'This Month' SELECT TOP 20 Customer.LastName AS Customer, SUM(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE (MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP)) AND (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY SUM(Sales.AmtCharge) DESC;IF @ViewRange = 'Last Month' SELECT TOP 20 Customer.LastName AS Customer, Sum(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE(MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP) - 1) And (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY Sum(Sales.AmtCharge) DESC; Any ideas?
View 8 Replies
View Related
