Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Urgent Help on SQL Qurey

Author  Topic 

singireddy
Starting Member

6 Posts
Posted - 2008-11-10 : 12:14:12
I need sql code help, i want the result something like this, could you please help me!!!
I want to group Today,Week,Month, Year like the example i have displayed.

I need RESULT like this.
[CODE]

CORP DEPARTMENT CYCLE GROSS_SALE GROSS_PROFIT
Tes1 Frozen Today 100 45
Tes1 Frozen Week 400 320
Tes1 Frozen Month 3400 2400
Tes1 Frozen Year 123390 102345
Tes1 DRY Today 100 45
Tes1 DRY Week 400 320
Tes1 DRY Month 3400 2400
Tes1 DRY Year 123390 102345
Tes2 Frozen Today 100 45
Tes2 Frozen Week 400 320
Tes2 Frozen Month 3400 2400
Tes2 Frozen Year 123390 102345
Tes2 PAPER Today 100 45
Tes2 PAPER Week 400 320
Tes2 PAPER Month 3400 2400
Tes2 PAPER Year 123390 102345
Tes3 Dry Today 100 45
Tes3 Dry Week 400 320
Tes3 Dry Month 3400 2400
Tes3 Dry Year 123390 102345
Tes3 CANDY Today 100 45
Tes3 CANDY Week 400 320
Tes3 CANDY Month 3400 2400
Tes3 CANDY Year 123390 102345



Once i get the above result i will have to display it in my report like this:



CORP DEPARTMENT CYCLE GROSS_SALE GROSS_PROFIT
Tes1 Frozen Today 100 45
Week 400 320
Month 3400 2400
Year 123390 102345
Tes1 DRY Today 100 45
Week 400 320
Month 3400 2400
Year 123390 102345
Tes2 Frozen Today 100 45
Week 400 320
Month 3400 2400
Year 123390 102345
Tes2 PAPER Today 100 45
Week 400 320
Month 3400 2400
Year 123390 102345
Tes3 Dry Today 100 45
Week 400 320
Month 3400 2400
Year 123390 102345
Tes3 CANDY Today 100 45
Week 400 320
Month 3400 2400
Year 123390 102345

[/CODE]

I am getting the Date for Today from the Table and then i am calculating week from Sunday to Today, Month from First to Today and Year from 1st of Jan to Today

I would really appriciate if anyone could write a SQL to display like above result, I have been try this for a while and running out of time.

Thank you,
Sing.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2008-11-10 : 12:23:20
You already have the query result you need, so just let your reporting software handle the formatting.





CODO ERGO SUM
Go to Top of Page

singireddy
Starting Member

6 Posts
Posted - 2008-11-10 : 12:26:41
quote:
Originally posted by Michael Valentine Jones

You already have the query result you need, so just let your reporting software handle the formatting.



CODO ERGO SUM



I dont have the qurey, i just created the result.

Thank you
Sing
Go to Top of Page

hanbingl
Aged Yak Warrior

652 Posts
Posted - 2008-11-10 : 13:47:19
Show the original Table please.
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2008-11-10 : 14:01:17
select CORP ,DEPARTMENT ,CYCLE ='TODAY',
(select SUM(GROSS_SALE) from yourtable where datefield =convert(varchar,getdate(),112))
union all
select CORP ,DEPARTMENT ,CYCLE ='WEEK',
(select SUM(GROSS_SALE) from yourtable where datefield between convert(varchar,DATEADD(DD, 1 - DATEPART(DW, getdate()),getdate()),112) and convert(varchar,getdate(),112))
union all
select CORP ,DEPARTMENT ,CYCLE ='MONTH',
(select SUM(GROSS_SALE) from yourtable where datefield between CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(getdate())-1),getdate()),112) and convert(varchar,getdate(),112))
union all
select CORP ,DEPARTMENT ,CYCLE ='YEAR',
(select SUM(GROSS_SALE) from yourtable where datefield between CONVERT(varchar,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0),112) and convert(varchar,getdate(),112))
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2008-11-10 : 14:06:07
Sorry, Had left a few things there.

select CORP ,DEPARTMENT ,CYCLE ='TODAY',
(select SUM(GROSS_SALE) from yourtable A where datefield =convert(varchar,getdate(),112) and B.CORP=A.CORP and B.DEPARTMENT=A.DEPARTMENT and B.CYCLE=A.CYCLE),
(select SUM(GROSS_PROFIT) from yourtable A where datefield =convert(varchar,getdate(),112) and B.CORP=A.CORP and B.DEPARTMENT=A.DEPARTMENT and B.CYCLE=A.CYCLE)
from yourtable B

union all

select CORP ,DEPARTMENT ,CYCLE ='WEEK',
(select SUM(GROSS_SALE) from yourtable where datefield between convert(varchar,DATEADD(DD, 1 - DATEPART(DW, getdate()),getdate()),112) and convert(varchar,getdate(),112) and B.CORP=A.CORP and B.DEPARTMENT=A.DEPARTMENT and B.CYCLE=A.CYCLE),
(select SUM(GROSS_PROFIT) from yourtable where datefield between convert(varchar,DATEADD(DD, 1 - DATEPART(DW, getdate()),getdate()),112) and convert(varchar,getdate(),112) and B.CORP=A.CORP and B.DEPARTMENT=A.DEPARTMENT and B.CYCLE=A.CYCLE)
from yourtable B

union all

select CORP ,DEPARTMENT ,CYCLE ='MONTH',
(select SUM(GROSS_SALE) from yourtable where datefield between CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(getdate())-1),getdate()),112) and convert(varchar,getdate(),112)and B.CORP=A.CORP and B.DEPARTMENT=A.DEPARTMENT and B.CYCLE=A.CYCLE),
(select SUM(GROSS_PROFIT) from yourtable where datefield between CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(getdate())-1),getdate()),112) and convert(varchar,getdate(),112)and B.CORP=A.CORP and B.DEPARTMENT=A.DEPARTMENT and B.CYCLE=A.CYCLE)
from yourtable B

union all

select CORP ,DEPARTMENT ,CYCLE ='YEAR',
(select SUM(GROSS_SALE) from yourtable where datefield between CONVERT(varchar,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0),112) and convert(varchar,getdate(),112)and B.CORP=A.CORP and B.DEPARTMENT=A.DEPARTMENT and B.CYCLE=A.CYCLE),
(select SUM(GROSS_PROFIT) from yourtable where datefield between CONVERT(varchar,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0),112) and convert(varchar,getdate(),112)and B.CORP=A.CORP and B.DEPARTMENT=A.DEPARTMENT and B.CYCLE=A.CYCLE)
from yourtable B
Go to Top of Page

singireddy
Starting Member

6 Posts
Posted - 2008-11-10 : 14:19:23
quote:
Originally posted by hanbingl

Show the original Table please.



Thank you for the reply, here is the SQL that i have written for day and week, I have written them seperately. The first qurey displays the results for Today and the second Qurey displays the result for week (Sunday to Todate). I want something which displays the result that i have put at the bottom of this qurey

[CODE]
--First Qurey for today's Result

SELECT
cih.inv_date,
ca.division_no,
d.division_name,
cigd.gl_acnt,
p.product_dept_no,
pd.product_dept_desc,
SUM(CASE cuii.catch_wgt WHEN null THEN cuii.unit_price * cuii.item_qty ELSE cuii.price_per_pound * cuii.catch_wgt END) AS grossSales,
SUM(CASE cuii.catch_wgt WHEN null THEN cuii.item_qty * ISNULL(pch.average_cost,0) ELSE cuii.catch_wgt * ISNULL(pch.average_cost,0.0) END) AS grossCost,
SUM(cuii.item_qty) AS totalUnits,
total_credits = 0
FROM
cust_acnt ca,
cust_inv_header cih,
cust_inv_item cuii,
product p,
product_cost_history pch,
fran_group fa,
product_dept pd,
division d,
cust_inv_gl_dist cigd
WHERE
cih.inv_date='10-01-2008' --between '10-01-2008' and '10-07-2008'
--AND cih.inv_date <= convert(char, getdate(), 110)
AND cih.sales_order_header_no IS NOT NULL
AND cih.cust_acnt_no = ca.cust_acnt_no
AND ca.division_no IS NOT NULL
AND fa.fran_group_no = ca.fran_group_no
AND cuii.cust_inv_header_no = cih.cust_inv_header_no
AND p.product_no = cuii.product_no
AND pd.product_dept_no = p.product_dept_no
AND d.division_no = ca.division_no
AND cih.cust_inv_header_no = cigd.cust_inv_header_no
AND cuii.product_cost_history_no *= pch.product_cost_history_no
GROUP BY
ca.division_no,
d.division_name,
cigd.gl_acnt,
p.product_dept_no,
pd.product_dept_desc,
cih.inv_date
UNION
SELECT
cih.inv_date,
ca.division_no,
d.division_name,
cigd.gl_acnt,
p.product_dept_no,
pd.product_dept_desc,
grossSales = 0,
grossCost = 0,
SUM(cmi.credit_qty * -1) as totalUnits,
SUM(cmi.unit_credit_amt) as total_credits
FROM
cust_acnt ca,
cust_inv_header cih,
credit_memo_header cmh,
credit_memo_item cmi,
division d,
product p,
product_dept pd,
cust_inv_gl_dist cigd
WHERE
cih.inv_date='10-01-2008' --between '10-01-2008' and '10-07-2008'
and ca.cust_acnt_no = cih.cust_acnt_no
AND cmh.orig_cust_inv_header_no = cih.cust_inv_header_no
AND cmi.credit_memo_header_no = cmh.credit_memo_header_no
AND d.division_no = ca.division_no
AND p.product_no = cmi.product_no
AND pd.product_dept_no = p.product_dept_no
AND cih.cust_inv_header_no = cigd.cust_inv_header_no
GROUP BY
ca.division_no,
d.division_name,
cigd.gl_acnt,
p.product_dept_no,
pd.product_dept_desc,
cih.inv_date





--Second Qurey for Week Result

SELECT
cih.inv_date,
ca.division_no,
d.division_name,
cigd.gl_acnt,
p.product_dept_no,
pd.product_dept_desc,
SUM(CASE cuii.catch_wgt WHEN null THEN cuii.unit_price * cuii.item_qty ELSE cuii.price_per_pound * cuii.catch_wgt END) AS grossSales,
SUM(CASE cuii.catch_wgt WHEN null THEN cuii.item_qty * ISNULL(pch.average_cost,0) ELSE cuii.catch_wgt * ISNULL(pch.average_cost,0.0) END) AS grossCost,
SUM(cuii.item_qty) AS totalUnits,
total_credits = 0
FROM
cust_acnt ca,
cust_inv_header cih,
cust_inv_item cuii,
product p,
product_cost_history pch,
fran_group fa,
product_dept pd,
division d,
cust_inv_gl_dist cigd
WHERE
inv_date >= (case datepart(dw, getdate())
when 1 then CONVERT(char, getdate(), 110)
when 2 then CONVERT(char, dateadd(dd, -1, getdate()), 110)
when 3 then CONVERT(char, dateadd(dd, -2, getdate()), 110)
when 4 then CONVERT(char, dateadd(dd, -3, getdate()), 110)
when 5 then CONVERT(char, dateadd(dd, -4, getdate()), 110)
when 6 then CONVERT(char, dateadd(dd, -5, getdate()), 110)
when 7 then CONVERT(char, dateadd(dd, -6, getdate()), 110)
end)
AND inv_date <= CONVERT(char, getdate(), 110)
AND cih.sales_order_header_no IS NOT NULL
AND cih.cust_acnt_no = ca.cust_acnt_no
AND ca.division_no IS NOT NULL
AND fa.fran_group_no = ca.fran_group_no
AND cuii.cust_inv_header_no = cih.cust_inv_header_no
AND p.product_no = cuii.product_no
AND pd.product_dept_no = p.product_dept_no
AND d.division_no = ca.division_no
AND cih.cust_inv_header_no = cigd.cust_inv_header_no
AND cuii.product_cost_history_no *= pch.product_cost_history_no
GROUP BY
ca.division_no,
d.division_name,
cigd.gl_acnt,
p.product_dept_no,
pd.product_dept_desc,
cih.inv_date
UNION
SELECT
cih.inv_date,
ca.division_no,
d.division_name,
cigd.gl_acnt,
p.product_dept_no,
pd.product_dept_desc,
grossSales = 0,
grossCost = 0,
SUM(cmi.credit_qty * -1) as totalUnits,
SUM(cmi.unit_credit_amt) as total_credits
FROM
cust_acnt ca,
cust_inv_header cih,
credit_memo_header cmh,
credit_memo_item cmi,
division d,
product p,
product_dept pd,
cust_inv_gl_dist cigd
WHERE
inv_date >= (case datepart(dw, getdate())
when 1 then CONVERT(char, getdate(), 110)
when 2 then CONVERT(char, dateadd(dd, -1, getdate()), 110)
when 3 then CONVERT(char, dateadd(dd, -2, getdate()), 110)
when 4 then CONVERT(char, dateadd(dd, -3, getdate()), 110)
when 5 then CONVERT(char, dateadd(dd, -4, getdate()), 110)
when 6 then CONVERT(char, dateadd(dd, -5, getdate()), 110)
when 7 then CONVERT(char, dateadd(dd, -6, getdate()), 110)
end)
AND inv_date <= CONVERT(char, getdate(), 110)
and ca.cust_acnt_no = cih.cust_acnt_no
AND cmh.orig_cust_inv_header_no = cih.cust_inv_header_no
AND cmi.credit_memo_header_no = cmh.credit_memo_header_no
AND d.division_no = ca.division_no
AND p.product_no = cmi.product_no
AND pd.product_dept_no = p.product_dept_no
AND cih.cust_inv_header_no = cigd.cust_inv_header_no
GROUP BY
ca.division_no,
d.division_name,
cigd.gl_acnt,
p.product_dept_no,
pd.product_dept_desc,
cih.inv_date
ORDER BY
cigd.gl_acnt desc,
ca.division_no,
p.product_dept_no,
cih.inv_date

[/CODE]


I want my result to be like this

[CODE]
Corporation Department Cycle Net Sales Net Profit Total Units total_credits
RIVER VALLEY MANAGEMENT
FROZEN PRODUCTS Today 4,232.70 261.66 144
Week 4,232.70 261.66 144
Month 3434 3434 232
Year 23 233 32

REFRIGERATED Today 2,436.54 206.04 108
Week 2,436.54 206.04 108
Month
Year

COCA COLA Today 1,074.00 -17.82 30
Week 1,074.00 -17.82 30
Month
Year

FUEL SURCHARGE Today 53.28 53.28 444
Week 53.28 53.28 444
Month
Year

KABOOM,LLC
FROZEN PRODUCTS Today 44,435.76 2,459.58 1,546
Week 44,435.76 2,459.58 1,546
Month
Year

REFRIGERATED Today 14,912.36 1,282.44 652
Week 14,912.36 1,282.44 652
Month
Year

COCA COLA Today 14,434.92 -318.18 352
Week 14,434.92 -318.18 352
Month
Year

FUEL SURCHARGE Today 641.28 641.28 5,344
Week 641.28 641.28 5,344
Month
Year


[/code]

Please let me know if you need more information.

Thank you
Sing
Go to Top of Page

singireddy
Starting Member

6 Posts
Posted - 2008-11-10 : 15:02:08
quote:
Originally posted by sakets_2000

Sorry, Had left a few things there.

select CORP ,DEPARTMENT ,CYCLE ='TODAY',
(select SUM(GROSS_SALE) from yourtable A where datefield =convert(varchar,getdate(),112) and B.CORP=A.CORP and B.DEPARTMENT=A.DEPARTMENT and B.CYCLE=A.CYCLE),
(select SUM(GROSS_PROFIT) from yourtable A where datefield =convert(varchar,getdate(),112) and B.CORP=A.CORP and B.DEPARTMENT=A.DEPARTMENT and B.CYCLE=A.CYCLE)
from yourtable B

union all

select CORP ,DEPARTMENT ,CYCLE ='WEEK',
(select SUM(GROSS_SALE) from yourtable where datefield between convert(varchar,DATEADD(DD, 1 - DATEPART(DW, getdate()),getdate()),112) and convert(varchar,getdate(),112) and B.CORP=A.CORP and B.DEPARTMENT=A.DEPARTMENT and B.CYCLE=A.CYCLE),
(select SUM(GROSS_PROFIT) from yourtable where datefield between convert(varchar,DATEADD(DD, 1 - DATEPART(DW, getdate()),getdate()),112) and convert(varchar,getdate(),112) and B.CORP=A.CORP and B.DEPARTMENT=A.DEPARTMENT and B.CYCLE=A.CYCLE)
from yourtable B

union all

select CORP ,DEPARTMENT ,CYCLE ='MONTH',
(select SUM(GROSS_SALE) from yourtable where datefield between CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(getdate())-1),getdate()),112) and convert(varchar,getdate(),112)and B.CORP=A.CORP and B.DEPARTMENT=A.DEPARTMENT and B.CYCLE=A.CYCLE),
(select SUM(GROSS_PROFIT) from yourtable where datefield between CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(getdate())-1),getdate()),112) and convert(varchar,getdate(),112)and B.CORP=A.CORP and B.DEPARTMENT=A.DEPARTMENT and B.CYCLE=A.CYCLE)
from yourtable B

union all

select CORP ,DEPARTMENT ,CYCLE ='YEAR',
(select SUM(GROSS_SALE) from yourtable where datefield between CONVERT(varchar,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0),112) and convert(varchar,getdate(),112)and B.CORP=A.CORP and B.DEPARTMENT=A.DEPARTMENT and B.CYCLE=A.CYCLE),
(select SUM(GROSS_PROFIT) from yourtable where datefield between CONVERT(varchar,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0),112) and convert(varchar,getdate(),112)and B.CORP=A.CORP and B.DEPARTMENT=A.DEPARTMENT and B.CYCLE=A.CYCLE)
from yourtable B


Saket,
Thank you for the quick reply,
I will try adding union all to the following two quries that i have written:

Here is the SQL that i have written for day and week, I have written them seperately. The first qurey displays the results for Today and the second Qurey displays the result for week (Sunday to Todate). I want something which displays the result that i have put at the bottom of this qurey. I will try by adding UNION ALL for these 2 quries, hope will give me the cirrect result.





[CODE]
--First Qurey for today's Result

SELECT
cih.inv_date,
ca.division_no,
d.division_name,
cigd.gl_acnt,
p.product_dept_no,
pd.product_dept_desc,
SUM(CASE cuii.catch_wgt WHEN null THEN cuii.unit_price * cuii.item_qty ELSE cuii.price_per_pound * cuii.catch_wgt END) AS grossSales,
SUM(CASE cuii.catch_wgt WHEN null THEN cuii.item_qty * ISNULL(pch.average_cost,0) ELSE cuii.catch_wgt * ISNULL(pch.average_cost,0.0) END) AS grossCost,
SUM(cuii.item_qty) AS totalUnits,
total_credits = 0
FROM
cust_acnt ca,
cust_inv_header cih,
cust_inv_item cuii,
product p,
product_cost_history pch,
fran_group fa,
product_dept pd,
division d,
cust_inv_gl_dist cigd
WHERE
cih.inv_date='10-01-2008' --between '10-01-2008' and '10-07-2008'
--AND cih.inv_date <= convert(char, getdate(), 110)
AND cih.sales_order_header_no IS NOT NULL
AND cih.cust_acnt_no = ca.cust_acnt_no
AND ca.division_no IS NOT NULL
AND fa.fran_group_no = ca.fran_group_no
AND cuii.cust_inv_header_no = cih.cust_inv_header_no
AND p.product_no = cuii.product_no
AND pd.product_dept_no = p.product_dept_no
AND d.division_no = ca.division_no
AND cih.cust_inv_header_no = cigd.cust_inv_header_no
AND cuii.product_cost_history_no *= pch.product_cost_history_no
GROUP BY
ca.division_no,
d.division_name,
cigd.gl_acnt,
p.product_dept_no,
pd.product_dept_desc,
cih.inv_date
UNION
SELECT
cih.inv_date,
ca.division_no,
d.division_name,
cigd.gl_acnt,
p.product_dept_no,
pd.product_dept_desc,
grossSales = 0,
grossCost = 0,
SUM(cmi.credit_qty * -1) as totalUnits,
SUM(cmi.unit_credit_amt) as total_credits
FROM
cust_acnt ca,
cust_inv_header cih,
credit_memo_header cmh,
credit_memo_item cmi,
division d,
product p,
product_dept pd,
cust_inv_gl_dist cigd
WHERE
cih.inv_date='10-01-2008' --between '10-01-2008' and '10-07-2008'
and ca.cust_acnt_no = cih.cust_acnt_no
AND cmh.orig_cust_inv_header_no = cih.cust_inv_header_no
AND cmi.credit_memo_header_no = cmh.credit_memo_header_no
AND d.division_no = ca.division_no
AND p.product_no = cmi.product_no
AND pd.product_dept_no = p.product_dept_no
AND cih.cust_inv_header_no = cigd.cust_inv_header_no
GROUP BY
ca.division_no,
d.division_name,
cigd.gl_acnt,
p.product_dept_no,
pd.product_dept_desc,
cih.inv_date





--Second Qurey for Week Result

SELECT
cih.inv_date,
ca.division_no,
d.division_name,
cigd.gl_acnt,
p.product_dept_no,
pd.product_dept_desc,
SUM(CASE cuii.catch_wgt WHEN null THEN cuii.unit_price * cuii.item_qty ELSE cuii.price_per_pound * cuii.catch_wgt END) AS grossSales,
SUM(CASE cuii.catch_wgt WHEN null THEN cuii.item_qty * ISNULL(pch.average_cost,0) ELSE cuii.catch_wgt * ISNULL(pch.average_cost,0.0) END) AS grossCost,
SUM(cuii.item_qty) AS totalUnits,
total_credits = 0
FROM
cust_acnt ca,
cust_inv_header cih,
cust_inv_item cuii,
product p,
product_cost_history pch,
fran_group fa,
product_dept pd,
division d,
cust_inv_gl_dist cigd
WHERE
inv_date >= (case datepart(dw, getdate())
when 1 then CONVERT(char, getdate(), 110)
when 2 then CONVERT(char, dateadd(dd, -1, getdate()), 110)
when 3 then CONVERT(char, dateadd(dd, -2, getdate()), 110)
when 4 then CONVERT(char, dateadd(dd, -3, getdate()), 110)
when 5 then CONVERT(char, dateadd(dd, -4, getdate()), 110)
when 6 then CONVERT(char, dateadd(dd, -5, getdate()), 110)
when 7 then CONVERT(char, dateadd(dd, -6, getdate()), 110)
end)
AND inv_date <= CONVERT(char, getdate(), 110)
AND cih.sales_order_header_no IS NOT NULL
AND cih.cust_acnt_no = ca.cust_acnt_no
AND ca.division_no IS NOT NULL
AND fa.fran_group_no = ca.fran_group_no
AND cuii.cust_inv_header_no = cih.cust_inv_header_no
AND p.product_no = cuii.product_no
AND pd.product_dept_no = p.product_dept_no
AND d.division_no = ca.division_no
AND cih.cust_inv_header_no = cigd.cust_inv_header_no
AND cuii.product_cost_history_no *= pch.product_cost_history_no
GROUP BY
ca.division_no,
d.division_name,
cigd.gl_acnt,
p.product_dept_no,
pd.product_dept_desc,
cih.inv_date
UNION
SELECT
cih.inv_date,
ca.division_no,
d.division_name,
cigd.gl_acnt,
p.product_dept_no,
pd.product_dept_desc,
grossSales = 0,
grossCost = 0,
SUM(cmi.credit_qty * -1) as totalUnits,
SUM(cmi.unit_credit_amt) as total_credits
FROM
cust_acnt ca,
cust_inv_header cih,
credit_memo_header cmh,
credit_memo_item cmi,
division d,
product p,
product_dept pd,
cust_inv_gl_dist cigd
WHERE
inv_date >= (case datepart(dw, getdate())
when 1 then CONVERT(char, getdate(), 110)
when 2 then CONVERT(char, dateadd(dd, -1, getdate()), 110)
when 3 then CONVERT(char, dateadd(dd, -2, getdate()), 110)
when 4 then CONVERT(char, dateadd(dd, -3, getdate()), 110)
when 5 then CONVERT(char, dateadd(dd, -4, getdate()), 110)
when 6 then CONVERT(char, dateadd(dd, -5, getdate()), 110)
when 7 then CONVERT(char, dateadd(dd, -6, getdate()), 110)
end)
AND inv_date <= CONVERT(char, getdate(), 110)
and ca.cust_acnt_no = cih.cust_acnt_no
AND cmh.orig_cust_inv_header_no = cih.cust_inv_header_no
AND cmi.credit_memo_header_no = cmh.credit_memo_header_no
AND d.division_no = ca.division_no
AND p.product_no = cmi.product_no
AND pd.product_dept_no = p.product_dept_no
AND cih.cust_inv_header_no = cigd.cust_inv_header_no
GROUP BY
ca.division_no,
d.division_name,
cigd.gl_acnt,
p.product_dept_no,
pd.product_dept_desc,
cih.inv_date
ORDER BY
cigd.gl_acnt desc,
ca.division_no,
p.product_dept_no,
cih.inv_date




I want my result to be like this,


Corporation Department Cycle Net Sales Net Profit Total Units total_credits
RIVER VALLEY MANAGEMENT
FROZEN PRODUCTS Today 4,232.70 261.66 144
Week 4,232.70 261.66 144
Month 3434 3434 232
Year 23 233 32

REFRIGERATED Today 2,436.54 206.04 108
Week 2,436.54 206.04 108
Month
Year

COCA COLA Today 1,074.00 -17.82 30
Week 1,074.00 -17.82 30
Month
Year

FUEL SURCHARGE Today 53.28 53.28 444
Week 53.28 53.28 444
Month
Year

KABOOM,LLC
FROZEN PRODUCTS Today 44,435.76 2,459.58 1,546
Week 44,435.76 2,459.58 1,546
Month
Year

REFRIGERATED Today 14,912.36 1,282.44 652
Week 14,912.36 1,282.44 652
Month
Year

COCA COLA Today 14,434.92 -318.18 352
Week 14,434.92 -318.18 352
Month
Year

FUEL SURCHARGE Today 641.28 641.28 5,344
Week 641.28 641.28 5,344
Month
Year
[/CODE]

Thank you so much for your response sakets, can you please review my qurey and let me know if it will work out.

Regards
Sing
Go to Top of Page

bklr
Master Smack Fu Yak Hacker

1693 Posts
Posted - 2008-11-17 : 05:21:09
SELECT * FROM FROZEN
ORDER BY CORP,DEPARTMENT DESC , CASE WHEN CYCLE = 'Today' THEN 0 ELSE 1 END ,
CASE WHEN CYCLE = 'Week' THEN 0 ELSE 1 END ,
CASE WHEN CYCLE = 'Month' THEN 0 ELSE 1 END
Go to Top of Page

Nageswar9
Aged Yak Warrior

600 Posts
Posted - 2008-11-17 : 07:49:29
declare @Temp table (COR varchar(32),DEPART varchar(64),CYCl varchar(64),GROSS_SALE int,GROSS_PROFIT int )
insert into @Temp
SELECT CORP,DEPARTMENT,CYCLE,GROSS_SALE,GROSS_PROFIT FROM FROZEN
ORDER BY CORP,DEPARTMENT DESC , CASE WHEN CYCLE = 'Today' THEN 0 ELSE 1 END ,
CASE WHEN CYCLE = 'Week' THEN 0 ELSE 1 END ,
CASE WHEN CYCLE = 'Month' THEN 0 ELSE 1 END

select Case when Cycl in ('Month','Week','Year') then '' else Cor end as 'COR',
case when Cycl in ('Month','Week','Year') then '' else depart end AS 'Department',
CYCL,GROSS_SALE,GROSS_PROFIT
from @Temp

I Struggle For Excellence
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site