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.
Author |
Topic |
kwikwisi
Constraint Violating Yak Guru
283 Posts |
Posted - 2013-03-04 : 22:39:49
|
How can I amend the below query to get divisor with the same week number (wkno) as dividend based on datetime column of divisor .SELECT CONVERT(decimal(10, 2), SUM(mAmount) / (SELECT COUNT(*) AS Expr1 FROM Filterednew_appointment WHERE (new_appointmentstatus = 'A') ) ) AS TotalBillSize, WkNo,'O' AS TypeFROM (SELECT DISTINCT new_patienthrn AS HRN, new_paidamount AS mAmount, new_name AS BillNo, CASE WHEN (DAY(new_startdate) >= 1 AND DAY(new_startdate) <= 7) THEN 'Wk1' WHEN (DAY(new_startdate) >= 8 AND DAY(new_startdate) <= 14) THEN 'Wk2' WHEN (DAY(new_startdate) >= 15 AND DAY(new_startdate) <= 21) THEN 'Wk3' WHEN (DAY(new_startdate) >= 22 AND DAY(new_startdate) <= 28) THEN 'Wk4' WHEN (DAY(new_startdate) > 28) THEN 'Wk5' END AS WkNo FROM Filterednew_outpatientbillinginfo WHERE (new_patienthrn IS NOT NULL) AND (MONTH(new_startdate) = MONTH(@SDate)) AND (YEAR(new_startdate) = YEAR(@SDate))) AS BGROUP BY WkNo |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-04 : 22:48:24
|
i didnt get your question clearly. I cant see any datetime column in divisor here. Anyways, if your question was to get weekno from datetime field, use DATEPART() function. Also it gives weekno of the year and not weekno of the month.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
kwikwisi
Constraint Violating Yak Guru
283 Posts |
Posted - 2013-03-04 : 23:06:25
|
Dividend is CONVERT(decimal(10, 2), SUM(mAmount)Divisor is (SELECT COUNT(*) AS Expr1FROM Filterednew_appointmentWHERE (new_appointmentstatus = 'A') )Divisor has a date time column "new_appointmentdate" but not in query currently.Whole query returns sum group by week no and divided by count returned from divisor (suppose divisor returns 30)Eg.100/30 wk1200/30 wk2But I'd like to change asif divisor has 10 records for wk1 ,divide 100 by 10if divisor has 20 records for wk2, divide 200 by 20Calculation of week number will be the same as main query but take "appointmentdate" from divisor tableThanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-04 : 23:44:38
|
then change divisor toSELECT COUNT(DISTINCT DATEPART(wk,new_appointmentdate)) AS Expr1FROM Filterednew_appointmentWHERE (new_appointmentstatus = 'A')------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
kwikwisi
Constraint Violating Yak Guru
283 Posts |
Posted - 2013-03-05 : 00:10:59
|
Thank you. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-05 : 00:15:13
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|