April 26, 2017 at 5:37 am
please tell me how to calculate below things:
create table months (monthIndex int);
create table invoice(invoiceId int, month int)
create table lineitems(lid int, invoiceID int, charged_amount decimal(6,2), contracted_rate decimal(6,2))
Insert into months values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);
Insert into invoice values
(1,1),
(2,1),
(3,2),
(4,2),
(5,4),
(6,5),
(7,5),
(8,8),
(9,8),
(10,3),
(11,3),
(12,6),
(13,7),
(14,10),
(15,11),
(16,11);
Insert into lineitems values
(1,1,1000.10,1000.10),
(2,1,1500.20,1200.00),
(3,1,1300.10,1300.10),
(4,2,2100.30,2100.30),
(5,2,1100.10,1000.10),
(6,2,1100.40,1100.40),
(7,3,6000.10,6000.40),
(8,3,1400.10,1400.40),
(9,4,4500.10,4500.10),
(10,5,3300.10,3000.18),
(11,5,2900.10,2900.10),
(12,6,8900.10,8900.10),
(13,6,2200.10,2200.10),
(14,8,3700.10,3700.50),
(15,9,7000.10,7000.60),
(16,10,2200.10,2200.10),
(17,10,2200.10,2200.10),
(18,11,2200.10,2200.10),
(19,12,2200.10,2200.10),
(20,13,2200.10,2200.10),
(21,13,2200.10,2200.10),
(22,14,1100.10,1000.10),
(23,14,1100.40,1100.40),
(24,14,6000.10,6000.40),
(25,15,1400.10,1400.40),
(26,16,4500.10,4500.10),
(27,16,3300.10,3000.18);
Develop a single SQL Query that computes the total invoices amount for each month , and the percentage of change from the previous month. If there are no invoices for the month , the computed value should be null.
the result shown in the table(attached)
April 26, 2017 at 5:44 am
Copied and pasted straight from your coursework assignment! Show us what you've tried so far, and we'll guide you further.
John
April 26, 2017 at 5:56 am
i am trying to learn something here ok not copying or doing any cheating
this is by far i have tired
;with cte (month, totalchargedamount)
As
(
Select c.monthindex ,sum(a.charged_amount)as totalcharged
From lineitems a join invoice b on a. invoiceID = b. invoiceID
right Join months c on b.month = c.monthIndex
group by monthindex)
,cta(month,total)
as
(
select b.month,(a.totalchargedamount- b.totalchargedamount)/100 as total
from cte a join cte b on b.month-1 = a.month)
select a.month,totalchargedamount,total from cte a left join cta b
on a.month = b.month
but i want to learn good easy way than this
April 26, 2017 at 6:42 am
Personally I think you'd be better off using a LEFT JOIN rather than right. It just reads that much better:SELECT *
FROM months m
LEFT JOIN invoice i on m.monthIndex = i.month
LEFT JOIN lineitems li on i.invoiceId = li.invoiceID;
I do also want to ask, if this is a homework question, are you really limited to SQL 2008? 2012 onwards has access to the LAG function, which is clearly something you'd want in this solution, if you can.
If it is 2008, you're on the right path with using a CTE, however, be careful on your join on the previous month. There is no month 0.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 26, 2017 at 6:51 am
Yes, fair enough. But nobody's going to do your homework for you, especially if you don't provide any evidence that you've at least put some effort in.
You've posted in the SQL Server 2008 forum. If that's what you're using, then you can't improve much on what you've done. If you're on SQL Server 2012 or later, you can use the LAG function: LAG(totalchargedamount) OVER (ORDER BY [month]). This avoids the need for self-join Otherwise, you can simplify your effort by replacing everything after the first CTE with this:SELECT
c1.[month]
, c1.totalchargedamount
, 100*(c2.totalchargedamount- c1.totalchargedamount)/c1.totalchargedamount
FROM cte c1
LEFT JOIN cte c2 ON c1.[month] = c2.[month] + 1
In your CTE definition, you only need to specify the column aliases in parentheses after the column name or individually within the SELECT list, but not both.
John
April 26, 2017 at 7:00 am
can you give me example with LAG FUNCTION AND How to use it
April 26, 2017 at 7:04 am
coool_sweet - Wednesday, April 26, 2017 7:00 AMcan you give me example with LAG FUNCTION AND How to use it
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 26, 2017 at 7:04 am
Lag works something like this:
SELECT m.monthIndex as CurrentMonthIndex,
LAG(m.monthIndex) OVER (ORDER BY m.monthIndex) AS PreviousMonthIndex
FROM months m;
You can read about it in full here: https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 26, 2017 at 7:07 am
coool_sweet - Wednesday, April 26, 2017 5:56 AMi am trying to learn something here ok not copying or doing any cheatingthis is by far i have tired
;with cte (month, totalchargedamount)
As
(
Select c.monthindex ,sum(a.charged_amount)as totalcharged
From lineitems a join invoice b on a. invoiceID = b. invoiceID
right Join months c on b.month = c.monthIndex
group by monthindex)
,cta(month,total)
as
(select b.month,(a.totalchargedamount- b.totalchargedamount)/100 as total
from cte a join cte b on b.month-1 = a.month)select a.month,totalchargedamount,total from cte a left join cta b
on a.month = b.month
but i want to learn good easy way than this
There is a simpler way. You just need to reduce one step. This is a modification of your query. Note all the changes, including:
1. Relevant table aliases
2. Proper spacing
3. Use of upper and lower case to format the code.
4. Removal of the semicolon before the cte
5. Correct percentage formula
The first four, won't change the result, but they will improve readability and will make it easier to maintain.WITH cte (month, totalchargedamount) AS(
SELECT m.monthindex,
SUM(li.charged_amount) AS totalcharged
FROM lineitems li
JOIN invoice i ON li.invoiceID = i.invoiceID
RIGHT
JOIN months m ON i.month = m.monthIndex
GROUP BY m.monthindex
)
SELECT cur.month,
cur.totalchargedamount,
(cur.totalchargedamount- pre.totalchargedamount) / cur.totalchargedamount * 100 AS total
FROM cte cur
LEFT
JOIN cte pre ON cur.month = pre.month + 1;
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply