All Sales Reported After Quarter End
Apr 19, 2007
It's been a long time since I've built tough queries and need some help.
I need to report all sales that happened within a quarter but were reported after the quarter end.
Quarters
Q1 - 1/1 thru 3/31
Q2 - 4/1 thru 6/30
Q3 - 7/1 thru 9/30
Q4 - 10/1 thru 12/31
Sample Data
ticketid salespersonid saledate sale_entereddate
1234 bsmith 1/1/2006 2/1/2006
1235 jgarcia 3/31/2006 4/1/2006
1236 bsmith 1/1/2006 2/3/2006
1237 jdoe 6/23/2006 7/1/2006
1238 bsmith 8/5/2006 8/6/2006
1239 bsmith 10/1/2006 1/1/2007
View 4 Replies
ADVERTISEMENT
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
May 19, 2015
i am trying to find a way to link an 'initial' Sale ID of a product to 'future' Sale IDs of products that will trace back to the original Sale ID.For example, if I call the original sale , 'Sale ID #123', how can i link future Sale ID's (child[ren]) and all future sales to the original Sale ID #123? Can I use a Surrogate Key or similar function?
Parent:Sale ID #123
Children: Sale ID # 456,
Sale ID #789,
Sale ID #.....
how I can link the original Sales ID (Parent) to Sale ID's (child[ren]) of future purchases currently existing and in the future going forward?
View 4 Replies
View Related
Dec 9, 2014
I have the below data. I need to produce a report that shows customer and total sales who had the max sales by year.
Order ID Cust ID Year Sales
O1 C1 2000 100
O2 C1 2000 150
O1 C2 2000 50
O1 C1 2001 150
O2 C3 2001 200
Report:
Cust ID Year Sales
C1 2000 250
C3 2001 200
View 1 Replies
View Related
Sep 30, 2015
I want to calculate the sum of actual sales until a date and forecast sales after a date.I am not sure what the best approach to this problem is, but I have tried my best with the following approach. Any better ways to solve this (using DAX).
I have created a parameter table that offers the last date of each month as possible choices to the user. I have tried to create a measure that sums actual sales up until this date.
SalesQuantityActual:=IF(HASONEVALUE(parLastActualMonth[Date]);CALCULATE(factSalesActual[Quantity];factSalesActual[Date]<=VALUES(parLastActualMonth[Date]));BLANK())
Unfortunately the measure above does not work.
In addition to the parameter table, I also have a normal date table.
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
May 26, 2008
All of you just read this article
http://www.bloombit.com/Articles/2008/05/ASCII-Encoded-Binary-String-Automated-SQL-Injection.aspx
View 1 Replies
View Related
Dec 2, 2005
I actually ran this on a reporting copy of my DB (restored once a week). I would guess the errors are present in the main copy as well. I fear running the check during production hours as it takes 2.5 hours and the system (4 processor) is under a lot of load for almost the entire day and much of the night.
So I've got errors on the tables:
sessions ID(462624691)
map ID(510624862)
links ID(1790629422)
It looks like the repair with data loss is my only choice but I would definitely prefer to know which rows are messed up. I'm also wary of running the repair as it's probably going to take well over an hour on the huge sessions table. I don't know how it got in this corrupted state. I inherited a DB of this size (50GB) that's been known to be very sensitive to any admin or index fiddling due to the high load. After I get this repaired I feel I should run the CHECKDB in a maintenance plan but I'm not sure if this will cause slowdowns in itself if it runs for 2.5 hours. The DB backup takes over 2 hours leaving almost no spare time in the off peak time.
Any advice/suggestions? Thanks.
DBCC results for 'dbloc'.
DBCC results for 'sessions'.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 462624691, index ID 0: Page (1:9517861) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 462624691, index ID 0: Page (1:9659261) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 462624691, index ID 7: Page (1:5825743) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 462624691, index ID 7: Page (1:5890660) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 462624691, index ID 7: Page (1:5931145) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 462624691, index ID 7: Page (1:5950021) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 462624691, index ID 7: Page (1:5978420) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 462624691, index ID 7: Page (1:5988261) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 462624691, index ID 7: Page (1:6046306) could not be processed. See other errors for details.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 109 and 110.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 112 and 113.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 113 and 114.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 114 and 115.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 117 and 118.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 118 and 119.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 121 and 122.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 122 and 123.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 510624862, Index ID 0. Keys out of order on page (1:11853027), slots 124 and 125.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 510624862, index ID 0: Page (1:11858027) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 510624862, index ID 0, page (1:11858027). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 510624862, index ID 0: Page (1:11865547) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 510624862, index ID 0, page (1:11865547). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 510624862, index ID 2: Page (1:11889147) could not be processed. See other errors for details.
DBCC results for 'sessions'.
There are 153364096 rows in 3102681 pages for object 'sessions'.
CHECKDB found 0 allocation errors and 9 consistency errors in table 'sessions' (object ID 462624691).
DBCC results for 'map'.
Server: Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 510624862, index ID 2, page (1:11889147). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 510624862, Index ID 3. Keys out of order on page (1:11931268), slots 206 and 207.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 510624862, Index ID 3. Keys out of order on page (1:11931268), slots 209 and 210.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 510624862, Index ID 3. Keys out of order on page (1:11931268), slots 210 and 211.
Server: Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 510624862, Index ID 3. Keys out of order on page (1:11931268), slots 230 and 231.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 510624862, index ID 11: Page (1:11988308) could not be processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 510624862, index ID 11, page (1:11988308), row 194. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 7251 and 18.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 510624862, index ID 11: Page (1:12003992) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 510624862, index ID 11, page (1:12003992). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1.
There are 15576829 rows in 65455 pages for object 'map'.
CHECKDB found 0 allocation errors and 23 consistency errors in table 'map' (object ID 510624862).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1790629422, index ID 16: Page (1:11471717) could not be processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 1790629422, index ID 16, page (1:11471717), row 88. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 3500 and 28.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1790629422, index ID 16: Page (1:11474112) could not be processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 1790629422, index ID 16, page (1:11474112), row 108. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 745 and 20.
DBCC results for 'links'.
There are 9919777 rows in 124284 pages for object 'links'.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'links' (object ID 1790629422).
CHECKDB found 0 allocation errors and 36 consistency errors in database 'dbloc'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (dbloc ).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
View 2 Replies
View Related
Oct 30, 2006
After upgrading a server from SQL Server 2000 to 2005,
an index was suddenly being reported as corrupt in the SQL Server log
(probably every time an Insert was done). DBCC CHECKDB / CHECKTABLE
reported no errors, with or without the new WITH DATA_PURITY option.
Anyone else experienced something similar?
How can an index be reported as corrupt when DBCC doesn't report it?
Dropping and re-creating the index solved the problem.
I've restored a backup of the database (made before dropping/re-creating
the index) to try repeating the problem, but no success so far.
View 1 Replies
View Related
Aug 15, 2014
I have a table that contains 1.6 million rows, and is about 2.6GB in size.One of the columns is ntext and contained some xml.Realistically, I don't need the xml for anything older than about 2 months, however, i'd like to keep some of the data in the other fields.I decided that if I updated the xml to blank, I would see some considerable space savings, however that doesn't appear to be the case.This is the output of sp_spaceused for my table
name rows reserved data index unused
CommitmentsForPosting1660979 2740336 KB 1857104 KB312 KB882920 KB
After I updated the table to remove the xml, the output of sp_spaceused remained the same.My first thought was that it was probably statistics, so I updated statistics for the table, and nothing changed.I then updated statistics for everything in the database, still no change.I then ran DBCC CLEANTABLE for that table. I didn't really expect it to make a difference, and no surprise, it didn't.
The index on the table is a clustered index on just a GUID column.I rebuilt the index and again it still made no difference (that's a bit of a lie - index_size changed by a few hundred KB)my next test was to run 'select * into XXX from YYY' to create a copy of this table with the same data and data types. I also created the same clustered index from the original table onto the new one.If I then run sp_spaceused on this new copy of the table, I see what I expect to see in sp_spaceused - a table using approximately 256MB of space
name rows reserved data index unused
SPS_TEST 1660979 266400 KB 266400 KB 304 KB56 KB
To be honest, this isn't hugely critical, but I'm just curious as to why the original table is still reporting 2.6GB size, when I think it should probably be nearer 256MB.
View 9 Replies
View Related
Dec 27, 2007
I'm trying to export excel data to a SQL Server database as:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=D:DATA_SOURCEData2.xlsx', 'SELECT * FROM [Sheet1$');
I get errors:
Error Message: The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
Is there any reason for this error message? Thanks.
View 6 Replies
View Related
Apr 11, 2006
Hi,
I have SQL Server 2005 Developer Edition installed on my laptop. At this time, our servers here are currently running SQL Server 2000 Enterprise edition. When I try to use the DTS import/export wizard to transfer records between two SQL Server 2000 databases, SQL 2005 always reports the transfer as successful! Although when you check the destination table there is only ever 1 record transfered
This has happened every I have attempted, on any tables, are there any fixes to either perfmit the copy of all records or at least report the error condition that prevents records being transfered?
Regards
Colin
View 4 Replies
View Related
Nov 1, 1999
Using BULK Insert with a format file I am receiving the following message:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAMS' reported an error. The provider did not give any information about the error.
The statement has been terminated.
I am running SQL Server 7.0 w/ SP1 applied. The same data file and format files work fine if I use bcp.
The data file contains fixed length records.
Any ideas what the problem is?
View 2 Replies
View Related
Jan 24, 2007
I have a report that is sent via email to a list of recipients. When I look at the subscription in 'My Subscriptions' the value in the Status column states that the message was 'sent successfully', however the email does not appear in the inboxes of any of the intended recipents. The report renders OK via the browser and only takes a few seconds to process. The subscription exports the report as an Excel file. The excel file is approx 8MB in size but this is still below the attachment size limit for internal emails within my organisation. The subscription is tied to a shared schedule. Other reports that are tied to the shared schedule are shown as successful in 'My Subscriptions' and also appear in the inboxes of recipients. All email address are correct.
I forgot to mention, the report was successfully received by all recipients until very recently and the report has not been modified since this time.
Has anyone come across this problem before.
Thanks
Mike
View 3 Replies
View Related
Nov 29, 2006
Greetings,
I receive an error message in event log when i try to connect to the Database Server using ODBC on a client machine. The database server is running on Windows 2003 Server Standard Edition and the client machine is Windows XP Professional. Following is the error message from the event log:
2147467259 - [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa' because the account is currently locked out. The system administrator can unlock it.
What causes the error to occur and how to resolve it?Appreciate for your assistence.
Thanks and regards,
Viknes
View 4 Replies
View Related
Jul 8, 2007
Hi Cindy,
After hours (days?) researching this, your name keeps popping up, you must be the GOTO person for VFP. So:
We have a server that runs a VFP application and stores .dbf free tables in a directory shared as F:apps ele.
I am working on an XPPro box running SQL Server Express 2005 using Management Studio Express.
I am logged on as a user with administrator rights for the domain
We would like to be able to query this data and move it to our SQL tables as needed.
Here is what I have:
EXEC sp_addlinkedserver @server = 'tele',
@srvproduct = 'VFP',
@provider = 'VFPOLEDB',
@datasrc = 'F:apps ele',
@provstr = 'VFPOLEDB.1'
GO
SELECT * FROM tele...PROSPECT_DIVU
Here is what I get:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "VFPOLEDB" for linked server "tele" reported an error. Access denied.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "VFPOLEDB" for linked server "tele".
I have tried:
exec sp_addlinkedsrvlogin
@rmtsrvname = 'tele',
@useself = false,
@locallogin = NULL,
@rmtuser = 'admin',
@rmtpassword = NULL
go
Same result.
I have also tried:
EXEC sp_addlinkedserver
'tele2',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'F:apps ele',
NULL,
'dBase IV'
GO
When I run:
select * from tele2...prospect_divu
I get:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "tele2" returned message "The Microsoft Jet database engine could not find the object 'prospect_divu'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_COLUMNS" for OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "tele2". The provider supports the interface, but returns a failure code when it is used.
What would you suggest?
View 4 Replies
View Related
Nov 26, 2007
I have a report which totals sales by customer. Then table footer has a grand total of all customer sales. I would like to get a percent of each customer's sales against the total sales. How do I get the sum from the table footer to use in an individual customer row?
Thanks.
View 3 Replies
View Related
Apr 29, 2015
I have inherited a server on which is a maintenance plan with two subplans on different schedules. In each subplan there are Execute T-SQL tasks with scripts for index rebuilds. Each task is set up with a Completion arrow to the next task and a Failure arrow to a Notify Operator Task. I was asked to add a task for index rebuilds to a specific subplan for a specific database, which is what the other tasks also do. I discovered that my task was failing but the others were fine. No notification was sent about my task failing even though the job is marked in MSDB as a failed job. I have sent a test email using the "Send Test Email..." option when right clicking Database Mail in SSMSand I receive an email so I know Database Mail works.
I set up a test job to model the index job that I can't get notifications from. I have two T-SQL tasks that just select the top row from a small table. The first task has a syntax error that I did so it would fail. I have a failure arrow to a Notify Operator Task and a Completion arrow to another T-SQL task with no syntax error which has a Success arrow to a Notify Operator task. As expected, when I execute this job I receive one failure email and one success email.
The only other troubleshooting step I know to try is to add a Notify Operator task before my failing task. That Notify Operator task will hopefully fire to tell me that the previous step was successful. I am not having problems with the other steps so I was just thinking I would try to get the subplan to send me a success email about one of the steps that has been working fine.
View 1 Replies
View Related
May 19, 2004
Hello, everyone:
There is a table named INCOME that has INCOME column for each day and DATE column starting from Aug. 29 1980. How to calculate income summary by each quarter? Thanks.
ZYT
View 9 Replies
View Related
Jan 28, 2007
Hi,
I need to retrieve both Mont and Quarter from an MS SQL server field containing a date as ''01/01/2007 12:10:00'.
Retrieving the Month is working properly, but getting some sql error with the Quarter function.
I will appreciate if someone can indicate me how should I do in order to retrieve the Quarter from a date.
To retrieve the month, I am using:
& "MONTH(STOCK.VALUEDATE) AS 'Month'"
To retrieve the Quarter, I am trying (but getting error):
& "QUARTER(STOCK.VALUEDATE) AS Quarter"
Thanks,
Aldo.
View 3 Replies
View Related
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
Dec 4, 2007
I have a transaction file with Company_code,Gl_code,Amount,Transaction_Date
I want to sum the amount based on a Company_Code,Glcode for every financial quarter(01/04/2007-30/06/2007,01/07/2007-30/09/2007 and so on).
Expected Output
Company_Code,Gl_code,Quarter_1,Amount_1,Quarter_2,Amount_2,Quarter_3,Amount_3,Quarter_4,Amount_4
Regards
Nirene
View 6 Replies
View Related
Aug 1, 2007
I am uisng VS 2005 to build reports for SQL 2005. The problem is with getting the Quarter() function, mentioned in the BOL, to work in the report builder. When I build an expression and try to use the function as noted in the BOL I receive an error stating that the name "Quarter" is not declaired. When I use the expression builder to build the expression the function is not available in the builder, which is probably why I get the error.
I am assuming there is some sort of service pack of hot fix I need to apply to my VS but have not been able to locate which one. I have verified that I have all of the recent service packs applied for VS and SQL server 2005 but I am still unable to see/use this function.
Any help would be greatly appreciated. I need this function for my financial reports.
View 1 Replies
View Related
Apr 4, 2008
Hi,
I am using QUARTER(startdate) formula to retrieve the quarter of a specific datetime and to store in my calculated field in dataset. But while running its showing "error: [BC30451] Name 'QUARTER' is not declared".
Can anyone show me some light on this problem?
View 5 Replies
View Related
Aug 18, 2015
There is a way to get the non empty max calendar quarter of the year and the last year. For example, the max calendar quarter of the last year should be 4, and in this moment the max quarter of this year should be 3.
I am building a report in SSRS and trying to avoid query the DWH database. I want to run every query against the cube.
View 3 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 24, 2014
I need accumulate the months by Quarter measurement. For example we're in March now and I need put these code in Where clause like:
Where MyDate > DATEADD(mm, -11, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) ...
here DATEADD(mm, -11, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) makes Apr of last year included because that is the first month in 2nd Quarter of last year. But in next month we're in Apr and I need make -11 as -12 in above code to include Apr of last year. And same reason if we're in Feb. 2014 then I need change -11 to -10.
So how can I calculate this to make the number fit into the code or some other way to get the same effect? I need get all last 4 quarters based on current month (include current Quarter).
View 2 Replies
View Related
Aug 20, 2014
I am trying to group counts by week,month,quarter, year for a particular activity type and I'm having issues.Here's my code so far:
SELECT
distinct
EmailAddressID,
emailaddress,
SUM(CASE WHEN [ActivityDate] >= DATEADD(WEEK, DATEDIFF(WEEK, 0, @DT), 0)
THEN
SUM(CASE WHEN EmailActivityType = 'OPEN' THEN 1 ELSE 0 END) END AS WeekTotalOpens
FROM EmailActivity
WHERE DATEPART(YEAR, [ActivityDate]) = DATEPART(YEAR, @DT)
GROUP BY EmailAddressID,emailaddress
Desired Output:
EmailAddressId EmailAddress WeekTotalOpens MonthTotalOpens etc. then WeekTotalClicks and so on....
SQL doesn't seem to like the sub-aggregate. What is the best way to approach?
View 2 Replies
View Related
Jul 23, 2005
Hello,I have a query that I would like to schedule in DTS. The criteria ofthis query checks for records in the table that are within the currentquarter. Here is what I have.WHERE submit_date BETWEEN '01/01/2005' AND '03/31/2005'I would like to dynamically generate the Quarter End and QuarterBeginning dates within my where clause based on the date that DTWSpackage is being executed on. Can anyone show me how this can beaccomplished?Thank You.
View 3 Replies
View Related
Jun 10, 2015
I am using the following expression to calculate the last day of the last quarter
=dateadd("s",-1,dateadd("q",datediff("q","1/1/1900",today()),"1/1/1900"))
It returns 3/31/2015 11:59:59 PM
What needs to change so that it return 3/31/2015 12:00:00 AM
View 4 Replies
View Related
Feb 28, 2005
I have a program that calls queries (OLAP system) the system includes a dimension of date: Year, Quater, Month, Week
When the result appears in the table, it is not in order? Only the year is in oredr and after that each heirachy is wrong and not in order....not sure how to do this!!!
any help would be grateful!!! not sure what I should be looking at.....
View 14 Replies
View Related
Sep 3, 2007
Hello,
what's best for year resp. quarter Field, char(4) resp. char(1), integer or other?
Both are part of a composite index.
Thanks
Silas
View 6 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
