SQL Server 2012 :: CTE To Return All Periods Between First And Last Transaction Date
Jan 21, 2014
I'm trying to create a cte to return a list of lots and every period between the first and last transaction date for each lot. I've gotten this far:
SELECTIntLotKey
,DATEPART(YYYY, StartDate)StartYear
,DATEPART(MM, StartDate)StartPeriod
,DATEPART(YYYY, EndDate)EndYear
,DATEPART(MM, EndDate)EndPeriod
[Code] ....
This gives me the following results:
IntLotKeyStartYearStartPeriodEndYearEndPeriod
271532013120135
28468201312201312
2846920131201312
2847020131201312
28472201312201312
593022013120131
593032013120131
Now what I need is something that looks like this:
LotKeyYearPeriod
2715320131
2715320132
2715320133
2715320134
2715320135
[Code] .....
Some lots may not have any transactions for some of the periods between the start and end dates but I need to report every period between the start and end period for each lot. I have a period table that I thought I could use but haven't come up with a way to get the results I'm after.
View 9 Replies
ADVERTISEMENT
Sep 4, 2014
I have a simple script where I want to pull GLAcct, GLDesc and Amounts by Period. I want my results to look like attached snip.
I tried playing around with the dates; however, I'm receiving errors. Just to note that when I ran for 07/01/14 - 07/31/14 with the transaction date in where clause I was able to retrieve the correct results. Now I want to expand to get a view set up for the whole year....automation!
select
gl_account.id as GLAcct,
gl_account.descr as GLDesc,
sum(gl_ledger.amount_n) as Net
from gl_account
[Code] ....
View 9 Replies
View Related
Jun 20, 2007
Hi, this is my first post here, i'm working on a view wich I have to display the results by period.
1st period - Current month
2nd period - Last month
3rd period - Month before last
and so on.
How can I achieve this? of course I have a column wich has the date I want to use, I was thinking on semthing like:
case when DATEDIFF(M, TRXDATE, getdate()) <= 0 then ammount END as Period1
(TRXDATE and ammount are columns on my table)
Any ideas? would this work?
Thanks for your replies
View 2 Replies
View Related
Oct 10, 2005
Hi,
I need to compare 2 periods (start date / end date) in order to find out if the first period overlaps or in included in the second period.
any idea ???
thanks
View 14 Replies
View Related
Feb 10, 2006
Guys
Date brain killer - I have a startdate and enddate and need, for a given period length in months, to identify the number
of periods betwen the startdate and enddate
so for example
DECLARE @Periods TABLE
(
StartDate datetime,
Enddate datetime,
NumberOfMonthsInPeriod INT ,
TotPeriodsINT
)
-- Initial data
insert @Periods
(
StartDate ,
Enddate ,
NumberOfMonthsInPeriod ,
TotPeriods-- for this illustration, initialised to 0 but need to be UPDATEd as per detail below
)
select
'30-Sep-2005',
'10-Apr-2009',
1 -- 1 month period
0
union all
select
'30-Sep-2005',
'10-Apr-2009',
3 -- 3 month period
0
union all
select
'30-Sep-2005',
'10-Apr-2009',
6 -- 6 month period
0
The following rules regarding periods apply
Each 1 (NumberOfMonthsInPeriod) month period is as would be expected ie
01-Jan - 31 Jan
01-Feb - 28-Feb (ie 1st March - 1 day which would deal with leap years - dateadd(...)
and so on to December
Each 3 (NumberOfMonthsInPeriod) month period is one of the following 'bands' per year
01-Jan - 31-Mar
01-Apr - 30-Jun
01-Jul - 30-Sep
01-Oct - 31-Dec
Each 6 (NumberOfMonthsInPeriod) month period is one of the following
01-Jan - 30-Jun
01-Jul - 31-Dec
I need to derive TotPeriods as follows:
For the row where NumberOfMonthsInPeriod = 1, the first period ie the one the start date falls within is 01-Sep-2005 to 30-Sep-2005, second is 01-Oct-2005 - 31-Oct-2005 and so on until
last period ie the one the end datye falls within is 01-Apr-2009 - 30-Apr-2009, a TotPeriods value of 44
For the row where NumberOfMonthsInPeriod = 3, first period is 01-Jul-2005 to 30-Sep-2005, second is 01-Oct-2005 - 31-Dec-2005 and so on until
last period is 01-Apr-2009 - 30-Jun-2009, a TotPeriods value of 16
For the row where NumberOfMonthsInPeriod = 6, first period is 01-Jul-2005 to 31-Dec-2005, second is 01-Jan-2006 - 30-Jun-2006 and so on until
last period is 01-Jan-2009 - 30-Jun-2009, a TotPeriods value of 8
Hope this is clear and thanks in advance
View 3 Replies
View Related
Nov 9, 2006
hi guys,
i have a booking table which has the following columns...
booking
-------------------------------------------
dCheckin (format 11/9/2006 12:00:00 AM)
dCheckout (format 11/11/2006 12:00:00 AM)
when a new booking is entered, we want to make sure that the period entered does not conflict with an existing record.
not sure how to go about building the query required. any help would be greatly appreciated.
mike
View 4 Replies
View Related
Oct 7, 2014
How can I compute the biweekly periods when a date is passed in as a parameter, for example if the user enters 9/12/2014 I should get:
Friday /2nd half9/12/2014
Saturday9/13/2014
Sunday9/14/2014
Monday9/15/2014
Tuesday9/16/2014
Wednesday9/17/2014
Thursday9/18/2014
Friday/1st half 9/19/2014
Friday/2nd half9/19/2014
Saturday9/20/2014
Sunday9/21/2014
Monday9/22/2014
Tuesday9/23/2014
Wednesday 9/24/2014
Thursday9/25/2014
Friday/1st half 9/26/2014
View 2 Replies
View Related
Aug 20, 2015
Question: How to determine if a date value was between one of the date periods that appear in multiple rows?
Background: We have a table of "license valid" periods, wherein each license can have one or more rows. (As you know, a driver's license can be started, expired, renewed, suspended, reinstated, revoked, etc.) Instead of of having a license activity table--from which valid license periods could be extrapolated--we store just the periods for which a license was valid.
My task is to take a list of licenses and specific dates and determine if each license was valid as of that date, returning either true or false. What is the best way to accomplish this?
DECLARE @ValidityInQuestion TABLE (
LicenseID int
, DateValidityInQuestion date);
DECLARE @LicenseValidPeriods TABLE (
LicenseID int
, BeginDate date
, EndDate date);
[Code] ...
How then do I query both tables in order to get the same result that results from the following query?
SELECT
12345 AS LicenseID
, '2015-01-15' AS DateValidityInQuestion
, 1 AS LicenseActive
UNION
SELECT
67890
, '2015-02-04'
, 0;
I assume I need to join on the LicenseID columns, but my understanding stops there.
View 4 Replies
View Related
May 30, 2015
I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.
I have table containing 5 columns.
caseid
referenceid
startdate
enddate
caseopendate
[code]...
View 4 Replies
View Related
Mar 18, 2014
I have a query to run a report where the results has a column named “Due Date” which holds a date value based on the project submission date.Now, I need to add 4 columns named, “45 Days Expectant”, “30 Days Overdue”, “60 Days Overdue” and “90 Days Overdue”.I need to do a calculation based on the “Due Date” and “System (I mean default computer date) Date” that if “System Date” is 45 days+ to “Due Date” than put “Yes” in “45 Days Expectant” row.
Also, if “Due Date” is less than or equal to system date by 30 days, put “Yes” in “30 Days Overdue” and same for the 60 and 90 days.how to write this Case Statement? I have some answers how to do it in SSRS (Report Designer) but I want to get the results using T-SQl.
View 2 Replies
View Related
Jun 2, 2014
I'm writing a view to check record counts in a table that has numerous datasets and therefore various "Activity Dates". Is it possible as part of the SQL statement to have a CASE statement for example so that it can identify the field to use as the activity date?
The field to use is being identified using a seperate table so at the moment I have CASE WHEN FieldToUse = '2' THEN MapCol ELSE '[Activity_Date]' END, where FieldToUse = '2' identifies the date field to use and the MapCol data is the field name to be used as the activity date.
Is this even possible?
View 3 Replies
View Related
Nov 21, 2013
i need a query to return the top 10 tables in each database on a server. have used EXEC sp_msforeachtable 'sp_spaceused ''?''' which returns what I need for one db but I need it to loop through and gather the info for all dbs on server. maybe need cursor not sure. for reporting reasons i would like to include the name of the server and name of database.
View 3 Replies
View Related
Nov 22, 2013
I have two tables I am working with, they are "Institutions" and "InstitutionOversights". The relationship is one-to-many.
The sample data is below.
Table one:
InstitutionID, InstName
------------------------
1 School Alpha
2 School Beta
3 School Charlie
4 School Delta
Table two:
InstitutionOversightID, InstitutionID, Type
------------------------------------------------
1 1 Accreditation
2 1 Verifcation
3 1 Old System
I would like a query to return the results in the following format:
InstitutionID, InstName, TypeList
-----------------------------------------------
1 School Alpha Accreditation, Verification, Old System
2 School Beta null
3 School Charlie null
4 School Delta null
View 3 Replies
View Related
Feb 16, 2014
i'm building a query to return metrics that will drive 3 seperate pivot tables showing
1. Total count of LineItems per D_Type each month
2. Total count of LineItems per Status each month
3. Avg count of LineItems per Invoice each month
I am able to get the first two, but having hard time with the 3rd.
Here's some representative ddl
create table Remediation
(Invoice nvarchar(10), D_Type nvarchar(20), Status nvarchar(20), RemediationDate datetime);
insert into Remediation values
--this will create data for Jan, 2014
('501', 'Recycle', 'Pass', getdate()-30),
('501', 'Reuse', 'Pass', getdate()-30),
('501', 'Remarket', 'Fail', getdate()-30),
[code]....
how to add the average metric to this query?
View 9 Replies
View Related
May 9, 2014
Very basically, I need to return a result set based on another value and only if there are multiples of that other value.
Example. select * from mytable
Returns (--column separater)
John---1---1
John---1---1
John---2---2
John---3---3
John---3---3
John---4---4
So I want a query that would return only
John---1---1
John---1---1
John---3---3
John---3---3
The query cannot be something as simple as this:
Select * from mytable where John=1 or John=3
I have to many possible results to plan like that. I am thinking some kind of aggregate with a group.
View 9 Replies
View Related
Aug 5, 2014
i am using the followig query :
select count (*) from MEMBERS,dbo.MEMBER_PROFILE
where MEMBER_PROFILE.member_no = members.member_no
AND JOIN_DATE between '07-01-2013 00:01' and '07-31-2013 11:59'
and email <> 'selfbuy_customer@cafepress.com'
and ROOT_FOLDER_NO is not null
and email not like '%bvt.bvt'
This returns the count for the month but I want to see what the total each day was.
View 5 Replies
View Related
Mar 3, 2015
I have created a function that will check whether the data is null or not. If its null then it will display that as No data else it will display the original value. Below is the function
GO
Object: UserDefinedFunction [dbo].[fnchkNull] Script Date: 3/4/2015 12:01:58 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[code]...
The code is working good. However i want the return type to be dynamic. If the data type supplied is integer then i want to return a integer value like 0 if its null. if the data value is varchar then i want to return 'No Data'.
View 7 Replies
View Related
May 5, 2015
In a t-sql 2012 select statement, I have a query that looks like the following:
SELECT CAST(ROUND(SUM([ABSCNT]), 1) AS NUMERIC(24,1)) from table1. The field called [ABSCNT] is declared as a double. I would like to know how to return a number like 009.99 from the query. I would basically like to have the following:
1. 2 leading zeroes (basically I want 3 numbers displayed before the decimal point)
2. the number before the decimal point to always display even if the value is 0, and
3. and 2 digits after the decimal point.
View 3 Replies
View Related
Nov 21, 2013
I have this SP
ALTER PROCEDURE GetDelayIntervalData(@start datetime, @stop datetime, @step int)
AS
DECLARE @steps bigint
SET @steps = DATEDIFF(hour, @start, @stop)/ @step
DECLARE @i bigint
SET @i=0
[Code] ....
View 1 Replies
View Related
Jun 9, 2014
I have a procedure where after processing, i am required to send multiple message to calling application.
For ex:
create procedure test as
(@a as int,
@b as int
@c as int )
/*
some transformations */
print 'variable a is' + @a;
print 'variable b is' + @b;
print 'variable c is' + @c;
I am only providing a sample of return messages, but in reality there a lot more messages where a string and a parameter value need to concatenated.Unfortunately print is not allowing to concatenate parameter value.I can use RaiseError, but these messages are not really any error messages.
i tried to concatenate all message and output it using OUT type parameter, but the length of all messages combined exceeds 10000 characters.
Is there any other alternate to send these messages out to application?
View 1 Replies
View Related
Jul 9, 2014
SQL Server 2012 Standard SP 1.
Stored procedure A calls another stored procedure B. Rowcount is set properly in called procedure B, but does not seem to return it to calling procedure A. Otherwise the two stored procedures are working correctly. Here is the relevant code from the calling procedure A:
declare @NumBufferManagerRows int = 0;
exec persist.LoadBufferManager @StartTicks, @EndTicks, @TimeDiff, @NumBufferManagerRows;
print 'BufferManagerRows';
print @NumBufferManagerRows;
Print statement prints @NumBufferManagerRows as 0.
Here is the called stored procedure B:
CREATE PROCEDURE [persist].[LoadBufferManager]
-- Add the parameters for the stored procedure here
@StartTicks bigint,
@EndTicks bigint,
@TimeDiff decimal(9,2),
@NumRows int OUTPUT
[Code] ...
View 2 Replies
View Related
Feb 24, 2015
I backed up my transaction log file using this:
BACKUP LOG iTest2_AELetters TO DISK = N'nul'(yes I know this breaks the log chain - it's expected in this intance)
But after I run it my t log is exact same size. I have refreshed many times.
I am using SQL Always On on this DB.
View 4 Replies
View Related
May 3, 2015
I'm taking the liberty to announce the availability of a suite of articles on my web site about error and transaction handling in SQL Server. In total there are three main parts and three appendixes.
The first part is a short jumpstart, while Part Two is a long in-depth discussion of what can happen in SQL Server in case of an error and what commands that are available. Part Three covers implementation and has lot of examples as well as a facility for logging and raising errors.
The appendixes cover special areas: linked servers, the CLR and Service Broker.
Start at [URL] ....
View 1 Replies
View Related
Aug 14, 2015
I have a Database that is set to Simple Recovery mode.
It is not clearing the log and the transaction log is growing.
I thought that it would clear the log upon checkpoint?
How can I force the Transaction Log to clear since NO-TRUNCATE is no longer available?
View 9 Replies
View Related
Dec 19, 2013
My overarching goal is to generate sets of random Symptom records for each Enrollee in a drug study, so that for each cycle (period of time), the code will insert a random number of random records for each enrollee.
I'm trying to return a number of random records from a table, but inside a table-valued function... (which could be my problem).
CREATE FUNCTION dbo.ufn_GetTopSymptoms (
@enrollID INT
, @CTCVersion VARCHAR(20)
, @NumRecords INT
)
RETURNS TABLE
[Code] ....
But that ORDER BY NEWID() clause is illegal apparently, because here's the error it throws:
Msg 443, Level 16, State 1, Procedure ufn_GetTopSymptoms, Line 13
Invalid use of a side-effecting operator 'newid' within a function.
I was hoping I could return a set of enrollmentIDs and then use CROSS APPLY to generate a random set of records for each enrollmentID... is this not possible with APPLY? I was trying to avoid using a cursor...
The idea is basically to create all the Symptom records for all the patients in treatment cycle at once by using Enrollee OUTER APPLY dbo.ufn_GetTopSymtoms(dbo.Enrollment.EnrolleeID)
but that's clearly not working. Is there a way to do this without resorting to a cursor?
View 9 Replies
View Related
Nov 11, 2014
I need only the count of databases that last fullbackup was older then 24 hours or null. and status is online. I have tried
SELECT Count(DISTINCT msdb.dbo.backupset.database_name)
From msdb.dbo.backupset
where datediff(day,backup_finish_date,GETDATE()) > 1 -- or is null
and Database_Name not in ('tempdb','ReportServerTempDB','AdventureWorksDW','AdventureWorks') --online also
group by Database_name, backup_finish_date
Tried using where max(backup_finish_date) < datediff(day,backup_finish_date,GETDATE()) .But get the aggregate in where clause error. get a count of databases with backups older than 24 hours not including the samples, report service, and tempdb. I would also want to put status is online but havent gotten the above to work so havent tried to add that yet.
View 2 Replies
View Related
Dec 29, 2014
What I want to do is return a row of data when my query doesn't return a record. I have two tables:
CREATE TABLE dbo.abc(
SeqNo smallint NULL,
Payment decimal(10, 2) NULL
) ON PRIMARY
[Code] ....
So when I run the following query:
SELECT 'abc' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR) FROM abc WHERE SeqNo = 1
UNION
SELECT 'def' + '-' + CAST(SeqNo AS VARCHAR) + '-' + CAST(Payment AS VARCHAR) FROM def WHERE SeqNo = 1
abc-1-200.00
abc-1-500.00
As you can see since 1 doesn't exists in table 'def' nothing is returned as expected. However, if a row isn't returned I want to be able to enter my own row such as
abc-1-200.00
abc-1-500.00
def-0-0.00
View 4 Replies
View Related
Apr 22, 2015
Table1 contains a CODES column which may store multiple codes separated by commas.
The descriptions for these codes are stored individually in a column in in Table2.
I would like to return the descriptions from Table2 in the same format as the codes from Table1.
Table1 Stores multiple codes per ID in a single column.
ID CODES
-- --------------
01 Code1,Code2,Code3
Table2 Stores the definitions for the codes in Table1
CODE DESCRIPTION
----- ---------------
Code1 Description1
Code2 Description2
Code3 Description3
The tables are what the tables are. Here is what I am trying to accomplish:
ID CODES DESCRIPTIONS
-- ----------------- --------------------------------------
01 Code1,etc Description1,Description2,Description3
I've found two different queries which return each code and associated description by ID in separate rows. Perhaps the next step is to aggregate from this?
View 7 Replies
View Related
May 4, 2015
I'm trying to convert the query immediately below into a function with the conditional logic to return a VARCHAR value with the gender: male, female or unknown.
SELECT empid, firstname, lastname, titleofcourtesy,
CASE
WHEN titleofcourtesy IN('Ms.', 'Mrs.') THEN 'Female'
WHEN titleofcourtesy = 'Mr.' THEN 'Male'
ELSE 'Unknown'
END AS gender
FROM HR.Employees;
GO
Below is the conditional logic function I'm trying to create to replicate the logic above.
CREATE FUNCTION dbo.Gender
(
@male AS VARCHAR(10),
@female AS VARCHAR(10),
@unknown AS VARCHAR(10)
)
RETURNS VARCHAR(10)
[Code] .....
View 6 Replies
View Related
Aug 9, 2007
(I may be in the wrong forum.)
How do I obtain/find the properties of a table using SQL Query Analyer (SQL Server 2000)? Specifically, I would like to run a query to find the most recent date of any transaction on a table. I have a script that I use for SQL Server 2005 but it doesn't work in 2000. I don't know 2000 but I'm guessing that the syntax is different?
Here's the 2005 SQL Server script (stolen from 2005 Books Online BTW):
CREATE TABLE ddl_log (PostTime datetime, DB_User nvarchar(100), Event nvarchar(100), TSQL nvarchar(2000));
GO
CREATE TRIGGER ddl_log
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
SET @data = EVENTDATA()
INSERT ddl_log
(PostTime, DB_User, Event, TSQL)
VALUES
(GETDATE(),
CONVERT(nvarchar(100), CURRENT_USER),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;
GO
Here's the error:
Msg 156, Level 15, State 1, Procedure ddl_log, Line 2
Incorrect syntax near the keyword 'DATABASE'.
Remember, I want to do the same thing in SQL Server 2000.
Thanks in advance for any assistance you can provide.
View 1 Replies
View Related
Oct 28, 2015
i have written a sql function which returns only number of working days (excludes holidays and Weekends) between given StartDate and EndDate.
USE [XXX]
GO
/****** Object: UserDefinedFunction [dbo].[CalculateNumberOFWorkDays] Script Date: 10/28/2015 10:20:25 AM ******/
SET ANSI_NULLS ON
GO
[code]...
I need a function or stored procedure which will return the date which is 15 working days (should exclude holidays and Weekends) prior to the given future Date? the future date should be passed as a parameter to this function or stored procedure to return the date. Example scenario: If i give date as 12/01/2015, my function or stored procedure should return the date which is 15 working days (should exclude holidays and Weekends) prior to the given date i.e 12/01/2015...In my application i have a table tblMasHolidayList where all the 2015 year holidays dates and info are stored.
View 18 Replies
View Related
Oct 7, 2015
How do you Write an expression to Return the date value based on the most recent date only?
Eg. Date 10-7-2015, action - done, notes - all items fixed.
Date 4-5-2016, action - not yet done, notes - buying parts
All these dates are returned based on a search parameter based on a project number. I only want the most current date and associated fields displayed.
View 4 Replies
View Related
Jun 27, 2014
I am basically trying to update a table which reflects account transactions. Accounts get paid in full but occasionally balance payments can be reversed and I want to update the table to show this - I need to show which period the account was previously paid in full.I've created a simplified version of the scenario and below are a couple of examples of things I've tried that do not work. I understand why they do not work but I'm struggling to figure out how to update the 'PeriodPrevPaidInFull' field.
create table Trans
(
AccNo int,
Transaction_Period_Index int,
PeriodOpeningBalance money,
DebtBalance money,
PeriodPaidInFull int NULL,
PeriodPrevPaidInFull int NULL,
[code]...
View 9 Replies
View Related
