September 21, 2015 at 5:00 pm
Hi,
I have three tables:
"PaymentsLog"
"DatePeriod"
"PaidOrders"
As per below
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PaymentsLog](
[ID] [int] NULL,
[DebtorName] [nvarchar](255) NULL,
[CreditController] [nvarchar](255) NULL,
[DueDate] [datetime] NULL,
[CurrencyCode] [varchar](10) NULL,
[Amount] [float] NULL,
[InjectedDate] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
insert into PaymentsLog Values (1,'COMPANY A','Jack Daniels','25-Sep-2015 00:00:00','GBP',100,'01-Sep-2015 10:00:09')
insert into PaymentsLog Values (1,'COMPANY A','Jack Daniels','25-Sep-2015 00:00:00','GBP',100,'03-Sep-2015 10:00:09')
insert into PaymentsLog Values (1,'COMPANY A','Jack Daniels','25-Sep-2015 00:00:00','GBP',100,'04-Sep-2015 10:00:09')
insert into PaymentsLog Values (1,'COMPANY A','Jack Daniels','25-Sep-2015 00:00:00','GBP',100,'02-Sep-2015 10:00:09')
insert into PaymentsLog Values (1,'COMPANY A','Jack Daniels','25-Sep-2015 00:00:00','GBP',100,'05-Sep-2015 10:00:09')
insert into PaymentsLog Values (2,'COMPANY B','Jim Beam','25-Sep-2015 00:00:00','GBP',200,'01-Sep-2015 10:00:09')
insert into PaymentsLog Values (2,'COMPANY B','Jim Beam','25-Sep-2015 00:00:00','GBP',200,'02-Sep-2015 10:00:09')
insert into PaymentsLog Values (2,'COMPANY B','Jim Beam','25-Sep-2015 00:00:00','GBP',200,'03-Sep-2015 10:00:09')
insert into PaymentsLog Values (3,'COMPANY C','Spice Morgan','25-Sep-2015 00:00:00','GBP',50,'03-Sep-2015 10:00:09')
insert into PaymentsLog Values (3,'COMPANY C','Spice Morgan','25-Sep-2015 00:00:00','GBP',50,'04-Sep-2015 10:00:09')
insert into PaymentsLog Values (4,'COMPANY D','Cheap Booze','25-Sep-2015 00:00:00','GBP',10,'01-Sep-2015 10:00:09')
insert into PaymentsLog Values (5,'COMPANY E','Expensive Booze','25-Sep-2015 00:00:00','GBP',300,'01-Sep-2015 10:00:09')
insert into PaymentsLog Values (5,'COMPANY E','Expensive Booze','25-Sep-2015 00:00:00','GBP',300,'02-Sep-2015 10:00:09')
insert into PaymentsLog Values (5,'COMPANY E','Expensive Booze','25-Sep-2015 00:00:00','GBP',300,'03-Sep-2015 10:00:09')
insert into PaymentsLog Values (5,'COMPANY E','Expensive Booze','25-Sep-2015 00:00:00','GBP',300,'04-Sep-2015 10:00:09')
insert into PaymentsLog Values (5,'COMPANY E','Expensive Booze','25-Sep-2015 00:00:00','GBP',300,'05-Sep-2015 10:00:09')
insert into PaymentsLog Values (5,'COMPANY E','Expensive Booze','25-Sep-2015 00:00:00','GBP',300,'06-Sep-2015 10:00:09')
insert into PaymentsLog Values (5,'COMPANY E','Expensive Booze','25-Sep-2015 00:00:00','GBP',300,'07-Sep-2015 10:00:09')
insert into PaymentsLog Values (5,'COMPANY E','Expensive Booze','25-Sep-2015 00:00:00','GBP',300,'08-Sep-2015 10:00:09')
insert into PaymentsLog Values (5,'COMPANY E','Expensive Booze','25-Sep-2015 00:00:00','GBP',300,'09-Sep-2015 10:00:09')
insert into PaymentsLog Values (5,'COMPANY E','Expensive Booze','25-Sep-2015 00:00:00','GBP',300,'10-Sep-2015 10:00:09')
insert into PaymentsLog Values (5,'COMPANY E','Expensive Booze','25-Sep-2015 00:00:00','GBP',300,'11-Sep-2015 10:00:09')
insert into PaymentsLog Values (6,'COMPANY F','Budweiser','25-Sep-2015 00:00:00','GBP',45,'03-Sep-2015 10:00:09')
insert into PaymentsLog Values (6,'COMPANY F','Budweiser','25-Sep-2015 00:00:00','GBP',20,'04-Sep-2015 10:00:09')
insert into PaymentsLog Values (6,'COMPANY F','Budweiser','25-Sep-2015 00:00:00','GBP',20,'05-Sep-2015 10:00:09')
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DatePeriod](
[DateID] [int] NOT NULL,
[StartDate] [date] NOT NULL,
[EndDate] [date] NOT NULL
) ON [PRIMARY]
GO
Insert into DatePeriod Values (1,'01-SEP-2015','15-SEP-2015')
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PaidOrders](
[ID] [int] NULL,
[DebtorName] [nvarchar](255) NULL,
[CreditController] [nvarchar](255) NULL,
[DueDate] [datetime] NULL,
[CurrencyCode] [varchar](10) NULL,
[Amount] [float] NULL,
[PaidDate] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
insert into PaidOrders Values (4,'COMPANY D','Cheap Booze','25-Sep-2015 00:00:00','GBP',10,'01-Sep-2015 00:00:00')
insert into PaidOrders Values (2,'COMPANY B','Jim Beam','25-Sep-2015 00:00:00','GBP',200,'03-Sep-2015 00:00:00')
insert into PaidOrders Values (6,'COMPANY F','Budweiser','25-Sep-2015 00:00:00','GBP',45,'03-Sep-2015 00:00:00')
insert into PaidOrders Values (3,'COMPANY C','Spice Morgan','25-Sep-2015 00:00:00','GBP',50,'04-Sep-2015 00:00:00')
insert into PaidOrders Values (1,'COMPANY A','Jack Daniels','25-Sep-2015 00:00:00','GBP',100,'05-Sep-2015 00:00:00')
insert into PaidOrders Values (6,'COMPANY F','Budweiser','25-Sep-2015 00:00:00','GBP',20,'05-Sep-2015 00:00:00')
The PaidOrders table is how I want the table result set to look like at the end, at the moment I am having to run the the below sql statements using the EXCEPT to tell me if the Amount has changed or the ID no longer exists and then I manually insert the data into the PaidOrders table.
Select
ID
,DebtorName
,CreditController
,DueDate
,CurrencyCode
,Amount
FROM PaymentsLog
where
InjectedDate between '01-SEP-2015' and '02-SEP-2015'
except
Select
ID
,DebtorName
,CreditController
,DueDate
,CurrencyCode
,Amount
FROM PaymentsLog
where
InjectedDate between '02-SEP-2015' and '03-SEP-2015'
Select
ID
,DebtorName
,CreditController
,DueDate
,CurrencyCode
,Amount
FROM PaymentsLog
where
InjectedDate between '02-SEP-2015' and '03-SEP-2015'
except
Select
ID
,DebtorName
,CreditController
,DueDate
,CurrencyCode
,Amount
FROM PaymentsLog
where
InjectedDate between '03-SEP-2015' and '04-SEP-2015'
Select
ID
,DebtorName
,CreditController
,DueDate
,CurrencyCode
,Amount
FROM PaymentsLog
where
InjectedDate between '03-SEP-2015' and '04-SEP-2015'
except
Select
ID
,DebtorName
,CreditController
,DueDate
,CurrencyCode
,Amount
FROM PaymentsLog
where
InjectedDate between '04-SEP-2015' and '05-SEP-2015'
Select
ID
,DebtorName
,CreditController
,DueDate
,CurrencyCode
,Amount
FROM PaymentsLog
where
InjectedDate between '04-SEP-2015' and '05-SEP-2015'
except
Select
ID
,DebtorName
,CreditController
,DueDate
,CurrencyCode
,Amount
FROM PaymentsLog
where
InjectedDate between '05-SEP-2015' and '06-SEP-2015'
Select
ID
,DebtorName
,CreditController
,DueDate
,CurrencyCode
,Amount
FROM PaymentsLog
where
InjectedDate between '05-SEP-2015' and '06-SEP-2015'
except
Select
ID
,DebtorName
,CreditController
,DueDate
,CurrencyCode
,Amount
FROM PaymentsLog
where
InjectedDate between '06-SEP-2015' and '07-SEP-2015'
Select
ID
,DebtorName
,CreditController
,DueDate
,CurrencyCode
,Amount
FROM PaymentsLog
where
InjectedDate between '06-SEP-2015' and '07-SEP-2015'
except
Select
ID
,DebtorName
,CreditController
,DueDate
,CurrencyCode
,Amount
FROM PaymentsLog
where
InjectedDate between '07-SEP-2015' and '08-SEP-2015'
Select
ID
,DebtorName
,CreditController
,DueDate
,CurrencyCode
,Amount
FROM PaymentsLog
where
InjectedDate between '07-SEP-2015' and '08-SEP-2015'
except
Select
ID
,DebtorName
,CreditController
,DueDate
,CurrencyCode
,Amount
FROM PaymentsLog
where
InjectedDate between '08-SEP-2015' and '09-SEP-2015'
Select
ID
,DebtorName
,CreditController
,DueDate
,CurrencyCode
,Amount
FROM PaymentsLog
where
InjectedDate between '08-SEP-2015' and '09-SEP-2015'
except
Select
ID
,DebtorName
,CreditController
,DueDate
,CurrencyCode
,Amount
FROM PaymentsLog
where
InjectedDate between '09-SEP-2015' and '10-SEP-2015'
Select
ID
,DebtorName
,CreditController
,DueDate
,CurrencyCode
,Amount
FROM PaymentsLog
where
InjectedDate between '09-SEP-2015' and '10-SEP-2015'
except
Select
ID
,DebtorName
,CreditController
,DueDate
,CurrencyCode
,Amount
FROM PaymentsLog
where
InjectedDate between '10-SEP-2015' and '11-SEP-2015'
Select
ID
,DebtorName
,CreditController
,DueDate
,CurrencyCode
,Amount
FROM PaymentsLog
where
InjectedDate between '10-SEP-2015' and '11-SEP-2015'
except
Select
ID
,DebtorName
,CreditController
,DueDate
,CurrencyCode
,Amount
FROM PaymentsLog
where
InjectedDate between '11-SEP-2015' and '12-SEP-2015'
Is there a way to look at the DatePeriod table and use the StartDtae and EndDate as the periods to be used in the select statement and then cursor through each date between these two dates and then insert the data in to the PaymentsLog table?
I hope this make sense?
September 22, 2015 at 1:26 am
Anyone?
September 22, 2015 at 3:03 am
First of all see the code below.
I have made a number of assumptions.
Because of the constructs you have used I assume that days are important.
So I assumed that I could work with 'exact' dates without the time.
So when comparing I compare the 'current' Day_one with the Day_next and check if there is a row for day_next, if that row does not exist the current row is selected.
This is not the same a you have show with the BETWEEN construction.
07-SEP-2015 is between '07-SEP-2015' and '08-SEP-2015'
08-SEP-2015 is between '07-SEP-2015' and '08-SEP-2015'
if '2015-08-06' between '2015-08-07' and '2015-08-08' PRINT 'yes 2015-08-06'
if '2015-08-07' between '2015-08-07' and '2015-08-08' PRINT 'yes 2015-08-07'
if '2015-08-08' between '2015-08-07' and '2015-08-08' PRINT 'yes 2015-08-08'
if '2015-08-09' between '2015-08-07' and '2015-08-08' PRINT 'yes 2015-08-09'
I assume that 2015-08-08 'should' not be between the two data's.
Because my regional settings differ from yours, I like to use a data format which works in all regional settings. So I like the format YYYY-MM-DD or YYYYMMDD. To convert the date to a date without a time there are a number of possibilities, I like to use the 126 format, because then I can 'chop' of the part I do not like. This can be seconds, minutes, hours, days etc, just get the required length correct.
I do assume that the id does always have a value. And testing on id2 is NULL gives the rows not having a next day.
I do realise the these assumptions make a difference with your code and also the result is in a single set. But Hope you can work with the code and adjust it to your own requirements. (The ON clause might include less fields for example).
Please inform me if this was helpfull.
And please explain a bit more what your goal is.
Ben
--
-- Check if the next day still has a row.
--
;
with
A as (
Select
ID
,DebtorName
,CreditController
,DueDate
,CurrencyCode
,Amount
,convert(datetime,convert(varchar(10),injecteddate,126)) Day_One
,dateadd(dd,1,convert(datetime,convert(varchar(10),injecteddate,126))) Day_Next
FROM PaymentsLog
),
B as (
Select A1.*,a2.id id2, a2.debtorname debtorname2 from A A1 left outer join A a2
on a1.id =a2.id
and
a1.debtorname = a2.debtorname
and
a1.CreditController = a2.CreditController
and
a1.duedate = a2.duedate
and
a1.CurrencyCode = a2.CurrencyCode
and
a1.Amount = a2.amount
and
a1.Day_Next = a2.Day_One
)
select * from B where id2 is NULL
September 23, 2015 at 10:09 am
Hi,
Thanks for the your help so far, it looks good so far but how do I get the the table "DatePeriod" to be part of the query?
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply