Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
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
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
),
I added the MONTH_COUNT column just for reference.Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
From novice to tech pro — start learning today.
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