February 20, 2020 at 12:58 pm
I have a Budget Table in following format
Account -- Year -- Month1- Month2- Month3- Month4- Month5- Month6- Month7- Month8- Month9- Month10- Month11- Month12
500100 - 2020 - 100 - 200 - 300- 300- 300- 300- 300- 300- 300- 300- 300- 300
I would like to convert the data like below
Account Period Amount
500100 - 1/1/2020 - $100
500100 - 2/1/2020- $200
500100 - 3/1/2020- $200
and so on.
After this i would like to convert the monthly data which i got from transposing the data into daily data.
I have a table which lists all the holidays , saturdays and sundays. Daily data should put zero for holidays , saturdays and sundays.
Is this possible ?
February 20, 2020 at 1:46 pm
;with
unpiv_cte as (
select
500100 Account, 2020 [Year],
100 Month1, 200 Month2, 300 Month3, 300 Month4,
300 Month5, 300 Month6, 300 Month7, 300 Month8,
300 Month9, 300 Month10, 300 Month11, 300 Month12)
select
Account,
datefromparts([Year], substring([Month], 6, 2), 1) Mo,
Months
from
unpiv_cte
unpivot
(Months for [Month] in
(Month1, Month2, Month3, Month4, Month5, Month6,
Month7, Month8, Month9, Month10, Month11, Month12)) unpvt;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
February 20, 2020 at 1:48 pm
To split into days do you want to allocate evenly across all days or exclude weekends and holidays?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
February 20, 2020 at 1:51 pm
allocation should exclude weekends and hoildays.
If Jan Budget is $200 and working days in Jan is 20 , So daily budget for each working day should be $10 and for weekend and holidays it should be zero.
Thanks for your help.
February 20, 2020 at 2:03 pm
You're using 2008R2 and I don't think the daterange function I would use here goes back that far. There was a similar question asked a while back and another SSC member helped solve. I'll see if I can find it.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
February 20, 2020 at 2:04 pm
Ok Thanks. Actually i am on SQL Server 2005. i was not able to locate 2005 forum.
February 20, 2020 at 2:13 pm
In this thread:
The OP apparently was provided something that worked for SQL Server 2000. It doesn't look like it was posted tho.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
February 20, 2020 at 3:52 pm
Don't think you can use UNPIVOT then either. Not sure if this will work, because 2019 doesn't allow a compatibility level to 2005. Maybe this works?
SELECT AccountID
, AcctYear
, ca.MonthNumber
, ca.Amt
, ca.WorkDays
, DailyAmt = CAST(ca.Amt * 1.0/ca.Workdays AS DECIMAL(5,2))
FROM UnpivotMe
CROSS APPLY (VALUES (1,[Jan],20),(2,[Feb],18),(3,[Mar],21),(4,[Apr],19)
,(5,[May],18),(6,[Jun],20),(7,[Jul],19),(8,[Aug],20)
,(9,[Sep],19),(10,[Oct],20),(11,[Nov],17),(12,[Decm],18)) ca(MonthNumber, Amt, Workdays);
I cheated on the Days In Month question, but that's in Aaron's example. You could just filter the Calendar table for weekdays and not holiday, then do a count by month. Then divide.
February 21, 2020 at 8:42 pm
I only filled in holidays / weekends for Jan, but this code should give you what you requested once you substitute in your own nonworkdays table.
;WITH base_data AS (
SELECT
500100 Account, 2020 Year,
100 Month1, 200 Month2, 300 Month3, 400 Month4,
500 Month5, 600 Month6, 700 Month7, 800 Month8,
900 Month9, 1000 Month10, 1100 Month11, 1200 Month12
),
nonworkdays AS (
SELECT *
FROM ( VALUES(CAST('20200101' AS date)),
('20200104'), ('20200105'), ('20200111'), ('20200112'),
('20200118'), ('20200119'), ('20200125'), ('20200126')
) AS nowworkdays_detail(date)
),
cte_tally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS number
FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2
)
SELECT
ca2.Account, Daily_Date,
CASE WHEN nwd.date IS NULL THEN CAST(Amount * 1.0 / (DATEDIFF(DAY, Period,
DATEADD(MONTH, 1, Period)) - nonworkday_count) AS decimal(9, 2))
ELSE 0 END AS Daily_Amount
FROM base_data
CROSS APPLY (
SELECT CAST(CAST(Year * 10000 + 0101 AS char(8)) AS date) AS year_start
) AS ca1
CROSS APPLY ( VALUES
(Account, DATEADD(MONTH, 0, year_start), Month1),
(Account, DATEADD(MONTH, 1, year_start), Month2),
(Account, DATEADD(MONTH, 2, year_start), Month3),
(Account, DATEADD(MONTH, 3, year_start), Month4),
(Account, DATEADD(MONTH, 4, year_start), Month5),
(Account, DATEADD(MONTH, 5, year_start), Month6),
(Account, DATEADD(MONTH, 6, year_start), Month7),
(Account, DATEADD(MONTH, 7, year_start), Month8),
(Account, DATEADD(MONTH, 8, year_start), Month9),
(Account, DATEADD(MONTH, 9, year_start), Month10),
(Account, DATEADD(MONTH,10, year_start), Month11),
(Account, DATEADD(MONTH,11, year_start), Month12)
) AS ca2(Account, Period, Amount)
CROSS APPLY (
SELECT COUNT(*) AS nonworkday_count
FROM nonworkdays
WHERE date >= Period AND date < DATEADD(MONTH, 1, Period)
) AS nonworkday_counts
INNER JOIN cte_tally100 t ON t.number BETWEEN 0 AND DATEDIFF(DAY, Period, DATEADD(MONTH, 1, Period)) - 1
CROSS APPLY (
SELECT DATEADD(DAY, t.number, ca2.Period) AS Daily_Date
) AS ca3
LEFT OUTER JOIN nonworkdays nwd ON nwd.date = Daily_Date
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 21, 2020 at 10:38 pm
Corrected the code a bit, and added any leftover amount to the first work day of the month.
;WITH base_data AS (
SELECT
500100 Account, 2020 Year,
100 Month1, 200 Month2, 300 Month3, 400 Month4,
500 Month5, 600 Month6, 700 Month7, 800 Month8,
900 Month9, 1000 Month10, 1100 Month11, 1200 Month12
),
nonworkdays AS (
SELECT *
FROM ( VALUES(CAST('20200101' AS date)),
('20200104'), ('20200105'), ('20200111'), ('20200112'),
('20200118'), ('20200119'), ('20200125'), ('20200126')
) AS nowworkdays_detail(date)
),
cte_tally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS number
FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2
)
SELECT
ca2.Account, Daily_Date,
CASE WHEN nwd.date IS NULL THEN Daily_Amount +
CASE WHEN DAY(Daily_Date) = first_work_day THEN monthly_leftover_amount ELSE 0 END
ELSE 0 END AS Daily_Amount
FROM base_data
CROSS APPLY (
SELECT CAST(CAST(Year * 10000 + 0101 AS char(8)) AS date) AS year_start
) AS ca1
CROSS APPLY ( VALUES
(Account, DATEADD(MONTH, 0, year_start), Month1, 31),
(Account, DATEADD(MONTH, 1, year_start), Month2,
DATEDIFF(DAY, DATEADD(MONTH, 0, year_start), DATEADD(MONTH, 1, year_start)) - 1),
(Account, DATEADD(MONTH, 2, year_start), Month1, 31),
(Account, DATEADD(MONTH, 3, year_start), Month1, 30),
(Account, DATEADD(MONTH, 4, year_start), Month1, 31),
(Account, DATEADD(MONTH, 5, year_start), Month1, 30),
(Account, DATEADD(MONTH, 6, year_start), Month1, 31),
(Account, DATEADD(MONTH, 7, year_start), Month1, 31),
(Account, DATEADD(MONTH, 8, year_start), Month1, 30),
(Account, DATEADD(MONTH, 9, year_start), Month1, 31),
(Account, DATEADD(MONTH,10, year_start), Month1, 30),
(Account, DATEADD(MONTH,11, year_start), Month1, 31)
) AS ca2(Account, Period, Amount, days_in_month)
CROSS APPLY (
SELECT
COUNT(*) AS nonworkday_count,
DATEDIFF(DAY, Period, DATEADD(MONTH, 1, Period)) - COUNT(*) AS workday_count,
CAST(ROUND(Amount * 1.0 / (DATEDIFF(DAY, Period, DATEADD(MONTH, 1, Period)) -
COUNT(*)), 2, 1) AS decimal(9, 2)) AS Daily_Amount,
MIN(date) AS min_nonwork_date, MAX(date) AS max_nonwork_date
FROM nonworkdays
WHERE date >= Period AND date < DATEADD(DAY, days_in_month, Period)
) AS nonworkday_counts
CROSS APPLY (
SELECT Amount - (workday_count * Daily_Amount) AS monthly_leftover_amount,
CASE WHEN min_nonwork_date IS NULL OR DAY(min_nonwork_date) > 1 THEN 1
ELSE (SELECT TOP (1) t.number + 1
FROM cte_tally100 t
WHERE t.number BETWEEN 1 AND days_in_month AND
NOT EXISTS(SELECT 1 FROM nonworkdays nwd
WHERE nwd.date = DATEADD(DAY, t.number, Period))
ORDER BY t.number)
END AS first_work_day
) AS ca4
INNER JOIN cte_tally100 t ON t.number BETWEEN 0 AND days_in_month - 1
CROSS APPLY (
SELECT DATEADD(DAY, t.number, ca2.Period) AS Daily_Date
) AS ca5
LEFT OUTER JOIN nonworkdays nwd ON nwd.date = Daily_Date
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 24, 2020 at 6:19 pm
>> I have a Budget Table in the following format <<
This is very nice of you. Unfortunately, we have to use SQL, so we need DDL instead of a narrative and a picture. If you're working at a shop that makes you program for crap like this, it's time to update your resume and quit.
Unfortunately, your picture is completely wrong and non-relational! A month is a unit of temporal measure. It is not an attribute! What you've done is put a spreadsheet into a useless non-SQL narrative.
CREATE TABLE Budget
(account_nbr CHAR(6) NOT NULL,
budget_month CHAR (10) NOT NULL
CHECK (budget_month LIKE (‘[12][0-9][0-9][0-9][0-9]-[01][0-9]-00’),
budget_amt DECIMAL (12,2) NOT NULL
CHECK(budget_amt <= 0.00)
PRIMARY KEY (account_nbr, budget_month)
);
Let me explain this. An identifier, such as an account number, cannot be a numeric value. Numeric values are used for computations. The budget month is a little harder to explain because I happen to like to use the MySQL convention where each month within the year, is shown as "yyyy-mm-00" in a string that references a monthly lookup table that gives the start to the end of month range. You don't seem to understand that by definition, not as an option, a table must have a key. It's really hard to put a key in a narrative instead of proper DDL is in it?
I also see that you don't know that the ANSI/ISO standard SQL requires that dates be represented using the ISO 8601 format, "yyyy-mm-dd" which is universally accepted on ISO standards. You're kind of like a mechanical engineer who doesn't know the metric system, aren't you? You also don't seem to know that putting a $ or other punctuation in numeric displays is COBOL and has never been part of SQL. Yes, SQL Server in its early days, provided this for the money and the date data types. This is been totally rejected and simply shows an incredible amount of non-relational mindset.
>> After this I would like to convert the monthly data which I got from transposing the data into daily data. <<
Yes, it is possible. But we need really hard rules. First of all, months are not the same number of days (is this a leap year or not?), Business days are all over the place. Do weekends count? Also, what is a weekend? If I'm in a Moslem country, they usually only count Friday as a non-work day. In short, your specification is too vague to be implemented directly. You're going to need to set up a calendar table and do a join on it. No single simple query will do it. And now you've learned a great truth that most of the work in SQL is done in the DDL and not in the DML. You also don't seem to know that putting a $ or other punctuation in numeric displays is COBOL and has never been part of SQL. Yes, SQL Server in its early days, provided this for the money and the date data types. This is been totally rejected and simply shows an incredible amount of nonrelational mindset.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply