[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQl Query Help

Posted on 2015-02-20
12
Medium Priority
?
99 Views
Last Modified: 2015-02-26
Hello All,
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      
                                  

Open in new window

Iam looking to simplify this so that I dont have to create 24 month total cte's like the following till 24
 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
)
0
Comment
Question by:Star79
[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
  • 3
  • 2
  • +2
12 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40621917
>create 24 month total cte's
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:
CREATE TABLE #tmp (dt date) 

Declare @x int = 0, @dt date = CAST(GETDATE() as date) 

WHILE @x < 24
   begin

   INSERT INTO #tmp (dt) 
   SELECT DATEADD(month, @x, @dt) 

   SET @x = @x + 1

   end

SELECT * FROM #tmp

Open in new window

0
 

Author Comment

by:Star79
ID: 40621964
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','779')
group by nsn
)
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1400 total points
ID: 40622064
Please try this:

SELECT
    di.nsn,
    di.speedex_id,
    SUM(CASE WHEN di.date_create >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -24, 0) AND
                  di.date_create <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -23, 0) 
             THEN di.qty_required ELSE 0 END) AS Month1,
    SUM(CASE WHEN di.date_create >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -23, 0) AND
                  di.date_create <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -22, 0) 
             THEN di.qty_required ELSE 0 END) AS Month2,
    SUM(CASE WHEN di.date_create >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -22, 0) AND
                  di.date_create <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -21, 0) 
             THEN di.qty_required ELSE 0 END) AS Month3,
    SUM(CASE WHEN di.date_create >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -21, 0) AND
                  di.date_create <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -20, 0) 
             THEN di.qty_required ELSE 0 END) AS Month4,
    SUM(CASE WHEN di.date_create >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -20, 0) AND
                  di.date_create <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -19, 0) 
             THEN di.qty_required ELSE 0 END) AS Month5,
    --...
    SUM(CASE WHEN di.date_create >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 0, 0) AND
                  di.date_create <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) 
             THEN di.qty_required ELSE 0 END) AS Month24
FROM d_item1 di
WHERE
    di.date_create >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -24, 0) AND
    di.date_create <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) AND
    status BETWEEN '01' AND '79' AND
    program in ('770','772','773','774','775','776','778','779')
GROUP BY
    di.nsn,
    di.speedex_id
ORDER BY
    nsn,
    speedex_id

Open in new window

0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

Author Comment

by:Star79
ID: 40622119
Hello Scott is there a way to tell the Month and year for month1, month2..instead say March 2014,April 2014,May 2014....
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40625462
>>"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.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40625599
What is the date format for the date_create field?
0
 

Author Comment

by:Star79
ID: 40628638
date_create field format is smalldatetime like 1989-07-07 00:00:00
0
 
LVL 52

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 600 total points
ID: 40630390
If it's smalldatetime you shouldn't compare it with a varchar.
Anyway, I think you can simplify your query with a group by year and month. Try this select:
SELECT
	nsn, speedex_id, year(date_create), month(date_create), sum(qty_required) as total_qty
FROM d_item1
WHERE date_create >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -24, 0)
    and date_create <= DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
	and status between '01' and '79'
	and program in ('770','772','773','774','775','776','778','779')
GROUP BY nsn, speedex_id, year(date_create), month(date_create) 

Open in new window

0
 

Author Comment

by:Star79
ID: 40630997
Thanks for all the queries but Iam looking to extract data in a pivot form.Please let me know.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40631942
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)
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40632720
hanks for all the queries but Iam looking to extract data in a pivot form
Give us an example of the desired result.
0
 

Author Closing Comment

by:Star79
ID: 40633867
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
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question