Transposing the data and then converting monthly data to daily data

  • 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 ?

  • ;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

  • 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

  • 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.

  • 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

  • Ok Thanks. Actually i am on SQL Server 2005. i was not able to locate 2005 forum.

  • In this thread:

    https://www.sqlservercentral.com/forums/topic/count-number-of-days-between-two-dates-excluding-weekends-for-a-particular-month#post-3687334

    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

  • 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.

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • >> 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 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply