SQLTeam.com | Weblogs | Forums

Ask for a query for report


#1

Hello,
Please help me to query the data and make the format for report, the data in the table Prod_Sales is like this:

Customer_Name Address City State Zip ProductName ProductType Month_Year Month_Total
CustomerA 100MainSt NewYork NY 10006 Table T11 Jan-18 100
CustomerA 100MainSt NewYork NY 10006 Chair C11 Feb-18 200
CustomerA 100MainSt NewYork NY 10006 Table T11 Apr-18 300
CustomerB 200ChurchSt LA CA 98765 Board B11 May-18 100
CustomerB 200ChurchSt LA CA 98754 Board B11 Jul-18 200
CustomerC 300FarmRd York NJ 771 Chair C11 May-18 500

The output should display all customer info with each month amount horizontally and the YTD_Total at the end. Also, display monthly total for all product on each month vertically. The output should be like this:

Customer Address City State Zip ProductName ProductType Jan Feb Mar Apr May June YTD_Total
CustomerA 100MainSt NewYork NY 10006 Table T11 100 300 400
CustomerA 100MainSt NewYork NY 10006 Chair C11 200 200
CustomerB 200ChurchSt LA CA 98765 Board B11 100 200 300
CustomerC 300CentralRd JerseyCity NJ 771 Chair C11 500 500
Total_Monthly_Sold 100 200 900 200 1400

Really appreciate for any helps.

Thanks,
Lw1990


#2

always provide sample data as follows

create table #months(month_string varchar(50), month_int int, 
month_year varchar(50))

insert into #months
select 'January', 1	,'Jan-18' union 
select 'February', 2	,'Feb-18' union 
select 'March', 3	,'Mar-18' union 
select 'April', 4	,'Apr-18' union 
select 'May', 5	,'May-18' union 
select 'June', 6	,'Jun-18' union 
select 'July', 7	,'Jul-18' union 
select 'August', 8	,'Aug-18' union 
select 'September', 9	,'Sep-18' union 
select 'October', 10	,'Oct-18' union 
select 'November', 11	,'Nov-18' union 
select 'December', 12	,'Dec-18' 

--select * From #months order by month_int


create table #lw1990(customerName varchar(100), Address varchar(100), City varchar(100),  
                     State varchar(100), zip int, ProductName varchar(100),
                     ProductType varchar(100), Month_year varchar(100), Month_Total int)


insert into #lw1990
select 'CustomerA','100MainSt', 'NewYork','NY',10006, 'Table','T11','Jan-18',	100 union
select 'CustomerA','100MainSt', 'NewYork','NY',10006, 'Chair','C11','Feb-18',	200 union
select 'CustomerA','100MainSt', 'NewYork','NY',10006, 'Table','T11','Apr-18',	300 union
select 'CustomerB','200ChurchSt', 'LA',  'CA',	98765, 'Board','B11','May-18',	100 union
select 'CustomerB','200ChurchSt', 'LA',  'CA',	98754, 'Board','B11','Jul-18',	200 union
select 'CustomerC','300FarmRd', 'York	','NJ',771, 'Chair','	C11','May-18', 200    

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
	@Piv as NVARCHAR(MAX)

SELECT  @cols = LEFT( b.Piv, LEN( b.Piv ) - 1 )
FROM (  SELECT  N'[' + Month_year + '], '
        FROM (  SELECT  DISTINCT m.Month_year, month_int
                  FROM #months m 
				   left join #lw1990 r on r.Month_year = m.month_year 
				  ) a
        ORDER BY a.month_int asc
        FOR XML PATH ( '' ) ) b ( Piv );

print @cols

set @query = 'SELECT customerName, 
						Address, 
						City,  
						State, 
						zip, 
						ProductName,
						ProductType, ' + @cols + '
                       
 from 
            (
				select  customerName, 
						Address, 
						City,  
						State, 
						zip, 
						ProductName,
						ProductType,
						Month_year,
						Month_Total                  
					from #lw1990
           ) x
            pivot 
            (
                 sum(Month_Total)
                for Month_year in (' + @cols + ')
            ) p '

select (@query)

exec (@query)

drop table #lw1990

drop table #months

#3

Thank you yosiasz for quick reply, I tested the query. It returns the sales records of same customer on different months. But how can I get the Year-To-Date total YTD_Total shown on the right and each month total Total_Monthly_Sold at the bottom of each month on the result?

Thanks again yosiasz,

lw1990


#4

hi

In Create data
I have created an extra column rn for each month

Create Data Script
use tempdb
go 

drop table #data 
go 

create table #data 
(
rn int,
Customer_Name	varchar(100),
Address	varchar(100),
City	varchar(100),
State	varchar(100),
Zip	int ,
ProductName	varchar(100),
ProductType	varchar(100),
Month_Year	varchar(100),
Month_Total int   
)
go 


insert into #data select 1,'CustomerA','100MainSt','NewYork','NY',10006,'Table','T11','Jan-18',100
insert into #data select 2,'CustomerA','100MainSt','NewYork','NY',10006,'Chair','C11','Feb-18',200
insert into #data select 3,'CustomerA','100MainSt','NewYork','NY',10006,'Table','T11','Apr-18',300
insert into #data select 4,'CustomerB','200ChurchSt','LA','CA',98765,'Board','B11','May-18',100
insert into #data select 5,'CustomerB','200ChurchSt','LA','CA',98754,'Board','B11','Jul-18',200
insert into #data select 4,'CustomerC','300FarmRd','York','NJ',771,'Chair','C11','May-18',500
go
SQL
SELECT a.rn, 
       a.customer_name, 
       a.address, 
       a.city, 
       a.state, 
       a.zip, 
       a.productname, 
       a.producttype, 
       a.month_year, 
       a.month_total, 
       b.total_monthly_sold, 
       c.ytd_total 
FROM   (SELECT * 
        FROM   #data) a 
       JOIN (SELECT month_year, 
                    Sum(month_total) AS Total_Monthly_Sold 
             FROM   #data 
             GROUP  BY month_year) b 
         ON a.month_year = b.month_year 
       JOIN (SELECT Sum(month_total) AS YTD_Total 
             FROM   #data) c 
         ON 1 = 1 
ORDER  BY rn
Result


#5

are you using SSRS for your reporting needs?