question

flyfishing avatar image
flyfishing asked

Comparing Rows

Hi My first SQL query on this forum! I have a table with following fields 1. Client ID(Unique) 5000 records 2. Date (24 row dates split across 12 from last year and 12 from current year for each Client) 3. Amount (24 rows for each Client) Objective is to compare the last year 12 monthly values with current years' monthly values in a new column Need help!
row-comparison
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi @flyfishing Welcome to askssc! Can you script out the CREATE TABLE statement so we can get an idea of the structure? Can you also post the SQL that you've attempted so far and tell us what makes you think it's wrong? A few rows of sample data would help us to help you too.
0 Likes 0 ·
Hi Here are the three create tables I am trying to use The due_date will have up to 12 monthly values of last year and/or upto 12 values for current year. I want to compare the amount of two years side by side as data is in rows coming from 3 tables joined as below tables 'a' and 'c' are joined on t_no as left outer join tables 'a' and 'd' are joined on t_no as left outer join CREATE TABLE [dbo].[a]( [comp] [char](3) NOT NULL, [t_no] [int] NOT NULL, [dd_no] [int] NOT NULL, [dd_year] [smallint] NULL CREATE TABLE [dbo].[c]( [comp] [char](3) NOT NULL, [t_no] [int] NOT NULL, [dd_no] [int] NOT NULL, [due_date] [datetime] NULL, [amount] [float] NULL, ) ON [PRIMARY] CREATE TABLE [dbo].[d]( [comp] [char](3) NOT NULL, [t_no] [int] NOT NULL, [source_cd] [char](6) NOT NULL
0 Likes 0 ·
GPO avatar image
GPO answered
In the absence of more complete information, see how this goes: DECLARE @start_first_period as date = '20130701'; DECLARE @end_first_period as date = '20140701'; DECLARE @start_next_period as date = dateadd(yy,1,@start_first_period); DECLARE @end_next_period as date = dateadd(yy,1,@end_first_period); WITH first_period as ( SELECT trs.client_id ,datename(mm,trs.transaction_date) as transaction_month ,sum(trs.amount) as total_amount FROM dbo.client_transactions trs WHERE trs.transaction_date >= @start_first_period and trs.transaction_date < @end_first_period GROUP BY trs.client_id ,datename(mm,trs.transaction_date) ) ,next_period as ( SELECT trs.client_id ,datename(mm,trs.transaction_date) as transaction_month ,sum(trs.amount) as total_amount FROM dbo.client_transactions trs WHERE trs.transaction_date >= @start_next_period and trs.transaction_date < @end_next_period GROUP BY trs.client_id ,datename(mm,trs.transaction_date) ) SELECT isnull(f.client_id,n.client_id) as client_id ,isnull(f.transaction_month,n.transaction_month) as transaction_month ,f.total_amount as first_total_amount ,n.total_amount as next_total_amount FROM first_period f --the FULL JOIN will cover your bases if you have data for a client in one year but not the other. FULL JOIN next_period n ON f.client_id = n.client_id and f.transaction_month = n.transaction_month ;
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

flyfishing avatar image
flyfishing answered
Excellent - it worked- Thanks a lot!
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.