May 11, 2018 at 2:22 pm
Should create a dynamic pivot table to report products sold monthly by company group.
* There are several products and each company group might have a different combination of them.
* The product status are Active and Cancelled.
* The Active products are counted using the date where they were billed (dbilldate) and the cancelled should be counted using dtContractCancelledDate.
* There are some cases where the company group does not sell or cancel a product during the month so all the column should reflect that (look on Prod2 Cancelled).
A calculation column should exist to calculate the net per product (Prod Active – Prod Cancelled).
We need help
• Creating different calculation to count the product active and cancelled using the corresponding dates (dbilled and dtcancelledbilled)
• Creating a column with 0s when there are not sold/cancelled products
• Creating the net columns for each product
• Creating the subtotals at the end of each column
This is the result desired:
Pd1Actv Pd1Cancd Pd1Net Prd2Actv Prd2Cancd Prd2Net Total
Comp1 6 5 1 15 0 15 16
Comp2 20 6 14 39 0 39 53
Comp3 63 14 49 82 0 82 131
Total 89 25 64 136 0 136 200
This is the main table from where data is extracted
'SELECT [iCompId],[sContractStatusCode],[sContractStatusDesc],[dBillDate] ,dtContractCancelledBilled FROM [Contract_Header]'
This is what I have until now:
DECLARE
@cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@Compgroupnumber as varchar(20),
@startdate as date,
@enddate as date
set @startdate=cast(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) as
date)
set @enddate=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
set @sCompGroupNumber ='DG10000174'
select @cols =
STUFF((SELECT distinct ',' + QUOTENAME(h.sProductCode+'
'+sContractStatusDesc)
from [Contract_Header] h
inner join [Comp_Header] d on d.iid=h.icompId
inner join [Comp_CompGroupLink] dgl on d.iId=dgl.CompId
inner join [Comp_DealerGroups] dg on dgl.iCompGroupId=dg.iId
where (h.sContractStatusCode='A' or h.sContractStatusCode='C') and
(h.dBillDate between @startdate and @enddate) and
sCompGroupNumber='DG10000174' and
h.sProductCode in
(
select distinct t.sProductCode
from [Contract_Header] t
inner join [Comp_Header] d on d.iid=t.iDealerId
inner join [Comp_CompGroupLink] dgl on d.iId=dgl.iCompId
inner join [Comp_CompGroups] dg on dgl.iDealerGroupId=dg.iId
where (t.sContractStatusCode='A' or t.sContractStatusCode='C') and
(t.dBillDate between @startdate and @enddate) and
sCompGroupNumber='DG10000174'
)FOR XML PATH('')) ,1,1,'')
set @query=
'SELECT sCompName,' + @cols + ' , +Total
from
(
select d.sCompName,
col = c.sProductCode+'' ''+c.sContractStatusDesc,c.sContractStatusCode,
from [Contract_Header] c
inner join [Comp_Header] d on d.iid=c.iDealerId
inner join [Comp_CompGroupLink] dgl on d.iId=dgl.iCompId
inner join [Comp_CompGroups] dg on dgl.iCompGroupId=dg.iId
where (dBillDate between '''+CAST(@startdate AS varchar)+''' and
'''+CAST(@enddate AS VARCHAR)+''') and
sCompGroupNumber='''+'DG10000174'+''' and
(c.sContractStatusCode='''+'A'+''' or
c.sContractStatusCode='''+'C'+''' ) and
sproductcode in
(select distinct sProductCode
from [Contract_Header] t
inner join [Comp_Header] d on d.iid=t.iCompId
inner join [Comp_CompGroupLink] dgl on d.iId=dgl.iCompId
inner join [Comp_CompGroups] dg on dgl.iCompGroupId=dg.iId
where (dBillDate between '''+CAST(@startdate AS varchar)+'''
and '''+CAST(@enddate AS VARCHAR)+''') and
(t.sContractStatusCode='''+'A'+''' or
t.sContractStatusCode='''+'C'+''' ) and
sCompGroupNumber='''+'DG10000174'+'''
)
) as DataSource
pivot
(
Count(sContractStatusCode)
for col in (' + @cols + ')
) p order by sCompName'
execute sp_executesql @query
This is the result of the query above
Pd1Actv Pd1Cancd Pd2Actv Pd3Actv
Comp 1 59 0 59 118
Comp 2 26 1 25 65
there are missing the columns when there are not products cancelled for Pd2 and Pd3, the net columns (pdAct-PdCanc), sub total and totals..
Thanks in advance for all the help...
mary
May 11, 2018 at 2:39 pm
To really help we will need the DDL (CREATE TABLE statement) for the table(s) involved, sample data (not production data) as INSERT VALUES statements for the table(s) involved, and based on the provided sample data what the expected results when done.
I can tell you that just your date calculations are wrong as you are using the DATE data type and it does not have a time component. In addition, when doing range extracts on dates you are best using a semi-open range. That is one that is closed at the beginning of the range and open on the end of the range. For example: DateBilled >= '2018-04-01' and DateBilled < '2018-05-01'. This will select all rows of data where the DateBilled is in April 2018.
Looking at the code you posted, unfortunately, is also difficult as it is unformatted. And without something to rung the code against, there really is no way to know what it is actually doing without guess work on our part.
Please understand we are volunteers giving of our own time to help, but to do so we need your help. We can't see what you see which means we rely on you to provide as much information as possible to work the problem.
May 14, 2018 at 10:22 am
Lynn:
Thanks very much for taking time to read and reply my question. It is appreciated. We cannot make insert values, just see the data and query them to create reports. Attached are samples of the data for each table, tables structures and relationships.
Thanks again for your time and wish to help us,
Mary
May 14, 2018 at 11:24 am
msramos - Monday, May 14, 2018 10:22 AMLynn:Thanks very much for taking time to read and reply my question. It is appreciated. We cannot make insert values, just see the data and query them to create reports. Attached are samples of the data for each table, tables structures and relationships.
Thanks again for your time and wish to help us,
Mary
What do you mean you can't make insert values? Like Lynn said, we're volunteers here, but we can't work magic. We need you to help us help you. There's a link in my signature on how to provide DDL and INSERT statements. The data doesn't need to be real, or can be made up, or psuedonymised.
We also really need you to format that code of yours; code that is all left alligned is very difficult to read, plus the fact it's not in a fixed sized font. Again, how to post code is detailed in my signature.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 14, 2018 at 1:51 pm
Thanks Thom for your time and guidance. Here is the code to create the tables and attached is the code to insert the data in each table. When I refer that we cannot insert data is becuase the information is entered by other companies, we can only read the information to create reportsCreate Table Contract_Header
(
iId int,
iDealerId int,
sContractNumber varchar(20),
sContractStatusCode varchar(1),
sContractStatusDesc nvarchar(20),
dBillDate date,
dtContractCancelledBilled date,
sProductCode varchar(4)
)
Create Table Dealer_Header
(
iId int
)
Create Table Dealer_DealerGroups
(
iId int,
sDealerGroupNumber varchar(20)
)
Create Table Dealer_DealerGroupLink
(iDealerId int,
iDealerGroupId int
)
Thanks again for all your help 🙂
Mary
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply