Weekday Report - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Weekday Report

Author

Topic

youngoz
Starting Member

6 Posts

Posted - 2008-05-29 : 18:05:20

Hello Guys,

I'm going crazy tring to figure out how to generate a report that execute a Store procedure and then give me a total Per WeekDay and group it by Part Number.

For example my data should look like this

(PartNumber-Monday-Tuesday-Wednesday-Thursday-Friday-Saturday-Sunday-)

The report will give me a list of all available part and the sold sold per part per day.

Thanks in Advance.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-29 : 18:27:02

Something like this maybe? (untested)

SELECT
	PartNumber,
	SUM(CASE WHEN MyDay = 'Monday' THEN 1 ELSE NULL END) AS Monday,
	...
	SUM(CASE WHEN MyDay = 'Sunday' THEN 1 ELSE NULL END) AS Sunday
FROM
	MyTable
GROUP BY
	PartNumber

youngoz
Starting Member

6 Posts

Posted - 2008-05-29 : 19:13:13

I tried doing that and it not summing all the values. For examepl let say I have two part number

Part Number

Q
B

The sales team sold the follwoing

Monday Q = 3
Monday B = 1
Friday Q = 15
Friday B = 0

So when I run the query is it returns the following

Part Number M--T--W--T--F--Sat--Sun--

Q 3 0 0 0 0 0
B 1 0 0 0 0 0

Why is the sum not being sum correctly?

Thanks in Advance.

dshelton
Yak Posting Veteran

73 Posts

Posted - 2008-05-29 : 20:08:37

For the SUM statements try using a 0 instead of NULL.

SUM(CASE WHEN MyDay = 'Sunday' THEN 1 ELSE 0 END) AS Sunday

David

youngoz
Starting Member

6 Posts

Posted - 2008-05-30 : 01:15:00

I tried using that it I still get the same result. I think I know were the problem occurs. In order to SUM the data I need to specify what Weekday to sum otherwise its going to sum all of the days that why I getting the same result as duplicate. My question is how can I run a SubQuery within the CASE WHEN statement. This is what I got so far

Case WHEN Temp1.CouponAmount IS NULL THEN Case When Sum(Detail.RetailPrice - Item.Commision) <=0 THEN '0.00' ELSE Sum(Temp1.CouponAmount - Item.Commision) * 8.625 ELSE
Case WHEN TempCoupon.CouponType = 'Currency' THEN Sum(Detail.RetailPrice - Item.Commision))
ELSE (Here is where I need to add the sub query but take the data from the query above.)

Thanks in Advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-30 : 01:28:54

Can you post your table structure with some sample data?

youngoz
Starting Member

6 Posts

Posted - 2008-05-30 : 08:39:41

I would love to but the query it huge and it will just get you confuse. Here is what I can say. I have a store procedure that get the Order information like Order Id, Model, Part Number, Total, Tax Rate, Total Tax.

I have 3 tables. One is called Order_Master which contains the Primary Key thats follow by my other table called Order_Detail and I have a table called OrderCoupons which is basically used to store information when a coupon is scan.

The table called Order Master contains the OrderId, UserName, TransctioDate, Status
The table called Order Details contains the OrderId, DetailId, PartNumber, Quantity, RetailPrice, TaxRate
The table called Order Coupon contains the OrderId, DetailId, CouponId

Now the store procedure takes care of all the Joining and what I'm trying to do is do a Select statement just like what dshelton said and called then store procedure and a Sub Query to the Case WHEN statement.
For example:

Case When DateName(Weekday, TransactionDate)='Monday' Then 'SUM the TOTAL WHERE the TransactionDate equal Monday' ELSE '' END

Do you see what Im trying to do?

Here is my SQL codes that GIVE me the total.

Case WHEN TempCoupon.CouponAmount IS NULL THEN Case When Sum(TempDetail.RetailPrice - TempPlan.Commision)+ Sum(TempDetail.RetailPrice - TempPlan.Commision) * TempDetail.TaxRate /100 <=0 THEN '0.00' ELSE Sum(TempDetail.RetailPrice - TempPlan.Commision)+ Sum(TempDetail.RetailPrice - TempPlan.Commision) * TempDetail.TaxRate /100 End ELSE
Case WHEN TempCoupon.CouponType = 'Currency' THEN Sum(((((TempDetail.RetailPrice - TempPlan.Commision) - TempCoupon.CouponAmount)* TempDetail.TaxRate /100 )+ TempDetail.RetailPrice - TempPlan.Commision)- TempCoupon.CouponAmount)
ELSE (Sum(TempDetail.RetailPrice - TempPlan.Commision) -
Sum((TempDetail.RetailPrice - TempPlan.Commision) * TempCoupon.CouponAmount /100))* TempDetail.TaxRate /100 +
(Sum(TempDetail.RetailPrice - TempPlan.Commision) -
Sum((TempDetail.RetailPrice - TempPlan.Commision) * TempCoupon.CouponAmount /100))
END END AS [Total]

Thanks in advance

Thanks in Advance.

dshelton
Yak Posting Veteran

73 Posts

Posted - 2008-06-01 : 20:54:32

how did you apply the Weekday logic to this code?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 00:54:01

quote:
Originally posted by youngoz

I would love to but the query it huge and it will just get you confuse. Here is what I can say. I have a store procedure that get the Order information like Order Id, Model, Part Number, Total, Tax Rate, Total Tax.

I have 3 tables. One is called Order_Master which contains the Primary Key thats follow by my other table called Order_Detail and I have a table called OrderCoupons which is basically used to store information when a coupon is scan.

The table called Order Master contains the OrderId, UserName, TransctioDate, Status
The table called Order Details contains the OrderId, DetailId, PartNumber, Quantity, RetailPrice, TaxRate
The table called Order Coupon contains the OrderId, DetailId, CouponId

Now the store procedure takes care of all the Joining and what I'm trying to do is do a Select statement just like what dshelton said and called then store procedure and a Sub Query to the Case WHEN statement.
For example:

Case When DateName(Weekday, TransactionDate)='Monday' Then 'SUM the TOTAL WHERE the TransactionDate equal Monday' ELSE '' END

Do you see what Im trying to do?

Here is my SQL codes that GIVE me the total.

Case WHEN TempCoupon.CouponAmount IS NULL THEN Case When Sum(TempDetail.RetailPrice - TempPlan.Commision)+ Sum(TempDetail.RetailPrice - TempPlan.Commision) * TempDetail.TaxRate /100 <=0 THEN '0.00' ELSE Sum(TempDetail.RetailPrice - TempPlan.Commision)+ Sum(TempDetail.RetailPrice - TempPlan.Commision) * TempDetail.TaxRate /100 End ELSE
Case WHEN TempCoupon.CouponType = 'Currency' THEN Sum(((((TempDetail.RetailPrice - TempPlan.Commision) - TempCoupon.CouponAmount)* TempDetail.TaxRate /100 )+ TempDetail.RetailPrice - TempPlan.Commision)- TempCoupon.CouponAmount)
ELSE (Sum(TempDetail.RetailPrice - TempPlan.Commision) -
Sum((TempDetail.RetailPrice - TempPlan.Commision) * TempCoupon.CouponAmount /100))* TempDetail.TaxRate /100 +
(Sum(TempDetail.RetailPrice - TempPlan.Commision) -
Sum((TempDetail.RetailPrice - TempPlan.Commision) * TempCoupon.CouponAmount /100))
END END AS [Total]

Thanks in advance

Thanks in Advance.

Your query should be something like this

SELECT PartNumber,
Sum(Case When DateName(Weekday, TransactionDate)='Monday' Then 'TOTAL WHERE the TransactionDate equal Monday' ELSE 0 END),
Sum(Case When DateName(Weekday, TransactionDate)='Tuesday' Then 'TOTAL WHERE the TransactionDate equal Tuesday' ELSE 0 END) 
...
FROM tables...
...
GROUP BY PartNumber

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources