Star79
asked on
SQl Query Help
Hello All,
I have the query below :
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','77 9')
group by nsn
)
I have the query below :
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
Iam looking to simplify this so that I dont have to create 24 month total cte's like the following till 24MONTH_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','
group by nsn
)
ASKER
I mean I dont see efficient coding to create the below 24 times to get the final output
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','77 9')
group by nsn
)
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','
group by nsn
)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hello Scott is there a way to tell the Month and year for month1, month2..instead say March 2014,April 2014,May 2014....
>>"is there a way to tell the Month and year for month1, month2..instead say March 2014,April 2014" ?
Do you mean is there a way to have column headings of "March 2014" instead of "Month1"?
If that is what you mean than this is much more difficult and you need to create "dynamic sql" (i.e. run a query that writes out a new query as a string, then that string gets executed)
Is this query designed to be used with Crystal? In an earlier related question it was suggested you use Crystal's crosstab capabilities instead of SQL; you might be well advised to do that instead of attempting dynamic sql.
Do you mean is there a way to have column headings of "March 2014" instead of "Month1"?
If that is what you mean than this is much more difficult and you need to create "dynamic sql" (i.e. run a query that writes out a new query as a string, then that string gets executed)
Is this query designed to be used with Crystal? In an earlier related question it was suggested you use Crystal's crosstab capabilities instead of SQL; you might be well advised to do that instead of attempting dynamic sql.
What is the date format for the date_create field?
ASKER
date_create field format is smalldatetime like 1989-07-07 00:00:00
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for all the queries but Iam looking to extract data in a pivot form.Please let me know.
I understand you want a pivot but perhaps you could answer my questions?
>>"is there a way to tell the Month and year for month1, month2..instead say March 2014,April 2014" ?
Q1 Do you mean is there a way to have column headings of "March 2014" instead of "Month1"?
Q2 Is this query designed to be used with Crystal?
If you do want "dynamic column headings" this is MUCH MORE DIFFICULT and might not be suitable for Crystal.
A Crystal crosstab might be your best option. (i.e. get Crystal to do the "pivot" for you, don't do a pivot in sql)
>>"is there a way to tell the Month and year for month1, month2..instead say March 2014,April 2014" ?
Q1 Do you mean is there a way to have column headings of "March 2014" instead of "Month1"?
Q2 Is this query designed to be used with Crystal?
If you do want "dynamic column headings" this is MUCH MORE DIFFICULT and might not be suitable for Crystal.
A Crystal crosstab might be your best option. (i.e. get Crystal to do the "pivot" for you, don't do a pivot in sql)
hanks for all the queries but Iam looking to extract data in a pivot formGive us an example of the desired result.
ASKER
Iam using Pentaho for the reporting.it has some issues with dynamic pivot.So I had to use sub-reports to get the column headers and the details section
Without digging into your T-SQL, copy-paste the below code into your SSMS, execute it to verify it works, and modify to meet your needs:
Open in new window