March 27, 2018 at 8:52 am
Hi There,
Below is a sample table and some data within that table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RentRegularPayments_Future](
[AccountId] [int] NOT NULL,
[ChargesFromDate] [varchar](23) NOT NULL,
[ChargesToDate] [varchar](23) NOT NULL,
[Collections] [int] NULL,
[TotalAmount] [decimal](38, 2) NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[RentRegularPayments_Future] ADD [Frequency] [varchar](213) NOT NULL
ALTER TABLE [dbo].[RentRegularPayments_Future] ADD [DayOfWeek] [int] NULL
ALTER TABLE [dbo].[RentRegularPayments_Future] ADD [DayOfMonth] [int] NULL
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[RentRegularPayments_Future] ([AccountId], [ChargesFromDate], [ChargesToDate], [Collections], [TotalAmount], [Frequency], [DayOfWeek], [DayOfMonth]) VALUES (12826, N'2018-04-02 00:00:00.000', N'2019-03-31 00:00:00.000', 12, CAST(576.00 AS Decimal(38, 2)), N'1 Monthly', NULL, 1)
INSERT [dbo].[RentRegularPayments_Future] ([AccountId], [ChargesFromDate], [ChargesToDate], [Collections], [TotalAmount], [Frequency], [DayOfWeek], [DayOfMonth]) VALUES (12856, N'2018-04-02 00:00:00.000', N'2019-03-31 00:00:00.000', 26, CAST(377.28 AS Decimal(38, 2)), N'2 Weekly', 4, NULL)
INSERT [dbo].[RentRegularPayments_Future] ([AccountId], [ChargesFromDate], [ChargesToDate], [Collections], [TotalAmount], [Frequency], [DayOfWeek], [DayOfMonth]) VALUES (13038, N'2018-04-02 00:00:00.000', N'2019-03-31 00:00:00.000', 13, CAST(469.44 AS Decimal(38, 2)), N'4 Weekly', 4, NULL)
For example 0
WHERE Frequency = 1 Monthly
Create 12 rows (based on the figure in COLLECTIONS).
Where the PaymentDates returned based on the CHARGESFROMDATE of 02/04/18.
As the DAYOFMONTH = 1 then the first Payment Date would be the 01/05/18 and then all other 11 payments would be on the 1st of the month
WHERE Frequency = 2 Weekly
Create 26 rows (based on the figure in COLLECTIONS).
Where the PaymentDates returned based on the CHARGESFROMDATE of 02/04/18.
As the DAYOFWEEK= 4 (4 is Thursday...1 would be Monday) then the first payment date would be the first thursday after the 02/04/18.
then subsquent rows would be every two weeks after the first payment date.
WHERE Frequency = 4 Weekly
Create 13 rows (based on the figure in COLLECTIONS).
Where the PaymentDates returned based on the CHARGESFROMDATE of 02/04/18.
As the DAYOFWEEK= 4 (4 is Thursday...1 would be Monday) then the first payment date would be the first thursday after the 02/04/18.
then subsquent rows would be every four weeks after the first payment date.
Thanks
March 27, 2018 at 9:53 am
First order of business... why are you using varchar(23) to store dates? Given that you are using ANSI PADDING turned on that only affects those columns, that just isn't making any sense at all. Besides, any practical method of generating dates would be one heck of a lot easier if those columns were dates (or datetimes) instead of varchar(23). You'll just end up having to CONVERT them to dates to make any use out of the values, so why store them in a way that can't be effectively used?
Steve (aka sgmunson) š š š
Rent Servers for Income (picks and shovels strategy)
March 27, 2018 at 11:51 am
sgmunson - Tuesday, March 27, 2018 9:53 AMFirst order of business... why are you using varchar(23) to store dates? Given that you are using ANSI PADDING turned on that only affects those columns, that just isn't making any sense at all. Besides, any practical method of generating dates would be one heck of a lot easier if those columns were dates (or datetimes) instead of varchar(23). You'll just end up having to CONVERT them to dates to make any use out of the values, so why store them in a way that can't be effectively used?
i think it was i did a select into....and hardcoded the date as text. They would be stored as dates normally.
March 27, 2018 at 12:31 pm
TSQL Tryer - Tuesday, March 27, 2018 11:51 AMsgmunson - Tuesday, March 27, 2018 9:53 AMFirst order of business... why are you using varchar(23) to store dates? Given that you are using ANSI PADDING turned on that only affects those columns, that just isn't making any sense at all. Besides, any practical method of generating dates would be one heck of a lot easier if those columns were dates (or datetimes) instead of varchar(23). You'll just end up having to CONVERT them to dates to make any use out of the values, so why store them in a way that can't be effectively used?i think it was i did a select into....and hardcoded the date as text. They would be stored as dates normally.
Actually, on a SELECT INTO, just specifying a datetime as text is not going to result in a datetime value unless you either CAST or CONVERT it first, or you union or union all it with a previous rowset that has those columns set up as datetime values and not just text.
Steve (aka sgmunson) š š š
Rent Servers for Income (picks and shovels strategy)
April 5, 2021 at 9:36 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply