Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Cross tab Report Query
Posted on 2015-02-19
Hello All,
I have the below code.Iam trying to put together a cross tab report with the below query for
columns NSN,SPEEDEX_ID and row will be each month total.
Please help me with modifying the below query so that I can integrate this into the cross tab report.
Should I calculate each month's total one by one or is there a way I can modify the query to use pivot to pull the data like pivot.Please help with pivot if that could be an option
I have the below code.Iam trying to put together a cross tab report with the below query for
columns NSN,SPEEDEX_ID and row will be each month total.
Please help me with modifying the below query so that I can integrate this into the cross tab report.
Should I calculate each month's total one by one or is there a way I can modify the query to use pivot to pull the data like pivot.Please help with pivot if that could be an option
WITH DUAL as
(
select
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -24, 0) ,101) as sdate,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -23, 0) ,101) as sdate2,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -22, 0) ,101) as sdate3,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -21, 0) ,101) as sdate4,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -20, 0) ,101) as sdate5,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -19, 0) ,101) as sdate6,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -18, 0) ,101) as sdate7,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -17, 0) ,101) as sdate8,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -16, 0) ,101) as sdate9,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -15, 0) ,101) as sdate10,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -14, 0) ,101) as sdate11,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -13, 0) ,101) as sdate12,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -12, 0) ,101) as sdate13,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -11, 0) ,101) as sdate14,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -10, 0) ,101) as sdate15,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -9, 0) ,101) as sdate16,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -8, 0) ,101) as sdate17,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -7, 0) ,101) as sdate18,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -6, 0) ,101) as sdate19,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -5, 0) ,101) as sdate20,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -4, 0) ,101) as sdate21,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -3, 0) ,101) as sdate22,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -2, 0) ,101) as sdate23,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -1, 0) ,101) as sdate24,
CONVERT(varchar(10),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),101) as edate
),
--select * from DUAL
ALL_NSN_INFO AS (
select
nsn,
speedex_id,
sum(qty_required) as total_qty
from d_item1, dual
where date_create >= sdate and date_create <= edate
and status between '01' and '79'
and program in ('770','772','773','774','775','776','778','779')
group by nsn, speedex_id
),
--select * from ALL_NSN_INFO order by nsn, speedex_id
MONTH_1 AS (
select
nsn,
sum(qty_required) as month1_total
from d_item1, dual
where date_create >= sdate and date_create < sdate2
and status between '01' and '79'
and program in ('770','772','773','774','775','776','778','779')
group by nsn
),
--select * from MONTH_1 order by nsn
MONTH_2 AS (
select
nsn,
sum(qty_required) as month2_total
from d_item1, dual
where date_create >= sdate2 and date_create < sdate3
and status between '01' and '79'
and program in ('770','772','773','774','775','776','778','779')
group by nsn
)
--select * from MONTH_2 order by nsn
select al.nsn
, al.speedex_id
, m1.month1_total
,m2.month2_total
, al.total_qty AS TOTAL
,(al.total_qty/24) AS DEMAND
from ALL_NSN_INFO al
left outer join MONTH_1 m1 on al.nsn = m1.nsn
left outer join MONTH_2 m2 on al.nsn = m2.nsn
order by al.nsn
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
4
3
12 Comments
Active today
Are you building a report in Crystal?
Does that query produce the data you desire?
If so you should be able to use it as a COMMAND datasource in Crystal to produce a cross tab.
There may be an easier way to do it in Crystal. What data are you trying to show?
mlmcc
Does that query produce the data you desire?
If so you should be able to use it as a COMMAND datasource in Crystal to produce a cross tab.
There may be an easier way to do it in Crystal. What data are you trying to show?
mlmcc
I've requested that this question be deleted for the following reason:
Re-submitting another question
Re-submitting another question
The screen shot attached is the data from sql.
Iam trying to display this in a crystal report.I guess a cross tab will help.
I have generated data for month1, month2, but it needs to go on till month24.
The report sample is atatched.I have to provide all the 24 months data along with the total.
Not sure if I need to modify the sql or can I play around in the cross tab.
Please advise.
Capture.PNG
reportSample.PNG
Iam trying to display this in a crystal report.I guess a cross tab will help.
I have generated data for month1, month2, but it needs to go on till month24.
The report sample is atatched.I have to provide all the 24 months data along with the total.
Not sure if I need to modify the sql or can I play around in the cross tab.
Please advise.
Capture.PNG
reportSample.PNG
Active 3 days ago
You're trying to create a column for each month, so each record contains all 24 months. That won't really work for a cross-tab, because a cross-tab uses groups (one, or more, groups for the rows, and one, or more, groups for the columns), and each record in a report can only appear once in any given group. You would need each record to be included in all 24 columns (ie. in each group), and CR just doesn't work like that.
Then again, if you have a separate column for each month, it doesn't seem like you need a cross-tab. You could just put each month field (month1_total, month2_total, etc.) on the report.
However, a better way to handle this might be to put each month in a separate record (I guess this may essentially be the "pivot" option you asked about). Your DUAL CTE has a separate column for each month (sdate, sdate2, etc.). You could put each month in a separate row instead. Something like:
Then you'd use date_create to Join d_item1 with DUAL, to get a row for each month. You could include sdate in the query results, and then the report could have a cross-tab, using sdate for the columns (grouped by day). Or you could include date_create in the query results, and use that for the cross-tab columns (grouped by month).
One big advantage to the "separate row for each month" approach is that you'd just have one CTE for the months (DUAL), which would be Joined with your main data, and that's it. With your approach, you'd presumably end up with 24 CTE's (one for each month, like MONTH_1 and MONTH_2), all of which would have to be Joined with your main data.
However, I'm not sure how ALL_NSN_INFO and speedex_id figure into this. ALL_NSN_INFO is grouped by nsn and speedex_id, but the month CTE's are only grouped by nsn, and don't reference speedex_id at all. Are ALL_NSN_INFO and the month CTE's grouping on two different levels? Or is the relationship between nsn and speedex_id unique, so grouping by nsn is the same as grouping by nsn and speedex_id ?
If the grouping is really the same, I think you could just do the summary in the report, and don't really need ALL_NSN_INFO at all. If you use a cross-tab, it will give you a total for each row. Or, using your original approach, you could have a formula add the month columns (month1_total, month2_total, etc.) together.
James
Then again, if you have a separate column for each month, it doesn't seem like you need a cross-tab. You could just put each month field (month1_total, month2_total, etc.) on the report.
However, a better way to handle this might be to put each month in a separate record (I guess this may essentially be the "pivot" option you asked about). Your DUAL CTE has a separate column for each month (sdate, sdate2, etc.). You could put each month in a separate row instead. Something like:
WITH DUAL as
(
SELECT 1 AS MONTH_COUNT,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -24, 0) ,101) as sdate,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -23, 0) ,101) as edate
UNION
SELECT 2 AS MONTH_COUNT,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -23, 0) ,101) as sdate,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -22, 0) ,101) as edate
UNION
<and so on>
SELECT 24 AS MONTH_COUNT,
CONVERT(Varchar(10),DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -1, 0) ,101) as sdate,
CONVERT(varchar(10),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),101) as edate
),
Then you'd use date_create to Join d_item1 with DUAL, to get a row for each month. You could include sdate in the query results, and then the report could have a cross-tab, using sdate for the columns (grouped by day). Or you could include date_create in the query results, and use that for the cross-tab columns (grouped by month).
One big advantage to the "separate row for each month" approach is that you'd just have one CTE for the months (DUAL), which would be Joined with your main data, and that's it. With your approach, you'd presumably end up with 24 CTE's (one for each month, like MONTH_1 and MONTH_2), all of which would have to be Joined with your main data.
However, I'm not sure how ALL_NSN_INFO and speedex_id figure into this. ALL_NSN_INFO is grouped by nsn and speedex_id, but the month CTE's are only grouped by nsn, and don't reference speedex_id at all. Are ALL_NSN_INFO and the month CTE's grouping on two different levels? Or is the relationship between nsn and speedex_id unique, so grouping by nsn is the same as grouping by nsn and speedex_id ?
If the grouping is really the same, I think you could just do the summary in the report, and don't really need ALL_NSN_INFO at all. If you use a cross-tab, it will give you a total for each row. Or, using your original approach, you could have a formula add the month columns (month1_total, month2_total, etc.) together.
James
Active today
WHy don't you let Crystal do the summarizing and just provide the raw data.
Try this query
select
nsn,
speedex_id,
sum(qty_required) as total_qty
from d_item1 where date_create >= DateAdd(24, 'M', CurrentDate) and status between '01' and '79'
and program in ('770','772','773','774','
You can then use the cross tab in Crystal to create the appropriate columns and rows.
For the date columns just set the group to be by MONTH
mlmcc
Try this query
select
nsn,
speedex_id,
sum(qty_required) as total_qty
from d_item1 where date_create >= DateAdd(24, 'M', CurrentDate) and status between '01' and '79'
and program in ('770','772','773','774','
You can then use the cross tab in Crystal to create the appropriate columns and rows.
For the date columns just set the group to be by MONTH
mlmcc
Active today
The only reason I see for that is to ensure there is data for all months in the time frame.
mlmcc
mlmcc
Featured Post
Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat. The purpose of this eBook is to educate the reader about ransomware attacks.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface.
Change Cu…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month5 days, 11 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
627 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.