May 24, 2011 at 9:04 am
ok, so i have written a query that gives me the counts for current mont. I have also written a query that gives me current year.
what i need to know is what is the best way to join these queries so that i have one select that gives me a column with current month and a column for ytd counts.
everytime i join them my numbers get all messed up.
currently the code i have for current month is:
SELECT
d.month_date_id,
s.customer_id,
COUNT (DISTINCT
(
CASE
WHEN s.checkout_date BETWEEN d.month_start_date
AND d.month_end_date
THEN s.id
END
))item1,
SUM
(
CASE
WHEN s.checkout_date BETWEEN d.month_start_date
AND d.month_end_date
THEN (extract(days from s.date2-s.date1))
END
) item2,
COUNT (DISTINCT
(
CASE
WHEN s.checkout_date BETWEEN d.month_start_date
AND d.month_end_date
and s.flag1 = 'Y'
THEN s.1id
END
))item3,
COUNT (DISTINCT
(
CASE
WHEN s.checkout_date BETWEEN d.month_start_date
AND d.month_end_date
and s.flag2 = 'Y'
THEN s.id
END
))item4,
case
when item2 is not null then 'thingb'
when item3 is not null then 'thingc'
when NoShow is not null then 'thingd'
end Type
FROM
table1 S
INNER JOIN
t_dim_date D
ON
TO_CHAR (s.date2, 'YYYYMMDD') = d.date_id
LEFT OUTER JOIN
table2 f
ON
s.id = f.id
WHERE
year_nbr = 2011 and
to_char(actual_date,'YYYYMM')= to_char(current_date,'YYYYMM')
GROUP BY
d.month_Date_id,
s.customer_id
the ytd code is:SELECT
d.month_date_id,
s.customer_id,
COUNT (DISTINCT
(
CASE
WHEN s.checkout_date BETWEEN d.month_start_date
AND d.month_end_date
THEN s.id
END
))item1,
SUM
(
CASE
WHEN s.checkout_date BETWEEN d.month_start_date
AND d.month_end_date
THEN (extract(days from s.date2-s.date1))
END
) item2,
COUNT (DISTINCT
(
CASE
WHEN s.checkout_date BETWEEN d.month_start_date
AND d.month_end_date
and s.flag1 = 'Y'
THEN s.1id
END
))item3,
COUNT (DISTINCT
(
CASE
WHEN s.checkout_date BETWEEN d.month_start_date
AND d.month_end_date
and s.flag2 = 'Y'
THEN s.id
END
))item4,
case
when item2 is not null then 'thingb'
when item3 is not null then 'thingc'
when NoShow is not null then 'thingd'
end Type
FROM
table1 S
INNER JOIN
t_dim_date D
ON
TO_CHAR (s.date2, 'YYYYMMDD') = d.date_id
LEFT OUTER JOIN
table2 f
ON
s.id = f.id
WHERE
year_nbr = 2011 and
to_char(actual_date,'YYYY')= to_char(current_date,'YYYY')
GROUP BY
d.month_Date_id,
s.customer_id
The output should be 3 columns:
type CM YTD
thingA 6 18
thingB 27 68
thingC 4 50
Please dont laugh too much at my sql. if there is a better faster way, i am totally open to that. I in no way, shape, or form claim to be the best sql coder.
Any help is appreciated!
May 24, 2011 at 9:26 am
Looks like Oracle SQL.
Are you sure it's MS SQL Server T-SQL?
-- Gianluca Sartori
May 24, 2011 at 10:03 am
well actually its code for a netezza box. i was just copying someone elses code and making it my own. i was wondering why i hadnt done it like that before when i was working on sql server boxes.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply