Joining 5 tables in to one

Transact-SQL
1

Hello

I have 5 Queries separately with comman columns I need to combine the query and make into to one table
Expect your help.

Thanks.

Select [Model Code],count([Model Code]) as TotalVehicle from R_ResrvStock where Status='Stock' group by [Model Code] order by [Model Code]

Select [Model Code],Count(Status) as WIP from R_ResrvStock where Status='WIP' group by [Model Code] order by [Model Code]

Select [Model Code],Count(Status) as Reserved from R_ResrvStock where Status='Reserved' group by [Model Code] order by [Model Code]

Select [Model Code],Count(Status) as Ready from R_ResrvStock where Status='Ready' group by [Model Code] order by [Model Code]

DECLARE @Fdate DATETIME
DECLARE @Cdate DATETIME
Set @Fdate=(SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101) AS FROMDAT)
Set @CDate=(SELECT CONVERT(VARCHAR(25),GETDATE(),101) AS TODAY)
**Select [Model Code],Count(Status) as Delivered from R_ResrvStock where Status='Delivered' **
and Delivery_Date >=@Fdate and Delivery_Date <=@Cdate group by [Model Code] order by [Model Code]

2

One easy way is to take your five querys and use them as CTEs:

DECLARE @Fdate DATETIME;
DECLARE @Cdate DATETIME;

SET @Fdate = (
        SELECT CONVERT(VARCHAR(25), DATEADD(dd, - (DAY(GETDATE()) - 1
                        ), GETDATE()), 101) AS FROMDAT
        );
SET @CDate = (
        SELECT CONVERT(VARCHAR(25), GETDATE(), 101) AS TODAY
        );

WITH 
	Totals AS (
		SELECT [Model Code]
			 , count([Model Code]) AS TotalVehicle
		FROM R_ResrvStock
		WHERE STATUS = 'Stock'
		GROUP BY [Model Code]
		ORDER BY [Model Code]
		)
	, WIPs AS (
		SELECT [Model Code]
			 , Count(STATUS) AS WIP
		FROM R_ResrvStock
		WHERE STATUS = 'WIP'
		GROUP BY [Model Code]
		ORDER BY [Model Code]
		)
	, Reserveds AS (
		SELECT [Model Code]
			 , Count(STATUS) AS Reserved
		FROM R_ResrvStock
		WHERE STATUS = 'Reserved'
		GROUP BY [Model Code]
		ORDER BY [Model Code]
		)
	, Readys AS (
		SELECT [Model Code]
			 , Count(STATUS) AS Ready
		FROM R_ResrvStock
		WHERE STATUS = 'Ready'
		GROUP BY [Model Code]
		ORDER BY [Model Code]
		)
	, Delivereds AS (
		SELECT [Model Code]
			 , Count(STATUS) AS Delivered
		FROM R_ResrvStock
		WHERE STATUS = 'Delivered'
			AND Delivery_Date >= @Fdate
			AND Delivery_Date <= @Cdate
		GROUP BY [Model Code]
		ORDER BY [Model Code]
		)
		
-- Main Query
		
SELECT Totals.[Model Code]
     , Totals.TotalVehicle AS 'Total Vehicles'
     , WIPs.WIP AS Reserved
     , Readys.Ready
     , Delivereds.Delivered
FROM Totals
JOIN WIPs ON Totals.[Model Code] = Wips.[Model Code]
JOIN Reserveds ON Totals.[Model Code] = Reserveds.[Model Code]
JOIN Readys ON Totals.[Model Code] = Readys.[Model Code]
JOIN Delivereds ON Totals.[Model Code] = Wips.[Model Code]
1 Like
3 
Select [Model Code],
    Sum(Case When Status = 'Stock' Then 1 Else 0 End) as TotalVehicle,
    Sum(Case When Status = 'WIP' Then 1 Else 0 End) as WIP,
    Sum(Case When Status = 'Reserved' Then 1 Else 0 End) as Reserved,
    Sum(Case When Status = 'Ready' Then 1 Else 0 End) as Ready,
    Sum(Case When Status = 'Delivered' Then 1 Else 0 End) as Delivered
From R_ResrvStock 
Group By [Model Code]
Order By [Model Code]
4

Thanks for the code man.

When i run the code it gives null value

5

Thanks man,

For delivery i need for current month only where i can substitute the from date and end date

Thanks

6 
Select [Model Code],
    Sum(Case When Status = 'Stock' Then 1 Else 0 End) as TotalVehicle,
    Sum(Case When Status = 'WIP' Then 1 Else 0 End) as WIP,
    Sum(Case When Status = 'Reserved' Then 1 Else 0 End) as Reserved,
    Sum(Case When Status = 'Ready' Then 1 Else 0 End) as Ready,
    Sum(Case When Status = 'Delivered' AND Delivery_Date >= @Fdate AND Delivery_Date <= @Cdate
             Then 1 Else 0 End) as Delivered
From R_ResrvStock 
Group By [Model Code]
Order By [Model Code]